1 package body BSC_AW_ADAPTER_DIM AS
2 /*$Header: BSCAWADB.pls 120.20 2006/11/04 10:54:24 amitgupt noship $*/
3
4 --given a array of dim, first get the full list of dim as parent child
5 --and then group them into dim
6 --p_dim_level_list is a set of BSC dim levels
7 procedure create_dim(p_dim_level_list dbms_sql.varchar2_table) is
8 l_affected_kpi dbms_sql.varchar2_table;
9 Begin
10 create_dim(p_dim_level_list,l_affected_kpi);
11 Exception when others then
12 log_n('Exception in create_dim '||sqlerrm);
13 raise;
14 End;
15
16 /*
17 if create_dim has to end up re-creating a dim, then the affected kpi are returned. these affected kpi have been
18 dropped so the dim can be recreated. mostly, p_affected_kpi should be empty
19 */
20 procedure create_dim(
21 p_dim_level_list dbms_sql.varchar2_table,
22 p_affected_kpi in out nocopy dbms_sql.varchar2_table
23 ) is
24 --
25 l_dim_parent_child dim_parent_child_tb;
26 l_dim_levels levels_tv;
27 --
28 Begin
29 get_all_dim_levels(p_dim_level_list,l_dim_parent_child,l_dim_levels);
30 --l_dim_levels contains the level name and id. will be used to generate the dim name
31 group_levels_into_sets(l_dim_parent_child);
32 create_dim(l_dim_parent_child,l_dim_levels,g_dimensions);
33 create_std_dim(g_dimensions); --this creates the type and projection dim
34 if g_debug then
35 dmp_g_dimensions(g_dimensions);
36 end if;
37 implement_dim_aw(g_dimensions,p_affected_kpi);
38 if g_debug then
39 log_n('create_dim complete '||bsc_aw_utility.get_time);
40 end if;
41 Exception when others then
42 log_n('Exception in create_dim '||sqlerrm);
43 raise;
44 End;
45
46 --get the parent child level relation and the properties of the levels
47 --excluding the position
48 --position is later calculated
49 --needs to be reset later
50 procedure get_all_dim_levels(
51 p_dim_level_list dbms_sql.varchar2_table,
52 p_dim_parent_child out nocopy dim_parent_child_tb,
53 p_dim_levels out nocopy levels_tv
54 ) is
55 Begin
56 if g_debug then
57 log_n('In get_all_dim_levels'||bsc_aw_utility.get_time);
58 end if;
59 --get all the parent child relations for the levels
60 bsc_aw_bsc_metadata.get_all_parent_child(p_dim_level_list,p_dim_parent_child,p_dim_levels);
61 if g_debug then
62 log_n('p_dim_parent_child');
63 for i in 1..p_dim_parent_child.count loop
64 log(p_dim_parent_child(i).parent_level||'('||p_dim_parent_child(i).parent_pk||') '||
65 p_dim_parent_child(i).child_level||'('||p_dim_parent_child(i).child_fk||')');
66 end loop;
67 end if;
68 Exception when others then
69 log_n('Exception in get_all_dim_levels '||sqlerrm);
70 raise;
71 End;
72
73 procedure group_levels_into_sets(
74 p_dim_parent_child in out nocopy dim_parent_child_tb
75 ) is
76 --
77 l_level_considered dbms_sql.varchar2_table;
78 l_set number;
79 l_flag boolean;
80 l_level varchar2(100);
81 --
82 Begin
83 l_level_considered.delete;
84 l_set:=0;
85 --loop completed when there are no more parent child combinations that need a set number assigned
86 loop
87 --get the first empty set
88 l_flag:=false;
89 for i in 1..p_dim_parent_child.count loop
90 if p_dim_parent_child(i).level_set is null then
91 l_level:=p_dim_parent_child(i).child_level;
92 l_flag:=true;
93 exit;
94 end if;
95 end loop;
96 --see if we can exit
97 if l_flag=false then
98 --all work complete
99 exit;
100 end if;
101 l_set:=l_set+1;
102 assign_set_to_level(p_dim_parent_child,l_level_considered,l_level,l_set);
103 end loop;
104 Exception when others then
105 log_n('Exception in group_levels_into_sets '||sqlerrm);
106 raise;
107 End;
108
109 --assign a set number to this level (p_level). then for each parent and child, see if a set is already assigned.
110 --if not, call this procedure recursively for those levels
111 procedure assign_set_to_level(
112 p_dim_parent_child in out nocopy dim_parent_child_tb,
113 p_level_considered in out nocopy dbms_sql.varchar2_table,
114 p_level varchar2,
115 p_set number
116 ) is
117 Begin
118 if bsc_aw_utility.in_array(p_level_considered,p_level) then
119 return;
120 end if;
121 for i in 1..p_dim_parent_child.count loop
122 if p_dim_parent_child(i).child_level=p_level then
123 p_dim_parent_child(i).level_set:=p_set;
124 p_level_considered(p_level_considered.count+1):=p_level;
125 end if;
126 end loop;
127 --for each parent, set the level_set number
128 for i in 1..p_dim_parent_child.count loop
129 if p_dim_parent_child(i).child_level=p_level then
130 if p_dim_parent_child(i).parent_level is not null and p_dim_parent_child(i).parent_level<>p_dim_parent_child(i).child_level then
131 assign_set_to_level(p_dim_parent_child,p_level_considered,p_dim_parent_child(i).parent_level,p_set);
132 end if;
133 end if;
134 end loop;
135 --for each child, set the level_set number
136 for i in 1..p_dim_parent_child.count loop
137 if p_dim_parent_child(i).parent_level=p_level then
138 if p_dim_parent_child(i).child_level is not null and p_dim_parent_child(i).parent_level<>p_dim_parent_child(i).child_level then
139 --child cannot be null...still no harm
140 assign_set_to_level(p_dim_parent_child,p_level_considered,p_dim_parent_child(i).child_level,p_set);
141 end if;
142 end if;
143 end loop;
144 Exception when others then
145 log_n('Exception in assign_set_to_level '||sqlerrm);
146 raise;
147 End;
148
149 /*
150 create_dim is passed an array of information. this api first creates the dim structure in memory
151 by using the level set info
152 */
153 procedure create_dim(
154 p_dim_parent_child dim_parent_child_tb,
155 p_dim_levels levels_tv,
156 p_dimensions out nocopy dimension_tb
157 ) is
158 --
159 l_max_set number;
160 l_level varchar2(100);
161 l_id number;
162 l_count number;
163 l_dim dimension_r;
164 --
165 TYPE CurTyp IS REF CURSOR;
166 cv CurTyp;
167 --
168 Begin
169 l_max_set:=0;
170 for i in 1..p_dim_parent_child.count loop
171 if p_dim_parent_child(i).level_set>l_max_set then
172 l_max_set:=p_dim_parent_child(i).level_set;
173 end if;
174 end loop;
175 --l_max_set is the number of dimensions
176 for i in 1..l_max_set loop
177 l_count:=0;
178 --bug fix 5636695
179 -- we have used a new public var here, so as to take an empty structure and fill in
180 -- the issue here was we had level values and parent child values retained from the previous
181 -- kpi run p_dimensions... which was causing problem
182 --- this will clear the previous entries from the structure
183 l_dim.level_groups.delete;
184 bsc_aw_utility.delete_table('bsc_aw_temp_vn',null);
185 for j in 1..p_dim_parent_child.count loop
186 if p_dim_parent_child(j).level_set=i then
187 l_level:=p_dim_parent_child(j).child_level;
188 l_id:=p_dim_levels(l_level).level_id;
189 execute immediate 'insert into bsc_aw_temp_vn(name,id) values(:1,:2)' using l_level,l_id;
190 l_count:=l_count+1;
191 l_dim.level_groups(1).parent_child(l_count):=p_dim_parent_child(j);
192 end if;
193 end loop;
194 l_count:=0;
195 g_stmt:='select distinct name,id from bsc_aw_temp_vn order by id';
196 open cv for g_stmt;
197 loop
198 fetch cv into l_level,l_id;
199 exit when cv%notfound;
200 l_count:=l_count+1;
201 l_dim.level_groups(1).levels(l_count):=p_dim_levels(l_level);
202 --p_dim_levels will have the level name, id, position(set_level_position api), pk, fk etc
203 --new dim all have default 1 level group
204 end loop;
205 close cv;
206 --all dim have at-least 2 levels. the top level is zero code level. this means all dim are concat dim
207 --if this is a single level dim vs multi level dim
208 --the name of the concat dim does not contain the zero codes or the rec levels
209 /*
210 we ran into an error where the name of the object ran to greater than 64 chars and AW errored creating the object
211 AW allows max of 64 chars in a name. so we use the string of all level ids and then create a hash value and use the
212 hash value. the hash value is starting from 100 to 1073741824
213 we trust that the hash value for a given string does not change from db version to version. tested on 9i and 10g.
214 its consistent.
215 */
216 p_dimensions(i) := l_dim;
217 p_dimensions(i).corrected:='N';
218 p_dimensions(i).concat:='Y';
219 make_dim_name(p_dimensions(i),get_dim_name_hash_string(p_dimensions(i)));
220 p_dimensions(i).dim_type:='custom';
221 p_dimensions(i).relation_name:=p_dimensions(i).dim_name||'.rel';
222 p_dimensions(i).level_groups(1).level_group_name:=get_default_lg_name;
223 --set the level position. level positions are useful to see if we need to aggregate on the fly
224 set_level_position(p_dimensions(i));
225 --if a dim is a recursive dim, we have no zero code level. the reason is that the top node is zero code
226 set_dim_recursive(p_dimensions(i));
227 get_kpi_for_dim(p_dimensions(i));
228 if p_dimensions(i).recursive='Y' then
229 set_rec_dim_properties(p_dimensions(i));--sets normal hier as default implementation
230 create_virtual_rec_level(p_dimensions(i));
231 create_rec_data_source(p_dimensions(i));
232 else
233 create_virtual_zero_code_level(p_dimensions(i));--simply creating the metadata
234 create_data_source(p_dimensions(i));
235 end if;
236 --set the dim properties
237 set_dim_properties(p_dimensions(i));
238 end loop;
239 Exception when others then
240 log_n('Exception in create_dim '||sqlerrm);
241 raise;
242 End;
243
244 function get_dim_name_hash_string(p_dimension dimension_r) return varchar2 is
245 l_hash_string varchar2(4000);
246 Begin
247 l_hash_string:='DIM';
248 for i in 1..p_dimension.level_groups.count loop
249 for j in 1..p_dimension.level_groups(i).levels.count loop
250 l_hash_string:=l_hash_string||'.'||p_dimension.level_groups(i).levels(j).level_id;
251 end loop;
252 end loop;
253 --add the time to make it unique
254 l_hash_string:=l_hash_string||'.'||bsc_aw_utility.get_dbms_time;
255 return l_hash_string;
256 Exception when others then
257 log_n('Exception in get_dim_name_hash_string '||sqlerrm);
258 raise;
259 End;
260
261 procedure make_dim_name(p_dimension in out nocopy dimension_r,p_hash_string varchar2) is
262 l_hash_value varchar2(200);
263 Begin
264 p_dimension.dim_name:='BSC_CC';
265 l_hash_value:=bsc_aw_utility.get_hash_value(p_hash_string,100,1073741824);
266 p_dimension.dim_name:=p_dimension.dim_name||'_'||l_hash_value;
267 Exception when others then
268 log_n('Exception in make_dim_name '||sqlerrm);
269 raise;
270 End;
271
272 procedure set_dim_properties(p_dim in out nocopy dimension_r) is
273 Begin
274 if p_dim.dim_type<>'std' and p_dim.level_groups(1).data_source.data_source is not null then --DBI dim
275 p_dim.dim_type:='dbi';
276 end if;
277 --here dim are normal. in bsc_aw_calendar module, we have time dim being created
278 p_dim.property:='dimension type=normal,dimension source type='||p_dim.dim_type||',corrected='||p_dim.corrected||',';
279 --as old dim ages, the corrected flag is set to yes
280 if p_dim.recursive='Y' then
281 p_dim.property:=p_dim.property||'recursive,';
282 if p_dim.recursive_norm_hier='Y' then
283 p_dim.property:=p_dim.property||'normal hier,';
284 --also the denorm source, child col and parent col. we need it in the load dim module to load
285 --norm hier into bsc_aw_temp_pc
286 --note>>>the denorm_data_source is like (select a,b from CC). it has comma inside. if in bscawldb.pls, we try
287 --to bsc_aw_utility.get_parameter_value(l_dim_property,'denorm source',','), we will get (select a since the stmt
288 --is cutoff at the first , so we will replace the , with *^ and later, in bscawldb.pls, replace the *^ with ,
289 --note>>>p_dim.level_groups(1).data_source.data_source and p_dim.level_groups(1).data_source.denorm_data_source go together
290 if p_dim.level_groups(1).data_source.data_source is not null then
291 p_dim.property:=p_dim.property||'denorm source='||replace(p_dim.level_groups(1).data_source.denorm_data_source,',','*^')||',';
292 p_dim.property:=p_dim.property||'child col='||p_dim.level_groups(1).data_source.child_col||',parent col='||
293 p_dim.level_groups(1).data_source.parent_col||',';
294 end if;
295 end if;
296 end if;
297 if p_dim.level_groups.count>1 or p_dim.level_groups(1).levels.count>1 then
298 p_dim.property:=p_dim.property||'multi level,';
299 end if;
300 for i in 1..p_dim.level_groups.count loop
301 if p_dim.level_groups(i).zero_levels.count>0 then
302 p_dim.property:=p_dim.property||'zero code,';
303 exit;
304 end if;
305 end loop;
306 p_dim.property:=p_dim.property||'relation name='||p_dim.relation_name||',';
307 p_dim.property:=p_dim.property||'level name dim='||p_dim.level_name_dim||',';
308 Exception when others then
309 log_n('Exception in set_dim_properties '||sqlerrm);
310 raise;
311 End;
312
313 procedure reset_dimension_r(p_dim in out nocopy dimension_r) is
314 Begin
315 p_dim.level_groups(1).levels.delete;
316 p_dim.level_groups(1).parent_child.delete;
317 p_dim.level_groups(1).zero_levels.delete;
318 p_dim.level_groups(1).rec_levels.delete;
319 p_dim.recursive:='N';
320 p_dim.recursive_norm_hier:='N';
321 p_dim.kpi_for_dim.delete;
322 Exception when others then
323 log_n('Exception in reset_dimension_r '||sqlerrm);
324 raise;
325 End;
326
327 procedure set_level_position(
328 p_dimension in out nocopy dimension_r
329 ) is
330 --
331 l_flag boolean;
332 --
333 Begin
334 if p_dimension.level_groups(1).levels.count=1 then
335 --single level and recursive dim
336 p_dimension.level_groups(1).levels(1).position:=1;
337 else
338 --multi level dim
339 --get the lowest levels. they are the seed levels. then set the positions
340 for i in 1..p_dimension.level_groups(1).levels.count loop
341 --for each level, see if its a child. if its a child, start setting the position
342 l_flag:=false;
343 for j in 1..p_dimension.level_groups(1).parent_child.count loop
344 if p_dimension.level_groups(1).parent_child(j).parent_level=p_dimension.level_groups(1).levels(i).level_name then
345 l_flag:=true;
346 exit;
347 end if;
348 end loop;
349 if l_flag=false then
350 set_level_position(p_dimension,p_dimension.level_groups(1).levels(i).level_name,1);
351 end if;
352 end loop;
353 end if;
354 Exception when others then
355 log_n('Exception in set_level_position '||sqlerrm);
356 raise;
357 End;
358
359 /*
360 consider this
361 1 - 2 - 3
362 8 - 7 - 6 - 5 - 4 - 3
363 8 - 9 - 10
364 8 - 9 - 3
365
366 lowest level have position 1. we also need to handle rec dim
367 rec dim are handled like this:
368 say only first 2 levels are pre-agg, larry and john wookey. the reports asks for cliff. then all we need to do it to limit the relation
369 dim.level to the value of cliff and call the aggregation. the entry for cliff has all the denorm values of employees under cliff in it.
370 so setting the position is not reqd for rec dim
371 */
372 procedure set_level_position(
373 p_dimension in out nocopy dimension_r,
374 p_level varchar2,
375 p_position number
376 ) is
377 --
378 l_level_index number;
379 --
380 Begin
381 --set the position
382 if g_debug then
383 log_n('In set_level_position, p_level='||p_level||' p_position='||p_position);
384 end if;
385 if p_level is null then
386 return;
387 end if;
388 for i in 1..p_dimension.level_groups(1).levels.count loop
389 if p_dimension.level_groups(1).levels(i).level_name=p_level then
390 l_level_index:=i;
391 exit;
392 end if;
393 end loop;
394 if p_dimension.level_groups(1).levels(l_level_index).position is not null
395 and p_dimension.level_groups(1).levels(l_level_index).position>=p_position then
396 return;
397 end if;
398 p_dimension.level_groups(1).levels(l_level_index).position:=p_position;
399 --call the routine recursively for all parents
403 and p_dimension.level_groups(1).parent_child(i).parent_level<>p_dimension.level_groups(1).parent_child(i).child_level then
400 for i in 1..p_dimension.level_groups(1).parent_child.count loop
401 if p_dimension.level_groups(1).parent_child(i).child_level=p_level
402 and p_dimension.level_groups(1).parent_child(i).parent_level is not null
404 set_level_position(p_dimension,p_dimension.level_groups(1).parent_child(i).parent_level,p_position+1);
405 end if;
406 end loop;
407 Exception when others then
408 log_n('Exception in set_level_position '||sqlerrm);
409 raise;
410 End;
411
412 /*
413 find all the kpi implemented in AW that have this dim
414 this reads BSC metadata. so its giving an idea of kpi that maynot be implemented as yet
415 but will be. this means there may be some kpi already implemented that no longer has this dim.
416 this kpi will not be picked up since we are reading bsc metadata
417 kpi_for_dim_r holds the kpi and dimset info
418 */
419 procedure get_kpi_for_dim(p_dimension in out nocopy dimension_r) is
420 Begin
421 bsc_aw_bsc_metadata.get_kpi_for_dim(p_dimension);
422 Exception when others then
423 log_n('Exception in get_kpi_for_dim '||sqlerrm);
424 raise;
425 End;
426
427 /*
428 zero code level are virtual levels that are going to be created
429 its the top levels for any dim
430 if we have
431 comp >- prod >- prod family
432 comp >- prod >- manager
433 we will have 2 zero code levels. final hierarchy is
434 comp >- prod >- prod family >- prod family zero
435 comp >- prod >- manager >- manager zero
436
437 This api is not invoked for recursive dimensions
438
439 new:
440 we do not need zero code levels. this will cause huge problems in olap table fn views. if we have zero code level
441 and regular level, on which will the olap table fn be based? we hit the zero code mv for zero values and regular
442 values.
443
444 zero code is implemented as simply a virtual level. the name is in dim.level_groups(1).levels. in the relation we will have for the
445 top level
446 |
447 zero| country(0) country(0)...
448 |
449 ----------------country---------------------->
450
451 New: we need zero code levels for all levels. imagine that a kpi has only city dim. in this case, we will have zero code
452 for city level since its the top level.
453
454 city zero| city(0)...
455 |
456 ctry zero| country(0) country(0)...
457 |
458 ---city-------------country---------------------->
459
460 */
461 procedure create_virtual_zero_code_level(
462 p_dimension in out nocopy dimension_r
463 )is
464 Begin
465 for i in 1..p_dimension.level_groups(1).levels.count loop
466 p_dimension.level_groups(1).zero_levels(p_dimension.level_groups(1).zero_levels.count+1).level_name:=p_dimension.level_groups(1).levels(i).level_name||'_ZERO';
467 p_dimension.level_groups(1).zero_levels(p_dimension.level_groups(1).zero_levels.count).child_level_name:=p_dimension.level_groups(1).levels(i).level_name;
468 end loop;
469 Exception when others then
470 log_n('Exception in create_virtual_zero_code_level '||sqlerrm);
471 raise;
472 End;
473
474 /*
475 called only for rec dim
476 creates a parent level. this level contains all entries of the child level. child level will maintain fact data for each
477 dim level value, like expense for each employee. the rolled up value = sum of all child values + its own value
478 */
479 procedure create_virtual_rec_level(
480 p_dimension in out nocopy dimension_r
481 )is
482 Begin
483 p_dimension.level_groups(1).rec_levels(1).level_name:=p_dimension.level_groups(1).levels(1).level_name||'_PARENT';
484 p_dimension.level_groups(1).rec_levels(1).child_level_name:=p_dimension.level_groups(1).levels(1).level_name;
485 Exception when others then
486 log_n('Exception in create_virtual_rec_level '||sqlerrm);
487 raise;
488 End;
489
490 /*if this is a bsc rec dim, we cannot have denorm implementation. only norm impl
491 */
492 procedure set_rec_dim_properties(p_dimension in out nocopy dimension_r) is
493 Begin
494 p_dimension.recursive_norm_hier:='Y'; --default
495 Exception when others then
496 log_n('Exception in set_rec_dim_properties '||sqlerrm);
497 raise;
498 End;
499
500 /*
501 rec dim have one level. 2 data sources are necessary. one for the key, like employee. the other is the relation
502 in a denorm fashion, employee,manager
503 we have inc program same as initial program for now
504 rec data source can be null if this is native bsc rec dim
505 */
506 procedure create_rec_data_source(
507 p_dimension in out nocopy dimension_r
508 ) is
509 Begin
510 bsc_aw_bsc_metadata.create_rec_data_source(p_dimension);
511 p_dimension.initial_load_program:='load_'||p_dimension.dim_name||'.initial';
512 p_dimension.inc_load_program:='load_'||p_dimension.dim_name||'.initial';
513 Exception when others then
514 log_n('Exception in create_rec_data_source '||sqlerrm);
515 raise;
516 End;
517
518 /*
519 this procedure creates the data source for dbi dimensions
520 */
521 procedure create_data_source(
522 p_dimension in out nocopy dimension_r
523 ) is
524 Begin
525 bsc_aw_bsc_metadata.create_data_source(p_dimension);
526 p_dimension.initial_load_program:='load_'||p_dimension.dim_name||'.initial';
527 p_dimension.inc_load_program:='load_'||p_dimension.dim_name||'.inc';
528 Exception when others then
529 log_n('Exception in create_data_source '||sqlerrm);
530 raise;
531 End;
532
533 /*
534 updates the metadata and then creates the aw objects for new dim
535 the logic is as follows:
536
537 */
538 procedure implement_dim_aw(
539 p_dimensions in out nocopy dimension_tb,
543 --
540 p_affected_kpi in out nocopy dbms_sql.varchar2_table
541 ) is
542 --
544 Begin
545 if g_debug then
546 log_n('In implement_dim_aw');
547 end if;
548 for i in 1..p_dimensions.count loop
549 implement_dim_aw(p_dimensions(i),p_affected_kpi);
550 end loop;
551 Exception when others then
552 log_n('Exception in implement_dim_aw '||sqlerrm);
553 raise;
554 End;
555
556 procedure implement_dim_aw(
557 p_dimension in out nocopy dimension_r,
558 p_affected_kpi in out nocopy dbms_sql.varchar2_table
559 ) is
560 --
561 l_dim_create varchar2(100);
562 l_recreate_option varchar2(100);
563 --
564 Begin
565 if g_debug then
566 log_n('In implement_dim_aw dimension='||p_dimension.dim_name||', dim type='||p_dimension.dim_type);
567 end if;
568 --see if the dim already exists, or any old dim needs correction
569 if p_dimension.dim_type<>'std' then
570 correct_old_dim(p_dimension,l_dim_create);
571 else
572 if g_debug then
573 log('This is std dim. Check to see if dim exists...');
574 end if;
575 if bsc_aw_md_api.is_dim_present(p_dimension.dim_name)=false then
576 l_dim_create:='create all';
577 end if;
578 end if;
579 --
580 if p_dimension.dim_type='std' then
581 l_recreate_option:='RECREATE STD DIM';
582 else
583 l_recreate_option:='RECREATE DIM';
584 end if;
585 if bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,l_recreate_option)='Y' then
586 if g_debug then
587 log('Dim needs to be force recreated');
588 end if;
589 l_dim_create:='create all';
590 elsif bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,'RECREATE PROGRAM')='Y' then
591 if g_debug then
592 log('Dim needs to be force recreated');
593 end if;
594 l_dim_create:='create all';
595 end if;
596 if l_dim_create='create all' then
597 if p_dimension.dim_type<>'std' then
598 check_dim_name_conflict(p_dimension);--change the dim name if there is clash
599 end if;
600 create_dim_objects(p_dimension);
601 else
602 if g_debug then
603 log('Noop for dimension '||p_dimension.dim_name);
604 end if;
605 end if;
606 Exception when others then
607 log_n('Exception in implement_dim_aw '||sqlerrm);
608 raise;
609 End;
610
611 /*
612 we will keep this api around. what if we need to drop and recreate kpi in some case?
613 */
614 procedure drop_kpi_objects_for_dim(p_dim_name varchar2,p_affected_kpi in out nocopy dbms_sql.varchar2_table) is
615 --
616 l_kpi_list dbms_sql.varchar2_table;
617 --
618 Begin
619 --get_kpi_for_dim gives the list of kpi already implemented in AW for this dim
620 bsc_aw_md_api.get_kpi_for_dim(p_dim_name,l_kpi_list);
621 bsc_aw_utility.merge_array(p_affected_kpi,l_kpi_list);
622 for i in 1..l_kpi_list.count loop
623 bsc_aw_adapter_kpi.drop_kpi_objects(l_kpi_list(i));
624 bsc_aw_md_api.mark_kpi_recreate(l_kpi_list(i));
625 end loop;
626 Exception when others then
627 log_n('Exception in drop_kpi_objects_for_dim '||sqlerrm);
628 raise;
629 End;
630
631 /*
632 have to drop in this order
633 1. relations and variables
634 2. concat dim
635 3. dim levels
636 4. others like programs
637 */
638 procedure drop_dim(p_dim_name varchar2) is
639 --
640 l_objects bsc_aw_md_wrapper.bsc_olap_object_tb;
641 l_flag dbms_sql.varchar2_table;
642 --
643 Begin
644 bsc_aw_md_api.get_dim_olap_objects(p_dim_name,l_objects,'all');
645 for i in 1..l_objects.count loop
646 if l_objects(i).olap_object_type is null then
647 l_flag(i):='Y';
648 else
649 l_flag(i):='N';
650 end if;
651 end loop;
652 --do not drop dim levels. with the concept of level groups, the dim levels are not blown away
653 for i in 1..l_objects.count loop
654 if l_objects(i).object_type='dimension level' then
655 l_flag(i):='Y'; -- do not drop dim levels. they are standalone dim for snow flaks. they are shared across concat dim
656 end if;
657 end loop;
658 for i in 1..l_objects.count loop
659 if l_flag(i)='N' and (l_objects(i).olap_object_type='relation' or l_objects(i).olap_object_type='variable') then
660 bsc_aw_utility.delete_aw_object(l_objects(i).object);
661 l_flag(i):='Y';
662 end if;
663 end loop;
664 for i in 1..l_objects.count loop
665 if l_flag(i)='N' and l_objects(i).object_type='concat dimension' then
666 bsc_aw_utility.delete_aw_object(l_objects(i).object);
667 l_flag(i):='Y';
668 end if;
669 end loop;
670 for i in 1..l_objects.count loop
671 if l_flag(i)='N' and l_objects(i).object_type='dimension' then
672 bsc_aw_utility.delete_aw_object(l_objects(i).object);
673 l_flag(i):='Y';
674 end if;
675 end loop;
676 for i in 1..l_objects.count loop
677 if l_flag(i)='N' then
678 bsc_aw_utility.delete_aw_object(l_objects(i).object);
679 l_flag(i):='Y';
680 end if;
681 end loop;
682 bsc_aw_md_api.drop_dim(p_dim_name);--this will delete all child objects in metadata
683 Exception when others then
684 log_n('Exception in drop_dim '||sqlerrm);
685 raise;
686 End;
687
688 /*see if any of the levels have a cc dim . if yes, drop them. for the levels, find the old cc dim,
689 for the ccdim, find the kpi drop the kpi structures, mark kpi for recreate, drop old cc dim
690 This is a specialized api. used only to drop the old cc dim for the levels
691 */
692 procedure drop_old_dim_for_level(p_dimension dimension_r,p_affected_kpi in out nocopy dbms_sql.varchar2_table) is
693 --
694 l_dim_list dbms_sql.varchar2_table;
698 --its best to have same structure for all dim. single level dim also have concat dimensions
695 --
696 Begin
697 --for single level dim, there is one level, itself
699 if g_debug then
700 log('In drop_old_dim_for_level '||p_dimension.dim_name);
701 end if;
702 bsc_aw_md_api.get_ccdim_for_levels(p_dimension,l_dim_list);
703 if g_debug then
704 for i in 1..l_dim_list.count loop
705 log('Old CC Dim to be dropped are '||l_dim_list(i));
706 end loop;
707 end if;
708 for i in 1..l_dim_list.count loop
709 drop_kpi_objects_for_dim(l_dim_list(i),p_affected_kpi);
710 drop_dim(l_dim_list(i));
711 end loop;
712 Exception when others then
713 log_n('Exception in drop_old_dim_for_level '||sqlerrm);
714 raise;
715 End;
716
717 /*
718 to create a dim
719 create the level dim
720 if level count> 1
721 create the cc dim
722 create the dim with the level names
723 create relation
724 endif
725 create initial load program
726 create inc refresh program
727 */
728 procedure create_dim_objects(p_dimension in out nocopy dimension_r) is
729 Begin
730 bsc_aw_utility.add_sqlerror(-34340,'ignore',null);
731 bsc_aw_utility.add_sqlerror(-36656,'ignore',null);
732 --all info to create a dim should be in p_dimension
733 create_dim_for_levels(p_dimension);
734 --if there is only 1 level and this level=dim name then we dont create cc dim or relations
735 --this is true for TYPE and PROJECTION dim
736 if not(p_dimension.level_groups(1).levels.count=1 and p_dimension.level_groups(1).levels(1).level_name=p_dimension.dim_name) then
737 create_ccdim(p_dimension);
738 create_level_name_dim(p_dimension);
739 create_relation(p_dimension);
740 end if;
741 create_dim_program(p_dimension);
742 --bug fix 5636695
743 -- calling this api here will fill the correct values in property attribute
744 -- including level name dim
745 set_dim_properties(p_dimension);
746 bsc_aw_md_api.create_dim_objects(p_dimension);
747 bsc_aw_utility.remove_sqlerror(-34340,'ignore');
748 bsc_aw_utility.remove_sqlerror(-36656,'ignore');
749 Exception when others then
750 log_n('Exception in create_dim_objects '||sqlerrm);
751 raise;
752 End;
753
754 /*
755 create dim
756 create filter and limit bool variables
757 create the first value "0"
758 */
759 procedure create_dim_for_levels(p_dimension in out nocopy dimension_r) is
760 --
761 l_dim_data_type varchar2(100);
762 --
763 Begin
764 for i in 1..p_dimension.level_groups(1).levels.count loop
765 --in the prototype in gsitst, we found that dim must be text always for performance
766 --dim datatype is always text. we ran perf prototype and found that with text, we have the
767 --best aggregation perf
768 l_dim_data_type:='TEXT';
769 g_stmt:='dfn '||p_dimension.level_groups(1).levels(i).level_name||' dimension '||l_dim_data_type;
770 bsc_aw_dbms_aw.execute(g_stmt);
771 p_dimension.level_groups(1).levels(i).filter_variable:=p_dimension.level_groups(1).levels(i).level_name||'.filter.bool';
772 p_dimension.level_groups(1).levels(i).limit_variable:=p_dimension.level_groups(1).levels(i).level_name||'.LB';
773 g_stmt:='dfn '||p_dimension.level_groups(1).levels(i).filter_variable||' variable boolean <'||
774 p_dimension.level_groups(1).levels(i).level_name||'>';
775 bsc_aw_dbms_aw.execute(g_stmt);
776 g_stmt:='dfn '||p_dimension.level_groups(1).levels(i).limit_variable||' variable boolean <'||
777 p_dimension.level_groups(1).levels(i).level_name||'>';
778 bsc_aw_dbms_aw.execute(g_stmt);
779 --if std dim, do not add 0
780 if p_dimension.dim_type<>'std' then
781 g_stmt:='maintain '||p_dimension.level_groups(1).levels(i).level_name||' merge ''0''';
782 bsc_aw_dbms_aw.execute(g_stmt);
783 end if;
784 --see if there are seed values to enter
785 --if seed value is a text, it will be entered as '''seedvalue''' . see create_projection_dim
786 for j in 1..p_dimension.level_groups(1).levels(i).seed_values.count loop
787 g_stmt:='maintain '||p_dimension.level_groups(1).levels(i).level_name||' merge '||p_dimension.level_groups(1).levels(i).seed_values(j);
788 bsc_aw_dbms_aw.execute(g_stmt);
789 end loop;
790 end loop;
791 --for the recursive levels
792 for i in 1..p_dimension.level_groups(1).rec_levels.count loop
793 g_stmt:='dfn '||p_dimension.level_groups(1).rec_levels(i).level_name||' dimension '||l_dim_data_type;
794 bsc_aw_dbms_aw.execute(g_stmt);
795 end loop;
796 Exception when others then
797 log_n('Exception in create_dim_for_levels '||sqlerrm);
798 raise;
799 End;
800
801 procedure create_ccdim(p_dimension in out nocopy dimension_r) is
802 l_levels dbms_sql.varchar2_table;
803 Begin
804 g_stmt:='dfn '||p_dimension.dim_name||' dimension concat(';
805 --normal levels
806 for i in 1..p_dimension.level_groups(1).levels.count loop
807 g_stmt:=g_stmt||p_dimension.level_groups(1).levels(i).level_name||',';
808 l_levels(l_levels.count+1):=p_dimension.level_groups(1).levels(i).level_name;
809 end loop;
810 --rec levels
811 for i in 1..p_dimension.level_groups(1).rec_levels.count loop
812 g_stmt:=g_stmt||p_dimension.level_groups(1).rec_levels(i).level_name||',';
813 l_levels(l_levels.count+1):=p_dimension.level_groups(1).rec_levels(i).level_name;
814 end loop;
815 g_stmt:=substr(g_stmt,1,length(g_stmt)-1);
816 g_stmt:=g_stmt||')';
817 bsc_aw_dbms_aw.execute(g_stmt);
818 --add the levels. this is reqd when dim merge happens
819 for i in 1..l_levels.count loop
820 g_stmt:='CHGDFN '||p_dimension.dim_name||' base add '||l_levels(i);
821 bsc_aw_dbms_aw.execute(g_stmt); --we have added ORA-36656 to ignore list
822 end loop;
823 p_dimension.filter_variable:=p_dimension.dim_name||'.filter.bool';
827 bsc_aw_dbms_aw.execute(g_stmt);
824 p_dimension.limit_variable:=p_dimension.dim_name||'.LB';
825 p_dimension.base_value_cube:=p_dimension.dim_name||'.BV';
826 g_stmt:='dfn '||p_dimension.filter_variable||' variable boolean <'||p_dimension.dim_name||'>';
828 g_stmt:='dfn '||p_dimension.limit_variable||' variable boolean <'||p_dimension.dim_name||'>';
829 bsc_aw_dbms_aw.execute(g_stmt);
830 g_stmt:='dfn '||p_dimension.base_value_cube||' variable TEXT <'||p_dimension.dim_name||'>';
831 bsc_aw_dbms_aw.execute(g_stmt);
832 Exception when others then
833 log_n('Exception in create_ccdim '||sqlerrm);
834 raise;
835 End;
836
837 /*
838 when creating the level names, for zero code, we add the names of each zero code level
839 instead of just one "ZERO". the reason is that we may have more than 1 zero code level and
840 we may wish to aggregate on only one of them
841
842 Rec dim have 2 imp. denorm imp and norm imp
843 for denorm imp:
844 for rec dim, the level name dim will contain the values of the dim
845 if we look at the relation, the X axis is cc dim values.
846 the Y axis is the individual level values, ex: employee
847
848 for norm imp:
849 we have the relation which stores the self relation and norm relation
850 a
851 b c
852 d e
853 is represented as
854 rel: A B C D E A A B B
855 dim: a b c d e B C D E
856 we have one more axis in normal hier of rec dim. this axis contains level_name_dim and has values '1', '2' etc
857 depending on how many parents a child has. this extra dim is to take care of cases when a child can have multiple parents
858 if in a dim, the max parents a child has is 3, level_name_dim contains '1', '2' and '3'
859 for non rec dim, the Y axis is the name of the level
860 */
861 procedure create_level_name_dim(p_dimension in out nocopy dimension_r) is
862 Begin
863 p_dimension.level_name_dim:=p_dimension.dim_name||'.levels';
864 g_stmt:='dfn '||p_dimension.level_name_dim||' dimension text';
865 bsc_aw_dbms_aw.execute(g_stmt);
866 if p_dimension.recursive='Y' then
867 if p_dimension.recursive_norm_hier='N' then
868 --we create the cube that is used to store the position of the level values
869 --larry will have 1, john wookey 2 etc. before agg we do limit .levels to .levels.position LE adv_sum_profile
870 --this name .position is assumes in bsc_aw_load_kpi.limit_dim_levels
871 p_dimension.rec_level_position_cube:=p_dimension.dim_name||'.levels.position';
872 g_stmt:='dfn '||p_dimension.rec_level_position_cube||' number<'||p_dimension.level_name_dim||'>';
873 bsc_aw_dbms_aw.execute(g_stmt);
874 else
875 g_stmt:='maintain '||p_dimension.level_name_dim||' merge ''1'''; --default
876 bsc_aw_dbms_aw.execute(g_stmt);
877 end if;
878 else
879 for i in 1..p_dimension.level_groups(1).parent_child.count loop
880 if p_dimension.level_groups(1).parent_child(i).parent_level is not null
881 and p_dimension.level_groups(1).parent_child(i).child_level is not null then
882 g_stmt:='maintain '||p_dimension.level_name_dim||' merge '''||p_dimension.level_groups(1).parent_child(i).parent_level||'.'||
883 p_dimension.level_groups(1).parent_child(i).child_level||'''';
884 bsc_aw_dbms_aw.execute(g_stmt);
885 end if;
886 end loop;
887 --zero code
888 for i in 1..p_dimension.level_groups(1).zero_levels.count loop
889 g_stmt:='maintain '||p_dimension.level_name_dim||' merge '''||p_dimension.level_groups(1).zero_levels(i).level_name||'.'||
890 p_dimension.level_groups(1).zero_levels(i).child_level_name||'''';
891 bsc_aw_dbms_aw.execute(g_stmt);
892 end loop;
893 end if;
894 --create level name dim for each level
895 if p_dimension.dim_type <> 'std' then
896 for i in 1..p_dimension.level_groups(1).levels.count loop
897 p_dimension.level_groups(1).levels(i).level_name_dim:=p_dimension.level_groups(1).levels(i).level_name||'.levels';
898 g_stmt:='dfn '||p_dimension.level_groups(1).levels(i).level_name_dim||' dimension text';
899 bsc_aw_dbms_aw.execute(g_stmt);
900 g_stmt:='maintain '||p_dimension.level_groups(1).levels(i).level_name_dim||' merge '''||
901 get_zero_level(p_dimension,p_dimension.level_groups(1).levels(i).level_name).level_name||'.'||
902 p_dimension.level_groups(1).levels(i).level_name||'''';
903 bsc_aw_dbms_aw.execute(g_stmt);
904 end loop;
905 end if;
906 --
907 Exception when others then
908 log_n('Exception in create_level_name_dim '||sqlerrm);
909 raise;
910 End;
911
912 /*for normal rec hier, level name dim contains ''1'' ''2'' etc. for denorm, its the level values themselves
913 */
914 procedure create_relation(p_dimension in out nocopy dimension_r) is
915 Begin
916 g_stmt:='dfn '||p_dimension.relation_name||' relation '||p_dimension.dim_name||'<'||p_dimension.dim_name||' '||p_dimension.level_name_dim||'>';
917 bsc_aw_dbms_aw.execute(g_stmt);
918 --create each level's relation
919 if p_dimension.dim_type <> 'std' then
920 for i in 1..p_dimension.level_groups(1).levels.count loop
921 p_dimension.level_groups(1).levels(i).relation_name:=p_dimension.level_groups(1).levels(i).level_name||'.rel';
922 g_stmt:='dfn '||p_dimension.level_groups(1).levels(i).relation_name||' relation '||p_dimension.level_groups(1).levels(i).level_name||'<'||
923 p_dimension.level_groups(1).levels(i).level_name||' '||p_dimension.level_groups(1).levels(i).level_name_dim||'>';
924 bsc_aw_dbms_aw.execute(g_stmt);
925 end loop;
926 end if;
927 Exception when others then
928 log_n('Exception in create_relation '||sqlerrm);
929 raise;
930 End;
931
932 procedure create_dim_program(p_dimension dimension_r) is
933 Begin
934 --recursive dim do not have initial and inc programs. they only have initial program. the delta data is
935 --prepared in bsc loader in temp tables.
936 if p_dimension.recursive='Y' then
940 create_dim_program_rec_norm(p_dimension,'initial');
937 if p_dimension.recursive_norm_hier='N' then --denorm implementation
938 create_dim_program_rec(p_dimension,'initial');
939 else --norm hier normalization
941 end if;
942 else
943 create_dim_program(p_dimension,'initial');
944 create_dim_program(p_dimension,'inc');
945 end if;
946 Exception when others then
947 log_n('Exception in create_dim_program '||sqlerrm);
948 raise;
949 End;
950
951 /*
952 This api is only called for NON rec dims
953 */
954 procedure create_dim_program(p_dimension dimension_r,p_mode varchar2) is
955 --
956 l_pgm varchar2(300);
957 --
958 Begin
959 --when this api is called, there must not be a program in the workspace
960 g_commands.delete;
961 if p_mode='initial' then
962 l_pgm:=p_dimension.initial_load_program;
963 else
964 l_pgm:=p_dimension.inc_load_program;
965 end if;
966 if l_pgm is not null then
967 bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_pgm||' program');
968 bsc_aw_utility.add_g_commands(g_commands,'allstat');
969 bsc_aw_utility.add_g_commands(g_commands,p_dimension.dim_name||'.LB=false');
970 for i in 1..p_dimension.level_groups.count loop
971 create_dim_program(p_dimension,p_dimension.level_groups(i),p_mode);
972 end loop;
973 --base value
974 --base value cubes will be used in the olap table functions for the kpi
975 if p_dimension.base_value_cube is not null then
976 bsc_aw_utility.add_g_commands(g_commands,p_dimension.base_value_cube||'=baseval('||p_dimension.dim_name||')');
977 end if;
978 --
979 bsc_aw_utility.exec_program_commands(l_pgm,g_commands);
980 end if;
981 Exception when others then
982 log_n('Exception in create_dim_program '||sqlerrm);
983 raise;
984 End;
985
986 procedure create_dim_program(p_dimension dimension_r,p_level_group level_group_r,p_mode varchar2) is
987 --
988 l_levels levels_tb;
989 l_parent_child dim_parent_child_tb;
990 l_zero_levels zero_levels_tb;
991 l_snowflake_levels levels_tb;
992 Begin
993 --if p_level_group.data_source.data_source is not null, then p_level_group.data_source.inc_data_source is also not null
994 if p_level_group.data_source.data_source is not null then
995 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
996 --this is where we cannot assume that the data source has the same name as the levels.
997 --this is for DBI dimensions (non rec)
998 --we will assume that in data_source.pk_col the order is an exact match to p_level_group.levels
999 l_levels.delete;
1000 l_parent_child.delete;
1001 l_zero_levels.delete;
1002 l_snowflake_levels.delete;
1003 --
1004 l_levels:=p_level_group.levels;
1005 if p_dimension.dim_type <> 'std' then
1006 l_snowflake_levels:=p_level_group.levels;
1007 end if;
1008 l_zero_levels:=p_level_group.zero_levels;
1009 for i in 1..p_level_group.parent_child.count loop
1010 if p_level_group.parent_child(i).parent_level is not null and p_level_group.parent_child(i).child_level is not null then
1011 l_parent_child(l_parent_child.count+1):=p_level_group.parent_child(i);
1012 end if;
1013 end loop;
1014 for i in 1..p_level_group.data_source.pk_col.count loop
1015 bsc_aw_utility.add_g_commands(g_commands,p_level_group.data_source.pk_col(i)||', --');
1016 end loop;
1017 bsc_aw_utility.trim_g_commands(g_commands,4,' --');
1018 bsc_aw_utility.add_g_commands(g_commands,'from --');
1019 if p_mode='initial' then
1020 bsc_aw_utility.add_g_commands(g_commands,p_level_group.data_source.data_source);
1021 else
1022 bsc_aw_utility.add_g_commands(g_commands,p_level_group.data_source.inc_data_source);
1023 end if;
1024 --there is no where clause. the where clause is in the data_source
1025 create_dim_program(p_dimension,l_levels,l_parent_child,l_zero_levels,l_snowflake_levels);
1026 else
1027 --default construction of the data source. this is for bsc dimensions.
1028 for i in 1..p_level_group.levels.count loop
1029 l_levels.delete;
1030 l_parent_child.delete;
1031 l_zero_levels.delete;
1032 l_snowflake_levels.delete;
1033 --
1034 l_levels(l_levels.count+1):=p_level_group.levels(i);
1035 if p_dimension.dim_type <> 'std' then
1036 l_snowflake_levels(l_snowflake_levels.count+1):=p_level_group.levels(i);
1037 end if;
1038 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
1039 bsc_aw_utility.add_g_commands(g_commands,nvl(p_level_group.levels(i).level_name||'.'||p_level_group.levels(i).pk.pk,'CODE')||', --');
1040 for j in 1..p_level_group.parent_child.count loop
1041 if p_level_group.parent_child(j).child_level=p_level_group.levels(i).level_name and p_level_group.parent_child(j).parent_level is not null then
1042 l_parent_child(l_parent_child.count+1):=p_level_group.parent_child(j);
1043 l_levels(l_levels.count+1):=get_level(p_dimension,p_level_group.parent_child(j).parent_level);
1044 bsc_aw_utility.add_g_commands(g_commands,p_level_group.parent_child(j).child_level||'.'||p_level_group.parent_child(j).child_fk||', --');
1045 end if;
1046 end loop;
1047 bsc_aw_utility.trim_g_commands(g_commands,4,' --');
1048 bsc_aw_utility.add_g_commands(g_commands,'from --');
1049 bsc_aw_utility.add_g_commands(g_commands,p_level_group.levels(i).level_name);
1050 for j in 1..p_level_group.zero_levels.count loop
1051 if p_level_group.zero_levels(j).child_level_name=p_level_group.levels(i).level_name and p_level_group.zero_levels(j).level_name is not null then
1052 l_zero_levels(l_zero_levels.count+1):=p_level_group.zero_levels(j);
1053 end if;
1054 end loop;
1055 create_dim_program(p_dimension,l_levels,l_parent_child,l_zero_levels,l_snowflake_levels);
1059 --
1056 end loop;
1057 end if;
1058 --setting the kpi limit cubes is now done outside the program in bsc_aw_load_dim.set_kpi_limit_variables
1060 /*deletes for dimensions
1061 delete are handled in the following way
1062 delete table has 2 columns. dim_level and delete_value. say we have geog dim. city,state,country
1063 we want to delete all cities in ca and ca
1064 the table has
1065 'city' 'SF'
1066 'city' 'LA'
1067 'state' 'CA'
1068 for each level in the dim, we see if there are delete values. if there are we do the following
1069 mark limit cubes for the parent value for re-agg.
1070 in our case, we will mark state of ca and country of usa for re-agg
1071 the dim values are deleted in dim load module
1072 */
1073 --p_level_group.levels(i).level_name
1074 bsc_aw_utility.add_g_commands(g_commands,'allstat');
1075 if p_level_group.levels.count>1 then
1076 for i in 1..p_level_group.levels.count loop
1077 --we only need these for levels that have parents.
1078 if level_has_parents(p_level_group.parent_child,p_level_group.levels(i).level_name) then
1079 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select delete_value from bsc_aw_dim_delete '||
1080 ' where dim_level=\'''||p_level_group.levels(i).level_name||'\''');
1081 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
1082 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
1083 /*5064802. we need to handle the case where the dim value in bsc_aw_dim_delete does not exist in aw dim. go on append mode. then
1084 dim loader delete will delete them */
1085 bsc_aw_utility.add_g_commands(g_commands,':append '||p_level_group.levels(i).level_name||' --');
1086 bsc_aw_utility.add_g_commands(g_commands,'then --');
1087 for j in 1..p_level_group.parent_child.count loop
1088 if p_level_group.parent_child(j).child_level=p_level_group.levels(i).level_name
1089 and p_level_group.parent_child(j).parent_level is not null then
1090 bsc_aw_utility.add_g_commands(g_commands,p_dimension.dim_name||'.LB('||p_dimension.dim_name||' '||
1091 p_dimension.relation_name||'('||p_dimension.dim_name||' '||p_level_group.parent_child(j).child_level||' '||
1092 p_dimension.level_name_dim||' \'''||p_level_group.parent_child(j).parent_level||'.'||
1093 p_level_group.parent_child(j).child_level||
1094 '\''))=TRUE --');
1095 --setting of kpi limit cubes is done in bsc_aw_load_dim.set_kpi_limit_variables
1096 end if;
1097 end loop;
1098 bsc_aw_utility.trim_g_commands(g_commands,3,null);
1099 bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
1100 bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
1101 end if;
1102 end loop;
1103 end if;
1104 Exception when others then
1105 log_n('Exception in create_dim_program '||sqlerrm);
1106 raise;
1107 End;
1108
1109 procedure create_dim_program(
1110 p_dimension dimension_r,
1111 p_levels levels_tb,
1112 p_parent_child dim_parent_child_tb,
1113 p_zero_levels zero_levels_tb,
1114 p_snowflake_levels levels_tb
1115 ) is
1116 Begin
1117 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
1118 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
1119 for i in 1..p_levels.count loop
1120 bsc_aw_utility.add_g_commands(g_commands,':append '||p_levels(i).level_name||' --');
1121 end loop;
1122 /*
1123 initial hier was A>- B>- C, it changed to A>- C
1124 in this case, we need to reaggregate for A and B
1125 the bottom relation part is true for BSC as well as a non rec dbi dim
1126 there was a bug here. we were marking A and B. this will reagg for A and C correctly, but not for B. we had to mark the
1127 children for B, only then data is aggregated for B correctly. the way around this is to mark the parents only. B and C.
1128 after we load the dim, we setthe kpi limit cubes. there, we today to limit dim to dim.LB, then for all the kpi limit cubes
1129 set kpi.LB=true. instead, we will have limit dim to dim.LB, then limit dim to children using dim.rel, then we set the kpi
1130 limit cubes
1131 */
1132 if p_parent_child.count>0 or p_zero_levels.count>0 or p_snowflake_levels.count>0 then
1133 bsc_aw_utility.add_g_commands(g_commands,'then --');
1134 --see if the relations have any change
1135 for i in 1..p_parent_child.count loop
1136 if p_parent_child(i).parent_level is not null then
1137 bsc_aw_utility.add_g_commands(g_commands,'if '||p_dimension.relation_name||'('||p_dimension.dim_name||' '||p_parent_child(i).child_level||' '||
1138 p_dimension.level_name_dim||' \'''||p_parent_child(i).parent_level||'.'||p_parent_child(i).child_level||'\'') ne NA --');
1139 bsc_aw_utility.add_g_commands(g_commands,'AND '||p_dimension.relation_name||'('||p_dimension.dim_name||' '||p_parent_child(i).child_level||' '||
1140 p_dimension.level_name_dim||' \'''||p_parent_child(i).parent_level||'.'||p_parent_child(i).child_level||
1141 '\'') ne '||p_dimension.dim_name||'('||
1142 p_parent_child(i).parent_level||' '||p_parent_child(i).parent_level||') --');
1143 bsc_aw_utility.add_g_commands(g_commands,'then do --');
1144 bsc_aw_utility.add_g_commands(g_commands,p_dimension.dim_name||'.LB('||p_dimension.dim_name||' '||p_dimension.relation_name||'('||
1145 p_dimension.dim_name||' '||p_parent_child(i).child_level||' '||p_dimension.level_name_dim||' \'''||
1146 p_parent_child(i).parent_level||'.'||p_parent_child(i).child_level||'\''))=TRUE --');
1147 --bug fix.initially, it was p_parent_child(i).child_level||')=TRUE --');
1148 bsc_aw_utility.add_g_commands(g_commands,p_dimension.dim_name||'.LB('||p_dimension.dim_name||' '||p_parent_child(i).parent_level||')=TRUE --');
1149 bsc_aw_utility.add_g_commands(g_commands,'doend --');
1150 end if;
1151 end loop;
1152 --now, assign the new values to the rel
1153 for i in 1..p_parent_child.count loop
1154 if p_parent_child(i).parent_level is not null then
1158 p_parent_child(i).parent_level||' '||p_parent_child(i).parent_level||') --');
1155 bsc_aw_utility.add_g_commands(g_commands,p_dimension.relation_name||'('||p_dimension.dim_name||' '||p_parent_child(i).child_level||' '||
1156 p_dimension.level_name_dim||' \'''||p_parent_child(i).parent_level||'.'||p_parent_child(i).child_level||
1157 '\'')='||p_dimension.dim_name||'('||
1159 end if;
1160 end loop;
1161 --add the relations for zero code
1162 if p_zero_levels.count>0 then
1163 for i in 1..p_zero_levels.count loop
1164 --we have to make sure we eliminate circular relation. all the levels contain one row that is the zero row. for example city's 0
1165 --rolls to state 0 which rolls to country 0. we need to make sure that 0 is not the parent of zero
1166 /* error looks like this:
1167 ORA-36036: (XSMHIERCK01) One or more loops have been detected in relationship BSC_AW!BSC_CCDIM_100_101_102_103.REL over
1168 BSC_AW!BSC_CCDIM_100_101_102_103. The 1 items involved are <BSC_D_PRODUCT_FAMILY_AW: 0>.
1169 */
1170 bsc_aw_utility.add_g_commands(g_commands,'if '||p_zero_levels(i).child_level_name||' NE \''0\'' --');
1171 bsc_aw_utility.add_g_commands(g_commands,'then do --');
1172 bsc_aw_utility.add_g_commands(g_commands,p_dimension.relation_name||'('||p_dimension.dim_name||' '||p_zero_levels(i).child_level_name||' '||
1173 p_dimension.level_name_dim||' \'''||p_zero_levels(i).level_name||'.'||p_zero_levels(i).child_level_name||
1174 '\'')='||p_dimension.dim_name||'('||
1175 p_zero_levels(i).child_level_name||' \''0\'') --');
1176 bsc_aw_utility.add_g_commands(g_commands,'doend --');
1177 end loop;
1178 end if;
1179 --snowflake relation. we need to populate the levels's relation that maps the levels to the zero code
1180 --in the future we may need to see if we need to create relations with other levels
1181 --but zero code will still be there.
1182 for i in 1..p_snowflake_levels.count loop
1183 if p_snowflake_levels(i).relation_name is not null then
1184 bsc_aw_utility.add_g_commands(g_commands,'if '||p_snowflake_levels(i).level_name||' NE \''0\'' --');
1185 bsc_aw_utility.add_g_commands(g_commands,'then do --');
1186 bsc_aw_utility.add_g_commands(g_commands,p_snowflake_levels(i).relation_name||'('||p_snowflake_levels(i).level_name_dim||
1187 ' \'''||get_zero_level(p_dimension,p_snowflake_levels(i).level_name).level_name||'.'||p_snowflake_levels(i).level_name||'\'')='||
1188 p_snowflake_levels(i).level_name||'('||p_snowflake_levels(i).level_name||' \''0\'') --');
1189 bsc_aw_utility.add_g_commands(g_commands,'doend --');
1190 end if;
1191 end loop;
1192 end if;
1193 bsc_aw_utility.trim_g_commands(g_commands,3,null);
1194 bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
1195 bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
1196 Exception when others then
1197 log_n('Exception in create_dim_program '||sqlerrm);
1198 raise;
1199 End;
1200
1201 function level_has_parents(
1202 p_parent_child dim_parent_child_tb,
1203 p_level_name varchar2) return boolean is
1204 Begin
1205 for i in 1..p_parent_child.count loop
1206 if p_parent_child(i).child_level=p_level_name and p_parent_child(i).parent_level is not null then
1207 return true;
1208 end if;
1209 end loop;
1210 return false;
1211 Exception when others then
1212 log_n('Exception in level_has_parents '||sqlerrm);
1213 raise;
1214 End;
1215
1216 /*This api is only called for rec dims ONLY implemented in denorm way
1217 denorm implementation possible only for dbi based rec dim
1218 */
1219 procedure create_dim_program_rec(p_dimension dimension_r,p_mode varchar2) is
1220 l_pgm varchar2(300);
1221 Begin
1222 g_commands.delete;
1223 if p_dimension.initial_load_program is not null then
1224 l_pgm:=p_dimension.initial_load_program;
1225 bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_pgm||' program');
1226 bsc_aw_utility.add_g_commands(g_commands,'allstat');
1227 bsc_aw_utility.add_g_commands(g_commands,p_dimension.dim_name||'.LB=false');
1228 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
1229 --in rec dim, there must be only ONE real level. then there is a virtual rec level
1230 --we select twice since we append to the real and virtual dim
1231 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.pk_col(1)||' code1, --');
1232 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.pk_col(1)||' code2 --');
1233 bsc_aw_utility.add_g_commands(g_commands,'from --');
1234 --for rec dim, we full refresh each time
1235 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.data_source);
1236 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
1237 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
1238 bsc_aw_utility.add_g_commands(g_commands,':append '||p_dimension.level_groups(1).levels(1).level_name||' --');
1239 bsc_aw_utility.add_g_commands(g_commands,':append '||p_dimension.level_groups(1).rec_levels(1).level_name||' ');
1240 bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
1241 bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
1242 --now, the child parent relations
1243 --we select parent_col twice for the virtual dim and also the name dim.
1244 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
1245 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.child_col||','||
1246 p_dimension.level_groups(1).data_source.parent_col||','||
1247 p_dimension.level_groups(1).data_source.parent_col||','||p_dimension.level_groups(1).data_source.position_col||' --');
1248 bsc_aw_utility.add_g_commands(g_commands,'from --');
1249 --relation fully refreshed each time. see notes
1250 --this does not mean kpi will need full agg. dim load will figure out if there is hier change
1254 bsc_aw_utility.add_g_commands(g_commands,':match '||p_dimension.level_groups(1).levels(1).level_name||' --');
1251 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.denorm_data_source);
1252 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
1253 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
1255 bsc_aw_utility.add_g_commands(g_commands,':match '||p_dimension.level_groups(1).rec_levels(1).level_name||' --');
1256 bsc_aw_utility.add_g_commands(g_commands,':append '||p_dimension.level_name_dim||' --');
1257 bsc_aw_utility.add_g_commands(g_commands,':'||p_dimension.rec_level_position_cube||' --');
1258 bsc_aw_utility.add_g_commands(g_commands,'then '||p_dimension.relation_name||'('||p_dimension.dim_name||' '||
1259 p_dimension.level_groups(1).levels(1).level_name||')='||
1260 p_dimension.dim_name||'('||p_dimension.level_groups(1).rec_levels(1).level_name||' '||p_dimension.level_groups(1).rec_levels(1).level_name||')');
1261 bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
1262 bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
1263 --set the value of the old relations to NA. these parent child relations no longer exist
1264 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
1265 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.parent_col||', --');
1266 bsc_aw_utility.add_g_commands(g_commands,'1, --');
1267 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.child_col||', --');
1268 bsc_aw_utility.add_g_commands(g_commands,'1, --');
1269 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.parent_col||' --');
1270 bsc_aw_utility.add_g_commands(g_commands,'from --');
1271 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.denorm_change_data_source);
1272 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
1273 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
1274 bsc_aw_utility.add_g_commands(g_commands,':match '||p_dimension.level_groups(1).levels(1).level_name||' --');
1275 bsc_aw_utility.add_g_commands(g_commands,':'||p_dimension.dim_name||'.LB('||
1276 p_dimension.dim_name||' '||p_dimension.level_groups(1).levels(1).level_name||') --');
1277 --we set kpi limit cubes in bsc_aw_load_dim.set_kpi_limit_variables
1278 --mark change for managers
1279 bsc_aw_utility.add_g_commands(g_commands,':match '||p_dimension.level_groups(1).levels(1).level_name||' --');
1280 bsc_aw_utility.add_g_commands(g_commands,':'||p_dimension.dim_name||'.LB('||
1281 p_dimension.dim_name||' '||p_dimension.level_groups(1).levels(1).level_name||') --');
1282 --mark change for employees
1283 bsc_aw_utility.add_g_commands(g_commands,':match '||p_dimension.level_name_dim||' --');
1284 bsc_aw_utility.add_g_commands(g_commands,'then '||p_dimension.relation_name||'('||p_dimension.dim_name||' '||
1285 p_dimension.level_groups(1).levels(1).level_name||')=NA ');
1286 --base value cubes will be used in the olap table functions for the kpi
1287 if p_dimension.base_value_cube is not null then
1288 bsc_aw_utility.add_g_commands(g_commands,p_dimension.base_value_cube||'=baseval('||p_dimension.dim_name||')');
1289 end if;
1290 bsc_aw_utility.exec_program_commands(l_pgm,g_commands);
1291 end if;
1292 Exception when others then
1293 log_n('Exception in create_dim_program_rec '||sqlerrm);
1294 raise;
1295 End;
1296
1297 /*if the rec dim is implemented with normal hier. this is the default implementation
1298 */
1299 procedure create_dim_program_rec_norm(p_dimension dimension_r,p_mode varchar2) is
1300 l_pgm varchar2(300);
1301 Begin
1302 --if the temp variable does not exist, create it.
1303 bsc_aw_dbms_aw.execute_ne('dfn temp_text text');
1304 g_commands.delete;
1305 if p_dimension.initial_load_program is not null then
1306 l_pgm:=p_dimension.initial_load_program;
1307 bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_pgm||' program');
1308 bsc_aw_utility.add_g_commands(g_commands,'allstat');
1309 bsc_aw_utility.add_g_commands(g_commands,p_dimension.dim_name||'.LB=false');
1310 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
1311 --in rec dim, there must be only ONE real level. then there is a virtual rec level
1312 --we select twice since we append to the real and virtual dim
1313 if p_dimension.level_groups(1).data_source.data_source is not null then --dbi dimension
1314 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.pk_col(1)||' code1, --');
1315 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.pk_col(1)||' code2 --');
1316 else
1317 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).levels(1).pk.pk||' code1, --');
1318 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).levels(1).pk.pk||' code2 --');
1319 end if;
1320 bsc_aw_utility.add_g_commands(g_commands,'from --');
1321 --for rec dim, we full refresh each time
1322 if p_dimension.level_groups(1).data_source.data_source is not null then
1323 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).data_source.data_source);
1324 else
1325 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).levels(1).level_name);
1326 end if;
1327 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
1328 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
1329 bsc_aw_utility.add_g_commands(g_commands,':append '||p_dimension.level_groups(1).levels(1).level_name||' --');
1330 bsc_aw_utility.add_g_commands(g_commands,':append '||p_dimension.level_groups(1).rec_levels(1).level_name||' --');
1331 bsc_aw_utility.add_g_commands(g_commands,'then --');
1332 bsc_aw_utility.add_g_commands(g_commands,p_dimension.relation_name||'('||
1333 p_dimension.dim_name||' '||p_dimension.level_groups(1).levels(1).level_name||' '||p_dimension.level_name_dim||' \''1\'')='||
1334 p_dimension.dim_name||'('||p_dimension.level_groups(1).rec_levels(1).level_name||' '||p_dimension.level_groups(1).rec_levels(1).level_name||')');
1338 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
1335 bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
1336 bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
1337 --now, the child parent relations
1339 if p_dimension.level_groups(1).data_source.data_source is not null then
1340 bsc_aw_utility.add_g_commands(g_commands,'child,parent,to_char(id) from bsc_aw_temp_pc');
1341 else
1342 --chil fk is the parent col and parent pk is the child col
1343 bsc_aw_utility.add_g_commands(g_commands,p_dimension.level_groups(1).parent_child(1).parent_pk||','||
1344 p_dimension.level_groups(1).parent_child(1).child_fk||',to_char(rank() over(partition by '||p_dimension.level_groups(1).parent_child(1).parent_pk||' '||
1345 'order by '||p_dimension.level_groups(1).parent_child(1).child_fk||')) id --');
1346 bsc_aw_utility.add_g_commands(g_commands,'from '||p_dimension.level_groups(1).levels(1).level_name||' where '||
1347 p_dimension.level_groups(1).parent_child(1).child_fk||' is not null');
1348 end if;
1349 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
1350 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
1351 bsc_aw_utility.add_g_commands(g_commands,':match '||p_dimension.level_groups(1).rec_levels(1).level_name||' --');
1352 bsc_aw_utility.add_g_commands(g_commands,':temp_text --');
1353 bsc_aw_utility.add_g_commands(g_commands,':append '||p_dimension.level_name_dim||' --');
1354 bsc_aw_utility.add_g_commands(g_commands,'then --');
1355 bsc_aw_utility.add_g_commands(g_commands,'if '||p_dimension.relation_name||'('||
1356 p_dimension.dim_name||' '||p_dimension.level_groups(1).rec_levels(1).level_name||') NE NA AND '||
1357 p_dimension.relation_name||'('||
1358 p_dimension.dim_name||' '||p_dimension.level_groups(1).rec_levels(1).level_name||') NE '||
1359 p_dimension.dim_name||'('||p_dimension.level_groups(1).rec_levels(1).level_name||' temp_text) --');
1360 bsc_aw_utility.add_g_commands(g_commands,'then do --');
1361 bsc_aw_utility.add_g_commands(g_commands,p_dimension.dim_name||'.LB('||p_dimension.dim_name||' '||
1362 p_dimension.relation_name||'('||p_dimension.dim_name||' '||p_dimension.level_groups(1).rec_levels(1).level_name||'))=TRUE --');
1363 bsc_aw_utility.add_g_commands(g_commands,p_dimension.dim_name||'.LB('||
1364 p_dimension.level_groups(1).rec_levels(1).level_name||' temp_text)=TRUE --');
1365 bsc_aw_utility.add_g_commands(g_commands,'doend --');
1366 bsc_aw_utility.add_g_commands(g_commands,p_dimension.relation_name||'('||
1367 p_dimension.dim_name||' '||p_dimension.level_groups(1).rec_levels(1).level_name||')='||
1368 p_dimension.dim_name||'('||p_dimension.level_groups(1).rec_levels(1).level_name||' temp_text)');
1369 bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
1370 bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
1371 --handle dim delete. this is just like for normal dim
1372 --if there are deletes, we have to mark the parents for these values for re-agg
1373 --for rec, dim the delete values are loaded into bsc_aw_dim_delete with the real level name, not the virtual parent
1374 --level name
1375 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
1376 bsc_aw_utility.add_g_commands(g_commands,'delete_value from bsc_aw_dim_delete '||
1377 ' where dim_level=\'''||p_dimension.level_groups(1).levels(1).level_name||'\''');
1378 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
1379 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
1380 bsc_aw_utility.add_g_commands(g_commands,':match '||p_dimension.level_groups(1).rec_levels(1).level_name||' --');
1381 bsc_aw_utility.add_g_commands(g_commands,'then --');
1382 bsc_aw_utility.add_g_commands(g_commands,p_dimension.dim_name||'.LB('||p_dimension.dim_name||' '||
1383 p_dimension.relation_name||'('||p_dimension.dim_name||' '||p_dimension.level_groups(1).rec_levels(1).level_name||'))=TRUE ');
1384 --base value cubes will be used in the olap table functions for the kpi
1385 if p_dimension.base_value_cube is not null then
1386 bsc_aw_utility.add_g_commands(g_commands,p_dimension.base_value_cube||'=baseval('||p_dimension.dim_name||')');
1387 end if;
1388 bsc_aw_utility.exec_program_commands(l_pgm,g_commands);
1389 end if;
1390 Exception when others then
1391 log_n('Exception in create_dim_program_rec_norm '||sqlerrm);
1392 raise;
1393 End;
1394
1395 procedure dmp_g_dimensions(p_dimensions dimension_tb) is
1396 Begin
1397 log('----------------------');
1398 for i in 1..p_dimensions.count loop
1399 dmp_dimension(p_dimensions(i));
1400 end loop;
1401 Exception when others then
1402 log_n('Exception in dmp_g_dimensions '||sqlerrm);
1403 raise;
1404 End;
1405
1406 procedure dmp_dimension(p_dim dimension_r) is
1407 Begin
1408 log('====');
1409 log('Dimension dmp');
1410 log('Dimension : '||p_dim.dim_name||' Rel:'||p_dim.relation_name||' rec:'||p_dim.recursive||
1411 ' Imp with Norm Hier:'||p_dim.recursive_norm_hier);
1412 log('Property: '||p_dim.property);
1413 for i in 1..p_dim.level_groups.count loop
1414 dmp_level_group(p_dim.level_groups(i));
1415 end loop;
1416 log('KPI for dim:-');
1417 for i in 1..p_dim.kpi_for_dim.count loop
1418 log(p_dim.kpi_for_dim(i).kpi);
1419 log(' KPI Dimsets for KPI:-');
1420 for j in 1..p_dim.kpi_for_dim(i).dim_set.count loop
1421 log(' '||p_dim.kpi_for_dim(i).dim_set(j));
1422 end loop;
1423 end loop;
1424 log('====');
1425 Exception when others then
1426 log_n('Exception in dmp_dimension '||sqlerrm);
1427 raise;
1428 End;
1429
1430 procedure dmp_level_group(p_level_group level_group_r) is
1431 Begin
1432 log('Level group '||p_level_group.level_group_name);
1433 log('Levels:-');
1434 for i in 1..p_level_group.levels.count loop
1435 log(p_level_group.levels(i).level_name||' id='||p_level_group.levels(i).level_id||' position='||
1439 log('Zero Levels:-');
1436 p_level_group.levels(i).position||' property='||p_level_group.levels(i).property||' pk='||
1437 p_level_group.levels(i).pk.pk||' datatype='||p_level_group.levels(i).pk.data_type);
1438 end loop;
1440 for i in 1..p_level_group.zero_levels.count loop
1441 log(p_level_group.zero_levels(i).level_name||' child level name='||
1442 p_level_group.zero_levels(i).child_level_name);
1443 end loop;
1444 log('Recursive Levels:-');
1445 for i in 1..p_level_group.rec_levels.count loop
1446 log(p_level_group.rec_levels(i).level_name);
1447 end loop;
1448 log('Parent Child:-');
1449 for i in 1..p_level_group.parent_child.count loop
1450 log(p_level_group.parent_child(i).parent_level||'('||p_level_group.parent_child(i).parent_pk||') '||
1451 p_level_group.parent_child(i).child_level||'('||p_level_group.parent_child(i).child_fk||') '||
1452 p_level_group.parent_child(i).level_set);
1453 end loop;
1454 log('Data Source:-');
1455 for i in 1..p_level_group.data_source.dim_name.count loop
1456 log(p_level_group.data_source.pk_col(i)||' -> '||p_level_group.data_source.dim_name(i));
1457 end loop;
1458 log('Initial data source='||p_level_group.data_source.data_source);
1459 log('Inc data source='||p_level_group.data_source.inc_data_source);
1460 log('denorm child col='||p_level_group.data_source.child_col||', denorm parent col='||
1461 p_level_group.data_source.parent_col||', denorm position col='||p_level_group.data_source.position_col);
1462 log('Initial denorm data source='||p_level_group.data_source.denorm_data_source);
1463 log('Change denorm data source='||p_level_group.data_source.denorm_change_data_source);
1464 log('---');
1465 Exception when others then
1466 log_n('Exception in dmp_level_group '||sqlerrm);
1467 raise;
1468 End;
1469
1470 /*
1471 this creates the std dim
1472 TYPE
1473 PROJECTION
1474 these dim are part of every kpi. in the kpi adapter, each kpi is also given these 2 dimensions
1475 */
1476 procedure create_std_dim(p_dimensions in out nocopy dimension_tb) is
1477 Begin
1478 create_type_dim(p_dimensions);
1479 create_projection_dim(p_dimensions);
1480 Exception when others then
1481 log_n('Exception in create_std_dim '||sqlerrm);
1482 raise;
1483 End;
1484
1485 --creates std type dim to the data structure
1486 procedure create_type_dim(p_dimensions in out nocopy dimension_tb) is
1487 l_dim dimension_r;
1488 Begin
1489 --bug fix 5636695
1490 -- we have used a new public var l_dim here, so as to take an empty structure and fill in
1491 -- the issue here was we had level values and parent child values retained from the previous
1492 -- kpi run p_dimensions... which was causing problem
1493 --- this will clear the previous entries from the structure
1494 l_dim.dim_name:='TYPE';
1495 l_dim.corrected:='N';
1496 l_dim.concat:='N';
1497 l_dim.dim_type:='std';
1498 l_dim.relation_name:=null;--not going to be used. type is single level dim
1499 l_dim.recursive:='N';
1500 l_dim.recursive_norm_hier:='N';
1501 l_dim.initial_load_program:='load_type.initial';
1502 l_dim.inc_load_program:='load_type.inc';
1503 --
1504 l_dim.level_groups(1).levels(1).level_name:='TYPE';
1505 l_dim.level_groups(1).levels(1).level_id:=1;
1506 l_dim.level_groups(1).levels(1).position:=1;
1507 l_dim.level_groups(1).levels(1).property:=null;
1508 l_dim.level_groups(1).levels(1).pk.pk:='type';
1509 l_dim.level_groups(1).levels(1).pk.data_type:='text';
1510 --
1511 l_dim.level_groups(1).parent_child(1).parent_level:=null;
1512 l_dim.level_groups(1).parent_child(1).child_level:='TYPE';
1513 l_dim.level_groups(1).parent_child(1).parent_pk:=null;
1514 l_dim.level_groups(1).parent_child(1).child_fk:=null;
1515 --
1516 l_dim.level_groups(1).data_source.dim_name(1):='TYPE';
1517 l_dim.level_groups(1).data_source.pk_col(1):='data_type';
1518 l_dim.level_groups(1).data_source.data_source:='(select distinct data_type from bsc_sys_benchmarks_b)';
1519 l_dim.level_groups(1).data_source.inc_data_source:='(select distinct data_type from bsc_sys_benchmarks_b)';
1520 --
1521 p_dimensions(p_dimensions.count+1):= l_dim;
1522 get_kpi_for_dim(p_dimensions(p_dimensions.count));
1523 set_dim_properties(p_dimensions(p_dimensions.count));
1524 Exception when others then
1525 log_n('Exception in create_type_dim '||sqlerrm);
1526 raise;
1527 End;
1528
1529 --creates std projection dim to the data structure
1530 procedure create_projection_dim(p_dimensions in out nocopy dimension_tb) is
1531 l_dim dimension_r;
1532 Begin
1533 --bug fix 5636695
1534 -- we have used a new public var l_dim here, so as to take an empty structure and fill in
1535 -- the issue here was we had level values and parent child values retained from the previous
1536 -- kpi run p_dimensions... which was causing problem
1537 --- this will clear the previous entries from the structure
1538 l_dim.dim_name:='PROJECTION';
1539 l_dim.corrected:='N';
1540 l_dim.concat:='N';
1541 l_dim.dim_type:='std';
1542 l_dim.relation_name:=null;--not going to be used. type is single level dim
1543 l_dim.recursive:='N';
1544 l_dim.recursive_norm_hier:='N';
1545 l_dim.initial_load_program:=null;--projection dim has 2 values, Y or N
1546 l_dim.inc_load_program:=null;
1547 --
1548 l_dim.level_groups(1).levels(1).level_name:='PROJECTION';
1549 l_dim.level_groups(1).levels(1).level_id:=1;
1550 l_dim.level_groups(1).levels(1).position:=1;
1551 l_dim.level_groups(1).levels(1).seed_values(1):='''Y''';
1552 l_dim.level_groups(1).levels(1).seed_values(2):='''N''';
1553 l_dim.level_groups(1).levels(1).property:=null;
1554 l_dim.level_groups(1).levels(1).pk.pk:='projection';
1555 l_dim.level_groups(1).levels(1).pk.data_type:='text';
1556 --
1557 l_dim.level_groups(1).parent_child(1).parent_level:=null;
1558 l_dim.level_groups(1).parent_child(1).child_level:='PROJECTION';
1562 --projection has no data source
1559 l_dim.level_groups(1).parent_child(1).parent_pk:=null;
1560 l_dim.level_groups(1).parent_child(1).child_fk:=null;
1561 --
1563 --
1564 p_dimensions(p_dimensions.count+1):= l_dim;
1565 get_kpi_for_dim(p_dimensions(p_dimensions.count));
1566 set_dim_properties(p_dimensions(p_dimensions.count));
1567 Exception when others then
1568 log_n('Exception in create_projection_dim '||sqlerrm);
1569 raise;
1570 End;
1571
1572 procedure create_dmp_program(p_dim_level varchar2,p_name varchar2) is
1573 Begin
1574 g_commands.delete;
1575 bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_name||' program');
1576 bsc_aw_utility.add_g_commands(g_commands,'allstat');
1577 bsc_aw_utility.add_g_commands(g_commands,'sql prepare c1 from --');
1578 bsc_aw_utility.add_g_commands(g_commands,'insert into bsc_aw_dim_data values (\'''||p_dim_level||'\'',:'||p_dim_level||') DIRECT=YES');
1579 bsc_aw_utility.add_g_commands(g_commands,'for '||p_dim_level);
1580 bsc_aw_utility.add_g_commands(g_commands,'do');
1581 bsc_aw_utility.add_g_commands(g_commands,'sql execute c1');
1582 bsc_aw_utility.add_g_commands(g_commands,'if sqlcode ne 0');
1583 bsc_aw_utility.add_g_commands(g_commands,'then break');
1584 bsc_aw_utility.add_g_commands(g_commands,'doend');
1585 bsc_aw_utility.exec_program_commands(p_name,g_commands);
1586 Exception when others then
1587 log_n('Exception in create_dmp_program '||sqlerrm);
1588 raise;
1589 End;
1590
1591 function get_zero_level(p_dimension dimension_r,p_level varchar2) return zero_levels_r is
1592 l_zero_level zero_levels_r;
1593 Begin
1594 for i in 1..p_dimension.level_groups.count loop
1595 l_zero_level:=get_zero_level(p_dimension.level_groups(i),p_level);
1596 if l_zero_level.level_name is not null then
1597 return l_zero_level;
1598 end if;
1599 end loop;
1600 return null;
1601 Exception when others then
1602 log_n('Exception in get_zero_level '||sqlerrm);
1603 raise;
1604 End;
1605
1606 function get_zero_level(p_level_group level_group_r,p_level varchar2) return zero_levels_r is
1607 Begin
1608 for i in 1..p_level_group.zero_levels.count loop
1609 if p_level_group.zero_levels(i).child_level_name=p_level then
1610 return p_level_group.zero_levels(i);
1611 end if;
1612 end loop;
1613 return null;
1614 Exception when others then
1615 log_n('Exception in get_zero_level '||sqlerrm);
1616 raise;
1617 End;
1618
1619 /*
1620 given a dim, what are the kpi limit cubes. called in dim load
1621 */
1622 procedure get_dim_kpi_limit_cubes(
1623 p_dim varchar2,
1624 p_limit_cubes out nocopy dbms_sql.varchar2_table,
1625 p_aggregate_marker out nocopy dbms_sql.varchar2_table,
1626 p_reset_cubes out nocopy dbms_sql.varchar2_table
1627 ) is
1628 --
1629 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
1630 Begin
1631 l_olap_object_relation.delete;
1632 bsc_aw_md_api.get_bsc_olap_object_relation(p_dim,'dimension',null,null,null,l_olap_object_relation);
1633 for i in 1..l_olap_object_relation.count loop
1634 --do this only if the limit cube is "actual" limit cube. not if its target limit cube
1635 if bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim set type',',')='actual' then
1636 if l_olap_object_relation(i).relation_type='kpi limit cube' then
1637 p_limit_cubes(p_limit_cubes.count+1):=l_olap_object_relation(i).relation_object;
1638 elsif l_olap_object_relation(i).relation_type='kpi aggregate marker' then
1639 p_aggregate_marker(p_aggregate_marker.count+1):=l_olap_object_relation(i).relation_object;
1640 elsif l_olap_object_relation(i).relation_type='kpi reset cube' then
1641 p_reset_cubes(p_reset_cubes.count+1):=l_olap_object_relation(i).relation_object;
1642 end if;
1643 end if;
1644 end loop;
1645 Exception when others then
1646 log_n('Exception in get_dim_kpi_limit_cubes '||sqlerrm);
1647 raise;
1648 End;
1649
1650 /*
1651 this procedure needs to see if there are existing dim for the levels and then to see if
1652 1. we can merge into an existing dim
1653 2. we need to correct the existing dim
1654 p_dim is the new image of the dim
1655 for each level in p_dim, check to see the existing dim.
1656 if not already processed
1657 see if we can merge.
1658 if all the old dim levels are in new dim
1659 if each relation of old dim is in new dim
1660 if old dim.number of levels<new dim.number of levels and old dim.corrected='N'
1661 merge
1662 endif
1663 endif
1664 endif
1665 if merge
1666 merge_dim(old dim, new dim)
1667 else
1668 correct dim(old dim,p_flag) p_flag is corrected or noop
1669 endif
1670 endif
1671 --
1672 correct dim:
1673 invalidate any level not used by aw kpi
1674 invalidate any non existent relation
1675 if any of the above 2 happens
1676 create level groups
1677 correct level name dim / relation
1678 recreate program
1679 delete old metadata
1680 insert new metadata
1681 endif
1682
1683 merge:
1684 delete old metadata
1685 create dim with merge mode
1686 add level name dim / relation
1687 create new levels
1688 recreate program
1689 insert metadata
1690 --
1691 */
1692 procedure correct_old_dim(p_dim dimension_r,p_flag out nocopy varchar2) is
1693 l_old_dim dimension_tb;
1694 l_merge_dim dimension_tb;
1695 l_new_dim dimension_r;
1696 l_old_dim_list dbms_sql.varchar2_table;
1697 l_old_dim_action dbms_sql.varchar2_table;
1698 Begin
1699 if g_debug then
1700 log('correct_old_dim for '||p_dim.dim_name);
1701 end if;
1702 p_flag:='create all';
1703 l_new_dim:=p_dim;
1704 bsc_aw_md_api.get_ccdim_for_levels(p_dim,l_old_dim_list);
1708 if g_debug then
1705 if l_old_dim_list.count>0 then
1706 for i in 1..l_old_dim_list.count loop
1707 bsc_aw_md_api.get_dim_md(l_old_dim_list(i),l_old_dim(i));
1709 log('Old dim dmp');
1710 dmp_dimension(l_old_dim(i));
1711 end if;
1712 --
1713 check_old_dim_operation(l_old_dim(i),p_dim,l_old_dim_action(i));
1714 if l_old_dim_action(i)='drop' then
1715 drop_dim(l_old_dim(i).dim_name);
1716 elsif l_old_dim_action(i)='correct' then
1717 correct_dim(l_old_dim(i));--correct old dim
1718 end if;
1719 end loop;
1720 --see if there is no change to the dim
1721 for i in 1..l_old_dim_list.count loop
1722 if l_old_dim_action(i)='same' then
1723 p_flag:='noop';
1724 exit;
1725 end if;
1726 end loop;
1727 if p_flag<>'noop' then
1728 --see if merge needed.
1729 for i in 1..l_old_dim_list.count loop
1730 if l_old_dim_action(i)='merge' then
1731 l_merge_dim(l_merge_dim.count+1):=l_old_dim(i);
1732 end if;
1733 end loop;
1734 if l_merge_dim.count>0 then
1735 merge_dim(l_merge_dim,l_new_dim);--merge into the dim with the best level match. rest are corrected
1736 p_flag:='noop'; --for the new dim. we need no op since we have already merged new into old
1737 end if;
1738 end if;
1739 end if;
1740 if g_debug then
1741 log('For the new dimension '||p_dim.dim_name||', final action required is '||p_flag);
1742 end if;
1743 Exception when others then
1744 log_n('Exception in correct_old_dim '||sqlerrm);
1745 raise;
1746 End;
1747
1748 /*
1749 correct old dim. this is essentially disintegrating the old dim
1750 correction of a dim is with respect to the metadata that exists in the system, not with respect to new dim
1751 corrections can happen on dim that had been corrected in previous runs
1752 */
1753 procedure correct_dim(p_old_dim in out nocopy dimension_r) is
1754 l_old_level_groups level_group_tb;
1755 l_new_levels levels_tb;
1756 l_new_zero_levels zero_levels_tb;
1757 l_new_parent_child dim_parent_child_tb;
1758 l_new_level_groups level_group_tb;
1759 Begin
1760 if g_debug then
1761 log('Correct Old dim '||p_old_dim.dim_name);
1762 end if;
1763 l_old_level_groups:=p_old_dim.level_groups;
1764 --
1765 for i in 1..p_old_dim.level_groups.count loop
1766 correct_levels(p_old_dim.level_groups(i).levels,l_new_levels);
1767 p_old_dim.level_groups(i).levels:=l_new_levels;
1768 end loop;
1769 for i in 1..p_old_dim.level_groups.count loop
1770 correct_zero_levels(p_old_dim.level_groups(i).levels,p_old_dim.level_groups(i).zero_levels,l_new_zero_levels);
1771 p_old_dim.level_groups(i).zero_levels:=l_new_zero_levels;
1772 end loop;
1773 for i in 1..p_old_dim.level_groups.count loop
1774 correct_parent_child(p_old_dim.level_groups(i),p_old_dim.level_groups(i).parent_child,l_new_parent_child);
1775 p_old_dim.level_groups(i).parent_child:=l_new_parent_child;
1776 end loop;
1777 correct_level_name_dim(p_old_dim.level_name_dim,l_old_level_groups,p_old_dim.level_groups);
1778 correct_level_groups(p_old_dim.level_groups,l_new_level_groups);
1779 p_old_dim.level_groups.delete;
1780 for i in 1..l_new_level_groups.count loop
1781 p_old_dim.level_groups(p_old_dim.level_groups.count+1):=l_new_level_groups(i);
1782 end loop;
1783 --
1784 /*
1785 following steps in correction
1786 drop old metadata
1787 drop programs
1788 re-create programs
1789 */
1790 --set the property to corrected
1791 p_old_dim.corrected:='Y';
1792 set_dim_properties(p_old_dim);
1793 if g_debug then
1794 log('After correct levels,zero levels,parent child,level name dim and level groups');
1795 dmp_dimension(p_old_dim);
1796 end if;
1797 bsc_aw_md_api.drop_dim(p_old_dim.dim_name);
1798 create_dim_program(p_old_dim);
1799 bsc_aw_md_api.create_dim_objects(p_old_dim);
1800 Exception when others then
1801 log_n('Exception in correct_dim '||sqlerrm);
1802 raise;
1803 End;
1804
1805 /*
1806 there can be multiple dim for merge. D1 with 10 levels could have split into 3,3,2,1,1
1807 if now all 10 levels are together, the 5 new dim are candidates for merge. merge into the one with
1808 3 levels. all others are corrected
1809 */
1810 procedure merge_dim(p_old_dim in out nocopy dimension_tb,p_new_dim in out nocopy dimension_r) is
1811 l_new_levels dbms_sql.varchar2_table;
1812 l_number_match dbms_sql.number_table;
1813 l_max number;
1814 Begin
1815 for i in 1..p_new_dim.level_groups.count loop
1816 for j in 1..p_new_dim.level_groups(i).levels.count loop
1817 bsc_aw_utility.merge_value(l_new_levels,p_new_dim.level_groups(i).levels(j).level_name);
1818 end loop;
1819 end loop;
1820 --
1821 for i in 1..p_old_dim.count loop
1822 l_number_match(i):=0;
1823 for j in 1..p_old_dim(i).level_groups.count loop
1824 for k in 1..p_old_dim(i).level_groups(j).levels.count loop
1825 if bsc_aw_utility.in_array(l_new_levels,p_old_dim(i).level_groups(j).levels(k).level_name) then
1826 l_number_match(i):=l_number_match(i)+1;
1827 end if;
1828 end loop;
1829 end loop;
1830 if g_debug then
1831 log('For old dim '||p_old_dim(i).dim_name||', number of levels matching new dim='||l_number_match(i));
1832 end if;
1833 end loop;
1834 l_max:=bsc_aw_utility.get_max(l_number_match);
1835 for i in 1..p_old_dim.count loop
1836 if l_number_match(i)=l_max then
1837 merge_dim(p_old_dim(i),p_new_dim);
1838 --correct all others
1839 for j in 1..p_old_dim.count loop
1840 if i <> j then
1841 correct_dim(p_old_dim(j));
1842 end if;
1843 end loop;
1844 exit;
1845 end if;
1846 end loop;
1850 End;
1847 Exception when others then
1848 log_n('Exception in merge_dim '||sqlerrm);
1849 raise;
1851
1852 procedure merge_dim(p_old_dim dimension_r,p_new_dim in out nocopy dimension_r) is
1853 l_new_dim_name varchar2(200);
1854 Begin
1855 if g_debug then
1856 log('Merge dim Old dim '||p_old_dim.dim_name||', New dim '||p_new_dim.dim_name);
1857 end if;
1858 l_new_dim_name:=p_new_dim.dim_name;
1859 --change the names in new dim to that of the old
1860 p_new_dim.dim_name:=p_old_dim.dim_name;
1861 p_new_dim.dim_type:=p_old_dim.dim_type;
1862 p_new_dim.concat:=p_old_dim.concat;
1863 p_new_dim.property:=p_old_dim.property;
1864 p_new_dim.relation_name:=p_old_dim.relation_name;
1865 p_new_dim.level_name_dim:=p_old_dim.level_name_dim;
1866 p_new_dim.recursive:=p_old_dim.recursive;
1867 p_new_dim.recursive_norm_hier:=p_old_dim.recursive_norm_hier;
1868 p_new_dim.initial_load_program:=p_old_dim.initial_load_program;
1869 p_new_dim.inc_load_program:=p_old_dim.inc_load_program;
1870 p_new_dim.filter_variable:=p_old_dim.filter_variable;
1871 p_new_dim.limit_variable:=p_old_dim.limit_variable;
1872 p_new_dim.rec_level_position_cube:=p_old_dim.rec_level_position_cube;
1873 p_new_dim.base_value_cube:=p_old_dim.base_value_cube;
1874 for i in 1..p_new_dim.level_groups.count loop
1875 for j in 1..p_new_dim.level_groups(i).data_source.dim_name.count loop
1876 if p_new_dim.level_groups(i).data_source.dim_name(j)=l_new_dim_name then
1877 p_new_dim.level_groups(i).data_source.dim_name(j):=p_new_dim.dim_name;
1878 end if;
1879 end loop;
1880 end loop;
1881 --
1882 bsc_aw_md_api.drop_dim(p_old_dim.dim_name);--drop metadata
1883 create_dim_objects(p_new_dim);--creates objects and metadata
1884 Exception when others then
1885 log_n('Exception in merge_dim '||sqlerrm);
1886 raise;
1887 End;
1888
1889 /*
1890 remove levels that are no more used by aw kpi
1891 */
1892 procedure correct_levels(p_old_level levels_tb,p_new_level out nocopy levels_tb) is
1893 Begin
1894 for i in 1..p_old_level.count loop
1895 if bsc_aw_bsc_metadata.is_level_used_by_aw_kpi(p_old_level(i).level_name) then
1896 p_new_level(p_new_level.count+1):=p_old_level(i);
1897 end if;
1898 end loop;
1899 Exception when others then
1900 log_n('Exception in correct_levels '||sqlerrm);
1901 raise;
1902 End;
1903
1904 procedure correct_zero_levels(p_new_level levels_tb,p_old_zero_level zero_levels_tb,p_new_zero_level out nocopy zero_levels_tb) is
1905 l_levels dbms_sql.varchar2_table;
1906 Begin
1907 for i in 1..p_new_level.count loop
1908 l_levels(l_levels.count+1):=p_new_level(i).level_name;
1909 end loop;
1910 for i in 1..p_old_zero_level.count loop
1911 if bsc_aw_utility.in_array(l_levels,p_old_zero_level(i).child_level_name) then
1912 p_new_zero_level(p_new_zero_level.count+1):=p_old_zero_level(i);
1913 end if;
1914 end loop;
1915 Exception when others then
1916 log_n('Exception in correct_zero_levels '||sqlerrm);
1917 raise;
1918 End;
1919
1920 procedure correct_parent_child(p_level_group level_group_r,p_old_pc dim_parent_child_tb,p_new_pc out nocopy dim_parent_child_tb) is
1921 l_parents dbms_sql.varchar2_table;
1922 l_levels dbms_sql.varchar2_table;
1923 l_dim_levels dbms_sql.varchar2_table;
1924 Begin
1925 for i in 1..p_level_group.levels.count loop
1926 l_dim_levels(l_dim_levels.count+1):=p_level_group.levels(i).level_name;
1927 end loop;
1928 for i in 1..p_old_pc.count loop
1929 if bsc_aw_utility.in_array(l_dim_levels,p_old_pc(i).child_level) then
1930 bsc_aw_utility.merge_value(l_levels,p_old_pc(i).child_level);
1931 end if;
1932 end loop;
1933 for i in 1..l_levels.count loop
1934 l_parents.delete;
1935 bsc_aw_bsc_metadata.get_parent_level(l_levels(i),l_parents); --does not return top node
1936 for j in 1..p_old_pc.count loop
1937 if p_old_pc(j).child_level=l_levels(i) then
1938 --if p_old_pc(j).parent_level is null and l_parents.count=0
1939 --and bsc_aw_utility.in_array(l_dim_levels,p_old_pc(j).parent_level) then
1940 if p_old_pc(j).parent_level is null then
1941 p_new_pc(p_new_pc.count+1):=p_old_pc(j);
1942 elsif l_parents.count=0 then
1943 p_new_pc(p_new_pc.count+1):=p_old_pc(j);
1944 p_new_pc(p_new_pc.count).parent_level:=null;
1945 p_new_pc(p_new_pc.count).parent_pk:=null;
1946 p_new_pc(p_new_pc.count).child_fk:=null;
1947 elsif bsc_aw_utility.in_array(l_parents,p_old_pc(j).parent_level)
1948 and bsc_aw_utility.in_array(l_dim_levels,p_old_pc(j).parent_level) then
1949 p_new_pc(p_new_pc.count+1):=p_old_pc(j);
1950 else
1951 --this relation is no more valid
1952 null;
1953 end if;
1954 end if;
1955 end loop;
1956 end loop;
1957 Exception when others then
1958 log_n('Exception in correct_parent_child '||sqlerrm);
1959 raise;
1960 End;
1961
1962 procedure correct_level_name_dim(p_level_name_dim varchar2,p_old_level_groups level_group_tb,p_new_level_groups level_group_tb) is
1963 l_flag boolean;
1964 Begin
1965 for i in 1..p_old_level_groups.count loop
1966 for j in 1..p_old_level_groups(i).parent_child.count loop
1967 l_flag:=false;
1968 for k in 1..p_new_level_groups.count loop
1969 for m in 1..p_new_level_groups(k).parent_child.count loop
1970 if nvl(p_old_level_groups(i).parent_child(j).child_level,'^')=nvl(p_new_level_groups(k).parent_child(m).child_level,'^')
1971 and nvl(p_old_level_groups(i).parent_child(j).parent_level,'^')=nvl(p_new_level_groups(k).parent_child(m).parent_level,'^') then
1972 l_flag:=true;
1973 exit;
1974 end if;
1975 end loop;
1976 if l_flag then
1977 exit;
1978 end if;
1979 end loop;
1983 bsc_aw_dbms_aw.execute('maintain '||p_level_name_dim||' delete '''||p_old_level_groups(i).parent_child(j).parent_level||'.'||
1980 if l_flag=false then
1981 if p_old_level_groups(i).parent_child(j).child_level is not null and p_old_level_groups(i).parent_child(j).parent_level is not null then
1982 --remove this
1984 p_old_level_groups(i).parent_child(j).child_level||'''');
1985 end if;
1986 end if;
1987 end loop;
1988 --correct zero entries
1989 for j in 1..p_old_level_groups(i).zero_levels.count loop
1990 l_flag:=false;
1991 for k in 1..p_new_level_groups.count loop
1992 for m in 1..p_new_level_groups(k).zero_levels.count loop
1993 if p_old_level_groups(i).zero_levels(j).level_name=p_new_level_groups(k).zero_levels(m).level_name then
1994 l_flag:=true;
1995 exit;
1996 end if;
1997 end loop;
1998 if l_flag then
1999 exit;
2000 end if;
2001 end loop;
2002 if l_flag=false then
2003 bsc_aw_dbms_aw.execute('maintain '||p_level_name_dim||' delete '''||p_old_level_groups(i).zero_levels(j).level_name||'.'||
2004 p_old_level_groups(i).zero_levels(j).child_level_name||'''');
2005 end if;
2006 end loop;
2007 end loop;
2008 Exception when others then
2009 log_n('Exception in correct_level_name_dim '||sqlerrm);
2010 raise;
2011 End;
2012
2013 /*
2014 level groups are never going to merge. they can only break up. so the number of level groups can only rise
2015 look at each level group. if there is break in connection, break up the level group
2016 */
2017 procedure correct_level_groups(p_old_level_groups level_group_tb,p_new_level_groups out nocopy level_group_tb) is
2018 l_max_lg number;
2019 l_new_level_groups level_group_tb;
2020 Begin
2021 l_max_lg:=0;
2022 for i in 1..p_old_level_groups.count loop
2023 l_new_level_groups.delete;
2024 correct_level_groups(p_old_level_groups(i),l_new_level_groups);
2025 for j in 1..l_new_level_groups.count loop
2026 l_max_lg:=l_max_lg+1;
2027 l_new_level_groups(j).level_group_name:='level group.'||l_max_lg;
2028 p_new_level_groups(p_new_level_groups.count+1):=l_new_level_groups(j);
2029 end loop;
2030 end loop;
2031 Exception when others then
2032 log_n('Exception in correct_level_groups '||sqlerrm);
2033 raise;
2034 End;
2035
2036 procedure correct_level_groups(p_old_level_group level_group_r,p_new_level_groups out nocopy level_group_tb) is
2037 --
2038 l_max_set number;
2039 l_level_considered dbms_sql.varchar2_table;
2040 l_zero_level zero_levels_r;
2041 l_rec_level rec_levels_r;
2042 l_old_level_group level_group_r;
2043 Begin
2044 l_old_level_group:=p_old_level_group;
2045 for i in 1..l_old_level_group.parent_child.count loop
2046 l_old_level_group.parent_child(i).level_set:=null;
2047 end loop;
2048 group_levels_into_sets(l_old_level_group.parent_child);
2049 l_max_set:=0;
2050 for i in 1..l_old_level_group.parent_child.count loop
2051 if l_old_level_group.parent_child(i).level_set>l_max_set then
2052 l_max_set:=l_old_level_group.parent_child(i).level_set;
2053 end if;
2054 end loop;
2055 --
2056 if l_max_set=1 then --no level group split
2057 p_new_level_groups(p_new_level_groups.count+1):=l_old_level_group;
2058 else
2059 for i in 1..l_max_set loop
2060 p_new_level_groups(p_new_level_groups.count+1).level_group_name:=l_old_level_group.level_group_name;
2061 /*
2062 the following are determined
2063 levels,
2064 parent_child,
2065 zero_levels,
2066 rec_levels,
2067 the following are blindly copied.
2068 data_source,
2069 property
2070 */
2071 p_new_level_groups(p_new_level_groups.count).data_source:=l_old_level_group.data_source;
2072 p_new_level_groups(p_new_level_groups.count).property:=l_old_level_group.property;
2073 l_level_considered.delete;
2074 for j in 1..l_old_level_group.parent_child.count loop
2075 if l_old_level_group.parent_child(j).level_set=i then
2076 p_new_level_groups(p_new_level_groups.count).parent_child(p_new_level_groups(p_new_level_groups.count).parent_child.count+1):=
2077 l_old_level_group.parent_child(j);
2078 --
2079 if l_old_level_group.parent_child(j).child_level is not null
2080 and bsc_aw_utility.in_array(l_level_considered,l_old_level_group.parent_child(j).child_level)=false then
2081 p_new_level_groups(p_new_level_groups.count).levels(p_new_level_groups(p_new_level_groups.count).levels.count+1):=
2082 get_level(l_old_level_group,l_old_level_group.parent_child(j).child_level);
2083 l_zero_level:=get_zero_level(l_old_level_group,l_old_level_group.parent_child(j).child_level);
2084 if l_zero_level.level_name is not null then
2085 p_new_level_groups(p_new_level_groups.count).zero_levels(p_new_level_groups(p_new_level_groups.count).zero_levels.count+1):=
2086 l_zero_level;
2087 end if;
2088 l_rec_level:=get_rec_level(l_old_level_group,l_old_level_group.parent_child(j).child_level);
2089 if l_rec_level.level_name is not null then
2090 p_new_level_groups(p_new_level_groups.count).rec_levels(p_new_level_groups(p_new_level_groups.count).rec_levels.count+1):=
2091 l_rec_level;
2092 end if;
2093 l_level_considered(l_level_considered.count+1):=l_old_level_group.parent_child(j).child_level;
2094 end if;
2095 --
2096 if l_old_level_group.parent_child(j).parent_level is not null
2097 and bsc_aw_utility.in_array(l_level_considered,l_old_level_group.parent_child(j).parent_level)=false then
2098 p_new_level_groups(p_new_level_groups.count).levels(p_new_level_groups(p_new_level_groups.count).levels.count+1):=
2102 p_new_level_groups(p_new_level_groups.count).zero_levels(p_new_level_groups(p_new_level_groups.count).zero_levels.count+1):=
2099 get_level(l_old_level_group,l_old_level_group.parent_child(j).parent_level);
2100 l_zero_level:=get_zero_level(l_old_level_group,l_old_level_group.parent_child(j).parent_level);
2101 if l_zero_level.level_name is not null then
2103 l_zero_level;
2104 end if;
2105 l_rec_level:=get_rec_level(l_old_level_group,l_old_level_group.parent_child(j).parent_level);
2106 if l_rec_level.level_name is not null then
2107 p_new_level_groups(p_new_level_groups.count).rec_levels(p_new_level_groups(p_new_level_groups.count).rec_levels.count+1):=
2108 l_rec_level;
2109 end if;
2110 l_level_considered(l_level_considered.count+1):=l_old_level_group.parent_child(j).parent_level;
2111 end if;
2112 end if;
2113 end loop;
2114 end loop;
2115 end if;
2116 Exception when others then
2117 log_n('Exception in correct_level_groups '||sqlerrm);
2118 raise;
2119 End;
2120
2121 /*
2122 p_flag = correct, merge, noop or same
2123 p_flag is from the perspective of p_old_dim
2124 */
2125 procedure check_old_dim_operation(p_old_dim dimension_r,p_new_dim dimension_r,p_flag out nocopy varchar2) is
2126 l_level_list dbms_sql.varchar2_table;
2127 l_old_level_list dbms_sql.varchar2_table;
2128 l_level_group level_group_r;
2129 l_pc_comparison number;
2130 Begin
2131 if g_debug then
2132 log('check_old_dim_operation, check Old dim '||p_old_dim.dim_name||' with New dim '||p_new_dim.dim_name);
2133 end if;
2134 for i in 1..p_new_dim.level_groups.count loop
2135 for j in 1..p_new_dim.level_groups(i).levels.count loop
2136 l_level_list(l_level_list.count+1):=p_new_dim.level_groups(i).levels(j).level_name;
2137 end loop;
2138 end loop;
2139 for i in 1..p_old_dim.level_groups.count loop
2140 for j in 1..p_old_dim.level_groups(i).levels.count loop
2141 l_old_level_list(l_old_level_list.count+1):=p_old_dim.level_groups(i).levels(j).level_name;
2142 end loop;
2143 end loop;
2144 --see if the dim is un-used and can be dropped
2145 if p_flag is null then
2146 if p_old_dim.corrected='Y' and p_old_dim.kpi_for_dim.count=0 then
2147 p_flag:='drop';
2148 end if;
2149 if g_debug then
2150 if p_flag is not null then
2151 log('Dim '||p_old_dim.dim_name||' is already corrected and unused. Drop...');
2152 end if;
2153 end if;
2154 end if;
2155 --
2156 if p_flag is null then
2157 for i in 1..l_old_level_list.count loop
2158 if bsc_aw_utility.in_array(l_level_list,l_old_level_list(i))=false then -- no merge possible. correction reqd
2159 p_flag:='correct';
2160 exit;
2161 end if;
2162 end loop;
2163 if p_flag is not null then
2164 if g_debug then
2165 log('All levels of old not in new.p_flag='||p_flag);
2166 end if;
2167 end if;
2168 end if;
2169 --
2170 if p_flag is null then --check to see if all old relations exist in the new one
2171 for i in 1..p_old_dim.level_groups.count loop --we check each level group
2172 l_level_group:=get_level_group(p_new_dim,p_old_dim.level_groups(i).levels(1).level_name);
2173 l_pc_comparison:=compare_pc_relations(l_level_group.parent_child,p_old_dim.level_groups(i).parent_child);
2174 if not(l_pc_comparison=0 or l_pc_comparison=2) then --old is NOT in new
2175 p_flag:='correct';
2176 exit;
2177 elsif l_pc_comparison=2 and p_old_dim.corrected='N' then
2178 p_flag:='merge';
2179 end if;
2180 end loop;
2181 if p_flag is not null then
2182 if g_debug then
2183 log('Checked old vs new level relations.p_flag='||p_flag);
2184 end if;
2185 end if;
2186 end if;
2187 if p_flag is null then
2188 if p_old_dim.corrected='N' then --old and new dim are the same. no change.
2189 p_flag:='same';
2190 else
2191 p_flag:='noop'; --no op reqd on old dim (which is a corrected dim)
2192 end if;
2193 end if;
2194 if g_debug then
2195 log('Final flag '||p_flag);
2196 end if;
2197 Exception when others then
2198 log_n('Exception in check_old_dim_operation '||sqlerrm);
2199 raise;
2200 End;
2201
2202 /*
2203 given a level, get the level group
2204 note>>> one level can only belong to one level group
2205 */
2206 function get_level_group(p_dim dimension_r,p_level varchar2) return level_group_r is
2207 Begin
2208 for i in 1..p_dim.level_groups.count loop
2209 for j in 1..p_dim.level_groups(i).levels.count loop
2210 if p_dim.level_groups(i).levels(j).level_name=p_level then
2211 return p_dim.level_groups(i);
2212 end if;
2213 end loop;
2214 end loop;
2215 return null;
2216 Exception when others then
2217 log_n('Exception in get_level_group '||sqlerrm);
2218 raise;
2219 End;
2220
2221 /*
2222 -1 : 2 rel diff
2223 0 : 2 rel same
2224 1 : 1 is in 2
2225 2: 2 is in 1
2226 */
2227 function compare_pc_relations(p_pc_1 dim_parent_child_tb,p_pc_2 dim_parent_child_tb) return number is
2228 --
2229 l_pc_1 bsc_aw_utility.parent_child_tb;
2230 l_pc_2 bsc_aw_utility.parent_child_tb;
2231 Begin
2232 for i in 1..p_pc_1.count loop
2233 l_pc_1(i).parent:=p_pc_1(i).parent_level;
2234 l_pc_1(i).child:=p_pc_1(i).child_level;
2235 end loop;
2236 for i in 1..p_pc_2.count loop
2237 l_pc_2(i).parent:=p_pc_2(i).parent_level;
2238 l_pc_2(i).child:=p_pc_2(i).child_level;
2239 end loop;
2240 return bsc_aw_utility.compare_pc_relations(l_pc_1,l_pc_2);
2241 Exception when others then
2242 log_n('Exception in compare_pc_relations '||sqlerrm);
2246 function get_level(p_dimension dimension_r,p_level varchar2) return levels_r is
2243 raise;
2244 End;
2245
2247 l_level levels_r;
2248 Begin
2249 for i in 1..p_dimension.level_groups.count loop
2250 l_level:=get_level(p_dimension.level_groups(i),p_level);
2251 if l_level.level_name is not null then
2252 return l_level;
2253 end if;
2254 end loop;
2255 return null;
2256 Exception when others then
2257 log_n('Exception in get_level '||sqlerrm);
2258 raise;
2259 End;
2260
2261 function get_level(p_level_group level_group_r,p_level varchar2) return levels_r is
2262 Begin
2263 for i in 1..p_level_group.levels.count loop
2264 if p_level_group.levels(i).level_name=p_level then
2265 return p_level_group.levels(i);
2266 end if;
2267 end loop;
2268 return null;
2269 Exception when others then
2270 log_n('Exception in get_level '||sqlerrm);
2271 raise;
2272 End;
2273
2274 function get_rec_level(p_level_group level_group_r,p_level varchar2) return rec_levels_r is
2275 Begin
2276 for i in 1..p_level_group.rec_levels.count loop
2277 if p_level_group.rec_levels(i).child_level_name=p_level then
2278 return p_level_group.rec_levels(i);
2279 end if;
2280 end loop;
2281 return null;
2282 Exception when others then
2283 log_n('Exception in get_rec_level '||sqlerrm);
2284 raise;
2285 End;
2286
2287 procedure check_dim_name_conflict(p_dimension in out nocopy dimension_r) is
2288 l_count number;
2289 Begin
2290 l_count:=0;
2291 loop --till we get unique name
2292 if l_count>bsc_aw_utility.g_infinite_loop then
2293 log('Infinite loop detected in check_dim_name_conflict');
2294 raise bsc_aw_utility.g_exception;
2295 end if;
2296 if bsc_aw_md_api.is_dim_present(p_dimension.dim_name)=false then --no name conflict
2297 exit;
2298 end if;
2299 l_count:=l_count+1;
2300 make_dim_name(p_dimension,get_dim_name_hash_string(p_dimension)||'.'||bsc_aw_utility.get_dbms_time);
2301 end loop;
2302 if g_debug then
2303 log('check_dim_name_conflict, final dim name '||p_dimension.dim_name);
2304 end if;
2305 Exception when others then
2306 log_n('Exception in check_dim_name_conflict '||sqlerrm);
2307 raise;
2308 End;
2309
2310 function get_default_lg_name return varchar2 is
2311 Begin
2312 return 'level group.1';
2313 Exception when others then
2314 log_n('Exception in get_default_lg_name '||sqlerrm);
2315 raise;
2316 End;
2317
2318 function get_std_dim_list return dbms_sql.varchar2_table is
2319 l_dim dbms_sql.varchar2_table;
2320 Begin
2321 l_dim(1):='TYPE';
2322 l_dim(2):='PROJECTION';
2323 return l_dim;
2324 Exception when others then
2325 log_n('Exception in get_std_dim_list '||sqlerrm);
2326 raise;
2327 End;
2328
2329 function get_preloaded_dim_list return dbms_sql.varchar2_table is
2330 l_dim dbms_sql.varchar2_table;
2331 Begin
2332 l_dim(1):='PROJECTION';
2333 return l_dim;
2334 Exception when others then
2335 log_n('Exception in get_preloaded_dim_list '||sqlerrm);
2336 raise;
2337 End;
2338
2339 /*given a set of child levels and parent levels, find the hier subset that includes all of them */
2340 function get_hier_subset(p_parent_child dim_parent_child_tb,p_parent_level dbms_sql.varchar2_table,
2341 p_child_level dbms_sql.varchar2_table) return dim_parent_child_tb is
2342 l_pc_subset dim_parent_child_tb;
2343 l_pc_subset_temp dim_parent_child_tb;
2344 Begin
2345 for i in 1..p_child_level.count loop
2346 for j in 1..p_parent_level.count loop
2347 l_pc_subset_temp.delete;
2348 l_pc_subset_temp:=get_hier_subset(p_parent_child,p_parent_level(j),p_child_level(i));
2349 if l_pc_subset_temp.count>0 then
2350 merge_hier(l_pc_subset,l_pc_subset_temp);
2351 end if;
2352 end loop;
2353 end loop;
2354 return l_pc_subset;
2355 Exception when others then
2356 log_n('Exception in get_hier_subset '||sqlerrm);
2357 raise;
2358 End;
2359
2360 /*merges the entries from p_pc_subset_merge into p_pc_subset */
2361 procedure merge_hier(p_pc_subset in out nocopy dim_parent_child_tb,p_pc_subset_merge dim_parent_child_tb) is
2362 l_pc_subset dim_parent_child_tb;
2363 flag boolean;
2364 Begin
2365 for i in 1..p_pc_subset_merge.count loop
2366 flag:=false;
2367 for j in 1..p_pc_subset.count loop
2368 if nvl(p_pc_subset(j).parent_level,'^')=nvl(p_pc_subset_merge(i).parent_level,'^')
2369 and nvl(p_pc_subset(j).child_level,'^')=nvl(p_pc_subset_merge(i).child_level,'^')
2370 and nvl(p_pc_subset(j).parent_pk,'^')=nvl(p_pc_subset_merge(i).parent_pk,'^')
2371 and nvl(p_pc_subset(j).child_fk,'^')=nvl(p_pc_subset_merge(i).child_fk,'^') then
2372 flag:=true;
2373 exit;
2374 end if;
2375 end loop;
2376 if flag=false then
2377 l_pc_subset(l_pc_subset.count+1):=p_pc_subset_merge(i);
2378 end if;
2379 end loop;
2380 for i in 1..l_pc_subset.count loop
2381 p_pc_subset(p_pc_subset.count+1):=l_pc_subset(i);
2382 end loop;
2383 Exception when others then
2384 log_n('Exception in merge_hier '||sqlerrm);
2385 raise;
2386 End;
2387
2388 /*given a parent child hier and the parent and child level, tries to create a subset hier. if child does not roll to the parent, return table
2389 is empty. go from child to parent*/
2390 function get_hier_subset(p_parent_child dim_parent_child_tb,p_parent_level varchar2,p_child_level varchar2) return dim_parent_child_tb is
2391 l_pc_subset dim_parent_child_tb;
2392 Begin
2393 check_parent(p_parent_child,p_child_level,p_parent_level,l_pc_subset);
2394 return l_pc_subset;
2395 Exception when others then
2396 log_n('Exception in get_hier_subset '||sqlerrm);
2400 procedure check_parent(
2397 raise;
2398 End;
2399
2401 p_parent_child dim_parent_child_tb,
2402 p_child_level varchar2,
2403 p_check_level varchar2,
2404 p_pc_subset out nocopy dim_parent_child_tb
2405 ) is
2406 --
2407 l_pc_subset dim_parent_child_tb;
2408 Begin
2409 for i in 1..p_parent_child.count loop
2410 if p_parent_child(i).child_level=p_child_level and p_parent_child(i).parent_level is not null then
2411 if p_parent_child(i).parent_level=p_check_level then
2412 p_pc_subset(p_pc_subset.count+1):=p_parent_child(i);
2413 exit;
2414 else
2415 check_parent(p_parent_child,p_parent_child(i).parent_level,p_check_level,l_pc_subset);
2416 if l_pc_subset.count>0 then
2417 for j in 1..l_pc_subset.count loop
2418 p_pc_subset(p_pc_subset.count+1):=l_pc_subset(j);
2419 end loop;
2420 p_pc_subset(p_pc_subset.count+1):=p_parent_child(i);
2421 exit;
2422 end if;
2423 end if;
2424 end if;
2425 end loop;
2426 Exception when others then
2427 log_n('Exception in check_parent '||sqlerrm);
2428 raise;
2429 End;
2430
2431 procedure set_dim_recursive(p_dimension in out nocopy dimension_r) is
2432 Begin
2433 bsc_aw_bsc_metadata.set_dim_recursive(p_dimension);
2434 if p_dimension.recursive is null or p_dimension.recursive='N' then --do one more check
2435 if p_dimension.level_groups(1).parent_child.count=1
2436 and p_dimension.level_groups(1).parent_child(1).parent_level=p_dimension.level_groups(1).parent_child(1).child_level then
2437 p_dimension.recursive:='Y';
2438 p_dimension.recursive_norm_hier:='Y';
2439 end if;
2440 end if;
2441 if g_debug then
2442 if p_dimension.recursive='Y' then
2443 log('Dimension '||p_dimension.dim_name||' is recursive');
2444 end if;
2445 end if;
2446 Exception when others then
2447 log_n('Exception in set_dim_recursive '||sqlerrm);
2448 raise;
2449 End;
2450
2451 /*if any level is view based, return Y*/
2452 function check_dim_view_based(p_dim varchar2) return varchar2 is
2453 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
2454 Begin
2455 bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);
2456 for i in 1..l_oo.count loop
2457 if bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level source',',')='view' then
2458 return 'Y';
2459 end if;
2460 end loop;
2461 return 'N';
2462 Exception when others then
2463 log_n('Exception in check_dim_view_based '||sqlerrm);
2464 raise;
2465 End;
2466
2467 procedure upgrade(p_new_version number,p_old_version number) is
2468 l_action bsc_aw_utility.boolean_table;
2469 Begin
2470 init_all;
2471 if g_debug then
2472 log('Dim upgrade New='||p_new_version||', Old='||p_old_version||bsc_aw_utility.get_time);
2473 end if;
2474 if p_new_version>p_old_version then
2475 if p_old_version<2 then
2476 /*upgrade to handle 5064802 */
2477 bsc_aw_load_dim.upgrade_load_sync_all_dim;
2478 end if;
2479 end if;
2480 Exception when others then
2481 log_n('Exception in upgrade '||sqlerrm);
2482 raise;
2483 End;
2484
2485 -------------------------
2486 procedure init_all is
2487 Begin
2488 g_debug:=bsc_aw_utility.g_debug;
2489 null;
2490 Exception when others then
2491 log_n('Exception in init_all '||sqlerrm);
2492 raise;
2493 End;
2494
2495 procedure log(p_message varchar2) is
2496 Begin
2497 bsc_aw_utility.log(p_message);
2498 Exception when others then
2499 null;
2500 End;
2501
2502 procedure log_n(p_message varchar2) is
2503 Begin
2504 log(' ');
2505 log(p_message);
2506 Exception when others then
2507 null;
2508 End;
2509
2510 END BSC_AW_ADAPTER_DIM;
[Home] [Help]