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,
6 p_dim_parent_child out nocopy BSC_AW_ADAPTER_DIM.dim_parent_child_tb,
7 p_dim_levels out nocopy BSC_AW_ADAPTER_DIM.levels_tv
8 ) is
9 --
10 l_level_considered dbms_sql.varchar2_table;
11 --
12 Begin
13 p_dim_parent_child.delete;
14 l_level_considered.delete;
15 p_dim_levels.delete;
16 g_count:=0;--to test infinite recursion
17 for i in 1..p_dim_level_list.count loop
18 if not bsc_aw_utility.in_array(l_level_considered,p_dim_level_list(i)) then
19 get_parent_children(p_dim_level_list(i),l_level_considered,p_dim_parent_child);
20 end if;
21 end loop;
22 --get the level info
23 get_all_distinct_levels(l_level_considered,p_dim_levels);
24 Exception when others then
25 log_n('Exception in get_all_parent_child '||sqlerrm);
26 raise;
27 End;
28
29 procedure get_parent_children(
30 p_level varchar2,
31 p_level_considered in out nocopy dbms_sql.varchar2_table,
32 p_dim_parent_child in out nocopy BSC_AW_ADAPTER_DIM.dim_parent_child_tb
33 ) is
34 --
35 l_parents BSC_AW_ADAPTER_DIM.dim_parent_child_tb;
36 l_children BSC_AW_ADAPTER_DIM.dim_parent_child_tb;
37 --
38 l_count number;
39 --
40 Begin
41 --p_level is the start level.
42 if p_level is null then
43 return;
44 end if;
45 g_count:=g_count+1;
46 if g_count>100000 then
47 log_n('Infinite loop detected in get_parent_children');
48 raise g_exception;
49 end if;
50 if bsc_aw_utility.in_array(p_level_considered,p_level) then
51 return;
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
61 if l_parents.count=0 then --this is single level dim or top level
62 l_count:=l_count+1;
63 p_dim_parent_child(l_count).parent_level:=null;
64 p_dim_parent_child(l_count).child_level:=p_level;
65 else
66 for i in 1..l_parents.count loop
67 l_count:=l_count+1;
68 --l_parents(i) has the parent level, child level, child fk and parent pk
69 p_dim_parent_child(l_count):=l_parents(i);
70 end loop;
71 end if;
72 --for each child / parent, call this recursively. do this only for non rec dim
73 if l_parents.count=1 and l_parents(1).parent_level=l_parents(1).child_level then
74 --this is rec dim
75 null;
76 else
77 --lets assume that there is no circular relation. otherwise, this can get into infinite recursion
78 for i in 1..l_parents.count loop
79 get_parent_children(l_parents(i).parent_level,p_level_considered,p_dim_parent_child);
80 end loop;
81 for i in 1..l_children.count loop
82 get_parent_children(l_children(i).child_level,p_level_considered,p_dim_parent_child);
83 end loop;
84 end if;
85 Exception when others then
86 log_n('Exception in get_parent_children '||sqlerrm);
87 raise;
88 End;
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;
98 end loop;
99 Exception when others then
100 log_n('Exception in get_parent_level '||sqlerrm);
101 raise;
102 End;
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;
112 end loop;
113 Exception when others then
114 log_n('Exception in get_child_level '||sqlerrm);
115 raise;
116 End;
117
118 /*
119 position is not set here. its set in the dim adapter
120 */
121 procedure get_all_distinct_levels(
122 p_levels dbms_sql.varchar2_table,
123 p_dim_levels out nocopy BSC_AW_ADAPTER_DIM.levels_tv
124 ) is
125 --
126 l_level_id number;
127 l_level_pk varchar2(100);
128 l_level_pk_datatype varchar2(100);
129 l_level_source varchar2(100);
130 --
131 Begin
132 if g_debug then
133 log_n('In get_all_distinct_levels '||p_levels.count);
134 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;
144 p_dim_levels(p_levels(i)).level_source:=l_level_source;
145 end loop;
146 Exception when others then
147 log_n('Exception in get_all_distinct_levels '||sqlerrm);
148 raise;
149 End;
150
151 /*
152 we need to get the levels of the dim, then pass the levels to get the kpi involved
153 returns a kpi if a kpi has any of the levels as its dim. we also return the dim set
154 in which the dim levels are involved
155 */
156 procedure get_kpi_for_dim(
157 p_dim in out nocopy bsc_aw_adapter_dim.dimension_r
158 ) is
159 --
160 l_kpi dbms_sql.varchar2_table;
161 l_dimset dbms_sql.varchar2_table;
162 l_kpi_flag boolean;
163 l_dimset_flag boolean;
164 l_kpi_index number;
165 --
166 Begin
167 for i in 1..p_dim.level_groups.count loop
168 for j in 1..p_dim.level_groups(i).levels.count loop
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;
178 l_dimset_flag:=false;
179 l_kpi_index:=0;
180 for m in 1..p_dim.kpi_for_dim.count loop
181 if p_dim.kpi_for_dim(m).kpi=l_kpi(k) then
182 l_kpi_flag:=true;
183 l_kpi_index:=m;
184 --does the dimset also exist?
185 for n in 1..p_dim.kpi_for_dim(m).dim_set.count loop
186 if p_dim.kpi_for_dim(m).dim_set(n)=l_dimset(k) then
187 l_dimset_flag:=true;
188 exit;
189 end if;
190 end loop;
191 exit;
192 end if;
193 end loop;
194 if l_kpi_flag and l_dimset_flag=false then
195 p_dim.kpi_for_dim(l_kpi_index).dim_set(p_dim.kpi_for_dim(l_kpi_index).dim_set.count+1):=l_dimset(k);
196 elsif l_kpi_flag=false and l_dimset_flag=false then
197 p_dim.kpi_for_dim(p_dim.kpi_for_dim.count+1).kpi:=l_kpi(k);
198 p_dim.kpi_for_dim(p_dim.kpi_for_dim.count).dim_set(p_dim.kpi_for_dim(p_dim.kpi_for_dim.count).dim_set.count+1):=l_dimset(k);
199 end if;
200 end loop;
201 end loop;
202 end loop;
203 Exception when others then
204 log_n('Exception in get_kpi_for_dim '||sqlerrm);
205 raise;
206 End;
207
208 --given a list of kpi, get the list of all dim levels that are referenced by the kpi
209 procedure get_dims_for_kpis(
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;
219
220 /*
221 for DBI rec dim. we will have to get the info we need from static package or optimizer std output.
222 for any dim, there can be only 1 data source for all the levels. if there are 2 levels, then the data
223 source will be like
224 (select level1, level2 from ...)
225 */
226 procedure create_rec_data_source(
227 p_dimension in out nocopy bsc_aw_adapter_dim.dimension_r
228 ) is
229 Begin
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,
239 p_dimension.level_groups(1).data_source.denorm_data_source,
240 p_dimension.level_groups(1).data_source.denorm_change_data_source);
241 end if;
242 Exception when others then
243 log_n('Exception in create_rec_data_source '||sqlerrm);
244 raise;
245 End;
246
247 /*
248 This procedure creates the data source for non rec dbi dimensions
249 */
250 procedure create_data_source(
251 p_dimension in out nocopy bsc_aw_adapter_dim.dimension_r
252 ) is
253 l_level_list dbms_sql.varchar2_table;
254 l_level_pk_col dbms_sql.varchar2_table;
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
264 for i in 1..p_dimension.level_groups(1).levels.count loop
265 p_dimension.level_groups(1).data_source.dim_name(i):=p_dimension.level_groups(1).levels(i).level_name;
266 p_dimension.level_groups(1).data_source.pk_col(i):=l_level_pk_col(i);
267 end loop;
268 end if;
269 Exception when others then
270 log_n('Exception in create_data_source '||sqlerrm);
271 raise;
272 End;
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
282 log_n('Exception in set_dim_recursive '||sqlerrm);
283 raise;
284 End;
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
294 log_n('Exception in get_kpi_for_calendar '||sqlerrm);
295 raise;
296 End;
297
298 /*
299 gets
300 calendar kpi belongs to
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;
310 End;
311
312 /*
313 given a kpi find out
314 1. dim sets
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;
324 end loop;
325 Exception when others then
326 log_n('Exception in get_kpi_dim_sets '||sqlerrm);
327 raise;
328 End;
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,
338 p_dim_set varchar2,
339 p_dim_level out nocopy dbms_sql.varchar2_table,
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;
349
350 /*
351 given a dim set, find out all info about the measures
352 unlike get_dim_set_dims, we directly set the dim_set data structure with the measure properties
353 measures belong to kpi and dim sets within the kpi
354 */
355 procedure get_dim_set_measures(
356 p_kpi varchar2,
357 p_dim_set varchar2,
358 p_measure in out nocopy bsc_aw_adapter_kpi.measure_tb
359 ) is
360 l_measure dbms_sql.varchar2_table;
361 l_measure_type dbms_sql.varchar2_table;
362 l_data_type dbms_sql.varchar2_table;
363 l_agg_formula dbms_sql.varchar2_table;
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);
373 p_measure(i).agg_formula.agg_formula:=l_agg_formula(i);
374 p_measure(i).agg_formula.sql_agg_formula:=l_agg_formula(i);
375 if upper(p_measure(i).agg_formula.sql_agg_formula)='AVERAGE' then /*database avg function is AVG */
376 p_measure(i).agg_formula.sql_agg_formula:='AVG';
377 end if;
378 p_measure(i).agg_formula.std_aggregation:=bsc_aw_utility.is_std_aggregation_function(l_agg_formula(i));--Y N
379 p_measure(i).agg_formula.avg_aggregation:=bsc_aw_utility.is_avg_aggregation_function(l_agg_formula(i));--Y N
380 p_measure(i).sql_aggregated:='N';/*default */
381 p_measure(i).forecast:=l_projection(i);
382 p_measure(i).forecast_method:=null;--for now, we dont implement projection in aw
383 bsc_aw_utility.merge_property(p_measure(i).property,l_property(i),',');/*if BALANCE LAST VALUE, this can contain balance loaded Y/N column name */
384 end loop;
385 Exception when others then
386 log_n('Exception in get_dim_set_measures '||sqlerrm);
387 raise;
388 End;
389
390 /*
391 given a dimset, get all the S views and their properties
392 gets both ZMV and regular MV
393 */
394 procedure get_s_views(p_kpi varchar2,p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
395 l_s_views dbms_sql.varchar2_table;
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);
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
409 p_dim_set.s_view(p_dim_set.s_view.count).dim(j):=bsc_aw_adapter_kpi.get_kpi_level_dim_r(p_dim_set,l_levels(j));
410 l_sview_level:=bsc_aw_adapter_kpi.get_dim_level_r(p_dim_set.s_view(p_dim_set.s_view.count).dim(j),l_levels(j));
411 p_dim_set.s_view(p_dim_set.s_view.count).dim(j).levels.delete; --only hold the s view level info
412 p_dim_set.s_view(p_dim_set.s_view.count).dim(j).levels(1):=l_sview_level;
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);
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));
425 l_sview_level:=bsc_aw_adapter_kpi.get_dim_level_r(p_dim_set.z_s_view(p_dim_set.z_s_view.count).dim(j),l_levels(j));
426 p_dim_set.z_s_view(p_dim_set.z_s_view.count).dim(j).levels.delete;
427 p_dim_set.z_s_view(p_dim_set.z_s_view.count).dim(j).levels(1):=l_sview_level;
428 end loop;
429 end loop;
430 Exception when others then
431 log_n('Exception in get_s_views '||sqlerrm);
432 raise;
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;
442
443 --this api called only when there are targets at higher levels
444 procedure get_target_dim_levels(p_kpi varchar2,p_target_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
445 l_levels dbms_sql.varchar2_table;
446 l_dim bsc_aw_adapter_kpi.dim_tb;--just temp backup
447 Begin
448 --backup dim and level info
449 l_dim:=p_target_dim_set.dim;
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
459 p_target_dim_set.dim(i).levels(p_target_dim_set.dim(i).levels.count+1):=l_dim(i).levels(j);
460 end if;
461 end loop;
462 end loop;
463 --get the filter at the lowest level for the target
464 --levels(1) is the lowest level of the target
465 for i in 1..p_target_dim_set.dim.count loop
466 get_dim_level_filter(p_kpi,p_target_dim_set.dim(i).levels(1));
467 end loop;
468 Exception when others then
469 log_n('Exception in get_target_dim_levels '||sqlerrm);
470 raise;
471 End;
472
473 procedure get_target_dim_periodicity(p_kpi varchar2,p_target_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
474 --
475 l_periodicities dbms_sql.number_table;
476 l_calendar bsc_aw_adapter_kpi.calendar_r;
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);
486 p_target_dim_set.calendar.periodicity(p_target_dim_set.calendar.periodicity.count).lowest_level:='Y'; --these are lowest level for targets
487 end if;
488 end loop;
489 --NOTE!! the periodicity we have added for targets is the lowest periodicity only. we have to add the upper periodicities
490 --l_calendar has the parent child relations in it
491 for i in 1..p_target_dim_set.calendar.periodicity.count loop
492 fill_in_target_periodicity(p_target_dim_set.calendar,l_calendar,p_target_dim_set.calendar.periodicity(i).aw_dim);
493 end loop;
494 Exception when others then
495 log_n('Exception in get_target_dim_periodicity '||sqlerrm);
496 raise;
497 End;
498
499 /*
500 fill in higher periodicities for targets
501 */
502 procedure fill_in_target_periodicity(
503 p_target_calendar in out nocopy bsc_aw_adapter_kpi.calendar_r,
504 p_source_calendar bsc_aw_adapter_kpi.calendar_r,
505 p_periodicity varchar2
506 ) is
507 --
508 l_flag boolean;
509 l_periodicity bsc_aw_adapter_kpi.periodicity_r;
510 Begin
511 if p_periodicity is not null then
512 l_flag:=false;
513 for i in 1..p_target_calendar.periodicity.count loop
514 if p_target_calendar.periodicity(i).aw_dim=p_periodicity then
515 l_flag:=true;
516 end if;
517 end loop;
518 if l_flag=false then
519 p_target_calendar.periodicity(p_target_calendar.periodicity.count+1):=bsc_aw_adapter_kpi.get_periodicity_r(p_source_calendar.periodicity,
520 p_periodicity);
521 end if;
522 for i in 1..p_source_calendar.parent_child.count loop
523 if p_source_calendar.parent_child(i).child_dim_name=p_periodicity then
524 --and p_source_calendar.parent_child(i).parent_dim_name is not null then
525 --only if the parent periodicity is a part of the kpi calendar
526 l_flag:=false;
527 for j in 1..p_source_calendar.periodicity.count loop
528 if p_source_calendar.periodicity(j).aw_dim=p_source_calendar.parent_child(i).parent_dim_name then
529 l_flag:=true;
530 exit;
531 end if;
532 end loop;
533 if l_flag then
534 fill_in_target_periodicity(p_target_calendar,p_source_calendar,p_source_calendar.parent_child(i).parent_dim_name);
535 end if;
536 end if;
537 end loop;
538 end if;
539 Exception when others then
540 log_n('Exception in fill_in_target_periodicity '||sqlerrm);
541 raise;
542 End;
543
544 /*
545 given a kpi and the dim set, find out the levels and their dim where there is zero code
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
555 --we can do this since check_dim_zero_code comes after get_dim_properties in bsc_aw_adapter_kpi
556 if p_dim_set.dim(i).levels(j).zero_code_level is not null then
557 p_dim_set.dim(i).levels(j).zero_code:='Y';
558 p_dim_set.dim(i).zero_code:='Y';
559 end if;
560 end if;
561 end loop;
562 end loop;
563 Exception when others then
564 log_n('Exception in check_dim_zero_code '||sqlerrm);
565 raise;
566 End;
567
568 /*
569 for each level get the pk,fk, datatype info
570 */
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));
580 Exception when others then
581 log_n('Exception in get_dim_level_properties '||sqlerrm);
582 raise;
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;
592
593 /*
594 get base tables involved in the dim set
595 for each base table, get the level at which it is
596 get the measures the base table feeds
597 get the formula for each measure
598 --
599 each base table is a data source
600 get_base_table_levels : gets all the levels of the base table
601 we need all the levels because we need to know if the base table has more levels than the dim set
602 get_base_table_measures : gets the measures relevant to this dim set
603 --
604 consider this: dimset is at month,week...both lowest levels. 2 base tables. both at day level. due to this, datasource
605 periodicity was null.
606 (we were doing if p_dim_set.data_source(i).base_tables(1).periodicity.periodicity=p_dim_set.calendar.periodicity(j).periodicity then)
607 new logic: if the base table periodicity is not the same as any of the lowest periodicities, we create as many datasources for
608 each lowest level periodicity
609 more correction. in bsc, B table to S table mapping is stored. this means a day level B table may feed only week and another
610 day level B table feeds month.
611 if the B table is at a lower periodicity, we need to check which periodicity it feeds
612 */
613 procedure get_dim_set_data_source(p_kpi varchar2,p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
614 l_base_tables dbms_sql.varchar2_table;
615 l_data_source bsc_aw_adapter_kpi.data_source_r;
616 l_ds_periodicity bsc_aw_adapter_kpi.periodicity_tb;
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
626 for i in 1..l_base_tables.count loop
627 l_data_source:=null;
628 get_base_table_data_source(p_kpi,p_dim_set.dim_set,l_base_tables(i),l_data_source);
629 set_measure_properties(p_dim_set,l_data_source);
630 l_ds_periodicity.delete;
631 for j in 1..p_dim_set.calendar.periodicity.count loop
632 if p_dim_set.calendar.periodicity(j).lowest_level='Y'
633 and p_dim_set.calendar.periodicity(j).periodicity=l_data_source.base_tables(1).periodicity.periodicity then
634 l_ds_periodicity(l_ds_periodicity.count+1):=p_dim_set.calendar.periodicity(j);
635 exit;
636 end if;
637 end loop;
638 if l_ds_periodicity.count=0 then --B table lower periodicity
639 l_feed_periodicity.delete;
640 get_B_table_feed_periodicity(p_kpi,p_dim_set.dim_set,l_base_tables(i),l_feed_periodicity);
641 for j in 1..p_dim_set.calendar.periodicity.count loop
642 if p_dim_set.calendar.periodicity(j).lowest_level='Y'
643 and bsc_aw_utility.in_array(l_feed_periodicity,p_dim_set.calendar.periodicity(j).periodicity) then
644 l_ds_periodicity(l_ds_periodicity.count+1):=p_dim_set.calendar.periodicity(j);
645 end if;
646 end loop;
647 end if;
648 if l_ds_periodicity.count=0 then --B table at higher periodicity
649 for j in 1..l_feed_periodicity.count loop
650 for k in 1..p_dim_set.calendar.periodicity.count loop
651 if p_dim_set.calendar.periodicity(k).periodicity=l_feed_periodicity(j) then
652 l_ds_periodicity(l_ds_periodicity.count+1):=p_dim_set.calendar.periodicity(k);
653 exit;
654 end if;
655 end loop;
656 end loop;
657 end if;
658 if l_ds_periodicity.count=0 then --problem...
659 log('No target periodicity for B table '||l_base_tables(i)||' could be determined. Fatal...');
660 raise bsc_aw_utility.g_exception;
661 end if;
662 for j in 1..l_ds_periodicity.count loop
663 if g_debug then
664 log('Create datasource for B table '||l_base_tables(i)||' and periodicity '||l_ds_periodicity(j).periodicity);
665 end if;
666 p_dim_set.data_source(p_dim_set.data_source.count+1):=l_data_source; --copy B table info
667 p_dim_set.data_source(p_dim_set.data_source.count).ds_type:='initial';
668 p_dim_set.data_source(p_dim_set.data_source.count).std_dim:=p_dim_set.std_dim;
669 p_dim_set.data_source(p_dim_set.data_source.count).dim:=p_dim_set.dim;
670 p_dim_set.data_source(p_dim_set.data_source.count).calendar:=p_dim_set.calendar;
671 p_dim_set.data_source(p_dim_set.data_source.count).calendar.periodicity.delete;
672 p_dim_set.data_source(p_dim_set.data_source.count).calendar.periodicity(1):=l_ds_periodicity(j);
673 /*starting from the periodicity of the ds as the lowest level, get the upper part of cal hier*/
674 get_ds_relevant_cal_hier(p_dim_set.data_source(p_dim_set.data_source.count).calendar);
675 --copy this info into the inc data source
676 --later the data_source_stmt is populated differently for data source and inc data source
677 l_ds_copy:=p_dim_set.data_source(p_dim_set.data_source.count);
678 p_dim_set.inc_data_source(p_dim_set.data_source.count):=l_ds_copy;
679 p_dim_set.inc_data_source(p_dim_set.data_source.count).ds_type:='inc';
680 end loop;
681 end loop;
682 Exception when others then
683 log_n('Exception in get_dim_set_data_source '||sqlerrm);
684 raise;
685 End;
686
687 /*set the measure properties from dimset into datasource (In get_base_table_data_source only the measure name is set)*/
688 procedure set_measure_properties(p_dim_set bsc_aw_adapter_kpi.dim_set_r,p_data_source in out nocopy bsc_aw_adapter_kpi.data_source_r) is
689 Begin
690 for i in 1..p_data_source.measure.count loop
691 for j in 1..p_dim_set.measure.count loop
692 if lower(p_dim_set.measure(j).measure)=lower(p_data_source.measure(i).measure) then
693 p_data_source.measure(i).measure_type:=p_dim_set.measure(j).measure_type;
694 p_data_source.measure(i).data_type:=p_dim_set.measure(j).data_type;
695 /*we do not set the formulas. formula is a DS property, not a dimset property*/
696 p_data_source.measure(i).forecast:=p_dim_set.measure(j).forecast;
697 p_data_source.measure(i).forecast_method:=p_dim_set.measure(j).forecast_method;
698 p_data_source.measure(i).cube:=p_dim_set.measure(j).cube;--null at this point
699 p_data_source.measure(i).countvar_cube:=p_dim_set.measure(j).countvar_cube;--null at this point
700 p_data_source.measure(i).fcst_cube:=p_dim_set.measure(j).fcst_cube;--null at this point
701 p_data_source.measure(i).property:=p_dim_set.measure(j).property;
702 exit;
703 end if;
704 end loop;
705 end loop;
706 Exception when others then
707 log_n('Exception in set_measure_properties '||sqlerrm);
708 raise;
709 End;
710
711 /*the DS is at some higher level. get just the upper part of the cal hier */
712 procedure get_ds_relevant_cal_hier(p_calendar in out nocopy bsc_aw_adapter_kpi.calendar_r) is
713 l_upper_hier bsc_aw_adapter_kpi.cal_parent_child_tb;
714 Begin
715 bsc_aw_adapter_kpi.get_upper_cal_hier(p_calendar.parent_child,p_calendar.periodicity(1).aw_dim,l_upper_hier);
716 p_calendar.parent_child.delete;
717 for i in 1..l_upper_hier.count loop
718 p_calendar.parent_child(p_calendar.parent_child.count+1):=l_upper_hier(i);
719 end loop;
720 Exception when others then
721 log_n('Exception in get_ds_relevant_cal_hier '||sqlerrm);
722 raise;
723 End;
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;
733
734 /*
735 for a base table, finds the levels and measures and formula involved
736 for now, we are saying a data source has 1 base table
737 also find the periodicity of the base table
738 */
739 procedure get_base_table_data_source(
740 p_kpi varchar2,
741 p_dim_set varchar2,
742 p_base_table varchar2,
743 p_data_source in out nocopy bsc_aw_adapter_kpi.data_source_r) is
744 --
745 l_bt_levels dbms_sql.varchar2_table;
746 l_bt_feed_levels dbms_sql.varchar2_table;
747 l_bt_level_fks dbms_sql.varchar2_table; --contains the fk like city_code etc from the base table
748 l_bt_level_pks dbms_sql.varchar2_table;
749 l_ds_measures dbms_sql.varchar2_table;--data source measures , same as dim set measures
750 l_formula dbms_sql.varchar2_table;
751 l_level bsc_aw_adapter_kpi.level_r;
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 --
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;
764 for i in 1..l_bt_levels.count loop
765 p_data_source.base_tables(1).levels(i).level_name:=l_bt_levels(i);
766 p_data_source.base_tables(1).levels(i).fk:=l_bt_level_fks(i);
767 p_data_source.base_tables(1).levels(i).pk:=l_bt_level_pks(i);
768 p_data_source.base_tables(1).feed_levels(i):=l_bt_feed_levels(i); --Y or N
769 end loop;
770 for i in 1..l_ds_measures.count loop
771 p_data_source.measure(i).measure:=l_ds_measures(i);
772 p_data_source.measure(i).formula:=l_formula(i);
773 end loop;
774 /*if there is prj table, we add the prj table as another B table for the DS
775 */
776 if l_prj_table is not null then
777 l_bt_copy:=p_data_source.base_tables(1);
778 l_bt_copy.base_table_name:=l_prj_table;
779 l_bt_copy.projection_table:=null;
780 p_data_source.base_tables(p_data_source.base_tables.count+1):=l_bt_copy;
781 end if;
782 Exception when others then
783 log_n('Exception in get_base_table_data_source '||sqlerrm);
784 raise;
785 End;
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
795 log_n('Exception in get_dim_set_calendar '||sqlerrm);
796 raise;
797 End;
798
799 /*
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;
809
810 /*
811 this procedure gives the period in which there is a mix of forecast and real data
812 we need the following
813 kpi and periodicity : we use this to hit bsc_db_tables that will indicate the current period
814 calendar : with this, we will get the current year.
815 then we can say make the period
816
817 called from bsc_aw_load_kpi
818 */
819 procedure get_forecast_current_period(
820 p_kpi varchar2,
821 p_calendar number,
822 p_periodicity number,
823 p_period out nocopy varchar2
824 ) is
825 --
826 l_year number;
827 l_period number;
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;
837 End;
838
839 /* period is in period.year format. periodicity is not specified or returned in this api.
840 we can later have caching
841 we may pass PRJ table which can have null current period*/
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
851 log_n('Exception in get_table_current_period '||sqlerrm);
852 raise;
853 End;
854
855 procedure get_all_kpi_in_aw(
856 p_kpi out nocopy dbms_sql.varchar2_table,
857 p_kpi_dimset out nocopy dbms_sql.varchar2_table
858 ) is
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
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;
871 end loop;
872 Exception when others then
873 log_n('Exception in get_all_kpi_in_aw '||sqlerrm);
874 raise;
875 End;
876
877 /*
878 4486476
879 Issue is that PMV uses the short names for dimensions and measures. we use the dim table name and measure name from bsc
880 so we decided to do the lookup for them.
881 get_measures_for_short_names and get_dim_levels_for_short_names
882 */
883 procedure get_measures_for_short_names(
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;
893
894 procedure get_dim_levels_for_short_names(
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;
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;
912
913 --------------------------
914 procedure init_all is
915 Begin
916 g_debug:=bsc_aw_utility.g_debug;
917 g_count:=0;
918 Exception when others then
919 null;
920 End;
921
922 procedure log(p_message varchar2) is
923 Begin
924 bsc_aw_utility.log(p_message);
925 Exception when others then
926 null;
927 End;
928
929 procedure log_n(p_message varchar2) is
930 Begin
931 log(' ');
932 log(p_message);
933 Exception when others then
934 null;
935 End;
936
937 END BSC_AW_BSC_METADATA;
[Home] [Help]