[Home] [Help]
489: exit;
490: end if;
491: end loop;
492: if l_aggregate_marker_program is not null then
493: bsc_aw_dbms_aw.execute('call '||l_aggregate_marker_program);
494: end if;
495: --
496: --for each dim set, for each base table, see if inc or full. then
497: --if there is initial for any of the baestables for the dimset, full refresh for the dim ste
677: log('Before Load, Composite Counts');
678: dmp_dimset_composite_count(l_dim_set);
679: end if;
680: for i in 1..l_ds_parameters.count loop
681: bsc_aw_dbms_aw.execute('call '||p_load_program||'('''||l_ds_parameters(i)||''')');
682: end loop;
683: if g_debug then
684: log('After Load, Composite Counts');
685: dmp_dimset_composite_count(l_dim_set);
992: dmp_dimset_composite_count(l_dim_set);
993: end if;
994: for i in 1..l_ds_parameters.count loop
995: --p_parameter contains cube and partition info
996: bsc_aw_dbms_aw.execute('call '||p_load_program||'('''||l_ds_parameters(i)||''','''||p_parameter||''')');
997: end loop;
998: if g_debug then
999: log('After Load, Composite Counts');
1000: dmp_dimset_composite_count(l_dim_set);
1008: --p_LB_resync_program is very important when we have partitions. without them, the LB will lose their information when we acquire the lock
1009: --in resync mode
1010: if l_end_lock_objects.count>0 then
1011: if p_LB_resync_program <> 'null' then
1012: bsc_aw_dbms_aw.execute('call '||p_LB_resync_program||'(''PRE'')');
1013: end if;
1014: bsc_aw_management.get_lock(l_end_lock_objects,'resync,wait type=active wait');
1015: if p_LB_resync_program <> 'null' then
1016: bsc_aw_dbms_aw.execute('call '||p_LB_resync_program||'(''POST'')');
1012: bsc_aw_dbms_aw.execute('call '||p_LB_resync_program||'(''PRE'')');
1013: end if;
1014: bsc_aw_management.get_lock(l_end_lock_objects,'resync,wait type=active wait');
1015: if p_LB_resync_program <> 'null' then
1016: bsc_aw_dbms_aw.execute('call '||p_LB_resync_program||'(''POST'')');
1017: end if;
1018: end if;
1019: --
1020: --save the cubes back to database. also releases the lock
1865: g_stmt:=p_actual_cube||'=if '||p_target_cube||' EQ NA then '||p_actual_cube||' else '||p_target_cube;
1866: if p_composite is not null then
1867: g_stmt:=g_stmt||' across '||p_composite;
1868: end if;
1869: bsc_aw_dbms_aw.execute(g_stmt);
1870: Exception when others then
1871: log_n('Exception in copy_target_to_actual '||sqlerrm);
1872: raise;
1873: End;
2203: log('Before Aggregate');
2204: dmp_dimset_dim_statlen(p_dim_set);
2205: dmp_dimset_composite_count(p_dim_set);
2206: end if;
2207: bsc_aw_dbms_aw.execute(l_agg_stmt);
2208: if bsc_aw_utility.get_parameter_value(p_aggregate_options,'onlytime',',')='Y' then
2209: pop_dim(p_dim_set.dim);
2210: end if;
2211: --if this is balance measures, then aggregate on time
2360: for i in 1..p_dim_set.dim.count loop
2361: if is_aggregation_on_dim(p_dim_set.dim(i)) then
2362: if p_dim_set.dim(i).levels.count=1 and p_dim_set.dim(i).recursive='N' then
2363: push_dim(p_dim_set.dim(i).dim_name);
2364: bsc_aw_dbms_aw.execute('limit '||p_dim_set.dim(i).levels(1).level_name||' to ''0''');
2365: limit_dim(p_dim_set.dim(i).dim_name,p_dim_set.dim(i).levels(1).level_name,'TO');
2366: else
2367: --remove the lowest level
2368: push_dim(p_dim_set.dim(i).dim_name);
2386: if g_debug then
2387: dmp_dimset_dim_statlen(p_dim_set);
2388: dmp_dimset_composite_count(p_dim_set);
2389: end if;
2390: bsc_aw_dbms_aw.execute(l_agg_stmt);
2391: if g_debug then
2392: dmp_dimset_composite_count(p_dim_set);
2393: end if;
2394: end loop;
2419: if g_debug then
2420: dmp_dimset_dim_statlen(p_dim_set);
2421: dmp_dimset_composite_count(p_dim_set);
2422: end if;
2423: bsc_aw_dbms_aw.execute(l_agg_stmt);
2424: if g_debug then
2425: dmp_dimset_composite_count(p_dim_set);
2426: end if;
2427: end loop;
2481: push_dim(p_dim_set.dim);
2482: push_dim(p_dim_set.calendar.aw_dim);
2483: limit_dim_ancestors(p_dim_set.dim,'ADD');
2484: g_stmt:='limit '||get_projection_dim(p_dim_set)||' to ''Y''';
2485: bsc_aw_dbms_aw.execute(g_stmt);
2486: g_stmt:='limit '||p_dim_set.calendar.aw_dim||' to null';
2487: bsc_aw_dbms_aw.execute(g_stmt);
2488: l_partition_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition',',');
2489: l_partition_dim_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition dim value',',');
2483: limit_dim_ancestors(p_dim_set.dim,'ADD');
2484: g_stmt:='limit '||get_projection_dim(p_dim_set)||' to ''Y''';
2485: bsc_aw_dbms_aw.execute(g_stmt);
2486: g_stmt:='limit '||p_dim_set.calendar.aw_dim||' to null';
2487: bsc_aw_dbms_aw.execute(g_stmt);
2488: l_partition_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition',',');
2489: l_partition_dim_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition dim value',',');
2490: if l_partition_value is not null then
2491: push_dim(p_dim_set.partition_dim);
2498: --if p_dim_set.calendar.periodicity(i).lowest_level='N' then
2499: if p_dim_set.calendar.periodicity(i).aggregated='Y' then
2500: l_period:=bsc_aw_utility.get_parameter_value(p_dim_set.calendar.periodicity(i).property,'current period',',');
2501: g_stmt:='limit '||p_dim_set.calendar.periodicity(i).aw_dim||' to '''||l_period||'''';
2502: bsc_aw_dbms_aw.execute(g_stmt);
2503: g_stmt:='limit '||p_dim_set.calendar.aw_dim||' add '||p_dim_set.calendar.periodicity(i).aw_dim;
2504: bsc_aw_dbms_aw.execute(g_stmt);
2505: end if;
2506: end loop;
2500: l_period:=bsc_aw_utility.get_parameter_value(p_dim_set.calendar.periodicity(i).property,'current period',',');
2501: g_stmt:='limit '||p_dim_set.calendar.periodicity(i).aw_dim||' to '''||l_period||'''';
2502: bsc_aw_dbms_aw.execute(g_stmt);
2503: g_stmt:='limit '||p_dim_set.calendar.aw_dim||' add '||p_dim_set.calendar.periodicity(i).aw_dim;
2504: bsc_aw_dbms_aw.execute(g_stmt);
2505: end if;
2506: end loop;
2507: /*
2508: when we had =0, aw gave error that we cannot change the values when looping across PT.
2537: --if l_pt_comp is not null and l_pt_comp_type='composite' then
2538: if l_pt_comp is not null then --for both composite and PT(10.2)(composite values are not cleared)
2539: g_stmt:=g_stmt||' across '||l_pt_comp;
2540: end if;
2541: bsc_aw_dbms_aw.execute(g_stmt);
2542: end loop;
2543: pop_dim(get_projection_dim(p_dim_set));
2544: pop_dim(p_dim_set.dim);
2545: pop_dim(p_dim_set.calendar.aw_dim);
2607: end if;
2608: push_dim(p_calendar.aw_dim);
2609: push_dim(p_calendar.end_period_level_name_dim);
2610: g_stmt:='limit '||p_calendar.aw_dim||' to null';
2611: bsc_aw_dbms_aw.execute(g_stmt);
2612: --
2613: for i in 1..p_calendar.periodicity.count loop
2614: if p_calendar.periodicity(i).lowest_level='Y' then
2615: l_period_lowest:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(i).property,'current period',',');
2613: for i in 1..p_calendar.periodicity.count loop
2614: if p_calendar.periodicity(i).lowest_level='Y' then
2615: l_period_lowest:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(i).property,'current period',',');
2616: g_stmt:='limit '||p_calendar.end_period_level_name_dim||' TO '''||p_calendar.periodicity(i).aw_dim||'''';
2617: bsc_aw_dbms_aw.execute(g_stmt);
2618: --for all upper levels, back up original relation value
2619: for j in 1..p_calendar.periodicity.count loop
2620: if p_calendar.periodicity(j).lowest_level <> 'Y' then
2621: l_period:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(j).property,'current period',',');
2619: for j in 1..p_calendar.periodicity.count loop
2620: if p_calendar.periodicity(j).lowest_level <> 'Y' then
2621: l_period:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(j).property,'current period',',');
2622: g_stmt:='limit '||p_calendar.periodicity(j).aw_dim||' to '''||l_period||'''';
2623: bsc_aw_dbms_aw.execute(g_stmt);
2624: g_stmt:='limit '||p_calendar.aw_dim||' to '||p_calendar.periodicity(j).aw_dim;
2625: bsc_aw_dbms_aw.execute(g_stmt);
2626: --set the rel.end_period relation
2627: --save the value for use in reset_calendar_end_period_rel
2621: l_period:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(j).property,'current period',',');
2622: g_stmt:='limit '||p_calendar.periodicity(j).aw_dim||' to '''||l_period||'''';
2623: bsc_aw_dbms_aw.execute(g_stmt);
2624: g_stmt:='limit '||p_calendar.aw_dim||' to '||p_calendar.periodicity(j).aw_dim;
2625: bsc_aw_dbms_aw.execute(g_stmt);
2626: --set the rel.end_period relation
2627: --save the value for use in reset_calendar_end_period_rel
2628: g_stmt:=p_calendar.end_period_relation_name||'.temp = '||p_calendar.end_period_relation_name;
2629: bsc_aw_dbms_aw.execute(g_stmt);
2625: bsc_aw_dbms_aw.execute(g_stmt);
2626: --set the rel.end_period relation
2627: --save the value for use in reset_calendar_end_period_rel
2628: g_stmt:=p_calendar.end_period_relation_name||'.temp = '||p_calendar.end_period_relation_name;
2629: bsc_aw_dbms_aw.execute(g_stmt);
2630: --we have to be careful here. imagine there is a hier like this
2631: -- M >- Q >- S >- Y
2632: -- W >- BiW >- Y
2633: --if we simply put the value of l_period_lowest for all upper levels, we may put month value into BiW. this is wrong.
2633: --if we simply put the value of l_period_lowest for all upper levels, we may put month value into BiW. this is wrong.
2634: --month does not rollup to BiW.
2635: g_stmt:=p_calendar.end_period_relation_name||'= if '||p_calendar.end_period_relation_name||' EQ NA then NA else '||
2636: p_calendar.aw_dim||'('||p_calendar.periodicity(i).aw_dim||' '''||l_period_lowest||''')';
2637: bsc_aw_dbms_aw.execute(g_stmt);
2638: end if;
2639: end loop;
2640: end if;
2641: end loop;
2668: push_dim(p_calendar.aw_dim);
2669: push_dim(p_calendar.end_period_level_name_dim);
2670: --we limit level name dim to the lowest level of the kpi dimset
2671: g_stmt:='limit '||p_calendar.aw_dim||' to null';
2672: bsc_aw_dbms_aw.execute(g_stmt);
2673: for i in 1..p_calendar.periodicity.count loop
2674: if p_calendar.periodicity(i).lowest_level='Y' then
2675: g_stmt:='limit '||p_calendar.end_period_level_name_dim||' to '''||p_calendar.periodicity(i).aw_dim||'''';
2676: bsc_aw_dbms_aw.execute(g_stmt);
2672: bsc_aw_dbms_aw.execute(g_stmt);
2673: for i in 1..p_calendar.periodicity.count loop
2674: if p_calendar.periodicity(i).lowest_level='Y' then
2675: g_stmt:='limit '||p_calendar.end_period_level_name_dim||' to '''||p_calendar.periodicity(i).aw_dim||'''';
2676: bsc_aw_dbms_aw.execute(g_stmt);
2677: for j in 1..p_calendar.periodicity.count loop
2678: if p_calendar.periodicity(j).lowest_level <> 'Y' then
2679: l_period:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(j).property,'current period',',');
2680: g_stmt:='limit '||p_calendar.periodicity(j).aw_dim||' to '''||l_period||'''';
2677: for j in 1..p_calendar.periodicity.count loop
2678: if p_calendar.periodicity(j).lowest_level <> 'Y' then
2679: l_period:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(j).property,'current period',',');
2680: g_stmt:='limit '||p_calendar.periodicity(j).aw_dim||' to '''||l_period||'''';
2681: bsc_aw_dbms_aw.execute(g_stmt);
2682: g_stmt:='limit '||p_calendar.aw_dim||' to '||p_calendar.periodicity(j).aw_dim;
2683: bsc_aw_dbms_aw.execute(g_stmt);
2684: g_stmt:=p_calendar.end_period_relation_name||'='||p_calendar.end_period_relation_name||'.temp';
2685: bsc_aw_dbms_aw.execute(g_stmt);
2679: l_period:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(j).property,'current period',',');
2680: g_stmt:='limit '||p_calendar.periodicity(j).aw_dim||' to '''||l_period||'''';
2681: bsc_aw_dbms_aw.execute(g_stmt);
2682: g_stmt:='limit '||p_calendar.aw_dim||' to '||p_calendar.periodicity(j).aw_dim;
2683: bsc_aw_dbms_aw.execute(g_stmt);
2684: g_stmt:=p_calendar.end_period_relation_name||'='||p_calendar.end_period_relation_name||'.temp';
2685: bsc_aw_dbms_aw.execute(g_stmt);
2686: end if;
2687: end loop;
2681: bsc_aw_dbms_aw.execute(g_stmt);
2682: g_stmt:='limit '||p_calendar.aw_dim||' to '||p_calendar.periodicity(j).aw_dim;
2683: bsc_aw_dbms_aw.execute(g_stmt);
2684: g_stmt:=p_calendar.end_period_relation_name||'='||p_calendar.end_period_relation_name||'.temp';
2685: bsc_aw_dbms_aw.execute(g_stmt);
2686: end if;
2687: end loop;
2688: end if;
2689: end loop;
2701: --limit a given dim to a given value
2702: procedure limit_dim(p_dim varchar2,p_value varchar2,p_mode varchar2) is
2703: Begin
2704: g_stmt:='limit '||p_dim||' '||p_mode||' '||p_value;
2705: bsc_aw_dbms_aw.execute(g_stmt);
2706: Exception when others then
2707: log_n('Exception in limit_dim '||sqlerrm);
2708: raise;
2709: End;
2725: procedure limit_dim_values(p_dim bsc_aw_adapter_kpi.dim_tb,p_mode varchar2) is
2726: Begin
2727: for i in 1..p_dim.count loop
2728: g_stmt:='limit '||p_dim(i).dim_name||' '||p_mode||' '||p_dim(i).limit_cube;
2729: bsc_aw_dbms_aw.execute(g_stmt);
2730: end loop;
2731: Exception when others then
2732: log_n('Exception in limit_dim_values '||sqlerrm);
2733: raise;
2771: procedure limit_dim_levels(p_dim bsc_aw_adapter_kpi.dim_r) is
2772: Begin
2773: if p_dim.recursive='N' then
2774: g_stmt:='limit '||p_dim.level_name_dim||' to null';
2775: bsc_aw_dbms_aw.execute(g_stmt);
2776: --we start the process with the lowest level. this procedure is called rec for the parents
2777: limit_dim_levels(p_dim,p_dim.levels(1).level_name);
2778: else --now, set the levels for the rec dim
2779: --p_dim.level_name_dim||'.position is the variable with the position for each rec dim value, larry=1, john wookey=2
2778: else --now, set the levels for the rec dim
2779: --p_dim.level_name_dim||'.position is the variable with the position for each rec dim value, larry=1, john wookey=2
2780: /*this api is only called when the rec dim is implemented in denorm fashion */
2781: g_stmt:='limit '||p_dim.level_name_dim||' to '||p_dim.level_name_dim||'.position LE '||p_dim.agg_level;
2782: bsc_aw_dbms_aw.execute(g_stmt);
2783: end if;
2784: Exception when others then
2785: log_n('Exception in limit_dim_levels '||sqlerrm);
2786: raise;
2801: if l_this_level.aggregated='Y' then
2802: if l_this_level.zero_code='Y' and l_this_level.zero_aggregated='Y' then
2803: g_stmt:='limit '||p_dim.level_name_dim||' add '''||l_this_level.zero_code_level||'.'||
2804: l_this_level.level_name||'''';
2805: bsc_aw_dbms_aw.execute(g_stmt);
2806: end if;
2807: end if;
2808: for i in 1..p_dim.parent_child.count loop
2809: l_flag(i):=false;
2812: if l_parent_level.aggregated='Y' then
2813: l_flag(i):=true;
2814: g_stmt:='limit '||p_dim.level_name_dim||' add '''||p_dim.parent_child(i).parent_level||'.'||
2815: p_dim.parent_child(i).child_level||'''';
2816: bsc_aw_dbms_aw.execute(g_stmt);
2817: end if;
2818: end if;
2819: end loop;
2820: --rec do this for the parent levels
2841: --we would have limited the level_name_dim before.
2842: for i in 1..p_dim.count loop
2843: if p_dim(i).relation_name is not null then
2844: g_stmt:='limit '||p_dim(i).dim_name||' '||p_operator||' ancestors using '||p_dim(i).relation_name;
2845: bsc_aw_dbms_aw.execute(g_stmt);
2846: end if;
2847: end loop;
2848: Exception when others then
2849: log_n('Exception in limit_dim_ancestors '||sqlerrm);
2854: Begin
2855: for i in 1..p_dim.count loop
2856: if p_dim(i).relation_name is not null then
2857: g_stmt:='limit '||p_dim(i).dim_name||' '||p_operator||' '||p_depth||' using '||p_dim(i).relation_name;
2858: bsc_aw_dbms_aw.execute(g_stmt);
2859: end if;
2860: end loop;
2861: Exception when others then
2862: log_n('Exception in limit_dim_descendents '||sqlerrm);
2873: g_stmt:=p_dim(i).limit_cube||'=FALSE';
2874: if p_dim(i).limit_cube_composite is not null then
2875: g_stmt:=g_stmt||' across '||p_dim(i).limit_cube_composite;
2876: end if;
2877: bsc_aw_dbms_aw.execute(g_stmt);
2878: end loop;
2879: Exception when others then
2880: log_n('Exception in reset_dim_limit_cubes '||sqlerrm);
2881: raise;
2886: */
2887: procedure limit_calendar_values(p_calendar bsc_aw_adapter_kpi.calendar_r,p_mode varchar2) is
2888: Begin
2889: g_stmt:='limit '||p_calendar.aw_dim||' '||p_mode||' '||p_calendar.limit_cube;
2890: bsc_aw_dbms_aw.execute(g_stmt);
2891: Exception when others then
2892: log_n('Exception in limit_calendar_values '||sqlerrm);
2893: raise;
2894: End;
2898: */
2899: procedure limit_calendar_levels(p_calendar bsc_aw_adapter_kpi.calendar_r) is
2900: Begin
2901: g_stmt:='limit '||p_calendar.level_name_dim||' to null';
2902: bsc_aw_dbms_aw.execute(g_stmt);
2903: --start the process. this procedure is called rec for parent levels
2904: --start from the child and go all the way up
2905: for i in 1..p_calendar.periodicity.count loop
2906: if p_calendar.periodicity(i).lowest_level='Y' then
2931: if l_periodicity.aggregated='Y' then
2932: l_flag(i):=true;
2933: g_stmt:='limit '||p_calendar.level_name_dim||' add '''||p_calendar.parent_child(i).parent_dim_name||'.'||
2934: p_calendar.parent_child(i).child_dim_name||'''';
2935: bsc_aw_dbms_aw.execute(g_stmt);
2936: end if;
2937: end if;
2938: end loop;
2939: for i in 1..p_calendar.parent_child.count loop
2951: --p_operator is "to" or "add"
2952: procedure limit_calendar_ancestors(p_calendar bsc_aw_adapter_kpi.calendar_r,p_operator varchar2) is
2953: Begin
2954: g_stmt:='limit '||p_calendar.aw_dim||' '||p_operator||' ancestors using '||p_calendar.relation_name;
2955: bsc_aw_dbms_aw.execute(g_stmt);
2956: Exception when others then
2957: log_n('Exception in limit_calendar_ancestors '||sqlerrm);
2958: raise;
2959: End;
2960:
2961: procedure limit_calendar_descendents(p_calendar bsc_aw_adapter_kpi.calendar_r,p_operator varchar2,p_depth varchar2) is
2962: Begin
2963: g_stmt:='limit '||p_calendar.aw_dim||' '||p_operator||' '||p_depth||' using '||p_calendar.relation_name;
2964: bsc_aw_dbms_aw.execute(g_stmt);
2965: Exception when others then
2966: log_n('Exception in limit_calendar_descendents '||sqlerrm);
2967: raise;
2968: End;
2976: g_stmt:=p_calendar.limit_cube||'=FALSE';
2977: if p_calendar.limit_cube_composite is not null then
2978: g_stmt:=g_stmt||' across '||p_calendar.limit_cube_composite;
2979: end if;
2980: bsc_aw_dbms_aw.execute(g_stmt);
2981: Exception when others then
2982: log_n('Exception in reset_calendar_limit_cubes '||sqlerrm);
2983: raise;
2984: End;
3024: End;
3025:
3026: procedure push_dim(p_dim varchar2) is
3027: Begin
3028: bsc_aw_dbms_aw.execute('push '||p_dim);
3029: Exception when others then
3030: log_n('Exception in push_dim '||sqlerrm);
3031: raise;
3032: End;
3042: End;
3043:
3044: procedure pop_dim(p_dim varchar2) is
3045: Begin
3046: bsc_aw_dbms_aw.execute('pop '||p_dim);
3047: Exception when others then
3048: log_n('Exception in pop_dim '||sqlerrm);
3049: raise;
3050: End;
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
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
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
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
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);
3170: p_cubes dbms_sql.varchar2_table,
3171: p_partition_value varchar2
3172: ) is
3173: Begin
3174: bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' to NULL');
3175: for i in 1..p_cubes.count loop
3176: if p_partition_value is not null then
3177: bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' add '''||p_cubes(i)||' (PARTITION '||p_partition_value||')''');
3178: else
3173: Begin
3174: bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' to NULL');
3175: for i in 1..p_cubes.count loop
3176: if p_partition_value is not null then
3177: bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' add '''||p_cubes(i)||' (PARTITION '||p_partition_value||')''');
3178: else
3179: bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' add '''||p_cubes(i)||'''');
3180: end if;
3181: end loop;
3175: for i in 1..p_cubes.count loop
3176: if p_partition_value is not null then
3177: bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' add '''||p_cubes(i)||' (PARTITION '||p_partition_value||')''');
3178: else
3179: bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' add '''||p_cubes(i)||'''');
3180: end if;
3181: end loop;
3182: Exception when others then
3183: log_n('Exception in limit_measure_dim '||sqlerrm);
3189: --now, we reset the limit cubes
3190: --in 10g, we call these 2 api to after all the threads have completed
3191: --set status to allstat in reset_dim_limits. in 10g parallelism, the dim status here is not what we need.
3192: --so best to set the status to allstat
3193: bsc_aw_dbms_aw.execute('allstat');
3194: reset_dim_limit_cubes(p_dim_set.dim);
3195: reset_dim_limit_cubes(p_dim_set.std_dim);
3196: reset_calendar_limit_cubes(p_dim_set.calendar);
3197: Exception when others then
3294: g_stmt:=p_limit_cube||'='||p_value;
3295: if p_composite_name is not null then
3296: g_stmt:=g_stmt||' across '||p_composite_name;
3297: end if;
3298: bsc_aw_dbms_aw.execute(g_stmt);
3299: Exception when others then
3300: log_n('Exception in limit_dim_limit_cube '||sqlerrm);
3301: raise;
3302: End;
3472: --
3473: l_name varchar2(200);
3474: Begin
3475: l_name:='dmp_kpi_'||p_kpi||'_'||p_dimset;
3476: bsc_aw_dbms_aw.execute_ne('delete '||l_name);
3477: bsc_aw_adapter_kpi.create_dmp_program(p_kpi,p_dimset,p_dim_levels,l_name,p_table_name);
3478: bsc_aw_dbms_aw.execute('call '||l_name);
3479: bsc_aw_dbms_aw.execute('delete '||l_name);
3480: Exception when others then
3474: Begin
3475: l_name:='dmp_kpi_'||p_kpi||'_'||p_dimset;
3476: bsc_aw_dbms_aw.execute_ne('delete '||l_name);
3477: bsc_aw_adapter_kpi.create_dmp_program(p_kpi,p_dimset,p_dim_levels,l_name,p_table_name);
3478: bsc_aw_dbms_aw.execute('call '||l_name);
3479: bsc_aw_dbms_aw.execute('delete '||l_name);
3480: Exception when others then
3481: log_n('Exception in dmp_kpi_cubes_into_table '||sqlerrm);
3482: raise;
3475: l_name:='dmp_kpi_'||p_kpi||'_'||p_dimset;
3476: bsc_aw_dbms_aw.execute_ne('delete '||l_name);
3477: bsc_aw_adapter_kpi.create_dmp_program(p_kpi,p_dimset,p_dim_levels,l_name,p_table_name);
3478: bsc_aw_dbms_aw.execute('call '||l_name);
3479: bsc_aw_dbms_aw.execute('delete '||l_name);
3480: Exception when others then
3481: log_n('Exception in dmp_kpi_cubes_into_table '||sqlerrm);
3482: raise;
3483: End;
3869: procedure dmp_dimset_dim_statlen(p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
3870: l_val varchar2(2000);
3871: Begin
3872: for i in 1..p_dim_set.dim.count loop
3873: l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.dim(i).dim_name||')');
3874: end loop;
3875: for i in 1..p_dim_set.std_dim.count loop
3876: l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.std_dim(i).dim_name||')');
3877: end loop;
3872: for i in 1..p_dim_set.dim.count loop
3873: l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.dim(i).dim_name||')');
3874: end loop;
3875: for i in 1..p_dim_set.std_dim.count loop
3876: l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.std_dim(i).dim_name||')');
3877: end loop;
3878: l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.calendar.aw_dim||')');
3879: Exception when others then
3880: log_n('Exception in dmp_dimset_dim_statlen '||sqlerrm);
3874: end loop;
3875: for i in 1..p_dim_set.std_dim.count loop
3876: l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.std_dim(i).dim_name||')');
3877: end loop;
3878: l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.calendar.aw_dim||')');
3879: Exception when others then
3880: log_n('Exception in dmp_dimset_dim_statlen '||sqlerrm);
3881: raise;
3882: End;
4153: /*set the projections */
4154: if l_prj_measures.count>0 then
4155: limit_dim(p_dim_set.calendar.aw_dim,'NULL','TO');
4156: for i in 1..l_bt_periodicity.count loop
4157: bsc_aw_dbms_aw.execute('limit '||l_bt_periodicity(i).aw_dim||' TO '''||p_start_period(i)||''' TO '''||p_end_period(i)||'''');
4158: limit_dim(p_dim_set.calendar.aw_dim,l_bt_periodicity(i).aw_dim,'ADD');
4159: end loop;
4160: limit_calendar_ancestors(p_dim_set.calendar,'ADD');
4161: bsc_aw_dbms_aw.execute('limit '||get_projection_dim(p_dim_set)||' to ''Y''');
4157: bsc_aw_dbms_aw.execute('limit '||l_bt_periodicity(i).aw_dim||' TO '''||p_start_period(i)||''' TO '''||p_end_period(i)||'''');
4158: limit_dim(p_dim_set.calendar.aw_dim,l_bt_periodicity(i).aw_dim,'ADD');
4159: end loop;
4160: limit_calendar_ancestors(p_dim_set.calendar,'ADD');
4161: bsc_aw_dbms_aw.execute('limit '||get_projection_dim(p_dim_set)||' to ''Y''');
4162: /*when we are here, there cannot be compressed composite.CC cannot be when there are projections or balance */
4163: limit_dim(p_dim_set.measurename_dim,'NULL','TO');
4164: for i in 1..l_prj_measures.count loop
4165: limit_dim(p_dim_set.measurename_dim,''''||l_prj_measures(i).measure||'''','ADD');
4170: l_stmt:=p_cube||'=NA';
4171: if l_pt_name is not null then
4172: l_stmt:=l_stmt||' across '||l_pt_name; /*both pt and composite */
4173: end if;
4174: bsc_aw_dbms_aw.execute(l_stmt);
4175: end if;
4176: /*set the balance */
4177: if l_bal_measures.count>0 then
4178: limit_dim(p_dim_set.calendar.aw_dim,'NULL','TO');
4176: /*set the balance */
4177: if l_bal_measures.count>0 then
4178: limit_dim(p_dim_set.calendar.aw_dim,'NULL','TO');
4179: for i in 1..l_bt_periodicity.count loop
4180: bsc_aw_dbms_aw.execute('limit '||l_bt_periodicity(i).aw_dim||' TO '''||p_start_period(i)||'''');
4181: limit_dim(p_dim_set.calendar.aw_dim,l_bt_periodicity(i).aw_dim,'ADD');
4182: end loop;
4183: limit_calendar_ancestors(p_dim_set.calendar,'ADD');
4184: /*for balance, we cannot limit projection dim to Y */
4192: l_stmt:=p_cube||'=NA';
4193: if l_pt_name is not null then
4194: l_stmt:=l_stmt||' across '||l_pt_name; /*both pt and composite */
4195: end if;
4196: bsc_aw_dbms_aw.execute(l_stmt);
4197: end if;
4198: /*restore dim status */
4199: if l_prj_measures.count>0 or l_bal_measures.count>0 then
4200: pop_dim(p_dim_set.calendar.aw_dim);
4231: procedure dmp_dimset_composite_count(p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
4232: l_val varchar2(2000);
4233: Begin
4234: for i in 1..p_dim_set.composite.count loop
4235: l_val:=bsc_aw_dbms_aw.interp('show obj(dimmax '''||p_dim_set.composite(i).composite_name||''')');
4236: end loop;
4237: Exception when others then
4238: log_n('Exception in dmp_dimset_composite_count '||sqlerrm);
4239: raise;
4374: comp_count number;
4375: Begin
4376: comp_count:=0;
4377: for i in 1..p_dim_set.composite.count loop
4378: l_val:=bsc_aw_dbms_aw.interp('show obj(dimmax '''||p_dim_set.composite(i).composite_name||''')');
4379: if bsc_aw_utility.is_number(l_val) then
4380: comp_count:=comp_count+to_number(l_val);
4381: else
4382: return -1;