[Home] [Help]
1: package body BSC_AW_BSC_METADATA AS
2: /*$Header: BSCAWMDB.pls 120.16 2006/04/20 11:31 vsurendr noship $*/
3:
4: procedure get_all_parent_child(
5: p_dim_level_list dbms_sql.varchar2_table,
52: end if;
53: p_level_considered(p_level_considered.count+1):=p_level;
54: l_parents.delete;
55: l_children.delete;
56: bsc_metadata.get_parent_level(p_level,l_parents);
57: bsc_metadata.get_child_level(p_level,l_children);
58: --assume that a parent child pair exists as a unique row in bsc_sys_dim_level_rels
59: l_count:=p_dim_parent_child.count;
60: --have to handle single level RECURSIVE dimensions. rec dim must have single entry with parent=child
53: p_level_considered(p_level_considered.count+1):=p_level;
54: l_parents.delete;
55: l_children.delete;
56: bsc_metadata.get_parent_level(p_level,l_parents);
57: bsc_metadata.get_child_level(p_level,l_children);
58: --assume that a parent child pair exists as a unique row in bsc_sys_dim_level_rels
59: l_count:=p_dim_parent_child.count;
60: --have to handle single level RECURSIVE dimensions. rec dim must have single entry with parent=child
61: if l_parents.count=0 then --this is single level dim or top level
89:
90: procedure get_parent_level(p_level varchar2,p_parents out nocopy dbms_sql.varchar2_table) is
91: l_parents BSC_AW_ADAPTER_DIM.dim_parent_child_tb;
92: Begin
93: bsc_metadata.get_parent_level(p_level,l_parents);
94: for i in 1..l_parents.count loop
95: if l_parents(i).parent_level is not null then
96: p_parents(p_parents.count+1):=l_parents(i).parent_level;
97: end if;
103:
104: procedure get_child_level(p_level varchar2,p_children out nocopy dbms_sql.varchar2_table) is
105: l_children BSC_AW_ADAPTER_DIM.dim_parent_child_tb;
106: Begin
107: bsc_metadata.get_child_level(p_level,l_children);
108: for i in 1..l_children.count loop
109: if l_children(i).child_level is not null then
110: p_children(p_children.count+1):=l_children(i).child_level;
111: end if;
135: p_dim_levels.delete;
136: for i in 1..p_levels.count loop
137: --l_level_id: can this be null?
138: log_n(p_levels(i));
139: bsc_metadata.get_level_pk(p_levels(i),l_level_id,l_level_pk,l_level_pk_datatype,l_level_source);
140: p_dim_levels(p_levels(i)).level_name:=p_levels(i);
141: p_dim_levels(p_levels(i)).level_id:=l_level_id;
142: p_dim_levels(p_levels(i)).pk.pk:=l_level_pk;
143: p_dim_levels(p_levels(i)).pk.data_type:=l_level_pk_datatype;
169: --NOTE!!! if a dim is std, then all kpi implemented in aw have this dim
170: if p_dim.dim_type='std' then
171: get_all_kpi_in_aw(l_kpi,l_dimset);
172: else
173: bsc_metadata.get_kpi_for_dim(p_dim.level_groups(i).levels(j).level_name,l_kpi,l_dimset);
174: end if;
175: --if both kpi+dimset already exists, do not add it. else add it.
176: for k in 1..l_kpi.count loop
177: l_kpi_flag:=false;
210: p_kpi_list dbms_sql.varchar2_table,
211: p_dim_list out nocopy dbms_sql.varchar2_table
212: ) is
213: Begin
214: bsc_metadata.get_dims_for_kpis(p_kpi_list,p_dim_list);
215: Exception when others then
216: log_n('Exception in get_dims_for_kpis '||sqlerrm);
217: raise;
218: End;
230: create_data_source(p_dimension);
231: --if rec dim, also set the denorm data source
232: --rec dim have only 1 level
233: if p_dimension.recursive='Y' then
234: bsc_metadata.get_denorm_data_source(
235: p_dimension.level_groups(1).levels(1).level_name,
236: p_dimension.level_groups(1).data_source.child_col,
237: p_dimension.level_groups(1).data_source.parent_col,
238: p_dimension.level_groups(1).data_source.position_col,
255: Begin
256: for i in 1..p_dimension.level_groups(1).levels.count loop
257: l_level_list(i):=p_dimension.level_groups(1).levels(i).level_name;
258: end loop;
259: bsc_metadata.get_dim_data_source(l_level_list,l_level_pk_col,
260: p_dimension.level_groups(1).data_source.data_source,p_dimension.level_groups(1).data_source.inc_data_source);
261: --for bsc dimensions, p_dimension.level_groups(1).data_source.data_source will be null
262: --it will be non null only foR DBI dimensions
263: if p_dimension.level_groups(1).data_source.data_source is not null then
273:
274: procedure set_dim_recursive(p_dimension in out nocopy bsc_aw_adapter_dim.dimension_r) is
275: Begin
276: --in rec dim, there is only 1 level
277: p_dimension.recursive:=bsc_metadata.is_dim_recursive(p_dimension.level_groups(1).levels(1).level_name);
278: if p_dimension.recursive='Y' then
279: p_dimension.recursive_norm_hier:='Y'; --implement rec dim as normal by default
280: end if;
281: Exception when others then
285:
286: procedure get_kpi_for_calendar(p_calendar in out nocopy bsc_aw_calendar.calendar_r) is
287: l_kpi_list dbms_sql.varchar2_table;
288: Begin
289: bsc_metadata.get_kpi_for_calendar(p_calendar.calendar_id,l_kpi_list);
290: for i in 1..l_kpi_list.count loop
291: p_calendar.kpi_for_dim(i).kpi:=l_kpi_list(i);
292: end loop;
293: Exception when others then
301: */
302: procedure get_kpi_properties(p_kpi in out nocopy bsc_aw_adapter_kpi.kpi_r) is
303: l_calendar number;
304: Begin
305: bsc_metadata.get_kpi_calendar(p_kpi.kpi,l_calendar);
306: p_kpi.calendar:=l_calendar;
307: Exception when others then
308: log_n('Exception in get_kpi_properties '||sqlerrm);
309: raise;
315: */
316: procedure get_kpi_dim_sets(p_kpi in out nocopy bsc_aw_adapter_kpi.kpi_r) is
317: l_dim_set dbms_sql.varchar2_table;
318: Begin
319: bsc_metadata.get_kpi_dim_sets(p_kpi.kpi,l_dim_set);
320: --l_dim_set will be numbers like 1,2 etc to make the name unique, append with kpi
321: for i in 1..l_dim_set.count loop
322: p_kpi.dim_set(i).dim_set:=l_dim_set(i);
323: p_kpi.dim_set(i).dim_set_name:='dimset_'||l_dim_set(i)||'_'||p_kpi.kpi;
329:
330: /*
331: a dim set will have dim levels from diff dimensions. it can have city,state,product,prod category and customer
332: the info that city and state belong to geog dim is in bsc olap objects. this package does not access bsc_olap_objects
333: its a one way access to bsc metadata. so this procedure returns the list of dim levels to kpi adapter. the kpi adapter
334: will access bsc olap metadata to group these dim levels into Concat dims
335: */
336: procedure get_dim_set_dims(
337: p_kpi varchar2,
340: p_mo_dim_group out nocopy dbms_sql.varchar2_table,
341: p_skip_level out nocopy dbms_sql.varchar2_table
342: ) is
343: Begin
344: bsc_metadata.get_dim_set_dims(p_kpi,p_dim_set,p_dim_level,p_mo_dim_group,p_skip_level);
345: Exception when others then
346: log_n('Exception in get_dim_set_dims '||sqlerrm);
347: raise;
348: End;
364: l_projection dbms_sql.varchar2_table;
365: l_property dbms_sql.varchar2_table;
366: Begin
367: p_measure.delete;
368: bsc_metadata.get_dim_set_measures(p_kpi,p_dim_set,l_measure,l_measure_type,l_data_type,l_agg_formula,l_projection,l_property);
369: for i in 1..l_measure.count loop
370: p_measure(i).measure:=l_measure(i);
371: p_measure(i).measure_type:=l_measure_type(i);
372: p_measure(i).data_type:=l_data_type(i);
396: l_levels dbms_sql.varchar2_table;
397: l_sview_level bsc_aw_adapter_kpi.level_r;
398: Begin
399: --get_s_views will return only regular MV not ZMV
400: bsc_metadata.get_s_views(p_kpi,p_dim_set.dim_set,l_s_views);
401: --for each s view get the level info
402: for i in 1..l_s_views.count loop
403: l_levels.delete;
404: bsc_metadata.get_s_view_levels(l_s_views(i),l_levels);
400: bsc_metadata.get_s_views(p_kpi,p_dim_set.dim_set,l_s_views);
401: --for each s view get the level info
402: for i in 1..l_s_views.count loop
403: l_levels.delete;
404: bsc_metadata.get_s_view_levels(l_s_views(i),l_levels);
405: p_dim_set.s_view(p_dim_set.s_view.count+1).s_view:=l_s_views(i);
406: p_dim_set.s_view(p_dim_set.s_view.count).id:='MV_'||i;
407: for j in 1..l_levels.count loop
408: --note>>>a level can be in 1 dim only, even in the case of standalone levels
413: end loop;
414: end loop;
415: --get the ZMV
416: l_s_views.delete;
417: bsc_metadata.get_z_s_views(p_kpi,p_dim_set.dim_set,l_s_views);
418: for i in 1..l_s_views.count loop
419: l_levels.delete;
420: bsc_metadata.get_s_view_levels(l_s_views(i),l_levels);
421: p_dim_set.z_s_view(p_dim_set.z_s_view.count+1).s_view:=l_s_views(i);
416: l_s_views.delete;
417: bsc_metadata.get_z_s_views(p_kpi,p_dim_set.dim_set,l_s_views);
418: for i in 1..l_s_views.count loop
419: l_levels.delete;
420: bsc_metadata.get_s_view_levels(l_s_views(i),l_levels);
421: p_dim_set.z_s_view(p_dim_set.z_s_view.count+1).s_view:=l_s_views(i);
422: p_dim_set.z_s_view(p_dim_set.z_s_view.count).id:='ZMV_'||i;
423: for j in 1..l_levels.count loop
424: p_dim_set.z_s_view(p_dim_set.z_s_view.count).dim(j):=bsc_aw_adapter_kpi.get_kpi_level_dim_r(p_dim_set,l_levels(j));
433: End;
434:
435: function is_target_at_higher_level(p_kpi varchar2,p_dim_set varchar2) return varchar2 is
436: Begin
437: return bsc_metadata.is_target_at_higher_level(p_kpi,p_dim_set);
438: Exception when others then
439: log_n('Exception in is_target_at_higher_level '||sqlerrm);
440: raise;
441: End;
450: --delete all level info
451: for i in 1..p_target_dim_set.dim.count loop
452: p_target_dim_set.dim(i).levels.delete;
453: end loop;
454: bsc_metadata.get_target_levels(p_kpi,p_target_dim_set.dim_set,l_levels);
455: --now for these levels, fill in the dim info
456: for i in 1..l_dim.count loop
457: for j in 1..l_dim(i).levels.count loop
458: if bsc_aw_utility.in_array(l_levels,l_dim(i).levels(j).level_name) then
477: Begin
478: --backup
479: l_calendar:=p_target_dim_set.calendar;
480: p_target_dim_set.calendar.periodicity.delete;
481: --bsc_metadata.get_target_periodicity will only return lowest periodicity for targets
482: bsc_metadata.get_target_periodicity(p_kpi,p_target_dim_set.dim_set,l_periodicities);
483: for i in 1..l_calendar.periodicity.count loop
484: if bsc_aw_utility.in_array(l_periodicities,l_calendar.periodicity(i).periodicity) then
485: p_target_dim_set.calendar.periodicity(p_target_dim_set.calendar.periodicity.count+1):=l_calendar.periodicity(i);
478: --backup
479: l_calendar:=p_target_dim_set.calendar;
480: p_target_dim_set.calendar.periodicity.delete;
481: --bsc_metadata.get_target_periodicity will only return lowest periodicity for targets
482: bsc_metadata.get_target_periodicity(p_kpi,p_target_dim_set.dim_set,l_periodicities);
483: for i in 1..l_calendar.periodicity.count loop
484: if bsc_aw_utility.in_array(l_periodicities,l_calendar.periodicity(i).periodicity) then
485: p_target_dim_set.calendar.periodicity(p_target_dim_set.calendar.periodicity.count+1):=l_calendar.periodicity(i);
486: p_target_dim_set.calendar.periodicity(p_target_dim_set.calendar.periodicity.count).lowest_level:='Y'; --these are lowest level for targets
546: */
547: procedure check_dim_zero_code(p_kpi varchar2,p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
548: l_levels dbms_sql.varchar2_table;
549: Begin
550: bsc_metadata.get_zero_code_levels(p_kpi,p_dim_set.dim_set,l_levels);
551: for i in 1..p_dim_set.dim.count loop
552: for j in 1..p_dim_set.dim(i).levels.count loop
553: if bsc_aw_utility.in_array(l_levels,p_dim_set.dim(i).levels(j).level_name) then
554: --we can say there is zero code only if there is zero code level for this level
571: procedure get_dim_level_properties(p_kpi varchar2,p_dim in out nocopy bsc_aw_adapter_kpi.dim_r) is
572: Begin
573: for i in 1..p_dim.levels.count loop
574: p_dim.levels(i).level_type:=p_dim.property; --normal or time
575: bsc_metadata.get_dim_level_properties(p_dim.levels(i).level_name,
576: p_dim.levels(i).pk,p_dim.levels(i).fk,p_dim.levels(i).data_type,p_dim.levels(i).level_source);
577: end loop;
578: --get the filter for the lowest level in the kpi
579: get_dim_level_filter(p_kpi,p_dim.levels(1));
583: End;
584:
585: procedure get_dim_level_filter(p_kpi varchar2,p_level in out nocopy bsc_aw_adapter_kpi.level_r) is
586: Begin
587: bsc_metadata.get_dim_level_filter(p_kpi,p_level.level_name,p_level.filter);
588: Exception when others then
589: log_n('Exception in get_dim_level_filter '||sqlerrm);
590: raise;
591: End;
617: l_ds_copy bsc_aw_adapter_kpi.data_source_r;
618: l_feed_periodicity dbms_sql.number_table;
619: Begin
620: if p_dim_set.dim_set_type='actual' then
621: bsc_metadata.get_dim_set_base_tables(p_kpi,p_dim_set.dim_set,l_base_tables);
622: else
623: bsc_metadata.get_dim_set_target_base_tables(p_kpi,p_dim_set.dim_set,l_base_tables);
624: end if;
625: --every base table is a data source
619: Begin
620: if p_dim_set.dim_set_type='actual' then
621: bsc_metadata.get_dim_set_base_tables(p_kpi,p_dim_set.dim_set,l_base_tables);
622: else
623: bsc_metadata.get_dim_set_target_base_tables(p_kpi,p_dim_set.dim_set,l_base_tables);
624: end if;
625: --every base table is a data source
626: for i in 1..l_base_tables.count loop
627: l_data_source:=null;
724:
725: --given a B table, what are the target periodicities it feeds
726: procedure get_B_table_feed_periodicity(p_kpi varchar2,p_dim_set varchar2,p_base_table varchar2,p_feed_periodicity out nocopy dbms_sql.number_table) is
727: Begin
728: bsc_metadata.get_B_table_feed_periodicity(p_kpi,p_dim_set,p_base_table,p_feed_periodicity);
729: Exception when others then
730: log_n('Exception in get_B_table_feed_periodicity '||sqlerrm);
731: raise;
732: End;
752: l_prj_table varchar2(100);
753: l_partition bsc_aw_utility.object_partition_r;
754: l_bt_copy bsc_aw_adapter_kpi.base_table_r;
755: Begin
756: bsc_metadata.get_base_table_levels(p_kpi,p_dim_set,p_base_table,l_bt_levels,l_bt_level_fks,l_bt_level_pks,l_bt_feed_levels);
757: bsc_metadata.get_base_table_measures(p_kpi,p_dim_set,p_base_table,l_ds_measures,l_formula);
758: bsc_metadata.get_base_table_periodicity(p_base_table,p_data_source.base_tables(1).periodicity.periodicity);
759: bsc_metadata.get_base_table_properties(p_base_table,l_prj_table,l_partition);
760: --
753: l_partition bsc_aw_utility.object_partition_r;
754: l_bt_copy bsc_aw_adapter_kpi.base_table_r;
755: Begin
756: bsc_metadata.get_base_table_levels(p_kpi,p_dim_set,p_base_table,l_bt_levels,l_bt_level_fks,l_bt_level_pks,l_bt_feed_levels);
757: bsc_metadata.get_base_table_measures(p_kpi,p_dim_set,p_base_table,l_ds_measures,l_formula);
758: bsc_metadata.get_base_table_periodicity(p_base_table,p_data_source.base_tables(1).periodicity.periodicity);
759: bsc_metadata.get_base_table_properties(p_base_table,l_prj_table,l_partition);
760: --
761: p_data_source.base_tables(1).base_table_name:=p_base_table;
754: l_bt_copy bsc_aw_adapter_kpi.base_table_r;
755: Begin
756: bsc_metadata.get_base_table_levels(p_kpi,p_dim_set,p_base_table,l_bt_levels,l_bt_level_fks,l_bt_level_pks,l_bt_feed_levels);
757: bsc_metadata.get_base_table_measures(p_kpi,p_dim_set,p_base_table,l_ds_measures,l_formula);
758: bsc_metadata.get_base_table_periodicity(p_base_table,p_data_source.base_tables(1).periodicity.periodicity);
759: bsc_metadata.get_base_table_properties(p_base_table,l_prj_table,l_partition);
760: --
761: p_data_source.base_tables(1).base_table_name:=p_base_table;
762: p_data_source.base_tables(1).projection_table:=l_prj_table;
755: Begin
756: bsc_metadata.get_base_table_levels(p_kpi,p_dim_set,p_base_table,l_bt_levels,l_bt_level_fks,l_bt_level_pks,l_bt_feed_levels);
757: bsc_metadata.get_base_table_measures(p_kpi,p_dim_set,p_base_table,l_ds_measures,l_formula);
758: bsc_metadata.get_base_table_periodicity(p_base_table,p_data_source.base_tables(1).periodicity.periodicity);
759: bsc_metadata.get_base_table_properties(p_base_table,l_prj_table,l_partition);
760: --
761: p_data_source.base_tables(1).base_table_name:=p_base_table;
762: p_data_source.base_tables(1).projection_table:=l_prj_table;
763: p_data_source.base_tables(1).table_partition:=l_partition;
786:
787: procedure get_dim_set_calendar(p_kpi varchar2,p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
788: l_periodicity dbms_sql.number_table;
789: Begin
790: bsc_metadata.get_kpi_periodicities(p_kpi,p_dim_set.dim_set,l_periodicity);
791: for i in 1..l_periodicity.count loop
792: p_dim_set.calendar.periodicity(p_dim_set.calendar.periodicity.count+1).periodicity:=l_periodicity(i);
793: end loop;
794: Exception when others then
800: given a calendar and periodicity, get the column name from bsc_db_calendar
801: */
802: function get_db_calendar_column(p_calendar number,p_periodicity number) return varchar2 is
803: Begin
804: return bsc_metadata.get_db_calendar_column(p_calendar,p_periodicity);
805: Exception when others then
806: log_n('Exception in get_db_calendar_column '||sqlerrm);
807: raise;
808: End;
828: Begin
829: --bsc_aw_calendar.get_calendar_current_year(p_calendar,l_year);
830: --we do not want to call bsc_aw_calendar.get_calendar_current_year. what if in the future, each kpi has its own
831: --current year?
832: bsc_metadata.get_kpi_current_period(p_kpi,p_periodicity,l_period,l_year);
833: p_period:=l_period||'.'||l_year;
834: Exception when others then
835: log_n('Exception in get_forecast_current_period '||sqlerrm);
836: raise;
842: procedure get_table_current_period(p_table varchar2,p_period out nocopy varchar2) is
843: l_year number;
844: l_period number;
845: Begin
846: bsc_metadata.get_table_current_period(p_table,l_period,l_year);
847: if l_period is not null and l_year is not null then
848: p_period:=l_period||'.'||l_year;
849: end if;
850: Exception when others then
859: --
860: l_kpi dbms_sql.varchar2_table;
861: l_dimset dbms_sql.varchar2_table;
862: Begin
863: bsc_metadata.get_all_kpi_in_aw(l_kpi);
864: for i in 1..l_kpi.count loop
865: l_dimset.delete;
866: bsc_metadata.get_kpi_dim_sets(l_kpi(i),l_dimset);
867: for j in 1..l_dimset.count loop
862: Begin
863: bsc_metadata.get_all_kpi_in_aw(l_kpi);
864: for i in 1..l_kpi.count loop
865: l_dimset.delete;
866: bsc_metadata.get_kpi_dim_sets(l_kpi(i),l_dimset);
867: for j in 1..l_dimset.count loop
868: p_kpi(p_kpi.count+1):=l_kpi(i);
869: p_kpi_dimset(p_kpi_dimset.count+1):=l_dimset(j);
870: end loop;
884: p_short_name dbms_sql.varchar2_table,
885: p_measure_name out nocopy dbms_sql.varchar2_table
886: ) is
887: Begin
888: bsc_metadata.get_measures_for_short_names(p_short_name,p_measure_name);
889: Exception when others then
890: log_n('Exception in get_measures_for_short_names '||sqlerrm);
891: raise;
892: End;
895: p_short_name dbms_sql.varchar2_table,
896: p_dim_level_name out nocopy dbms_sql.varchar2_table
897: ) is
898: Begin
899: bsc_metadata.get_dim_levels_for_short_names(p_short_name,p_dim_level_name);
900: Exception when others then
901: log_n('Exception in get_dim_levels_for_short_names '||sqlerrm);
902: raise;
903: End;
903: End;
904:
905: function is_level_used_by_aw_kpi(p_level varchar2) return boolean is
906: Begin
907: return bsc_metadata.is_level_used_by_aw_kpi(p_level);
908: Exception when others then
909: log_n('Exception in is_level_used_by_aw_kpi '||sqlerrm);
910: raise;
911: End;
933: Exception when others then
934: null;
935: End;
936:
937: END BSC_AW_BSC_METADATA;