[Home] [Help]
156: --l_base_table_list contains base tables that belong to the kpi from "p_base_table_list"
157: get_dimset_for_base_table(p_kpi,l_base_table_list,l_dim_set);
158: else --loading kpi
159: --!!! l_oo_dimset contains all the dimset, actuals and targets
160: bsc_aw_md_api.get_kpi_dimset(p_kpi,l_oo_dimset);
161: for i in 1..l_oo_dimset.count loop
162: l_dim_set(l_dim_set.count+1):=l_oo_dimset(i).object;
163: end loop;
164: end if;
392: Begin
393: --
394: l_olap_object_relation.delete;
395: --get the base tables l_olap_object_relation(i).object are the base tables
396: bsc_aw_md_api.get_dimset_base_table(p_kpi,p_dim_set,'base table dim set',l_olap_object_relation);
397: --if base tables are specified, filter out the un-needed dimsets
398: --there must be atleast one base table belonging to any dimset
399: if p_base_tables.count>0 then
400: for i in 1..l_olap_object_relation.count loop
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
560: in 10g, we can parallelize load of measures if they have diff composites
561: now, for 10g, we parallelize based on partitions
562: */
563: l_measures.delete;
564: bsc_aw_md_api.get_relation_object(l_dimset_oor,'dim set measure',l_measures);
565: parallel_flag:=can_launch_jobs(l_kpi,l_aggregation.dim_set(get_dim_set_index(l_aggregation,l_dimset)),l_measures);
566: if parallel_flag='Y' then
567: if l_pl_base_tables.count>0 then
568: /*check to see if there is sufficient data load to go for parallel load. otherwise, serial load is faster */
593: bsc_aw_utility.update_property(l_property,'current change vector',to_char(l_max_value(i)),',');
594: if l_bt_current_period(i) is not null and l_bt_current_period(i)<>'null' then
595: bsc_aw_utility.update_property(l_property,'base table current period',l_bt_current_period(i),',');
596: end if;
597: bsc_aw_md_api.update_olap_object_relation(p_olap_object_relation(i).object,p_olap_object_relation(i).object_type,
598: p_olap_object_relation(i).relation_type,p_olap_object_relation(i).parent_object,p_olap_object_relation(i).parent_object_type,
599: 'relation_object,relation_object_type',p_olap_object_relation(i).relation_object||','||p_olap_object_relation(i).relation_object_type,
600: 'property1',l_property);
601: end loop;
2764: /*
2765: the algo:
2766: start from the lowest level. limit level name dim to all parent.child and recursively go up
2767: as long as the level is a part of the dim
2768: now, in bsc_aw_md_api, when we load the parent child relation for both dim and calendar,
2769: we only bring in the parent child that belong to the kpi
2770: */
2771: procedure limit_dim_levels(p_dim bsc_aw_adapter_kpi.dim_r) is
2772: Begin
2989: p_aggregation:=null;
2990: p_aggregation.kpi:=p_kpi;
2991: l_cache_aggregation_r:=get_cache_aggregation_r(p_kpi);
2992: if l_cache_aggregation_r.kpi is null then
2993: bsc_aw_md_api.get_aggregation_r(p_aggregation);
2994: get_forecast_current_period(p_aggregation);
2995: dmp_aggregation_r(p_aggregation);
2996: g_cache_aggregation_r(g_cache_aggregation_r.count+1):=p_aggregation;
2997: else
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);
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;
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',',');
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',',');
3157: bsc_aw_md_api.update_olap_object_relation(l_olap_object_relation(i).object,l_olap_object_relation(i).object_type,
3158: l_olap_object_relation(i).relation_type,l_olap_object_relation(i).parent_object,l_olap_object_relation(i).parent_object_type,
3159: 'relation_object,relation_object_type',l_olap_object_relation(i).relation_object||','||l_olap_object_relation(i).relation_object_type,
3160: 'property1',l_property);
3161: end loop;
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;
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
3502: l_levels dbms_sql.varchar2_table;
3503: l_values bsc_aw_utility.value_tb;
3504: l_count number;
3505: Begin
3506: bsc_aw_md_api.get_kpi_dimset_actual(p_kpi,l_oo_dimset);
3507: --loop across the dimset
3508: for i in 1..l_oo_dimset.count loop
3509: l_dimset:=bsc_aw_utility.get_parameter_value(l_oo_dimset(i).property1,'dim set',',');
3510: l_dimset_name:=l_oo_dimset(i).object;
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
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
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);
3654: for j in 1..l_oor_dimset.count loop
3655: if bsc_aw_utility.in_array(p_dim_set,l_oor_dimset(j).relation_object)=false then
3656: p_dim_set(p_dim_set.count+1):=l_oor_dimset(j).relation_object;
3657: end if;
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);
3673: for j in 1..l_oor.count loop
3674: if p_base_table_list.count=0 or bsc_aw_utility.in_array(p_base_table_list,l_oor(j).object) then
3675: bsc_aw_utility.merge_value(p_dimset_base_tables,l_oor(j).object);
3676: end if;
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
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;
4040: log('Options '||p_options);
4041: log('--');
4042: end if;
4043: /*we do not have base table info in p_dim_set. so we have to go back to olap metadata */
4044: bsc_aw_md_api.get_dimset_base_table(p_kpi,p_dim_set.dim_set_name,'base table dim set',l_oor);
4045: /*load base table structures */
4046: for i in 1..p_base_tables.count loop
4047: l_base_tables(i).table_name:=p_base_tables(i);
4048: if p_bt_current_period(i) is not null and p_bt_current_period(i)<>'null' then
4245: change_vector dbms_sql.number_table;
4246: Begin
4247: if p_base_tables.count>0 then
4248: for i in 1..p_base_tables.count loop
4249: change_vector(i):=bsc_aw_md_api.get_bt_change_vector(p_base_tables(i));
4250: end loop;
4251: return is_parallel_load(p_base_tables,change_vector,p_cutoff);
4252: else
4253: if g_debug then