1 package body BSC_AW_MD_API AS
2 /*$Header: BSCAWMAB.pls 120.22 2006/11/04 10:39:38 amitgupt noship $*/
3
4 function is_dim_present(
5 p_dimension varchar2
6 )return boolean is
7 Begin
8 g_olap_object.delete;
9 get_bsc_olap_object(p_dimension,'dimension',p_dimension,'dimension',g_olap_object);
10 if g_olap_object.count>0 then
11 return true;
12 else
13 return false;
14 end if;
15 Exception when others then
16 log_n('Exception in is_dim_present '||sqlerrm);
17 raise;
18 End;
19
20 procedure get_kpi_for_dim(
21 p_dim_name varchar2,
22 p_kpi_list out nocopy dbms_sql.varchar2_table
23 ) is
24 Begin
25 g_olap_object_relation.delete;
26 get_bsc_olap_object_relation(null,null,'dimension kpi',p_dim_name,'dimension',g_olap_object_relation);
27 for i in 1..g_olap_object_relation.count loop
28 p_kpi_list(i):=g_olap_object_relation(i).relation_object;
29 end loop;
30 Exception when others then
31 log_n('Exception in get_kpi_for_dim '||sqlerrm);
32 raise;
33 End;
34
35 procedure mark_kpi_recreate(p_kpi varchar2) is
36 Begin
37 bsc_aw_md_wrapper.mark_kpi_recreate(p_kpi);
38 Exception when others then
39 log_n('Exception in mark_kpi_recreate '||sqlerrm);
40 raise;
41 End;
42
43 /*
44 returns the name of the olap objects that need to be dropped from aw
45 */
46 procedure get_dim_olap_objects(
47 p_dim_name varchar2,
48 p_objects out nocopy bsc_aw_md_wrapper.bsc_olap_object_tb,
49 p_type varchar2
50 ) is
51 --
52 Begin
53 if p_type='all' then
54 get_bsc_olap_object(null,null,p_dim_name,'dimension',p_objects);
55 else
56 get_bsc_olap_object(null,p_type,p_dim_name,'dimension',p_objects);
57 end if;
58 Exception when others then
59 log_n('Exception in get_dim_olap_objects '||sqlerrm);
60 raise;
61 End;
62
63 procedure drop_dim(p_dim_name varchar2) is
64 Begin
65 clear_all_cache;
66 bsc_aw_md_wrapper.drop_dim(p_dim_name);
67 clear_all_cache;
68 Exception when others then
69 log_n('Exception in drop_dim '||sqlerrm);
70 raise;
71 End;
72
73 /*
74 in this api, we need to loop and search because a level might have been in a diff dim before
75 so we need to get the list of all cc dim for the levels that are now part of p_dimension
76 */
77 procedure get_ccdim_for_levels(
78 p_dimension bsc_aw_adapter_dim.dimension_r,
79 p_dim_list out nocopy dbms_sql.varchar2_table
80 ) is
81 --
82 l_dim dbms_sql.varchar2_table;
83 --
84 Begin
85 p_dim_list.delete;
86 if g_debug then
87 log_n('CCDIM for levels');
88 end if;
89 for i in 1..p_dimension.level_groups.count loop
90 for j in 1..p_dimension.level_groups(i).levels.count loop
91 l_dim.delete;
92 get_dims_for_level(p_dimension.level_groups(i).levels(j).level_name,l_dim);
93 bsc_aw_utility.merge_array(p_dim_list,l_dim);
94 end loop;
95 end loop;
96 Exception when others then
97 log_n('Exception in get_ccdim_for_levels '||sqlerrm);
98 raise;
99 End;
100
101 /*
102 given a dim level, find the CC dim
103 note>>> this only returns the latest un-corrected dim. used in BSCAWAKB.pls, BSCAWLDB.pls
104 */
105 procedure get_dim_for_level(p_level varchar2,p_dim out nocopy varchar2) is
106 l_oo_dim bsc_aw_md_wrapper.bsc_olap_object_tb;
107 l_oo_level bsc_aw_md_wrapper.bsc_olap_object_tb;
108 Begin
109 get_bsc_olap_object(p_level,'dimension level',null,null,l_oo_level);
110 --there can multiple dim in l_oo_level. only 1 is un-corrected
111 for i in 1..l_oo_level.count loop
112 if l_oo_level(i).parent_object_type='dimension' then
113 l_oo_dim.delete;
114 get_bsc_olap_object(l_oo_level(i).parent_object,'dimension',l_oo_level(i).parent_object,'dimension',l_oo_dim);
115 if nvl(bsc_aw_utility.get_parameter_value(l_oo_dim(1).property1,'corrected',','),'N')='N' then
116 p_dim:=l_oo_dim(1).object;
117 return;
118 end if;
119 end if;
120 end loop;
121 Exception when others then
122 log_n('Exception in get_dim_for_level '||sqlerrm);
123 raise;
124 End;
125
126 /*
127 given a dim level, find all CC dims
128 */
129 procedure get_dims_for_level(p_level varchar2,p_dim out nocopy dbms_sql.varchar2_table) is
130 l_oo_level bsc_aw_md_wrapper.bsc_olap_object_tb;
131 Begin
132 get_bsc_olap_object(p_level,'dimension level',null,null,l_oo_level);
133 --there can multiple dim in l_oo_level. only 1 is un-corrected
134 for i in 1..l_oo_level.count loop
135 if l_oo_level(i).parent_object_type='dimension' then
136 p_dim(p_dim.count+1):=l_oo_level(i).parent_object;
137 end if;
138 end loop;
139 Exception when others then
140 log_n('Exception in get_dims_for_level '||sqlerrm);
141 raise;
142 End;
143
144 /*
145 here we convert from one format to the other, from bsc_aw_adapter_dim.dimension_r to
146 bsc_aw_md_wrapper.dimension_r
147 */
148 procedure create_dim_objects(
149 p_dimension bsc_aw_adapter_dim.dimension_r
150 ) is
151 Begin
152 bsc_aw_md_wrapper.create_dim(p_dimension);
153 clear_all_cache;
154 Exception when others then
155 log_n('Exception in create_dim_objects '||sqlerrm);
156 raise;
157 End;
158
159 /*
160 get the position of a level. this will be used from the UI module to see the positions of the levels and see
161 if agg on the fly is reqd
162 */
163 function get_level_position(
164 p_dim_level varchar2
165 ) return number is
166 l_position number;
167 Begin
168 g_olap_object.delete;
169 get_bsc_olap_object(p_dim_level,'dimension level',null,null,g_olap_object);
170 l_position:=bsc_aw_utility.get_parameter_value(g_olap_object(1).property1,'position',',');
171 if l_position is null then
172 return null;
173 else
174 return to_number(l_position);
175 end if;
176 Exception when others then
177 log_n('Exception in get_level_position '||sqlerrm);
178 raise;
179 End;
180
181 procedure drop_kpi(p_kpi varchar2) is
182 Begin
183 clear_all_cache;
184 bsc_aw_md_wrapper.drop_kpi(p_kpi);
185 clear_all_cache;
186 Exception when others then
187 log_n('Exception in drop_kpi '||sqlerrm);
188 raise;
189 End;
190
191 procedure get_kpi_olap_objects(
192 p_kpi varchar2,
193 p_objects out nocopy bsc_aw_utility.object_tb,
194 p_type varchar2
195 ) is
196 Begin
197 g_olap_object.delete;
198 if p_type='all' then
199 get_bsc_olap_object(null,null,p_kpi,'kpi',g_olap_object);
200 else
201 get_bsc_olap_object(null,p_type,p_kpi,'kpi',g_olap_object);
202 end if;
203 for i in 1..g_olap_object.count loop
204 if g_olap_object(i).olap_object_type is not null then
205 p_objects(p_objects.count+1).object_name:=g_olap_object(i).olap_object;
206 p_objects(p_objects.count).object_type:=g_olap_object(i).olap_object_type;
207 end if;
208 end loop;
209 Exception when others then
210 log_n('Exception in get_kpi_olap_objects '||sqlerrm);
211 raise;
212 End;
213
214 /*
215 calendar is time dim. so it appears in the metadata like a dim
216 */
217 procedure delete_calendar(p_calendar bsc_aw_calendar.calendar_r) is
218 Begin
219 clear_all_cache;
220 drop_dim(p_calendar.dim_name);
221 clear_all_cache;
222 Exception when others then
223 log_n('Exception in delete_calendar '||sqlerrm);
224 raise;
225 End;
226
227 procedure create_calendar(p_calendar bsc_aw_calendar.calendar_r) is
228 Begin
229 bsc_aw_md_wrapper.create_calendar(p_calendar);
230 clear_all_cache;
231 Exception when others then
232 log_n('Exception in create_calendar '||sqlerrm);
233 raise;
234 End;
235
236 /*
237 given a dim find out all the properties
238 from bsc olap metadata
239 */
240 procedure get_dim_properties(p_dim in out nocopy bsc_aw_adapter_kpi.dim_r) is
241 l_property bsc_aw_utility.value_tb;
242 Begin
243 g_olap_object.delete;
244 --get_bsc_olap_object(p_dim.dim_name,'dimension',null,null,g_olap_object);
245 get_bsc_olap_object(null,null,p_dim.dim_name,'dimension',g_olap_object);
246 for i in 1..g_olap_object.count loop
247 if g_olap_object(i).object=p_dim.dim_name and g_olap_object(i).object_type='dimension' then
248 if g_olap_object(i).olap_object_type='concat dimension' then
249 p_dim.concat:='Y';
250 else
251 p_dim.concat:='N';
252 end if;
253 bsc_aw_utility.parse_parameter_values(g_olap_object(i).property1,',',l_property);
254 p_dim.property:=bsc_aw_utility.get_parameter_value(l_property,'dimension type'); --normal vs time
255 p_dim.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_property,'recursive'),'N'); --Y or N
256 p_dim.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_property,'normal hier'),'N');
257 p_dim.multi_level:=nvl(bsc_aw_utility.get_parameter_value(l_property,'multi level'),'N'); --Y or N
258 elsif g_olap_object(i).object_type='relation' then
259 --relation name can be null if there is no rollup on this dim
260 p_dim.relation_name:=g_olap_object(i).object;
261 elsif g_olap_object(i).object_type='level name dim' then
262 p_dim.level_name_dim:=g_olap_object(i).object;
263 elsif g_olap_object(i).object_type='base value cube' then
264 p_dim.base_value_cube:=g_olap_object(i).object;
265 end if;
266 end loop;
267 Exception when others then
268 log_n('Exception in get_dim_properties '||sqlerrm);
269 raise;
270 End;
271
272 /*
273 given a calenadar find out all the properties
274 from bsc olap metadata
275 Input : calendar.aw_dim like bsc_calendar_1 NOT the calendar id
276 */
277 procedure get_calendar_properties(p_calendar in out nocopy bsc_aw_adapter_kpi.calendar_r) is
278 l_property bsc_aw_utility.value_tb;
279 Begin
280 g_olap_object.delete;
281 get_bsc_olap_object(null,null,p_calendar.aw_dim,'dimension',g_olap_object);
282 for i in 1..g_olap_object.count loop
283 if g_olap_object(i).object=p_calendar.aw_dim and g_olap_object(i).object_type='dimension' then
284 p_calendar.calendar:=bsc_aw_utility.get_parameter_value(g_olap_object(i).property1,'calendar',',');
285 elsif g_olap_object(i).object_type='relation' then
286 p_calendar.relation_name:=g_olap_object(i).object;
287 elsif g_olap_object(i).object_type='denorm relation' then
288 p_calendar.denorm_relation_name:=g_olap_object(i).object;
289 elsif g_olap_object(i).object_type='level name dim' then
290 p_calendar.level_name_dim:=g_olap_object(i).object;
291 elsif g_olap_object(i).object_type='end period level name dim' then
292 p_calendar.end_period_level_name_dim:=g_olap_object(i).object;
293 elsif g_olap_object(i).object_type='end period relation' then
294 p_calendar.end_period_relation_name:=g_olap_object(i).object;
295 end if;
296 end loop;
297 Exception when others then
298 log_n('Exception in get_calendar_properties '||sqlerrm);
299 raise;
300 End;
301
302 /*
303 given a dim , get the full hier of the dim
304 the parent child relations are in bsc metadata. so why do we need an api here?
305 we need this because we need to see if there is any change to the parent child relation
306 bsc metadata will only contain the latest info
307 note>>>this loads parent child without reference to level groups
308 */
309 procedure get_dim_parent_child(p_dim varchar2,p_parent_child out nocopy bsc_aw_adapter_dim.dim_parent_child_tb) is
310 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
311 l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
312 Begin
313 l_oo.delete;
314 get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);
315 for i in 1..l_oo.count loop
316 l_oor.delete;
317 get_bsc_olap_object_relation(l_oo(i).object,l_oo(i).object_type,'parent level',p_dim,'dimension',l_oor);
318 for j in 1..l_oor.count loop
319 p_parent_child(p_parent_child.count+1).child_level:=l_oor(j).object;
320 p_parent_child(p_parent_child.count).parent_level:=l_oor(j).relation_object;
321 p_parent_child(p_parent_child.count).child_fk:=bsc_aw_utility.get_parameter_value(l_oor(j).property1,'fk',',');
322 p_parent_child(p_parent_child.count).parent_pk:=bsc_aw_utility.get_parameter_value(l_oor(j).property1,'pk',',');
323 end loop;
324 end loop;
325 Exception when others then
326 log_n('Exception in get_dim_parent_child '||sqlerrm);
327 raise;
328 End;
329
330 procedure get_bsc_olap_object(
331 p_object varchar2,
332 p_type varchar2,
333 p_parent_object varchar2,
334 p_parent_type varchar2,
335 p_bsc_olap_object out nocopy bsc_aw_md_wrapper.bsc_olap_object_tb
336 ) is
337 --
338 l_cache_found varchar2(20);
339 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
340 Begin
341 l_cache_found:=get_oo_cache(p_object,p_type,p_parent_object,p_parent_type,l_bsc_olap_object);
342 if l_cache_found='N' then
343 bsc_aw_md_wrapper.get_bsc_olap_object(p_object,p_type,p_parent_object,p_parent_type,l_bsc_olap_object);
344 add_oo_cache(p_object,p_type,p_parent_object,p_parent_type,l_bsc_olap_object);
345 end if;
346 p_bsc_olap_object:=l_bsc_olap_object;
347 Exception when others then
348 log_n('Exception in get_bsc_olap_object '||sqlerrm);
349 raise;
350 End;
351
352 function get_oo_cache(
353 p_object varchar2,
354 p_type varchar2,
355 p_parent_object varchar2,
356 p_parent_type varchar2,
357 p_bsc_olap_object out nocopy bsc_aw_md_wrapper.bsc_olap_object_tb
358 ) return varchar2 is
359 --
360 l_cache_found varchar2(20);
361 Begin
362 l_cache_found:='N';
363 for i in 1..g_oo_cache.count loop
364 if nvl(g_oo_cache(i).object,'^')=nvl(p_object,'^')
365 and nvl(g_oo_cache(i).object_type,'^')=nvl(p_type,'^')
366 and nvl(g_oo_cache(i).parent_object,'^')=nvl(p_parent_object,'^')
367 and nvl(g_oo_cache(i).parent_object_type,'^')=nvl(p_parent_type,'^') then
368 if g_oo_cache(i).bsc_olap_object.count > 0 then
369 p_bsc_olap_object:=g_oo_cache(i).bsc_olap_object;
370 l_cache_found:='Y';
371 end if;
372 exit;
373 end if;
374 end loop;
375 return l_cache_found;
376 Exception when others then
377 log_n('Exception in get_oo_cache '||sqlerrm);
378 raise;
379 End;
380
381 procedure add_oo_cache(
382 p_object varchar2,
383 p_type varchar2,
384 p_parent_object varchar2,
385 p_parent_type varchar2,
386 p_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb
387 ) is
388 --
389 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
390 Begin
391 l_bsc_olap_object:=p_bsc_olap_object;
392 g_oo_cache(g_oo_cache.count+1).object:=p_object;
393 g_oo_cache(g_oo_cache.count).object_type:=p_type;
394 g_oo_cache(g_oo_cache.count).parent_object:=p_parent_object;
395 g_oo_cache(g_oo_cache.count).parent_object_type:=p_parent_type;
396 g_oo_cache(g_oo_cache.count).bsc_olap_object:=l_bsc_olap_object;
397 Exception when others then
398 log_n('Exception in add_oo_cache '||sqlerrm);
399 raise;
400 End;
401
402 procedure get_bsc_olap_object_relation(
403 p_object varchar2,
404 p_object_type varchar2,
405 p_relation_type varchar2,
406 p_parent_object varchar2,
407 p_parent_object_type varchar2,
408 p_bsc_olap_object_relation out nocopy bsc_aw_md_wrapper.bsc_olap_object_relation_tb
409 ) is
410 --
411 l_cache_found varchar2(20);
412 l_bsc_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
413 Begin
414 l_cache_found:=get_oor_cache(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
415 if l_cache_found='N' then
416 bsc_aw_md_wrapper.get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,
417 l_bsc_olap_object_relation);
418 add_oor_cache(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
419 end if;
420 p_bsc_olap_object_relation:=l_bsc_olap_object_relation;
421 Exception when others then
422 log_n('Exception in get_bsc_olap_object_relation '||sqlerrm);
423 raise;
424 End;
425
426 function get_oor_cache(
427 p_object varchar2,
428 p_object_type varchar2,
429 p_relation_type varchar2,
430 p_parent_object varchar2,
431 p_parent_object_type varchar2,
432 p_bsc_olap_object_relation out nocopy bsc_aw_md_wrapper.bsc_olap_object_relation_tb
433 ) return varchar2 is
434 --
435 l_cache_found varchar2(20);
436 Begin
437 l_cache_found:='N';
438 for i in 1..g_oor_cache.count loop
439 if nvl(g_oor_cache(i).object,'^')=nvl(p_object,'^')
440 and nvl(g_oor_cache(i).object_type,'^')=nvl(p_object_type,'^')
441 and nvl(g_oor_cache(i).relation_type,'^')=nvl(p_relation_type,'^')
442 and nvl(g_oor_cache(i).parent_object,'^')=nvl(p_parent_object,'^')
443 and nvl(g_oor_cache(i).parent_object_type,'^')=nvl(p_parent_object_type,'^') then
444 if g_oor_cache(i).bsc_olap_object_relation.count > 0 then
445 p_bsc_olap_object_relation:=g_oor_cache(i).bsc_olap_object_relation;
446 l_cache_found:='Y';
447 end if;
448 exit;
449 end if;
450 end loop;
451 return l_cache_found;
452 Exception when others then
453 log_n('Exception in get_oor_cache '||sqlerrm);
454 raise;
455 End;
456
457 procedure add_oor_cache(
458 p_object varchar2,
459 p_object_type varchar2,
460 p_relation_type varchar2,
461 p_parent_object varchar2,
462 p_parent_object_type varchar2,
463 p_bsc_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb
464 ) is
465 --
466 l_bsc_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
467 Begin
468 l_bsc_olap_object_relation:=p_bsc_olap_object_relation;
469 g_oor_cache(g_oor_cache.count+1).object:=p_object;
470 g_oor_cache(g_oor_cache.count).object_type:=p_object_type;
471 g_oor_cache(g_oor_cache.count).relation_type:=p_relation_type;
472 g_oor_cache(g_oor_cache.count).parent_object:=p_parent_object;
473 g_oor_cache(g_oor_cache.count).parent_object_type:=p_parent_object_type;
474 g_oor_cache(g_oor_cache.count).bsc_olap_object_relation:=l_bsc_olap_object_relation;
475 Exception when others then
476 log_n('Exception in add_oor_cache '||sqlerrm);
477 raise;
478 End;
479
480 procedure create_kpi(p_kpi bsc_aw_adapter_kpi.kpi_r) is
481 Begin
482 bsc_aw_md_wrapper.create_kpi(p_kpi);
483 clear_all_cache;
484 Exception when others then
485 log_n('Exception in create_kpi '||sqlerrm);
486 raise;
487 End;
488
489 /*
490 given a kpi, find the dims
491 will be used in the ui module
492 */
493 procedure get_dim_for_kpi(
494 p_kpi varchar2,
495 p_dim_list out nocopy dbms_sql.varchar2_table
496 ) is
497 Begin
498 g_olap_object_relation.delete;
499 get_bsc_olap_object_relation(p_kpi,'kpi','kpi dimension',p_kpi,'kpi',g_olap_object_relation);
500 for i in 1..g_olap_object_relation.count loop
501 p_dim_list(i):=g_olap_object_relation(i).relation_object;
502 end loop;
503 Exception when others then
504 log_n('Exception in get_dim_for_kpi '||sqlerrm);
505 raise;
506 End;
507
508 --set the relation name, periodicity aw name and also the parent child info
509 procedure get_dim_set_calendar(
510 p_kpi bsc_aw_adapter_kpi.kpi_r,
511 p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r
512 ) is
513 --
514 l_periodicity dbms_sql.varchar2_table;
515 l_periodicity_type dbms_sql.varchar2_table;
516 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
517 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
518 Begin
519 --get the relation name
520 l_oo.delete;
521 get_bsc_olap_object(p_dim_set.calendar.aw_dim,'dimension',p_dim_set.calendar.aw_dim,'dimension',l_oo);
522 get_calendar_properties(p_dim_set.calendar);
523 --from bsc olap metadata, get the properties of the periodicities
524 l_oo.delete;
525 get_bsc_olap_object(null,'dimension level',p_dim_set.calendar.aw_dim,'dimension',l_oo);
526 for i in 1..l_oo.count loop
527 l_periodicity(i):=null;
528 l_periodicity_type(i):=null;
529 l_periodicity(i):=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity',',');
530 l_periodicity_type(i):=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity_type',',');
531 end loop;
532 for i in 1..p_dim_set.calendar.periodicity.count loop
533 if p_dim_set.calendar.periodicity(i).aw_dim is null then
534 for j in 1..l_periodicity.count loop
535 if to_number(l_periodicity(j))=p_dim_set.calendar.periodicity(i).periodicity then
536 p_dim_set.calendar.periodicity(i).aw_dim:=l_oo(j).object;
537 p_dim_set.calendar.periodicity(i).periodicity_type:=l_periodicity_type(j);
538 exit;
539 end if;
540 end loop;
541 elsif p_dim_set.calendar.periodicity(i).periodicity is null then
542 for j in 1..l_oo.count loop
543 if p_dim_set.calendar.periodicity(i).aw_dim=l_oo(j).object then
544 p_dim_set.calendar.periodicity(i).periodicity:=to_number(l_periodicity(j));
545 exit;
546 end if;
547 end loop;
548 end if;
549 end loop;
550 --
551 --fill parent child info
552 l_olap_object_relation.delete;
553 p_dim_set.calendar.parent_child.delete;
554 get_bsc_olap_object_relation(null,null,'parent level',p_dim_set.calendar.aw_dim,'dimension',l_olap_object_relation);
555 for i in 1..l_olap_object_relation.count loop
556 p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count+1).parent_dim_name:=l_olap_object_relation(i).relation_object;
557 p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count).child_dim_name:=l_olap_object_relation(i).object;
558 p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count).parent:=to_number(
559 bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'parent periodicity',','));
560 p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count).child:=to_number(
561 bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'child periodicity',','));
562 end loop;
563 Exception when others then
564 log_n('Exception in get_dim_set_calendar '||sqlerrm);
565 raise;
566 End;
567
568 function is_kpi_present(
569 p_kpi varchar2
570 )return boolean is
571
572 Begin
573 g_olap_object.delete;
574 get_bsc_olap_object(p_kpi,'kpi',p_kpi,'kpi',g_olap_object);
575 if g_olap_object.count>0 then
576 return true;
577 else
578 return false;
579 end if;
580 Exception when others then
581 log_n('Exception in is_kpi_present '||sqlerrm);
582 raise;
583 End;
584
585 procedure get_kpi_dimset(
586 p_kpi varchar2,
587 p_bsc_olap_object out nocopy bsc_aw_md_wrapper.bsc_olap_object_tb
588 ) is
589 Begin
590 get_bsc_olap_object(null,'kpi dimension set',p_kpi,'kpi',p_bsc_olap_object);
591 Exception when others then
592 log_n('Exception in get_kpi_dimset '||sqlerrm);
593 raise;
594 End;
595
596 --only Actual dimset not targets
597 procedure get_kpi_dimset_actual(
598 p_kpi varchar2,
599 p_bsc_olap_object out nocopy bsc_aw_md_wrapper.bsc_olap_object_tb
600 ) is
601 --
602 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
603 Begin
604 get_kpi_dimset(p_kpi,l_bsc_olap_object);
605 for i in 1..l_bsc_olap_object.count loop
606 if bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set type',',')='actual' then
607 p_bsc_olap_object(p_bsc_olap_object.count+1):=l_bsc_olap_object(i);
608 end if;
609 end loop;
610 Exception when others then
611 log_n('Exception in get_kpi_dimset_actual '||sqlerrm);
612 raise;
613 End;
614
615 /*
616 given a kpi and a dimset, get all the base table loading the dimset
617 */
618 procedure get_dimset_base_table(
619 p_kpi varchar2,
620 p_dimset varchar2,
621 p_base_table_type varchar2,--"base table dim set"
622 p_olap_object_relation out nocopy bsc_aw_md_wrapper.bsc_olap_object_relation_tb
623 ) is
624 Begin
625 g_olap_object_relation.delete;
626 --get full relations for the kpi. then find out the base tables for the dimset
627 get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',g_olap_object_relation);
628 if p_base_table_type is not null then
629 for i in 1..g_olap_object_relation.count loop
630 if g_olap_object_relation(i).object_type='base table' and g_olap_object_relation(i).relation_type=p_base_table_type
631 and g_olap_object_relation(i).relation_object=p_dimset then
632 p_olap_object_relation(p_olap_object_relation.count+1):=g_olap_object_relation(i);
633 end if;
634 end loop;
635 else
636 for i in 1..g_olap_object_relation.count loop
637 if g_olap_object_relation(i).object_type='base table' and g_olap_object_relation(i).relation_type='base table dim set'
638 and g_olap_object_relation(i).relation_object=p_dimset then
639 p_olap_object_relation(p_olap_object_relation.count+1):=g_olap_object_relation(i);
640 end if;
641 end loop;
642 end if;
643 Exception when others then
644 log_n('Exception in get_dimset_base_table '||sqlerrm);
645 raise;
646 End;
647
648 /*
649 given a kpi and a base table, find out all the dimsets using the base table
650 */
651 procedure get_base_table_dimset(
652 p_kpi varchar2,
653 p_base_table varchar2,
654 p_base_table_type varchar2,--"base table dim set"
655 p_olap_object_relation out nocopy bsc_aw_md_wrapper.bsc_olap_object_relation_tb
656 ) is
657 Begin
658 g_olap_object_relation.delete;
659 --get full relations for the kpi. then find out the dimsets for the base table
660 get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',g_olap_object_relation);
661 if p_base_table_type is not null then
662 for i in 1..g_olap_object_relation.count loop
663 if g_olap_object_relation(i).object_type='base table' and g_olap_object_relation(i).relation_type=p_base_table_type
664 and g_olap_object_relation(i).object=p_base_table then
665 p_olap_object_relation(p_olap_object_relation.count+1):=g_olap_object_relation(i);
666 end if;
667 end loop;
668 else
669 for i in 1..g_olap_object_relation.count loop
670 if g_olap_object_relation(i).object_type='base table' and g_olap_object_relation(i).relation_type='base table dim set'
671 and g_olap_object_relation(i).object=p_base_table then
672 p_olap_object_relation(p_olap_object_relation.count+1):=g_olap_object_relation(i);
673 end if;
674 end loop;
675 end if;
676 Exception when others then
677 log_n('Exception in get_base_table_dimset '||sqlerrm);
678 raise;
679 End;
680
681 procedure get_dimset_measure(
682 p_kpi varchar2,
683 p_dimset varchar2,
684 p_measure out nocopy bsc_aw_adapter_kpi.measure_tb
685 ) is
686 --
687 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
688 l_values bsc_aw_utility.value_tb;
689 l_property varchar2(2000);
690 Begin
691 get_bsc_olap_object_relation(p_dimset,'kpi dimension set','dim set measure',p_kpi,'kpi',l_olap_object_relation);
692 --l_agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
693 for i in 1..l_olap_object_relation.count loop
694 p_measure(p_measure.count+1).measure:=l_olap_object_relation(i).relation_object;
695 p_measure(p_measure.count).measure_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measure type',',');
696 p_measure(p_measure.count).forecast:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast',',');
697 p_measure(p_measure.count).forecast_method:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast method',',');
698 p_measure(p_measure.count).sql_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'sql aggregated',','),'N');
699 p_measure(p_measure.count).agg_formula.agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
700 p_measure(p_measure.count).agg_formula.std_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'std agg',',');
701 p_measure(p_measure.count).agg_formula.avg_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'avg agg',',');
702 l_values.delete;
703 bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
704 'agg formula cubes',','),'+',l_values);
705 if l_values.count>0 then
706 for j in 1..l_values.count loop
707 p_measure(p_measure.count).agg_formula.cubes(p_measure(p_measure.count).agg_formula.cubes.count+1):=l_values(j).parameter;
708 end loop;
709 end if;
710 l_values.delete;
711 bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
712 'agg formula measures',','),'+',l_values);
713 if l_values.count>0 then
714 for j in 1..l_values.count loop
715 p_measure(p_measure.count).agg_formula.measures(p_measure(p_measure.count).agg_formula.measures.count+1):=l_values(j).parameter;
716 end loop;
717 end if;
718 --cannot change get_parameter_value to scanning bsc_olap_object even though bsc_olap_object has the cube and fcst cube etc
719 --in bsc_olap_object, we cannot know which measure belongs to which dimset
720 p_measure(p_measure.count).cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube',',');
721 p_measure(p_measure.count).fcst_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'fcst cube',',');
722 p_measure(p_measure.count).countvar_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'countvar cube',',');
723 p_measure(p_measure.count).display_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'display cube',',');
724 l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'balance loaded column',',');
725 if l_property is not null then
726 bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'balance loaded column',null,l_property);
727 end if;
728 l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'year cube',',');
729 if l_property is not null then
730 bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'year cube',null,l_property);
731 end if;
732 l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'period cube',',');
733 if l_property is not null then
734 bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'period cube',null,l_property);
735 end if;
736 --in 10g we have composites per measure
737 end loop;
738 Exception when others then
739 log_n('Exception in get_dimset_measure '||sqlerrm);
740 raise;
741 End;
742
743 /*
744 this procedure reads all metadata for a dimset from olap metadata
745 loads all info of a dimset
746 this api is important. will be used by kpi data loading, forecasting, aggregations etc
747 for now, we will keep this only for aggregation and forecasting
748 this means we are not concerned about the data source
749
750 when we aggregate , we dont need the agg maps defined per dim . we need the agg maps defined per
751 dim only when we aggregate in the UI
752 */
753 procedure get_kpi_dimset_md(
754 p_kpi varchar2,
755 p_dimset_name varchar2,
756 p_dimset out nocopy bsc_aw_adapter_kpi.dim_set_r
757 ) is
758 --
759 l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
760 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
761 l_num number;
762 Begin
763 --get all properties of the kpi. then loop through
764 get_bsc_olap_object(p_dimset_name,'kpi dimension set',p_kpi,'kpi',l_olap_object);
765 get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
766 p_dimset.dim_set_name:=p_dimset_name;
767 --dimset properties
768 p_dimset.dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set',',');
769 p_dimset.dim_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set type',',');
770 p_dimset.base_dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'base dim set',',');
771 p_dimset.targets_higher_levels:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'targets',','),'N');
772 p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
773 p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
774 p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
775 p_dimset.number_partitions:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'number partitions',',');
776 p_dimset.partition_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition type',',');
777 p_dimset.compressed:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'compressed',',');
778 p_dimset.pre_calculated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'pre calculated',','),'N');
779 --we are not looking at agg maps defined per dim
780 --agg_map_average is usedfor AVERAGE aggregation
781 --agg_map_notime is used for balance measures
782 for i in 1..l_olap_object_relation.count loop
783 if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set agg map' then
784 p_dimset.agg_map.agg_map:=l_olap_object_relation(i).relation_object;
785 p_dimset.agg_map.created:='Y';
786 elsif l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set agg map notime' then
787 p_dimset.agg_map_notime.agg_map:=l_olap_object_relation(i).relation_object;
788 p_dimset.agg_map_notime.created:='Y';
789 elsif l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='agg map measure dim' then
790 p_dimset.aggmap_operator.measure_dim:=l_olap_object_relation(i).relation_object;
791 elsif l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='agg map opvar' then
792 p_dimset.aggmap_operator.opvar:=l_olap_object_relation(i).relation_object;
793 elsif l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='agg map argvar' then
794 p_dimset.aggmap_operator.argvar:=l_olap_object_relation(i).relation_object;
795 elsif l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='aggregate marker program' then
796 p_dimset.aggregate_marker_program:=l_olap_object_relation(i).relation_object;
797 elsif l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='LB resync program' then
798 p_dimset.LB_resync_program:=l_olap_object_relation(i).relation_object;
799 end if;
800 end loop;
801 --get the dim
802 get_kpi_dimset_dim_md(p_kpi,p_dimset_name,p_dimset.dim,p_dimset.std_dim);
803 --get calendar metadata
804 get_kpi_dimset_calendar_md(p_kpi,p_dimset_name,p_dimset.calendar);
805 --get the partition and composite info
806 get_dimset_comp_PT(p_kpi,p_dimset_name,p_dimset.partition_template,p_dimset.composite);
807 --get the cube info
808 get_dimset_cube_set(p_kpi,p_dimset_name,p_dimset.cube_set);
809 --get the measure information
810 get_dimset_measure(p_kpi,p_dimset_name,p_dimset.measure);
811 Exception when others then
812 log_n('Exception in get_kpi_dimset_md '||sqlerrm);
813 raise;
814 End;
815
816 /*
817 get the partition template and composite info
818 */
819 procedure get_dimset_comp_PT(
820 p_kpi varchar2,
821 p_dimset_name varchar2,
822 p_partition_template out nocopy bsc_aw_adapter_kpi.partition_template_tb,
823 p_composite out nocopy bsc_aw_adapter_kpi.composite_tb) is
824 --
825 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
826 Begin
827 get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
828 for i in 1..l_olap_object_relation.count loop
829 if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set partition template' then
830 p_partition_template(p_partition_template.count+1).template_name:=l_olap_object_relation(i).relation_object;
831 p_partition_template(p_partition_template.count).template_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
832 'template type',',');
833 p_partition_template(p_partition_template.count).template_use:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
834 'template use',',');
835 p_partition_template(p_partition_template.count).template_dim:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
836 'template dim',',');
837 --now, the template partitions
838 for j in 1..l_olap_object_relation.count loop
839 if l_olap_object_relation(j).object=p_partition_template(p_partition_template.count).template_name and
840 l_olap_object_relation(j).relation_type='partition template partition' then
841 p_partition_template(p_partition_template.count).template_partitions(
842 p_partition_template(p_partition_template.count).template_partitions.count+1).partition_name:=l_olap_object_relation(j).relation_object;
843 p_partition_template(p_partition_template.count).template_partitions(
844 p_partition_template(p_partition_template.count).template_partitions.count).partition_dim_value:=
845 bsc_aw_utility.get_parameter_value(l_olap_object_relation(j).property1,'partition dim value',',');
846 end if;
847 end loop;
848 --we are not loading the partition axis info here. this info is not really reqd
849 end if;
850 end loop;
851 --get the composite info
852 for i in 1..l_olap_object_relation.count loop
853 if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set measure composite' then
854 p_composite(p_composite.count+1).composite_name:=l_olap_object_relation(i).relation_object;
855 p_composite(p_composite.count).composite_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'composite type',',');
856 end if;
857 end loop;
858 Exception when others then
859 log_n('Exception in get_dimset_comp_PT '||sqlerrm);
860 raise;
861 End;
862
863 /*
864 procedure to get the cube info
865 */
866 procedure get_dimset_cube_set(
867 p_kpi varchar2,
868 p_dimset_name varchar2,
869 p_cube_set out nocopy bsc_aw_adapter_kpi.cube_set_tb) is
870 --
871 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
872 Begin
873 get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
874 for i in 1..l_olap_object_relation.count loop
875 if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set cube set' then
876 p_cube_set(p_cube_set.count+1).cube_set_name:=l_olap_object_relation(i).relation_object;
877 p_cube_set(p_cube_set.count).cube_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube set type',',');
878 p_cube_set(p_cube_set.count).measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measurename dim',',');
879 --cubes
880 get_dimset_cube(p_kpi,p_dimset_name,p_cube_set(p_cube_set.count).cube_set_name,'cube set measure cube',
881 p_cube_set(p_cube_set.count).cube);
882 get_dimset_cube(p_kpi,p_dimset_name,p_cube_set(p_cube_set.count).cube_set_name,'cube set countvar cube',
883 p_cube_set(p_cube_set.count).countvar_cube);
884 get_dimset_cube(p_kpi,p_dimset_name,p_cube_set(p_cube_set.count).cube_set_name,'cube set display cube',
885 p_cube_set(p_cube_set.count).display_cube);
886 end if;
887 end loop;
888 Exception when others then
889 log_n('Exception in get_dimset_cube_set '||sqlerrm);
890 raise;
891 End;
892
893 /*
894 p_cube_type is cube set measure cube and cube set countvar cube
895 */
896 procedure get_dimset_cube(
897 p_kpi varchar2,
898 p_dimset_name varchar2,
899 p_cube_set_name varchar2,
900 p_cube_type varchar2,
901 p_cube out nocopy bsc_aw_adapter_kpi.cube_r) is
902 --
903 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
904 Begin
905 get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
906 for i in 1..l_olap_object_relation.count loop
907 if l_olap_object_relation(i).object=p_cube_set_name and l_olap_object_relation(i).relation_type=p_cube_type then
908 p_cube.cube_name:=l_olap_object_relation(i).relation_object;
909 p_cube.cube_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube type',',');
910 p_cube.cube_datatype:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube datatype',',');
911 end if;
912 end loop;
913 --get the axis
914 for i in 1..l_olap_object_relation.count loop
915 if l_olap_object_relation(i).object=p_cube.cube_name and l_olap_object_relation(i).relation_type='cube axis' then
916 p_cube.cube_axis(p_cube.cube_axis.count+1).axis_name:=l_olap_object_relation(i).relation_object;
917 p_cube.cube_axis(p_cube.cube_axis.count).axis_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'axis type',',');
918 end if;
919 end loop;
920 Exception when others then
921 log_n('Exception in get_dimset_cube '||sqlerrm);
922 raise;
923 End;
924
925 /*
926 get dimset dim properties
927 */
928 procedure get_kpi_dimset_dim_md(
929 p_kpi varchar2,
930 p_dimset_name varchar2,
931 p_dim out nocopy bsc_aw_adapter_kpi.dim_tb,
932 p_std_dim out nocopy bsc_aw_adapter_kpi.dim_tb
933 ) is
934 --
935 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
936 Begin
937 get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
938 for i in 1..l_olap_object_relation.count loop
939 if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set dim' then
940 p_dim(p_dim.count+1).dim_name:=l_olap_object_relation(i).relation_object;
941 get_kpi_dimset_dim_md(p_kpi,p_dimset_name,p_dim(p_dim.count),l_olap_object_relation(i).relation_type,
942 'dim set dim level');
943 elsif l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set std dim' then
944 p_std_dim(p_std_dim.count+1).dim_name:=l_olap_object_relation(i).relation_object;
945 get_kpi_dimset_dim_md(p_kpi,p_dimset_name,p_std_dim(p_std_dim.count),l_olap_object_relation(i).relation_type,
946 'dim set dim level');
947 end if;
948 end loop;
949 Exception when others then
950 log_n('Exception in get_kpi_dimset_dim_md '||sqlerrm);
951 raise;
952 End;
953
954 /*
955 given a dim, get the properties and levels
956 p_dim.dim_name is known
957 p_dim.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',') is reqd since in
958 bsc_olap_object we do not know which limit cube is tied to which dim
959
960 zero code is stored at the dim level, not at the dim level
961 p_level_type is "dim set dim level"
962 */
963 procedure get_kpi_dimset_dim_md(
964 p_kpi varchar2,
965 p_dimset_name varchar2,
966 p_dim in out nocopy bsc_aw_adapter_kpi.dim_r,
967 p_dim_type varchar2,
968 p_level_type varchar2
969 ) is
970 --
971 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
972 l_oor_zero_code bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
973 l_oor_rec_level bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
974 l_index number;
975 Begin
976 get_dim_properties(p_dim);--property,rec,multi level,leven name dim
977 --get the levels
978 --limit cube=kpi_3014_2_BSC_CCDIM_100.limit.bool,agg map=aggmap_BSC_CCDIM_100_2_3014
979 get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
980 for i in 1..l_olap_object_relation.count loop
981 if l_olap_object_relation(i).relation_type=p_dim_type
982 and l_olap_object_relation(i).relation_object=p_dim.dim_name and l_olap_object_relation(i).object=p_dimset_name then
983 p_dim.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
984 p_dim.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
985 p_dim.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
986 p_dim.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
987 p_dim.agg_level:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg level',',');
988 exit;
989 end if;
990 end loop;
991 --get the levels
992 --need only level name and position
993 --no...we also need the parent child relations...the issue of diamond hierarchies and the need to specify the
994 --correct parent.child when limiting level name dim
995 --first gte the lowest level
996 p_dim.levels.delete;
997 p_dim.levels(p_dim.levels.count+1).level_name:=null;--init
998 for i in 1..l_olap_object_relation.count loop
999 if l_olap_object_relation(i).relation_type=p_level_type
1000 and l_olap_object_relation(i).object=p_dim.dim_name||'+'||p_dimset_name then
1001 if instr(l_olap_object_relation(i).property1,'lowest level')>0 then
1002 l_index:=1;
1003 else
1004 l_index:=p_dim.levels.count+1;--start with a min of 2
1005 end if;
1006 p_dim.levels(l_index).level_name:=l_olap_object_relation(i).relation_object;
1007 --p_dim.levels(l_index).position:=nvl(get_level_position(p_dim.levels(l_index).level_name),1);
1008 p_dim.levels(l_index).position:=nvl(to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'position',',')),1);
1009 p_dim.levels(l_index).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'aggregated',','),'Y');
1010 p_dim.levels(l_index).zero_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero_aggregated',','),'Y');
1011 /*nvl(Y) for zero_aggregated for backward compatibility */
1012 p_dim.levels(l_index).zero_code:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero code',','),'N'); --Y or N
1013 if p_dim.levels(l_index).zero_code='Y' then
1014 l_oor_zero_code.delete;
1015 get_bsc_olap_object_relation(p_dim.levels(l_index).level_name,'dimension level','zero code level',p_dim.dim_name,'dimension',l_oor_zero_code);
1016 p_dim.levels(l_index).zero_code_level:=l_oor_zero_code(1).relation_object;
1017 end if;
1018 if p_dim.recursive='Y' then
1019 l_oor_rec_level.delete;
1020 get_bsc_olap_object_relation(p_dim.levels(l_index).level_name,'dimension level','recursive parent level',p_dim.dim_name,'dimension',l_oor_rec_level);
1021 p_dim.levels(l_index).rec_parent_level:=l_oor_rec_level(1).relation_object;
1022 end if;
1023 end if;
1024 end loop;
1025 --
1026 l_olap_object_relation.delete;
1027 get_bsc_olap_object_relation(null,null,'parent level',p_dim.dim_name,'dimension',l_olap_object_relation);
1028 for i in 1..l_olap_object_relation.count loop
1029 --we add the parent child relation if both the parent and the child are levels of the kpi
1030 if is_level_in_dim(p_dim,l_olap_object_relation(i).relation_object) and
1031 is_level_in_dim(p_dim,l_olap_object_relation(i).object) then
1032 p_dim.parent_child(p_dim.parent_child.count+1).parent_level:=l_olap_object_relation(i).relation_object;
1033 p_dim.parent_child(p_dim.parent_child.count).child_level:=l_olap_object_relation(i).object;
1034 end if;
1035 end loop;
1036 Exception when others then
1037 log_n('Exception in get_kpi_dimset_dim_md '||sqlerrm);
1038 raise;
1039 End;
1040
1041 function is_level_in_dim(
1042 p_dim bsc_aw_adapter_kpi.dim_r,
1043 p_level varchar2) return boolean is
1044 Begin
1045 for i in 1..p_dim.levels.count loop
1046 if p_dim.levels(i).level_name=p_level then
1047 return true;
1048 end if;
1049 end loop;
1050 return false;
1051 Exception when others then
1052 log_n('Exception in is_level_in_dim '||sqlerrm);
1053 raise;
1054 End;
1055
1056 /*
1057 given a dimset, get all the calendar metadata
1058 */
1059 procedure get_kpi_dimset_calendar_md(
1060 p_kpi varchar2,
1061 p_dimset_name varchar2,
1062 p_calendar out nocopy bsc_aw_adapter_kpi.calendar_r
1063 ) is
1064 --
1065 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
1066 l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
1067 l_index number;
1068 Begin
1069 --dim set calendar
1070 --
1071 get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
1072 for i in 1..l_olap_object_relation.count loop
1073 if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set calendar' then
1074 p_calendar.aw_dim:=l_olap_object_relation(i).relation_object;
1075 get_calendar_properties(p_calendar);
1076 p_calendar.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
1077 p_calendar.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
1078 p_calendar.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
1079 p_calendar.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
1080 exit;
1081 end if;
1082 end loop;
1083 --get the level name dim
1084 get_bsc_olap_object(p_calendar.aw_dim,'dimension',p_calendar.aw_dim,'dimension',l_olap_object);
1085 get_calendar_properties(p_calendar);
1086 --get the periodicities
1087 --remember...for periodicities, lowest level is not periodicity(1). its indicated by the property lowest level only
1088 for i in 1..l_olap_object_relation.count loop
1089 if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set periodicity' then
1090 p_calendar.periodicity(p_calendar.periodicity.count+1).aw_dim:=l_olap_object_relation(i).relation_object;
1091 p_calendar.periodicity(p_calendar.periodicity.count).periodicity:=to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1092 'periodicity',','));
1093 p_calendar.periodicity(p_calendar.periodicity.count).lowest_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1094 'lowest level',','),'N');
1095 p_calendar.periodicity(p_calendar.periodicity.count).missing_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1096 'missing level',','),'N');
1097 p_calendar.periodicity(p_calendar.periodicity.count).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1098 'aggregated',','),'Y');
1099 p_calendar.periodicity(p_calendar.periodicity.count).periodicity_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1100 'periodicity_type',',');
1101 end if;
1102 end loop;
1103 --load the periodicity relations
1104 l_olap_object_relation.delete;
1105 p_calendar.parent_child.delete;
1106 get_bsc_olap_object_relation(null,null,'parent level',p_calendar.aw_dim,'dimension',l_olap_object_relation);
1107 for i in 1..l_olap_object_relation.count loop
1108 if is_periodicity_in_dim(p_calendar,l_olap_object_relation(i).relation_object) and
1109 is_periodicity_in_dim(p_calendar,l_olap_object_relation(i).object) then
1110 p_calendar.parent_child(p_calendar.parent_child.count+1).parent_dim_name:=l_olap_object_relation(i).relation_object;
1111 p_calendar.parent_child(p_calendar.parent_child.count).child_dim_name:=l_olap_object_relation(i).object;
1112 p_calendar.parent_child(p_calendar.parent_child.count).parent:=to_number(
1113 bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'parent periodicity',','));
1114 p_calendar.parent_child(p_calendar.parent_child.count).child:=to_number(
1115 bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'child periodicity',','));
1116 end if;
1117 end loop;
1118 Exception when others then
1119 log_n('Exception in get_kpi_dimset_calendar_md '||sqlerrm);
1120 raise;
1121 End;
1122
1123 function is_periodicity_in_dim(
1124 p_calendar bsc_aw_adapter_kpi.calendar_r,
1125 p_periodicty_dim varchar2
1126 )return boolean is
1127 Begin
1128 for i in 1..p_calendar.periodicity.count loop
1129 if p_calendar.periodicity(i).aw_dim=p_periodicty_dim then
1130 return true;
1131 end if;
1132 end loop;
1133 return false;
1134 Exception when others then
1135 log_n('Exception in is_periodicity_in_dim '||sqlerrm);
1136 raise;
1137 End;
1138
1139 /*
1140 this procedure sets the aggregation_r datatype in bsc_aw_load_kpi
1141 input : kpi
1142 find out all the dimsets for the kpi.
1143 the dimsets will include both actuals and targets
1144 */
1145 procedure get_aggregation_r(p_aggregation in out nocopy bsc_aw_load_kpi.aggregation_r) is
1146 --
1147 l_kpi bsc_aw_adapter_kpi.kpi_r;
1148 Begin
1149 l_kpi.kpi:=p_aggregation.kpi;
1150 get_kpi(l_kpi);
1151 --
1152 p_aggregation.parent_kpi:=l_kpi.parent_kpi;
1153 --
1154 for i in 1..l_kpi.dim_set.count loop
1155 p_aggregation.dim_set(p_aggregation.dim_set.count+1):=l_kpi.dim_set(i);
1156 end loop;
1157 for i in 1..l_kpi.target_dim_set.count loop
1158 p_aggregation.dim_set(p_aggregation.dim_set.count+1):=l_kpi.target_dim_set(i);
1159 end loop;
1160 Exception when others then
1161 log_n('Exception in get_aggregation_r '||sqlerrm);
1162 raise;
1163 End;
1164
1165 /*input is the kpi name. all other metadata is populated
1166 */
1167 procedure get_kpi(p_kpi in out nocopy bsc_aw_adapter_kpi.kpi_r) is
1168 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
1169 l_dim_set varchar2(300);
1170 Begin
1171 --get kpi properties
1172 if g_debug then
1173 log('In get_kpi '||p_kpi.kpi);
1174 end if;
1175 get_bsc_olap_object(p_kpi.kpi,'kpi',p_kpi.kpi,'kpi',l_oo);
1176 if l_oo.count=0 then
1177 log('Could not find kpi info');
1178 raise bsc_aw_utility.g_exception;
1179 end if;
1180 p_kpi.parent_kpi:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'parent kpi',','); --could be null
1181 p_kpi.calendar:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'calendar',',');
1182 --now the dimsets
1183 l_oo.delete;
1184 get_kpi_dimset(p_kpi.kpi,l_oo);
1185 for i in 1..l_oo.count loop
1186 if bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dim set type',',')='actual' then
1187 l_dim_set:=l_oo(i).object;
1188 get_kpi_dimset_md(p_kpi.kpi,l_dim_set,p_kpi.dim_set(p_kpi.dim_set.count+1));
1189 end if;
1190 end loop;
1191 --targets
1192 for i in 1..l_oo.count loop
1193 if bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dim set type',',')='target' then
1194 l_dim_set:=l_oo(i).object;
1195 get_kpi_dimset_md(p_kpi.kpi,l_dim_set,p_kpi.target_dim_set(p_kpi.target_dim_set.count+1));
1196 end if;
1197 end loop;
1198 --
1199 Exception when others then
1200 log_n('Exception in get_kpi '||sqlerrm);
1201 raise;
1202 End;
1203
1204 -----------------------------
1205 procedure create_workspace(p_name varchar2) is
1206 Begin
1207 bsc_aw_md_wrapper.create_workspace(p_name);
1208 Exception when others then
1209 log_n('Exception in create_workspace '||sqlerrm);
1210 raise;
1211 End;
1212
1213 procedure drop_workspace(p_name varchar2) is
1214 Begin
1215 clear_all_cache;
1216 bsc_aw_md_wrapper.drop_workspace(p_name);
1217 clear_all_cache;
1218 Exception when others then
1219 log_n('Exception in create_workspace '||sqlerrm);
1220 raise;
1221 End;
1222
1223 function check_workspace(p_workspace_name varchar2) return varchar2 is
1224 l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
1225 Begin
1226 get_bsc_olap_object(p_workspace_name,'aw workspace','BSC','APPS',l_olap_object);
1227 if l_olap_object.count>0 then
1228 return 'Y';
1229 else
1230 return 'N';
1231 end if;
1232 Exception when others then
1233 log_n('Exception in check_workspace '||sqlerrm);
1234 raise;
1235 End;
1236
1237 /*
1238 p_object varchar2,
1239 p_object_type varchar2,
1240 p_parent_object varchar2,
1241 p_parent_object_type varchar2,
1242 are mandatory
1243 optional (match or set)
1244 p_olap_object varchar2,
1245 p_olap_object_type varchar2,
1246 p_property varchar2,
1247 p_operation_flag varchar2
1248 */
1249 procedure update_olap_object(
1250 p_object varchar2,
1251 p_object_type varchar2,
1252 p_parent_object varchar2,
1253 p_parent_object_type varchar2,
1254 p_match_columns varchar2, --comma separated
1255 p_match_values varchar2, --comma separated
1256 p_set_columns varchar2, --comma separated
1257 p_set_values varchar2 --^ separated. since values can contain , inside
1258 ) is
1259 --
1260 l_match_columns dbms_sql.varchar2_table;
1261 l_match_values dbms_sql.varchar2_table;
1262 l_set_columns dbms_sql.varchar2_table;
1263 l_set_values dbms_sql.varchar2_table;
1264 Begin
1265 clear_all_cache;
1266 bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1267 bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1268 bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1269 bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1270 bsc_aw_md_wrapper.update_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,
1271 l_match_columns,l_match_values,l_set_columns,l_set_values);
1272 --invalidate cache
1273 clear_all_cache;
1274 Exception when others then
1275 log_n('Exception in update_olap_object '||sqlerrm);
1276 raise;
1277 End;
1278
1279 /*
1280 p_object varchar2,
1281 p_object_type varchar2,
1282 p_relation_type varchar2,
1283 p_parent_object varchar2,
1284 p_parent_object_type varchar2,
1285 are mandatory
1286 optional (match or set)
1287 relation object, relation object type, property
1288 */
1289 procedure update_olap_object_relation(
1290 p_object varchar2,
1291 p_object_type varchar2,
1292 p_relation_type varchar2,
1293 p_parent_object varchar2,
1294 p_parent_object_type varchar2,
1295 p_match_columns varchar2, --comma separated
1296 p_match_values varchar2, --comma separated
1297 p_set_columns varchar2, --comma separated
1298 p_set_values varchar2 --^ separated
1299 ) is
1300 --
1301 l_match_columns dbms_sql.varchar2_table;
1302 l_match_values dbms_sql.varchar2_table;
1303 l_set_columns dbms_sql.varchar2_table;
1304 l_set_values dbms_sql.varchar2_table;
1305 Begin
1306 clear_all_cache;
1307 bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1308 bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1309 bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1310 bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1311 bsc_aw_md_wrapper.update_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,
1312 l_match_columns,l_match_values,l_set_columns,l_set_values);
1313 clear_all_cache;
1314 Exception when others then
1315 log_n('Exception in update_olap_object_relation '||sqlerrm);
1316 raise;
1317 End;
1318
1319 procedure insert_olap_object(
1320 p_object varchar2,
1321 p_object_type varchar2,
1322 p_olap_object varchar2,
1323 p_olap_object_type varchar2,
1324 p_parent_object varchar2,
1325 p_parent_object_type varchar2,
1326 p_property1 varchar2
1327 ) is
1328 Begin
1329 /*insert has no cache invalidation */
1330 bsc_aw_md_wrapper.insert_olap_object(p_object,p_object_type,p_olap_object,p_olap_object_type,p_parent_object,p_parent_object_type,p_property1);
1331 Exception when others then
1332 log_n('Exception in insert_olap_object '||sqlerrm);
1333 raise;
1334 End;
1335
1336 procedure insert_olap_object_relation(
1337 p_object varchar2,
1338 p_object_type varchar2,
1339 p_relation_object varchar2,
1340 p_relation_object_type varchar2,
1341 p_relation_type varchar2,
1342 p_parent_object varchar2,
1343 p_parent_object_type varchar2,
1344 p_property1 varchar2
1345 ) is
1346 Begin
1347 /*insert has no cache invalidation */
1348 bsc_aw_md_wrapper.insert_olap_object_relation(p_object,p_object_type,p_relation_object,p_relation_object_type,p_relation_type,
1349 p_parent_object,p_parent_object_type,p_property1);
1350 Exception when others then
1351 log_n('Exception in insert_olap_object_relation '||sqlerrm);
1352 raise;
1353 End;
1354
1355
1356 /*
1357 we store current change vector value for a base table. called from top loader pack bscawlob.pls
1358 3 api. one to create metadata entry, one to get the current value. one to update to a new value
1359 get_base_table_change_vector will return null if the base table is not in olap object relation
1360 also creates an entry for current period
1361 */
1362 procedure create_bt_change_vector(p_base_table varchar2) is
1363 l_cv_value number;
1364 Begin
1365 l_cv_value:=get_bt_change_vector(p_base_table);
1366 if l_cv_value is null then --create entry
1367 bsc_aw_md_wrapper.insert_olap_object_relation(p_base_table,'base table','0','change vector','base table change vector',
1368 p_base_table,'base table',null);
1369 bsc_aw_md_wrapper.insert_olap_object_relation(p_base_table,'base table',null,'current period','base table current period',
1370 p_base_table,'base table',null);
1371 end if;
1372 clear_all_cache;
1373 Exception when others then
1374 log_n('Exception in create_bt_change_vector '||sqlerrm);
1375 raise;
1376 End;
1377
1378 procedure drop_bt_change_vector(p_base_table varchar2) is
1379 Begin
1380 clear_all_cache;
1381 bsc_aw_md_wrapper.delete_olap_object_relation(p_base_table,'base table','base table change vector',null,null,p_base_table,'base table');
1382 bsc_aw_md_wrapper.delete_olap_object_relation(p_base_table,'base table','base table current period',null,null,p_base_table,'base table');
1383 clear_all_cache;
1384 Exception when others then
1385 log_n('Exception in drop_bt_change_vector '||sqlerrm);
1386 raise;
1387 End;
1388
1389 function get_bt_change_vector(p_base_table varchar2) return number is
1390 l_bsc_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
1391 Begin
1392 get_bsc_olap_object_relation(p_base_table,'base table','base table change vector',p_base_table,'base table',l_bsc_olap_object_relation);
1393 if l_bsc_olap_object_relation.count=0 then
1394 return null;
1395 else
1396 return to_number(l_bsc_olap_object_relation(1).relation_object);
1397 end if;
1398 Exception when others then
1399 log_n('Exception in get_bt_change_vector '||sqlerrm);
1400 raise;
1401 End;
1402
1403 function get_bt_current_period(p_base_table varchar2) return varchar2 is
1404 l_bsc_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
1405 Begin
1406 get_bsc_olap_object_relation(p_base_table,'base table','base table current period',p_base_table,'base table',l_bsc_olap_object_relation);
1407 if l_bsc_olap_object_relation.count=0 then
1408 return null;
1409 else
1410 return l_bsc_olap_object_relation(1).relation_object;
1411 end if;
1412 Exception when others then
1413 log_n('Exception in get_bt_current_period '||sqlerrm);
1414 raise;
1415 End;
1416
1417 procedure update_bt_change_vector(p_base_table varchar2, p_value number) is
1418 Begin
1419 update_olap_object_relation(p_base_table,'base table','base table change vector',p_base_table,'base table',
1420 null,null,'relation_object',to_char(p_value));
1421 Exception when others then
1422 log_n('Exception in update_bt_change_vector '||sqlerrm);
1423 raise;
1424 End;
1425
1426 /*to set the current period of the B table , p_value is period.year format at the periodicity of the B table
1427 we need this value to set projection and balance aggregations on time to null when the cp moves forward*/
1428 procedure update_bt_current_period(p_base_table varchar2,p_value varchar2) is
1429 Begin
1430 update_olap_object_relation(p_base_table,'base table','base table current period',p_base_table,'base table',
1431 null,null,'relation_object',p_value);
1432 Exception when others then
1433 log_n('Exception in update_bt_current_period '||sqlerrm);
1434 raise;
1435 End;
1436
1437 /*
1438 given a bsc_olap_object_relation_tb and a relation type, get all the relation objects
1439 */
1440 procedure get_relation_object(
1441 p_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb,
1442 p_relation_type varchar2,
1443 p_relation_object in out nocopy dbms_sql.varchar2_table
1444 ) is
1445 Begin
1446 for i in 1..p_olap_object_relation.count loop
1447 if p_olap_object_relation(i).relation_type=p_relation_type then
1448 p_relation_object(p_relation_object.count+1):=p_olap_object_relation(i).relation_object;
1449 end if;
1450 end loop;
1451 Exception when others then
1452 log_n('Exception in get_relation_object '||sqlerrm);
1453 raise;
1454 End;
1455
1456 procedure clear_all_cache is
1457 Begin
1458 g_oo_cache.delete;
1459 g_oor_cache.delete;
1460 Exception when others then
1461 log_n('Exception in clear_all_cache '||sqlerrm);
1462 raise;
1463 End;
1464
1465 /*
1466 for a dim, load the dimension_r structure
1467 */
1468 procedure get_dim_md(p_dim_name varchar2,p_dimension out nocopy bsc_aw_adapter_dim.dimension_r) is
1469 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
1470 l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
1471 l_lg_index bsc_aw_utility.number_table; --level group
1472 l_rl_index bsc_aw_utility.number_table;--rec level
1473 l_lg varchar2(200);
1474 l_default_lg_name varchar2(200);
1475 l_level_name_dim varchar2(300);
1476 Begin
1477 get_bsc_olap_object(null,null,p_dim_name,'dimension',l_oo);
1478 get_bsc_olap_object_relation(null,null,null,p_dim_name,'dimension',l_oor);
1479 l_default_lg_name:=bsc_aw_adapter_dim.get_default_lg_name;
1480 --dim properties
1481 p_dimension.dim_name:=p_dim_name;
1482 for i in 1..l_oo.count loop
1483 if l_oo(i).object=p_dim_name and l_oo(i).object_type='dimension' then
1484 if l_oo(i).olap_object_type='concat dimension' then
1485 p_dimension.concat:='Y';
1486 else
1487 p_dimension.concat:='N';
1488 end if;
1489 p_dimension.property:=l_oo(i).property1;
1490 p_dimension.dim_type:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dimension source type',',');
1491 p_dimension.corrected:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'corrected',','),'N');
1492 p_dimension.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'recursive',','),'N');
1493 p_dimension.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'normal hier',','),'N');
1494 p_dimension.relation_name:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'relation name',',');
1495 p_dimension.level_name_dim:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level name dim',',');
1496 elsif l_oo(i).object_type='filter cube' then
1497 p_dimension.filter_variable:=l_oo(i).object;
1498 elsif l_oo(i).object_type='limit cube' then
1499 p_dimension.limit_variable:=l_oo(i).object;
1500 elsif l_oo(i).object_type='rec level position cube' then
1501 p_dimension.rec_level_position_cube:=l_oo(i).object;
1502 elsif l_oo(i).object_type='base value cube' then
1503 p_dimension.base_value_cube:=l_oo(i).object;
1504 elsif l_oo(i).object_type='dml program' and l_oo(i).olap_object_type='dml program initial load' then
1505 p_dimension.initial_load_program:=l_oo(i).object;
1506 elsif l_oo(i).object_type='dml program' and l_oo(i).olap_object_type='dml program inc load' then
1507 p_dimension.inc_load_program:=l_oo(i).object;
1508 --bug fix 5636695
1509 elsif l_oo(i).object_type='level name dim' then
1510 l_level_name_dim:=l_oo(i).object;
1511 end if;
1512 end loop;
1513 --bug fix 5636695
1514 -- if in property1 level name dim is null, we will get it differently.
1515 -- I have also fixed the issue where we are putting null value for level dim name in property1
1516 -- But the following code is added for already existing dimension
1517 if p_dimension.level_name_dim is null then
1518 p_dimension.level_name_dim := l_level_name_dim;
1519 end if;
1520 --level groups
1521 for i in 1..l_oo.count loop
1522 if l_oo(i).object_type='level group' then
1523 p_dimension.level_groups(p_dimension.level_groups.count+1).level_group_name:=l_oo(i).object;
1524 l_lg_index(l_oo(i).object):=p_dimension.level_groups.count;
1525 end if;
1526 end loop;
1527 if p_dimension.level_groups.count=0 then --backward compatibility
1528 p_dimension.level_groups(p_dimension.level_groups.count+1).level_group_name:=l_default_lg_name;
1529 l_lg_index(l_default_lg_name):=p_dimension.level_groups.count;
1530 end if;
1531 --get level group levels, relations, data source
1532 for i in 1..l_oo.count loop
1533 if l_oo(i).object_type='dimension level' then
1534 l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level group',','),l_default_lg_name);
1535 p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count+1).level_name:=l_oo(i).object;
1536 p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count).position:=
1537 to_number(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'position',','));
1538 p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count).pk.pk:=
1539 bsc_aw_utility.get_parameter_value(l_oo(i).property1,'pk',',');
1540 elsif l_oo(i).object_type='recursive level' then
1541 l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level group',','),l_default_lg_name);
1542 p_dimension.level_groups(l_lg_index(l_lg)).rec_levels(p_dimension.level_groups(l_lg_index(l_lg)).rec_levels.count+1).level_name:=
1543 l_oo(i).object;
1544 l_rl_index(l_oo(i).object):=p_dimension.level_groups(l_lg_index(l_lg)).rec_levels.count;
1545 end if;
1546 end loop;
1547 --olap object relations
1548 for i in 1..l_oor.count loop
1549 if l_oor(i).relation_type='dimension kpi' then
1550 p_dimension.kpi_for_dim(p_dimension.kpi_for_dim.count+1).kpi:=l_oor(i).relation_object;
1551 elsif l_oor(i).relation_type='zero code level' then
1552 l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'level group',','),l_default_lg_name);
1553 p_dimension.level_groups(l_lg_index(l_lg)).zero_levels(p_dimension.level_groups(l_lg_index(l_lg)).zero_levels.count+1).level_name:=
1554 l_oor(i).relation_object;
1555 p_dimension.level_groups(l_lg_index(l_lg)).zero_levels(p_dimension.level_groups(l_lg_index(l_lg)).zero_levels.count).child_level_name:=
1556 l_oor(i).object;
1557 elsif l_oor(i).relation_type='recursive parent level' then
1558 l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'level group',','),l_default_lg_name);
1559 p_dimension.level_groups(l_lg_index(l_lg)).rec_levels(l_rl_index(l_oor(i).relation_object)).child_level_name:=l_oor(i).object;
1560 elsif l_oor(i).relation_type='parent level' then
1561 l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'level group',','),l_default_lg_name);
1562 p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count+1).child_level:=
1563 l_oor(i).object;
1564 p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count).parent_level:=
1565 l_oor(i).relation_object;
1566 p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count).parent_pk:=
1567 bsc_aw_utility.get_parameter_value(l_oor(i).property1,'pk',',');
1568 p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count).child_fk:=
1569 bsc_aw_utility.get_parameter_value(l_oor(i).property1,'fk',',');
1570 end if;
1571 end loop;
1572 --get the snowflake info also
1573 for i in 1..p_dimension.level_groups.count loop
1574 for j in 1..p_dimension.level_groups(i).levels.count loop
1575 l_oo.delete;
1576 get_bsc_olap_object(null,'dimension',p_dimension.level_groups(i).levels(j).level_name,'dimension',l_oo);
1577 p_dimension.level_groups(i).levels(j).relation_name:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'relation name',',');
1578 p_dimension.level_groups(i).levels(j).level_name_dim:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'level name dim',',');
1579 end loop;
1580 end loop;
1581 Exception when others then
1582 log_n('Exception in get_dim_md '||sqlerrm);
1583 raise;
1584 End;
1585
1586 procedure analyze_md_tables is
1587 Begin
1588 bsc_aw_md_wrapper.analyze_md_tables;
1589 Exception when others then
1590 log_n('Exception in analyze_md_tables '||sqlerrm);
1591 raise;
1592 End;
1593
1594 function get_upgrade_version return number is
1595 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
1596 Begin
1597 bsc_aw_md_api.get_bsc_olap_object('bsc aw version','bsc aw version','bsc aw version','bsc aw version',l_oo);
1598 if l_oo.count>0 then
1599 return to_number(l_oo(1).olap_object);
1600 else
1601 return 0;
1602 end if;
1603 Exception when others then
1604 log_n('Exception in get_upgrade_version '||sqlerrm);
1605 raise;
1606 End;
1607
1608 procedure set_upgrade_version(p_version number) is
1609 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
1610 Begin
1611 bsc_aw_md_api.get_bsc_olap_object('bsc aw version','bsc aw version','bsc aw version','bsc aw version',l_oo);
1612 if l_oo.count>0 then
1613 update_olap_object('bsc aw version','bsc aw version','bsc aw version','bsc aw version',
1614 null,null,'olap_object,olap_object_type',bsc_aw_utility.g_upgrade_version||'^bsc aw version');
1615 else /*first time*/
1616 insert_olap_object('bsc aw version','bsc aw version',to_char(bsc_aw_utility.g_upgrade_version),'bsc aw version','bsc aw version',
1617 'bsc aw version',null);
1618 end if;
1619 Exception when others then
1620 log_n('Exception in set_upgrade_version '||sqlerrm);
1621 raise;
1622 End;
1623
1624 -----------------------------
1625 procedure init_all is
1626 Begin
1627 bsc_aw_md_wrapper.set_context('AW');
1628 g_debug:=bsc_aw_utility.g_debug;
1629 Exception when others then
1630 null;
1631 End;
1632
1633 procedure log(p_message varchar2) is
1634 Begin
1635 bsc_aw_utility.log(p_message);
1636 Exception when others then
1637 null;
1638 End;
1639
1640 procedure log_n(p_message varchar2) is
1641 Begin
1642 log(' ');
1643 log(p_message);
1644 Exception when others then
1645 null;
1646 End;
1647
1648 END BSC_AW_MD_API;
[Home] [Help]