1 package body BSC_AW_LOAD_DIM AS
2 /*$Header: BSCAWLDB.pls 120.11 2006/04/20 11:39 vsurendr noship $*/
3
4 /*
5 the top procedure is called with a list of dim levels. from RSG, a list of dim levels affecting a kpi or base table
6 are passed in. we will resolve into aw dim and call procedure and pass in aw dim
7 this procedure must then resolve all info and load. in 10g, these procedures will be launched in parallel as jobs
8 */
9 procedure load_dim(p_dim_level_list dbms_sql.varchar2_table) is
10 Begin
11 --p_dim_level_list is an assorted list.can contain city,product, state etc
12 if g_debug then
13 log_n('Load Dim');
14 log('Levels to load ');
15 for i in 1..p_dim_level_list.count loop
16 log(p_dim_level_list(i));
17 end loop;
18 end if;
19 --get the dimensions for the levels
20 load_dim_levels(p_dim_level_list);
21 Exception when others then
22 log_n('Exception in load_dim '||sqlerrm);
23 raise;
24 End;
25
26 procedure load_dim_levels(p_dim_level_list dbms_sql.varchar2_table) is
27 l_dim_list dbms_sql.varchar2_table;
28 l_dim varchar2(300);
29 --
30 Begin
31 for i in 1..p_dim_level_list.count loop
32 bsc_aw_md_api.get_dim_for_level(p_dim_level_list(i),l_dim);
33 if bsc_aw_utility.in_array(l_dim_list,l_dim)=false then
34 l_dim_list(l_dim_list.count+1):=l_dim;
35 end if;
36 end loop;
37 if g_debug then
38 log_n('Load the following AW Dim');
39 for i in 1..l_dim_list.count loop
40 log(l_dim_list(i));
41 end loop;
42 end if;
43 --
44 load_dimensions(l_dim_list);
45 Exception when others then
46 log_n('Exception in load_dim_levels '||sqlerrm);
47 raise;
48 End;
49
50 procedure load_dimensions(p_dim_list dbms_sql.varchar2_table) is
51 l_parallel boolean;
52 Begin
53 --if 10g, we can launcg parallel jobs.
54 l_parallel:=false;
55 if bsc_aw_utility.can_launch_jobs(p_dim_list.count)='Y' then
56 l_parallel:=true;
57 end if;
58 if l_parallel=false then
59 for i in 1..p_dim_list.count loop
60 load_aw_dim(p_dim_list(i),null,null,null);
61 end loop;
62 else --launch jobs and wait
63 load_aw_dim_jobs(p_dim_list);
64 end if;
65 Exception when others then
66 log_n('Exception in load_dimensions '||sqlerrm);
67 raise;
68 End;
69
70 procedure load_dim_if_needed(p_dim dbms_sql.varchar2_table) is
71 --
72 l_load_dim dbms_sql.varchar2_table;
73 l_dim_lock dbms_sql.varchar2_table;
74 Begin
75 if g_debug then
76 log('load_dim_if_needed, the dimensions to check for load are');
77 for i in 1..p_dim.count loop
78 log(p_dim(i));
79 end loop;
80 end if;
81 l_load_dim.delete;
82 l_dim_lock.delete;
83 for i in 1..p_dim.count loop
84 l_dim_lock(l_dim_lock.count+1):='lock_aw_dim_'||p_dim(i);
85 bsc_aw_utility.get_db_lock(l_dim_lock(l_dim_lock.count));
86 if check_dim_loaded(p_dim(i))='N' or bsc_aw_adapter_dim.check_dim_view_based(p_dim(i))='Y' then
87 l_load_dim(l_load_dim.count+1):=p_dim(i);
88 else
89 bsc_aw_utility.release_db_lock(l_dim_lock(l_dim_lock.count));
90 l_dim_lock.delete(l_dim_lock.count);
91 end if;
92 end loop;
93 if g_debug then
94 log('Dimensions to load');
95 for i in 1..l_load_dim.count loop
96 log(l_load_dim(i));
97 end loop;
98 end if;
99 --
100 load_dimensions(l_load_dim);--will parallelize if needed
101 --
102 for i in 1..l_dim_lock.count loop
103 bsc_aw_utility.release_db_lock(l_dim_lock(i));
104 end loop;
105 Exception when others then
106 for i in 1..l_dim_lock.count loop
107 bsc_aw_utility.release_db_lock(l_dim_lock(i));
108 end loop;
109 log_n('Exception in load_dim_if_needed '||sqlerrm);
110 raise;
111 End;
112
113 procedure load_aw_dim_jobs(p_dim_list dbms_sql.varchar2_table) is
114 --
115 l_job_name varchar2(100);
116 l_process varchar2(8000);
117 l_job_status bsc_aw_utility.parallel_job_tb;
118 l_exception exception;
119 pragma exception_init(l_exception,-20000);
120 --
121 Begin
122 bsc_aw_utility.clean_up_jobs('all');
123 for i in 1..p_dim_list.count loop
124 l_job_name:='bsc_aw_job_dim_'||bsc_aw_utility.get_session_id||'_'||i;
125 l_process:='bsc_aw_load_dim.load_aw_dim('''||p_dim_list(i)||''','||i||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
126 bsc_aw_utility.start_job(l_job_name,i,l_process,null);
127 end loop;
128 --wait (this will lock and wait)
129 bsc_aw_utility.wait_on_jobs(null,l_job_status);
130 --check the status
131 for i in 1..l_job_status.count loop
132 if l_job_status(i).status='error' then
133 --raise error
134 raise l_exception;
135 end if;
136 end loop;
137 Exception when others then
138 log_n('Exception in load_aw_dim_jobs '||sqlerrm);
139 raise;
140 End;
141
142 /*
143 this procedure is the atomic unit, given the aw dim, loads it.
144 it decides inc vs full etc. in 10g jobs also, this is the procedure that is called in the job
145
146 for now, lets start with full refresh all the time. otherwise we need to check the aw dim to see if there are
147 values in it. or we may need to check the temp table for the levels and see if there is data in the temp table
148 if there is data, assume inc refresh. else full
149 we commit after each dim load. if these are dbms jobs, there needs to be a commit after each dim load
150 (lets do this as part of 10g enhancements)
151 p_options: to support parallel jobs
152 */
153 procedure load_aw_dim(p_dim varchar2,p_run_id number,p_job_name varchar2,p_options varchar2) is
154 l_initial_load_pgm varchar2(300);
155 l_inc_load_pgm varchar2(300);
156 l_dim_property varchar2(4000);
157 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
158 l_denorm_src varchar2(4000);
159 l_dim_level_delete dim_level_delete_tv;
160 l_dim_delete_flag boolean;
161 Begin
162 --for the dim, get the program name,
163 --get the lock on the dim objects
164 if p_run_id is not null then
165 --this is a dbms job. we have to do the initializations since this is a new session
166 bsc_aw_utility.g_options.delete;
167 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
168 bsc_aw_utility.open_file(p_dim||'_'||p_run_id);
169 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
170 bsc_aw_utility.init_all_procedures;
171 end if;
172 log_n('load_aw_dim, p_dim='||p_dim||', p_run_id='||p_run_id||', p_job_name='||p_job_name||
173 ', p_options='||p_options);
174 l_oo.delete;
175 bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_oo);
176 for i in 1..l_oo.count loop
177 if l_oo(i).object_type='dml program' then
178 if l_oo(i).olap_object_type='dml program initial load' then
179 l_initial_load_pgm:=l_oo(i).object;
180 elsif l_oo(i).olap_object_type='dml program inc load' then
181 l_inc_load_pgm:=l_oo(i).object;
182 end if;
183 end if;
184 end loop;
185 for i in 1..l_oo.count loop
186 if l_oo(i).object_type='dimension' then
187 l_dim_property:=l_oo(i).property1;
188 exit;
189 end if;
190 end loop;
191 /*we cannot get multi locks if there are deletes. so we first pre-load delete values into memory. if deletes are involved, we get full lock */
192 load_dim_delete(p_dim,l_dim_property,l_dim_level_delete,l_dim_delete_flag);
193 lock_dim_objects(p_dim,l_dim_delete_flag);
194 if l_dim_delete_flag then
195 merge_delete_values_to_levels(l_dim_level_delete);
196 end if;
197 l_oo.delete;
198 /*if this is a rec dim and implemented with normal hier, we need to populate bsc_aw_temp_pc */
199 if nvl(bsc_aw_utility.get_parameter_value(l_dim_property,'recursive',','),'N')='Y' and
200 nvl(bsc_aw_utility.get_parameter_value(l_dim_property,'normal hier',','),'N')='Y' then
201 l_denorm_src:=bsc_aw_utility.get_parameter_value(l_dim_property,'denorm source',',');
202 if l_denorm_src is not null then --dbi rec dim
203 load_recursive_norm_hier(replace(l_denorm_src,'*^',','),
204 bsc_aw_utility.get_parameter_value(l_dim_property,'child col',','),
205 bsc_aw_utility.get_parameter_value(l_dim_property,'parent col',','));
206 end if;
207 end if;
208 /*4646329 for some reason, the dim levels were missing the value 0. maybe the dim was purged. we need a way to get the std values back into the dim
209 we do this for std and custom dim for now. projection dim is not loaded via program. now we assume text datatype. later we can have property as
210 pk data type in oo for dim level. for rec dim, 0 is never all
211 */
212 for i in 1..l_oo.count loop
213 if l_oo(i).object_type='dimension level' then
214 if l_oo(i).olap_object is not null then
215 bsc_aw_dbms_aw.execute('maintain '||l_oo(i).olap_object||' merge ''0''');
216 end if;
217 end if;
218 end loop;
219 --launch the aw program
220 if g_debug then
221 log_n('Going to load '||p_dim);
222 end if;
223 if check_initial_load(p_dim) then
224 bsc_aw_dbms_aw.execute('call '||l_initial_load_pgm);
225 else
226 bsc_aw_dbms_aw.execute('call '||l_inc_load_pgm);
227 end if;
228 if g_debug then
229 log_n('Finished load '||p_dim);
230 end if;
231 /*we now need to handle any deletes*/
232 if l_dim_delete_flag then
233 execute_dim_delete(l_dim_level_delete);
234 clean_bsc_aw_dim_delete(l_dim_level_delete);
235 end if;
236 --
237 set_kpi_limit_variables(p_dim);
238 bsc_aw_management.commit_aw;--will release the locks on the dim objects in 10g
239 bsc_aw_management.detach_workspace;
240 --if this is a job, send success message to pipe...update bsc_olap_object saying operation_flag='loaded'
241 mark_dim_loaded(p_dim);
242 --
243 if p_run_id is not null then
244 bsc_aw_utility.send_pipe_message(p_job_name,'status=success');
245 end if;
246 commit;
247 Exception when others then
248 log_n('Exception in load_aw_dim '||sqlerrm);
249 if p_run_id is not null then
250 bsc_aw_utility.send_pipe_message(p_job_name,'status=error,sqlcode='||sqlcode||',message='||sqlerrm);
251 rollback;
252 bsc_aw_management.detach_workspace;
253 else
254 raise;
255 end if;
256 End;
257
258 /*
259 delete are handled in the following way
260 delete table has 2 columns. dim_level and delete_value. say we have geog dim. city,state,country
261 we want to delete all cities in ca and ca
262 the table has
263 'city' 'SF'
264 'city' 'LA'
265 'state' 'CA'
266 the program already has support for marking limit cubes. once the program runs, this already has happened.
267 we now need to clean up the values from the dim.
268 Q:if we clean up CA and we still retail some cities in ca, what will happen to the relation. for example, sacramento
269 will say parent=ca while ca is gone. will agg on this relation error out? No. did a prototype to verify this. if CA is gone,
270 AW will take care of removing CA from the relation or at-least not considering it anymore
271 */
272 procedure load_dim_delete(
273 p_dim varchar2,
274 p_dim_property varchar2,
275 p_dim_level_delete in out nocopy dim_level_delete_tv,
276 p_delete_flag out nocopy boolean) is
277 --
278 l_level varchar2(300);
279 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
280 Begin
281 p_delete_flag:=false;
282 p_dim_level_delete('ALL').delete_name:='ALL';
283 p_dim_level_delete('ALL').delete_values.delete;
284 --get the dim levels
285 l_oo.delete;
286 bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);
287 --
288 if l_oo.count>0 then
289 for i in 1..l_oo.count loop
290 bsc_aw_utility.merge_value(p_dim_level_delete('ALL').delete_values,l_oo(i).object);--ALL will hold the levels involved
291 load_delete_dim_level_value(l_oo(i).object,upper(l_oo(i).object),p_dim_level_delete);
292 if p_dim_level_delete(l_oo(i).object).delete_values.count>0 then
293 p_delete_flag:=true;
294 end if;
295 end loop;
296 --
297 if nvl(bsc_aw_utility.get_parameter_value(p_dim_property,'recursive',','),'N')='Y' then
298 l_level:=l_oo(1).object;
299 l_oo.delete;
300 bsc_aw_md_api.get_bsc_olap_object(null,'recursive level',p_dim,'dimension',l_oo);
301 for i in 1..l_oo.count loop
302 bsc_aw_utility.merge_value(p_dim_level_delete('ALL').delete_values,l_oo(i).object);--ALL will hold the levels involved
303 load_delete_dim_level_value(l_oo(i).object,upper(l_level),p_dim_level_delete);
304 end loop;
305 end if;
306 end if;
307 Exception when others then
308 log_n('Exception in load_dim_delete '||sqlerrm);
309 raise;
310 End;
311
312 /*5064802. cannot delete in multi mode. pre-load deletes into memory. if there are any deletes, then get rw lock */
313 procedure load_delete_dim_level_value(
314 p_dim_level varchar2,
315 p_select_level varchar2,--useful in case of rec dim. we need to delete the virtual parent level also
316 p_dim_level_delete in out nocopy dim_level_delete_tv) is
317 l_stmt varchar2(4000);
318 TYPE CurTyp IS REF CURSOR;
319 cv CurTyp;
320 Begin
321 l_stmt:='select distinct delete_value from bsc_aw_dim_delete where dim_level=:1';
322 if g_debug then
323 log(l_stmt||' '||p_select_level||bsc_aw_utility.get_time);
324 end if;
325 p_dim_level_delete(p_dim_level).delete_name:=p_dim_level;
326 p_dim_level_delete(p_dim_level).delete_values.delete;
327 open cv for l_stmt using p_select_level;
328 loop
329 fetch cv bulk collect into p_dim_level_delete(p_dim_level).delete_values;
330 exit when cv%notfound;
331 end loop;
332 if g_debug then
333 log('Fetched '||p_dim_level_delete(p_dim_level).delete_values.count||' rows'||bsc_aw_utility.get_time);
334 end if;
335 close cv;
336 Exception when others then
337 log_n('Exception in load_delete_dim_level_value '||sqlerrm);
338 raise;
339 End;
340
341 procedure execute_dim_delete(p_dim_level_delete dim_level_delete_tv) is
342 l_levels dbms_sql.varchar2_table;
343 Begin
344 l_levels:=p_dim_level_delete('ALL').delete_values; --levels also include virtual rec dim parent level
345 for i in 1..l_levels.count loop
346 delete_dim_level_value(l_levels(i),p_dim_level_delete(l_levels(i)).delete_values);
347 end loop;
348 Exception when others then
349 log_n('Exception in execute_dim_delete '||sqlerrm);
350 raise;
351 End;
352
353 /*bsc_aw_dim_delete is created by loader. so keep the sql dynamic
354 5064802: when we delete values, we cannot be in multi attach mode. must get full rw lock
355 lock_dim_objects will lock the dim in full mode if there are deletes reqd*/
356 procedure delete_dim_level_value(p_dim_level varchar2,p_delete_values dbms_sql.varchar2_table) is
357 Begin
358 for i in 1..p_delete_values.count loop
359 bsc_aw_dbms_aw.execute('mnt '||p_dim_level||' delete '''||p_delete_values(i)||'''');
360 end loop;
361 Exception when others then
362 log_n('Exception in delete_dim_level_value '||sqlerrm);
363 raise;
364 End;
365
366 procedure clean_bsc_aw_dim_delete(p_dim_level_delete dim_level_delete_tv) is
367 l_levels dbms_sql.varchar2_table;
368 l_stmt varchar2(4000);
369 Begin
370 l_levels:=p_dim_level_delete('ALL').delete_values;
371 for i in 1..l_levels.count loop
372 l_stmt:='delete bsc_aw_dim_delete where dim_level=:1';
373 if g_debug then
374 log(l_stmt||' '||l_levels(i)||bsc_aw_utility.get_time);
375 end if;
376 execute immediate l_stmt using l_levels(i);
377 if g_debug then
378 log('Deleted '||sql%rowcount||' rows '||bsc_aw_utility.get_time);
379 end if;
380 end loop;
381 Exception when others then
382 log_n('Exception in clean_bsc_aw_dim_delete '||sqlerrm);
383 raise;
384 End;
385
386 /*
387 to check initial vs inc laod, check the temp table.
388 */
389 function check_initial_load(p_dim varchar2) return boolean is
390 Begin
391 --for now, full refresh
392 return true;
393 Exception when others then
394 log_n('Exception in check_initial_load '||sqlerrm);
395 raise;
396 End;
397
398 /*
399 purge a dim completely from the given list of levels
400 */
401 procedure purge_dim(p_dim_level_list dbms_sql.varchar2_table) is
402 l_dim_list dbms_sql.varchar2_table;
403 l_dim varchar2(300);
404 Begin
405 for i in 1..p_dim_level_list.count loop
406 bsc_aw_md_api.get_dim_for_level(p_dim_level_list(i),l_dim);
407 if bsc_aw_utility.in_array(l_dim_list,l_dim)=false then
408 l_dim_list(l_dim_list.count+1):=l_dim;
409 end if;
410 end loop;
411 for i in 1..l_dim_list.count loop
412 purge_dim(l_dim_list(i));
413 end loop;
414 Exception when others then
415 log_n('Exception in purge_dim '||sqlerrm);
416 raise;
417 End;
418
419 /*purge a dim
420 logic:
421 purge all related kpi
422 purge dim related data objects
423 purge dim levels
424 */
425 procedure purge_dim(p_dim varchar2) is
426 --
427 l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
428 l_kpi_list dbms_sql.varchar2_table;
429 Begin
430 if g_debug then
431 log_n('Purge dim '||p_dim);
432 end if;
433 --get lock, for purge, we need exclusive locks. otherwise we cannot delete dimensions
434 bsc_aw_management.get_workspace_lock('rw',null);
435 --purge kpi
436 bsc_aw_md_api.get_kpi_for_dim(p_dim,l_kpi_list);
437 for i in 1..l_kpi_list.count loop
438 bsc_aw_load_kpi.purge_kpi(l_kpi_list(i));
439 end loop;
440 --
441 bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_olap_object);
442 --purge variables
443 for i in 1..l_olap_object.count loop
444 if l_olap_object(i).object_type='limit cube' or l_olap_object(i).object_type='filter cube' then
445 bsc_aw_dbms_aw.execute('clear all from '||l_olap_object(i).olap_object);
446 end if;
447 end loop;
448 --
449 for i in 1..l_olap_object.count loop
450 if l_olap_object(i).object_type='dimension level' then
451 bsc_aw_dbms_aw.execute('mnt '||l_olap_object(i).olap_object||' delete all');
452 end if;
453 end loop;
454 --
455 bsc_aw_management.commit_aw;
456 commit;
457 Exception when others then
458 log_n('Exception in purge_dim '||sqlerrm);
459 raise;
460 End;
461
462 /*
463 this procedure will dmp the dim level data into table bsc_aw_dim_data
464 used for bis dimensions that are not materialized. bsc loader needs the dim values
465 to know which values have got deleted
466
467 this creates the program on the fly, executes it and drops the program
468 NO COMMIT!!!
469 remember: multiple dim loads can be happening. so we cannot have just one program for all
470 dim levels. the same level cannot be loaded in 2 sessions
471 */
472 procedure dmp_dim_level_into_table(p_dim_level_list dbms_sql.varchar2_table) is
473 Begin
474 for i in 1..p_dim_level_list.count loop
475 dmp_dim_level_into_table(upper(p_dim_level_list(i)));
476 end loop;
477 Exception when others then
478 log_n('Exception in dmp_dim_level_into_table '||sqlerrm);
479 raise;
480 End;
481 --
482 procedure dmp_dim_level_into_table(p_dim_level varchar2) is
483 --
484 l_name varchar2(300);
485 Begin
486 l_name:='dmp_'||p_dim_level;
487 bsc_aw_dbms_aw.execute_ne('delete '||l_name);
488 bsc_aw_adapter_dim.create_dmp_program(p_dim_level,l_name);
489 bsc_aw_dbms_aw.execute('call '||l_name);
490 bsc_aw_dbms_aw.execute('delete '||l_name);
491 Exception when others then
492 log_n('Exception in dmp_dim_level_into_table '||sqlerrm);
493 raise;
494 End;
495
496 /*
497 say there are hier changes. we need to make sure we mark the kpi limit variables accordingly.
498 example:
499 if any(BSC_CCDIM_100_101_102_103.limit.bool) --limit bool now represented as .LB to reduce length of name
500 then do
501 limit BSC_CCDIM_100_101_102_103 to BSC_CCDIM_100_101_102_103.limit.bool
502 kpi_3014_1.BSC_CCDIM_100_101_102_103.limit.bool=TRUE
503 doend
504 earlier this was in the dim program. but there is an issue here. when creating the dim programs, we cannot
505 know which kpi are implemented. if we assume that all kpi marked for aw are implemented, we can run into an
506 issue if a kpi ends up not implemented. so better do this at runtime, after reading the olap metadata
507
508 here we will limit the dim to whatever hier changed. then we will set the kpi.dim.limit.bool to these values
509 at this time, the levels for which the hier changed maynot even be a level of the kpi. this is ok. at the time
510 when we are about to aggregate the kpi, we will eliminate all the levels that are not involved
511 we also set the aggregate marker for the kpi dimset to true
512 */
513 procedure set_kpi_limit_variables(p_dim varchar2) is
514 --
515 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
516 l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
517 l_dim_limit_cube varchar2(300);
518 l_kpi_limit_cubes dbms_sql.varchar2_table;
519 l_kpi_aggregate_markers dbms_sql.varchar2_table;
520 l_kpi_reset_cubes dbms_sql.varchar2_table;
521 l_statlen varchar2(200);
522 l_relation varchar2(200);
523 Begin
524 bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'dim limit cube',p_dim,'dimension',l_olap_object_relation);
525 bsc_aw_md_api.get_bsc_olap_object(null,'relation',p_dim,'dimension',l_olap_object);
526 if l_olap_object.count>0 then
527 l_relation:=l_olap_object(1).olap_object;
528 else
529 if g_debug then
530 log_n('No relation found for dim '||p_dim);
531 end if;
532 end if;
533 --there must be only 1 limit cube
534 --see which kpi and which dim sets have this dim. get the kpi limit cube from the property
535 if l_olap_object_relation.count > 0 then
536 l_dim_limit_cube:=l_olap_object_relation(1).relation_object;
537 --get the cube limit cubes
538 --see if we need to set the aggregate marker
539 --the output comes in formats like 321,667...so do not want to use to_number(...)
540 bsc_aw_dbms_aw.execute('push '||p_dim);
541 bsc_aw_dbms_aw.execute('limit '||p_dim||' to '||l_dim_limit_cube);
542 l_statlen:=bsc_aw_dbms_aw.interp('show statlen('||p_dim||')');
543 if l_statlen <> '0' then
544 if l_relation is not null then
545 bsc_aw_dbms_aw.execute('limit '||p_dim||' to children using '||l_relation);
546 end if;
547 l_statlen:=bsc_aw_dbms_aw.interp('show statlen('||p_dim||')');
548 if l_statlen <> '0' then
549 l_kpi_limit_cubes.delete;
550 l_kpi_aggregate_markers.delete;
551 bsc_aw_adapter_dim.get_dim_kpi_limit_cubes(p_dim,l_kpi_limit_cubes,l_kpi_aggregate_markers,l_kpi_reset_cubes);
552 for i in 1..l_kpi_limit_cubes.count loop
553 bsc_aw_dbms_aw.execute(l_kpi_limit_cubes(i)||'=TRUE');
554 end loop;
555 for i in 1..l_kpi_aggregate_markers.count loop
556 bsc_aw_dbms_aw.execute(l_kpi_aggregate_markers(i)||'=TRUE');
557 end loop;
558 end if;
559 --handle setting reset cubes
560 /*
561 we cannot have this part inside if l_statlen <> '0' (after drill down to children). imagine we have A >- B.
562 now, A does not have a manager. so we have A and B. in this case, l_statlen will be 0. but we need to set the
563 reset cubes to 0 for B.
564 reset cubes are reqd because a parent node can be left with no children in which case, we need to set the values for
565 the node to NA in the cube
566 example: A C changes to A C
567 a b d a b d
568 in this case, we need to set the aggregated value for A to na in the cubes. AW will not re-agg A. it will re-agg only if
569 A has at-least another child node, even if this child node is not a part of the cube
570 */
571 if l_relation is not null then
572 bsc_aw_dbms_aw.execute('limit '||p_dim||' to parents using '||l_relation);
573 bsc_aw_dbms_aw.execute(l_dim_limit_cube||'=false');
574 bsc_aw_dbms_aw.execute('limit '||p_dim||' to '||l_dim_limit_cube);--only hanging nodes left
575 l_statlen:=bsc_aw_dbms_aw.interp('show statlen('||p_dim||')');
576 if l_statlen <> '0' then
577 for i in 1..l_kpi_reset_cubes.count loop
578 bsc_aw_dbms_aw.execute(l_kpi_reset_cubes(i)||'=TRUE');
579 end loop;
580 end if;
581 end if;
582 --dim limit cube will be set to false at the start of the dim load
583 end if;
584 bsc_aw_dbms_aw.execute('pop '||p_dim);
585 end if;
586 Exception when others then
587 log_n('Exception in set_kpi_limit_variables '||sqlerrm);
588 raise;
589 End;
590
591 procedure lock_dim_objects(p_dim varchar2,p_dim_delete boolean) is
592 --
593 l_lock_objects dbms_sql.varchar2_table;
594 Begin
595 if p_dim_delete then --full rw lock
596 bsc_aw_management.get_workspace_lock('rw',null);
597 else
598 get_dim_objects_to_lock(p_dim,l_lock_objects);
599 bsc_aw_management.get_workspace_lock(l_lock_objects,null);
600 end if;
601 Exception when others then
602 log_n('Exception in lock_dim_objects '||sqlerrm);
603 raise;
604 End;
605
606 /*
607 we cannot lock concat dim. got error
608 acquire BSC_CCDIM_100_101_102_103 (S: 04/13/2005 17:34:10
609 Exception in execute acquire BSC_CCDIM_100_101_102_103 ORA-37018:Multiwriter operations are not supported for object BSC_AW!BSC_CCDIM_100_101_102_103.
610 --
611 we have to acquire locks and update in a certain order . else we get
612 ORA-37023: (XSMLTUPD01) Object workspace object cannot be updated without dimension workspace object.
613 we cannot update a relation before a dim. so when we get locks, we first get dim, then relations, then variables
614 */
615 procedure get_dim_objects_to_lock(p_dim varchar2,p_lock_objects out nocopy dbms_sql.varchar2_table) is
616 --
617 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
618 l_kpi_limit_cubes dbms_sql.varchar2_table;
619 l_kpi_reset_cubes dbms_sql.varchar2_table;
620 l_kpi_aggregate_markers dbms_sql.varchar2_table;
621 l_objects dbms_sql.varchar2_table;
622 Begin
623 l_bsc_olap_object.delete;
624 bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_bsc_olap_object);
625 for i in 1..l_bsc_olap_object.count loop
626 if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='dimension' then
627 l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
628 end if;
629 end loop;
630 for i in 1..l_bsc_olap_object.count loop
631 if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='relation' then
632 l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
633 end if;
634 end loop;
635 for i in 1..l_bsc_olap_object.count loop
636 if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='variable' then
637 l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
638 end if;
639 end loop;
640 --also, we have to lock the kpi limit cubes
641 bsc_aw_adapter_dim.get_dim_kpi_limit_cubes(p_dim,l_kpi_limit_cubes,l_kpi_aggregate_markers,l_kpi_reset_cubes);
642 for i in 1..l_kpi_limit_cubes.count loop
643 l_objects(l_objects.count+1):=l_kpi_limit_cubes(i);
644 end loop;
645 for i in 1..l_kpi_aggregate_markers.count loop
646 l_objects(l_objects.count+1):=l_kpi_aggregate_markers(i);
647 end loop;
648 --
649 for i in 1..l_objects.count loop
650 if bsc_aw_utility.in_array(p_lock_objects,l_objects(i))=false then
651 p_lock_objects(p_lock_objects.count+1):=l_objects(i);
652 end if;
653 end loop;
654 Exception when others then
655 log_n('Exception in get_dim_objects_to_lock '||sqlerrm);
656 raise;
657 End;
658
659 /*
660 this is called only for rec dim implemented with normal hier. in this case, we take the data from denorm
661 table and normalize it into a temp table bsc_aw_temp_pc. the dim load program for the rec dim will
662 pick up data from here
663 to support multiple parents, we use the rank fn. if a child has 2 parents (example 3), we will see
664 C P RANK()OVER(PARTITIONBYCORDERBYROWNUM)
665 ---------- ---------- -------------------------------------
666 2 1 1
667 3 1 1
668 3 6 2
669 order by parent col is imp. it makes the result repeatable
670 this api is only fired for dbi based rec dim
671 */
672 procedure load_recursive_norm_hier(p_denorm_source varchar2,p_child_col varchar2,p_parent_col varchar2) is
673 --
674 l_stmt varchar2(8000);
675 Begin
676 if p_denorm_source is not null then
677 bsc_aw_utility.execute_stmt('delete bsc_aw_temp_pc');
678 bsc_aw_utility.execute_stmt('delete bsc_aw_temp_vn');
679 --
680 l_stmt:='insert into bsc_aw_temp_vn(name,id) select '||p_child_col||',count(*) from '||p_denorm_source||' group by '||p_child_col;
681 bsc_aw_utility.execute_stmt(l_stmt);
682 --
683 l_stmt:='insert into bsc_aw_temp_pc(parent,child,id) select '||p_parent_col||','||p_child_col||',rank() over(partition by '||
684 p_child_col||' order by '||p_parent_col||') from (select denorm.'||p_parent_col||',denorm.'||p_child_col||' from '||
685 p_denorm_source||' denorm,bsc_aw_temp_vn t1,bsc_aw_temp_vn t2 where denorm.'||p_parent_col||'=t1.name(+) and '||
686 'denorm.'||p_child_col||'=t2.name and t2.id=nvl(t1.id,0)+1)';
687 bsc_aw_utility.execute_stmt(l_stmt);
688 end if;
689 Exception when others then
690 log_n('Exception in load_recursive_norm_hier '||sqlerrm);
691 raise;
692 End;
693
694 function check_dim_loaded(p_dim varchar2) return varchar2 is
695 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
696 Begin
697 if bsc_aw_utility.in_array(bsc_aw_adapter_dim.get_preloaded_dim_list,p_dim)=false then
698 bsc_aw_md_api.get_bsc_olap_object(p_dim,'dimension',p_dim,'dimension',l_oo);
699 if l_oo(1).operation_flag is not null and l_oo(1).operation_flag='loaded' then
700 return 'Y';
701 else
702 return 'N';
703 end if;
704 else
705 return 'Y';
706 end if;
707 Exception when others then
708 log_n('Exception in check_dim_loaded '||sqlerrm);
709 raise;
710 End;
711
712 procedure mark_dim_loaded(p_dim varchar2) is
713 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
714 Begin
715 bsc_aw_md_api.update_olap_object(p_dim,'dimension',p_dim,'dimension',null,null,'operation_flag','loaded');
716 bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);
717 for i in 1..l_oo.count loop --mark snowflake impl as loaded
718 bsc_aw_md_api.update_olap_object(l_oo(i).object,'dimension',l_oo(i).object,'dimension',null,null,'operation_flag','loaded');
719 end loop;
720 Exception when others then
721 log_n('Exception in mark_dim_loaded '||sqlerrm);
722 raise;
723 End;
724
725 /* 5064802 cannot recreate dim programs. do this: load all dim once again and also load the values from bsc_aw_dim_delete table
726 from now on, hopefully, there will not be attempt to delete values that do not exist in dimensions. newly created dim will have robust strategy
727 to handle cases where dim delete values are not yet in aw dim*/
728 procedure upgrade_load_sync_all_dim is
729 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
730 l_dim varchar2(200);
731 l_dim_list dbms_sql.varchar2_table;
732 Begin
733 bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',null,'dimension',l_oo);
734 for i in 1..l_oo.count loop
735 if bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity',',') is null then --this is not a calendar dim level
736 bsc_aw_md_api.get_dim_for_level(l_oo(i).object,l_dim);
737 bsc_aw_utility.merge_value(l_dim_list,l_dim);
738 end if;
739 end loop;
740 --
741 for i in 1..l_dim_list.count loop
742 upgrade_load_sync_all_dim(l_dim_list(i));
743 end loop;
744 Exception when others then
745 log_n('Exception in upgrade_load_sync_all_dim '||sqlerrm);
746 raise;
747 End;
748
749 procedure upgrade_load_sync_all_dim(p_dim varchar2) is
750 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
751 l_dim_level_delete dim_level_delete_tv;
752 l_delete_flag boolean;
753 Begin
754 if g_debug then
755 log('upgrade_load_sync_all_dim '||p_dim);
756 end if;
757 /*add delete values into the dim levels */
758 bsc_aw_md_api.get_bsc_olap_object(p_dim,'dimension',p_dim,'dimension',l_oo);
759 if l_oo.count>0 then
760 load_dim_delete(p_dim,l_oo(1).property1,l_dim_level_delete,l_delete_flag);
761 if l_delete_flag then
762 merge_delete_values_to_levels(l_dim_level_delete);
763 end if;
764 end if;
765 /*refresh the dim */
766 l_oo.delete;
767 bsc_aw_md_api.get_bsc_olap_object(null,'dml program',p_dim,'dimension',l_oo);
768 for i in 1..l_oo.count loop
769 if l_oo(i).olap_object_type='dml program initial load' then
770 begin
771 bsc_aw_dbms_aw.execute('call '||l_oo(i).object);
772 exception when others then
773 null;
774 end;
775 exit;
776 end if;
777 end loop;
778 Exception when others then
779 log_n('Exception in upgrade_load_sync_all_dim '||sqlerrm);
780 raise;
781 End;
782
783 /*5074869 in the load programs, the delete values are handled only when the dim levels have parents. if this is the top level and
784 bsc_aw_dim_delete has values not in the dim, we can run into the issue of value not valid error
785 best way is to merge these delete values into the dim levels anyway */
786 procedure merge_delete_values_to_levels(p_dim_level_delete dim_level_delete_tv) is
787 l_levels dbms_sql.varchar2_table;
788 Begin
789 l_levels:=p_dim_level_delete('ALL').delete_values;
790 for i in 1..l_levels.count loop
791 for j in 1..p_dim_level_delete(l_levels(i)).delete_values.count loop
792 bsc_aw_dbms_aw.execute('mnt '||l_levels(i)||' merge '''||p_dim_level_delete(l_levels(i)).delete_values(j)||'''');
793 end loop;
794 end loop;
795 Exception when others then
796 log_n('Exception in merge_delete_values_to_levels '||sqlerrm);
797 raise;
798 End;
799
800 ------------------------------------------
801 procedure init_all is
802 Begin
803 g_debug:=bsc_aw_utility.g_debug;
804 Exception when others then
805 log_n('Exception in init_all '||sqlerrm);
806 raise;
807 End;
808
809 procedure log(p_message varchar2) is
810 Begin
811 bsc_aw_utility.log(p_message);
812 Exception when others then
813 null;
814 End;
815
816 procedure log_n(p_message varchar2) is
817 Begin
818 log(' ');
819 log(p_message);
820 Exception when others then
821 null;
822 End;
823
824 END BSC_AW_LOAD_DIM;
[Home] [Help]