1 package body BSC_AW_MD_WRAPPER AS
2 /*$Header: BSCAWMWB.pls 120.20 2006/04/20 11:47 vsurendr noship $*/
3
4 procedure set_context(p_context varchar2) is
5 Begin
6 g_context:=p_context;
7 Exception when others then
8 log_n('Exception in set_context '||sqlerrm);
9 raise;
10 End;
11
12 procedure default_context_if_null is
13 Begin
14 if g_context is null then
15 g_context:='AW';
16 end if;
17 Exception when others then
18 log_n('Exception in default_context_if_null '||sqlerrm);
19 raise;
20 End;
21
22 procedure mark_kpi_recreate(
23 p_kpi varchar2
24 ) is
25 Begin
26 default_context_if_null;
27 update bsc_olap_object set operation_flag='recreate' where object=p_kpi and object_type='kpi' and context=g_context;
28 Exception when others then
29 log_n('Exception in mark_kpi_recreate '||sqlerrm);
30 raise;
31 End;
32
33 procedure drop_dim(p_dim_name varchar2) is
34 Begin
35 default_context_if_null;
36 if g_debug then
37 log('Drop metadata for Dim '||p_dim_name);
38 end if;
39 delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;
40 --with the concept of level groups, we do not remove snowflake implementation
41 --delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object in (select object from
42 --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
43 --and context=g_context)
44 --and context=g_context;
45 --delete bsc_olap_object where parent_object_type='dimension' and parent_object in (select object from
46 --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
47 --and context=g_context)
48 --and context=g_context;
49 delete bsc_olap_object where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;
50 Exception when others then
51 log_n('Exception in drop_dim '||sqlerrm);
52 raise;
53 End;
54
55 /*
56 create the following
57 dim
58 levels
59 zero levels
60 rec levels
61 level relations : only for real levels
62 register the program names : we need this since we will need to drop the programs when we drop the dim
63 we assume that the dimension entry is clean. this means we dont try a delete here
64 */
65 procedure create_dim(p_dimension bsc_aw_adapter_dim.dimension_r) is
66 --
67 l_position varchar2(400);
68 l_property varchar2(4000);
69 l_dim_type varchar2(100);
70 --
71 Begin
72 default_context_if_null;
73 if p_dimension.concat='Y' then
74 l_dim_type:='concat dimension';
75 else
76 l_dim_type:='dimension';--for TYPE, PROJECTION
77 end if;
78 --property1 will hold info on recursive dim or normal vs time, multi level
79 insert_olap_object(p_dimension.dim_name,'dimension',p_dimension.dim_name,l_dim_type,p_dimension.dim_name,'dimension',p_dimension.property);
80 --dim related objects
81 if p_dimension.relation_name is not null then
82 insert_olap_object(p_dimension.relation_name,'relation',p_dimension.relation_name,'relation',
83 p_dimension.dim_name,'dimension',null);
84 end if;
85 if p_dimension.level_name_dim is not null then
86 insert_olap_object(p_dimension.level_name_dim,'level name dim',p_dimension.level_name_dim,'dimension',
87 p_dimension.dim_name,'dimension',null);
88 end if;
89 if p_dimension.filter_variable is not null then
90 insert_olap_object(p_dimension.filter_variable,'filter cube',p_dimension.filter_variable,'variable',
91 p_dimension.dim_name,'dimension',null);
92 end if;
93 if p_dimension.limit_variable is not null then
94 insert_olap_object(p_dimension.limit_variable,'limit cube',p_dimension.limit_variable,'variable',
95 p_dimension.dim_name,'dimension',null);
96 end if;
97 if p_dimension.rec_level_position_cube is not null then
98 insert_olap_object(p_dimension.rec_level_position_cube,'rec level position cube',p_dimension.rec_level_position_cube,'variable',
99 p_dimension.dim_name,'dimension',null);
100 end if;
101 if p_dimension.base_value_cube is not null then
102 insert_olap_object(p_dimension.base_value_cube,'base value cube',p_dimension.base_value_cube,'variable',
103 p_dimension.dim_name,'dimension',null);
104 end if;
105 --enter the level group info
106 for i in 1..p_dimension.level_groups.count loop
107 insert_olap_object(p_dimension.level_groups(i).level_group_name,'level group',null,null,p_dimension.dim_name,'dimension',null);
108 end loop;
109 --populate the levels
110 for i in 1..p_dimension.level_groups.count loop
111 for j in 1..p_dimension.level_groups(i).levels.count loop
112 l_property:='level group='||p_dimension.level_groups(i).level_group_name||',position='||p_dimension.level_groups(i).levels(j).position||
113 ',pk='||p_dimension.level_groups(i).levels(j).pk.pk||',level source='||p_dimension.level_groups(i).levels(j).level_source;
114 insert_olap_object(p_dimension.level_groups(i).levels(j).level_name,'dimension level',p_dimension.level_groups(i).levels(j).level_name,'dimension',
115 p_dimension.dim_name,'dimension',l_property);
116 end loop;
117 end loop;
118 --
119 --snow flake. to support kpi when they reference independent levels. look at bsc_aw_adapter_kpi.identify_standalone_levels
120 --since the same level can come from diff dim, we need to merge the entries in
121 --if this is the metadata of the corrected dim, do not touch the snowflake implementation. corrected dim cannot have new dim levels
122 if p_dimension.dim_type <> 'std' and p_dimension.corrected='N' then
123 for i in 1..p_dimension.level_groups.count loop
124 for j in 1..p_dimension.level_groups(i).levels.count loop
125 l_property:='dimension type=normal,zero code,relation name='||p_dimension.level_groups(i).levels(j).relation_name||',level name dim='||
126 p_dimension.level_groups(i).levels(j).level_name_dim||',pk='||p_dimension.level_groups(i).levels(j).pk.pk||',';
127 merge_olap_object(p_dimension.level_groups(i).levels(j).level_name,'dimension',p_dimension.level_groups(i).levels(j).level_name,'dimension',
128 p_dimension.level_groups(i).levels(j).level_name,'dimension',l_property);
129 --also insert row for relation and level name dim
130 merge_olap_object(p_dimension.level_groups(i).levels(j).relation_name,'relation',p_dimension.level_groups(i).levels(j).relation_name,'relation',
131 p_dimension.level_groups(i).levels(j).level_name,'dimension',l_property);
132 merge_olap_object(p_dimension.level_groups(i).levels(j).level_name_dim,'level name dim',p_dimension.level_groups(i).levels(j).level_name_dim,
133 'dimension',p_dimension.level_groups(i).levels(j).level_name,'dimension',l_property);
134 --related objects for each level
135 merge_olap_object(p_dimension.level_groups(i).levels(j).filter_variable,'filter cube',p_dimension.level_groups(i).levels(j).filter_variable,
136 'variable',p_dimension.level_groups(i).levels(j).level_name,'dimension',l_property);
137 merge_olap_object(p_dimension.level_groups(i).levels(j).limit_variable,'limit cube',p_dimension.level_groups(i).levels(j).limit_variable,
138 'variable',p_dimension.level_groups(i).levels(j).level_name,'dimension',l_property);
139 end loop;
140 end loop;
141 end if;
142 --
143 if p_dimension.limit_variable is not null then
144 insert_olap_object_relation(p_dimension.dim_name,'dimension',p_dimension.limit_variable,
145 'dim limit cube','dim limit cube',p_dimension.dim_name,'dimension',null);
146 end if;
147 --also insert in olap relation the level and its corresponding zero code level
148 for i in 1..p_dimension.level_groups.count loop
149 for j in 1..p_dimension.level_groups(i).zero_levels.count loop
150 l_property:='level group='||p_dimension.level_groups(i).level_group_name;
151 insert_olap_object_relation(p_dimension.level_groups(i).zero_levels(j).child_level_name,'dimension level',
152 p_dimension.level_groups(i).zero_levels(j).level_name,'zero code level','zero code level',p_dimension.dim_name,'dimension',l_property);
153 --insert this zero code also as a relation for the dim of each level (snow flake levels)
154 if p_dimension.dim_type <> 'std' and p_dimension.corrected='N' then
155 l_property:=null;--no level group since snow flake
156 merge_olap_object_relation(p_dimension.level_groups(i).zero_levels(j).child_level_name,'dimension level',
157 p_dimension.level_groups(i).zero_levels(j).level_name,'zero code level','zero code level',
158 p_dimension.level_groups(i).zero_levels(j).child_level_name,'dimension',l_property);
159 end if;
160 end loop;
161 end loop;
162 --rec levels
163 for i in 1..p_dimension.level_groups.count loop
164 for j in 1..p_dimension.level_groups(i).rec_levels.count loop
165 l_property:='level group='||p_dimension.level_groups(i).level_group_name;
166 insert_olap_object(p_dimension.level_groups(i).rec_levels(j).level_name,'recursive level',p_dimension.level_groups(i).rec_levels(j).level_name,
167 'dimension',p_dimension.dim_name,'dimension',l_property);
168 end loop;
169 --also insert into olap relation
170 for j in 1..p_dimension.level_groups(i).rec_levels.count loop
171 l_property:='level group='||p_dimension.level_groups(i).level_group_name;
172 insert_olap_object_relation(p_dimension.level_groups(i).rec_levels(j).child_level_name,'dimension level',
173 p_dimension.level_groups(i).rec_levels(j).level_name,
174 'recursive parent level','recursive parent level',p_dimension.dim_name,'dimension',l_property);
175 end loop;
176 end loop;
177 --level relations
178 --pk and fk info will be used in creating data source for base tables. if base table is at city level and the kpi is at country
179 --level, we need to join to the city and state view. we need to know how to join
180 for i in 1..p_dimension.level_groups.count loop
181 for j in 1..p_dimension.level_groups(i).parent_child.count loop
182 insert_olap_object_relation(p_dimension.level_groups(i).parent_child(j).child_level,'dimension level',
183 p_dimension.level_groups(i).parent_child(j).parent_level,
184 'dimension level','parent level',p_dimension.dim_name,'dimension','level group='||p_dimension.level_groups(i).level_group_name||
185 ',pk='||p_dimension.level_groups(i).parent_child(j).parent_pk||',fk='||p_dimension.level_groups(i).parent_child(j).child_fk);
186 end loop;
187 end loop;
188 --register the program names
189 if p_dimension.initial_load_program is not null then
190 insert_olap_object(p_dimension.initial_load_program,'dml program',p_dimension.initial_load_program,'dml program initial load',
191 p_dimension.dim_name,'dimension',null);
192 end if;
193 --inc refresh program
194 if p_dimension.inc_load_program is not null then
195 insert_olap_object(p_dimension.inc_load_program,'dml program',p_dimension.inc_load_program,'dml program inc load',
196 p_dimension.dim_name,'dimension',null);
197 end if;
198 --insert dep kpi info
199 for i in 1..p_dimension.kpi_for_dim.count loop
200 --insert_olap_object_relation(p_dimension.dim_name,'dimension',p_dimension.kpi_for_dim(i).kpi,'kpi','dimension kpi',p_dimension.dim_name,'dimension',
201 --null);
202 --when we delete a kpi, we must delete this entry. for perf reasons, we keep the object as kpi
203 insert_olap_object_relation(p_dimension.kpi_for_dim(i).kpi,'kpi',p_dimension.kpi_for_dim(i).kpi,'kpi','dimension kpi',p_dimension.dim_name,'dimension',
204 null);
205 end loop;
206 Exception when others then
207 log_n('Exception in create_dim '||sqlerrm);
208 raise;
209 End;
210
211 procedure drop_kpi(p_kpi varchar2) is
212 Begin
213 default_context_if_null;
214 if g_debug then
215 log('drop kpi metadata '||p_kpi||bsc_aw_utility.get_time);
216 end if;
217 if p_kpi is not null then
218 delete bsc_olap_object_relation where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
219 delete bsc_olap_object_relation where object=p_kpi and object_type='kpi' and relation_object=p_kpi and relation_object_type='kpi' and
220 relation_type='dimension kpi';
221 delete bsc_olap_object where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
222 end if;
223 Exception when others then
224 log_n('Exception in drop_kpi '||sqlerrm);
225 raise;
226 End;
227
228 procedure create_calendar(p_calendar bsc_aw_calendar.calendar_r) is
229 l_property varchar2(4000);
230 Begin
231 default_context_if_null;
232 insert_olap_object(p_calendar.dim_name,'dimension',p_calendar.dim_name,'concat dimension',
233 p_calendar.dim_name,'dimension',p_calendar.property);
234 --related objects of the dim
235 insert_olap_object(p_calendar.relation_name,'relation',p_calendar.relation_name,'relation',
236 p_calendar.dim_name,'dimension',null);
237 insert_olap_object(p_calendar.denorm_relation_name,'denorm relation',p_calendar.denorm_relation_name,'relation',
238 p_calendar.dim_name,'dimension',null);
239 insert_olap_object(p_calendar.end_period_relation_name,'end period relation',p_calendar.end_period_relation_name,'relation',
240 p_calendar.dim_name,'dimension',null);
241 --there is also the end_period.temp variable
242 insert_olap_object(p_calendar.end_period_relation_name||'.temp','end period temp variable',
243 p_calendar.end_period_relation_name||'.temp','variable',p_calendar.dim_name,'dimension',null);
244 --
245 insert_olap_object(p_calendar.levels_name,'level name dim',p_calendar.levels_name,'dimension',
246 p_calendar.dim_name,'dimension',null);
247 insert_olap_object(p_calendar.end_period_levels_name,'end period level name dim',p_calendar.end_period_levels_name,'dimension',
248 p_calendar.dim_name,'dimension',null);
249 --misc objects
250 for i in 1..p_calendar.misc_object.count loop
251 insert_olap_object(p_calendar.misc_object(i).object_name,p_calendar.misc_object(i).object_type,
252 p_calendar.misc_object(i).object_name,p_calendar.misc_object(i).object_type,p_calendar.dim_name,'dimension',null);
253 end loop;
254 --populate the levels
255 for i in 1..p_calendar.periodicity.count loop
256 insert_olap_object(p_calendar.periodicity(i).dim_name,'dimension level',p_calendar.periodicity(i).dim_name,'dimension',
257 p_calendar.dim_name,'dimension','periodicity='||p_calendar.periodicity(i).periodicity_id||',db_column_name='||
258 p_calendar.periodicity(i).db_column_name||',periodicity_type='||p_calendar.periodicity(i).periodicity_type);
259 --related objects for each periodicity
260 if p_calendar.periodicity(i).aw_time_dim_name is not null then
261 insert_olap_object(p_calendar.periodicity(i).aw_time_dim_name,'aw time dim level',p_calendar.periodicity(i).aw_time_dim_name,'dimension',
262 p_calendar.dim_name,'dimension','periodicity='||p_calendar.periodicity(i).periodicity_id);
263 insert_olap_object(p_calendar.periodicity(i).aw_bsc_aw_rel_name,'bsc aw time relation',
264 p_calendar.periodicity(i).aw_bsc_aw_rel_name,'relation',
265 p_calendar.dim_name,'dimension','periodicity='||p_calendar.periodicity(i).periodicity_id);
266 insert_olap_object(p_calendar.periodicity(i).aw_aw_bsc_rel_name,'aw bsc time relation',
267 p_calendar.periodicity(i).aw_aw_bsc_rel_name,'relation',
268 p_calendar.dim_name,'dimension','periodicity='||p_calendar.periodicity(i).periodicity_id);
269 end if;
270 end loop;
271 --register the program names
272 insert_olap_object(p_calendar.load_program,'dml program',p_calendar.load_program,'dml program initial load',
273 p_calendar.dim_name,'dimension',null);
274 --fill dep kpi info
275 for i in 1..p_calendar.kpi_for_dim.count loop
276 --insert_olap_object_relation(p_calendar.dim_name,'dimension',p_calendar.kpi_for_dim(i).kpi,'kpi','dimension kpi',p_calendar.dim_name,'dimension',
277 --null);
278 insert_olap_object_relation(p_calendar.kpi_for_dim(i).kpi,'kpi',p_calendar.kpi_for_dim(i).kpi,'kpi','dimension kpi',p_calendar.dim_name,'dimension',
279 null);
280 end loop;
281 --
282 --insert the parent child relations
283 for i in 1..p_calendar.parent_child.count loop
284 l_property:='parent periodicity='||p_calendar.parent_child(i).parent||',child periodicity='||p_calendar.parent_child(i).child||',';
285 insert_olap_object_relation(p_calendar.parent_child(i).child_dim_name,'dimension level',
286 p_calendar.parent_child(i).parent_dim_name,'dimension level','parent level',p_calendar.dim_name,'dimension',l_property);
287 end loop;
288 Exception when others then
289 log_n('Exception in create_calendar '||sqlerrm);
290 raise;
291 End;
292
293 /*
294 get bsc olap object table data given the parent object and parent type
295 access paths
296 1. par obj specified. par obj type specified. obj not specified. obj type not specified.
297 2. par obj specified. par obj type specified. obj type specified. obj not specified
298 3. obj specified. obj type specified
299 4. obj specified. obj type specified. parent obj type specified
300 5. obj specified. obj type specified. parent obj specified. parent obj type specified
301 6. obj null, obj type not null, parent obj null, par obj type not null
302 */
303 procedure get_bsc_olap_object(
304 p_object varchar2,
305 p_type varchar2,
306 p_parent_object varchar2,
307 p_parent_type varchar2,
308 p_bsc_olap_object out nocopy bsc_olap_object_tb
309 ) is
310 --
311 cursor c1 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
312 and context=g_context order by object_type;
313 cursor c2 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
314 and object_type=p_type and context=g_context order by object_type;
315 cursor c3 is select * from bsc_olap_object where object=p_object and object_type=p_type
316 and context=g_context order by object_type;
317 cursor c4 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object_type=p_parent_type
318 and context=g_context order by object_type;
319 cursor c5 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object=p_parent_object
320 and parent_object_type=p_parent_type and context=g_context order by object_type;
321 cursor c6 is select * from bsc_olap_object where object_type=p_type and parent_object_type=p_parent_type and context=g_context;
322 Begin
323 default_context_if_null;
324 if g_debug and bsc_aw_utility.g_debug_level='all' then
325 log(' get_bsc_olap_object object='||p_object||' type='||p_type||' parent_object='||p_parent_object||' po_type='||
326 p_parent_type);
327 end if;
328 if p_object is null and p_type is null and p_parent_object is not null and p_parent_type is not null then --given the parent, get the objects
329 open c1;
330 loop
331 fetch c1 bulk collect into p_bsc_olap_object;
332 exit when c1%notfound;
333 end loop;
334 close c1;
335 elsif p_object is null and p_type is not null and p_parent_object is not null and p_parent_type is not null then
336 open c2;
337 loop
338 fetch c2 bulk collect into p_bsc_olap_object;
339 exit when c2%notfound;
340 end loop;
341 close c2;
342 elsif p_object is not null and p_type is not null and p_parent_object is null and p_parent_type is null then
343 open c3;
344 loop
345 fetch c3 bulk collect into p_bsc_olap_object;
346 exit when c3%notfound;
347 end loop;
348 close c3;
349 elsif p_object is not null and p_type is not null and p_parent_object is null and p_parent_type is not null then
350 open c4;
351 loop
352 fetch c4 bulk collect into p_bsc_olap_object;
353 exit when c4%notfound;
354 end loop;
355 close c4;
356 elsif p_object is not null and p_type is not null and p_parent_object is not null and p_parent_type is not null then
357 open c5;
358 loop
359 fetch c5 bulk collect into p_bsc_olap_object;
360 exit when c5%notfound;
361 end loop;
362 close c5;
363 elsif p_object is null and p_type is not null and p_parent_object is null and p_parent_type is not null then
364 open c6;
365 loop
366 fetch c6 bulk collect into p_bsc_olap_object;
367 exit when c6%notfound;
368 end loop;
369 close c6;
370 end if;
371 Exception when others then
372 log_n('Exception in get_bsc_olap_object '||sqlerrm);
373 raise;
374 End;
375
376 /*
377 access paths:
378 obj specified. rel type not specified. par obj specified
379 obj specified. rel type specified. par obj specified
380 obj specified. rel type specified. par obj not specified
381 obj not specified. rel type not specified. par obj specified
382 obj not specified. rel type specified. par obj specified
383 obj specified. rel type not specified. par obj not specified
384 */
385 procedure get_bsc_olap_object_relation(
386 p_object varchar2,
387 p_object_type varchar2,
388 p_relation_type varchar2,
389 p_parent_object varchar2,
390 p_parent_object_type varchar2,
391 p_bsc_olap_object_relation out nocopy bsc_olap_object_relation_tb
392 ) is
393 --
394 cursor c1 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
395 and parent_object=p_parent_object and parent_object_type=p_parent_object_type
396 and context=g_context;
397 cursor c2 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
398 and parent_object=p_parent_object and parent_object_type=p_parent_object_type
399 and relation_type=p_relation_type and context=g_context;
400 cursor c3 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
401 and relation_type=p_relation_type and context=g_context;
402 cursor c4 is select * from bsc_olap_object_relation where parent_object=p_parent_object
403 and parent_object_type=p_parent_object_type
404 and context=g_context;
405 cursor c5 is select * from bsc_olap_object_relation where relation_type=p_relation_type
406 and parent_object=p_parent_object and parent_object_type=p_parent_object_type
407 and context=g_context;
408 cursor c6 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
409 and context=g_context;
410 --
411 Begin
412 default_context_if_null;
413 if g_debug and bsc_aw_utility.g_debug_level='all' then
414 log(' get_bsc_olap_object_relation object='||p_object||' type='||p_object_type||' rel_type='||
415 p_relation_type||' par_obj='||p_parent_object||' par_obj_type='||p_parent_object_type);
416 end if;
417 if p_object is not null and p_relation_type is null and p_parent_object is not null then
418 open c1;
419 loop
420 fetch c1 bulk collect into p_bsc_olap_object_relation;
421 exit when c1%notfound;
422 end loop;
423 close c1;
424 elsif p_object is not null and p_relation_type is not null and p_parent_object is not null then
425 open c2;
426 loop
427 fetch c2 bulk collect into p_bsc_olap_object_relation;
428 exit when c2%notfound;
429 end loop;
430 close c2;
431 elsif p_object is not null and p_relation_type is not null and p_parent_object is null then
432 open c3;
433 loop
434 fetch c3 bulk collect into p_bsc_olap_object_relation;
435 exit when c3%notfound;
436 end loop;
437 close c3;
438 elsif p_object is null and p_relation_type is null and p_parent_object is not null then
439 open c4;
440 loop
441 fetch c4 bulk collect into p_bsc_olap_object_relation;
442 exit when c4%notfound;
443 end loop;
444 close c4;
445 elsif p_object is null and p_relation_type is not null and p_parent_object is not null then
446 open c5;
447 loop
448 fetch c5 bulk collect into p_bsc_olap_object_relation;
449 exit when c5%notfound;
450 end loop;
451 close c5;
452 elsif p_object is not null and p_relation_type is null and p_parent_object is null then
453 open c6;
454 loop
455 fetch c6 bulk collect into p_bsc_olap_object_relation;
456 exit when c6%notfound;
457 end loop;
458 close c6;
459 end if;
460 Exception when others then
461 log_n('Exception in get_bsc_olap_object_relation '||sqlerrm);
462 raise;
463 End;
464
465 procedure create_kpi(p_kpi bsc_aw_adapter_kpi.kpi_r) is
466 Begin
467 default_context_if_null;
468 if g_debug then
469 log('create kpi metadata '||p_kpi.kpi||bsc_aw_utility.get_time);
470 end if;
471 --=======bsc olap objects====================
472 insert_olap_object(p_kpi.kpi,'kpi',null,null,p_kpi.kpi,'kpi','calendar='||p_kpi.calendar||',parent kpi='||p_kpi.parent_kpi);
473 for i in 1..p_kpi.dim_set.count loop
474 create_kpi(p_kpi.kpi,p_kpi.dim_set(i));
475 end loop;
476 --targets
477 for i in 1..p_kpi.target_dim_set.count loop
478 if p_kpi.target_dim_set(i).dim_set is not null then
479 create_kpi(p_kpi.kpi,p_kpi.target_dim_set(i));
480 end if;
481 end loop;
482 Exception when others then
483 log_n('Exception in create_kpi '||sqlerrm);
484 raise;
485 End;
486
487 procedure create_kpi(p_kpi varchar2,p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
488 l_property varchar2(4000);
489 l_comp_added dbms_sql.varchar2_table; --so we insert into oo distinct composite names
490 l_dimset_name_property varchar2(400);
491 l_bsc_olap_object_relation bsc_olap_object_relation_tb;
492 Begin
493 default_context_if_null;
494 --=======bsc olap objects====================
495 --we need to have all the objects entered in bsc_olap_object that needs to be dropped
496 --dimension set
497 --we just need to knoow if there are targets or not, if there are targets we load, aggregate, load targets and again aggregate
498 --base dim set will be useful for target dimsets, for regular dimsets, this is will be null.
499 --in BSCAWLKB.pls, we have to see the base dim set for a target dimset. only then can we copy data from target cubes to actual cubes
500 l_dimset_name_property:='dim set name='||p_dim_set.dim_set_name||',';
501 l_property:='dim set='||p_dim_set.dim_set||',dim set type='||p_dim_set.dim_set_type||',base dim set='||p_dim_set.base_dim_set||',';
502 if p_dim_set.targets_higher_levels='Y' then
503 l_property:=l_property||'targets,';
504 end if;
505 l_property:=l_property||'measurename dim='||p_dim_set.measurename_dim||',partition dim='||p_dim_set.partition_dim||',cube design='||
506 p_dim_set.cube_design||',number partitions='||p_dim_set.number_partitions||',partition type='||p_dim_set.partition_type||
507 ',compressed='||p_dim_set.compressed||',pre calculated='||p_dim_set.pre_calculated;
508 insert_olap_object(p_dim_set.dim_set_name,'kpi dimension set',null,null,p_kpi,'kpi',l_property);
509 /*
510 insert the partition, composite info
511 */
512 for i in 1..p_dim_set.partition_template.count loop
513 l_property:=l_dimset_name_property;
514 l_property:=l_property||',template type='||p_dim_set.partition_template(i).template_type||',template use='||p_dim_set.partition_template(i).template_use||
515 ',template dim='||p_dim_set.partition_template(i).template_dim;
516 insert_olap_object(p_dim_set.partition_template(i).template_name,'partition template',
517 p_dim_set.partition_template(i).template_name,'partition template',p_kpi,'kpi',l_property);
518 end loop;
519 --register the measurename_dim...it also must be dropped
520 insert_olap_object(p_dim_set.measurename_dim,'measurename dim',p_dim_set.measurename_dim,'dimension',p_kpi,'kpi',l_property);
521 --composite name
522 --in 10g, this composite will not exist. each cube has its own
523 --we do not create an entry for dimension set composite. in 9i also, measure composite will create entry for the composite name
524 for i in 1..p_dim_set.composite.count loop
525 l_property:=l_dimset_name_property;
526 l_property:=l_property||',composite type='||p_dim_set.composite(i).composite_type;
527 insert_olap_object(p_dim_set.composite(i).composite_name,'measure composite',
528 p_dim_set.composite(i).composite_name,'composite',p_kpi,'kpi',l_property);
529 end loop;
530 --cubes
531 for i in 1..p_dim_set.cube_set.count loop
532 l_property:=l_dimset_name_property;
533 l_property:=l_property||',cube set type='||p_dim_set.cube_set(i).cube_set_type||',measurename dim='||p_dim_set.cube_set(i).measurename_dim;
534 insert_olap_object(p_dim_set.cube_set(i).cube.cube_name,'data cube',p_dim_set.cube_set(i).cube.cube_name,'cube',
535 p_kpi,'kpi',l_property||',cube type='||p_dim_set.cube_set(i).cube.cube_type||',cube data type='||p_dim_set.cube_set(i).cube.cube_datatype);
536 if p_dim_set.cube_set(i).countvar_cube.cube_name is not null then
537 insert_olap_object(p_dim_set.cube_set(i).countvar_cube.cube_name,'countvar cube',p_dim_set.cube_set(i).countvar_cube.cube_name,'cube',
538 p_kpi,'kpi',l_property||',cube type='||p_dim_set.cube_set(i).countvar_cube.cube_type||',cube data type='||
539 p_dim_set.cube_set(i).countvar_cube.cube_datatype);
540 end if;
541 if p_dim_set.cube_set(i).display_cube.cube_name is not null then
542 insert_olap_object(p_dim_set.cube_set(i).display_cube.cube_name,'display cube',p_dim_set.cube_set(i).display_cube.cube_name,'cube',
543 p_kpi,'kpi',l_property||',cube type='||p_dim_set.cube_set(i).display_cube.cube_type||',cube data type='||
544 p_dim_set.cube_set(i).display_cube.cube_datatype);
545 end if;
546 /*
547 right now, we do not create the fcst cube. so we do not register it in olap pbjects. if we register it there, in 10g, lock acquire
548 fails with Exception in get_lock ORA-34492: Analytic workspace object BUGLOG_2_4014_FCST does not exist.
549 */
550 end loop;
551 --formulas. we need it in the case where we use datacube design
552 for i in 1..p_dim_set.measure.count loop
553 if p_dim_set.measure(i).aw_formula.formula_name is not null then
554 l_property:=l_dimset_name_property;
555 insert_olap_object(p_dim_set.measure(i).aw_formula.formula_name,'measure formula',p_dim_set.measure(i).aw_formula.formula_name,'formula',
556 p_kpi,'kpi',l_property||',measure='||p_dim_set.measure(i).measure);
557 end if;
558 end loop;
559 --agg maps
560 insert_olap_object(p_dim_set.aggmap_operator.measure_dim,'agg map measure dim',p_dim_set.aggmap_operator.measure_dim,
561 'dimension',p_kpi,'kpi',l_dimset_name_property||'agg map');
562 insert_olap_object(p_dim_set.aggmap_operator.opvar,'agg map opvar',p_dim_set.aggmap_operator.opvar,
563 'variable',p_kpi,'kpi',l_dimset_name_property||'agg map');
564 insert_olap_object(p_dim_set.aggmap_operator.argvar,'agg map argvar',p_dim_set.aggmap_operator.argvar,
565 'variable',p_kpi,'kpi',l_dimset_name_property||'agg map');
566 --
567 if p_dim_set.agg_map.created='Y' then
568 insert_olap_object(p_dim_set.agg_map.agg_map,'agg map',p_dim_set.agg_map.agg_map,'agg map',p_kpi,'kpi',l_dimset_name_property||'agg map');
569 end if;
570 if p_dim_set.agg_map_notime.created='Y' then
571 insert_olap_object(p_dim_set.agg_map_notime.agg_map,'agg map',p_dim_set.agg_map_notime.agg_map,'agg map',p_kpi,'kpi',
572 l_dimset_name_property||'agg map notime');
573 end if;
574 --limit cubes of the dim
575 for i in 1..p_dim_set.dim.count loop
576 if p_dim_set.dim(i).limit_cube_composite is not null then
577 insert_olap_object(p_dim_set.dim(i).limit_cube_composite,'limit cube composite',p_dim_set.dim(i).limit_cube_composite,
578 'composite',p_kpi,'kpi',l_dimset_name_property);
579 end if;
580 if p_dim_set.dim(i).limit_cube is not null then
581 insert_olap_object(p_dim_set.dim(i).limit_cube,'dim limit cube',p_dim_set.dim(i).limit_cube,
582 'variable',p_kpi,'kpi',l_dimset_name_property);
583 end if;
584 if p_dim_set.dim(i).aggregate_marker is not null then
585 insert_olap_object(p_dim_set.dim(i).aggregate_marker,'dim aggregate marker',p_dim_set.dim(i).aggregate_marker,
586 'variable',p_kpi,'kpi',l_dimset_name_property);
587 end if;
588 if p_dim_set.dim(i).reset_cube is not null then
589 insert_olap_object(p_dim_set.dim(i).reset_cube,'dim reset cube',p_dim_set.dim(i).reset_cube,
590 'variable',p_kpi,'kpi',l_dimset_name_property);
591 end if;
592 end loop;
593 for i in 1..p_dim_set.std_dim.count loop
594 if p_dim_set.std_dim(i).limit_cube_composite is not null then
595 insert_olap_object(p_dim_set.std_dim(i).limit_cube_composite,'limit cube composite',p_dim_set.std_dim(i).limit_cube_composite,
596 'composite',p_kpi,'kpi',l_dimset_name_property);
597 end if;
598 if p_dim_set.std_dim(i).limit_cube is not null then
599 insert_olap_object(p_dim_set.std_dim(i).limit_cube,'dim limit cube',p_dim_set.std_dim(i).limit_cube,
600 'variable',p_kpi,'kpi',l_dimset_name_property);
601 end if;
602 --std dim and calendar do not have reset cubes
603 end loop;
604 --calendar limit cube
605 if p_dim_set.calendar.limit_cube_composite is not null then
606 insert_olap_object(p_dim_set.calendar.limit_cube_composite,'limit cube composite',p_dim_set.calendar.limit_cube_composite,
607 'composite',p_kpi,'kpi',l_dimset_name_property);
608 end if;
609 if p_dim_set.calendar.limit_cube is not null then
610 insert_olap_object(p_dim_set.calendar.limit_cube,'dim limit cube',p_dim_set.calendar.limit_cube,
611 'variable',p_kpi,'kpi',l_dimset_name_property);
612 end if;
613 if p_dim_set.calendar.aggregate_marker is not null then
614 insert_olap_object(p_dim_set.calendar.aggregate_marker,'dim aggregate marker',p_dim_set.calendar.aggregate_marker,
615 'variable',p_kpi,'kpi',l_dimset_name_property);
616 end if;
617 --dim agg maps
618 for i in 1..p_dim_set.dim.count loop
619 if p_dim_set.dim(i).agg_map.agg_map is not null then
620 insert_olap_object(p_dim_set.dim(i).agg_map.agg_map,'dim agg map',p_dim_set.dim(i).agg_map.agg_map,
621 'agg map',p_kpi,'kpi',l_dimset_name_property);
622 end if;
623 end loop;
624 /*calendar aggmap */
625 if p_dim_set.calendar.agg_map.agg_map is not null then
626 insert_olap_object(p_dim_set.calendar.agg_map.agg_map,'calendar agg map',p_dim_set.calendar.agg_map.agg_map,'agg map',p_kpi,'kpi',
627 l_dimset_name_property);
628 end if;
629 --program
630 insert_olap_object(p_dim_set.initial_load_program.program_name,'dml program',p_dim_set.initial_load_program.program_name,'dml program initial load',
631 p_kpi,'kpi',l_dimset_name_property||'DS='||p_dim_set.initial_load_program.ds_base_tables);
632 insert_olap_object(p_dim_set.inc_load_program.program_name,'dml program',p_dim_set.inc_load_program.program_name,'dml program inc load',p_kpi,'kpi',
633 l_dimset_name_property||'DS='||p_dim_set.inc_load_program.ds_base_tables);
634 if p_dim_set.initial_load_program_parallel.program_name is not null then
635 insert_olap_object(p_dim_set.initial_load_program_parallel.program_name,'dml program',p_dim_set.initial_load_program_parallel.program_name,
636 'dml program initial load parallel',p_kpi,'kpi',l_dimset_name_property||'DS='||p_dim_set.initial_load_program_parallel.ds_base_tables);
637 end if;
638 if p_dim_set.inc_load_program_parallel.program_name is not null then
639 insert_olap_object(p_dim_set.inc_load_program_parallel.program_name,'dml program',p_dim_set.inc_load_program_parallel.program_name,
640 'dml program inc load parallel',p_kpi,'kpi',l_dimset_name_property||'DS='||p_dim_set.inc_load_program_parallel.ds_base_tables);
641 end if;
642 if p_dim_set.LB_resync_program is not null then
643 insert_olap_object(p_dim_set.LB_resync_program,'LB resync program',p_dim_set.LB_resync_program,
644 'program',p_kpi,'kpi',l_dimset_name_property);
645 end if;
646 if p_dim_set.aggregate_marker_program is not null then
647 insert_olap_object(p_dim_set.aggregate_marker_program,'aggregate marker program',p_dim_set.aggregate_marker_program,'program',p_kpi,'kpi',
648 l_dimset_name_property);
649 end if;
650 --the relational types and views
651 for i in 1..p_dim_set.s_view.count loop
652 if p_dim_set.s_view(i).type_name is not null then
653 insert_olap_object(p_dim_set.s_view(i).type_name,'relational type',null,null,p_kpi,'kpi',l_dimset_name_property);
654 end if;
655 if p_dim_set.s_view(i).s_view is not null then
656 insert_olap_object(p_dim_set.s_view(i).s_view,'relational view',null,null,p_kpi,'kpi',l_dimset_name_property);
657 end if;
658 end loop;
659 --zero code mviews
660 for i in 1..p_dim_set.z_s_view.count loop
661 if p_dim_set.z_s_view(i).type_name is not null then
662 insert_olap_object(p_dim_set.z_s_view(i).type_name,'relational type',null,null,p_kpi,'kpi',l_dimset_name_property||'zero code');
663 end if;
664 if p_dim_set.z_s_view(i).s_view is not null then
665 insert_olap_object(p_dim_set.z_s_view(i).s_view,'relational view',null,null,p_kpi,'kpi',l_dimset_name_property||'zero code');
666 end if;
667 end loop;
668 --
669 --=======bsc olap object relations===========
670 /*
671 we enter the following
672 given a dim set, the dimensions. for each dim, the levels. for each dim set, the cubes and the programs.
673 we also say if the level has zero code
674 to make objects unique, we will do obj+dimset.
675 otherwise there will be confusion. example
676 BSC_CCDIM_100 dimension AW dim set dim level HRI_PER dimension level lowest level,zero code=,
677 BSC_CCDIM_100 dimension AW dim set dim level HRI_PER dimension level lowest level,zero code=,
678 these are the dim entries for 2 dim sets. one dim set may have geog with 2 levels. another with 3 levels. if we know only the dim name,
679 we cannot know how many levels are in this dim set
680 */
681 for i in 1..p_dim_set.partition_template.count loop
682 l_property:='template type='||p_dim_set.partition_template(i).template_type||',template use='||p_dim_set.partition_template(i).template_use||
683 ',template dim='||p_dim_set.partition_template(i).template_dim;
684 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.partition_template(i).template_name,'partition name',
685 'dim set partition template',p_kpi,'kpi',l_property);
686 end loop;
687 --partition template partition info
688 for i in 1..p_dim_set.partition_template.count loop
689 for j in 1..p_dim_set.partition_template(i).template_partitions.count loop
690 l_property:='partition dim value='||p_dim_set.partition_template(i).template_partitions(j).partition_dim_value;
691 insert_olap_object_relation(p_dim_set.partition_template(i).template_name,'partition template',
692 p_dim_set.partition_template(i).template_partitions(j).partition_name,'partition name',
693 'partition template partition',p_kpi,'kpi',l_property);
694 end loop;
695 end loop;
696 --composites
697 for i in 1..p_dim_set.composite.count loop
698 l_property:='composite type='||p_dim_set.composite(i).composite_type;
699 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.composite(i).composite_name,'measure composite',
700 'dim set measure composite',p_kpi,'kpi',l_property);
701 end loop;
702 --cube sets
703 for i in 1..p_dim_set.cube_set.count loop
704 l_property:='cube set type='||p_dim_set.cube_set(i).cube_set_type||',measurename dim='||p_dim_set.cube_set(i).measurename_dim;
705 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.cube_set(i).cube_set_name,'cube set',
706 'dim set cube set',p_kpi,'kpi',l_property);
707 end loop;
708 --cubes in the cube set
709 for i in 1..p_dim_set.cube_set.count loop
710 l_property:='cube type='||p_dim_set.cube_set(i).cube.cube_type||',cube datatype='||p_dim_set.cube_set(i).cube.cube_datatype;
711 insert_olap_object_relation(p_dim_set.cube_set(i).cube_set_name,'cube set',p_dim_set.cube_set(i).cube.cube_name,'measure cube',
712 'cube set measure cube',p_kpi,'kpi',l_property);
713 if p_dim_set.cube_set(i).countvar_cube.cube_name is not null then
714 l_property:='cube type='||p_dim_set.cube_set(i).countvar_cube.cube_type||',cube datatype='||
715 p_dim_set.cube_set(i).countvar_cube.cube_datatype;
716 insert_olap_object_relation(p_dim_set.cube_set(i).cube_set_name,'cube set',p_dim_set.cube_set(i).countvar_cube.cube_name,'countvar cube',
717 'cube set countvar cube',p_kpi,'kpi',l_property);
718 end if;
719 if p_dim_set.cube_set(i).display_cube.cube_name is not null then
720 l_property:='cube type='||p_dim_set.cube_set(i).display_cube.cube_type||',cube datatype='||
721 p_dim_set.cube_set(i).display_cube.cube_datatype;
722 insert_olap_object_relation(p_dim_set.cube_set(i).cube_set_name,'cube set',p_dim_set.cube_set(i).display_cube.cube_name,'display cube',
723 'cube set display cube',p_kpi,'kpi',l_property);
724 end if;
725 /*
726 right now, we do not create the fcst cube. so we do not register it in olap pbjects. if we register it there, in 10g, lock acquire
727 fails with Exception in get_lock ORA-34492: Analytic workspace object BUGLOG_2_4014_FCST does not exist.
728 */
729 end loop;
730 --cube info
731 for i in 1..p_dim_set.cube_set.count loop
732 for j in 1..p_dim_set.cube_set(i).cube.cube_axis.count loop
733 l_property:='axis type='||p_dim_set.cube_set(i).cube.cube_axis(j).axis_type;
734 insert_olap_object_relation(p_dim_set.cube_set(i).cube.cube_name,'cube',
735 p_dim_set.cube_set(i).cube.cube_axis(j).axis_name,'axis name','cube axis',p_kpi,'kpi',l_property);
736 --
737 if p_dim_set.cube_set(i).countvar_cube.cube_name is not null then
738 l_property:='axis type='||p_dim_set.cube_set(i).countvar_cube.cube_axis(j).axis_type;
739 insert_olap_object_relation(p_dim_set.cube_set(i).countvar_cube.cube_name,'cube',
740 p_dim_set.cube_set(i).countvar_cube.cube_axis(j).axis_name,'axis name','cube axis',p_kpi,'kpi',l_property);
741 end if;
742 end loop;
743 if p_dim_set.cube_set(i).display_cube.cube_name is not null then
744 for j in 1..p_dim_set.cube_set(i).display_cube.cube_axis.count loop
745 l_property:='axis type='||p_dim_set.cube_set(i).display_cube.cube_axis(j).axis_type;
746 insert_olap_object_relation(p_dim_set.cube_set(i).display_cube.cube_name,'cube',
747 p_dim_set.cube_set(i).display_cube.cube_axis(j).axis_name,'axis name','cube axis',p_kpi,'kpi',l_property);
748 end loop;
749 end if;
750 end loop;
751 --for each dim set, enter the dim
752 for i in 1..p_dim_set.dim.count loop
753 l_property:='limit cube='||p_dim_set.dim(i).limit_cube||',limit cube composite='||p_dim_set.dim(i).limit_cube_composite||
754 ',aggregate marker='||p_dim_set.dim(i).aggregate_marker||',agg map='||p_dim_set.dim(i).agg_map.agg_map||',agg level='||
755 p_dim_set.dim(i).agg_level||',';
756 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.dim(i).dim_name,'dimension',
757 'dim set dim',p_kpi,'kpi',l_property);
758 --we need the levels to know which levels to aggregate to
759 for j in 1..p_dim_set.dim(i).levels.count loop
760 l_property:=null;
761 if j=1 then --lowest level
762 l_property:='lowest level,';
763 end if;
764 l_property:=l_property||'zero code='||p_dim_set.dim(i).levels(j).zero_code||',position='||p_dim_set.dim(i).levels(j).position||','||
765 'aggregated='||p_dim_set.dim(i).levels(j).aggregated||',zero_aggregated='||p_dim_set.dim(i).levels(j).zero_aggregated||',';
766 insert_olap_object_relation(p_dim_set.dim(i).dim_name||'+'||p_dim_set.dim_set_name,'dimension',
767 p_dim_set.dim(i).levels(j).level_name,'dimension level','dim set dim level',p_kpi,'kpi',l_property);
768 end loop;
769 --we need to register the limit cube with object as the dim. this is for bsc_aw_load_dim.set_kpi_limit_variables
770 --we also register the aggregate markers
771 l_property:='dim set type='||p_dim_set.dim_set_type||',';
772 insert_olap_object_relation(p_dim_set.dim(i).dim_name,'dimension',p_dim_set.dim(i).limit_cube,'kpi limit cube','kpi limit cube',
773 p_kpi,'kpi',l_property);
774 if p_dim_set.dim(i).aggregate_marker is not null then
775 insert_olap_object_relation(p_dim_set.dim(i).dim_name,'dimension',p_dim_set.dim(i).aggregate_marker,'kpi aggregate marker','kpi aggregate marker',
776 p_kpi,'kpi',l_property);
777 end if;
778 if p_dim_set.dim(i).reset_cube is not null then
779 insert_olap_object_relation(p_dim_set.dim(i).dim_name,'dimension',p_dim_set.dim(i).reset_cube,'kpi reset cube','kpi reset cube',
780 p_kpi,'kpi',l_property);
781 end if;
782 --need to register the kpi as belonging to the dim
783 merge_olap_object_relation(p_kpi,'kpi',p_kpi,'kpi','dimension kpi',p_dim_set.dim(i).dim_name,'dimension',null);
784 end loop;
785 --target dim
786 --we dont need limit cubes or agg maps here. targets will have the same dim as actuals, levels though are diff. but when we
787 --load targets, the limit cubes will correctly catch the level and when we aggregate, only those levels are aggregated up
788 --say target is at state. when we aggregate after loading targets, we aggregate from state upwards
789 --std dimensions
790 --we dont need agg maps for std dim since they are single level dim
791 for i in 1..p_dim_set.std_dim.count loop
792 l_property:='limit cube='||p_dim_set.std_dim(i).limit_cube||',limit cube composite='||p_dim_set.std_dim(i).limit_cube_composite||
793 ',agg level='||p_dim_set.std_dim(i).agg_level||',';
794 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.std_dim(i).dim_name,'dimension',
795 'dim set std dim',p_kpi,'kpi',l_property);
796 --we need the levels to know which levels to aggregate to
797 for j in 1..p_dim_set.std_dim(i).levels.count loop
798 l_property:=null;
799 if j=1 then --lowest level
800 l_property:='lowest level,';
801 end if;
802 --initially we had "dim set std dim level". this is not reqd. type and projection are std dim. levels within them are
803 --just levels of a dim
804 insert_olap_object_relation(p_dim_set.std_dim(i).dim_name||'+'||p_dim_set.dim_set_name,'dimension',
805 p_dim_set.std_dim(i).levels(j).level_name,'dimension level','dim set dim level',p_kpi,'kpi',l_property);
806 end loop;
807 end loop;
808 --for each dim set, enter calendar info
809 --we need calendar info since we need to aggregate on time
810 l_property:='calendar='||p_dim_set.calendar.calendar||',limit cube='||p_dim_set.calendar.limit_cube||','||
811 'limit cube composite='||p_dim_set.calendar.limit_cube_composite||',aggregate marker='||p_dim_set.calendar.aggregate_marker||',agg map='||
812 p_dim_set.calendar.agg_map.agg_map;
813 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.calendar.aw_dim,'calendar',
814 'dim set calendar', p_kpi,'kpi',l_property);
815 --note>>> we are not adding an entry for "kpi aggregate marker" for calendar because hier changes in calendar always result in full
816 --refresh of the calendar. so we dont track hier changes in calendar
817 for i in 1..p_dim_set.calendar.periodicity.count loop
818 l_property:='periodicity='||p_dim_set.calendar.periodicity(i).periodicity||',';
819 --if i=1 then --lowest periodicity
820 if p_dim_set.calendar.periodicity(i).lowest_level='Y' then
821 l_property:=l_property||'lowest level,';
822 end if;
823 if p_dim_set.calendar.periodicity(i).missing_level='Y' then
824 l_property:=l_property||'missing level,';
825 end if;
826 l_property:=l_property||'aggregated='||p_dim_set.calendar.periodicity(i).aggregated||',periodicity_type='||
827 p_dim_set.calendar.periodicity(i).periodicity_type;
828 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.calendar.periodicity(i).aw_dim,
829 'periodicity','dim set periodicity',p_kpi,'kpi',l_property);
830 end loop;
831 --log the agg map info
832 l_property:=null;
833 if p_dim_set.agg_map.agg_map is not null then
834 if p_dim_set.agg_map.created='Y' then
835 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.agg_map.agg_map,'agg map','dim set agg map',
836 p_kpi,'kpi',l_property);
837 end if;
838 l_property:=null;
839 if p_dim_set.agg_map_notime.created='Y' then
840 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.agg_map_notime.agg_map,'agg map',
841 'dim set agg map notime',p_kpi,'kpi',l_property);
842 end if;
843 --aggmap_operator properties
844 l_property:=null;
845 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.aggmap_operator.measure_dim,
846 'agg map measure dim','agg map measure dim',p_kpi,'kpi',l_property);
847 l_property:=null;
848 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.aggmap_operator.opvar,
849 'agg map opvar','agg map opvar',p_kpi,'kpi',l_property);
850 l_property:=null;
851 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.aggmap_operator.argvar,
852 'agg map argvar','agg map argvar',p_kpi,'kpi',l_property);
853 end if;
854 --dim set cubes
855 for i in 1..p_dim_set.measure.count loop
856 l_property:='agg formula='||p_dim_set.measure(i).agg_formula.agg_formula||',std agg='||p_dim_set.measure(i).agg_formula.std_aggregation||','||
857 'avg agg='||p_dim_set.measure(i).agg_formula.avg_aggregation||',sql aggregated='||p_dim_set.measure(i).sql_aggregated||
858 ',forecast='||p_dim_set.measure(i).forecast||',forecast method='||p_dim_set.measure(i).forecast_method||',';
859 l_property:=l_property||'cube='||p_dim_set.measure(i).cube||',fcst cube='||
860 p_dim_set.measure(i).fcst_cube||',countvar cube='||p_dim_set.measure(i).countvar_cube||','||
861 'display cube='||p_dim_set.measure(i).display_cube||','||
862 'measure type='||p_dim_set.measure(i).measure_type||',';
863 if p_dim_set.measure(i).aw_formula.formula_name is not null then
864 l_property:=l_property||'aw formula='||p_dim_set.measure(i).aw_formula.formula_name||',';
865 end if;
866 --for non std aggregation, insert the agg formula cubes
867 if p_dim_set.measure(i).agg_formula.cubes.count>0 then
868 l_property:=l_property||'agg formula cubes=';
869 for j in 1..p_dim_set.measure(i).agg_formula.cubes.count loop
870 l_property:=l_property||p_dim_set.measure(i).agg_formula.cubes(j)||'+';
871 end loop;
872 l_property:=l_property||',';
873 end if;
874 if p_dim_set.measure(i).agg_formula.measures.count>0 then
875 l_property:=l_property||'agg formula measures=';
876 for j in 1..p_dim_set.measure(i).agg_formula.measures.count loop
877 l_property:=l_property||p_dim_set.measure(i).agg_formula.measures(j)||'+';
878 end loop;
879 l_property:=l_property||',';
880 end if;
881 l_property:=l_property||bsc_aw_utility.get_property_string(p_dim_set.measure(i).property);
882 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.measure(i).measure,'measure',
883 'dim set measure',p_kpi,'kpi',l_property);
884 end loop;
885 --programs
886 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.initial_load_program.program_name,'dml program',
887 'dml program initial load',p_kpi,'kpi',null);
888 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.inc_load_program.program_name,'dml program',
889 'dml program inc load',p_kpi,'kpi',null);
890 --
891 if p_dim_set.initial_load_program_parallel.program_name is not null then
892 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.initial_load_program_parallel.program_name,'dml program',
893 'dml program initial load parallel',p_kpi,'kpi',null);
894 end if;
895 if p_dim_set.inc_load_program_parallel.program_name is not null then
896 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.inc_load_program_parallel.program_name,'dml program',
897 'dml program inc load parallel',p_kpi,'kpi',null);
898 end if;
899 if p_dim_set.LB_resync_program is not null then
900 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.LB_resync_program,'LB resync program',
901 'LB resync program',p_kpi,'kpi',null);
902 end if;
903 --
904 if p_dim_set.aggregate_marker_program is not null then
905 insert_olap_object_relation(p_dim_set.dim_set_name,'kpi dimension set',p_dim_set.aggregate_marker_program,'aggregate marker program',
906 'aggregate marker program',p_kpi,'kpi',null);
907 end if;
908 -------------------
909 --given base tables, find the dimension sets they impact
910 --this is imp because, if we need to load a base table, we need tofind the dim set they impact and then load them
911 --data sources can share base tables. so first get the distinct list...this is not imp. if base table -> dim set repeats,
912 --its ok
913 bsc_aw_utility.init_is_new_value(1);
914 for i in 1..p_dim_set.inc_data_source.count loop
915 for j in 1..p_dim_set.inc_data_source(i).base_tables.count loop
916 if bsc_aw_utility.is_new_value(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,1) then
917 --note>>>load kpi module (api load_kpi_dimset_base_table) has dependency on how property looks like.
918 --if l_property needs to be changed, make the change in load kpi module also
919 l_property:='base table periodicity='||p_dim_set.inc_data_source(i).base_tables(j).periodicity.periodicity||
920 ',current change vector=0,measures=';
921 for k in 1..p_dim_set.inc_data_source(i).measure.count loop
922 l_property:=l_property||p_dim_set.inc_data_source(i).measure(k).measure||'+';
923 end loop;
924 insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',
925 p_dim_set.dim_set_name,'dimension set','base table dim set',p_kpi,'kpi',l_property);
926 --for each base table, create an entry that will hold the current load set id (in change_vector column)
927 l_bsc_olap_object_relation.delete;
928 get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',
929 p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);
930 --if the entry is new, create one, we enter 0 as the current change vector value
931 --these base table entries need to be periodically validated and cleaned up. a base table may no longer be used
932 if l_bsc_olap_object_relation.count=0 then
933 insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',
934 '0','change vector','base table change vector',p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',null);
935 end if;
936 end if;
937 end loop;
938 end loop;
939 Exception when others then
940 log_n('Exception in create_kpi '||sqlerrm);
941 raise;
942 End;
943
944 procedure insert_olap_object(
945 p_object varchar2,
946 p_object_type varchar2,
947 p_olap_object varchar2,
948 p_olap_object_type varchar2,
949 p_parent_object varchar2,
950 p_parent_object_type varchar2,
951 p_property1 varchar2
952 ) is
953 Begin
954 default_context_if_null;
955 if p_object is not null then
956 insert into bsc_olap_object(object,object_type,olap_object,olap_object_type,property1,parent_object,parent_object_type,
957 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
958 values (p_object,p_object_type,p_olap_object,p_olap_object_type,p_property1,p_parent_object,p_parent_object_type,
959 sysdate,sysdate,g_who,g_who,g_who,g_context);
960 end if;
961 Exception when others then
962 log_n('Exception in insert_olap_object '||sqlerrm);
963 raise;
964 End;
965
966 /*
967 check
968 p_object varchar2,
969 p_object_type varchar2,
970 p_parent_object varchar2,
971 p_parent_object_type varchar2,
972 then insert if needed. later we will see if update is needed else.
973 we do not match olap_object also since given an object and object type, we must not have multiple olap objects for the same entity
974 */
975 procedure merge_olap_object(
976 p_object varchar2,
977 p_object_type varchar2,
978 p_olap_object varchar2,
979 p_olap_object_type varchar2,
980 p_parent_object varchar2,
981 p_parent_object_type varchar2,
982 p_property1 varchar2
983 ) is
984 --
985 l_bsc_olap_object bsc_olap_object_tb;
986 Begin
987 default_context_if_null;
988 get_bsc_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,l_bsc_olap_object);
989 if l_bsc_olap_object.count>0 then
990 null;
991 /*update is risky here. we can have multiple rows of data in l_bsc_olap_object. so which rows do we update here?*/
992 else
993 insert_olap_object(p_object,p_object_type,p_olap_object,p_olap_object_type,p_parent_object,p_parent_object_type,p_property1);
994 end if;
995 Exception when others then
996 log_n('Exception in merge_olap_object '||sqlerrm);
997 raise;
998 End;
999
1000 procedure insert_olap_object_relation(
1001 p_object varchar2,
1002 p_object_type varchar2,
1003 p_relation_object varchar2,
1004 p_relation_object_type varchar2,
1005 p_relation_type varchar2,
1006 p_parent_object varchar2,
1007 p_parent_object_type varchar2,
1008 p_property1 varchar2
1009 ) is
1010 Begin
1011 default_context_if_null;
1012 if p_object is not null then
1013 insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,
1014 relation_type,parent_object,parent_object_type,property1,
1015 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
1016 values (p_object,p_object_type,p_relation_object,p_relation_object_type,
1017 p_relation_type,p_parent_object,p_parent_object_type,p_property1,
1018 sysdate,sysdate,g_who,g_who,g_who,g_context);
1019 end if;
1020 Exception when others then
1021 log_n('Exception in insert_olap_object_relation '||sqlerrm);
1022 raise;
1023 End;
1024
1025 /*
1026 check
1027 p_object varchar2,
1028 p_object_type varchar2,
1029 p_relation_object varchar2,
1030 p_relation_object_type varchar2,
1031 p_relation_type varchar2,
1032 p_parent_object varchar2,
1033 p_parent_object_type varchar2,
1034 then insert...later update?
1035 */
1036 procedure merge_olap_object_relation(
1037 p_object varchar2,
1038 p_object_type varchar2,
1039 p_relation_object varchar2,
1040 p_relation_object_type varchar2,
1041 p_relation_type varchar2,
1042 p_parent_object varchar2,
1043 p_parent_object_type varchar2,
1044 p_property1 varchar2
1045 ) is
1046 --
1047 l_bsc_olap_object_relation bsc_olap_object_relation_tb;
1048 Begin
1049 default_context_if_null;
1050 get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
1051 if l_bsc_olap_object_relation.count>0 then
1052 null;
1053 else
1054 insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,
1055 relation_type,parent_object,parent_object_type,property1,
1056 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
1057 values (p_object,p_object_type,p_relation_object,p_relation_object_type,
1058 p_relation_type,p_parent_object,p_parent_object_type,p_property1,
1059 sysdate,sysdate,g_who,g_who,g_who,g_context);
1060 end if;
1061 Exception when others then
1062 log_n('Exception in insert_olap_object_relation '||sqlerrm);
1063 raise;
1064 End;
1065
1066 /*
1067 input:
1068 p_object varchar2,
1069 p_object_type varchar2,
1070 p_relation_type varchar2,
1071 p_parent_object varchar2,
1072 p_parent_object_type varchar2,
1073
1074 update:
1075 p_relation_object varchar2,
1076 p_relation_object_type varchar2,
1077 p_property varchar2
1078 */
1079 procedure update_olap_object_relation(
1080 p_object varchar2,
1081 p_object_type varchar2,
1082 p_relation_type varchar2,
1083 p_parent_object varchar2,
1084 p_parent_object_type varchar2,
1085 p_match_columns dbms_sql.varchar2_table,
1086 p_match_values dbms_sql.varchar2_table,
1087 p_set_columns dbms_sql.varchar2_table,
1088 p_set_values dbms_sql.varchar2_table
1089 ) is
1090 --
1091 l_stmt varchar2(8000);
1092 Begin
1093 default_context_if_null;
1094 l_stmt:='update bsc_olap_object_relation set ';
1095 for i in 1..p_set_columns.count loop
1096 l_stmt:=l_stmt||p_set_columns(i)||'='''||p_set_values(i)||''',';
1097 end loop;
1098 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
1099 l_stmt:=l_stmt||' where object=:1 and object_type=:2 and relation_type=:3 and parent_object=:4 and parent_object_type=:5 and context=:6';
1100 for i in 1..p_match_columns.count loop
1101 l_stmt:=l_stmt||' and '||p_match_columns(i)||'='''||p_match_values(i)||'''';
1102 end loop;
1103 if g_debug then
1104 log(l_stmt||' using '||p_object||','||p_object_type||','||p_relation_type||','||p_parent_object||','||p_parent_object_type||','||
1105 g_context);
1106 end if;
1107 execute immediate l_stmt using p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,g_context;
1108 if g_debug then
1109 log('Updated '||sql%rowcount||' rows');
1110 end if;
1111 Exception when others then
1112 log_n('Exception in update_olap_object_relation '||sqlerrm);
1113 raise;
1114 End;
1115
1116 /*
1117 input :
1118 p_object varchar2,
1119 p_object_type varchar2,
1120 p_parent_object varchar2,
1121 p_parent_object_type varchar2
1122
1123 update
1124 p_olap_object varchar2,
1125 p_olap_object_type varchar2,
1126 p_property varchar2,
1127 p_operation_flag varchar2
1128
1129 */
1130 procedure update_olap_object(
1131 p_object varchar2,
1132 p_object_type varchar2,
1133 p_parent_object varchar2,
1134 p_parent_object_type varchar2,
1135 p_match_columns dbms_sql.varchar2_table,
1136 p_match_values dbms_sql.varchar2_table,
1137 p_set_columns dbms_sql.varchar2_table,
1138 p_set_values dbms_sql.varchar2_table
1139 ) is
1140 --
1141 l_stmt varchar2(8000);
1142 Begin
1143 default_context_if_null;
1144 l_stmt:='update bsc_olap_object set ';
1145 for i in 1..p_set_columns.count loop
1146 l_stmt:=l_stmt||p_set_columns(i)||'='''||p_set_values(i)||''',';
1147 end loop;
1148 l_stmt:=l_stmt||'last_update_date=sysdate';
1149 l_stmt:=l_stmt||' where object=:1 and object_type=:2 and parent_object=:3 and parent_object_type=:4 and context=:5';
1150 for i in 1..p_match_columns.count loop
1151 l_stmt:=l_stmt||' and '||p_match_columns(i)||'='''||p_match_values(i)||'''';
1152 end loop;
1153 if g_debug then
1154 log(l_stmt||' using '||p_object||','||p_object_type||','||p_parent_object||','||p_parent_object_type||','||g_context);
1155 end if;
1156 execute immediate l_stmt using p_object,p_object_type,p_parent_object,p_parent_object_type,g_context;
1157 if g_debug then
1158 log('Updated '||sql%rowcount||' rows');
1159 end if;
1160 Exception when others then
1161 log_n('Exception in update_olap_object '||sqlerrm);
1162 raise;
1163 End;
1164
1165 /*
1166 delete oor
1167 any parameter can be null. if all are null, full delete happens
1168 */
1169 procedure delete_olap_object_relation(
1170 p_object varchar2,
1171 p_object_type varchar2,
1172 p_relation_type varchar2,
1173 p_relation_object varchar2,
1174 p_relation_object_type varchar2,
1175 p_parent_object varchar2,
1176 p_parent_object_type varchar2) is
1177 --
1178 Begin
1179 default_context_if_null;
1180 if g_debug then
1181 log('delete_olap_object_relation:object='||p_object||', object_type='||p_object_type||
1182 ', p_relation_type='||p_relation_type||', p_relation_object='||p_relation_object||
1183 ', p_relation_object_type='||p_relation_object_type||', p_parent_object='||p_parent_object||
1184 ', p_parent_object_type='||p_parent_object_type||bsc_aw_utility.get_time);
1185 end if;
1186 delete bsc_olap_object_relation
1187 where object=nvl(p_object,object)
1188 and object_type=nvl(p_object_type,object_type)
1189 and relation_type=nvl(p_relation_type,relation_type)
1190 and relation_object=nvl(p_relation_object,relation_object)
1191 and relation_object_type=nvl(p_relation_object_type,relation_object_type)
1192 and parent_object=nvl(p_parent_object,parent_object)
1193 and parent_object_type=nvl(p_parent_object_type,parent_object_type)
1194 and context=g_context;
1195 if g_debug then
1196 log('Deleted '||sql%rowcount||' rows'||bsc_aw_utility.get_time);
1197 end if;
1198 Exception when others then
1199 log_n('Exception in delete_olap_object_relation '||sqlerrm);
1200 raise;
1201 End;
1202
1203 procedure delete_olap_object(
1204 p_object varchar2,
1205 p_object_type varchar2,
1206 p_olap_object varchar2,
1207 p_olap_object_type varchar2,
1208 p_parent_object varchar2,
1209 p_parent_object_type varchar2) is
1210 --
1211 Begin
1212 default_context_if_null;
1213 if g_debug then
1214 log('delete_olap_object:object='||p_object||', object_type='||p_object_type||
1215 ', p_olap_object='||p_olap_object||', p_olap_object_type='||p_olap_object_type||
1216 ', p_parent_object='||p_parent_object||', p_parent_object_type='||p_parent_object_type||bsc_aw_utility.get_time);
1217 end if;
1218 delete bsc_olap_object
1219 where object=nvl(p_object,object)
1220 and object_type=nvl(p_object_type,object_type)
1221 and olap_object=nvl(p_olap_object,olap_object)
1222 and olap_object_type=nvl(p_olap_object_type,olap_object_type)
1223 and parent_object=nvl(p_parent_object,parent_object)
1224 and parent_object_type=nvl(p_parent_object_type,parent_object_type)
1225 and context=g_context;
1226 if g_debug then
1227 log('Deleted '||sql%rowcount||' rows'||bsc_aw_utility.get_time);
1228 end if;
1229 Exception when others then
1230 log_n('Exception in delete_olap_object '||sqlerrm);
1231 raise;
1232 End;
1233
1234 -------------------------------
1235 ---workspace metadata
1236 procedure create_workspace(p_name varchar2) is
1237 Begin
1238 default_context_if_null;
1239 insert into bsc_olap_object(object,object_type,olap_object,olap_object_type,property1,parent_object,parent_object_type,
1240 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
1241 values (p_name,'aw workspace',p_name,'aw workspace','BSC AW Workspace','BSC','APPS',
1242 sysdate,sysdate,g_who,g_who,g_who,g_context);
1243 Exception when others then
1244 log_n('Exception in create_workspace '||sqlerrm);
1245 raise;
1246 End;
1247
1248 procedure drop_workspace(p_name varchar2) is
1249 Begin
1250 default_context_if_null;
1251 delete bsc_olap_object where object=p_name;
1252 Exception when others then
1253 log_n('Exception in drop_workspace '||sqlerrm);
1254 raise;
1255 End;
1256
1257 /*
1258 managing info on loads, aggregations, locks etc
1259 in bsc_olap_object, we will use property9 and 10 (9 for now) to store the load start time, end time
1260 session id etc. it will be stored as
1261 load+start time=01:01:2000:12:12:12+end time=01:01:2000:12:12:12+session id=1022,aggregation+..
1262 groups are separated by comma. elements in each group are separated by +.
1263 this is read into a table of records format. then we can update, insert etc.
1264 its then saved back into this format
1265 we have 2 api
1266 get_runtime_parameters(obj,objtype,par obj,par objtype,table of records (output))
1267 update_runtime_parameters(table of records (input))
1268 table of records has obj,type,par obj and par obj type so we can update
1269 */
1270 /*
1271 types of input
1272 obj yes, obj type yes, par obj yes, par obj type yes
1273 obj yes, obj type yes, par obj no, par obj type no
1274 obj no, obj type no, par obj yes, par obj type yes
1275 obj no, obj type no, par obj no, par obj type no
1276
1277 procedure get_runtime_parameters(
1278 object varchar2,
1279 object_type varchar2,
1280 parent_object varchar2,
1281 parent_object_type varchar2,
1282 p_parameters out nocopy bsc_runtime_tb
1283 ) is
1284 --
1285 --
1286 Begin
1287 null;
1288 Exception when others then
1289 log_n('Exception in get_runtime_parameters '||sqlerrm);
1290 raise;
1291 End;
1292
1293 will implement at a later date
1294 */
1295
1296 procedure analyze_md_tables is
1297 Begin
1298 bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT',60);
1299 bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT_RELATION',60);
1300 Exception when others then
1301 log_n('Exception in analyze_md_tables '||sqlerrm);
1302 raise;
1303 End;
1304
1305 -------------------------------
1306 procedure init_all is
1307 Begin
1308 g_context:='AW';--default
1309 g_who:=bsc_aw_utility.get_who;
1310 g_debug:=bsc_aw_utility.g_debug;
1311 Exception when others then
1312 null;
1313 End;
1314
1315 procedure log(p_message varchar2) is
1316 Begin
1317 bsc_aw_utility.log(p_message);
1318 Exception when others then
1319 null;
1320 End;
1321
1322 procedure log_n(p_message varchar2) is
1323 Begin
1324 log(' ');
1325 log(p_message);
1326 Exception when others then
1327 null;
1328 End;
1329
1330 END BSC_AW_MD_WRAPPER;
[Home] [Help]