1 package body BSC_AW_LOAD_KPI AS
2 /*$Header: BSCAWLKB.pls 120.24 2006/04/20 11:22 vsurendr noship $*/
3
4 /*
5 for kpi, there are 2 ways to load
6 1 load a kpi
7 2 load base table and kpi associated with them
8
9 in 10g, each kpi can run in parallel
10 within each kpi, each dim set can run in parallel
11 10g:each kpi may be a separate conc process
12 */
13
14 procedure load_kpi(
15 p_kpi_list dbms_sql.varchar2_table
16 ) is
17 --
18 l_parallel boolean;
19 Begin
20 --if 10g, we can launcg parallel jobs.
21 l_parallel:=false;
22 if bsc_aw_utility.can_launch_jobs(p_kpi_list.count)='Y' then
23 l_parallel:=true;
24 end if;
25 if l_parallel=false then
26 for i in 1..p_kpi_list.count loop
27 load_kpi(p_kpi_list(i),null,null,null,null);
28 end loop;
29 else
30 --start jobs and wait
31 load_kpi_jobs(p_kpi_list,null);
32 end if;
33 Exception when others then
34 log_n('Exception in load_kpi '||sqlerrm);
35 raise;
36 End;
37
38 --p_base_table_list : comma separated list of base tables
39 procedure load_kpi_jobs(p_kpi_list dbms_sql.varchar2_table,p_base_table_list varchar2) is
40 --
41 l_job_name varchar2(100);
42 l_process varchar2(8000);
43 l_job_status bsc_aw_utility.parallel_job_tb;
44 Begin
45 bsc_aw_utility.clean_up_jobs('all');
46 for i in 1..p_kpi_list.count loop
47 l_job_name:='bsc_aw_load_kpi_'||bsc_aw_utility.get_dbms_time||'_'||i;
48 l_process:='bsc_aw_load_kpi.load_kpi('''||p_kpi_list(i)||''','''||nvl(p_base_table_list,'null')||''','||i||','''||l_job_name||''','''||
49 bsc_aw_utility.get_option_string||''');';
50 bsc_aw_utility.start_job(l_job_name,i,l_process,null);
51 end loop;
52 bsc_aw_utility.wait_on_jobs(null,l_job_status);
53 for i in 1..l_job_status.count loop
54 if l_job_status(i).status='error' then
55 raise bsc_aw_utility.g_exception;
56 end if;
57 end loop;
58 Exception when others then
59 log_n('Exception in load_kpi_jobs '||sqlerrm);
60 raise;
61 End;
62
63 /*
64 p_base_table_list and p_kpi_list are 1 to 1. the entries can look as
65 BSC_B_1 3014
66 BSC_B_1 4000
67 BSC_B_2 3014
68 in 10g, we can parallelize each B -> KPI load
69 */
70 procedure load_base_table(
71 p_base_table_list dbms_sql.varchar2_table,
72 p_kpi_list dbms_sql.varchar2_table
73 ) is
74 --
75 l_kpi_list dbms_sql.varchar2_table; --distinct list of kpi
76 --l_base_table_list dbms_sql.varchar2_table;--base tables in each kpi
77 l_parallel boolean;
78 Begin
79 for i in 1..p_kpi_list.count loop
80 if bsc_aw_utility.in_array(l_kpi_list,p_kpi_list(i))=false then
81 l_kpi_list(l_kpi_list.count+1):=p_kpi_list(i);
82 end if;
83 end loop;
84 l_parallel:=false;
85 if bsc_aw_utility.can_launch_jobs(l_kpi_list.count)='Y' then
86 l_parallel:=true;
87 end if;
88 --NOTE!!! >>> load_kpi loads and aggregates
89 if l_parallel=false then
90 for i in 1..l_kpi_list.count loop
91 load_kpi(l_kpi_list(i),bsc_aw_utility.make_string_from_list(p_base_table_list),null,null,null);
92 end loop;
93 else
94 load_kpi_jobs(p_kpi_list,bsc_aw_utility.make_string_from_list(p_base_table_list));
95 end if;
96 Exception when others then
97 log_n('Exception in load_base_table '||sqlerrm);
98 raise;
99 End;
100
101 -------------private api--------------------------------------
102 /*
103 in 10g, this will be called as a dbms job (each kpi will be a conc process)
104 find out the dimsets for the kpi. each dim set in 10g can be loaded in parallel
105 dimsets can be parallelized using dbms jobs
106
107 in 10g, the aggregation of dimsets can be in parallel, and within each dimset, the agg of cubes
108 can be in parallel
109
110 we are going to follow this:
111 load all dimsets of the kpi.
112 then aggregate all dimsets
113
114 when loading base tables,
115 load all base tables for a kpi.
116 then aggregate all dimsets involved
117
118 aggregation is tricky. we have to aggregate actuals and related target dimsets together since the status
119 of limit cubes have to be kept in mind p_base_table_list is comma separated list of base tables
120
121 This is the single point of entry for both loading kpi and base tables
122 p_base_table_list can contain all the base tables. this means base tables of the kpi and not of the kpi
123 */
124 procedure load_kpi(p_kpi varchar2,p_base_table_list varchar2,p_run_id number,p_job_name varchar2,p_options varchar2) is
125 l_dim_set dbms_sql.varchar2_table; --this is all the dimsets, actuals and targets
126 l_aggregate_dim_set dbms_sql.varchar2_table; --dimsets used in aggregation. (Targets eliminated)
127 l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
128 l_base_table_list dbms_sql.varchar2_table;
129 l_varchar_table dbms_sql.varchar2_table;--temp
130 --
131 l_parallel boolean;
132 l_aggregation aggregation_r;
133 l_dim_set_parallel dbms_sql.varchar2_table;
134 l_dim_set_r bsc_aw_adapter_kpi.dim_set_r;
135 l_pl_base_tables dbms_sql.varchar2_table;
136 Begin
137 if p_run_id is not null then
138 --this is a dbms job. we have to do the initializations since this is a new session
139 bsc_aw_utility.g_options.delete;
140 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
141 bsc_aw_utility.open_file('Load_kpi_'||p_kpi||'_'||p_run_id);
142 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
143 bsc_aw_utility.init_all_procedures;
144 end if;
145 log_n('load_kpi, p_kpi='||p_kpi||',p_base_table_list='||p_base_table_list||
146 ', p_run_id='||p_run_id||', p_job_name='||p_job_name||', p_options='||p_options);
147 --
148 set_aggregation(p_kpi,l_aggregation);
149 --if the calendar is not loaded, load it now
150 load_calendar_if_needed(p_kpi);
151 --get md info on all dimsets
152 --l_aggregation has all the dimsets, actuals and targets
153 if p_base_table_list is not null and p_base_table_list <> 'null' then --loading base tables
154 bsc_aw_utility.parse_parameter_values(p_base_table_list,',',l_varchar_table);
155 get_kpi_base_tables(p_kpi,l_varchar_table,l_base_table_list);
156 --l_base_table_list contains base tables that belong to the kpi from "p_base_table_list"
157 get_dimset_for_base_table(p_kpi,l_base_table_list,l_dim_set);
158 else --loading kpi
159 --!!! l_oo_dimset contains all the dimset, actuals and targets
160 bsc_aw_md_api.get_kpi_dimset(p_kpi,l_oo_dimset);
161 for i in 1..l_oo_dimset.count loop
162 l_dim_set(l_dim_set.count+1):=l_oo_dimset(i).object;
163 end loop;
164 end if;
165 if g_debug then
166 log_n('Load and Aggregate the following dimsets');
167 for i in 1..l_dim_set.count loop
168 log(l_dim_set(i));
169 end loop;
170 if l_base_table_list.count>0 then
171 log('With the following base tables');
172 for i in 1..l_base_table_list.count loop
173 log(l_base_table_list(i));
174 end loop;
175 end if;
176 end if;
177 --first, load dim if needed
178 load_dim_if_needed(p_kpi,l_dim_set);
179 --
180 /*detach workspace . found an interesting issue. if a diff session updates and commits an object, then if the workspace is
181 not detached and attached again, update to that object raises error. dim load touches the LB. then when we try to save
182 these back in the load of the kpi, it raises an error
183 this is true when we try to get lock resync on a cube. the error is that dependent dim also must be relocked because they
184 have changed in the dim load. so best is to release the WS and reacquire the WS
185 ideally, we should first save lock set, commit lock set, then detach and then lock the lock set back again. since at this point, there are no
186 objects waiting to be saved or locked, we skip that step
187 */
188 bsc_aw_management.detach_workspace;
189 /*need logic to handle load. if all dimsets are parallelized and each dimset has partitions, then too many jobs are launched
190 logic:check dimsets for partitions. dimsets with partitions are launched serially. then the remaining dimsets are launched in
191 parallel if possible */
192 l_dim_set_parallel.delete;
193 for i in 1..l_dim_set.count loop
194 l_dim_set_r:=l_aggregation.dim_set(get_dim_set_index(l_aggregation,l_dim_set(i)));
195 if l_dim_set_r.number_partitions>0 then
196 if g_debug then
197 log(l_dim_set(i)||' has partitions. Load run serially');
198 end if;
199 load_kpi_dimset(p_kpi,l_dim_set(i),l_base_table_list);/*if there are partitions for the dimset, launch them serially */
200 else
201 l_dim_set_parallel(l_dim_set_parallel.count+1):=l_dim_set(i);
202 end if;
203 end loop;
204 l_parallel:=false;
205 if l_dim_set_parallel.count>0 then
206 if bsc_aw_utility.can_launch_jobs(l_dim_set_parallel.count)='Y' then
207 l_parallel:=true;
208 end if;
209 --note>> if l_base_table_list is specified, l_dim_set_parallel will only be the dimsets which contain the base tabkes
210 --as a src. this is done in get_dimset_for_base_table above
211 /* */
212 if l_parallel then
213 l_pl_base_tables.delete;
214 get_base_table_for_dimset(p_kpi,l_base_table_list,l_dim_set_parallel,l_pl_base_tables);
215 /*l_pl_base_tables contains the B tables that belong to l_dim_set_parallel and belonging to l_base_table_list if l_base_table_list.count>0
216 to be 100 percent accurate, we have to only pick B tables that have inc data. But to keep this simple, just look at total B table
217 size*/
218 if is_parallel_load(l_pl_base_tables,bsc_aw_utility.g_parallel_load_cutoff)=false then
219 if g_debug then
220 log('Due to insufficient load, Parallel load of dimsets made serial');
221 end if;
222 l_parallel:=false;
223 end if;
224 end if;
225 if l_parallel=false then
226 for i in 1..l_dim_set_parallel.count loop
227 load_kpi_dimset(p_kpi,l_dim_set_parallel(i),l_base_table_list);
228 end loop;
229 else
230 load_kpi_dimset_job(p_kpi,l_dim_set_parallel,l_base_table_list);
231 end if;
232 end if;
233 /*must detach workspace
234 ideally, we should first save lock set, commit lock set, then detach and then lock the lock set back again. since at this point, there are no
235 objects waiting to be saved or locked, we skip that step*/
236 bsc_aw_management.detach_workspace;
237 --aggregate the dimsets
238 --aggregate_kpi_dimset will only aggregate actual dimsets if there is a corresponding target also
239 get_aggregate_dimsets(p_kpi,l_dim_set,l_aggregate_dim_set);
240 if l_aggregate_dim_set.count>0 then
241 if g_debug then
242 log_n('Aggregate the following dimsets. After eliminating targets');
243 for i in 1..l_aggregate_dim_set.count loop
244 log(l_aggregate_dim_set(i));
245 end loop;
246 end if;
247 --
248 --now, we can aggregate these dimsets
249 --aggregate_kpi_dimset handled targets. note: targets have already been loaded in load_kpi_dimset in the prev step...
250 --reset_dim_limits also happens in aggregate_kpi_dimset
251 --we will only try and aggregate the actuals. when aggregating the actuals, aggregate_kpi_dimset will also aggregate the
252 --corresponding target dimset. this is why we pass l_aggregation. we must aggregate only the actuals here. the reason is that
253 --if we try aggregating targets first, ahead of actuals, then when we aggregate the actuals, it will overwrite the target info
254 --we aggregate all measures of the dimset. cost is in composite creation. so aggregating only the measures affected by the B tables
255 --does not get better perf.
256 /*to manage load better, if dimset has partitions, we launch them serially */
257 l_dim_set_parallel.delete;
258 for i in 1..l_aggregate_dim_set.count loop
259 l_dim_set_r:=l_aggregation.dim_set(get_dim_set_index(l_aggregation,l_aggregate_dim_set(i)));
260 if l_dim_set_r.number_partitions>0 then
261 if g_debug then
262 log(l_aggregate_dim_set(i)||' has partitions. Aggregation run serially');
263 end if;
264 aggregate_kpi_dimset(p_kpi,l_aggregate_dim_set(i),null,null,null);/*to aggregate in parallel on partitions or not is checked inside this api*/
265 else
266 l_dim_set_parallel(l_dim_set_parallel.count+1):=l_aggregate_dim_set(i);
267 end if;
268 end loop;
269 l_parallel:=false;
270 if l_dim_set_parallel.count>0 then
271 if bsc_aw_utility.can_launch_jobs(l_dim_set_parallel.count)='Y' then
272 l_parallel:=true;
273 end if;
274 /*check to see if there is sufficient load to warrant parallel load .
275 earlier we had is_parallel_aggregate test here. we cannot do this. when we aggregate, 100 nodes can explode into 100000 nodes
276 depending on the hier depths. this means we cannot disable parallel aggregation just because there are onlyu 100 nodes. load could
277 bring in 100 nodes.the only time we can do is_parallel_aggregate test is for formula and target copy because the nodes are
278 not increasing in these cases
279 */
280 if l_parallel=false then
281 for i in 1..l_dim_set_parallel.count loop
282 aggregate_kpi_dimset(p_kpi,l_dim_set_parallel(i),null,null,null);
283 end loop;
284 else
285 aggregate_kpi_dimset_job(p_kpi,l_dim_set_parallel);
286 end if;
287 end if;
288 if p_run_id is not null then
289 bsc_aw_utility.send_pipe_message(p_job_name,'status=success');
290 bsc_aw_management.detach_workspace;
291 end if;
292 else
293 if g_debug then
294 log('No dimsets to aggregate');
295 end if;
296 end if;
297 commit;
298 Exception when others then
299 log_n('Exception in load_kpi '||sqlerrm);
300 if p_run_id is not null then
301 bsc_aw_utility.send_pipe_message(p_job_name,'status=error,sqlcode='||sqlcode||',message='||sqlerrm);
302 rollback;
303 bsc_aw_management.detach_workspace;
304 else
305 raise;
306 end if;
307 End;
308
309 /*
310 p_base_tables will be base tables belonging to the kpi
311 */
312 procedure load_kpi_dimset_job(p_kpi varchar2,p_dimset_list dbms_sql.varchar2_table,p_base_tables dbms_sql.varchar2_table) is
313 --
314 l_job_name varchar2(100);
315 l_process varchar2(8000);
316 l_job_status bsc_aw_utility.parallel_job_tb;
317 Begin
318 bsc_aw_utility.clean_up_jobs('all');
319 for i in 1..p_dimset_list.count loop
320 l_job_name:='bsc_aw_load_kpi_dimset_'||bsc_aw_utility.get_dbms_time||'_'||i;
321 l_process:='bsc_aw_load_kpi.load_kpi_dimset('''||p_kpi||''','''||p_dimset_list(i)||''','''||
322 nvl(bsc_aw_utility.make_string_from_list(p_base_tables),'null')||''','||i||','''||l_job_name||''','''||
323 bsc_aw_utility.get_option_string||''');';
324 bsc_aw_utility.start_job(l_job_name,i,l_process,null);
325 end loop;
326 bsc_aw_utility.wait_on_jobs(null,l_job_status);
327 for i in 1..l_job_status.count loop
328 if l_job_status(i).status='error' then
329 raise bsc_aw_utility.g_exception;
330 end if;
331 end loop;
332 Exception when others then
333 log_n('Exception in load_kpi_dimset_job '||sqlerrm);
334 raise;
335 End;
336
337 --just a wrapper to call load_kpi_dimset as a job. we cannot pass dbms_sql.varchar2_table when launching a job
338 procedure load_kpi_dimset(p_kpi varchar2,p_dim_set varchar2,p_base_tables varchar2,
339 p_run_id number,p_job_name varchar2,p_options varchar2) is
340 --
341 l_base_tables dbms_sql.varchar2_table;
342 Begin
343 if p_base_tables is not null and p_base_tables <> 'null' then
344 bsc_aw_utility.parse_parameter_values(p_base_tables,',',l_base_tables);
345 end if;
346 if p_run_id is not null then
347 --this is a dbms job. we have to do the initializations since this is a new session
348 bsc_aw_utility.g_options.delete;
349 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
350 bsc_aw_utility.open_file('Load_KD_'||p_dim_set||'_'||p_run_id);
351 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
352 bsc_aw_utility.init_all_procedures;
353 end if;
354 load_kpi_dimset(p_kpi,p_dim_set,l_base_tables);
355 if p_run_id is not null then
356 bsc_aw_utility.send_pipe_message(p_job_name,'status=success');
357 bsc_aw_management.detach_workspace;
358 end if;
359 commit;
360 Exception when others then
361 log_n('Exception in load_kpi_dimset '||sqlerrm);
362 if p_run_id is not null then
363 bsc_aw_utility.send_pipe_message(p_job_name,'status=error,sqlcode='||sqlcode||',message='||sqlerrm);
364 rollback;
365 bsc_aw_management.detach_workspace;
366 else
367 raise;
368 end if;
369 End;
370
371 /*
372 given a dimset, find out the cubes and load them
373 also do the calculations like forecasts, aggregations etc
374
375 this will be called as a dbms job in 10g. commit happens here
376
377 logic:
378 find out the base tables.
379 if any of the base tables is full load, the whole dimset is full load. check the _aw tables for data.
380 if they have data, inc load. else check b table. if there are rows, then full load
381
382 Once a dimset has been loaded, we can aggregate the cubes in the dimset
383 --
384 base tables can also have corresponding prj tables. p_base_tables will not contain the prj tables. however, get_ds_bt_parameters will grab
385 the correspnding string that contains the prj table also. so for now, we are not adding the prj tables to p_base_tables or l_oor_dimsets
386 */
387 procedure load_kpi_dimset(p_kpi varchar2,p_dim_set varchar2,p_base_tables dbms_sql.varchar2_table) is
388 --
389 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
390 l_load_type varchar2(40); --initial or inc
391 l_oor_dimsets bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
392 Begin
393 --
394 l_olap_object_relation.delete;
395 --get the base tables l_olap_object_relation(i).object are the base tables
396 bsc_aw_md_api.get_dimset_base_table(p_kpi,p_dim_set,'base table dim set',l_olap_object_relation);
397 --if base tables are specified, filter out the un-needed dimsets
398 --there must be atleast one base table belonging to any dimset
399 if p_base_tables.count>0 then
400 for i in 1..l_olap_object_relation.count loop
401 if bsc_aw_utility.in_array(p_base_tables,l_olap_object_relation(i).object) then
402 l_oor_dimsets(l_oor_dimsets.count+1):=l_olap_object_relation(i);
403 end if;
404 end loop;
405 else
406 l_oor_dimsets:=l_olap_object_relation;
407 end if;
408 load_kpi_dimset_base_table(l_oor_dimsets);
409 Exception when others then
410 log_n('Exception in load_kpi_dimset '||sqlerrm);
411 raise;
412 End;
413
414 /*
415 this is the atomic procedure. a table of olap relations are passed . this can be 2 types.
416 1. load of a kpi. im this case we will have
417 B1 dimset1 kpi1
418 B2 dimset1 kpi1
419 diff base tables for the same dimset and kpi
420
421 2. load of a baes table. in this case we will have
422 B1 dimset1 kpi1
423 or
424 B1 dimset2 kpi1
425 or
426 B1 dimset1 kpi2
427 the reason for the or is that in caes of base table loading, there will be only 1 entry in the table. each of them
428 can be run in parallel in 10g.
429
430 this api desiced inc vs full refresh
431 note: in this api, we load essentially one dimset for a kpi. it can be multiple base tables or single base table
432
433 here we call the aggregate marker program. this api is running in parallel for various dim sets in the same kpi. so
434 its ok to call the aggregate marker program since this program is processing each dim set. the same dimset cannot be loaded
435 at the same time by 2 diff sessions. we have lock_dimset_objects here.
436 */
437 procedure load_kpi_dimset_base_table(
438 p_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb
439 ) is
440 --
441 l_load_type varchar2(40); --initial or inc
442 l_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
443 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
444 l_aggregate_marker_program varchar2(300);
445 l_load_program varchar2(300);
446 l_load_program_parallel varchar2(300);
447 --
448 l_kpi varchar2(300);
449 l_dimset varchar2(300);
450 --
451 l_pl_base_tables dbms_sql.varchar2_table;/*to check parallel load */
452 l_pl_change_vector dbms_sql.number_table;
453 l_min_value dbms_sql.number_table;
454 l_max_value dbms_sql.number_table;
455 l_bt_current_period dbms_sql.varchar2_table;
456 l_measures dbms_sql.varchar2_table;--to see if we can launch parallel jobs, we need the count of the no. of measures in the dimset
457 l_property varchar2(3000);
458 l_aggregation aggregation_r;
459 l_LB_resync_program varchar2(200);
460 parallel_flag varchar2(10);
461 --
462 Begin
463 if g_debug then
464 log_n('load_kpi_dimset_base_table ');
465 log('Entries');
466 for i in 1..p_olap_object_relation.count loop
467 log('kpi='||p_olap_object_relation(i).parent_object||' base table='||p_olap_object_relation(i).object
468 ||' objtype='||p_olap_object_relation(i).object_type||' reltype='||p_olap_object_relation(i).relation_type
469 ||' dimset='||p_olap_object_relation(i).relation_object||' relobjtype='||p_olap_object_relation(i).relation_object_type);
470 end loop;
471 bsc_aw_utility.clean_stats('group.load_kpi_dimset_base_table');
472 bsc_aw_utility.load_stats('Start Of Process. Load kpi dimset base table','group.load_kpi_dimset_base_table');
473 end if;
474 --note!! >> there is only 1 dimset in this api. there can be 1 or more base tables
475 l_kpi:=p_olap_object_relation(1).parent_object;
476 l_dimset:=p_olap_object_relation(1).relation_object;
477 --
478 set_aggregation(l_kpi,l_aggregation);
479 --get lock
480 lock_dimset_objects(l_kpi,l_dimset,null,null);
481 --before we call the load programs, we will first run aggregate_marker_program. this will set the limit cubes to true
482 --all the values that need to be re-aggregated because dim hierarchies changed
483 l_dimset_oor.delete;
484 l_aggregate_marker_program:=null;
485 bsc_aw_md_api.get_bsc_olap_object_relation(l_dimset,'kpi dimension set',null,l_kpi,'kpi',l_dimset_oor);
486 for i in 1..l_dimset_oor.count loop
487 if l_dimset_oor(i).relation_type='aggregate marker program' then
488 l_aggregate_marker_program:=l_dimset_oor(i).relation_object;
489 exit;
490 end if;
491 end loop;
492 if l_aggregate_marker_program is not null then
493 bsc_aw_dbms_aw.execute('call '||l_aggregate_marker_program);
494 end if;
495 --
496 --for each dim set, for each base table, see if inc or full. then
497 --if there is initial for any of the baestables for the dimset, full refresh for the dim ste
498 --a dimset is unique only in the context of a kpi. but since the kpi name is a part of the dimset, dimset name is unique
499 for i in 1..p_olap_object_relation.count loop
500 l_load_type:=check_load_type(p_olap_object_relation(i).property1);
501 if l_load_type='initial' then
502 exit;
503 end if;
504 end loop;
505 --p_olap_object_relation(1).relation_object is the dimset. we assume only 1 dimset in this api
506 l_load_program:=null;
507 l_load_program_parallel:=null;
508 for i in 1..l_dimset_oor.count loop
509 if (l_load_type='initial' and l_dimset_oor(i).relation_type='dml program initial load') or
510 (l_load_type='inc' and l_dimset_oor(i).relation_type='dml program inc load') then
511 l_load_program:=l_dimset_oor(i).relation_object;
512 exit;
513 end if;
514 end loop;
515 for i in 1..l_dimset_oor.count loop
516 if (l_load_type='initial' and l_dimset_oor(i).relation_type='dml program initial load parallel') or
517 (l_load_type='inc' and l_dimset_oor(i).relation_type='dml program inc load parallel') then
518 l_load_program_parallel:=l_dimset_oor(i).relation_object;
519 exit;
520 end if;
521 end loop;
522 --
523 for i in 1..l_dimset_oor.count loop
524 if l_dimset_oor(i).relation_type='LB resync program' then
525 l_LB_resync_program:=l_dimset_oor(i).relation_object;
526 exit;
527 end if;
528 end loop;
529 --get base table change vector value . insert a row into bsc_aw_temp_cv for each base table
530 l_pl_base_tables.delete;
531 l_pl_change_vector.delete;
532 for i in 1..p_olap_object_relation.count loop
533 l_min_value(i):=to_number(bsc_aw_utility.get_parameter_value(p_olap_object_relation(i).property1,'current change vector',','))+1;
534 l_olap_object_relation.delete;
535 bsc_aw_md_api.get_bsc_olap_object_relation(p_olap_object_relation(i).object,'base table',null,
536 p_olap_object_relation(i).object,'base table',l_olap_object_relation);
537 l_max_value(i):=null;
538 l_bt_current_period(i):=null;
539 for j in 1..l_olap_object_relation.count loop
540 if l_olap_object_relation(j).relation_type='base table change vector' then
541 l_max_value(i):=to_number(l_olap_object_relation(j).relation_object);
542 if l_max_value(i) is not null and (l_min_value(i) is null or l_max_value(i)>=l_min_value(i)) then
543 l_pl_base_tables(l_pl_base_tables.count+1):=p_olap_object_relation(i).object;
544 l_pl_change_vector(l_pl_change_vector.count+1):=l_max_value(i);
545 end if;
546 elsif l_olap_object_relation(j).relation_type='base table current period' then
547 l_bt_current_period(i):=l_olap_object_relation(j).relation_object;
548 end if;
549 end loop;
550 if l_max_value(i) is null then
551 log_n('Error!!! No change vector value found for base table '||p_olap_object_relation(i).object);
552 raise bsc_aw_utility.g_exception;
553 end if;
554 if l_bt_current_period(i) is null then
555 l_bt_current_period(i):='null';
556 end if;
557 end loop;
558 /*
559 to see if we can parallelize, we will take a count of the number of measures in the dimset.
560 in 10g, we can parallelize load of measures if they have diff composites
561 now, for 10g, we parallelize based on partitions
562 */
563 l_measures.delete;
564 bsc_aw_md_api.get_relation_object(l_dimset_oor,'dim set measure',l_measures);
565 parallel_flag:=can_launch_jobs(l_kpi,l_aggregation.dim_set(get_dim_set_index(l_aggregation,l_dimset)),l_measures);
566 if parallel_flag='Y' then
567 if l_pl_base_tables.count>0 then
568 /*check to see if there is sufficient data load to go for parallel load. otherwise, serial load is faster */
569 if is_parallel_load(l_pl_base_tables,l_pl_change_vector,bsc_aw_utility.g_parallel_load_cutoff)=false then
570 if g_debug then
571 log('Due to insufficient load Parallel load of cubes and partitions made serial');
572 end if;
573 parallel_flag:='N';
574 end if;
575 else
576 if g_debug then
577 log('There is no new B table data to load. Parallel load of cubes and partitions made serial');
578 end if;
579 parallel_flag:='N';
580 end if;
581 end if;
582 if parallel_flag='Y' then
583 load_kpi_dimset_base_table_job(l_kpi,l_dimset,p_olap_object_relation,l_dimset_oor,l_load_program_parallel,l_LB_resync_program,
584 l_min_value,l_max_value,l_bt_current_period);
585 else
586 load_kpi_dimset_base_table(l_kpi,l_dimset,p_olap_object_relation,l_dimset_oor,l_load_program,
587 l_min_value,l_max_value,l_bt_current_period);
588 end if;
589 --base table dimset combination for a change vector is unique
590 --update the current change vector for a B table->dimset combination
591 for i in 1..p_olap_object_relation.count loop
592 l_property:=p_olap_object_relation(i).property1;
593 bsc_aw_utility.update_property(l_property,'current change vector',to_char(l_max_value(i)),',');
594 if l_bt_current_period(i) is not null and l_bt_current_period(i)<>'null' then
595 bsc_aw_utility.update_property(l_property,'base table current period',l_bt_current_period(i),',');
596 end if;
597 bsc_aw_md_api.update_olap_object_relation(p_olap_object_relation(i).object,p_olap_object_relation(i).object_type,
598 p_olap_object_relation(i).relation_type,p_olap_object_relation(i).parent_object,p_olap_object_relation(i).parent_object_type,
599 'relation_object,relation_object_type',p_olap_object_relation(i).relation_object||','||p_olap_object_relation(i).relation_object_type,
600 'property1',l_property);
601 end loop;
602 --
603 bsc_aw_management.commit_aw;--release the dimset objects
604 commit;
605 if g_debug then
606 bsc_aw_utility.load_stats('End Of Process. Load kpi dimset base table','group.load_kpi_dimset_base_table');
607 bsc_aw_utility.print_stats('group.load_kpi_dimset_base_table');
608 end if;
609 Exception when others then
610 --lock release happens in an exxeption when the workspace is eventually detached and the session ends
611 log_n('Exception in load_kpi_dimset_base_table '||sqlerrm);
612 raise;
613 End;
614
615 /*
616 This procedure loads the base table dimset combination in single thread. this means it calls the dimset load
617 program and passes the base table as parameter
618 load_kpi_dimset_base_table_job on the other hand loads the measures in parallel
619 */
620 procedure load_kpi_dimset_base_table(
621 p_kpi varchar2,
622 p_dimset varchar2,
623 p_base_table_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get baes table, dimset and bt measures
624 p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
625 p_load_program varchar2,
626 p_min_value dbms_sql.number_table,
627 p_max_value dbms_sql.number_table,
628 p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null'. this has to be in sync with p_base_table_dimset_oor*/
629 ) is
630 --
631 l_base_tables dbms_sql.varchar2_table;
632 l_bt_current_period dbms_sql.varchar2_table;
633 l_flag boolean;
634 l_ds_parameters dbms_sql.varchar2_table;
635 l_aggregation aggregation_r;
636 l_dim_set bsc_aw_adapter_kpi.dim_set_r;
637 --
638 l_cubes dbms_sql.varchar2_table;
639 l_measures dbms_sql.varchar2_table;
640 l_bt_measures dbms_sql.varchar2_table;
641 Begin
642 l_flag:=false;
643 for i in 1..p_base_table_dimset_oor.count loop
644 if p_min_value(i)<=p_max_value(i) then
645 insert_bsc_aw_temp_cv(p_min_value(i),p_max_value(i),upper(p_base_table_dimset_oor(i).object));
646 l_flag:=true;
647 end if;
648 end loop;
649 for i in 1..p_base_table_dimset_oor.count loop
650 l_base_tables(l_base_tables.count+1):=upper(p_base_table_dimset_oor(i).object);
651 l_bt_current_period(l_bt_current_period.count+1):=p_bt_current_period(i);
652 end loop;
653 set_aggregation(p_kpi,l_aggregation);
654 l_dim_set:=l_aggregation.dim_set(get_dim_set_index(l_aggregation,p_dimset));
655 for i in 1..p_base_table_dimset_oor.count loop
656 l_bt_measures.delete;
657 bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(p_base_table_dimset_oor(i).property1,'measures',','),'+',
658 l_bt_measures);
659 for j in 1..l_bt_measures.count loop
660 bsc_aw_utility.merge_value(l_measures,l_bt_measures(j));
661 end loop;
662 end loop;
663 for i in 1..p_dimset_oor.count loop
664 if p_dimset_oor(i).relation_type='dim set measure' then
665 if bsc_aw_utility.in_array(l_measures,p_dimset_oor(i).relation_object) then
666 bsc_aw_utility.merge_value(l_cubes,bsc_aw_utility.get_parameter_value(p_dimset_oor(i).property1,'cube',','));
667 end if;
668 end if;
669 end loop;
670 /*see if we need to correct projection or bal values with a change to base table current period */
671 check_bt_current_period_change(p_kpi,l_dim_set,l_cubes,l_measures,l_base_tables,l_bt_current_period,null);
672 if l_flag then
673 /*set the cal end period rel for balance aggregation in the kpi load programs*/
674 limit_calendar_end_period_rel(l_dim_set.calendar);
675 get_ds_BT_parameters(p_kpi,p_dimset,p_load_program,l_base_tables,l_ds_parameters);
676 if g_debug then
677 log('Before Load, Composite Counts');
678 dmp_dimset_composite_count(l_dim_set);
679 end if;
680 for i in 1..l_ds_parameters.count loop
681 bsc_aw_dbms_aw.execute('call '||p_load_program||'('''||l_ds_parameters(i)||''')');
682 end loop;
683 if g_debug then
684 log('After Load, Composite Counts');
685 dmp_dimset_composite_count(l_dim_set);
686 end if;
687 reset_calendar_end_period_rel(l_dim_set.calendar);
688 end if;
689 Exception when others then
690 log_n('Exception in load_kpi_dimset_base_table '||sqlerrm);
691 raise;
692 End;
693
694 /*
695 In 10g, the cubes can be loaded in parallel in addition to being aggregated in parallel. this brings true parallelization
696 each cube has its own composite. if not loaded in parallel, load takes time as N composites are created at the load time
697 in parallel, these N composites can be created in parallel.
698 this procedure is a wrapper.
699 p_olap_object_relation contains 1 dimset but multiple base tables
700 for 10g, we now have datacubes and load/aggregate in parallel
701 */
702 procedure load_kpi_dimset_base_table_job(
703 p_kpi varchar2,
704 p_dimset varchar2,
705 p_base_table_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get baes table, dimset and bt measures
706 p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
707 p_load_program varchar2,
708 p_LB_resync_program varchar2,
709 p_min_value dbms_sql.number_table,
710 p_max_value dbms_sql.number_table,
711 p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null' */
712 ) is
713 --
714 l_job_name varchar2(100);
715 l_process varchar2(8000);
716 l_job_status bsc_aw_utility.parallel_job_tb;
717 --
718 l_measures dbms_sql.varchar2_table;
719 l_cubes dbms_sql.varchar2_table;
720 l_start_lock_objects varchar2(3000);
721 l_end_lock_objects varchar2(3000);
722 l_parameter_string varchar2(2000);
723 l_base_table_stmt varchar2(2000);
724 l_min_stmt varchar2(2000);
725 l_max_stmt varchar2(2000);
726 l_bt_current_period_stmt varchar2(2000);
727 l_lock_object_cubes dbms_sql.varchar2_table;
728 l_lock_object_limit_cubes dbms_sql.varchar2_table;
729 l_lock_object_reset_cubes dbms_sql.varchar2_table;
730 l_run_id number;
731 l_cubes_to_load dbms_sql.varchar2_table;
732 l_measures_to_load dbms_sql.varchar2_table;
733 --
734 l_aggregation aggregation_r;
735 l_dim_set bsc_aw_adapter_kpi.dim_set_r;
736 l_cube_pt bsc_aw_adapter_kpi.partition_template_r;
737 l_bt_considered dbms_sql.varchar2_table;
738 l_partition_options varchar2(2000); /*hold partition dim value */
739 Begin
740 bsc_aw_utility.clean_up_jobs('all');
741 for i in 1..p_dimset_oor.count loop
742 --p_dimset_oor contains info pertaining to p_dimset only
743 if p_dimset_oor(i).relation_type='dim set measure' then
744 l_measures(l_measures.count+1):=p_dimset_oor(i).relation_object;
745 l_cubes(l_cubes.count+1):=bsc_aw_utility.get_parameter_value(p_dimset_oor(i).property1,'cube',',');
746 if bsc_aw_utility.in_array(l_cubes_to_load,l_cubes(l_cubes.count))=false then
747 l_cubes_to_load(l_cubes_to_load.count+1):=l_cubes(l_cubes.count);
748 end if;
749 end if;
750 end loop;
751 /*
752 we now have support for partitions. we need to have a thread per cube...not per measure
753 then for each cube, we can parallelize based on partitions
754 technically, we need to group cubes by PT or comp.lets assume that diff cubes have diff PT or comp so they can be loaded in
755 parallel
756 for each measure, find out all base tables that carry this measure.
757 release the lock on the cubes. we release all cubes and countvar cubes of the dimset. this is ok since 2 base tables feeding a dimset
758 have to go one after the other anyway . we need l_aggregation to get the dimset info
759 */
760 --
761 set_aggregation(p_kpi,l_aggregation);
762 l_dim_set:=l_aggregation.dim_set(get_dim_set_index(l_aggregation,p_dimset));
763 --
764 get_measure_objects_to_lock(p_kpi,p_dimset,l_measures,l_lock_object_cubes);
765 get_dimset_objects_to_lock(p_kpi,p_dimset,'dim limit cube',l_lock_object_limit_cubes);
766 get_dimset_objects_to_lock(p_kpi,p_dimset,'dim reset cube',l_lock_object_reset_cubes);
767 --we must save these objects back to the system first, especially the limit cubes that can change due to aggregate marker pgm
768 --for perf reasons, at present, we will not save the cube...it could not have changed
769 --reset cubes share the composite with limit cubes.
770 bsc_aw_management.commit_aw(l_lock_object_limit_cubes,'no release lock');
771 bsc_aw_management.release_lock(l_lock_object_cubes);
772 bsc_aw_management.release_lock(l_lock_object_limit_cubes);
773 bsc_aw_management.release_lock(l_lock_object_reset_cubes);
774 --
775 /*
776 if the cube is partitioned, we load the partitions in parallel, job per partition for the cube
777 if the cube is not partitioned, its one job per cube
778 */
779 --
780 l_run_id:=0;
781 for i in 1..l_cubes_to_load.count loop
782 l_base_table_stmt:=null;
783 l_min_stmt:=null;
784 l_max_stmt:=null;
785 l_bt_current_period_stmt:=null;
786 l_bt_considered.delete;
787 l_measures_to_load.delete;
788 for j in 1..l_measures.count loop
789 if l_cubes(j)=l_cubes_to_load(i) then
790 for k in 1..p_base_table_dimset_oor.count loop
791 if instr(p_base_table_dimset_oor(k).property1,l_measures(j)||'+')>0 then
792 bsc_aw_utility.merge_value(l_measures_to_load,l_measures(j));
793 if bsc_aw_utility.in_array(l_bt_considered,p_base_table_dimset_oor(k).object)=false then
794 l_base_table_stmt:=l_base_table_stmt||p_base_table_dimset_oor(k).object||',';
795 l_min_stmt:=l_min_stmt||p_min_value(k)||',';
796 l_max_stmt:=l_max_stmt||p_max_value(k)||',';
797 l_bt_current_period_stmt:=l_bt_current_period_stmt||p_bt_current_period(k)||',';
798 l_bt_considered(l_bt_considered.count+1):=p_base_table_dimset_oor(k).object;
799 end if;
800 end if;
801 end loop;
802 end if;
803 end loop;
804 if l_base_table_stmt is not null then
805 --l_base_table_stmt can be null. we may be loading base table that is feeding 2 out of 5 measures for example
806 --see if the cube is partitioned or not
807 l_cube_pt.template_name:=bsc_aw_adapter_kpi.get_cube_axis(l_cubes_to_load(i),l_dim_set,'partition template');
808 if l_cube_pt.template_name is not null then --this is a partitioned cube
809 l_cube_pt:=bsc_aw_adapter_kpi.get_partition_template_r(l_cube_pt.template_name,l_dim_set);
810 l_end_lock_objects:=null;
811 --end lock objects are the limit cubes
812 for j in 1..l_lock_object_limit_cubes.count loop
813 l_end_lock_objects:=l_end_lock_objects||l_lock_object_limit_cubes(j)||',';
814 end loop;
815 for j in 1..l_cube_pt.template_partitions.count loop
816 l_job_name:='bsc_aw_lc_'||p_dimset||'_'||l_cubes_to_load(i)||'_part_'||l_cube_pt.template_partitions(j).partition_name||'_'||
817 bsc_aw_utility.get_dbms_time;
818 l_parameter_string:=l_cubes_to_load(i)||','''',''''partition='||l_cube_pt.template_partitions(j).partition_name||',';
819 l_partition_options:='partition='||l_cube_pt.template_partitions(j).partition_name||',partition dim value='||
820 l_cube_pt.template_partitions(j).partition_dim_value;
821 l_start_lock_objects:=l_cubes_to_load(i)||' (partition '||l_cube_pt.template_partitions(j).partition_name||'),';
822 l_run_id:=l_run_id+1;
823 l_process:='bsc_aw_load_kpi.load_cube_base_table('''||p_kpi||''','''||p_dimset||''','''||l_parameter_string||''','''||
824 l_cubes_to_load(i)||''','''||bsc_aw_utility.make_string_from_list(l_measures_to_load)||''','''||
825 l_base_table_stmt||''','''||l_start_lock_objects||''','''||l_end_lock_objects||''','''||p_load_program||''','''||
826 p_LB_resync_program||''','''||l_min_stmt||''','''||l_max_stmt||''','''||l_bt_current_period_stmt||''','''||l_partition_options||''','||
827 l_run_id||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
828 bsc_aw_utility.start_job(l_job_name,l_run_id,l_process,null);
829 end loop;
830 else --non partitioned cube
831 l_job_name:='bsc_aw_lc_'||p_dimset||'_'||l_cubes_to_load(i)||'_'||bsc_aw_utility.get_dbms_time;
832 l_start_lock_objects:=l_cubes_to_load(i)||',';
833 l_run_id:=l_run_id+1;
834 l_process:='bsc_aw_load_kpi.load_cube_base_table('''||p_kpi||''','''||p_dimset||''','''||l_start_lock_objects||''','''||
835 l_cubes_to_load(i)||''','''||bsc_aw_utility.make_string_from_list(l_measures_to_load)||''','''||
836 l_base_table_stmt||''','''||l_start_lock_objects||''','''||l_end_lock_objects||''','''||p_load_program||''',''null'','''||l_min_stmt||''','''||
837 l_max_stmt||''','''||l_bt_current_period_stmt||''',''null'','||
838 l_run_id||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
839 bsc_aw_utility.start_job(l_job_name,l_run_id,l_process,null);
840 end if;
841 end if;
842 end loop;
843 /*
844 in the case where we have partitions, we do not need to launch the process for limit cubes. if we call the program with
845 LIMIT CUBE, it will do nothing. but best if we do not call the procedure at all
846 */
847 if l_dim_set.cube_design <> 'datacube' then
848 l_base_table_stmt:=null;
849 l_min_stmt:=null;
850 l_max_stmt:=null;
851 l_bt_current_period_stmt:=null;
852 for i in 1..p_base_table_dimset_oor.count loop
853 l_base_table_stmt:=l_base_table_stmt||p_base_table_dimset_oor(i).object||',';
854 l_min_stmt:=l_min_stmt||p_min_value(i)||',';
855 l_max_stmt:=l_max_stmt||p_max_value(i)||',';
856 l_bt_current_period_stmt:=l_bt_current_period_stmt||p_bt_current_period(i)||',';
857 end loop;
858 if l_base_table_stmt is not null then
859 l_job_name:='bsc_aw_load_limit_cube_'||p_dimset||'_'||bsc_aw_utility.get_dbms_time||'_'||l_run_id;
860 l_start_lock_objects:=null;
861 for i in 1..l_lock_object_limit_cubes.count loop --l_lock_object_limit_cubes are the limit cubes
862 l_start_lock_objects:=l_start_lock_objects||l_lock_object_limit_cubes(i)||',';
863 end loop;
864 l_run_id:=l_run_id+1;
865 l_process:='bsc_aw_load_kpi.load_cube_base_table('''||p_kpi||''','''||p_dimset||''',''LIMIT CUBE'','||
866 '''null'',''null'','''||
867 l_base_table_stmt||''','''||l_start_lock_objects||''',''null'','''||p_load_program||''',''null'','''||l_min_stmt||''','''||
868 l_max_stmt||''','''||l_bt_current_period_stmt||''',''null'','||l_run_id||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
869 bsc_aw_utility.start_job(l_job_name,l_run_id,l_process,null);
870 end if;
871 end if;
872 --
873 bsc_aw_utility.wait_on_jobs(null,l_job_status);
874 for i in 1..l_job_status.count loop
875 if l_job_status(i).status='error' then
876 raise bsc_aw_utility.g_exception;
877 end if;
878 end loop;
879 --get the locks back
880 bsc_aw_management.get_lock(l_lock_object_cubes,'resync');
881 if l_lock_object_limit_cubes.count>0 then
882 bsc_aw_management.get_lock(l_lock_object_limit_cubes,'resync');
883 end if;
884 if l_lock_object_reset_cubes.count>0 then
885 bsc_aw_management.get_lock(l_lock_object_reset_cubes,'resync');
886 end if;
887 Exception when others then
888 log_n('Exception in load_kpi_dimset_base_table_job '||sqlerrm);
889 raise;
890 End;
891
892 /*
893 this procedure is called from a dbms job. this loads a measure given the base table.
894 p_base_table is a concat list of base tables ...b1,b2, format
895 p_measure can also be "LIMIT CUBE" which is to load limit cubes
896 p_min_value is in the format 1,2, etc. min value and max value match base tables
897 if p_parameter ='LIMIT CUBE' then p_cubes will contain the limit cubes
898 p_start_lock_objects : we lock them in the begining itself
899 p_end_lock_objects : these we lock only after executing the load.
900 */
901 procedure load_cube_base_table(
902 p_kpi varchar2,
903 p_dim_set varchar2,
904 p_parameter varchar2,
905 p_cubes varchar2,--c1,c2 format
906 p_measures varchar2,--m1,m2 format
907 p_base_table varchar2, --B1,B2,B3, format
908 p_start_lock_objects varchar2, --usually 1 cube c1, format
909 p_end_lock_objects varchar2, --in case of partitions, limit cubes
910 p_load_program varchar2,
911 p_LB_resync_program varchar2,
912 p_min_value varchar2,
913 p_max_value varchar2,
914 p_bt_current_period varchar2,
915 p_partition_options varchar2,
916 p_run_id number,p_job_name varchar2,p_options varchar2) is
917 --
918 l_cubes dbms_sql.varchar2_table;
919 l_measures dbms_sql.varchar2_table;
920 l_start_lock_objects dbms_sql.varchar2_table;
921 l_end_lock_objects dbms_sql.varchar2_table;
922 l_base_tables dbms_sql.varchar2_table;
923 l_stmt varchar2(4000);
924 l_min_values dbms_sql.varchar2_table;
925 l_max_values dbms_sql.varchar2_table;
926 l_bt_current_period dbms_sql.varchar2_table;
927 l_lock_objects dbms_sql.varchar2_table;
928 l_flag boolean;
929 l_ds_parameters dbms_sql.varchar2_table;
930 --
931 l_aggregation aggregation_r;
932 l_dim_set bsc_aw_adapter_kpi.dim_set_r;
933 l_partition_options varchar2(2000);
934 Begin
935 if p_run_id is not null then
936 --this is a dbms job. we have to do the initializations since this is a new session
937 bsc_aw_utility.g_options.delete;
938 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
939 bsc_aw_utility.open_file(p_job_name);
940 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
941 bsc_aw_utility.init_all_procedures;
942 if g_debug then
943 bsc_aw_utility.clean_stats('group.load_cube_base_table');
944 bsc_aw_utility.load_stats('Start Of Process. Load kpi dimset cube base table','group.load_cube_base_table');
945 end if;
946 end if;
947 --
948 bsc_aw_utility.parse_parameter_values(p_start_lock_objects,',',l_start_lock_objects);
949 if p_end_lock_objects <> 'null' then
950 bsc_aw_utility.parse_parameter_values(p_end_lock_objects,',',l_end_lock_objects);
951 end if;
952 if p_cubes<>'null' then
953 bsc_aw_utility.parse_parameter_values(p_cubes,',',l_cubes);
954 end if;
955 if p_measures<>'null' then
956 bsc_aw_utility.parse_parameter_values(p_measures,',',l_measures);
957 end if;
958 l_partition_options:=p_partition_options;
959 if l_partition_options='null' then
960 l_partition_options:=null;
961 end if;
962 bsc_aw_utility.parse_parameter_values(p_base_table,',',l_base_tables);
963 bsc_aw_utility.parse_parameter_values(p_min_value,',',l_min_values);
964 bsc_aw_utility.parse_parameter_values(p_max_value,',',l_max_values);
965 bsc_aw_utility.parse_parameter_values(p_bt_current_period,',',l_bt_current_period);
966 l_base_tables:=bsc_aw_utility.make_upper(l_base_tables);
967 --
968 --get the lock. if measure, on the cube. if LIMIT CUBE, on all the limit cubes. note that when its LIMIT CUBE
969 --l_cubes will contain the limit cubes
970 bsc_aw_management.get_workspace_lock(l_start_lock_objects,null);
971 --
972 l_flag:=false;
973 for i in 1..l_base_tables.count loop
974 if to_number(l_min_values(i))<=to_number(l_max_values(i)) then
975 insert_bsc_aw_temp_cv(to_number(l_min_values(i)),to_number(l_max_values(i)),l_base_tables(i));
976 l_flag:=true;
977 end if;
978 end loop;
979 --
980 set_aggregation(p_kpi,l_aggregation);
981 l_dim_set:=l_aggregation.dim_set(get_dim_set_index(l_aggregation,p_dim_set));
982 if p_parameter<>'LIMIT CUBE' then
983 /*see if we need to correct projection values or bal values with a change to current period in the base table */
984 check_bt_current_period_change(p_kpi,l_dim_set,l_cubes,l_measures,l_base_tables,l_bt_current_period,l_partition_options);
985 end if;
986 if l_flag then
987 /*set the cal end period rel for balance aggregation in the kpi load programs*/
988 limit_calendar_end_period_rel(l_dim_set.calendar);
989 get_ds_BT_parameters(p_kpi,p_dim_set,p_load_program,l_base_tables,l_ds_parameters);
990 if g_debug then
991 log('Before Load, Composite Counts');
992 dmp_dimset_composite_count(l_dim_set);
993 end if;
994 for i in 1..l_ds_parameters.count loop
995 --p_parameter contains cube and partition info
996 bsc_aw_dbms_aw.execute('call '||p_load_program||'('''||l_ds_parameters(i)||''','''||p_parameter||''')');
997 end loop;
998 if g_debug then
999 log('After Load, Composite Counts');
1000 dmp_dimset_composite_count(l_dim_set);
1001 end if;
1002 reset_calendar_end_period_rel(l_dim_set.calendar);
1003 end if;
1004 --get the lock on the end lock objects . must spin till lock is acquired. if not careful, wait can lead to deadlock. in this case,
1005 --all processes will start with the same order, say A then B then C. so the process to get A will get B and C. then others queue up for A.
1006 --once process 2 gets A, its guaranteed to get B and C. get_lock will also add the end lock objects to the global list, so commit_aw will
1007 --save then back to db
1008 --p_LB_resync_program is very important when we have partitions. without them, the LB will lose their information when we acquire the lock
1009 --in resync mode
1010 if l_end_lock_objects.count>0 then
1011 if p_LB_resync_program <> 'null' then
1012 bsc_aw_dbms_aw.execute('call '||p_LB_resync_program||'(''PRE'')');
1013 end if;
1014 bsc_aw_management.get_lock(l_end_lock_objects,'resync,wait type=active wait');
1015 if p_LB_resync_program <> 'null' then
1016 bsc_aw_dbms_aw.execute('call '||p_LB_resync_program||'(''POST'')');
1017 end if;
1018 end if;
1019 --
1020 --save the cubes back to database. also releases the lock
1021 bsc_aw_management.commit_aw;
1022 --
1023 if p_run_id is not null then
1024 bsc_aw_utility.send_pipe_message(p_job_name,'status=success');
1025 bsc_aw_management.detach_workspace;
1026 end if;
1027 commit;
1028 if p_run_id is not null and g_debug then
1029 bsc_aw_utility.load_stats('End Of Process. Load kpi dimset cube base table','group.load_cube_base_table');
1030 bsc_aw_utility.print_stats('group.load_cube_base_table');
1031 end if;
1032 Exception when others then
1033 log_n('Exception in load_cube_base_table '||sqlerrm);
1034 if p_run_id is not null then
1035 bsc_aw_utility.send_pipe_message(p_job_name,'status=error,sqlcode='||sqlcode||',message='||sqlerrm);
1036 rollback;
1037 bsc_aw_management.detach_workspace;
1038 else
1039 raise;
1040 end if;
1041 End;
1042
1043 function check_load_type(p_property varchar2) return varchar2 is
1044 l_cv_value number;
1045 Begin
1046 --ping the aw table for data
1047 l_cv_value:=to_number(bsc_aw_utility.get_parameter_value(p_property,'current change vector',','));
1048 if l_cv_value=0 then
1049 return 'initial';
1050 else
1051 return 'inc';
1052 end if;
1053 Exception when others then
1054 log_n('Exception in check_load_type '||sqlerrm);
1055 raise;
1056 End;
1057
1058 /*
1059 dbms job wrapper for aggregate_kpi_dimset
1060 */
1061 procedure aggregate_kpi_dimset_job(p_kpi varchar2,p_dim_set dbms_sql.varchar2_table) is
1062 --
1063 l_job_name varchar2(100);
1064 l_process varchar2(8000);
1065 l_job_status bsc_aw_utility.parallel_job_tb;
1066 Begin
1067 bsc_aw_utility.clean_up_jobs('all');
1068 for i in 1..p_dim_set.count loop
1069 l_job_name:='bsc_aw_aggregate_kpi_dimset_'||bsc_aw_utility.get_dbms_time||'_'||i;
1070 l_process:='bsc_aw_load_kpi.aggregate_kpi_dimset('''||p_kpi||''','''||p_dim_set(i)||''','||i||','''||l_job_name||''','''||
1071 bsc_aw_utility.get_option_string||''');';
1072 bsc_aw_utility.start_job(l_job_name,i,l_process,null);
1073 end loop;
1074 bsc_aw_utility.wait_on_jobs(null,l_job_status);
1075 for i in 1..l_job_status.count loop
1076 if l_job_status(i).status='error' then
1077 raise bsc_aw_utility.g_exception;
1078 end if;
1079 end loop;
1080 Exception when others then
1081 log_n('Exception in aggregate_kpi_dimset_job '||sqlerrm);
1082 raise;
1083 End;
1084
1085 /*
1086 given a kpi and dimset, aggregate the cubes involved. just a wrapper for
1087 procedure aggregate_kpi_dimset(p_aggregation aggregation_r)
1088 */
1089 procedure aggregate_kpi_dimset(p_kpi varchar2,p_dim_set varchar2,
1090 p_run_id number,p_job_name varchar2,p_options varchar2) is
1091 l_aggregation aggregation_r;
1092 l_dimset_index number;
1093 Begin
1094 if p_run_id is not null then
1095 --this is a dbms job. we have to do the initializations since this is a new session
1096 bsc_aw_utility.g_options.delete;
1097 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
1098 bsc_aw_utility.open_file('Aggregate_KD_'||p_dim_set||'_'||p_run_id);
1099 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
1100 bsc_aw_utility.init_all_procedures;
1101 end if;
1102 --l_aggregation has info on all dimsets for the kpi
1103 set_aggregation(p_kpi,l_aggregation);
1104 --find the actual and target pairs
1105 l_dimset_index:=0;
1106 l_dimset_index:=get_dim_set_index(l_aggregation,p_dim_set);
1107 aggregate_kpi_dimset(p_kpi,l_aggregation,l_aggregation.dim_set(l_dimset_index));
1108 if p_run_id is not null then
1109 bsc_aw_utility.send_pipe_message(p_job_name,'status=success');
1110 bsc_aw_management.detach_workspace;
1111 end if;
1112 commit;
1113 Exception when others then
1114 log_n('Exception in aggregate_kpi_dimset '||sqlerrm);
1115 if p_run_id is not null then
1116 bsc_aw_utility.send_pipe_message(p_job_name,'status=error,sqlcode='||sqlcode||',message='||sqlerrm);
1117 rollback;
1118 bsc_aw_management.detach_workspace;
1119 else
1120 raise;
1121 end if;
1122 End;
1123
1124 /*
1125 this procedure looks at the list of dimsets and then eliminates targets when corresponding actuals exist
1126 when there is a pair, aggregate only the actual. aggregating actuals will aggregate targets also
1127 then we aggregate standalone targets
1128 */
1129 procedure get_aggregate_dimsets(
1130 p_kpi varchar2,
1131 p_dim_set dbms_sql.varchar2_table,
1132 p_aggregate_dimset out nocopy dbms_sql.varchar2_table
1133 ) is
1134 l_aggregation aggregation_r;
1135 l_agg_flag dbms_sql.varchar2_table;--will be used to eliminate "target" dimsets that have "actual" dimsets aggregating
1136 Begin
1137 --l_aggregation has info on all dimsets for the kpi
1138 set_aggregation(p_kpi,l_aggregation);
1139 --find the actual and target pairs
1140 for i in 1..l_aggregation.dim_set.count loop
1141 l_agg_flag(i):='N';
1142 if bsc_aw_utility.in_array(p_dim_set,l_aggregation.dim_set(i).dim_set_name) then
1143 --see if the dimset does not need agg (if pre-calc)
1144 if l_aggregation.dim_set(i).pre_calculated='N' then
1145 l_agg_flag(i):='Y';
1146 else
1147 if g_debug then
1148 log('Dimset '||p_dim_set(i)||' is pre calculated');
1149 end if;
1150 end if;
1151 end if;
1152 end loop;
1153 for i in 1..l_aggregation.dim_set.count loop
1154 if l_aggregation.dim_set(i).dim_set_type='actual' and l_agg_flag(i)='Y' then
1155 --see if there is a target too
1156 for j in 1..l_aggregation.dim_set.count loop
1157 if l_aggregation.dim_set(j).dim_set_type='target' and l_aggregation.dim_set(j).base_dim_set=l_aggregation.dim_set(i).dim_set_name then
1158 l_agg_flag(j):='N';
1159 exit;
1160 end if;
1161 end loop;
1162 end if;
1163 end loop;
1164 for i in 1..l_aggregation.dim_set.count loop
1165 if l_agg_flag(i)='Y' then
1166 p_aggregate_dimset(p_aggregate_dimset.count+1):=l_aggregation.dim_set(i).dim_set_name;
1167 end if;
1168 end loop;
1169 Exception when others then
1170 log_n('Exception in get_aggregate_dimsets '||sqlerrm);
1171 raise;
1172 End;
1173
1174 /*
1175 This procedure is only called as a part of the kpi dimset load
1176 its also called when base tables are loaded. with B tables, we find out the affected
1177 dimsets and aggregate them
1178 logic
1179 for the dim set get the dim
1180 for the dim get the limit cubes
1181 find out the levels of the dim for this dim set
1182 get the level positions. limit levels to within adv sum profile.
1183 see if there is zero code. for the levels with zero code, get the zero code level name
1184
1185 look at adv sum profile
1186 limit the dim to the limit cube
1187 for time, limit to all applicable periodicities
1188 for rec dim, call api to find out the levels to limit to. for now, aggregate all levels
1189 get the cubes.
1190 look at the agg formula. if its simple, we can aggregate it
1191 if its a formula, we need to execute the formula after we aggregate the cubes
1192
1193 if there are targets at higher levels we do
1194 1. load the dimset
1195 2. aggregate the dimset
1196 3. load the targets
1197 4. aggregate the dimset
1198
1199 after each agg, the limit cubes are reset
1200
1201 read the measures of the dimset.
1202 in 9i, we aggregate all measures together
1203 1. we aggregate normal measures
1204 2. we aggregate balance measures
1205 3. we aggregate measures with formula. this is one by one
1206
1207 In 10g, we can aggregate cubes in parallel
1208 in 10g, we can launch aggregate_measure in parallel
1209
1210 if the measure is balance, we dont aggregate it with other measures, we use the agg_map_notime to aggregate
1211 balance measures
1212
1213 if aggregation is complex with formula, then we cannot launch the aggregaqtion of the measure in paralle because
1214 this measure needs the other measures to be aggregated before it can execute the formula
1215 this means when we set the dim status, we have to set the status to the higher levels and then aggregate
1216
1217 forecast or projections are handled like this:
1218 each kpi has default dim "projection". when we aggregate we have projection dim as Y and N.
1219 then we limit the dim to Y. then for each periodicity, for those periods where there is a mix of
1220 real and projected data, we make the cube data=0
1221 look at correct_forecast_aggregation
1222
1223 if there are measures with average, we aggergate them separately from measures with sum or other agg functions
1224
1225 please note that the agg maps have opvar and argvar. we aggregate sum, average measures together
1226
1227 if dimset=actual then
1228 aggregate actuals
1229 aggregate targets of corresponding target dimset
1230 if dimset=target then
1231 aggregate targets of corresponding target dimset
1232
1233 "aggregate targets of corresponding target dimset" has both the copy from targets to actuals and the aggregation of actuals
1234 */
1235 procedure aggregate_kpi_dimset(
1236 p_kpi varchar2,
1237 p_aggregation aggregation_r,
1238 p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
1239 --
1240 l_dim_set bsc_aw_adapter_kpi.dim_set_r;
1241 Begin
1242 --get the full dimset lock
1243 lock_dimset_objects(p_kpi,p_dim_set.dim_set_name,null,null);
1244 if p_dim_set.dim_set_type='actual' then
1245 --get corresponding target dimset
1246 l_dim_set.dim_set_name:=null;
1247 for i in 1..p_aggregation.dim_set.count loop
1248 if p_aggregation.dim_set(i).dim_set_type='target' and p_aggregation.dim_set(i).base_dim_set=p_dim_set.dim_set_name then
1249 l_dim_set:=p_aggregation.dim_set(i);
1250 exit;
1251 end if;
1252 end loop;
1253 aggregate_kpi_dimset_actuals(p_kpi,p_dim_set);
1254 if l_dim_set.dim_set_name is not null then
1255 --aggregate targets only if there are targets
1256 --p_dim_set=actual, l_dim_set=target
1257 --lock the target limit cubes only!!
1258 lock_dimset_objects(p_kpi,l_dim_set.dim_set_name,'dim limit cube','inc');
1259 aggregate_kpi_dimset_targets(p_kpi,p_dim_set,l_dim_set);
1260 end if;
1261 elsif p_dim_set.dim_set_type='target' then
1262 --get corresponding actual dimset
1263 l_dim_set.dim_set_name:=null;
1264 for i in 1..p_aggregation.dim_set.count loop
1265 if p_aggregation.dim_set(i).dim_set_type='actual' and p_aggregation.dim_set(i).dim_set_name=p_dim_set.base_dim_set then
1266 l_dim_set:=p_aggregation.dim_set(i);
1267 exit;
1268 end if;
1269 end loop;
1270 --l_dim_set=actual, p_dim_set=target
1271 --if l_dim_set.dim_set_name is null, its an error, every target must have an actuals
1272 aggregate_kpi_dimset_targets(p_kpi,l_dim_set,p_dim_set);
1273 end if;
1274 --set status to allstat in reset_dim_limits. in 10g parallelism, the dim status here is not what we need.
1275 --so best to set the status to allstat. N: reset_dim_limits sets status to allstat!!
1276 --NOTE!!! reset_dim_limits is permanently changing the limit cubes
1277 reset_dim_limits(p_dim_set);
1278 if l_dim_set.dim_set_name is not null then
1279 reset_dim_limits(l_dim_set);
1280 end if;
1281 bsc_aw_management.commit_aw;
1282 commit;
1283 Exception when others then
1284 log_n('Exception in aggregate_kpi_dimset '||sqlerrm);
1285 raise;
1286 End;
1287
1288 /*
1289 this procedure is used to aggregate actuals cubes
1290 N:
1291 assumption>>>> when we enter this procedure, all changes have been updated and commited
1292 the process before this is load. load will update and commit for every dimset/base table combination
1293 whether called as a job or in the same session
1294 must be very careful. commit_aw releases locks. so may have to call commit_aw('no release lock') option we have to
1295 do a commit here
1296 */
1297 procedure aggregate_kpi_dimset_actuals(p_kpi varchar2,p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
1298 --
1299 l_measures dbms_sql.varchar2_table;
1300 l_parallel boolean;
1301 l_aggregate_option varchar2(2000);
1302 Begin
1303 --we aggregate all measures that are not BALANCE aggregation
1304 if g_debug then
1305 log_n('=====================================');
1306 log('aggregate_kpi_dimset_actuals '||p_dim_set.dim_set_name);
1307 log('=====================================');
1308 end if;
1309 --
1310 --limit the dim for aggregate_measure and aggregate_measure_formula
1311 --so these 2 api do not have to call limit of dim
1312 --aggregate_measure_job will launch jobs to aggregate the cubes. there are 2 pre-reqs
1313 --1. dim limit cubes are saved back to the database
1314 --2. the cubes are saved back to the database
1315 --at this point, we come from either load or aggregate targets. so the limit cubes and cubes are in saved state
1316 --N: the above is the assumption!!
1317 --N:when we have compressed composites, we cannot limit dim status before aggregations. also we cannot copy data into the higher
1318 --levels. this means we cannot have targets at higher levels when we have compressed composites
1319 limit_all_dim(p_dim_set);
1320 --
1321 /*new logic
1322 if there are non bal measures and bal measures then
1323 first, aggregate non bal and bal measures on notime
1324 then aggregate non bal on only time
1325 elsif there is only non bal then
1326 aggregate on dimset agg map
1327 elsif there is only bal then
1328 this is covered by first if itself
1329 end if
1330 this is 25 percent faster than old method of aggregating first the non bal on all dim and then bal mesures on notime
1331 if we take 2 years of data, only 730 distinct days can exist. an equal or more combinations belong to other dimensions. so one time
1332 aggregation on other dim for all measures is faster
1333 another approach using parallelism is to have bal measures on separate composite so bal and non bal measures can be aggregated in parallel
1334 however, this is not useful because launching 8 processes on 4 cpu box is no point. aggregation is cpu intensive. it doubles storage too
1335 --
1336 the best way would have been if we could aggregate bal and non bal measures in one shot. when the aggregation is on time, measurename dim
1337 must be limited to non bal measures. for other dim, to bal and non bal measures. tried having a diff opvar for time with entry for bal measures
1338 null. aw simply assumed SUM aggregation. so did not work. then tried placing value like NONE for bal measures. aw nulled out higher values
1339 we cannot use precompute or valueset because both can only constrain time values, not values of another dim
1340 */
1341 l_measures.delete;
1342 for i in 1..p_dim_set.measure.count loop
1343 if p_dim_set.measure(i).agg_formula.std_aggregation='Y' then
1344 l_measures(l_measures.count+1):=p_dim_set.measure(i).measure;
1345 end if;
1346 end loop;
1347 if dimset_has_bal_measures(p_dim_set) then
1348 l_aggregate_option:='notime';
1349 else
1350 l_aggregate_option:=null;
1351 end if;
1352 --aggregate these measures that are like sum etc
1353 if l_measures.count>0 then
1354 l_parallel:=false;
1355 if can_launch_jobs(p_kpi,p_dim_set,l_measures)='Y' then
1356 l_parallel:=true;
1357 end if;
1358 /*check to see if there is sufficient load to warrant parallel load .
1359 earlier we had is_parallel_aggregate test here. we cannot do this. when we aggregate, 100 nodes can explode into 100000 nodes
1360 depending on the hier depths. this means we cannot disable parallel aggregation just because there are onlyu 100 nodes. load could
1361 bring in 100 nodes. the only time we can do is_parallel_aggregate test is for formula and target copy because the nodes are
1362 not increasing in these cases
1363 */
1364 if l_parallel=false then
1365 aggregate_measure(p_kpi,p_dim_set,l_measures,l_aggregate_option);
1366 else
1367 aggregate_measure_job(p_kpi,p_dim_set,l_measures,l_aggregate_option,'normal');
1368 end if;
1369 end if;
1370 ----------------
1371 /*now we aggregate non balance measures on time alone. we do this if the dimset needs to aggregate on time
1372 if the dimset is partitioned at the lowest time, there is no agg on time*/
1373 if bsc_aw_adapter_kpi.is_calendar_aggregated(p_dim_set.calendar) then
1374 l_measures.delete;
1375 if dimset_has_bal_measures(p_dim_set) then
1376 for i in 1..p_dim_set.measure.count loop
1377 if p_dim_set.measure(i).agg_formula.std_aggregation='Y' and p_dim_set.measure(i).measure_type='NORMAL' then
1378 l_measures(l_measures.count+1):=p_dim_set.measure(i).measure;
1379 end if;
1380 end loop;
1381 end if;
1382 if l_measures.count>0 then
1383 l_aggregate_option:='onlytime';
1384 --aggregate these measures that are non BALANCE
1385 --aggregate_measure will first aggregate on all dim except time then aggregate on time for non bal measures
1386 --we will check each time l_parallel because some other load may have launched many jobs.
1387 l_parallel:=false;
1388 if can_launch_jobs(p_kpi,p_dim_set,l_measures)='Y' then
1389 l_parallel:=true;
1390 end if;
1391 /*earlier we had is_parallel_aggregate test here. we cannot do this */
1392 if l_parallel=false then
1393 aggregate_measure(p_kpi,p_dim_set,l_measures,l_aggregate_option);
1394 else
1395 aggregate_measure_job(p_kpi,p_dim_set,l_measures,l_aggregate_option,'onlytime');
1396 end if;
1397 end if;
1398 else
1399 if g_debug then
1400 log('Aggregation not specified on time');
1401 end if;
1402 end if;
1403 -----------------
1404 --now aggregate the formulas
1405 l_measures.delete;
1406 for i in 1..p_dim_set.measure.count loop
1407 if p_dim_set.measure(i).agg_formula.std_aggregation='N' and p_dim_set.measure(i).sql_aggregated='N' then
1408 l_measures(l_measures.count+1):=p_dim_set.measure(i).measure;
1409 end if;
1410 end loop;
1411 l_parallel:=false;
1412 if can_launch_jobs(p_kpi,p_dim_set,l_measures)='Y' then
1413 l_parallel:=true;
1414 end if;
1415 if l_measures.count>0 then
1416 if l_parallel and p_dim_set.compressed='N' then
1417 /*cannot do is_parallel_aggregate test for CC,comp count is compressed node count*/
1418 if is_parallel_aggregate(p_dim_set,bsc_aw_utility.g_parallel_aggregate_cutoff)=false then
1419 if g_debug then
1420 log('Due to insufficient load, Parallel aggregate for dimset measures made serial');
1421 end if;
1422 l_parallel:=false;
1423 end if;
1424 end if;
1425 if l_parallel=false then
1426 aggregate_measure_formula(p_kpi,p_dim_set,l_measures,null);
1427 else
1428 aggregate_measure_job(p_kpi,p_dim_set,l_measures,null,'formula');
1429 end if;
1430 end if;
1431 Exception when others then
1432 log_n('Exception in aggregate_kpi_dimset_actuals '||sqlerrm);
1433 raise;
1434 End;
1435
1436 /*
1437 this procedure is used to aggregate actuals cubes
1438 procedure called when there is targets at higher levels
1439 logic:
1440 limit all dim to bool status
1441 limit all dim add ancestors
1442 limit all dim add target bool status
1443 set limit variables to false
1444 --
1445 limit all dim -> keep only level of target
1446 set limit.bool to true by looping across target composite (take any 1 measure)
1447 limit dim to limit.bool
1448 copy data from target to actuals, across target composite
1449 aggregate again
1450
1451 time: targets must have the same periodicity as dim set
1452 */
1453 procedure aggregate_kpi_dimset_targets(
1454 p_kpi varchar2,
1455 p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
1456 p_target_dim_set bsc_aw_adapter_kpi.dim_set_r
1457 ) is
1458 --
1459 l_dim_set_reagg bsc_aw_adapter_kpi.dim_set_r;
1460 l_parallel boolean;
1461 l_measures dbms_sql.varchar2_table;
1462 l_lock_object_limit_cubes dbms_sql.varchar2_table;
1463 Begin
1464 if g_debug then
1465 log_n('=====================================');
1466 log('aggregate_kpi_dimset_targets');
1467 log('=====================================');
1468 end if;
1469 --
1470 --N:we cannot have compressed composites and targets at higher levels since targets need to copy data into the higher
1471 --levels.
1472 limit_all_dim(p_actual_dim_set);
1473 --
1474 limit_dim_ancestors(p_actual_dim_set.dim,'ADD');
1475 limit_calendar_ancestors(p_actual_dim_set.calendar,'ADD');
1476 --
1477 limit_dim_values(p_target_dim_set.dim,'ADD');--note>> targets abd actuals have the same dim, diff levels
1478 limit_dim_values(p_target_dim_set.std_dim,'ADD');
1479 limit_calendar_values(p_target_dim_set.calendar,'ADD');
1480 --
1481 --the status of dim is actuals + targets.
1482 --limit all dim -> keep only level of target. this does NOT limit TIME
1483 limit_dim_target_level_only(p_actual_dim_set,p_target_dim_set);
1484 --
1485 --set limit.bool to true by looping across target composite (take any 1 measure)
1486 --to get the target composite name, we can simply take 1 measure. even in 10g, we need to consider
1487 --only 1 measure. so we look at measure(1)
1488 --earlier: limit_dim_limit_cube(p_target_dim_set.dim,'TRUE',p_target_dim_set.measure(1).composite_name);
1489 limit_dim_limit_cube(p_target_dim_set.dim,'TRUE');
1490 limit_dim_limit_cube(p_target_dim_set.std_dim,'TRUE');
1491 limit_dim_limit_cube(p_target_dim_set.calendar.limit_cube,'TRUE',p_target_dim_set.calendar.limit_cube_composite);
1492 --
1493 l_measures.delete;
1494 for i in 1..p_actual_dim_set.measure.count loop
1495 l_measures(l_measures.count+1):=p_actual_dim_set.measure(i).measure;
1496 end loop;
1497 --
1498 l_parallel:=false;
1499 if can_launch_jobs(p_kpi,p_actual_dim_set,l_measures)='Y' then
1500 l_parallel:=true;
1501 end if;
1502 if l_parallel and p_target_dim_set.compressed='N' then
1503 if is_parallel_aggregate(p_target_dim_set,bsc_aw_utility.g_parallel_target_cutoff)=false then
1504 if g_debug then
1505 log('Due to insufficient load, Parallel copy from Target to Actuals made serial');
1506 end if;
1507 l_parallel:=false;
1508 end if;
1509 end if;
1510 if l_parallel=false then
1511 copy_target_to_actual_serial(p_actual_dim_set,p_target_dim_set,l_measures);
1512 else
1513 --even if there is one cube and the cube is partitioned, can_launch_jobs will return Y
1514 copy_target_to_actual_job(p_kpi,p_actual_dim_set,p_target_dim_set,l_measures);
1515 end if;
1516 --aggregate again
1517 /*
1518 once we copy the targets and we are ready to re-aggregate, we have to be careful. we cannot do
1519 aggregate_kpi_dimset_actuals(p_kpi,p_actual_dim_set);
1520 this is because the levels of p_actual_dim_set are lower than the targets. so we should do
1521 aggregate_kpi_dimset_actuals(p_kpi,target_dim_set);
1522 but this also does not work since the measures in target_dim_set is not the ones we are aggregating.
1523 so we create a new l_dim_set_reagg. we place calendar and dim from targets and rest from actuals and aggregate it
1524 */
1525 /*
1526 we need to solve a complex issue here. say we have a hier change in the upper levels. say the dim are
1527 component > product > prod family, release and geog (city > state > country > region) now, the target is at prod, release
1528 city. there is a change to country > region hier. agg of the actuals will null out target data in the following levels
1529 prod release country
1530 fam release country
1531 prod release region
1532 fam release region
1533 this is because, data tries to aggregate for actuals from component level. here, there is no data for targets. so the aggregates
1534 get null. the copy in the code above will not bring in the data since grog is at country level. there is target data at
1535 prod release state level. we need to reaggregate this data. now, the limit cubes not only reflect the hier changes but also
1536 the inc data.so to keep symmetey, we will drill the dim down to descendents and then keep the target level. then re-aggregate the
1537 target data
1538 N:we assume that the actuals limit cubes are intact and have not been reset
1539 */
1540 --===
1541 limit_all_dim(p_actual_dim_set);
1542 limit_dim_descendents(p_actual_dim_set.dim,'ADD','DESCENDANTS');
1543 limit_calendar_descendents(p_actual_dim_set.calendar,'ADD','DESCENDANTS');
1544 limit_dim_target_level_only(p_actual_dim_set,p_target_dim_set); --does not limit TIME
1545 /*here there is no more agg for balance. balance agg has already happened in the kpi load programs
1546 both for actuals and targets. so we can limit time also to the level of target */
1547 limit_cal_target_level_only(p_actual_dim_set,p_target_dim_set);
1548 limit_dim_limit_cube(p_target_dim_set.dim,'TRUE');
1549 limit_dim_limit_cube(p_target_dim_set.std_dim,'TRUE');
1550 limit_dim_limit_cube(p_target_dim_set.calendar.limit_cube,'TRUE',p_target_dim_set.calendar.limit_cube_composite);
1551 get_dimset_objects_to_lock(p_kpi,p_target_dim_set.dim_set_name,'dim limit cube',l_lock_object_limit_cubes);
1552 bsc_aw_management.commit_aw(l_lock_object_limit_cubes,'no release lock');
1553 --===
1554 l_dim_set_reagg:=p_actual_dim_set;
1555 l_dim_set_reagg.dim:=p_target_dim_set.dim;
1556 l_dim_set_reagg.std_dim:=p_target_dim_set.std_dim;
1557 l_dim_set_reagg.calendar:=p_target_dim_set.calendar; --we need the target limit cubes, periodicities
1558 l_dim_set_reagg.calendar.agg_map:=p_actual_dim_set.calendar.agg_map;/*copy info missing in target calendar */
1559 --
1560 aggregate_kpi_dimset_actuals(p_kpi,l_dim_set_reagg);
1561 --
1562 Exception when others then
1563 log_n('Exception in aggregate_kpi_dimset_targets '||sqlerrm);
1564 raise;
1565 End;
1566
1567 /*
1568 copy in serial or non parallel mode
1569 this converts measures to cubes and calls copy_target_to_actual
1570 */
1571 procedure copy_target_to_actual_serial(
1572 p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
1573 p_target_dim_set bsc_aw_adapter_kpi.dim_set_r,
1574 p_actual_measures dbms_sql.varchar2_table) is
1575 --
1576 l_cubes dbms_sql.varchar2_table;
1577 Begin
1578 get_cubes_for_measures(p_actual_measures,p_actual_dim_set,l_cubes);
1579 copy_target_to_actual(p_actual_dim_set,p_target_dim_set,l_cubes,null,null);
1580 Exception when others then
1581 log_n('Exception in copy_target_to_actual_serial '||sqlerrm);
1582 raise;
1583 End;
1584
1585 /*
1586 we commit the target limit cubes.
1587 the actual cubes are in a saved state. aggregate_kpi_dimset_actuals would have saved the cubes
1588 N: when copying targets to actuals, we cannot parallelize by partitions since target and actuals
1589 are in diff partition templates we can only parallelize by cubes
1590 when there are targets, the actuals cannot be partitioned at all. only the targets can be partitioned
1591 --
1592 the above is not true anymore. with hash partitions on time, we can partition when there are targets as long as actuals and targets have the
1593 same periodicity. this way, data at a particular period is guaranteed to be in P.x for both actuals and targets. this is true even when there
1594 are balance (end period) and the load has agg bal measure. we need to launch jobs for cubes+partitions
1595 */
1596 procedure copy_target_to_actual_job(
1597 p_kpi varchar2,
1598 p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
1599 p_target_dim_set bsc_aw_adapter_kpi.dim_set_r,
1600 p_actual_measures dbms_sql.varchar2_table
1601 ) is
1602 --
1603 l_lock_cubes dbms_sql.varchar2_table;
1604 --
1605 l_job_name varchar2(100);
1606 l_process varchar2(8000);
1607 l_job_status bsc_aw_utility.parallel_job_tb;
1608 --
1609 l_cubes dbms_sql.varchar2_table;
1610 pt_comp dbms_sql.varchar2_table;/*for each l_cubes */
1611 pt_comp_type dbms_sql.varchar2_table;
1612 agg_cubes dbms_sql.varchar2_table;
1613 agg_pt_comp dbms_sql.varchar2_table;
1614 cube_pt bsc_aw_adapter_kpi.partition_template_r;
1615 aggregate_options varchar2(2000);
1616 l_run_id number;
1617 Begin
1618 bsc_aw_management.save_lock_set('target copy');
1619 bsc_aw_management.commit_lock_set('target copy','no release lock');
1620 --release locks on the actual cubes
1621 l_lock_cubes.delete;
1622 get_measure_objects_to_lock(p_actual_dim_set,p_actual_measures,l_lock_cubes);
1623 bsc_aw_management.release_lock(l_lock_cubes); --release lock on the cube and countvar cube. locks still exist on LB
1624 --
1625 get_cubes_for_measures(p_actual_measures,p_actual_dim_set,l_cubes);
1626 --
1627 /*we find the distinct pt_comp. then for each, partitions. then launch as many jobs */
1628 for i in 1..l_cubes.count loop
1629 pt_comp(i):=null;
1630 pt_comp_type(i):=null;
1631 pt_comp(i):=bsc_aw_adapter_kpi.get_cube_pt_comp(l_cubes(i),p_actual_dim_set,pt_comp_type(i));/*l_cubes are actuals cubes */
1632 end loop;
1633 /* */
1634 l_run_id:=0;
1635 aggregate_options:=null;
1636 bsc_aw_utility.clean_up_jobs('all');
1637 agg_cubes.delete;
1638 for i in 1..pt_comp.count loop /*first all cubes that do not have composite or PT */
1639 if pt_comp(i) is null then
1640 agg_cubes(agg_cubes.count+1):=l_cubes(i);
1641 end if;
1642 end loop;
1643 if agg_cubes.count>0 then
1644 l_run_id:=l_run_id+1;
1645 l_job_name:='bsc_aw_copy_target_actual_'||bsc_aw_utility.get_dbms_time||'_'||l_run_id;
1646 l_process:='bsc_aw_load_kpi.copy_target_to_actual_job('''||p_kpi||''','''||p_actual_dim_set.dim_set_name||''','''||
1647 p_target_dim_set.dim_set_name||''','''||bsc_aw_utility.make_string_from_list(agg_cubes)||''',''null'','||
1648 l_run_id||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
1649 bsc_aw_utility.start_job(l_job_name,l_run_id,l_process,null);
1650 end if;
1651 for i in 1..pt_comp.count loop
1652 if pt_comp(i) is not null then
1653 bsc_aw_utility.merge_value(agg_pt_comp,pt_comp(i));
1654 end if;
1655 end loop;
1656 if agg_pt_comp.count>0 then
1657 for i in 1..agg_pt_comp.count loop
1658 agg_cubes.delete;
1659 for j in 1..pt_comp.count loop
1660 if agg_pt_comp(i)=pt_comp(j) then
1661 agg_cubes(agg_cubes.count+1):=l_cubes(j);
1662 end if;
1663 end loop;
1664 if agg_cubes.count>0 then
1665 aggregate_options:=null;
1666 cube_pt:=bsc_aw_adapter_kpi.get_partition_template_r(agg_pt_comp(i),p_actual_dim_set);
1667 if cube_pt.template_name is not null then /*this is a PT */
1668 for j in 1..cube_pt.template_partitions.count loop
1669 aggregate_options:='partition='||cube_pt.template_partitions(j).partition_name||',partition dim value='||
1670 cube_pt.template_partitions(j).partition_dim_value;
1671 l_run_id:=l_run_id+1;
1672 l_job_name:='bsc_aw_copy_target_actual_PT_'||bsc_aw_utility.get_dbms_time||'_'||l_run_id;
1673 l_process:='bsc_aw_load_kpi.copy_target_to_actual_job('''||p_kpi||''','''||p_actual_dim_set.dim_set_name||''','''||
1674 p_target_dim_set.dim_set_name||''','''||bsc_aw_utility.make_string_from_list(agg_cubes)||''','''||aggregate_options||''','||
1675 l_run_id||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
1676 bsc_aw_utility.start_job(l_job_name,l_run_id,l_process,null);
1677 end loop;
1678 else /*this is a composite */
1679 l_run_id:=l_run_id+1;
1680 l_job_name:='bsc_aw_copy_target_actual_'||bsc_aw_utility.get_dbms_time||'_'||l_run_id;
1681 l_process:='bsc_aw_load_kpi.copy_target_to_actual_job('''||p_kpi||''','''||p_actual_dim_set.dim_set_name||''','''||
1682 p_target_dim_set.dim_set_name||''','''||bsc_aw_utility.make_string_from_list(agg_cubes)||''',''null'','||
1683 l_run_id||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
1684 bsc_aw_utility.start_job(l_job_name,l_run_id,l_process,null);
1685 end if;
1686 end if;
1687 end loop;
1688 end if;
1689 bsc_aw_utility.wait_on_jobs(null,l_job_status);
1690 for i in 1..l_job_status.count loop
1691 if l_job_status(i).status='error' then
1692 raise bsc_aw_utility.g_exception;
1693 end if;
1694 end loop;
1695 /*we will detach and attach the workspace here to prevent errors due to resync*/
1696 bsc_aw_management.detach_workspace;
1697 bsc_aw_management.lock_lock_set('target copy',null);/*gets lock on LB and cubes. resync will be done is needed
1698 this will also attach the workspace back*/
1699 Exception when others then
1700 log_n('Exception in copy_target_to_actual_job '||sqlerrm);
1701 raise;
1702 End;
1703
1704 /* this procedure is only launched as a job. this is just a wrapper for copy_target_to_actual*/
1705 procedure copy_target_to_actual_job(
1706 p_kpi varchar2,
1707 p_actual_dimset varchar2,
1708 p_target_dimset varchar2,
1709 p_cubes varchar2,
1710 p_aggregate_options varchar2,/*all p_actual_cubes must have the same partition value*/
1711 p_run_id number,p_job_name varchar2,p_options varchar2) is
1712 --
1713 l_aggregation aggregation_r;
1714 l_actual_dimset bsc_aw_adapter_kpi.dim_set_r;
1715 l_target_dimset bsc_aw_adapter_kpi.dim_set_r;
1716 l_cubes dbms_sql.varchar2_table;
1717 l_lock_objects dbms_sql.varchar2_table;
1718 partition_value varchar2(40);
1719 partition_dim_value varchar2(40);
1720 Begin
1721 if p_run_id is not null then
1722 --this is a dbms job. we have to do the initializations since this is a new session
1723 bsc_aw_utility.g_options.delete;
1724 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
1725 bsc_aw_utility.open_file('Copy_TA_'||p_target_dimset||'_'||p_run_id);
1726 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
1727 bsc_aw_utility.init_all_procedures;
1728 end if;
1729 partition_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition',',');/*P.0, P.1 etc */
1730 partition_dim_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition dim value',','); /*1, 2 etc */
1731 bsc_aw_utility.parse_parameter_values(p_cubes,',',l_cubes);
1732 set_aggregation(p_kpi,l_aggregation);
1733 l_actual_dimset:=l_aggregation.dim_set(get_dim_set_index(l_aggregation,p_actual_dimset));
1734 l_target_dimset:=l_aggregation.dim_set(get_dim_set_index(l_aggregation,p_target_dimset));
1735 --get locks
1736 l_lock_objects:=l_cubes;
1737 if partition_value is not null then
1738 for i in 1..l_lock_objects.count loop
1739 l_lock_objects(i):=l_lock_objects(i)||'(partition '||partition_value||')';
1740 end loop;
1741 end if;
1742 bsc_aw_management.get_workspace_lock(l_lock_objects,null);
1743 --
1744 copy_target_to_actual(l_actual_dimset,l_target_dimset,l_cubes,partition_value,partition_dim_value);
1745 --save the cubes back to database
1746 bsc_aw_management.commit_aw(l_lock_objects);
1747 --
1748 if p_run_id is not null then
1749 bsc_aw_utility.send_pipe_message(p_job_name,'status=success');
1750 bsc_aw_management.detach_workspace;--release the lock
1751 end if;
1752 commit;
1753 Exception when others then
1754 log_n('Exception in copy_target_to_actual_job '||sqlerrm);
1755 if p_run_id is not null then
1756 bsc_aw_utility.send_pipe_message(p_job_name,'status=error,sqlcode='||sqlcode||',message='||sqlerrm);
1757 rollback;
1758 bsc_aw_management.detach_workspace; --this will release the locks
1759 else
1760 raise;
1761 end if;
1762 End;
1763
1764 /*
1765 copy_target_to_actual for serial processing
1766 copy_target_to_actual_job for parallel processing
1767 we pass a list of actual cubes here. from the cubes, we find the measures and then from the measures, we find the target cube
1768 N:we assume that if in actuals, measures m1,m2,m3 belong to c1, then for targets, the 3 measures belong to a corresponding
1769 cube, say c1.tgt
1770 when there are targets, actuals cannot have partitions!!
1771 */
1772 procedure copy_target_to_actual(
1773 p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
1774 p_target_dim_set bsc_aw_adapter_kpi.dim_set_r,
1775 p_actual_cubes dbms_sql.varchar2_table,
1776 p_partition_value varchar2,
1777 p_partition_dim_value varchar2
1778 ) is
1779 --
1780 l_pt_comp varchar2(200);
1781 l_pt_comp_type varchar2(200);
1782 l_measures bsc_aw_adapter_kpi.measure_tb;
1783 l_target_cube bsc_aw_adapter_kpi.cube_r;
1784 Begin
1785 if g_debug then
1786 bsc_aw_utility.clean_stats('group.copy_target_to_actual');
1787 bsc_aw_utility.load_stats('Start Of Process. Copy Target to Actual','group.copy_target_to_actual');
1788 end if;
1789 /* */
1790 push_dim(p_target_dim_set.dim);
1791 push_dim(p_target_dim_set.std_dim);
1792 push_dim(p_target_dim_set.calendar.aw_dim);
1793 /* */
1794 limit_dim_values(p_target_dim_set.dim,'to');
1795 limit_dim_values(p_target_dim_set.std_dim,'to');
1796 limit_calendar_values(p_target_dim_set.calendar,'to');
1797 if p_partition_value is not null then /*p_partition_value P.0 etc */
1798 push_dim(p_actual_dim_set.partition_dim);
1799 limit_dim(p_actual_dim_set.partition_dim,p_partition_dim_value,'TO');
1800 end if;
1801 /* */
1802 if g_debug then
1803 dmp_dimset_dim_statlen(p_target_dim_set);
1804 log('Target Composite Counts');
1805 dmp_dimset_composite_count(p_target_dim_set);
1806 log('Actual Composite Counts');
1807 dmp_dimset_composite_count(p_actual_dim_set);
1808 end if;
1809 --copy data from target to actuals, across target composite
1810 for i in 1..p_actual_cubes.count loop
1811 --get target cube
1812 bsc_aw_adapter_kpi.get_measures_for_cube(p_actual_cubes(i),p_actual_dim_set,l_measures);
1813 --all measures must be in the same tgt cube
1814 --limit measurename dim to the measures of the cube. actual and target share the same measurename_dim
1815 /*when we have targets, we do not have compressed composite */
1816 push_dim(p_actual_dim_set.measurename_dim);
1817 limit_dim(p_actual_dim_set.measurename_dim,'NULL','TO');
1818 for j in 1..l_measures.count loop
1819 limit_dim(p_actual_dim_set.measurename_dim,''''||l_measures(j).measure||'''','ADD');
1820 end loop;
1821 --
1822 l_target_cube:=bsc_aw_adapter_kpi.get_cube_set_for_measure(l_measures(1).measure,p_target_dim_set).cube;
1823 --get target cube PT or comp
1824 l_pt_comp:=bsc_aw_adapter_kpi.get_cube_pt_comp(l_target_cube.cube_name,p_target_dim_set,l_pt_comp_type);
1825 copy_target_to_actual(p_actual_cubes(i),l_target_cube.cube_name,l_pt_comp);
1826 if g_debug then
1827 log('After Target Copy, Actual Composite Counts');
1828 dmp_dimset_composite_count(p_actual_dim_set);
1829 end if;
1830 pop_dim(p_actual_dim_set.measurename_dim);
1831 end loop;
1832 if g_debug then
1833 bsc_aw_utility.load_stats('End Of Process. Copy Target to Actual','group.copy_target_to_actual');
1834 bsc_aw_utility.print_stats('group.copy_target_to_actual');
1835 end if;
1836 if p_partition_value is not null then
1837 pop_dim(p_actual_dim_set.partition_dim);
1838 end if;
1839 pop_dim(p_target_dim_set.calendar.aw_dim);
1840 pop_dim(p_target_dim_set.std_dim);
1841 pop_dim(p_target_dim_set.dim);
1842 Exception when others then
1843 log_n('Exception in copy_target_to_actual '||sqlerrm);
1844 raise;
1845 End;
1846
1847 /*
1848 there was a question on whether the copy from one PT to another will produce accurate results
1849 datacube.1=datacube.2 across PT.2 if they 2 PT share the same partition dim, then the operation results in loop across the
1850 partition dim. pdim=0, then execute the stmt. then pdim=2, execute the stmt etc. this will make sure that the data from one
1851 partition is taken to just another partition. since partitioning is done on an independent dim, at this point, its as if that dim is
1852 just another dim. there is no migration across partitions. tested this with a prototype.
1853 --
1854 more complications: when targets exist, actuals cannot be partitioned. targets can be. targets have diff levels. so if we partition the
1855 actuals, to which partition will we copy the target data?
1856 */
1857 procedure copy_target_to_actual(
1858 p_actual_cube varchar2,
1859 p_target_cube varchar2,
1860 p_composite varchar2
1861 ) is
1862 Begin
1863 /*now that balance aggregations are done in the kpi program itself, targets for non balance measures will be null at higher levels
1864 of time */
1865 g_stmt:=p_actual_cube||'=if '||p_target_cube||' EQ NA then '||p_actual_cube||' else '||p_target_cube;
1866 if p_composite is not null then
1867 g_stmt:=g_stmt||' across '||p_composite;
1868 end if;
1869 bsc_aw_dbms_aw.execute(g_stmt);
1870 Exception when others then
1871 log_n('Exception in copy_target_to_actual '||sqlerrm);
1872 raise;
1873 End;
1874
1875 /*
1876 we follow this logic
1877 create a temp virtual kpi . naming: <kpi>_<dimset>_aggregate
1878 then create the metadata using bsc_aw_md_wrapper.create_kpi
1879 then the threads will use this to aggregate
1880 then delete it from bsc olap metadata bsc_aw_md_wrapper.drop_kpi;
1881 this proc has to release the locks for the measures and then re-acquire them at the end
1882 N: we do not call bsc_aw_management.commit_aw aggregate_kpi_dimset_actuals must manage that.
1883 we have p_measure_agg_type so we do not have to create a api called "aggregate_measure_formula_job"
1884 and repeat all the code in aggregate_measure_job in aggregate_measure_formula_job
1885 */
1886 procedure aggregate_measure_job(
1887 p_kpi varchar2,
1888 p_dim_set bsc_aw_adapter_kpi.dim_set_r,
1889 p_measures dbms_sql.varchar2_table,
1890 p_options varchar2,
1891 p_measure_agg_type varchar2 --normal, balance or formula
1892 ) is
1893 --
1894 l_kpi_r bsc_aw_adapter_kpi.kpi_r;
1895 l_lock_objects dbms_sql.varchar2_table;
1896 --
1897 l_job_name varchar2(100);
1898 l_process varchar2(8000);
1899 l_job_status bsc_aw_utility.parallel_job_tb;
1900 l_api_name varchar2(200);
1901 l_run_id number;
1902 --
1903 l_cube_set bsc_aw_adapter_kpi.cube_set_r;
1904 l_cube_pt bsc_aw_adapter_kpi.partition_template_r;
1905 l_cubes_to_aggregate dbms_sql.varchar2_table;
1906 l_aggregate_options varchar2(2000);
1907 l_cube_for_measure dbms_sql.varchar2_table;
1908 l_measures_for_cube dbms_sql.varchar2_table;
1909 Begin
1910 if g_debug then
1911 log('aggregate_measure_job '||p_kpi);
1912 end if;
1913 l_kpi_r.kpi:=p_kpi||'_'||p_dim_set.dim_set_name||'_aggregate';
1914 l_kpi_r.parent_kpi:=p_kpi;
1915 l_kpi_r.dim_set(1):=p_dim_set;
1916 --first drop the metadata if it exists
1917 bsc_aw_md_wrapper.drop_kpi(l_kpi_r.kpi);
1918 --then create
1919 bsc_aw_md_wrapper.create_kpi(l_kpi_r);
1920 commit;
1921 --release locks on the cubes, l_lock_objects are the cubes and the countvar cubes
1922 get_measure_objects_to_lock(p_dim_set,p_measures,l_lock_objects);
1923 --N:> we are making an assumption here that the cubes can be released and that there are no outstanding
1924 --changes to be updated and commited for the cubes!!!!!
1925 bsc_aw_management.release_lock(l_lock_objects);
1926 --
1927 l_api_name:='bsc_aw_load_kpi.aggregate_measure';
1928 --
1929 bsc_aw_utility.clean_up_jobs('all');
1930 for i in 1..p_measures.count loop
1931 l_cube_set:=bsc_aw_adapter_kpi.get_cube_set_for_measure(p_measures(i),p_dim_set);
1932 l_cube_for_measure(i):=l_cube_set.cube.cube_name;
1933 if bsc_aw_utility.in_array(l_cubes_to_aggregate,l_cube_set.cube.cube_name)=false then
1934 l_cubes_to_aggregate(l_cubes_to_aggregate.count+1):=l_cube_set.cube.cube_name;
1935 end if;
1936 end loop;
1937 --
1938 /*
1939 if the cube is partitioned, its a job per partition. else its a job per cube
1940 */
1941 l_run_id:=0;
1942 for i in 1..l_cubes_to_aggregate.count loop
1943 l_measures_for_cube.delete;
1944 for j in 1..p_measures.count loop
1945 if l_cube_for_measure(j)=l_cubes_to_aggregate(i) then
1946 if bsc_aw_utility.in_array(l_measures_for_cube,p_measures(j))=false then
1947 l_measures_for_cube(l_measures_for_cube.count+1):=p_measures(j);
1948 end if;
1949 end if;
1950 end loop;
1951 l_cube_pt.template_name:=bsc_aw_adapter_kpi.get_cube_axis(l_cubes_to_aggregate(i),p_dim_set,'partition template');
1952 if l_cube_pt.template_name is not null then --this is a partitioned cube. job per partition
1953 l_cube_pt:=bsc_aw_adapter_kpi.get_partition_template_r(l_cube_pt.template_name,p_dim_set);
1954 for j in 1..l_cube_pt.template_partitions.count loop
1955 l_aggregate_options:=p_options||',partition='||l_cube_pt.template_partitions(j).partition_name||',partition dim value='||
1956 l_cube_pt.template_partitions(j).partition_dim_value;
1957 l_run_id:=l_run_id+1;
1958 l_job_name:='bsc_aw_aggregate_measure_'||bsc_aw_utility.get_dbms_time||'_'||l_run_id;
1959 l_process:=l_api_name||'('''||l_kpi_r.kpi||''','''||bsc_aw_utility.make_string_from_list(l_measures_for_cube)||''','''||
1960 l_aggregate_options||''','''||p_measure_agg_type||''','||l_run_id||','''||l_job_name||''','''||
1961 bsc_aw_utility.get_option_string||''');';
1962 bsc_aw_utility.start_job(l_job_name,l_run_id,l_process,null);
1963 end loop;
1964 else --non partitioned cube. launch job per cube
1965 l_aggregate_options:=p_options;
1966 l_run_id:=l_run_id+1;
1967 l_job_name:='bsc_aw_aggregate_measure_'||bsc_aw_utility.get_dbms_time||'_'||l_run_id;
1968 l_process:=l_api_name||'('''||l_kpi_r.kpi||''','''||bsc_aw_utility.make_string_from_list(l_measures_for_cube)||''','''||
1969 l_aggregate_options||''','''||p_measure_agg_type||''','||l_run_id||','''||l_job_name||''','''||
1970 bsc_aw_utility.get_option_string||''');';
1971 bsc_aw_utility.start_job(l_job_name,l_run_id,l_process,null);
1972 end if;
1973 end loop;
1974 bsc_aw_utility.wait_on_jobs(null,l_job_status);
1975 for i in 1..l_job_status.count loop
1976 if l_job_status(i).status='error' then
1977 raise bsc_aw_utility.g_exception;
1978 end if;
1979 end loop;
1980 --get lock back on the cubes
1981 bsc_aw_management.get_lock(l_lock_objects,'resync');
1982 --
1983 bsc_aw_md_wrapper.drop_kpi(l_kpi_r.kpi);
1984 commit;
1985 Exception when others then
1986 log_n('Exception in aggregate_measure_job '||sqlerrm);
1987 rollback;
1988 if g_debug is null or g_debug=false then
1989 bsc_aw_md_wrapper.drop_kpi(l_kpi_r.kpi);
1990 commit;
1991 end if;
1992 raise;
1993 End;
1994
1995 /*
1996 wrapper for aggregate_measure. this is called from aggregate_measure_job as dbms job
1997 N:!!!! p_kpi here is p_kpi||'_'||p_dim_set.dim_set_name||'_aggregate';
1998 any query going to olap metadata with p_kpi is going for p_kpi||'_'||p_dim_set.dim_set_name||'_aggregate'
1999 p_measure_agg_type is used so we dont have to un-necessarily code a wrapper for aggregate_measure_formula
2000 with the same logic as this procedure
2001 */
2002 procedure aggregate_measure(
2003 p_kpi varchar2, -- is p_kpi||'_'||p_dim_set.dim_set_name||'_aggregate';
2004 p_measures varchar2,
2005 p_aggregate_options varchar2,
2006 p_measure_agg_type varchar2, --normal, balance or formula
2007 p_run_id number,p_job_name varchar2,p_options varchar2) is
2008 --
2009 l_measures dbms_sql.varchar2_table;
2010 l_aggregation aggregation_r;
2011 l_dimset bsc_aw_adapter_kpi.dim_set_r;
2012 --
2013 l_lock_objects dbms_sql.varchar2_table;
2014 l_partition_value varchar2(200);
2015 l_partition_dim_value varchar2(200);
2016 Begin
2017 if p_run_id is not null then
2018 --this is a dbms job. we have to do the initializations since this is a new session
2019 bsc_aw_utility.g_options.delete;
2020 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
2021 bsc_aw_utility.open_file('Agg_M_'||p_kpi||'_'||p_measure_agg_type||'_'||bsc_aw_utility.get_dbms_time||'_'||p_run_id);
2022 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
2023 bsc_aw_utility.init_all_procedures;
2024 end if;
2025 --
2026 bsc_aw_utility.parse_parameter_values(p_measures,',',l_measures);
2027 --
2028 set_aggregation(p_kpi,l_aggregation);
2029 --N: l_aggregation must have only 1 dimset
2030 l_dimset:=l_aggregation.dim_set(1);
2031 --get lock we only lock the cube and countvar cube
2032 get_measure_objects_to_lock(l_dimset,l_measures,l_lock_objects);
2033 --if there is partition, then add the partition stmt to the lock objects
2034 l_partition_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition',',');
2035 l_partition_dim_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition dim value',',');
2036 if l_partition_value is not null then
2037 for i in 1..l_lock_objects.count loop
2038 l_lock_objects(i):=l_lock_objects(i)||'(partition '||l_partition_value||')';
2039 end loop;
2040 end if;
2041 bsc_aw_management.get_workspace_lock(l_lock_objects,null);
2042 --
2043 limit_all_dim(l_dimset);
2044 --
2045 if p_measure_agg_type='formula' then
2046 aggregate_measure_formula(p_kpi,l_dimset,l_measures,p_aggregate_options);
2047 else
2048 aggregate_measure(p_kpi,l_dimset,l_measures,p_aggregate_options);
2049 end if;
2050 --release locks. cubes have already been saved in aggregate_measure and aggregate_measure_formula
2051 bsc_aw_management.release_lock(l_lock_objects);
2052 if p_run_id is not null then
2053 bsc_aw_utility.send_pipe_message(p_job_name,'status=success');
2054 bsc_aw_management.detach_workspace;--release the lock
2055 end if;
2056 commit;
2057 Exception when others then
2058 log_n('Exception in aggregate_measure '||sqlerrm);
2059 if p_run_id is not null then
2060 bsc_aw_utility.send_pipe_message(p_job_name,'status=error,sqlcode='||sqlcode||',message='||sqlerrm);
2061 rollback;
2062 bsc_aw_management.detach_workspace; --this will release the locks
2063 else
2064 raise;
2065 end if;
2066 End;
2067
2068 /*
2069 given a dimset and a list of measures, this procedure will aggregate them
2070 in 10g, this may be called from another api which is launched as a dbms job
2071
2072 correct_forecast_aggregation has to happen for normal measures only as these are aggregated on time
2073 and for balance measures
2074 if Jan 20 is the current period and from jan 21 we have projection, for the month of jan, we should
2075 only consider data till Jan 20 for aggregation. so for jan, we make month value with projection=Y as 0
2076 */
2077 procedure aggregate_measure(
2078 p_kpi varchar2,
2079 p_dim_set bsc_aw_adapter_kpi.dim_set_r,
2080 p_measures dbms_sql.varchar2_table,
2081 p_aggregate_options varchar2
2082 ) is
2083 --
2084 l_agg_stmt varchar2(4000);
2085 l_flag dbms_sql.varchar2_table;
2086 l_measures bsc_aw_adapter_kpi.measure_tb;
2087 l_aggregate_flag boolean;
2088 --
2089 l_lock_objects dbms_sql.varchar2_table;
2090 l_cubes_to_aggregate dbms_sql.varchar2_table;
2091 l_cube_set bsc_aw_adapter_kpi.cube_set_tb;
2092 l_partition_value varchar2(200);
2093 l_partition_dim_value varchar2(200);
2094 l_agg_map varchar2(200);
2095 l_countvar_stmt varchar2(4000);
2096 Begin
2097 if g_debug then
2098 log_n('In aggregate_measure, kpi='||p_kpi||', dim set='||p_dim_set.dim_set_name||
2099 ', p_aggregate_options='||p_aggregate_options);
2100 log('Measures:-');
2101 for i in 1..p_measures.count loop
2102 log(p_measures(i));
2103 end loop;
2104 bsc_aw_utility.clean_stats('group.aggregate_measure');
2105 bsc_aw_utility.load_stats('Start Of Process. Aggregate Measure','group.aggregate_measure');
2106 end if;
2107 l_partition_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition',',');
2108 l_partition_dim_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition dim value',',');
2109 --first mark the measures that need to aggregated
2110 for i in 1..p_dim_set.measure.count loop
2111 if bsc_aw_utility.in_array(p_measures,p_dim_set.measure(i).measure) then
2112 l_flag(i):='Y';
2113 l_measures(l_measures.count+1):=p_dim_set.measure(i);
2114 else
2115 l_flag(i):='N';
2116 end if;
2117 end loop;
2118 --
2119 for i in 1..l_measures.count loop
2120 if bsc_aw_utility.in_array(l_cubes_to_aggregate,l_measures(i).cube)=false then
2121 l_cubes_to_aggregate(l_cubes_to_aggregate.count+1):=l_measures(i).cube;
2122 l_cube_set(l_cube_set.count+1):=bsc_aw_adapter_kpi.get_cube_set_r(l_measures(i).cube,p_dim_set);
2123 end if;
2124 end loop;
2125 --
2126 if p_dim_set.compressed='N' then /*we can limit measurename dim only when the comp is non compressed */
2127 limit_dim(p_dim_set.measurename_dim,'NULL','TO');
2128 for i in 1..l_measures.count loop
2129 limit_dim(p_dim_set.measurename_dim,''''||l_measures(i).measure||'''','ADD');
2130 end loop;
2131 end if;
2132 /*when the dimset is compressed, we cannot limit the partition dim. we need to mention the partition in the aggregate
2133 command
2134 */
2135 if p_dim_set.compressed='N' and l_partition_value is not null then
2136 push_dim(p_dim_set.partition_dim);
2137 limit_dim(p_dim_set.partition_dim,l_partition_dim_value,'TO');
2138 /*tried to see if dimensioning LB with hash partition dim can improve perf of partition aggregations. the idea was that the dim must be
2139 limited to only the values in the partition. it did not help perf. firstly, since the partition is on time, all partitions contian all other
2140 dim values. agg cost is in index traversal. if there are 1000 values of a dim in P.0, there is good change there are the same 1000 values in P.1
2141 cost in traversing the index is the same. so timing with and without partitions seem equal
2142 some more investigation into the log file found that even after limiting the partition dim, the thread aggregates all partitions.
2143 we have to specify the partition to make sure only that partition is agregated (aggregate cube (partition P.0) ...
2144 for this, we have to make entry in measuredim for cube (partition P.n)
2145 */
2146 end if;
2147 /*
2148 issue when we specify (partition P.0) with countvar cube.
2149 ORA-33852: You provided extra input starting at '('. so we will limit the partition dim and not specify the (part...) clause
2150 */
2151 l_agg_stmt:='aggregate';
2152 for i in 1..l_cube_set.count loop
2153 l_agg_stmt:=l_agg_stmt||' '||l_cube_set(i).cube.cube_name;
2154 if l_partition_value is not null then
2155 l_agg_stmt:=l_agg_stmt||' (partition '||l_partition_value||')';
2156 end if;
2157 l_lock_objects(l_lock_objects.count+1):=l_cube_set(i).cube.cube_name;
2158 if l_partition_value is not null then
2159 l_lock_objects(l_lock_objects.count):=l_lock_objects(l_lock_objects.count)||'(partition '||l_partition_value||')';
2160 end if;
2161 end loop;
2162 l_aggregate_flag:=true;
2163 if bsc_aw_utility.get_parameter_value(p_aggregate_options,'notime',',')='Y' then
2164 l_agg_map:=p_dim_set.agg_map_notime.agg_map;
2165 elsif bsc_aw_utility.get_parameter_value(p_aggregate_options,'onlytime',',')='Y' then
2166 l_agg_map:=p_dim_set.calendar.agg_map.agg_map;
2167 else
2168 l_agg_map:=p_dim_set.agg_map.agg_map;
2169 end if;
2170 if l_agg_map is null then
2171 l_aggregate_flag:=false;
2172 else
2173 l_countvar_stmt:=null;
2174 for i in 1..l_cube_set.count loop
2175 if l_cube_set(i).countvar_cube.cube_name is not null then
2176 l_countvar_stmt:=l_countvar_stmt||' '||l_cube_set(i).countvar_cube.cube_name;
2177 l_lock_objects(l_lock_objects.count+1):=l_cube_set(i).countvar_cube.cube_name;
2178 if l_partition_value is not null then
2179 l_lock_objects(l_lock_objects.count):=l_lock_objects(l_lock_objects.count)||'(partition '||l_partition_value||')';
2180 end if;
2181 end if;
2182 end loop;
2183 l_agg_stmt:=l_agg_stmt||' using '||l_agg_map;
2184 if l_countvar_stmt is not null then
2185 l_countvar_stmt:=' countvar '||l_countvar_stmt;
2186 l_agg_stmt:=l_agg_stmt||l_countvar_stmt;
2187 end if;
2188 end if;
2189 --
2190 if l_aggregate_flag then
2191 --before we execute agg maps we must limit the measuredim to the measures we are aggregating. measuredim contains all
2192 --the measures
2193 /*if we are aggregating measures in time alone, we need to limit all other dim add parents
2194 this happens when there are balance measures and non bal measures in the dimset*/
2195 if bsc_aw_utility.get_parameter_value(p_aggregate_options,'onlytime',',')='Y' then
2196 push_dim(p_dim_set.dim);
2197 limit_dim_ancestors(p_dim_set.dim,'ADD');
2198 end if;
2199 limit_measure_dim(p_dim_set.aggmap_operator,l_cubes_to_aggregate,l_partition_value);
2200 /*I:if countvar cube is needed, we cannot have partitions. cannot have stmt like
2201 aggregate datacube.4.4014 (partition P.0) using aggmap.4.4014.notime countvar test.cube (partition P.0) */
2202 if g_debug then
2203 log('Before Aggregate');
2204 dmp_dimset_dim_statlen(p_dim_set);
2205 dmp_dimset_composite_count(p_dim_set);
2206 end if;
2207 bsc_aw_dbms_aw.execute(l_agg_stmt);
2208 if bsc_aw_utility.get_parameter_value(p_aggregate_options,'onlytime',',')='Y' then
2209 pop_dim(p_dim_set.dim);
2210 end if;
2211 --if this is balance measures, then aggregate on time
2212 if bsc_aw_utility.get_parameter_value(p_aggregate_options,'notime',',')='Y' then
2213 --aggregate_balance_time(p_kpi,p_dim_set,l_measures,p_aggregate_options);
2214 /*balance aggregation are now done in the kpi load programs. we had issues aggregating balances here because looping across
2215 comp or PT is not possible to create higher periodicity balances. without looping across comp or PT, perf is very bad since
2216 its creating all logical records*/
2217 null;
2218 else
2219 --if there is forecast, we need to correct the forecast periods(periods where real and forecast data mix)
2220 --this is called for normal measures and balance measures
2221 correct_forecast_aggregation(p_kpi,p_dim_set,l_measures,p_aggregate_options);
2222 end if;
2223 --save the changes
2224 --we need to save the changes after aggregating the measures because in 10g, the subsequent operations can be in parallel
2225 --in diff sessions. for example, there can be aggregate formula which is m3=m1/m2. if we do not save m1 and m2, when
2226 --m3 is computed in a dbms job, it will not get the aggregated values of m1 and m2.
2227 bsc_aw_management.commit_aw(l_lock_objects,'no release lock');
2228 if g_debug then
2229 log('After Aggregate');
2230 dmp_dimset_composite_count(p_dim_set);
2231 end if;
2232 end if;
2233 if l_partition_value is not null then
2234 pop_dim(p_dim_set.partition_dim);
2235 end if;
2236 if g_debug then
2237 bsc_aw_utility.load_stats('End Of Process. Aggregate Measure','group.aggregate_measure');
2238 bsc_aw_utility.print_stats('group.aggregate_measure');
2239 end if;
2240 Exception when others then
2241 log_n('Exception in aggregate_measure '||sqlerrm);
2242 raise;
2243 End;
2244
2245 /*
2246 this procedure executes agg formula. this is called only for those measures that have agg formula
2247
2248 given a dimset and a list of measures, this procedure will aggregate them
2249 in 10g, this may be called from another api which is launched as a dbms job
2250
2251 handles on those measure that have agg formula like average at the lowest level
2252 here we do the following
2253 limit the dim values
2254 limit the levels
2255 limit the periods and periodicities
2256 each dim, limit status to ancestors
2257 limit time values to ancestors
2258
2259 note: when we limit dimensions, we limit all dim, whether there is rollup or not (single level).
2260 when we take the dim to the ancestors, we do so if there is a relation defined on the dim
2261
2262 when this api is called, we have already checked and seen that p_measure has a non-std formula
2263 based agg
2264 */
2265 procedure aggregate_measure_formula(
2266 p_kpi varchar2,
2267 p_dim_set bsc_aw_adapter_kpi.dim_set_r,
2268 p_measures dbms_sql.varchar2_table,
2269 p_aggregate_options varchar2
2270 ) is
2271 --
2272 l_agg_stmt varchar2(4000);
2273 l_flag dbms_sql.varchar2_table;
2274 l_measures bsc_aw_adapter_kpi.measure_tb;
2275 l_lock_objects dbms_sql.varchar2_table;
2276 --
2277 l_cube_set bsc_aw_adapter_kpi.cube_set_tb;
2278 l_pt_name dbms_sql.varchar2_table;
2279 l_pt_type dbms_sql.varchar2_table;
2280 l_partition_value varchar2(200);
2281 l_partition_dim_value varchar2(200);
2282 Begin
2283 --in 10g, l_aggregation will be null if this is a new thread. in 9i, this is already set in aggregate_kpi_dimset
2284 if g_debug then
2285 log_n('In aggregate_measure_formula, kpi='||p_kpi||', dim set='||p_dim_set.dim_set_name||
2286 ', p_aggregate_options='||p_aggregate_options);
2287 log('Measures:-');
2288 for i in 1..p_measures.count loop
2289 log(p_measures(i));
2290 end loop;
2291 bsc_aw_utility.clean_stats('group.aggregate_measure_formula');
2292 bsc_aw_utility.load_stats('Start Of Process. Aggregate Measure Formula','group.aggregate_measure_formula');
2293 end if;
2294 --first mark the measures that need to aggregated
2295 --take the dim values to the parents
2296 push_dim(p_dim_set.dim);
2297 push_dim(p_dim_set.calendar.aw_dim);
2298 --
2299 for i in 1..p_dim_set.measure.count loop
2300 if bsc_aw_utility.in_array(p_measures,p_dim_set.measure(i).measure) then
2301 l_flag(i):='Y';
2302 l_measures(l_measures.count+1):=p_dim_set.measure(i);
2303 l_lock_objects(l_lock_objects.count+1):=l_measures(l_measures.count).cube;
2304 else
2305 l_flag(i):='N';
2306 end if;
2307 end loop;
2308 --
2309 for i in 1..l_measures.count loop
2310 l_cube_set(i):=bsc_aw_adapter_kpi.get_cube_set_r(l_measures(i).cube,p_dim_set);
2311 l_pt_name(i):=null;
2312 l_pt_type(i):=null;
2313 l_pt_name(i):=bsc_aw_adapter_kpi.get_cube_pt_comp(l_measures(i).cube,p_dim_set,l_pt_type(i));
2314 end loop;
2315 l_partition_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition',',');
2316 l_partition_dim_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition dim value',',');
2317 /*
2318 if this procedure has been called for a partition, we limit the partition dim.
2319 if there is partitions, we must save back to the system the partitions of the cube
2320 we do not have compressed composite when we have formula
2321 */
2322 if l_partition_value is not null then
2323 push_dim(p_dim_set.partition_dim);
2324 limit_dim(p_dim_set.partition_dim,l_partition_dim_value,'TO');
2325 for i in 1..l_lock_objects.count loop
2326 l_lock_objects(i):=l_lock_objects(i)||'(partition '||l_partition_value||')';
2327 end loop;
2328 end if;
2329 /*limit measurename dim to all measures of the dimset
2330 when we have formulas that are non sql aggregated, we cannot be having compressed composites*/
2331 limit_dim(p_dim_set.measurename_dim,'NULL','TO');
2332 for i in 1..p_dim_set.measure.count loop
2333 limit_dim(p_dim_set.measurename_dim,''''||p_dim_set.measure(i).measure||'''','ADD');
2334 end loop;
2335 /*
2336 when aggregating formulas, we have to aggregate over each dim. say there is
2337 prod > cat > zero
2338 city > state > zero
2339 type
2340 day > month > year
2341 we have to aggregate over each dim, keeping other dim at all their levels.
2342 this means prod limited to cat and zero. other dim limited to all levels
2343 then geog limited to state and zero. other dim limited to all levels
2344 same for time
2345 */
2346 --across all dim except time
2347 --if this is a non rec single level dim, skpi it. no agg on this dim
2348 limit_dim_ancestors(p_dim_set.dim,'ADD');
2349 limit_calendar_ancestors(p_dim_set.calendar,'ADD');
2350 --now the dim and time are limited to all values including lowest level.
2351 --for each dim, we need to start remoivng the lowest level
2352 --dim push and pop go in pairs
2353 --d1 =1 to 10. push d1. then d1 changed to 4 to 8. then push d1. then pop d1. d1 is 4 to 8, then again pop d1
2354 --d1 is now 1 to 10
2355 --about the REMOVE...this was designed at the time we had zero code as a virtual level and we created a level for this
2356 --now, zero is only a value in the level. zero code level is a pure virtual level that just dimensions the relation, so we
2357 --can aggregate . this means, if we simply remove the level, then formula does not get calculated for zero values.
2358 --we need to do this : if there is only 1 level and there is agg, its zero code agg. so we limit the value of the level to "0"
2359 --limit dim to the level
2360 for i in 1..p_dim_set.dim.count loop
2361 if is_aggregation_on_dim(p_dim_set.dim(i)) then
2362 if p_dim_set.dim(i).levels.count=1 and p_dim_set.dim(i).recursive='N' then
2363 push_dim(p_dim_set.dim(i).dim_name);
2364 bsc_aw_dbms_aw.execute('limit '||p_dim_set.dim(i).levels(1).level_name||' to ''0''');
2365 limit_dim(p_dim_set.dim(i).dim_name,p_dim_set.dim(i).levels(1).level_name,'TO');
2366 else
2367 --remove the lowest level
2368 push_dim(p_dim_set.dim(i).dim_name);
2369 limit_dim(p_dim_set.dim(i).dim_name,p_dim_set.dim(i).levels(1).level_name,'REMOVE');
2370 end if;
2371 --aggregate
2372 /*in 10g, when this is running as a job, we cannot use across composite. the composite for the formula cube does nothave
2373 the higher dim values. this means across composite will not apply the formula to any higher level dim value
2374 say we have cube per measure in 10g and each measure has its own composite. then we cannot say across
2375 we do not have this case where we have measure cube and separate composites. if its 10g, its datacube
2376 if its 9i its measurecube with the same composite. so taking off check on db version*/
2377 for j in 1..l_measures.count loop
2378 l_agg_stmt:=l_measures(j).cube;
2379 if l_cube_set(j).cube_set_type='datacube' then
2380 l_agg_stmt:=l_agg_stmt||'('||l_cube_set(j).measurename_dim||' '''||l_measures(j).measure||''')';
2381 end if;
2382 l_agg_stmt:=l_agg_stmt||'=('||l_measures(j).agg_formula.agg_formula||')';
2383 if l_pt_name(j) is not null then
2384 l_agg_stmt:=l_agg_stmt||' across '||l_pt_name(j);
2385 end if;
2386 if g_debug then
2387 dmp_dimset_dim_statlen(p_dim_set);
2388 dmp_dimset_composite_count(p_dim_set);
2389 end if;
2390 bsc_aw_dbms_aw.execute(l_agg_stmt);
2391 if g_debug then
2392 dmp_dimset_composite_count(p_dim_set);
2393 end if;
2394 end loop;
2395 pop_dim(p_dim_set.dim(i).dim_name);
2396 end if;
2397 end loop;
2398 --now do the same on time
2399 /*there is a question here as to what we need to do if the formula is loading a balance column. balance agg on time has happened at load
2400 time. however, a user has defined a formula also. this means the formula takes precedence over the agg at load time. i dont think there is
2401 a case where balance measure has formula to aggregate */
2402 if is_aggregation_on_time(p_dim_set.calendar) then
2403 push_dim(p_dim_set.calendar.aw_dim);
2404 for i in 1..p_dim_set.calendar.periodicity.count loop
2405 if p_dim_set.calendar.periodicity(i).lowest_level='Y' then
2406 limit_dim(p_dim_set.calendar.aw_dim,p_dim_set.calendar.periodicity(i).aw_dim,'REMOVE');
2407 end if;
2408 end loop;
2409 --aggregate
2410 for i in 1..l_measures.count loop
2411 l_agg_stmt:=l_measures(i).cube;
2412 if l_cube_set(i).cube_set_type='datacube' then
2413 l_agg_stmt:=l_agg_stmt||'('||l_cube_set(i).measurename_dim||' '''||l_measures(i).measure||''')';
2414 end if;
2415 l_agg_stmt:=l_agg_stmt||'=('||l_measures(i).agg_formula.agg_formula||')';
2416 if l_pt_name(i) is not null then
2417 l_agg_stmt:=l_agg_stmt||' across '||l_pt_name(i);
2418 end if;
2419 if g_debug then
2420 dmp_dimset_dim_statlen(p_dim_set);
2421 dmp_dimset_composite_count(p_dim_set);
2422 end if;
2423 bsc_aw_dbms_aw.execute(l_agg_stmt);
2424 if g_debug then
2425 dmp_dimset_composite_count(p_dim_set);
2426 end if;
2427 end loop;
2428 pop_dim(p_dim_set.calendar.aw_dim);
2429 end if;
2430 pop_dim(p_dim_set.dim);
2431 pop_dim(p_dim_set.calendar.aw_dim);
2432 if l_partition_value is not null then
2433 pop_dim(p_dim_set.partition_dim);
2434 end if;
2435 --save the changes
2436 bsc_aw_management.commit_aw(l_lock_objects,'no release lock');
2437 if g_debug then
2438 bsc_aw_utility.load_stats('End Of Process. Aggregate Measure Formula','group.aggregate_measure_formula');
2439 bsc_aw_utility.print_stats('group.aggregate_measure_formula');
2440 end if;
2441 Exception when others then
2442 log_n('Exception in aggregate_measure_formula '||sqlerrm);
2443 raise;
2444 End;
2445
2446 /*
2447 this procedure is called only if the measure has projections on it. in this case,
2448 we limit the projection dim to Y, then we look at each periodicity for the cube. for
2449 each periodicity, we choose the period where the real and projected data are present.
2450 we then set the value of the cube to 0
2451
2452 at this time, all dim are limited to the values for aggregation.
2453 projection dim is limited to Y and N
2454 time is limited to all the affected lowest level values (days)
2455
2456 we limit projection to Y
2457 we loop through each periodicity, remove the affected periods from the status of time dim
2458
2459 then we set cube=0
2460
2461 we set the cube=0 only for those measures that have forecast. if a measure does not have forecast, we do not
2462 have to do this.
2463 */
2464 procedure correct_forecast_aggregation(
2465 p_kpi varchar2,
2466 p_dim_set bsc_aw_adapter_kpi.dim_set_r,
2467 p_measures bsc_aw_adapter_kpi.measure_tb,
2468 p_aggregate_options varchar2
2469 ) is
2470 --
2471 l_period varchar2(100);
2472 l_pt_comp varchar2(200);
2473 l_pt_comp_type varchar2(200);
2474 l_partition_value varchar2(200);
2475 l_partition_dim_value varchar2(200);
2476 l_cubes dbms_sql.varchar2_table;
2477 l_measures_to_limit dbms_sql.varchar2_table;
2478 Begin
2479 if bsc_aw_adapter_kpi.is_calendar_aggregated(p_dim_set.calendar) then
2480 push_dim(get_projection_dim(p_dim_set));
2481 push_dim(p_dim_set.dim);
2482 push_dim(p_dim_set.calendar.aw_dim);
2483 limit_dim_ancestors(p_dim_set.dim,'ADD');
2484 g_stmt:='limit '||get_projection_dim(p_dim_set)||' to ''Y''';
2485 bsc_aw_dbms_aw.execute(g_stmt);
2486 g_stmt:='limit '||p_dim_set.calendar.aw_dim||' to null';
2487 bsc_aw_dbms_aw.execute(g_stmt);
2488 l_partition_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition',',');
2489 l_partition_dim_value:=bsc_aw_utility.get_parameter_value(p_aggregate_options,'partition dim value',',');
2490 if l_partition_value is not null then
2491 push_dim(p_dim_set.partition_dim);
2492 limit_dim(p_dim_set.partition_dim,l_partition_dim_value,'TO');
2493 end if;
2494 --loop through each periodicity. we dont have to do this for lowest periodicity
2495 for i in 1..p_dim_set.calendar.periodicity.count loop
2496 --we specify the calendar and periodicity and expect to get back the period for which there is
2497 --real and projected data
2498 --if p_dim_set.calendar.periodicity(i).lowest_level='N' then
2499 if p_dim_set.calendar.periodicity(i).aggregated='Y' then
2500 l_period:=bsc_aw_utility.get_parameter_value(p_dim_set.calendar.periodicity(i).property,'current period',',');
2501 g_stmt:='limit '||p_dim_set.calendar.periodicity(i).aw_dim||' to '''||l_period||'''';
2502 bsc_aw_dbms_aw.execute(g_stmt);
2503 g_stmt:='limit '||p_dim_set.calendar.aw_dim||' add '||p_dim_set.calendar.periodicity(i).aw_dim;
2504 bsc_aw_dbms_aw.execute(g_stmt);
2505 end if;
2506 end loop;
2507 /*
2508 when we had =0, aw gave error that we cannot change the values when looping across PT.
2509 does this mean that this is generating new values? so far no perf issues.=na is fine
2510 we cannot have cube(partition P)=na across PT. error : DATACUBE.1.4014 is not a command.
2511 found later that having=na is fine but it does not NA the values. so we have to leave the PT out
2512 --
2513 in 10.2, found that across PT does work. data is set to na, however, composite is not cleared. since we set =na only for the actuals
2514 cube, we shound be fine.
2515 */
2516 l_measures_to_limit.delete;
2517 for i in 1..p_measures.count loop
2518 if p_measures(i).forecast='Y' then
2519 l_measures_to_limit(l_measures_to_limit.count+1):=''''||p_measures(i).measure||'''';
2520 if bsc_aw_utility.in_array(l_cubes,p_measures(i).cube)=false then
2521 l_cubes(l_cubes.count+1):=p_measures(i).cube;
2522 end if;
2523 end if;
2524 end loop;
2525 if g_debug then
2526 dmp_dimset_dim_statlen(p_dim_set);
2527 end if;
2528 --N: if the dimset has multiple cubes, the cubes cannot share measures. so we can set the measurename_dim to all
2529 --measures
2530 /*when we are here, the composite cannot be compressed */
2531 push_dim(p_dim_set.measurename_dim);
2532 limit_dim(p_dim_set.measurename_dim,'NULL','TO');
2533 limit_dim(p_dim_set.measurename_dim,l_measures_to_limit,'ADD');
2534 for i in 1..l_cubes.count loop
2535 l_pt_comp:=bsc_aw_adapter_kpi.get_cube_pt_comp(l_cubes(i),p_dim_set,l_pt_comp_type);
2536 g_stmt:=l_cubes(i)||'=NA';
2537 --if l_pt_comp is not null and l_pt_comp_type='composite' then
2538 if l_pt_comp is not null then --for both composite and PT(10.2)(composite values are not cleared)
2539 g_stmt:=g_stmt||' across '||l_pt_comp;
2540 end if;
2541 bsc_aw_dbms_aw.execute(g_stmt);
2542 end loop;
2543 pop_dim(get_projection_dim(p_dim_set));
2544 pop_dim(p_dim_set.dim);
2545 pop_dim(p_dim_set.calendar.aw_dim);
2546 if l_partition_value is not null then
2547 pop_dim(p_dim_set.partition_dim);
2548 end if;
2549 pop_dim(p_dim_set.measurename_dim);
2550 end if;
2551 Exception when others then
2552 log_n('Exception in correct_forecast_aggregation '||sqlerrm);
2553 raise;
2554 End;
2555
2556 /*
2557 this procedure gives the period in which there is a mix of forecast and real data
2558 we need the following
2559 kpi and periodicity : we use this to hit bsc_db_tables that will indicate the current period
2560 calendar : with this, we will get the current year.
2561 then we can say make the period
2562 */
2563 procedure get_forecast_current_period(
2564 p_aggregation in out nocopy aggregation_r) is
2565 --
2566 l_period varchar2(100);
2567 Begin
2568 for i in 1..p_aggregation.dim_set.count loop
2569 for j in 1..p_aggregation.dim_set(i).calendar.periodicity.count loop
2570 get_forecast_current_period(nvl(p_aggregation.parent_kpi,p_aggregation.kpi),p_aggregation.dim_set(i).calendar.calendar,
2571 p_aggregation.dim_set(i).calendar.periodicity(j).periodicity,l_period);
2572 p_aggregation.dim_set(i).calendar.periodicity(j).property:=p_aggregation.dim_set(i).calendar.periodicity(j).property||
2573 'current period='||l_period||',';
2574 end loop;
2575 end loop;
2576 Exception when others then
2577 log_n('Exception in get_forecast_current_period '||sqlerrm);
2578 raise;
2579 End;
2580
2581 procedure get_forecast_current_period(
2582 p_kpi varchar2,
2583 p_calendar number,
2584 p_periodicity number,
2585 p_period out nocopy varchar2
2586 ) is
2587 Begin
2588 bsc_aw_bsc_metadata.get_forecast_current_period(p_kpi,p_calendar,p_periodicity,p_period);
2589 Exception when others then
2590 log_n('Exception in get_forecast_current_period '||sqlerrm);
2591 raise;
2592 End;
2593
2594 /*
2595 this procedure is used for aggregatingbalance measures on time
2596 this limist teh time dim end period relation to the current period
2597 Q:do we need to set the proj values for missing periodicities to null? for now, lets do this, so the data is in sync
2598 across all periodicities of the kpi
2599 */
2600 procedure limit_calendar_end_period_rel(p_calendar bsc_aw_adapter_kpi.calendar_r) is
2601 --
2602 l_period_lowest varchar2(100); --current period of the lowest periodicity
2603 l_period varchar2(100);
2604 Begin
2605 if g_debug then
2606 log('--limit_calendar_end_period_rel------');
2607 end if;
2608 push_dim(p_calendar.aw_dim);
2609 push_dim(p_calendar.end_period_level_name_dim);
2610 g_stmt:='limit '||p_calendar.aw_dim||' to null';
2611 bsc_aw_dbms_aw.execute(g_stmt);
2612 --
2613 for i in 1..p_calendar.periodicity.count loop
2614 if p_calendar.periodicity(i).lowest_level='Y' then
2615 l_period_lowest:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(i).property,'current period',',');
2616 g_stmt:='limit '||p_calendar.end_period_level_name_dim||' TO '''||p_calendar.periodicity(i).aw_dim||'''';
2617 bsc_aw_dbms_aw.execute(g_stmt);
2618 --for all upper levels, back up original relation value
2619 for j in 1..p_calendar.periodicity.count loop
2620 if p_calendar.periodicity(j).lowest_level <> 'Y' then
2621 l_period:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(j).property,'current period',',');
2622 g_stmt:='limit '||p_calendar.periodicity(j).aw_dim||' to '''||l_period||'''';
2623 bsc_aw_dbms_aw.execute(g_stmt);
2624 g_stmt:='limit '||p_calendar.aw_dim||' to '||p_calendar.periodicity(j).aw_dim;
2625 bsc_aw_dbms_aw.execute(g_stmt);
2626 --set the rel.end_period relation
2627 --save the value for use in reset_calendar_end_period_rel
2628 g_stmt:=p_calendar.end_period_relation_name||'.temp = '||p_calendar.end_period_relation_name;
2629 bsc_aw_dbms_aw.execute(g_stmt);
2630 --we have to be careful here. imagine there is a hier like this
2631 -- M >- Q >- S >- Y
2632 -- W >- BiW >- Y
2633 --if we simply put the value of l_period_lowest for all upper levels, we may put month value into BiW. this is wrong.
2634 --month does not rollup to BiW.
2635 g_stmt:=p_calendar.end_period_relation_name||'= if '||p_calendar.end_period_relation_name||' EQ NA then NA else '||
2636 p_calendar.aw_dim||'('||p_calendar.periodicity(i).aw_dim||' '''||l_period_lowest||''')';
2637 bsc_aw_dbms_aw.execute(g_stmt);
2638 end if;
2639 end loop;
2640 end if;
2641 end loop;
2642 pop_dim(p_calendar.aw_dim);
2643 pop_dim(p_calendar.end_period_level_name_dim);
2644 if g_debug then
2645 log('----');
2646 end if;
2647 Exception when others then
2648 log_n('Exception in limit_calendar_end_period_rel '||sqlerrm);
2649 raise;
2650 End;
2651
2652 /*
2653 this procedure restores the relation p_calendar.end_period_relation_name
2654 limit_calendar_end_period_rel has altered the relation. if we commit the changes, the relation is forever
2655 altered.
2656 another strategy is not to alter the relation. in this case, we first do the copy, then for each
2657 higher periodicity, we can copy based on the current period.
2658 This has the isue that the copy occurs several times. its more efficient to do it once.
2659 resetting the relation back is easier. we need to store the values in global variables
2660 */
2661 procedure reset_calendar_end_period_rel(p_calendar bsc_aw_adapter_kpi.calendar_r) is
2662 --
2663 l_period varchar2(100);
2664 Begin
2665 if g_debug then
2666 log('--reset_calendar_end_period_rel------');
2667 end if;
2668 push_dim(p_calendar.aw_dim);
2669 push_dim(p_calendar.end_period_level_name_dim);
2670 --we limit level name dim to the lowest level of the kpi dimset
2671 g_stmt:='limit '||p_calendar.aw_dim||' to null';
2672 bsc_aw_dbms_aw.execute(g_stmt);
2673 for i in 1..p_calendar.periodicity.count loop
2674 if p_calendar.periodicity(i).lowest_level='Y' then
2675 g_stmt:='limit '||p_calendar.end_period_level_name_dim||' to '''||p_calendar.periodicity(i).aw_dim||'''';
2676 bsc_aw_dbms_aw.execute(g_stmt);
2677 for j in 1..p_calendar.periodicity.count loop
2678 if p_calendar.periodicity(j).lowest_level <> 'Y' then
2679 l_period:=bsc_aw_utility.get_parameter_value(p_calendar.periodicity(j).property,'current period',',');
2680 g_stmt:='limit '||p_calendar.periodicity(j).aw_dim||' to '''||l_period||'''';
2681 bsc_aw_dbms_aw.execute(g_stmt);
2682 g_stmt:='limit '||p_calendar.aw_dim||' to '||p_calendar.periodicity(j).aw_dim;
2683 bsc_aw_dbms_aw.execute(g_stmt);
2684 g_stmt:=p_calendar.end_period_relation_name||'='||p_calendar.end_period_relation_name||'.temp';
2685 bsc_aw_dbms_aw.execute(g_stmt);
2686 end if;
2687 end loop;
2688 end if;
2689 end loop;
2690 --
2691 pop_dim(p_calendar.aw_dim);
2692 pop_dim(p_calendar.end_period_level_name_dim);
2693 if g_debug then
2694 log('----');
2695 end if;
2696 Exception when others then
2697 log_n('Exception in reset_calendar_end_period_rel '||sqlerrm);
2698 raise;
2699 End;
2700
2701 --limit a given dim to a given value
2702 procedure limit_dim(p_dim varchar2,p_value varchar2,p_mode varchar2) is
2703 Begin
2704 g_stmt:='limit '||p_dim||' '||p_mode||' '||p_value;
2705 bsc_aw_dbms_aw.execute(g_stmt);
2706 Exception when others then
2707 log_n('Exception in limit_dim '||sqlerrm);
2708 raise;
2709 End;
2710
2711 --limit a given dim to a given set of value
2712 procedure limit_dim(p_dim varchar2,p_value dbms_sql.varchar2_table,p_mode varchar2) is
2713 Begin
2714 for i in 1..p_value.count loop
2715 limit_dim(p_dim,p_value(i),p_mode);
2716 end loop;
2717 Exception when others then
2718 log_n('Exception in limit_dim '||sqlerrm);
2719 raise;
2720 End;
2721
2722 /*
2723 this procedures limits the dim values
2724 */
2725 procedure limit_dim_values(p_dim bsc_aw_adapter_kpi.dim_tb,p_mode varchar2) is
2726 Begin
2727 for i in 1..p_dim.count loop
2728 g_stmt:='limit '||p_dim(i).dim_name||' '||p_mode||' '||p_dim(i).limit_cube;
2729 bsc_aw_dbms_aw.execute(g_stmt);
2730 end loop;
2731 Exception when others then
2732 log_n('Exception in limit_dim_values '||sqlerrm);
2733 raise;
2734 End;
2735
2736 /*
2737 given a adv sum profile value, this procedure limits the dim levels
2738
2739 for actuals, the dim will have all the levels
2740 when called for targets, the dim's lowest level will be the level of the target, like state for example
2741
2742 for rec dim, we limit the levels only in the case where the rec dim is implemented as denorm hier
2743 if implemented as normal hier, we aggregate to all levels. in this case, we have to aggregate all levels
2744 to reach the top node.
2745 */
2746 procedure limit_dim_levels(p_dim bsc_aw_adapter_kpi.dim_tb) is
2747 Begin
2748 for i in 1..p_dim.count loop
2749 if p_dim(i).recursive<>'Y' then
2750 limit_dim_levels(p_dim(i));
2751 end if;
2752 end loop;
2753 --now, set the levels for the rec dim
2754 for i in 1..p_dim.count loop
2755 if p_dim(i).recursive='Y' and p_dim(i).recursive_norm_hier='N' then
2756 limit_dim_levels(p_dim(i));
2757 end if;
2758 end loop;
2759 Exception when others then
2760 log_n('Exception in limit_dim_levels '||sqlerrm);
2761 raise;
2762 End;
2763
2764 /*
2765 the algo:
2766 start from the lowest level. limit level name dim to all parent.child and recursively go up
2767 as long as the level is a part of the dim
2768 now, in bsc_aw_md_api, when we load the parent child relation for both dim and calendar,
2769 we only bring in the parent child that belong to the kpi
2770 */
2771 procedure limit_dim_levels(p_dim bsc_aw_adapter_kpi.dim_r) is
2772 Begin
2773 if p_dim.recursive='N' then
2774 g_stmt:='limit '||p_dim.level_name_dim||' to null';
2775 bsc_aw_dbms_aw.execute(g_stmt);
2776 --we start the process with the lowest level. this procedure is called rec for the parents
2777 limit_dim_levels(p_dim,p_dim.levels(1).level_name);
2778 else --now, set the levels for the rec dim
2779 --p_dim.level_name_dim||'.position is the variable with the position for each rec dim value, larry=1, john wookey=2
2780 /*this api is only called when the rec dim is implemented in denorm fashion */
2781 g_stmt:='limit '||p_dim.level_name_dim||' to '||p_dim.level_name_dim||'.position LE '||p_dim.agg_level;
2782 bsc_aw_dbms_aw.execute(g_stmt);
2783 end if;
2784 Exception when others then
2785 log_n('Exception in limit_dim_levels '||sqlerrm);
2786 raise;
2787 End;
2788
2789 /*
2790 this procedure fires recursively
2791 p_level is the child level
2792 parent_child will onky have the parent child belonging to the kpi
2793 */
2794 procedure limit_dim_levels(p_dim bsc_aw_adapter_kpi.dim_r,p_level varchar2) is
2795 l_this_level bsc_aw_adapter_kpi.level_r;
2796 l_parent_level bsc_aw_adapter_kpi.level_r;
2797 l_flag bsc_aw_utility.boolean_table;
2798 Begin
2799 --first the zero code
2800 l_this_level:=bsc_aw_adapter_kpi.get_dim_level_r(p_dim,p_level);
2801 if l_this_level.aggregated='Y' then
2802 if l_this_level.zero_code='Y' and l_this_level.zero_aggregated='Y' then
2803 g_stmt:='limit '||p_dim.level_name_dim||' add '''||l_this_level.zero_code_level||'.'||
2804 l_this_level.level_name||'''';
2805 bsc_aw_dbms_aw.execute(g_stmt);
2806 end if;
2807 end if;
2808 for i in 1..p_dim.parent_child.count loop
2809 l_flag(i):=false;
2810 if p_dim.parent_child(i).child_level=p_level and p_dim.parent_child(i).parent_level is not null then
2811 l_parent_level:=bsc_aw_adapter_kpi.get_dim_level_r(p_dim,p_dim.parent_child(i).parent_level);
2812 if l_parent_level.aggregated='Y' then
2813 l_flag(i):=true;
2814 g_stmt:='limit '||p_dim.level_name_dim||' add '''||p_dim.parent_child(i).parent_level||'.'||
2815 p_dim.parent_child(i).child_level||'''';
2816 bsc_aw_dbms_aw.execute(g_stmt);
2817 end if;
2818 end if;
2819 end loop;
2820 --rec do this for the parent levels
2821 for i in 1..p_dim.parent_child.count loop
2822 if l_flag(i) then
2823 limit_dim_levels(p_dim,p_dim.parent_child(i).parent_level);
2824 end if;
2825 end loop;
2826 Exception when others then
2827 log_n('Exception in limit_dim_levels '||sqlerrm);
2828 raise;
2829 End;
2830
2831 /*
2832 limits the dim values to only the upper levels.
2833 p_operator is "to" or "add"
2834 */
2835 procedure limit_dim_ancestors(p_dim bsc_aw_adapter_kpi.dim_tb,p_operator varchar2) is
2836 Begin
2837 --we cannot use if p_dim(i).levels.count> 1 and p_dim(i).relation_name is not null then
2838 --because rec dim only have 1 level. single level dim do not have relation_name
2839 --we use an api is_aggregation_on_dim that says if there is agg on a dim or not for this dimset
2840 --i think we should not use is_aggregation_on_dim here. if we are using only 1 level of a dim here,
2841 --we would have limited the level_name_dim before.
2842 for i in 1..p_dim.count loop
2843 if p_dim(i).relation_name is not null then
2844 g_stmt:='limit '||p_dim(i).dim_name||' '||p_operator||' ancestors using '||p_dim(i).relation_name;
2845 bsc_aw_dbms_aw.execute(g_stmt);
2846 end if;
2847 end loop;
2848 Exception when others then
2849 log_n('Exception in limit_dim_ancestors '||sqlerrm);
2850 raise;
2851 End;
2852
2853 procedure limit_dim_descendents(p_dim bsc_aw_adapter_kpi.dim_tb,p_operator varchar2,p_depth varchar2) is
2854 Begin
2855 for i in 1..p_dim.count loop
2856 if p_dim(i).relation_name is not null then
2857 g_stmt:='limit '||p_dim(i).dim_name||' '||p_operator||' '||p_depth||' using '||p_dim(i).relation_name;
2858 bsc_aw_dbms_aw.execute(g_stmt);
2859 end if;
2860 end loop;
2861 Exception when others then
2862 log_n('Exception in limit_dim_descendents '||sqlerrm);
2863 raise;
2864 End;
2865
2866 /*
2867 after aggregation, we reset the limit cubes. at this time, when the api is called,
2868 the dim is limited to those values where the limit cube was TRUE
2869 */
2870 procedure reset_dim_limit_cubes(p_dim bsc_aw_adapter_kpi.dim_tb) is
2871 Begin
2872 for i in 1..p_dim.count loop
2873 g_stmt:=p_dim(i).limit_cube||'=FALSE';
2874 if p_dim(i).limit_cube_composite is not null then
2875 g_stmt:=g_stmt||' across '||p_dim(i).limit_cube_composite;
2876 end if;
2877 bsc_aw_dbms_aw.execute(g_stmt);
2878 end loop;
2879 Exception when others then
2880 log_n('Exception in reset_dim_limit_cubes '||sqlerrm);
2881 raise;
2882 End;
2883
2884 /*
2885 limits the time values
2886 */
2887 procedure limit_calendar_values(p_calendar bsc_aw_adapter_kpi.calendar_r,p_mode varchar2) is
2888 Begin
2889 g_stmt:='limit '||p_calendar.aw_dim||' '||p_mode||' '||p_calendar.limit_cube;
2890 bsc_aw_dbms_aw.execute(g_stmt);
2891 Exception when others then
2892 log_n('Exception in limit_calendar_values '||sqlerrm);
2893 raise;
2894 End;
2895
2896 /*
2897 this procedure limits the periodities to the ones applicable to the dim set for agg
2898 */
2899 procedure limit_calendar_levels(p_calendar bsc_aw_adapter_kpi.calendar_r) is
2900 Begin
2901 g_stmt:='limit '||p_calendar.level_name_dim||' to null';
2902 bsc_aw_dbms_aw.execute(g_stmt);
2903 --start the process. this procedure is called rec for parent levels
2904 --start from the child and go all the way up
2905 for i in 1..p_calendar.periodicity.count loop
2906 if p_calendar.periodicity(i).lowest_level='Y' then
2907 limit_calendar_levels(p_calendar,p_calendar.periodicity(i).aw_dim);
2908 end if;
2909 end loop;
2910 Exception when others then
2911 log_n('Exception in limit_calendar_levels '||sqlerrm);
2912 raise;
2913 End;
2914
2915 /*
2916 this procedure is called rec for the parent levels
2917 */
2918 procedure limit_calendar_levels(
2919 p_calendar bsc_aw_adapter_kpi.calendar_r,
2920 p_periodicity_dim varchar2) is
2921 --
2922 l_flag bsc_aw_utility.boolean_table;
2923 l_periodicity bsc_aw_adapter_kpi.periodicity_r;
2924 Begin
2925 l_periodicity:=bsc_aw_adapter_kpi.get_periodicity_r(p_calendar.periodicity,p_periodicity_dim);
2926 if l_periodicity.aggregated='Y' then
2927 for i in 1..p_calendar.parent_child.count loop
2928 l_flag(i):=false;
2929 if p_calendar.parent_child(i).child_dim_name=p_periodicity_dim and p_calendar.parent_child(i).parent_dim_name is not null then
2930 l_periodicity:=bsc_aw_adapter_kpi.get_periodicity_r(p_calendar.periodicity,p_calendar.parent_child(i).parent_dim_name);
2931 if l_periodicity.aggregated='Y' then
2932 l_flag(i):=true;
2933 g_stmt:='limit '||p_calendar.level_name_dim||' add '''||p_calendar.parent_child(i).parent_dim_name||'.'||
2934 p_calendar.parent_child(i).child_dim_name||'''';
2935 bsc_aw_dbms_aw.execute(g_stmt);
2936 end if;
2937 end if;
2938 end loop;
2939 for i in 1..p_calendar.parent_child.count loop
2940 if l_flag(i) then
2941 limit_calendar_levels(p_calendar,p_calendar.parent_child(i).parent_dim_name);
2942 end if;
2943 end loop;
2944 end if;
2945 Exception when others then
2946 log_n('Exception in limit_calendar_levels '||sqlerrm);
2947 raise;
2948 End;
2949
2950 --limit the calendar vlues to the parents
2951 --p_operator is "to" or "add"
2952 procedure limit_calendar_ancestors(p_calendar bsc_aw_adapter_kpi.calendar_r,p_operator varchar2) is
2953 Begin
2954 g_stmt:='limit '||p_calendar.aw_dim||' '||p_operator||' ancestors using '||p_calendar.relation_name;
2955 bsc_aw_dbms_aw.execute(g_stmt);
2956 Exception when others then
2957 log_n('Exception in limit_calendar_ancestors '||sqlerrm);
2958 raise;
2959 End;
2960
2961 procedure limit_calendar_descendents(p_calendar bsc_aw_adapter_kpi.calendar_r,p_operator varchar2,p_depth varchar2) is
2962 Begin
2963 g_stmt:='limit '||p_calendar.aw_dim||' '||p_operator||' '||p_depth||' using '||p_calendar.relation_name;
2964 bsc_aw_dbms_aw.execute(g_stmt);
2965 Exception when others then
2966 log_n('Exception in limit_calendar_descendents '||sqlerrm);
2967 raise;
2968 End;
2969
2970 /*
2971 reset the calendar limit cube after aggregation. at this time calendar is limited to those
2972 values where the limit cube was TRUE
2973 */
2974 procedure reset_calendar_limit_cubes(p_calendar bsc_aw_adapter_kpi.calendar_r) is
2975 Begin
2976 g_stmt:=p_calendar.limit_cube||'=FALSE';
2977 if p_calendar.limit_cube_composite is not null then
2978 g_stmt:=g_stmt||' across '||p_calendar.limit_cube_composite;
2979 end if;
2980 bsc_aw_dbms_aw.execute(g_stmt);
2981 Exception when others then
2982 log_n('Exception in reset_calendar_limit_cubes '||sqlerrm);
2983 raise;
2984 End;
2985
2986 procedure set_aggregation(p_kpi varchar2,p_aggregation out nocopy aggregation_r) is
2987 l_cache_aggregation_r aggregation_r;
2988 Begin
2989 p_aggregation:=null;
2990 p_aggregation.kpi:=p_kpi;
2991 l_cache_aggregation_r:=get_cache_aggregation_r(p_kpi);
2992 if l_cache_aggregation_r.kpi is null then
2993 bsc_aw_md_api.get_aggregation_r(p_aggregation);
2994 get_forecast_current_period(p_aggregation);
2995 dmp_aggregation_r(p_aggregation);
2996 g_cache_aggregation_r(g_cache_aggregation_r.count+1):=p_aggregation;
2997 else
2998 p_aggregation:=l_cache_aggregation_r;
2999 end if;
3000 Exception when others then
3001 log_n('Exception in set_aggregation '||sqlerrm);
3002 raise;
3003 End;
3004
3005 procedure dmp_aggregation_r(p_aggregation aggregation_r) is
3006 Begin
3007 log_n('Dmp Aggregation:- KPI='||p_aggregation.kpi);
3008 for i in 1..p_aggregation.dim_set.count loop
3009 bsc_aw_adapter_kpi.dmp_dimset(p_aggregation.dim_set(i));
3010 end loop;
3011 Exception when others then
3012 log_n('Exception in dmp_aggregation_r '||sqlerrm);
3013 raise;
3014 End;
3015
3016 procedure push_dim(p_dim bsc_aw_adapter_kpi.dim_tb) is
3017 Begin
3018 for i in 1..p_dim.count loop
3019 push_dim(p_dim(i).dim_name);
3020 end loop;
3021 Exception when others then
3022 log_n('Exception in push_dim '||sqlerrm);
3023 raise;
3024 End;
3025
3026 procedure push_dim(p_dim varchar2) is
3027 Begin
3028 bsc_aw_dbms_aw.execute('push '||p_dim);
3029 Exception when others then
3030 log_n('Exception in push_dim '||sqlerrm);
3031 raise;
3032 End;
3033
3034 procedure pop_dim(p_dim bsc_aw_adapter_kpi.dim_tb) is
3035 Begin
3036 for i in 1..p_dim.count loop
3037 pop_dim(p_dim(i).dim_name);
3038 end loop;
3039 Exception when others then
3040 log_n('Exception in pop_dim '||sqlerrm);
3041 raise;
3042 End;
3043
3044 procedure pop_dim(p_dim varchar2) is
3045 Begin
3046 bsc_aw_dbms_aw.execute('pop '||p_dim);
3047 Exception when others then
3048 log_n('Exception in pop_dim '||sqlerrm);
3049 raise;
3050 End;
3051
3052 /*
3053 during aggregation, we set the status of various dimensions. we want to be able to restore the
3054 status of the dim back to the level before the agg. otherwise it becomes hard to track the dim
3055 status from api to api
3056
3057 in push level, we must use context. the reason is that pushlevel command in aw can be used only within programs
3058 " You can use PUSHLEVEL only within programs"
3059
3060 For now, not used.
3061 */
3062 procedure push_level(p_marker varchar2) is
3063 Begin
3064 null;
3065 Exception when others then
3066 log_n('Exception in push_level '||sqlerrm);
3067 raise;
3068 End;
3069
3070 procedure pop_level(p_marker varchar2) is
3071 Begin
3072 null;
3073 Exception when others then
3074 log_n('Exception in pop_level '||sqlerrm);
3075 raise;
3076 End;
3077
3078 /*
3079 this procedure purges the kpi. i
3080 */
3081 procedure purge_kpi(p_kpi varchar2) is
3082 --
3083 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
3084 Begin
3085 if g_debug then
3086 log_n('Purge KPI '||p_kpi);
3087 end if;
3088 --for purge, we need exclusive lock. else we cannot delete composites
3089 bsc_aw_management.get_workspace_lock('rw',null);
3090 bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3091 --clear the cubes
3092 for i in 1..l_bsc_olap_object.count loop
3093 if l_bsc_olap_object(i).object_type='data cube' then
3094 bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3095 end if;
3096 end loop;
3097 --clear fcst cubes if there are any
3098 --clear the limit cubes
3099 for i in 1..l_bsc_olap_object.count loop
3100 if l_bsc_olap_object(i).object_type='dim limit cube' then
3101 bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3102 end if;
3103 end loop;
3104 for i in 1..l_bsc_olap_object.count loop
3105 if l_bsc_olap_object(i).object_type='limit cube composite' then
3106 bsc_aw_dbms_aw.execute('maintain '||l_bsc_olap_object(i).olap_object||' delete all');
3107 end if;
3108 end loop;
3109 --clear the countvar cubes
3110 for i in 1..l_bsc_olap_object.count loop
3111 if l_bsc_olap_object(i).object_type='countvar cube' then
3112 bsc_aw_dbms_aw.execute('clear all from '||l_bsc_olap_object(i).olap_object);
3113 end if;
3114 end loop;
3115 --clear the composites
3116 for i in 1..l_bsc_olap_object.count loop
3117 if l_bsc_olap_object(i).object_type='measure composite' then
3118 bsc_aw_dbms_aw.execute('maintain '||l_bsc_olap_object(i).olap_object||' delete all');
3119 end if;
3120 end loop;
3121 --set the dimset current change vector to 0 for all dimsets of the kpi
3122 reset_dimset_change_vector(p_kpi);
3123 --
3124 bsc_aw_management.commit_aw;
3125 commit;
3126 Exception when others then
3127 log_n('Exception in purge_kpi '||sqlerrm);
3128 raise;
3129 End;
3130
3131 procedure get_dimset_objects(p_kpi varchar2,p_dim_set varchar2,p_oo out nocopy bsc_aw_md_wrapper.bsc_olap_object_tb) is
3132 l_oo_kpi bsc_aw_md_wrapper.bsc_olap_object_tb;
3133 Begin
3134 bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_oo_kpi);
3135 for i in 1..l_oo_kpi.count loop
3136 if bsc_aw_utility.get_parameter_value(l_oo_kpi(i).property1,'dim set name',',')=p_dim_set then
3137 p_oo(p_oo.count+1):=l_oo_kpi(i);
3138 end if;
3139 end loop;
3140 Exception when others then
3141 log_n('Exception in get_dimset_objects '||sqlerrm);
3142 raise;
3143 End;
3144
3145 /*
3146 this will set the current change vector to 0. called from purge_kpi
3147 */
3148 procedure reset_dimset_change_vector(p_kpi varchar2) is
3149 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3150 l_property varchar2(4000);
3151 Begin
3152 bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'base table dim set',p_kpi,'kpi',l_olap_object_relation);
3153 for i in 1..l_olap_object_relation.count loop
3154 --we must not lose the measures=m1,m2, from the property of base table dim set after we reset the change vector
3155 l_property:=l_olap_object_relation(i).property1;
3156 bsc_aw_utility.update_property(l_property,'current change vector','0',',');
3157 bsc_aw_md_api.update_olap_object_relation(l_olap_object_relation(i).object,l_olap_object_relation(i).object_type,
3158 l_olap_object_relation(i).relation_type,l_olap_object_relation(i).parent_object,l_olap_object_relation(i).parent_object_type,
3159 'relation_object,relation_object_type',l_olap_object_relation(i).relation_object||','||l_olap_object_relation(i).relation_object_type,
3160 'property1',l_property);
3161 end loop;
3162 Exception when others then
3163 log_n('Exception in reset_dimset_change_vector '||sqlerrm);
3164 raise;
3165 End;
3166
3167 --limit the measuredim to the measures that we are going to aggregate
3168 procedure limit_measure_dim(
3169 p_aggmap_operator bsc_aw_adapter_kpi.aggmap_operator_r,
3170 p_cubes dbms_sql.varchar2_table,
3171 p_partition_value varchar2
3172 ) is
3173 Begin
3174 bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' to NULL');
3175 for i in 1..p_cubes.count loop
3176 if p_partition_value is not null then
3177 bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' add '''||p_cubes(i)||' (PARTITION '||p_partition_value||')''');
3178 else
3179 bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' add '''||p_cubes(i)||'''');
3180 end if;
3181 end loop;
3182 Exception when others then
3183 log_n('Exception in limit_measure_dim '||sqlerrm);
3184 raise;
3185 End;
3186
3187 procedure reset_dim_limits(p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
3188 Begin
3189 --now, we reset the limit cubes
3190 --in 10g, we call these 2 api to after all the threads have completed
3191 --set status to allstat in reset_dim_limits. in 10g parallelism, the dim status here is not what we need.
3192 --so best to set the status to allstat
3193 bsc_aw_dbms_aw.execute('allstat');
3194 reset_dim_limit_cubes(p_dim_set.dim);
3195 reset_dim_limit_cubes(p_dim_set.std_dim);
3196 reset_calendar_limit_cubes(p_dim_set.calendar);
3197 Exception when others then
3198 log_n('Exception in reset_dim_limits '||sqlerrm);
3199 raise;
3200 End;
3201
3202 /*
3203 this procedure limits the dim to the lowest level of targets. say target is at state level.
3204 say geog is at city, state and vountry level. when this procedure executes, geog is
3205 limited to "KEEP" state
3206 p_actual_dim_set and p_target_dim_set will be related actual and target dim sets
3207 */
3208 procedure limit_dim_target_level_only(
3209 p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
3210 p_target_dim_set bsc_aw_adapter_kpi.dim_set_r
3211 ) is
3212 Begin
3213 for i in 1..p_actual_dim_set.dim.count loop
3214 for j in 1..p_target_dim_set.dim.count loop
3215 if p_target_dim_set.dim(j).dim_name=p_actual_dim_set.dim(i).dim_name then
3216 /*
3217 This is critical...if p_actual_dim_set.dim(i).levels.count>1 then
3218 encountered the issue when we did the change for handling hanging parent values. ie initial hier:
3219 A C changing to A C
3220 a b d a b d
3221 in this case the value for A in the cubes must be reset to 0. during this time,it was found that when we limit
3222 a concat dim to its level, when the concat had only 1 level, limited the value of the dim to the first value in
3223 the status. release dim had values 0,1,2 in status. then "KEEP" made the value of the concat dim just 0. this meant
3224 that the copy from the target to the actual did not happen and the values in the cube for targets were nulled out.
3225 they get nulled out before this during the actual agg. the copy is supposed to restore the value and then the
3226 subsequent agg will correct the higher level rollup for targets. if the copy does not happen, data is messed up for targets
3227 we keep the level if this is a normal dom with more than 1 level or if this is a rec dim implemented with denorm hier
3228 */
3229 if p_actual_dim_set.dim(i).levels.count>1 or
3230 (p_actual_dim_set.dim(i).recursive='Y' and p_actual_dim_set.dim(i).recursive_norm_hier='N') then
3231 limit_dim(p_actual_dim_set.dim(i).dim_name,p_target_dim_set.dim(j).levels(1).level_name,'KEEP');
3232 end if;
3233 exit;
3234 end if;
3235 end loop;
3236 end loop;
3237 Exception when others then
3238 log_n('Exception in limit_dim_target_level_only '||sqlerrm);
3239 raise;
3240 End;
3241
3242 procedure limit_cal_target_level_only(
3243 p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
3244 p_target_dim_set bsc_aw_adapter_kpi.dim_set_r
3245 ) is
3246 Begin
3247 --calendar
3248 --remove all levels that are not the lowest level
3249 for i in 1..p_target_dim_set.calendar.periodicity.count loop
3250 if p_target_dim_set.calendar.periodicity(i).lowest_level='Y' then
3251 limit_dim(p_target_dim_set.calendar.periodicity(i).aw_dim,p_actual_dim_set.calendar.aw_dim,'TO');
3252 end if;
3253 end loop;
3254 limit_dim(p_actual_dim_set.calendar.aw_dim,'NULL','TO');
3255 for i in 1..p_target_dim_set.calendar.periodicity.count loop
3256 if p_target_dim_set.calendar.periodicity(i).lowest_level='Y' then
3257 limit_dim(p_actual_dim_set.calendar.aw_dim,p_target_dim_set.calendar.periodicity(i).aw_dim,'ADD');
3258 end if;
3259 end loop;
3260 Exception when others then
3261 log_n('Exception in limit_cal_target_level_only '||sqlerrm);
3262 raise;
3263 End;
3264
3265 /*
3266 this procedure sets the limit cubes of dimensions to the value , if a composite is specified, it loops
3267 across it. used in aggregate_kpi_dimset_targets
3268 --
3269 earlier, we passed p_composite_name varchar2 as a parameter. this used to be p_target_dim_set.measure(1).composite_name
3270 but, each limit cube has its own composite. so its best to limit the limit cube to its own composite.
3271 9i and 10g, both have limit cube composite. N: limit cube composite has values only for the level at which data
3272 is loaded. but this is ok since we do not aggregate target cubes
3273 */
3274 procedure limit_dim_limit_cube(
3275 p_dim bsc_aw_adapter_kpi.dim_tb,
3276 p_value varchar2
3277 ) is
3278 Begin
3279 for i in 1..p_dim.count loop
3280 limit_dim_limit_cube(p_dim(i).limit_cube,p_value,p_dim(i).limit_cube_composite);
3281 end loop;
3282 Exception when others then
3283 log_n('Exception in limit_dim_limit_cube '||sqlerrm);
3284 raise;
3285 End;
3286
3287 --have to specify p_composite_name. limit cubes use named composites
3288 procedure limit_dim_limit_cube(
3289 p_limit_cube varchar2,
3290 p_value varchar2,
3291 p_composite_name varchar2
3292 ) is
3293 Begin
3294 g_stmt:=p_limit_cube||'='||p_value;
3295 if p_composite_name is not null then
3296 g_stmt:=g_stmt||' across '||p_composite_name;
3297 end if;
3298 bsc_aw_dbms_aw.execute(g_stmt);
3299 Exception when others then
3300 log_n('Exception in limit_dim_limit_cube '||sqlerrm);
3301 raise;
3302 End;
3303
3304 function get_projection_dim(p_dim_set bsc_aw_adapter_kpi.dim_set_r) return varchar2 is
3305 Begin
3306 return bsc_aw_adapter_kpi.get_projection_dim(p_dim_set);
3307 Exception when others then
3308 log_n('Exception in get_projection_dim '||sqlerrm);
3309 raise;
3310 End;
3311
3312 /*
3313 this function sees if agg is implemented on a dim. if this is a non rec dim and there is only 1 level and no zero code,
3314 there is no agg on it
3315 */
3316 function is_aggregation_on_dim(p_dim bsc_aw_adapter_kpi.dim_r) return boolean is
3317 Begin
3318 if p_dim.recursive='N' and p_dim.levels.count=1 then
3319 if p_dim.levels(1).zero_code='Y' then
3320 return true;
3321 else
3322 return false;
3323 end if;
3324 else
3325 return true;
3326 end if;
3327 Exception when others then
3328 log_n('Exception in is_aggregation_on_dim '||sqlerrm);
3329 raise;
3330 End;
3331
3332 /*
3333 this procedure sees if there is agg on calendar.
3334 looks to see if there is more than 1 periodicity
3335 */
3336 function is_aggregation_on_time(p_calendar bsc_aw_adapter_kpi.calendar_r) return boolean is
3337 Begin
3338 if p_calendar.periodicity.count>1 then
3339 return true;
3340 else
3341 return false;
3342 end if;
3343 Exception when others then
3344 log_n('Exception in is_aggregation_on_time '||sqlerrm);
3345 raise;
3346 End;
3347
3348 /*
3349 given a dim table and a dim, returns the index of where the dim was found
3350 */
3351 function get_dim_index(
3352 p_dim bsc_aw_adapter_kpi.dim_tb,
3353 p_dim_name varchar2
3354 )return number is
3355 Begin
3356 for i in 1..p_dim.count loop
3357 if p_dim(i).dim_name=p_dim_name then
3358 return i;
3359 end if;
3360 end loop;
3361 return null;
3362 Exception when others then
3363 log_n('Exception in get_dim_index '||sqlerrm);
3364 raise;
3365 End;
3366
3367 /*
3368 given a table of measure and a measure name, returns the index where it occured
3369 */
3370 function get_measure_index(
3371 p_measure bsc_aw_adapter_kpi.measure_tb,
3372 p_measure_name varchar2
3373 )return number is
3374 Begin
3375 for i in 1..p_measure.count loop
3376 if p_measure(i).measure=p_measure_name then
3377 return i;
3378 end if;
3379 end loop;
3380 return null;
3381 Exception when others then
3382 log_n('Exception in get_measure_index '||sqlerrm);
3383 raise;
3384 End;
3385
3386 /*
3387 when we use compressed composites, we cannot limit the dim values that make up the composite.
3388 we can limit the levels of the relation. its important to do so since we can have a kpi at the month level. without limiting
3389 the levels, aggregation will start from day. this means aggregate values will all be null
3390 */
3391 procedure limit_all_dim(p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
3392 Begin
3393 if p_dim_set.compressed='N' then
3394 limit_dim_values(p_dim_set.dim,'to');
3395 limit_dim_values(p_dim_set.std_dim,'to');
3396 limit_calendar_values(p_dim_set.calendar,'to');
3397 end if;
3398 limit_dim_levels(p_dim_set.dim); --l_aggregation.dim has the agg_level in it
3399 limit_calendar_levels(p_dim_set.calendar);--limit the periodicities
3400 Exception when others then
3401 log_n('Exception in limit_all_dim '||sqlerrm);
3402 raise;
3403 End;
3404
3405 /*
3406 calendar is getting special treatment compared to other dim. we do not load other dim when we load kpi.
3407 we need to handle the case where a user has already bsc implemented. they make no changes to their calendars. in this case,
3408 we need to create and load the calendars. its best if we load the calendar here
3409 */
3410 procedure load_calendar_if_needed(p_kpi varchar2) is
3411 --
3412 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3413 l_calendar number;
3414 l_lock_name varchar2(40);
3415 Begin
3416 bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
3417 for i in 1..l_olap_object_relation.count loop
3418 if l_olap_object_relation(i).relation_type='dim set calendar' then
3419 l_calendar:=to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'calendar',','));
3420 exit;
3421 end if;
3422 end loop;
3423 l_lock_name:='lock_aw_calendar_'||l_calendar;
3424 if l_calendar is not null then
3425 --serialize access
3426 bsc_aw_utility.get_db_lock(l_lock_name);
3427 if bsc_aw_calendar.check_calendar_loaded(l_calendar)='N' then
3428 --get lock for calendar
3429 bsc_aw_calendar.lock_calendar_objects(l_calendar);
3430 bsc_aw_calendar.load_calendar(l_calendar);
3431 bsc_aw_management.commit_aw;
3432 commit;
3433 end if;
3434 bsc_aw_utility.release_db_lock(l_lock_name);
3435 else
3436 log_n('Could not locate kpi calendar in load_calendar_if_needed');
3437 end if;
3438 Exception when others then
3439 bsc_aw_utility.release_db_lock(l_lock_name);
3440 log_n('Exception in load_calendar_if_needed '||sqlerrm);
3441 raise;
3442 End;
3443
3444 /*enh needed to load dim on demand. two scenarios. aw kpi created fresh. aw dim are empty. case II view based dim.
3445 */
3446 procedure load_dim_if_needed(p_kpi varchar2,p_dim_set dbms_sql.varchar2_table) is
3447 l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3448 l_dim dbms_sql.varchar2_table;
3449 Begin
3450 bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_oor);
3451 for i in 1..l_oor.count loop
3452 if l_oor(i).object_type='kpi dimension set' and (l_oor(i).relation_type='dim set dim' or l_oor(i).relation_type='dim set std dim')
3453 and l_oor(i).relation_object_type='dimension' then
3454 if bsc_aw_utility.in_array(p_dim_set,l_oor(i).object) then
3455 bsc_aw_utility.merge_value(l_dim,l_oor(i).relation_object);
3456 end if;
3457 end if;
3458 end loop;
3459 --
3460 bsc_aw_load_dim.load_dim_if_needed(l_dim);
3461 --
3462 Exception when others then
3463 log_n('Exception in load_dim_if_needed '||sqlerrm);
3464 raise;
3465 End;
3466
3467 procedure dmp_kpi_cubes_into_table(
3468 p_kpi varchar2,
3469 p_dimset varchar2,
3470 p_dim_levels dbms_sql.varchar2_table,
3471 p_table_name varchar2) is
3472 --
3473 l_name varchar2(200);
3474 Begin
3475 l_name:='dmp_kpi_'||p_kpi||'_'||p_dimset;
3476 bsc_aw_dbms_aw.execute_ne('delete '||l_name);
3477 bsc_aw_adapter_kpi.create_dmp_program(p_kpi,p_dimset,p_dim_levels,l_name,p_table_name);
3478 bsc_aw_dbms_aw.execute('call '||l_name);
3479 bsc_aw_dbms_aw.execute('delete '||l_name);
3480 Exception when others then
3481 log_n('Exception in dmp_kpi_cubes_into_table '||sqlerrm);
3482 raise;
3483 End;
3484
3485 /*
3486 pass a kpi. this will loop over all dimset, all dim and levels. it eill create tables
3487 as p_table_name||dimset||1,2 etc. then these table names will be returned in p_tables
3488 */
3489 procedure dmp_kpi_cubes_into_table(
3490 p_kpi varchar2,
3491 p_table_name varchar2,
3492 p_tables out nocopy dbms_sql.varchar2_table
3493 ) is
3494 --
3495 l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
3496 l_oo_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3497 l_dimset varchar2(100);
3498 l_dimset_name varchar2(200);
3499 l_combinations dbms_sql.varchar2_table;
3500 l_combinations_copy dbms_sql.varchar2_table;
3501 l_dimensions dbms_sql.varchar2_table;
3502 l_levels dbms_sql.varchar2_table;
3503 l_values bsc_aw_utility.value_tb;
3504 l_count number;
3505 Begin
3506 bsc_aw_md_api.get_kpi_dimset_actual(p_kpi,l_oo_dimset);
3507 --loop across the dimset
3508 for i in 1..l_oo_dimset.count loop
3509 l_dimset:=bsc_aw_utility.get_parameter_value(l_oo_dimset(i).property1,'dim set',',');
3510 l_dimset_name:=l_oo_dimset(i).object;
3511 l_count:=0;
3512 --get the dim
3513 l_combinations.delete;
3514 l_dimensions.delete;
3515 --
3516 l_oo_relation.delete;
3517 bsc_aw_md_api.get_bsc_olap_object_relation(l_dimset_name,'kpi dimension set','dim set dim',p_kpi,'kpi',l_oo_relation);
3518 for j in 1..l_oo_relation.count loop
3519 l_dimensions(l_dimensions.count+1):=l_oo_relation(j).relation_object;
3520 end loop;
3521 l_oo_relation.delete;
3522 bsc_aw_md_api.get_bsc_olap_object_relation(l_dimset_name,'kpi dimension set','dim set std dim',p_kpi,'kpi',l_oo_relation);
3523 for j in 1..l_oo_relation.count loop
3524 l_dimensions(l_dimensions.count+1):=l_oo_relation(j).relation_object;
3525 end loop;
3526 l_oo_relation.delete;
3527 bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'dim set dim level',p_kpi,'kpi',l_oo_relation);
3528 --loop over each dim
3529 for j in 1..l_dimensions.count loop
3530 l_levels.delete;
3531 for k in 1..l_oo_relation.count loop
3532 if l_oo_relation(k).object=l_dimensions(j)||'+'||l_dimset_name then
3533 l_levels(l_levels.count+1):=l_oo_relation(k).relation_object;
3534 end if;
3535 end loop;
3536 --
3537 if l_combinations.count=0 then
3538 --add the levels to the combinations
3539 for k in 1..l_levels.count loop
3540 l_combinations(l_combinations.count+1):=l_levels(k);
3541 end loop;
3542 else
3543 l_combinations_copy.delete;
3544 l_combinations_copy:=l_combinations;
3545 --now cartesian
3546 for k in 1..l_levels.count loop
3547 for m in 1..l_combinations_copy.count loop
3548 l_combinations(l_combinations.count+1):=l_combinations_copy(m)||','||l_levels(k);
3549 end loop;
3550 end loop;
3551 end if;
3552 end loop;
3553 --
3554 for j in 1..l_combinations.count loop
3555 l_levels.delete;
3556 l_values.delete;
3557 bsc_aw_utility.parse_parameter_values(l_combinations(j),',',l_values);
3558 for k in 1..l_values.count loop
3559 l_levels(l_levels.count+1):=l_values(k).parameter;
3560 end loop;
3561 if l_levels.count=l_dimensions.count then
3562 --each combination is a table
3563 l_count:=l_count+1;
3564 p_tables(p_tables.count+1):=p_table_name||'_'||l_dimset||'_'||l_count;
3565 dmp_kpi_cubes_into_table(p_kpi,l_dimset,l_levels,p_tables(p_tables.count));
3566 p_tables(p_tables.count):=p_tables(p_tables.count)||' ('||l_dimset_name||')-> '||l_combinations(j);
3567 end if;
3568 end loop;
3569 end loop;
3570 Exception when others then
3571 log_n('Exception in dmp_kpi_cubes_into_table '||sqlerrm);
3572 raise;
3573 End;
3574
3575 /*p_dimset of null means all dimsets
3576 null p_object_type means any object
3577 p_lock_type of inc means do not call bsc_aw_management.get_workspace_lock(). get_workspace_lock will do g_lockec_objects.delete
3578 it refreshes the internal info on current locked objects.
3579 inc means only go for additional locks.let the existing locks remain as they are
3580 */
3581 procedure lock_dimset_objects(p_kpi varchar2,p_dimset varchar2,p_object_type varchar2,p_lock_type varchar2) is
3582 --
3583 l_lock_objects dbms_sql.varchar2_table;
3584 Begin
3585 get_dimset_objects_to_lock(p_kpi,p_dimset,p_object_type,l_lock_objects);
3586 if p_lock_type='inc' then
3587 bsc_aw_management.get_lock(l_lock_objects,null);
3588 else
3589 bsc_aw_management.get_workspace_lock(l_lock_objects,null);
3590 end if;
3591 Exception when others then
3592 log_n('Exception in lock_dimset_objects '||sqlerrm);
3593 raise;
3594 End;
3595
3596 procedure get_dimset_objects_to_lock(
3597 p_kpi varchar2,
3598 p_dimset varchar2,
3599 p_object_type varchar2,
3600 p_lock_objects out nocopy dbms_sql.varchar2_table) is
3601 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
3602 l_objects dbms_sql.varchar2_table;
3603 Begin
3604 bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3605 for i in 1..l_bsc_olap_object.count loop
3606 if l_bsc_olap_object(i).olap_object_type='dimension' and l_bsc_olap_object(i).object_type<>'agg map measure dim'
3607 and l_bsc_olap_object(i).object_type<>'measurename dim' and
3608 (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3609 (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3610 l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3611 end if;
3612 end loop;
3613 for i in 1..l_bsc_olap_object.count loop
3614 if l_bsc_olap_object(i).olap_object_type='cube' and
3615 (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3616 (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3617 l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3618 end if;
3619 end loop;
3620 for i in 1..l_bsc_olap_object.count loop
3621 if l_bsc_olap_object(i).olap_object_type='variable' and
3622 (p_dimset is null or bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set name',',')=p_dimset) and
3623 (p_object_type is null or nvl(bsc_aw_utility.get_parameter_value(p_object_type,l_bsc_olap_object(i).object_type,','),'N')='Y') then
3624 l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
3625 end if;
3626 end loop;
3627 --
3628 for i in 1..l_objects.count loop
3629 if bsc_aw_utility.in_array(p_lock_objects,l_objects(i))=false then
3630 p_lock_objects(p_lock_objects.count+1):=l_objects(i);
3631 end if;
3632 end loop;
3633 Exception when others then
3634 log_n('Exception in get_dimset_objects_to_lock '||sqlerrm);
3635 raise;
3636 End;
3637
3638 /*
3639 given a base table list, get the list of dimsets
3640 p_base_table_list may not be a part of the kpi.
3641 in that case l_oor_dimset.count will be 0
3642 */
3643 procedure get_dimset_for_base_table(
3644 p_kpi varchar2,
3645 p_base_table_list dbms_sql.varchar2_table,
3646 p_dim_set out nocopy dbms_sql.varchar2_table
3647 ) is
3648 --
3649 l_oor_dimset bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3650 Begin
3651 for i in 1..p_base_table_list.count loop
3652 l_oor_dimset.delete;
3653 bsc_aw_md_api.get_base_table_dimset(p_kpi,p_base_table_list(i),'base table dim set',l_oor_dimset);
3654 for j in 1..l_oor_dimset.count loop
3655 if bsc_aw_utility.in_array(p_dim_set,l_oor_dimset(j).relation_object)=false then
3656 p_dim_set(p_dim_set.count+1):=l_oor_dimset(j).relation_object;
3657 end if;
3658 end loop;
3659 end loop;
3660 Exception when others then
3661 log_n('Exception in get_dimset_for_base_table '||sqlerrm);
3662 raise;
3663 End;
3664
3665 /*given a set of dimsets and B tables, finds out the list of B tables from p_base_table_list that belong to the dimset */
3666 procedure get_base_table_for_dimset(p_kpi varchar2,p_base_table_list dbms_sql.varchar2_table,p_dim_set dbms_sql.varchar2_table,
3667 p_dimset_base_tables out nocopy dbms_sql.varchar2_table) is
3668 l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3669 Begin
3670 for i in 1..p_dim_set.count loop
3671 l_oor.delete;
3672 bsc_aw_md_api.get_dimset_base_table(p_kpi,p_dim_set(i),'base table dim set',l_oor);
3673 for j in 1..l_oor.count loop
3674 if p_base_table_list.count=0 or bsc_aw_utility.in_array(p_base_table_list,l_oor(j).object) then
3675 bsc_aw_utility.merge_value(p_dimset_base_tables,l_oor(j).object);
3676 end if;
3677 end loop;
3678 end loop;
3679 Exception when others then
3680 log_n('Exception in get_base_table_for_dimset '||sqlerrm);
3681 raise;
3682 End;
3683
3684 /*
3685 we have this procedure so we can eliminate all base tables that are not part of the kpi
3686 p_base_table_list will have all the base tables.
3687 */
3688 procedure get_kpi_base_tables(
3689 p_kpi varchar2,
3690 p_base_table_list dbms_sql.varchar2_table,
3691 p_kpi_base_tables out nocopy dbms_sql.varchar2_table
3692 ) is
3693 --
3694 l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3695 l_base_table varchar2(100);
3696 Begin
3697 bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'base table dim set',p_kpi,'kpi',l_olap_object_relation);
3698 for i in 1..l_olap_object_relation.count loop
3699 l_base_table:=null;
3700 if p_base_table_list.count>0 then
3701 if bsc_aw_utility.in_array(p_base_table_list,l_olap_object_relation(i).object) then
3702 l_base_table:=l_olap_object_relation(i).object;
3703 end if;
3704 else
3705 l_base_table:=l_olap_object_relation(i).object;
3706 end if;
3707 if l_base_table is not null and bsc_aw_utility.in_array(p_kpi_base_tables,l_base_table)=false then
3708 p_kpi_base_tables(p_kpi_base_tables.count+1):=l_base_table;
3709 end if;
3710 end loop;
3711 Exception when others then
3712 log_n('Exception in get_kpi_base_tables '||sqlerrm);
3713 raise;
3714 End;
3715
3716 function get_cache_aggregation_r(p_kpi varchar2) return aggregation_r is
3717 Begin
3718 for i in 1..g_cache_aggregation_r.count loop
3719 if g_cache_aggregation_r(i).kpi=p_kpi then
3720 return g_cache_aggregation_r(i);
3721 end if;
3722 end loop;
3723 return null;
3724 Exception when others then
3725 log_n('Exception in get_cache_aggregation_r '||sqlerrm);
3726 raise;
3727 End;
3728
3729 function get_dim_set_index(p_aggregation aggregation_r,p_dim_set varchar2) return number is
3730 Begin
3731 for i in 1..p_aggregation.dim_set.count loop
3732 if p_aggregation.dim_set(i).dim_set_name=p_dim_set then
3733 return i;
3734 end if;
3735 end loop;
3736 log_n('Could not locate dimset '||p_dim_set||' in kpi ');
3737 raise bsc_aw_utility.g_exception;
3738 Exception when others then
3739 log_n('Exception in get_dim_set_index '||sqlerrm);
3740 raise;
3741 End;
3742
3743 /*
3744 used by aggregate_measure in dbms job mode to get the locks on the cubes given the measures
3745 we return
3746 cube of the measure
3747 N: in the case of partitions, we make the assumption that the objects returned from this api can lock the same
3748 partition with the same name. example cube(partition P0) and countvarcube(partition P0). P0 can belong to diff PT
3749 (compressed)
3750 */
3751 procedure get_measure_objects_to_lock(
3752 p_dim_set bsc_aw_adapter_kpi.dim_set_r,
3753 p_measures dbms_sql.varchar2_table,
3754 p_lock_objects out nocopy dbms_sql.varchar2_table
3755 ) is
3756 Begin
3757 for i in 1..p_dim_set.measure.count loop
3758 if bsc_aw_utility.in_array(p_measures,p_dim_set.measure(i).measure) then
3759 if p_dim_set.measure(i).cube is not null then
3760 if bsc_aw_utility.in_array(p_lock_objects,p_dim_set.measure(i).cube)=false then
3761 p_lock_objects(p_lock_objects.count+1):=p_dim_set.measure(i).cube;
3762 end if;
3763 end if;
3764 if p_dim_set.measure(i).countvar_cube is not null then
3765 if bsc_aw_utility.in_array(p_lock_objects,p_dim_set.measure(i).countvar_cube)=false then
3766 p_lock_objects(p_lock_objects.count+1):=p_dim_set.measure(i).countvar_cube;
3767 end if;
3768 end if;
3769 end if;
3770 end loop;
3771 Exception when others then
3772 log_n('Exception in get_measure_objects_to_lock '||sqlerrm);
3773 raise;
3774 End;
3775
3776 --overloaded. first find dim_set_r, then call get_measure_objects_to_lock
3777 procedure get_measure_objects_to_lock(
3778 p_kpi varchar2,
3779 p_dimset varchar2,
3780 p_measures dbms_sql.varchar2_table,
3781 p_lock_objects out nocopy dbms_sql.varchar2_table
3782 ) is
3783 --
3784 l_aggregation aggregation_r;
3785 l_index number;
3786 Begin
3787 set_aggregation(p_kpi,l_aggregation);
3788 l_index:=get_dim_set_index(l_aggregation,p_dimset);
3789 if l_index is null then
3790 log_n('Could not find dimset '||p_dimset||', kpi '||p_kpi||' in aggregation_r. Fatal!');
3791 raise bsc_aw_utility.g_exception;
3792 end if;
3793 get_measure_objects_to_lock(l_aggregation.dim_set(l_index),p_measures,p_lock_objects);
3794 Exception when others then
3795 log_n('Exception in get_measure_objects_to_lock '||sqlerrm);
3796 raise;
3797 End;
3798
3799 procedure insert_bsc_aw_temp_cv(p_min_value number,p_max_value number,p_base_table varchar2) is
3800 l_stmt varchar2(3000);
3801 Begin
3802 l_stmt:='insert into bsc_aw_temp_cv(change_vector_min_value,change_vector_max_value,change_vector_base_table) values(:1,:2,:3)';
3803 if g_debug then
3804 log(l_stmt||' using '||p_min_value||','||p_max_value||','||p_base_table);
3805 end if;
3806 execute immediate l_stmt using p_min_value,p_max_value,p_base_table;
3807 Exception when others then
3808 log_n('Exception in insert_bsc_aw_temp_cv '||sqlerrm);
3809 raise;
3810 End;
3811
3812 /*
3813 we will look at how many cubes are involved and whether partitions are involved
3814 */
3815 function can_launch_jobs(p_kpi varchar2,p_dimset bsc_aw_adapter_kpi.dim_set_r,p_measures dbms_sql.varchar2_table) return varchar2 is
3816 l_cubes dbms_sql.varchar2_table;
3817 l_pt_comp varchar2(200);
3818 l_pt_comp_type varchar2(200);
3819 l_number_jobs number;
3820 l_cube_set bsc_aw_adapter_kpi.cube_set_r;
3821 Begin
3822 if p_dimset.cube_design='single composite' then
3823 return 'N';
3824 else
3825 /*for the measures, get cubes. for the cubes get pt comps. we can parallelize for each distinct pt comp */
3826 for i in 1..p_measures.count loop
3827 l_cube_set:=bsc_aw_adapter_kpi.get_cube_set_for_measure(p_measures(i),p_dimset);
3828 if bsc_aw_utility.in_array(l_cubes,l_cube_set.cube.cube_name)=false then
3829 l_cubes(l_cubes.count+1):=l_cube_set.cube.cube_name;
3830 end if;
3831 end loop;
3832 l_number_jobs:=0;
3833 bsc_aw_utility.init_is_new_value(1);
3834 for i in 1..l_cubes.count loop
3835 l_pt_comp:=bsc_aw_adapter_kpi.get_cube_pt_comp(l_cubes(i),p_dimset,l_pt_comp_type);
3836 if l_pt_comp is not null then
3837 if bsc_aw_utility.is_new_value(l_pt_comp,1) then
3838 l_number_jobs:=l_number_jobs+1;
3839 end if;
3840 end if;
3841 if p_dimset.number_partitions>0 then
3842 l_number_jobs:=l_number_jobs*p_dimset.number_partitions;
3843 end if;
3844 end loop;
3845 return bsc_aw_utility.can_launch_jobs(l_number_jobs);
3846 end if;
3847 Exception when others then
3848 log_n('Exception in can_launch_jobs '||sqlerrm);
3849 raise;
3850 End;
3851
3852 procedure get_cubes_for_measures(
3853 p_measures dbms_sql.varchar2_table,
3854 p_dim_set bsc_aw_adapter_kpi.dim_set_r,
3855 p_cubes out nocopy dbms_sql.varchar2_table) is
3856 l_cube_set bsc_aw_adapter_kpi.cube_set_r;
3857 Begin
3858 for i in 1..p_measures.count loop
3859 l_cube_set:=bsc_aw_adapter_kpi.get_cube_set_for_measure(p_measures(i),p_dim_set);
3860 if bsc_aw_utility.in_array(p_cubes,l_cube_set.cube.cube_name)=false then
3861 p_cubes(p_cubes.count+1):=l_cube_set.cube.cube_name;
3862 end if;
3863 end loop;
3864 Exception when others then
3865 log_n('Exception in get_cubes_for_measures '||sqlerrm);
3866 raise;
3867 End;
3868
3869 procedure dmp_dimset_dim_statlen(p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
3870 l_val varchar2(2000);
3871 Begin
3872 for i in 1..p_dim_set.dim.count loop
3873 l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.dim(i).dim_name||')');
3874 end loop;
3875 for i in 1..p_dim_set.std_dim.count loop
3876 l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.std_dim(i).dim_name||')');
3877 end loop;
3878 l_val:=bsc_aw_dbms_aw.interp('show statlen('||p_dim_set.calendar.aw_dim||')');
3879 Exception when others then
3880 log_n('Exception in dmp_dimset_dim_statlen '||sqlerrm);
3881 raise;
3882 End;
3883
3884 /*
3885 given a list of base tables and the load program, api decides how many times to call the load program and what B table parameters to
3886 pass. if the DS has these entries : DS=B1+B2+B3+B1^B2^B3 then if the B tables to load are B2,B1 and B3, we will use B1,B2,B3 parameter
3887 the DS tables are stored in sorted order.
3888 */
3889 procedure get_ds_BT_parameters(
3890 p_kpi varchar2,
3891 p_dimset varchar2,
3892 p_load_program varchar2,
3893 p_b_tables dbms_sql.varchar2_table,
3894 p_ds_parameters out nocopy dbms_sql.varchar2_table) is
3895 --
3896 l_bt_string varchar2(4000);
3897 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
3898 l_ds_parameters dbms_sql.varchar2_table;
3899 l_ds_tables dbms_sql.varchar2_table;
3900 l_flag boolean;
3901 l_bt_flag dbms_sql.varchar2_table;
3902 Begin
3903 l_bt_string:=bsc_aw_utility.make_string_from_list(bsc_aw_utility.order_array(p_b_tables));
3904 --
3905 bsc_aw_md_api.get_bsc_olap_object(p_load_program,'dml program',p_kpi,'kpi',l_oo);
3906 bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_oo(1).property1,'DS',','),'+',l_ds_parameters);
3907 for i in 1..l_ds_parameters.count loop
3908 l_ds_parameters(i):=replace(l_ds_parameters(i),'^',',');
3909 end loop;
3910 if g_debug then
3911 log('For load program '||p_load_program||', DS Parameters found');
3912 for i in 1..l_ds_parameters.count loop
3913 log(l_ds_parameters(i));
3914 end loop;
3915 log('BT string='||l_bt_string);
3916 end if;
3917 --
3918 if l_ds_parameters.count=0 then
3919 p_ds_parameters(p_ds_parameters.count+1):=l_bt_string;
3920 end if;
3921 --
3922 --quick search to see if we can find the string in the DS
3923 if p_ds_parameters.count=0 then
3924 for i in 1..l_ds_parameters.count loop
3925 if l_ds_parameters(i)=l_bt_string then
3926 p_ds_parameters(p_ds_parameters.count+1):=l_ds_parameters(i);
3927 exit;
3928 end if;
3929 end loop;
3930 end if;
3931 --
3932 --see if all tables are in any DS
3933 if p_ds_parameters.count=0 then --more involved search..
3934 for i in 1..l_ds_parameters.count loop
3935 l_ds_tables.delete;
3936 l_flag:=true;
3937 bsc_aw_utility.parse_parameter_values(l_ds_parameters(i),',',l_ds_tables);
3938 for j in 1..p_b_tables.count loop
3939 if bsc_aw_utility.in_array(l_ds_tables,p_b_tables(j))=false then
3940 l_flag:=false;
3941 exit;
3942 end if;
3943 end loop;
3944 if l_flag then
3945 p_ds_parameters(p_ds_parameters.count+1):=l_ds_parameters(i);
3946 exit;
3947 end if;
3948 end loop;
3949 end if;
3950 --
3951 /*
3952 more complex. no DS contains all the tables in p_b_tables. this can be very common. dimset contains B1 and B2. B1 and B2 are
3953 week and month.
3954 the for i in reverse 1..l_ds_parameters.count loop is a quick fix. ideally, we need to find the DS with the best match
3955 */
3956 if p_ds_parameters.count=0 then
3957 for i in 1..p_b_tables.count loop
3958 l_bt_flag(i):='N';
3959 end loop;
3960 for i in reverse 1..l_ds_parameters.count loop
3961 l_ds_tables.delete;
3962 l_flag:=false;
3963 bsc_aw_utility.parse_parameter_values(l_ds_parameters(i),',',l_ds_tables);
3964 for j in 1..p_b_tables.count loop
3965 if l_bt_flag(j)='N' and bsc_aw_utility.in_array(l_ds_tables,p_b_tables(j)) then
3966 l_bt_flag(j):='Y';
3967 l_flag:=true;
3968 end if;
3969 end loop;
3970 if l_flag then
3971 p_ds_parameters(p_ds_parameters.count+1):=l_ds_parameters(i);
3972 end if;
3973 l_flag:=false;
3974 for j in 1..l_bt_flag.count loop
3975 if l_bt_flag(j)='N' then
3976 l_flag:=true;
3977 exit;
3978 end if;
3979 end loop;
3980 if l_flag=false then --we are done
3981 exit;
3982 end if;
3983 end loop;
3984 end if;
3985 if g_debug then
3986 log('get_ds_BT_parameters, DS parameters');
3987 for i in 1..p_ds_parameters.count loop
3988 log(p_ds_parameters(i));
3989 end loop;
3990 end if;
3991 Exception when others then
3992 log_n('Exception in get_ds_BT_parameters '||sqlerrm);
3993 raise;
3994 End;
3995
3996 /*this will check the base tables given and see if there is a current period change in the base table. if there is, we need to set the projection
3997 and balance measures to na
3998 we fire this for actual and target dimsets*/
3999 procedure check_bt_current_period_change(
4000 p_kpi varchar2,
4001 p_dim_set bsc_aw_adapter_kpi.dim_set_r,
4002 p_cubes dbms_sql.varchar2_table,
4003 p_measures dbms_sql.varchar2_table,
4004 p_base_tables dbms_sql.varchar2_table,
4005 p_bt_current_period dbms_sql.varchar2_table,
4006 p_options varchar2
4007 ) is
4008 --
4009 l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
4010 l_cube_measures bsc_aw_adapter_kpi.measure_tb;
4011 l_bt_periodicity dbms_sql.number_table;
4012 l_start_period dbms_sql.varchar2_table;
4013 l_end_period dbms_sql.varchar2_table;
4014 --
4015 type bt_r is record(
4016 table_name varchar2(100),
4017 periodicity number,
4018 current_period varchar2(40),
4019 ds_current_period varchar2(40),
4020 measures dbms_sql.varchar2_table
4021 );
4022 type bt_tb is table of bt_r index by pls_integer;
4023 l_base_tables bt_tb;
4024 --
4025 Begin
4026 if g_debug then
4027 log('check_bt_current_period_change for kpi '||p_kpi||', dimset '||p_dim_set.dim_set_name);
4028 log('Cubes');
4029 for i in 1..p_cubes.count loop
4030 log(p_cubes(i));
4031 end loop;
4032 log('Measures');
4033 for i in 1..p_measures.count loop
4034 log(p_measures(i));
4035 end loop;
4036 log('Base Tables and current period');
4037 for i in 1..p_base_tables.count loop
4038 log(p_base_tables(i)||' '||p_bt_current_period(i));
4039 end loop;
4040 log('Options '||p_options);
4041 log('--');
4042 end if;
4043 /*we do not have base table info in p_dim_set. so we have to go back to olap metadata */
4044 bsc_aw_md_api.get_dimset_base_table(p_kpi,p_dim_set.dim_set_name,'base table dim set',l_oor);
4045 /*load base table structures */
4046 for i in 1..p_base_tables.count loop
4047 l_base_tables(i).table_name:=p_base_tables(i);
4048 if p_bt_current_period(i) is not null and p_bt_current_period(i)<>'null' then
4049 l_base_tables(i).current_period:=p_bt_current_period(i);
4050 end if;
4051 for j in 1..l_oor.count loop
4052 if l_oor(j).object=p_base_tables(i) then
4053 l_base_tables(i).periodicity:=to_number(bsc_aw_utility.get_parameter_value(l_oor(j).property1,'base table periodicity',','));
4054 l_base_tables(i).ds_current_period:=bsc_aw_utility.get_parameter_value(l_oor(j).property1,'base table current period',',');
4055 bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_oor(j).property1,'measures',','),'+',
4056 l_base_tables(i).measures);
4057 exit;
4058 end if;
4059 end loop;
4060 end loop;
4061 /*we have to drive from cubes */
4062 for i in 1..p_cubes.count loop
4063 l_cube_measures.delete;
4064 l_bt_periodicity.delete;
4065 l_start_period.delete;
4066 l_end_period.delete;
4067 for j in 1..p_dim_set.measure.count loop
4068 if p_dim_set.measure(j).cube=p_cubes(i) and bsc_aw_utility.in_array(p_measures,p_dim_set.measure(j).measure) then
4069 l_cube_measures(l_cube_measures.count+1):=p_dim_set.measure(j);
4070 end if;
4071 end loop;
4072 for j in 1..l_base_tables.count loop
4073 if l_base_tables(j).current_period is not null and l_base_tables(j).ds_current_period is not null
4074 and l_base_tables(j).ds_current_period<>l_base_tables(j).current_period then
4075 for k in 1..l_cube_measures.count loop
4076 if bsc_aw_utility.in_array(l_base_tables(j).measures,l_cube_measures(k).measure) then
4077 l_bt_periodicity(l_bt_periodicity.count+1):=l_base_tables(j).periodicity;
4078 l_start_period(l_start_period.count+1):=l_base_tables(j).ds_current_period;
4079 l_end_period(l_end_period.count+1):=l_base_tables(j).current_period;
4080 exit;
4081 end if;
4082 end loop;
4083 end if;
4084 end loop;
4085 if l_cube_measures.count>0 and l_bt_periodicity.count>0 then
4086 check_bt_current_period_change(p_kpi,p_dim_set,p_cubes(i),l_cube_measures,l_bt_periodicity,l_start_period,l_end_period,p_options);
4087 else
4088 if g_debug then
4089 log('For cube '||p_cubes(i)||', no forecast or balance correction required for current period change in B table');
4090 end if;
4091 end if;
4092 end loop;
4093 Exception when others then
4094 log_n('Exception in check_bt_current_period_change '||sqlerrm);
4095 raise;
4096 End;
4097
4098 /*when we have projections or balance, we cannot have compressed composites */
4099 procedure check_bt_current_period_change(
4100 p_kpi varchar2,
4101 p_dim_set bsc_aw_adapter_kpi.dim_set_r,
4102 p_cube varchar2,
4103 p_measures bsc_aw_adapter_kpi.measure_tb,
4104 p_bt_periodicity dbms_sql.number_table, /*p_bt_periodicity,p_start_period and p_end_period match in count*/
4105 p_start_period dbms_sql.varchar2_table,
4106 p_end_period dbms_sql.varchar2_table,
4107 p_options varchar2 /*contains partition info */
4108 ) is
4109 --
4110 l_prj_measures bsc_aw_adapter_kpi.measure_tb;
4111 l_bal_measures bsc_aw_adapter_kpi.measure_tb;
4112 l_partition_dim_value varchar2(100);
4113 l_bt_periodicity bsc_aw_adapter_kpi.periodicity_tb;
4114 l_pt_name varchar2(100);
4115 l_pt_type varchar2(100);
4116 l_stmt varchar2(2000);
4117 Begin
4118 /*correct projections if the measures have projections in them
4119 when this api is called, locks are already in place
4120 */
4121 for i in 1..p_measures.count loop
4122 if p_measures(i).forecast='Y' then
4123 l_prj_measures(l_prj_measures.count+1):=p_measures(i);
4124 end if;
4125 /*if we have default balance columns ie balance based on last period, have to na the old current period */
4126 if p_measures(i).measure_type='BALANCE' then
4127 l_bal_measures(l_bal_measures.count+1):=p_measures(i);
4128 end if;
4129 end loop;
4130 l_partition_dim_value:=bsc_aw_utility.get_parameter_value(p_options,'partition dim value',',');
4131 for i in 1..p_bt_periodicity.count loop
4132 l_bt_periodicity(i):=bsc_aw_adapter_kpi.get_periodicity_r(p_dim_set.calendar.periodicity,p_bt_periodicity(i));
4133 end loop;
4134 l_pt_name:=bsc_aw_adapter_kpi.get_cube_pt_comp(p_cube,p_dim_set,l_pt_type);
4135 if l_prj_measures.count>0 or l_bal_measures.count>0 then
4136 push_dim(p_dim_set.calendar.aw_dim);
4137 bsc_aw_utility.init_is_new_value(1);
4138 for i in 1..l_bt_periodicity.count loop
4139 if bsc_aw_utility.is_new_value(l_bt_periodicity(i).aw_dim,1) then
4140 push_dim(l_bt_periodicity(i).aw_dim);
4141 end if;
4142 end loop;
4143 push_dim(get_projection_dim(p_dim_set));
4144 push_dim(p_dim_set.measurename_dim);
4145 if l_partition_dim_value is not null and p_dim_set.partition_dim is not null then
4146 push_dim(p_dim_set.partition_dim);
4147 end if;
4148 if l_partition_dim_value is not null and p_dim_set.partition_dim is not null then
4149 limit_dim(p_dim_set.partition_dim,l_partition_dim_value,'TO');
4150 end if;
4151 /*all other dimensions are limited to all */
4152 end if;
4153 /*set the projections */
4154 if l_prj_measures.count>0 then
4155 limit_dim(p_dim_set.calendar.aw_dim,'NULL','TO');
4156 for i in 1..l_bt_periodicity.count loop
4157 bsc_aw_dbms_aw.execute('limit '||l_bt_periodicity(i).aw_dim||' TO '''||p_start_period(i)||''' TO '''||p_end_period(i)||'''');
4158 limit_dim(p_dim_set.calendar.aw_dim,l_bt_periodicity(i).aw_dim,'ADD');
4159 end loop;
4160 limit_calendar_ancestors(p_dim_set.calendar,'ADD');
4161 bsc_aw_dbms_aw.execute('limit '||get_projection_dim(p_dim_set)||' to ''Y''');
4162 /*when we are here, there cannot be compressed composite.CC cannot be when there are projections or balance */
4163 limit_dim(p_dim_set.measurename_dim,'NULL','TO');
4164 for i in 1..l_prj_measures.count loop
4165 limit_dim(p_dim_set.measurename_dim,''''||l_prj_measures(i).measure||'''','ADD');
4166 end loop;
4167 if g_debug then
4168 dmp_dimset_dim_statlen(p_dim_set);
4169 end if;
4170 l_stmt:=p_cube||'=NA';
4171 if l_pt_name is not null then
4172 l_stmt:=l_stmt||' across '||l_pt_name; /*both pt and composite */
4173 end if;
4174 bsc_aw_dbms_aw.execute(l_stmt);
4175 end if;
4176 /*set the balance */
4177 if l_bal_measures.count>0 then
4178 limit_dim(p_dim_set.calendar.aw_dim,'NULL','TO');
4179 for i in 1..l_bt_periodicity.count loop
4180 bsc_aw_dbms_aw.execute('limit '||l_bt_periodicity(i).aw_dim||' TO '''||p_start_period(i)||'''');
4181 limit_dim(p_dim_set.calendar.aw_dim,l_bt_periodicity(i).aw_dim,'ADD');
4182 end loop;
4183 limit_calendar_ancestors(p_dim_set.calendar,'ADD');
4184 /*for balance, we cannot limit projection dim to Y */
4185 limit_dim(p_dim_set.measurename_dim,'NULL','TO');
4186 for i in 1..l_bal_measures.count loop
4187 limit_dim(p_dim_set.measurename_dim,''''||l_bal_measures(i).measure||'''','ADD');
4188 end loop;
4189 if g_debug then
4190 dmp_dimset_dim_statlen(p_dim_set);
4191 end if;
4192 l_stmt:=p_cube||'=NA';
4193 if l_pt_name is not null then
4194 l_stmt:=l_stmt||' across '||l_pt_name; /*both pt and composite */
4195 end if;
4196 bsc_aw_dbms_aw.execute(l_stmt);
4197 end if;
4198 /*restore dim status */
4199 if l_prj_measures.count>0 or l_bal_measures.count>0 then
4200 pop_dim(p_dim_set.calendar.aw_dim);
4201 bsc_aw_utility.init_is_new_value(1);
4202 for i in 1..l_bt_periodicity.count loop
4203 if bsc_aw_utility.is_new_value(l_bt_periodicity(i).aw_dim,1) then
4204 pop_dim(l_bt_periodicity(i).aw_dim);
4205 end if;
4206 end loop;
4207 pop_dim(get_projection_dim(p_dim_set));
4208 pop_dim(p_dim_set.measurename_dim);
4209 if l_partition_dim_value is not null and p_dim_set.partition_dim is not null then
4210 pop_dim(p_dim_set.partition_dim);
4211 end if;
4212 end if;
4213 Exception when others then
4214 log_n('Exception in check_bt_current_period_change '||sqlerrm);
4215 raise;
4216 End;
4217
4218 function dimset_has_bal_measures(p_dim_set bsc_aw_adapter_kpi.dim_set_r) return boolean is
4219 Begin
4220 for i in 1..p_dim_set.measure.count loop
4221 if substr(p_dim_set.measure(i).measure_type,1,7)='BALANCE' then
4222 return true;
4223 end if;
4224 end loop;
4225 return false;
4226 Exception when others then
4227 log_n('Exception in dimset_has_bal_measures '||sqlerrm);
4228 raise;
4229 End;
4230
4231 procedure dmp_dimset_composite_count(p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
4232 l_val varchar2(2000);
4233 Begin
4234 for i in 1..p_dim_set.composite.count loop
4235 l_val:=bsc_aw_dbms_aw.interp('show obj(dimmax '''||p_dim_set.composite(i).composite_name||''')');
4236 end loop;
4237 Exception when others then
4238 log_n('Exception in dmp_dimset_composite_count '||sqlerrm);
4239 raise;
4240 End;
4241
4242 /*need ability to control when to turn on parallel threads. parallelism enabled for large objects */
4243 /*for loads, we look at stats for the B tables and check the total count . if the stats is current, get an idea of count for this load */
4244 function is_parallel_load(p_base_tables dbms_sql.varchar2_table,p_cutoff number) return boolean is
4245 change_vector dbms_sql.number_table;
4246 Begin
4247 if p_base_tables.count>0 then
4248 for i in 1..p_base_tables.count loop
4249 change_vector(i):=bsc_aw_md_api.get_bt_change_vector(p_base_tables(i));
4250 end loop;
4251 return is_parallel_load(p_base_tables,change_vector,p_cutoff);
4252 else
4253 if g_debug then
4254 log('is_parallel_load(1), p_base_tables.count=0. Go for parallel');
4255 end if;
4256 return true;/*default is to parallelize */
4257 end if;
4258 Exception when others then
4259 log_n('Exception in is_parallel_load '||sqlerrm);
4260 raise;
4261 End;
4262
4263 function is_parallel_load(p_base_tables dbms_sql.varchar2_table,p_change_vector dbms_sql.number_table,p_cutoff number) return boolean is
4264 table_count number;
4265 total_table_count number;
4266 Begin
4267 if p_base_tables.count=0 then
4268 if g_debug then
4269 log('is_parallel_load(2), p_base_tables.count=0. Go for parallel');
4270 end if;
4271 return true;
4272 end if;
4273 total_table_count:=0;
4274 for i in 1..p_base_tables.count loop
4275 table_count:=get_table_load_count(p_base_tables(i),p_change_vector(i));
4276 if g_debug then
4277 log('For table '||p_base_tables(i)||' with change vector '||p_change_vector(i)||', Table Count='||table_count);
4278 end if;
4279 if table_count<0 then /*could not get the count */
4280 if g_debug then
4281 log('is_parallel_load, table_count<0. Go for parallel');
4282 end if;
4283 return true;/*go for parallel load. dont know if the table is small */
4284 end if;
4285 total_table_count:=total_table_count+table_count;
4286 end loop;
4287 if g_debug then
4288 log('is_parallel_load, total_table_count='||total_table_count||' and p_cutoff='||p_cutoff);
4289 end if;
4290 if total_table_count<=p_cutoff then
4291 return false;
4292 end if;
4293 return true;
4294 Exception when others then
4295 log_n('Exception in is_parallel_load '||sqlerrm);
4296 raise;
4297 End;
4298
4299 /*tries to get this load count. if the stats are old, returns -1 */
4300 function get_table_load_count(p_table varchar2,p_change_vector number) return number is
4301 all_tables bsc_aw_utility.all_tables_tb;
4302 table_count number;
4303 Begin
4304 all_tables:=bsc_aw_utility.get_db_table_parameters(p_table,bsc_aw_utility.get_table_owner(p_table));
4305 if all_tables(1).last_analyzed is null or (sysdate-all_tables(1).last_analyzed)>30 then
4306 return -1;/*no stats or too old stats. cannot get count */
4307 end if;
4308 if p_change_vector is null or p_change_vector=1 then /*for initial load, just get the table row count */
4309 return nvl(all_tables(1).NUM_ROWS,-1);
4310 else
4311 /*assume there is bitmap on the change vector */
4312 table_count:=bsc_aw_utility.get_table_count(p_table,'change_vector='||p_change_vector);
4313 return nvl(table_count,-1);
4314 end if;
4315 return -1;
4316 Exception when others then
4317 log_n('Exception in get_table_load_count '||sqlerrm);
4318 raise;
4319 End;
4320
4321 /*given a set of dimsets, checks total comp count to determine if parallel is true or false */
4322 function is_parallel_aggregate(p_dim_set bsc_aw_adapter_kpi.dim_set_tb,p_cutoff number) return boolean is
4323 comp_count number;
4324 total_comp_count number;
4325 Begin
4326 total_comp_count:=0;
4327 for i in 1..p_dim_set.count loop
4328 comp_count:=get_dimset_composite_count(p_dim_set(i));
4329 if comp_count<0 then
4330 if g_debug then
4331 log('is_parallel_aggregate, comp_count<0. Go for parallel');
4332 end if;
4333 return true;
4334 else
4335 total_comp_count:=total_comp_count+comp_count;
4336 end if;
4337 end loop;
4338 if g_debug then
4339 log('is_parallel_aggregate, total_comp_count='||total_comp_count||' and p_cutoff='||p_cutoff);
4340 end if;
4341 if total_comp_count<=p_cutoff then
4342 return false;
4343 end if;
4344 return true;
4345 Exception when others then
4346 log_n('Exception in is_parallel_aggregate '||sqlerrm);
4347 raise;
4348 End;
4349
4350 function is_parallel_aggregate(p_dim_set bsc_aw_adapter_kpi.dim_set_r,p_cutoff number) return boolean is
4351 comp_count number;
4352 Begin
4353 comp_count:=get_dimset_composite_count(p_dim_set);
4354 if comp_count<0 then /*could not determine comp count */
4355 if g_debug then
4356 log('is_parallel_aggregate, comp_count<0. Go for parallel aggregate');
4357 end if;
4358 return true;/*try parallel */
4359 end if;
4360 if g_debug then
4361 log('is_parallel_aggregate, comp_count='||comp_count||' and p_cutoff='||p_cutoff);
4362 end if;
4363 if comp_count<=p_cutoff then
4364 return false;
4365 end if;
4366 return true;
4367 Exception when others then
4368 log_n('Exception in is_parallel_aggregate '||sqlerrm);
4369 raise;
4370 End;
4371
4372 function get_dimset_composite_count(p_dim_set bsc_aw_adapter_kpi.dim_set_r) return number is
4373 l_val varchar2(2000);
4374 comp_count number;
4375 Begin
4376 comp_count:=0;
4377 for i in 1..p_dim_set.composite.count loop
4378 l_val:=bsc_aw_dbms_aw.interp('show obj(dimmax '''||p_dim_set.composite(i).composite_name||''')');
4379 if bsc_aw_utility.is_number(l_val) then
4380 comp_count:=comp_count+to_number(l_val);
4381 else
4382 return -1;
4383 end if;
4384 end loop;
4385 return comp_count;
4386 Exception when others then
4387 log_n('Exception in get_dimset_composite_count '||sqlerrm);
4388 raise;
4389 End;
4390
4391 ----------------------------------------------------
4392 procedure init_all is
4393 Begin
4394 g_debug:=bsc_aw_utility.g_debug;
4395 Exception when others then
4396 null;
4397 End;
4398
4399 procedure log(p_message varchar2) is
4400 Begin
4401 bsc_aw_utility.log(p_message);
4402 Exception when others then
4403 null;
4404 End;
4405
4406 procedure log_n(p_message varchar2) is
4407 Begin
4408 log(' ');
4409 log(p_message);
4410 Exception when others then
4411 null;
4412 End;
4413
4414 END BSC_AW_LOAD_KPI;
[Home] [Help]