[Home] [Help]
1: package body BSC_AW_ADAPTER_KPI AS
2: /*$Header: BSCAWAKB.pls 120.30 2006/05/20 02:10:19 vsurendr ship $*/
3:
4: procedure create_kpi(p_kpi_list dbms_sql.varchar2_table) is
5: l_kpi kpi_tb;
6: Begin
7: for i in 1..p_kpi_list.count loop
8: l_kpi(i).kpi:=p_kpi_list(i);
278: procedure get_dim_set_dims(
279: p_kpi varchar2,
280: p_dim_set in out nocopy dim_set_r) is
281: --
282: l_dim_level dbms_sql.varchar2_table;
283: l_mo_dim_group dbms_sql.varchar2_table; --used to see if a level is stand alone or part of parent child
284: l_skip_level dbms_sql.varchar2_table;
285: l_dim varchar2(300);
286: Begin
279: p_kpi varchar2,
280: p_dim_set in out nocopy dim_set_r) is
281: --
282: l_dim_level dbms_sql.varchar2_table;
283: l_mo_dim_group dbms_sql.varchar2_table; --used to see if a level is stand alone or part of parent child
284: l_skip_level dbms_sql.varchar2_table;
285: l_dim varchar2(300);
286: Begin
287: bsc_aw_bsc_metadata.get_dim_set_dims(p_kpi,p_dim_set.dim_set,l_dim_level,l_mo_dim_group,l_skip_level);
280: p_dim_set in out nocopy dim_set_r) is
281: --
282: l_dim_level dbms_sql.varchar2_table;
283: l_mo_dim_group dbms_sql.varchar2_table; --used to see if a level is stand alone or part of parent child
284: l_skip_level dbms_sql.varchar2_table;
285: l_dim varchar2(300);
286: Begin
287: bsc_aw_bsc_metadata.get_dim_set_dims(p_kpi,p_dim_set.dim_set,l_dim_level,l_mo_dim_group,l_skip_level);
288: for i in 1..l_dim_level.count loop
343: also, in the load program, we follow the same order. this api just rearranges the dim in this order
344: please note that time dim is handled differently in calendar in the dim set
345: */
346: procedure set_dim_order(p_dim_set in out nocopy dim_set_r) is
347: l_order dbms_sql.number_table;
348: l_rank number;
349: l_dim dim_tb;
350: Begin
351: for i in 1..p_dim_set.dim.count loop
560: procedure set_DS_dim_levels(p_kpi varchar2,p_dim_set in out nocopy dim_set_r,p_data_source in out nocopy data_source_r) is
561: l_base_table base_table_r;
562: l_dim dim_r;
563: l_level level_r;
564: l_ds_dim_set dbms_sql.varchar2_table;
565: Begin
566: for i in 1..p_data_source.base_tables.count loop
567: set_DS_dim_levels(p_kpi,p_dim_set,p_data_source,p_data_source.base_tables(i));
568: end loop;
600: */
601: procedure set_DS_dim_levels(p_kpi varchar2,p_dim_set dim_set_r,p_data_source data_source_r,p_base_table in out nocopy base_table_r) is
602: l_dim dim_r;
603: l_oo_dim varchar2(100);
604: l_ds_dim_set dbms_sql.varchar2_table;
605: Begin
606: for i in 1..p_base_table.levels.count loop
607: p_base_table.level_status(i):=null;
608: end loop;
671: p_dim_set dim_set_r,
672: p_data_source in out nocopy data_source_tb
673: ) is
674: --
675: l_distinct_levels dbms_sql.varchar2_table;
676: l_level_string dbms_sql.varchar2_table;
677: l_data_source data_source_tb;
678: l_new_data_source data_source_tb;
679: Begin
672: p_data_source in out nocopy data_source_tb
673: ) is
674: --
675: l_distinct_levels dbms_sql.varchar2_table;
676: l_level_string dbms_sql.varchar2_table;
677: l_data_source data_source_tb;
678: l_new_data_source data_source_tb;
679: Begin
680: if p_data_source.count=0 then
733: ) is
734: --
735: j integer;
736: l_measure_index bsc_aw_utility.number_table;
737: l_balance_loaded_column dbms_sql.varchar2_table;
738: Begin
739: p_new_data_source.dim:=p_data_source(1).dim;
740: p_new_data_source.std_dim:=p_data_source(1).std_dim;
741: p_new_data_source.calendar:=p_data_source(1).calendar;
1224: --
1225: l_dim varchar2(300);
1226: l_parent_child bsc_aw_adapter_dim.dim_parent_child_tb;
1227: l_pc_subset bsc_aw_adapter_dim.dim_parent_child_tb;
1228: l_from_sql dbms_sql.varchar2_table;
1229: l_where_sql dbms_sql.varchar2_table;
1230: l_group_sql dbms_sql.varchar2_table;
1231: l_base_cal_column varchar2(100); --lower periodicity column name from bsc_db_calendar
1232: l_dimset_cal_column varchar2(100);
1225: l_dim varchar2(300);
1226: l_parent_child bsc_aw_adapter_dim.dim_parent_child_tb;
1227: l_pc_subset bsc_aw_adapter_dim.dim_parent_child_tb;
1228: l_from_sql dbms_sql.varchar2_table;
1229: l_where_sql dbms_sql.varchar2_table;
1230: l_group_sql dbms_sql.varchar2_table;
1231: l_base_cal_column varchar2(100); --lower periodicity column name from bsc_db_calendar
1232: l_dimset_cal_column varchar2(100);
1233: l_remove_agg_flag boolean;
1226: l_parent_child bsc_aw_adapter_dim.dim_parent_child_tb;
1227: l_pc_subset bsc_aw_adapter_dim.dim_parent_child_tb;
1228: l_from_sql dbms_sql.varchar2_table;
1229: l_where_sql dbms_sql.varchar2_table;
1230: l_group_sql dbms_sql.varchar2_table;
1231: l_base_cal_column varchar2(100); --lower periodicity column name from bsc_db_calendar
1232: l_dimset_cal_column varchar2(100);
1233: l_remove_agg_flag boolean;
1234: l_formula varchar(4000);
1234: l_formula varchar(4000);
1235: l_flag boolean;
1236: --l_base_aw_join_flag is used only for inc load if the number of keys in the base > that in the dim set
1237: --keeps track of what keys to join between base and base_aw
1238: l_base_aw_join_flag dbms_sql.varchar2_table;
1239: l_alias varchar2(100);
1240: l_aw_table_fk_driver_only boolean;--if true the aw table is only a fk driver table
1241: l_balance_loaded_column varchar2(40);
1242: Begin
1519:
1520: procedure drop_kpi_objects_aw(p_kpi varchar2) is
1521: --
1522: l_objects bsc_aw_utility.object_tb; --object_t is object_name and object_type
1523: l_flag dbms_sql.varchar2_table;
1524: Begin
1525: bsc_aw_md_api.get_kpi_olap_objects(p_kpi,l_objects,'all');
1526: for i in 1..l_objects.count loop
1527: l_flag(i):='N';
1579: drop_kpi_objects_relational action is final. due to database commit, if there is a failure in between, we cannot guarantee
1580: the system. drop has to be rerun till successful
1581: */
1582: procedure drop_kpi_objects_relational(p_kpi varchar2) is
1583: l_flag dbms_sql.varchar2_table;
1584: l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
1585: Begin
1586: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_olap_object);
1587: for i in 1..l_olap_object.count loop
2173: log_n('Exception in create_measure_dim '||sqlerrm);
2174: raise;
2175: End;
2176:
2177: function get_comp_dimensions(p_dim_set in out nocopy dim_set_r) return dbms_sql.varchar2_table is
2178: --
2179: l_comp_dimensions dbms_sql.varchar2_table;
2180: Begin
2181: --first we have the dim on which are going to aggregate. confirmed with AW team. best to have the dim on which we are aggregating
2175: End;
2176:
2177: function get_comp_dimensions(p_dim_set in out nocopy dim_set_r) return dbms_sql.varchar2_table is
2178: --
2179: l_comp_dimensions dbms_sql.varchar2_table;
2180: Begin
2181: --first we have the dim on which are going to aggregate. confirmed with AW team. best to have the dim on which we are aggregating
2182: --ahead in the index. we will have time first
2183: l_comp_dimensions(l_comp_dimensions.count+1):=p_dim_set.calendar.aw_dim;
2217: */
2218: procedure create_composite(
2219: p_kpi kpi_r,
2220: p_dim_set in out nocopy dim_set_r) is
2221: l_comp_created dbms_sql.varchar2_table;
2222: l_comp_dimensions dbms_sql.varchar2_table;
2223: Begin
2224: l_comp_dimensions:=get_comp_dimensions(p_dim_set);
2225: --first create the composites
2218: procedure create_composite(
2219: p_kpi kpi_r,
2220: p_dim_set in out nocopy dim_set_r) is
2221: l_comp_created dbms_sql.varchar2_table;
2222: l_comp_dimensions dbms_sql.varchar2_table;
2223: Begin
2224: l_comp_dimensions:=get_comp_dimensions(p_dim_set);
2225: --first create the composites
2226: for i in 1..p_dim_set.composite.count loop
2264: p_dim_set in out nocopy dim_set_r,
2265: p_partition_template in out nocopy partition_template_r
2266: ) is
2267: --
2268: l_comp_dimensions dbms_sql.varchar2_table;
2269: Begin
2270: if p_partition_template.template_name is not null then
2271: l_comp_dimensions:=get_comp_dimensions(p_dim_set);
2272: p_partition_template.template_dimensions(1):=p_partition_template.template_dim;
2388: End;
2389:
2390: procedure create_cube(p_kpi kpi_r,p_dim_set dim_set_r,p_cube cube_r,p_cube_axis axis_tb) is
2391: l_stmt varchar2(4000);
2392: l_dimensions dbms_sql.varchar2_table;
2393: Begin
2394: l_stmt:='dfn '||p_cube.cube_name||' '||p_cube.cube_datatype||'<';
2395: for i in 1..p_cube_axis.count loop
2396: l_stmt:=l_stmt||' '||p_cube_axis(i).axis_name;
2687: l_stmt varchar(4000);
2688: --
2689: l_cube_set cube_set_r;
2690: l_filter varchar2(2000);
2691: l_ordered_b_tables dbms_sql.varchar2_table;
2692: l_balance_loaded_column varchar2(40);
2693: Begin
2694: for i in 1..p_data_source.base_tables.count loop
2695: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
2779: raise;
2780: End;
2781:
2782: procedure create_balance_aggregation(p_dim_set dim_set_r, p_data_source data_source_r,p_measures measure_tb) is
2783: l_upper_periodicity dbms_sql.varchar2_table;
2784: l_cube_copy_stmt dbms_sql.varchar2_table;
2785: l_limit_cube_copy_stmt dbms_sql.varchar2_table;
2786: l_stmt varchar2(8000);
2787: l_cube_set cube_set_r;
2780: End;
2781:
2782: procedure create_balance_aggregation(p_dim_set dim_set_r, p_data_source data_source_r,p_measures measure_tb) is
2783: l_upper_periodicity dbms_sql.varchar2_table;
2784: l_cube_copy_stmt dbms_sql.varchar2_table;
2785: l_limit_cube_copy_stmt dbms_sql.varchar2_table;
2786: l_stmt varchar2(8000);
2787: l_cube_set cube_set_r;
2788: --
2781:
2782: procedure create_balance_aggregation(p_dim_set dim_set_r, p_data_source data_source_r,p_measures measure_tb) is
2783: l_upper_periodicity dbms_sql.varchar2_table;
2784: l_cube_copy_stmt dbms_sql.varchar2_table;
2785: l_limit_cube_copy_stmt dbms_sql.varchar2_table;
2786: l_stmt varchar2(8000);
2787: l_cube_set cube_set_r;
2788: --
2789: l_balance_loaded_column varchar2(40);
3047: --
3048: l_data_source data_source_tb;
3049: l_pgm varchar2(300);
3050: l_cube_measures measure_tb;
3051: l_cube_considered dbms_sql.varchar2_table;
3052: Begin
3053: g_commands.delete;
3054: if p_mode='initial' then
3055: set_program_property(p_dim_set.initial_load_program_parallel,p_dim_set.data_source);
3098: p_data_source data_source_r) is
3099: --
3100: l_measure_index number;
3101: l_stmt varchar(4000);
3102: l_measures dbms_sql.varchar2_table;
3103: l_ordered_b_tables dbms_sql.varchar2_table;
3104: l_balance_loaded_column varchar2(40);
3105: Begin
3106: for i in 1..p_measures.count loop
3099: --
3100: l_measure_index number;
3101: l_stmt varchar(4000);
3102: l_measures dbms_sql.varchar2_table;
3103: l_ordered_b_tables dbms_sql.varchar2_table;
3104: l_balance_loaded_column varchar2(40);
3105: Begin
3106: for i in 1..p_measures.count loop
3107: l_measures(i):=p_measures(i).measure;
3321: l_data_source data_source_r;
3322: i integer;
3323: l_partition_stmt varchar2(4000);
3324: l_partition_type varchar2(40);
3325: l_cubes dbms_sql.varchar2_table;
3326: l_property_value varchar2(4000);
3327: Begin
3328: l_data_source:=p_data_source;
3329: i:=1;
3389: procedure create_kpi_program_limit_cube(
3390: p_kpi kpi_r,
3391: p_dim_set dim_set_r,
3392: p_data_source data_source_r) is
3393: l_ordered_b_tables dbms_sql.varchar2_table;
3394: Begin
3395: for i in 1..p_data_source.base_tables.count loop
3396: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
3397: end loop;
3497: */
3498: procedure create_kpi_view(p_kpi kpi_r,p_dim_set dim_set_r,p_s_view s_view_r,p_type varchar2) is
3499: PRAGMA AUTONOMOUS_TRANSACTION;
3500: --
3501: l_dimensions dbms_sql.varchar2_table;
3502: l_outer_measures dbms_sql.varchar2_table;
3503: l_outer_measure_agg_types dbms_sql.varchar2_table;
3504: l_outer_measure_aggregations dbms_sql.varchar2_table;/*holds the agg formula */
3505: l_inner_measures dbms_sql.varchar2_table;
3498: procedure create_kpi_view(p_kpi kpi_r,p_dim_set dim_set_r,p_s_view s_view_r,p_type varchar2) is
3499: PRAGMA AUTONOMOUS_TRANSACTION;
3500: --
3501: l_dimensions dbms_sql.varchar2_table;
3502: l_outer_measures dbms_sql.varchar2_table;
3503: l_outer_measure_agg_types dbms_sql.varchar2_table;
3504: l_outer_measure_aggregations dbms_sql.varchar2_table;/*holds the agg formula */
3505: l_inner_measures dbms_sql.varchar2_table;
3506: l_inner_select_columns dbms_sql.varchar2_table;
3499: PRAGMA AUTONOMOUS_TRANSACTION;
3500: --
3501: l_dimensions dbms_sql.varchar2_table;
3502: l_outer_measures dbms_sql.varchar2_table;
3503: l_outer_measure_agg_types dbms_sql.varchar2_table;
3504: l_outer_measure_aggregations dbms_sql.varchar2_table;/*holds the agg formula */
3505: l_inner_measures dbms_sql.varchar2_table;
3506: l_inner_select_columns dbms_sql.varchar2_table;
3507: l_inner_select_column_types dbms_sql.varchar2_table;/*fk or measure */
3500: --
3501: l_dimensions dbms_sql.varchar2_table;
3502: l_outer_measures dbms_sql.varchar2_table;
3503: l_outer_measure_agg_types dbms_sql.varchar2_table;
3504: l_outer_measure_aggregations dbms_sql.varchar2_table;/*holds the agg formula */
3505: l_inner_measures dbms_sql.varchar2_table;
3506: l_inner_select_columns dbms_sql.varchar2_table;
3507: l_inner_select_column_types dbms_sql.varchar2_table;/*fk or measure */
3508: l_name varchar2(30);
3501: l_dimensions dbms_sql.varchar2_table;
3502: l_outer_measures dbms_sql.varchar2_table;
3503: l_outer_measure_agg_types dbms_sql.varchar2_table;
3504: l_outer_measure_aggregations dbms_sql.varchar2_table;/*holds the agg formula */
3505: l_inner_measures dbms_sql.varchar2_table;
3506: l_inner_select_columns dbms_sql.varchar2_table;
3507: l_inner_select_column_types dbms_sql.varchar2_table;/*fk or measure */
3508: l_name varchar2(30);
3509: l_inner_stmt varchar2(32000);
3502: l_outer_measures dbms_sql.varchar2_table;
3503: l_outer_measure_agg_types dbms_sql.varchar2_table;
3504: l_outer_measure_aggregations dbms_sql.varchar2_table;/*holds the agg formula */
3505: l_inner_measures dbms_sql.varchar2_table;
3506: l_inner_select_columns dbms_sql.varchar2_table;
3507: l_inner_select_column_types dbms_sql.varchar2_table;/*fk or measure */
3508: l_name varchar2(30);
3509: l_inner_stmt varchar2(32000);
3510: l_sql_agg_stmt varchar2(32000);
3503: l_outer_measure_agg_types dbms_sql.varchar2_table;
3504: l_outer_measure_aggregations dbms_sql.varchar2_table;/*holds the agg formula */
3505: l_inner_measures dbms_sql.varchar2_table;
3506: l_inner_select_columns dbms_sql.varchar2_table;
3507: l_inner_select_column_types dbms_sql.varchar2_table;/*fk or measure */
3508: l_name varchar2(30);
3509: l_inner_stmt varchar2(32000);
3510: l_sql_agg_stmt varchar2(32000);
3511: sql_aggregation boolean;
4236: p_dim_set dim_set_r
4237: ) is
4238: --
4239: l_agg_formula varchar2(1000);
4240: l_start_array dbms_sql.number_table; --contains the start address of a measure in a formula
4241: l_cube varchar2(200);
4242: l_cube_set cube_set_r;
4243: Begin
4244: l_agg_formula:=p_measure.agg_formula.agg_formula;
4286: this is better for aw. sotherwise, when adv sum profile is dropped from 3 to 2, how do we reclaim space
4287: from the cubes? it gets complicated
4288: */
4289: procedure set_dim_agg_level(p_kpi in out nocopy kpi_r) is
4290: l_flag dbms_sql.varchar2_table;--to track if we do change the agg level in a target dim set
4291: Begin
4292: --
4293: for i in 1..p_kpi.dim_set.count loop
4294: init_agg_level(p_kpi.dim_set(i));
4410: /*if there are targets, we need to make sure that the level at the target is aggregated. if target is at month, we cannot just agg to
4411: day level */
4412: procedure set_calendar_agg_level(p_dim_set in out nocopy dim_set_r,p_target_dim_set dim_set_r) is
4413: l_pc bsc_aw_utility.parent_child_tb;
4414: l_child_levels dbms_sql.varchar2_table;
4415: Begin
4416: set_calendar_agg_level(p_dim_set);
4417: if p_dim_set.targets_higher_levels='Y' then
4418: for i in 1..p_dim_set.calendar.parent_child.count loop
4531: procedure identify_standalone_levels(
4532: p_kpi varchar2,
4533: p_dim_set in out nocopy dim_set_r) is
4534: --
4535: l_distict_mo_dim_groups dbms_sql.varchar2_table;
4536: l_levels level_tb;
4537: l_levels_for_new_dim level_tb;
4538: l_dim_to_split dim_tb;
4539: l_dim_copy dim_tb;
4600:
4601: --level mo dim group is held in level.property
4602: procedure get_dim_mo_dim_groups(
4603: p_dim dim_r,
4604: p_distict_mo_dim_groups out nocopy dbms_sql.varchar2_table) is
4605: --
4606: l_mo_dim_group varchar2(200);
4607: Begin
4608: for i in 1..p_dim.levels.count loop
4624: */
4625: procedure create_dmp_program(
4626: p_kpi varchar2,
4627: p_dimset varchar2,
4628: p_dim_levels dbms_sql.varchar2_table,
4629: p_name varchar2,
4630: p_table_name varchar2) is
4631: --
4632: l_oo_object bsc_aw_md_wrapper.bsc_olap_object_tb;
4631: --
4632: l_oo_object bsc_aw_md_wrapper.bsc_olap_object_tb;
4633: l_oo_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
4634: l_dimset varchar2(100);
4635: l_dim_level_dims dbms_sql.varchar2_table;
4636: l_measures dbms_sql.varchar2_table;
4637: l_cubes dbms_sql.varchar2_table;
4638: l_cube_formula dbms_sql.varchar2_table;
4639: l_cube_composites dbms_sql.varchar2_table;
4632: l_oo_object bsc_aw_md_wrapper.bsc_olap_object_tb;
4633: l_oo_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
4634: l_dimset varchar2(100);
4635: l_dim_level_dims dbms_sql.varchar2_table;
4636: l_measures dbms_sql.varchar2_table;
4637: l_cubes dbms_sql.varchar2_table;
4638: l_cube_formula dbms_sql.varchar2_table;
4639: l_cube_composites dbms_sql.varchar2_table;
4640: l_stmt varchar2(8000);
4633: l_oo_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
4634: l_dimset varchar2(100);
4635: l_dim_level_dims dbms_sql.varchar2_table;
4636: l_measures dbms_sql.varchar2_table;
4637: l_cubes dbms_sql.varchar2_table;
4638: l_cube_formula dbms_sql.varchar2_table;
4639: l_cube_composites dbms_sql.varchar2_table;
4640: l_stmt varchar2(8000);
4641: l_calendar_id number;
4634: l_dimset varchar2(100);
4635: l_dim_level_dims dbms_sql.varchar2_table;
4636: l_measures dbms_sql.varchar2_table;
4637: l_cubes dbms_sql.varchar2_table;
4638: l_cube_formula dbms_sql.varchar2_table;
4639: l_cube_composites dbms_sql.varchar2_table;
4640: l_stmt varchar2(8000);
4641: l_calendar_id number;
4642: l_dim_levels dbms_sql.varchar2_table;
4635: l_dim_level_dims dbms_sql.varchar2_table;
4636: l_measures dbms_sql.varchar2_table;
4637: l_cubes dbms_sql.varchar2_table;
4638: l_cube_formula dbms_sql.varchar2_table;
4639: l_cube_composites dbms_sql.varchar2_table;
4640: l_stmt varchar2(8000);
4641: l_calendar_id number;
4642: l_dim_levels dbms_sql.varchar2_table;
4643: l_axis_type varchar2(100);
4638: l_cube_formula dbms_sql.varchar2_table;
4639: l_cube_composites dbms_sql.varchar2_table;
4640: l_stmt varchar2(8000);
4641: l_calendar_id number;
4642: l_dim_levels dbms_sql.varchar2_table;
4643: l_axis_type varchar2(100);
4644: Begin
4645: l_dim_levels:=p_dim_levels;
4646: bsc_aw_md_api.get_kpi_dimset(p_kpi,l_oo_object);
4751: End;
4752:
4753: procedure get_missing_periodicity(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r) is
4754: --
4755: l_periodicity_dim dbms_sql.varchar2_table;
4756: l_lowest_level dbms_sql.varchar2_table;
4757: l_initial_count number;
4758: l_flag boolean;
4759: Begin
4752:
4753: procedure get_missing_periodicity(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r) is
4754: --
4755: l_periodicity_dim dbms_sql.varchar2_table;
4756: l_lowest_level dbms_sql.varchar2_table;
4757: l_initial_count number;
4758: l_flag boolean;
4759: Begin
4760: l_initial_count:=p_dim_set.calendar.periodicity.count;
4827: p_kpi kpi_r,
4828: p_dim_set dim_set_r
4829: )is
4830: --
4831: l_composite_name dbms_sql.varchar2_table;
4832: l_flag boolean;
4833: l_cube cube_tb;
4834: Begin
4835: --note>>> we dont do this for std dim since they are single level dim
5099: /*check to see if list partition is possible or we need hash partition the number of partitions are set here*/
5100: procedure set_pt_type_count(p_dim_set in out nocopy dim_set_r) is
5101: l_bt_pt_type varchar2(40);
5102: l_bt_pt_count number;
5103: l_bt_levels dbms_sql.varchar2_table;
5104: l_reason varchar2(8000);
5105: l_partition_count number;
5106: Begin
5107: if bsc_aw_utility.get_property(p_dim_set.property,'hash partition').property_value='Y' then
5349: log_n('Exception in set_PT_dim_aggregated '||sqlerrm);
5350: raise;
5351: End;
5352:
5353: procedure set_PT_dim_aggregated(p_dim in out nocopy dim_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table) is
5354: l_parent_child bsc_aw_adapter_dim.dim_parent_child_tb;
5355: l_pc bsc_aw_utility.parent_child_tb;
5356: l_child_levels dbms_sql.varchar2_table;
5357: zero_aggregated dbms_sql.varchar2_table;
5352:
5353: procedure set_PT_dim_aggregated(p_dim in out nocopy dim_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table) is
5354: l_parent_child bsc_aw_adapter_dim.dim_parent_child_tb;
5355: l_pc bsc_aw_utility.parent_child_tb;
5356: l_child_levels dbms_sql.varchar2_table;
5357: zero_aggregated dbms_sql.varchar2_table;
5358: aggregated dbms_sql.varchar2_table;
5359: Begin
5360: bsc_aw_md_api.get_dim_parent_child(p_dim.dim_name,l_parent_child);
5353: procedure set_PT_dim_aggregated(p_dim in out nocopy dim_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table) is
5354: l_parent_child bsc_aw_adapter_dim.dim_parent_child_tb;
5355: l_pc bsc_aw_utility.parent_child_tb;
5356: l_child_levels dbms_sql.varchar2_table;
5357: zero_aggregated dbms_sql.varchar2_table;
5358: aggregated dbms_sql.varchar2_table;
5359: Begin
5360: bsc_aw_md_api.get_dim_parent_child(p_dim.dim_name,l_parent_child);
5361: for i in 1..l_parent_child.count loop
5354: l_parent_child bsc_aw_adapter_dim.dim_parent_child_tb;
5355: l_pc bsc_aw_utility.parent_child_tb;
5356: l_child_levels dbms_sql.varchar2_table;
5357: zero_aggregated dbms_sql.varchar2_table;
5358: aggregated dbms_sql.varchar2_table;
5359: Begin
5360: bsc_aw_md_api.get_dim_parent_child(p_dim.dim_name,l_parent_child);
5361: for i in 1..l_parent_child.count loop
5362: if l_parent_child(i).parent_level is not null and l_parent_child(i).child_level is not null then
5415: log_n('Exception in set_PT_calendar_aggregated '||sqlerrm);
5416: raise;
5417: End;
5418:
5419: procedure set_PT_calendar_aggregated(p_calendar in out nocopy calendar_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table) is
5420: l_pc bsc_aw_utility.parent_child_tb;
5421: l_child_levels dbms_sql.varchar2_table;
5422: aggregated dbms_sql.varchar2_table;
5423: Begin
5417: End;
5418:
5419: procedure set_PT_calendar_aggregated(p_calendar in out nocopy calendar_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table) is
5420: l_pc bsc_aw_utility.parent_child_tb;
5421: l_child_levels dbms_sql.varchar2_table;
5422: aggregated dbms_sql.varchar2_table;
5423: Begin
5424: for i in 1..p_calendar.parent_child.count loop
5425: if p_calendar.parent_child(i).parent_dim_name is not null and p_calendar.parent_child(i).child_dim_name is not null then
5418:
5419: procedure set_PT_calendar_aggregated(p_calendar in out nocopy calendar_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table) is
5420: l_pc bsc_aw_utility.parent_child_tb;
5421: l_child_levels dbms_sql.varchar2_table;
5422: aggregated dbms_sql.varchar2_table;
5423: Begin
5424: for i in 1..p_calendar.parent_child.count loop
5425: if p_calendar.parent_child(i).parent_dim_name is not null and p_calendar.parent_child(i).child_dim_name is not null then
5426: l_pc(l_pc.count+1).parent:=p_calendar.parent_child(i).parent_dim_name;
5614: this is to register the B table combinations used in the load program. when we load the B tables, we see which one of these combinations
5615: to use
5616: */
5617: procedure set_program_property(p_load_program in out nocopy load_program_r,p_data_source data_source_tb) is
5618: l_ordered_bt dbms_sql.varchar2_table;
5619: Begin
5620: p_load_program.ds_base_tables:=null;
5621: for i in 1..p_data_source.count loop
5622: l_ordered_bt.delete;
5845:
5846: /* this looks at the cal hier and looks at the periodicities in the dimset and trims the parent child relation to have only the relevant
5847: parent child relations*/
5848: procedure get_relevant_cal_hier(p_periodicity periodicity_tb,p_pc cal_parent_child_tb,p_relevant_pc out nocopy cal_parent_child_tb) is
5849: l_periodicity dbms_sql.varchar2_table;
5850: Begin
5851: for i in 1..p_periodicity.count loop
5852: l_periodicity(l_periodicity.count+1):=p_periodicity(i).aw_dim;
5853: end loop;
6078: l_dim_parent_child parent_child_tb;/*for initialization */
6079: l_index number;
6080: l_parent_child bsc_aw_adapter_dim.dim_parent_child_tb;
6081: l_pc_subset bsc_aw_adapter_dim.dim_parent_child_tb;
6082: l_child_levels dbms_sql.varchar2_table;
6083: Begin
6084: for i in 1..p_DS_dim.count loop
6085: for j in 1..p_hpt_data.hpt_dimensions.count loop /*hpt_dimension does not include time */
6086: if p_DS_dim(i).dim_name=p_hpt_data.hpt_dimensions(j).dim_name then
6293: End;
6294:
6295: function make_display_cube_axis(p_dim_set dim_set_r,p_cube cube_r) return axis_tb is
6296: l_axis axis_tb;
6297: l_dimensions dbms_sql.varchar2_table;
6298: Begin
6299: for i in 1..p_cube.cube_axis.count loop
6300: l_dimensions.delete;
6301: if p_cube.cube_axis(i).axis_type='partition template' then