[Home] [Help]
123: */
124: procedure load_kpi(p_kpi varchar2,p_base_table_list varchar2,p_run_id number,p_job_name varchar2,p_options varchar2) is
125: l_dim_set dbms_sql.varchar2_table; --this is all the dimsets, actuals and targets
126: l_aggregate_dim_set dbms_sql.varchar2_table; --dimsets used in aggregation. (Targets eliminated)
127: l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
128: l_base_table_list dbms_sql.varchar2_table;
129: l_varchar_table dbms_sql.varchar2_table;--temp
130: --
131: l_parallel boolean;
385: the correspnding string that contains the prj table also. so for now, we are not adding the prj tables to p_base_tables or l_oor_dimsets
386: */
387: procedure load_kpi_dimset(p_kpi varchar2,p_dim_set varchar2,p_base_tables dbms_sql.varchar2_table) is
388: --
389: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
390: l_load_type varchar2(40); --initial or inc
391: l_oor_dimsets bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
392: Begin
393: --
387: procedure load_kpi_dimset(p_kpi varchar2,p_dim_set varchar2,p_base_tables dbms_sql.varchar2_table) is
388: --
389: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
390: l_load_type varchar2(40); --initial or inc
391: l_oor_dimsets bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
392: Begin
393: --
394: l_olap_object_relation.delete;
395: --get the base tables l_olap_object_relation(i).object are the base tables
434: its ok to call the aggregate marker program since this program is processing each dim set. the same dimset cannot be loaded
435: at the same time by 2 diff sessions. we have lock_dimset_objects here.
436: */
437: procedure load_kpi_dimset_base_table(
438: p_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb
439: ) is
440: --
441: l_load_type varchar2(40); --initial or inc
442: l_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
438: p_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb
439: ) is
440: --
441: l_load_type varchar2(40); --initial or inc
442: l_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
443: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
444: l_aggregate_marker_program varchar2(300);
445: l_load_program varchar2(300);
446: l_load_program_parallel varchar2(300);
439: ) is
440: --
441: l_load_type varchar2(40); --initial or inc
442: l_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
443: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
444: l_aggregate_marker_program varchar2(300);
445: l_load_program varchar2(300);
446: l_load_program_parallel varchar2(300);
447: --
481: --before we call the load programs, we will first run aggregate_marker_program. this will set the limit cubes to true
482: --all the values that need to be re-aggregated because dim hierarchies changed
483: l_dimset_oor.delete;
484: l_aggregate_marker_program:=null;
485: bsc_aw_md_api.get_bsc_olap_object_relation(l_dimset,'kpi dimension set',null,l_kpi,'kpi',l_dimset_oor);
486: for i in 1..l_dimset_oor.count loop
487: if l_dimset_oor(i).relation_type='aggregate marker program' then
488: l_aggregate_marker_program:=l_dimset_oor(i).relation_object;
489: exit;
531: l_pl_change_vector.delete;
532: for i in 1..p_olap_object_relation.count loop
533: l_min_value(i):=to_number(bsc_aw_utility.get_parameter_value(p_olap_object_relation(i).property1,'current change vector',','))+1;
534: l_olap_object_relation.delete;
535: bsc_aw_md_api.get_bsc_olap_object_relation(p_olap_object_relation(i).object,'base table',null,
536: p_olap_object_relation(i).object,'base table',l_olap_object_relation);
537: l_max_value(i):=null;
538: l_bt_current_period(i):=null;
539: for j in 1..l_olap_object_relation.count loop
619: */
620: procedure load_kpi_dimset_base_table(
621: p_kpi varchar2,
622: p_dimset varchar2,
623: p_base_table_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get baes table, dimset and bt measures
624: p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
625: p_load_program varchar2,
626: p_min_value dbms_sql.number_table,
627: p_max_value dbms_sql.number_table,
620: procedure load_kpi_dimset_base_table(
621: p_kpi varchar2,
622: p_dimset varchar2,
623: p_base_table_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get baes table, dimset and bt measures
624: p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
625: p_load_program varchar2,
626: p_min_value dbms_sql.number_table,
627: p_max_value dbms_sql.number_table,
628: p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null'. this has to be in sync with p_base_table_dimset_oor*/
701: */
702: procedure load_kpi_dimset_base_table_job(
703: p_kpi varchar2,
704: p_dimset varchar2,
705: p_base_table_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get baes table, dimset and bt measures
706: p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
707: p_load_program varchar2,
708: p_LB_resync_program varchar2,
709: p_min_value dbms_sql.number_table,
702: procedure load_kpi_dimset_base_table_job(
703: p_kpi varchar2,
704: p_dimset varchar2,
705: p_base_table_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get baes table, dimset and bt measures
706: p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
707: p_load_program varchar2,
708: p_LB_resync_program varchar2,
709: p_min_value dbms_sql.number_table,
710: p_max_value dbms_sql.number_table,
3079: this procedure purges the kpi. i
3080: */
3081: procedure purge_kpi(p_kpi varchar2) is
3082: --
3083: l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
3084: Begin
3085: if g_debug then
3086: log_n('Purge KPI '||p_kpi);
3087: end if;
3086: log_n('Purge KPI '||p_kpi);
3087: end if;
3088: --for purge, we need exclusive lock. else we cannot delete composites
3089: bsc_aw_management.get_workspace_lock('rw',null);
3090: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3091: --clear the cubes
3092: for i in 1..l_bsc_olap_object.count loop
3093: if l_bsc_olap_object(i).object_type='data cube' then
3094: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3088: --for purge, we need exclusive lock. else we cannot delete composites
3089: bsc_aw_management.get_workspace_lock('rw',null);
3090: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3091: --clear the cubes
3092: for i in 1..l_bsc_olap_object.count loop
3093: if l_bsc_olap_object(i).object_type='data cube' then
3094: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3095: end if;
3096: end loop;
3089: bsc_aw_management.get_workspace_lock('rw',null);
3090: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3091: --clear the cubes
3092: for i in 1..l_bsc_olap_object.count loop
3093: if l_bsc_olap_object(i).object_type='data cube' then
3094: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3095: end if;
3096: end loop;
3097: --clear fcst cubes if there are any
3090: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3091: --clear the cubes
3092: for i in 1..l_bsc_olap_object.count loop
3093: if l_bsc_olap_object(i).object_type='data cube' then
3094: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3095: end if;
3096: end loop;
3097: --clear fcst cubes if there are any
3098: --clear the limit cubes
3095: end if;
3096: end loop;
3097: --clear fcst cubes if there are any
3098: --clear the limit cubes
3099: for i in 1..l_bsc_olap_object.count loop
3100: if l_bsc_olap_object(i).object_type='dim limit cube' then
3101: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3102: end if;
3103: end loop;
3096: end loop;
3097: --clear fcst cubes if there are any
3098: --clear the limit cubes
3099: for i in 1..l_bsc_olap_object.count loop
3100: if l_bsc_olap_object(i).object_type='dim limit cube' then
3101: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3102: end if;
3103: end loop;
3104: for i in 1..l_bsc_olap_object.count loop
3097: --clear fcst cubes if there are any
3098: --clear the limit cubes
3099: for i in 1..l_bsc_olap_object.count loop
3100: if l_bsc_olap_object(i).object_type='dim limit cube' then
3101: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3102: end if;
3103: end loop;
3104: for i in 1..l_bsc_olap_object.count loop
3105: if l_bsc_olap_object(i).object_type='limit cube composite' then
3100: if l_bsc_olap_object(i).object_type='dim limit cube' then
3101: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3102: end if;
3103: end loop;
3104: for i in 1..l_bsc_olap_object.count loop
3105: if l_bsc_olap_object(i).object_type='limit cube composite' then
3106: bsc_aw_dbms_aw.execute('maintain '||l_bsc_olap_object(i).olap_object||' delete all');
3107: end if;
3108: end loop;
3101: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3102: end if;
3103: end loop;
3104: for i in 1..l_bsc_olap_object.count loop
3105: if l_bsc_olap_object(i).object_type='limit cube composite' then
3106: bsc_aw_dbms_aw.execute('maintain '||l_bsc_olap_object(i).olap_object||' delete all');
3107: end if;
3108: end loop;
3109: --clear the countvar cubes
3102: end if;
3103: end loop;
3104: for i in 1..l_bsc_olap_object.count loop
3105: if l_bsc_olap_object(i).object_type='limit cube composite' then
3106: bsc_aw_dbms_aw.execute('maintain '||l_bsc_olap_object(i).olap_object||' delete all');
3107: end if;
3108: end loop;
3109: --clear the countvar cubes
3110: for i in 1..l_bsc_olap_object.count loop
3106: bsc_aw_dbms_aw.execute('maintain '||l_bsc_olap_object(i).olap_object||' delete all');
3107: end if;
3108: end loop;
3109: --clear the countvar cubes
3110: for i in 1..l_bsc_olap_object.count loop
3111: if l_bsc_olap_object(i).object_type='countvar cube' then
3112: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3113: end if;
3114: end loop;
3107: end if;
3108: end loop;
3109: --clear the countvar cubes
3110: for i in 1..l_bsc_olap_object.count loop
3111: if l_bsc_olap_object(i).object_type='countvar cube' then
3112: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3113: end if;
3114: end loop;
3115: --clear the composites
3108: end loop;
3109: --clear the countvar cubes
3110: for i in 1..l_bsc_olap_object.count loop
3111: if l_bsc_olap_object(i).object_type='countvar cube' then
3112: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3113: end if;
3114: end loop;
3115: --clear the composites
3116: for i in 1..l_bsc_olap_object.count loop
3112: bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3113: end if;
3114: end loop;
3115: --clear the composites
3116: for i in 1..l_bsc_olap_object.count loop
3117: if l_bsc_olap_object(i).object_type='measure composite' then
3118: bsc_aw_dbms_aw.execute('maintain '||l_bsc_olap_object(i).olap_object||' delete all');
3119: end if;
3120: end loop;
3113: end if;
3114: end loop;
3115: --clear the composites
3116: for i in 1..l_bsc_olap_object.count loop
3117: if l_bsc_olap_object(i).object_type='measure composite' then
3118: bsc_aw_dbms_aw.execute('maintain '||l_bsc_olap_object(i).olap_object||' delete all');
3119: end if;
3120: end loop;
3121: --set the dimset current change vector to 0 for all dimsets of the kpi
3114: end loop;
3115: --clear the composites
3116: for i in 1..l_bsc_olap_object.count loop
3117: if l_bsc_olap_object(i).object_type='measure composite' then
3118: bsc_aw_dbms_aw.execute('maintain '||l_bsc_olap_object(i).olap_object||' delete all');
3119: end if;
3120: end loop;
3121: --set the dimset current change vector to 0 for all dimsets of the kpi
3122: reset_dimset_change_vector(p_kpi);
3127: log_n('Exception in purge_kpi '||sqlerrm);
3128: raise;
3129: End;
3130:
3131: procedure get_dimset_objects(p_kpi varchar2,p_dim_set varchar2,p_oo out nocopy bsc_aw_md_wrapper.bsc_olap_object_tb) is
3132: l_oo_kpi bsc_aw_md_wrapper.bsc_olap_object_tb;
3133: Begin
3134: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_oo_kpi);
3135: for i in 1..l_oo_kpi.count loop
3128: raise;
3129: End;
3130:
3131: procedure get_dimset_objects(p_kpi varchar2,p_dim_set varchar2,p_oo out nocopy bsc_aw_md_wrapper.bsc_olap_object_tb) is
3132: l_oo_kpi bsc_aw_md_wrapper.bsc_olap_object_tb;
3133: Begin
3134: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_oo_kpi);
3135: for i in 1..l_oo_kpi.count loop
3136: if bsc_aw_utility.get_parameter_value(l_oo_kpi(i).property1,'dim set name',',')=p_dim_set then
3130:
3131: procedure get_dimset_objects(p_kpi varchar2,p_dim_set varchar2,p_oo out nocopy bsc_aw_md_wrapper.bsc_olap_object_tb) is
3132: l_oo_kpi bsc_aw_md_wrapper.bsc_olap_object_tb;
3133: Begin
3134: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_oo_kpi);
3135: for i in 1..l_oo_kpi.count loop
3136: if bsc_aw_utility.get_parameter_value(l_oo_kpi(i).property1,'dim set name',',')=p_dim_set then
3137: p_oo(p_oo.count+1):=l_oo_kpi(i);
3138: end if;
3145: /*
3146: this will set the current change vector to 0. called from purge_kpi
3147: */
3148: procedure reset_dimset_change_vector(p_kpi varchar2) is
3149: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3150: l_property varchar2(4000);
3151: Begin
3152: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'base table dim set',p_kpi,'kpi',l_olap_object_relation);
3153: for i in 1..l_olap_object_relation.count loop
3148: procedure reset_dimset_change_vector(p_kpi varchar2) is
3149: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3150: l_property varchar2(4000);
3151: Begin
3152: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'base table dim set',p_kpi,'kpi',l_olap_object_relation);
3153: for i in 1..l_olap_object_relation.count loop
3154: --we must not lose the measures=m1,m2, from the property of base table dim set after we reset the change vector
3155: l_property:=l_olap_object_relation(i).property1;
3156: bsc_aw_utility.update_property(l_property,'current change vector','0',',');
3408: we need to create and load the calendars. its best if we load the calendar here
3409: */
3410: procedure load_calendar_if_needed(p_kpi varchar2) is
3411: --
3412: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3413: l_calendar number;
3414: l_lock_name varchar2(40);
3415: Begin
3416: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
3412: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3413: l_calendar number;
3414: l_lock_name varchar2(40);
3415: Begin
3416: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
3417: for i in 1..l_olap_object_relation.count loop
3418: if l_olap_object_relation(i).relation_type='dim set calendar' then
3419: l_calendar:=to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'calendar',','));
3420: exit;
3443:
3444: /*enh needed to load dim on demand. two scenarios. aw kpi created fresh. aw dim are empty. case II view based dim.
3445: */
3446: procedure load_dim_if_needed(p_kpi varchar2,p_dim_set dbms_sql.varchar2_table) is
3447: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3448: l_dim dbms_sql.varchar2_table;
3449: Begin
3450: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_oor);
3451: for i in 1..l_oor.count loop
3446: procedure load_dim_if_needed(p_kpi varchar2,p_dim_set dbms_sql.varchar2_table) is
3447: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3448: l_dim dbms_sql.varchar2_table;
3449: Begin
3450: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_oor);
3451: for i in 1..l_oor.count loop
3452: if l_oor(i).object_type='kpi dimension set' and (l_oor(i).relation_type='dim set dim' or l_oor(i).relation_type='dim set std dim')
3453: and l_oor(i).relation_object_type='dimension' then
3454: if bsc_aw_utility.in_array(p_dim_set,l_oor(i).object) then
3491: p_table_name varchar2,
3492: p_tables out nocopy dbms_sql.varchar2_table
3493: ) is
3494: --
3495: l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
3496: l_oo_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3497: l_dimset varchar2(100);
3498: l_dimset_name varchar2(200);
3499: l_combinations dbms_sql.varchar2_table;
3492: p_tables out nocopy dbms_sql.varchar2_table
3493: ) is
3494: --
3495: l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
3496: l_oo_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3497: l_dimset varchar2(100);
3498: l_dimset_name varchar2(200);
3499: l_combinations dbms_sql.varchar2_table;
3500: l_combinations_copy dbms_sql.varchar2_table;
3513: l_combinations.delete;
3514: l_dimensions.delete;
3515: --
3516: l_oo_relation.delete;
3517: bsc_aw_md_api.get_bsc_olap_object_relation(l_dimset_name,'kpi dimension set','dim set dim',p_kpi,'kpi',l_oo_relation);
3518: for j in 1..l_oo_relation.count loop
3519: l_dimensions(l_dimensions.count+1):=l_oo_relation(j).relation_object;
3520: end loop;
3521: l_oo_relation.delete;
3518: for j in 1..l_oo_relation.count loop
3519: l_dimensions(l_dimensions.count+1):=l_oo_relation(j).relation_object;
3520: end loop;
3521: l_oo_relation.delete;
3522: bsc_aw_md_api.get_bsc_olap_object_relation(l_dimset_name,'kpi dimension set','dim set std dim',p_kpi,'kpi',l_oo_relation);
3523: for j in 1..l_oo_relation.count loop
3524: l_dimensions(l_dimensions.count+1):=l_oo_relation(j).relation_object;
3525: end loop;
3526: l_oo_relation.delete;
3523: for j in 1..l_oo_relation.count loop
3524: l_dimensions(l_dimensions.count+1):=l_oo_relation(j).relation_object;
3525: end loop;
3526: l_oo_relation.delete;
3527: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'dim set dim level',p_kpi,'kpi',l_oo_relation);
3528: --loop over each dim
3529: for j in 1..l_dimensions.count loop
3530: l_levels.delete;
3531: for k in 1..l_oo_relation.count loop
3597: p_kpi varchar2,
3598: p_dimset varchar2,
3599: p_object_type varchar2,
3600: p_lock_objects out nocopy dbms_sql.varchar2_table) is
3601: l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
3602: l_objects dbms_sql.varchar2_table;
3603: Begin
3604: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3605: for i in 1..l_bsc_olap_object.count loop
3600: p_lock_objects out nocopy dbms_sql.varchar2_table) is
3601: l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
3602: l_objects dbms_sql.varchar2_table;
3603: Begin
3604: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3605: for i in 1..l_bsc_olap_object.count loop
3606: if l_bsc_olap_object(i).olap_object_type='dimension' and l_bsc_olap_object(i).object_type<>'agg map measure dim'
3607: and l_bsc_olap_object(i).object_type<>'measurename dim' and
3608: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3601: l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
3602: l_objects dbms_sql.varchar2_table;
3603: Begin
3604: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3605: for i in 1..l_bsc_olap_object.count loop
3606: if l_bsc_olap_object(i).olap_object_type='dimension' and l_bsc_olap_object(i).object_type<>'agg map measure dim'
3607: and l_bsc_olap_object(i).object_type<>'measurename dim' and
3608: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3609: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3602: l_objects dbms_sql.varchar2_table;
3603: Begin
3604: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3605: for i in 1..l_bsc_olap_object.count loop
3606: if l_bsc_olap_object(i).olap_object_type='dimension' and l_bsc_olap_object(i).object_type<>'agg map measure dim'
3607: and l_bsc_olap_object(i).object_type<>'measurename dim' and
3608: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3609: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3610: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3603: Begin
3604: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3605: for i in 1..l_bsc_olap_object.count loop
3606: if l_bsc_olap_object(i).olap_object_type='dimension' and l_bsc_olap_object(i).object_type<>'agg map measure dim'
3607: and l_bsc_olap_object(i).object_type<>'measurename dim' and
3608: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3609: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3610: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3611: end if;
3604: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3605: for i in 1..l_bsc_olap_object.count loop
3606: if l_bsc_olap_object(i).olap_object_type='dimension' and l_bsc_olap_object(i).object_type<>'agg map measure dim'
3607: and l_bsc_olap_object(i).object_type<>'measurename dim' and
3608: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3609: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3610: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3611: end if;
3612: end loop;
3605: for i in 1..l_bsc_olap_object.count loop
3606: if l_bsc_olap_object(i).olap_object_type='dimension' and l_bsc_olap_object(i).object_type<>'agg map measure dim'
3607: and l_bsc_olap_object(i).object_type<>'measurename dim' and
3608: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3609: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3610: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3611: end if;
3612: end loop;
3613: for i in 1..l_bsc_olap_object.count loop
3606: if l_bsc_olap_object(i).olap_object_type='dimension' and l_bsc_olap_object(i).object_type<>'agg map measure dim'
3607: and l_bsc_olap_object(i).object_type<>'measurename dim' and
3608: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3609: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3610: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3611: end if;
3612: end loop;
3613: for i in 1..l_bsc_olap_object.count loop
3614: if l_bsc_olap_object(i).olap_object_type='cube' and
3609: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3610: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3611: end if;
3612: end loop;
3613: for i in 1..l_bsc_olap_object.count loop
3614: if l_bsc_olap_object(i).olap_object_type='cube' and
3615: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3616: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3617: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3610: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3611: end if;
3612: end loop;
3613: for i in 1..l_bsc_olap_object.count loop
3614: if l_bsc_olap_object(i).olap_object_type='cube' and
3615: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3616: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3617: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3618: end if;
3611: end if;
3612: end loop;
3613: for i in 1..l_bsc_olap_object.count loop
3614: if l_bsc_olap_object(i).olap_object_type='cube' and
3615: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3616: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3617: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3618: end if;
3619: end loop;
3612: end loop;
3613: for i in 1..l_bsc_olap_object.count loop
3614: if l_bsc_olap_object(i).olap_object_type='cube' and
3615: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3616: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3617: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3618: end if;
3619: end loop;
3620: for i in 1..l_bsc_olap_object.count loop
3613: for i in 1..l_bsc_olap_object.count loop
3614: if l_bsc_olap_object(i).olap_object_type='cube' and
3615: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3616: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3617: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3618: end if;
3619: end loop;
3620: for i in 1..l_bsc_olap_object.count loop
3621: if l_bsc_olap_object(i).olap_object_type='variable' and
3616: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3617: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3618: end if;
3619: end loop;
3620: for i in 1..l_bsc_olap_object.count loop
3621: if l_bsc_olap_object(i).olap_object_type='variable' and
3622: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3623: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3624: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3617: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3618: end if;
3619: end loop;
3620: for i in 1..l_bsc_olap_object.count loop
3621: if l_bsc_olap_object(i).olap_object_type='variable' and
3622: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3623: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3624: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3625: end if;
3618: end if;
3619: end loop;
3620: for i in 1..l_bsc_olap_object.count loop
3621: if l_bsc_olap_object(i).olap_object_type='variable' and
3622: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3623: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3624: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3625: end if;
3626: end loop;
3619: end loop;
3620: for i in 1..l_bsc_olap_object.count loop
3621: if l_bsc_olap_object(i).olap_object_type='variable' and
3622: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3623: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3624: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3625: end if;
3626: end loop;
3627: --
3620: for i in 1..l_bsc_olap_object.count loop
3621: if l_bsc_olap_object(i).olap_object_type='variable' and
3622: (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3623: (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3624: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3625: end if;
3626: end loop;
3627: --
3628: for i in 1..l_objects.count loop
3645: p_base_table_list dbms_sql.varchar2_table,
3646: p_dim_set out nocopy dbms_sql.varchar2_table
3647: ) is
3648: --
3649: l_oor_dimset bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3650: Begin
3651: for i in 1..p_base_table_list.count loop
3652: l_oor_dimset.delete;
3653: bsc_aw_md_api.get_base_table_dimset(p_kpi,p_base_table_list(i),'base table dim set',l_oor_dimset);
3664:
3665: /*given a set of dimsets and B tables, finds out the list of B tables from p_base_table_list that belong to the dimset */
3666: procedure get_base_table_for_dimset(p_kpi varchar2,p_base_table_list dbms_sql.varchar2_table,p_dim_set dbms_sql.varchar2_table,
3667: p_dimset_base_tables out nocopy dbms_sql.varchar2_table) is
3668: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3669: Begin
3670: for i in 1..p_dim_set.count loop
3671: l_oor.delete;
3672: bsc_aw_md_api.get_dimset_base_table(p_kpi,p_dim_set(i),'base table dim set',l_oor);
3690: p_base_table_list dbms_sql.varchar2_table,
3691: p_kpi_base_tables out nocopy dbms_sql.varchar2_table
3692: ) is
3693: --
3694: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3695: l_base_table varchar2(100);
3696: Begin
3697: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'base table dim set',p_kpi,'kpi',l_olap_object_relation);
3698: for i in 1..l_olap_object_relation.count loop
3693: --
3694: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3695: l_base_table varchar2(100);
3696: Begin
3697: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'base table dim set',p_kpi,'kpi',l_olap_object_relation);
3698: for i in 1..l_olap_object_relation.count loop
3699: l_base_table:=null;
3700: if p_base_table_list.count>0 then
3701: if bsc_aw_utility.in_array(p_base_table_list,l_olap_object_relation(i).object) then
3893: p_b_tables dbms_sql.varchar2_table,
3894: p_ds_parameters out nocopy dbms_sql.varchar2_table) is
3895: --
3896: l_bt_string varchar2(4000);
3897: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
3898: l_ds_parameters dbms_sql.varchar2_table;
3899: l_ds_tables dbms_sql.varchar2_table;
3900: l_flag boolean;
3901: l_bt_flag dbms_sql.varchar2_table;
3901: l_bt_flag dbms_sql.varchar2_table;
3902: Begin
3903: l_bt_string:=bsc_aw_utility.make_string_from_list(bsc_aw_utility.order_array(p_b_tables));
3904: --
3905: bsc_aw_md_api.get_bsc_olap_object(p_load_program,'dml program',p_kpi,'kpi',l_oo);
3906: bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_oo(1).property1,'DS',','),'+',l_ds_parameters);
3907: for i in 1..l_ds_parameters.count loop
3908: l_ds_parameters(i):=replace(l_ds_parameters(i),'^',',');
3909: end loop;
4005: p_bt_current_period dbms_sql.varchar2_table,
4006: p_options varchar2
4007: ) is
4008: --
4009: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
4010: l_cube_measures bsc_aw_adapter_kpi.measure_tb;
4011: l_bt_periodicity dbms_sql.number_table;
4012: l_start_period dbms_sql.varchar2_table;
4013: l_end_period dbms_sql.varchar2_table;