[Home] [Help]
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,
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
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
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
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;