[Home] [Help]
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
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;
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||''');';
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;
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
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
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;
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);
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;
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
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;
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;
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;
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;
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||
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);
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: --
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
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
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;
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
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
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;
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;
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||''','''||
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);
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);
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
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
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;
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);
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;
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;
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;
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);
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
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');
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
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;
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
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;
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;
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;
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;
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';
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,
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,
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
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);
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;
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
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;
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 */
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);
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;
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;
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;
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)||',';
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)||',';
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||'),';
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);
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;
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)||',';
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)||''','''||
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);
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;
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: /*
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: --
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);
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;
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');
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;
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
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');
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');
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;
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: --
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);
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
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;
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;
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;
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);
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: --
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
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
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);
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
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);
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
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;
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';
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;
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||''');';
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;
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
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
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;
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);
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;
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;
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
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);
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
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;
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
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;
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;
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;
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);
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
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
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));
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
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);
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 */
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;
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;
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);
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;
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;
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;
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;
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;
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;
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;
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 */
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 */
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);
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));
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));
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
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
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;
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);
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);
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);
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;
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;
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
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: --
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;
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);
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;
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;
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;
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);
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;
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);
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
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;
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');
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;
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;
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: --
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);
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: --
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);
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||')';
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;
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
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;
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',',');
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
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
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';
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;
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
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;
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);
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
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*/
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);
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;
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
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);
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
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
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
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);
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;
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');
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;
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);
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;
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
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);
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);
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
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
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
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);
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;
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);
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
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:
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;
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: --
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
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;
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
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;
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
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;
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
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;
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: --
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
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;
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;
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
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;
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
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;
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;
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
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;
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);
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;
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
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
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;
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
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
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
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;
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;
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;
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;
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;
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;
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;
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
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;
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);
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;
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));
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;
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));
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
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 */
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
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;
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:
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: