[Home] [Help]
29: --
30: Begin
31: for i in 1..p_dim_level_list.count loop
32: bsc_aw_md_api.get_dim_for_level(p_dim_level_list(i),l_dim);
33: if bsc_aw_utility.in_array(l_dim_list,l_dim)=false then
34: l_dim_list(l_dim_list.count+1):=l_dim;
35: end if;
36: end loop;
37: if g_debug then
51: l_parallel boolean;
52: Begin
53: --if 10g, we can launcg parallel jobs.
54: l_parallel:=false;
55: if bsc_aw_utility.can_launch_jobs(p_dim_list.count)='Y' then
56: l_parallel:=true;
57: end if;
58: if l_parallel=false then
59: for i in 1..p_dim_list.count loop
81: l_load_dim.delete;
82: l_dim_lock.delete;
83: for i in 1..p_dim.count loop
84: l_dim_lock(l_dim_lock.count+1):='lock_aw_dim_'||p_dim(i);
85: bsc_aw_utility.get_db_lock(l_dim_lock(l_dim_lock.count));
86: if check_dim_loaded(p_dim(i))='N' or bsc_aw_adapter_dim.check_dim_view_based(p_dim(i))='Y' then
87: l_load_dim(l_load_dim.count+1):=p_dim(i);
88: else
89: bsc_aw_utility.release_db_lock(l_dim_lock(l_dim_lock.count));
85: bsc_aw_utility.get_db_lock(l_dim_lock(l_dim_lock.count));
86: if check_dim_loaded(p_dim(i))='N' or bsc_aw_adapter_dim.check_dim_view_based(p_dim(i))='Y' then
87: l_load_dim(l_load_dim.count+1):=p_dim(i);
88: else
89: bsc_aw_utility.release_db_lock(l_dim_lock(l_dim_lock.count));
90: l_dim_lock.delete(l_dim_lock.count);
91: end if;
92: end loop;
93: if g_debug then
99: --
100: load_dimensions(l_load_dim);--will parallelize if needed
101: --
102: for i in 1..l_dim_lock.count loop
103: bsc_aw_utility.release_db_lock(l_dim_lock(i));
104: end loop;
105: Exception when others then
106: for i in 1..l_dim_lock.count loop
107: bsc_aw_utility.release_db_lock(l_dim_lock(i));
103: bsc_aw_utility.release_db_lock(l_dim_lock(i));
104: end loop;
105: Exception when others then
106: for i in 1..l_dim_lock.count loop
107: bsc_aw_utility.release_db_lock(l_dim_lock(i));
108: end loop;
109: log_n('Exception in load_dim_if_needed '||sqlerrm);
110: raise;
111: End;
113: procedure load_aw_dim_jobs(p_dim_list dbms_sql.varchar2_table) is
114: --
115: l_job_name varchar2(100);
116: l_process varchar2(8000);
117: l_job_status bsc_aw_utility.parallel_job_tb;
118: l_exception exception;
119: pragma exception_init(l_exception,-20000);
120: --
121: Begin
118: l_exception exception;
119: pragma exception_init(l_exception,-20000);
120: --
121: Begin
122: bsc_aw_utility.clean_up_jobs('all');
123: for i in 1..p_dim_list.count loop
124: l_job_name:='bsc_aw_job_dim_'||bsc_aw_utility.get_session_id||'_'||i;
125: l_process:='bsc_aw_load_dim.load_aw_dim('''||p_dim_list(i)||''','||i||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
126: bsc_aw_utility.start_job(l_job_name,i,l_process,null);
120: --
121: Begin
122: bsc_aw_utility.clean_up_jobs('all');
123: for i in 1..p_dim_list.count loop
124: l_job_name:='bsc_aw_job_dim_'||bsc_aw_utility.get_session_id||'_'||i;
125: l_process:='bsc_aw_load_dim.load_aw_dim('''||p_dim_list(i)||''','||i||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
126: bsc_aw_utility.start_job(l_job_name,i,l_process,null);
127: end loop;
128: --wait (this will lock and wait)
121: Begin
122: bsc_aw_utility.clean_up_jobs('all');
123: for i in 1..p_dim_list.count loop
124: l_job_name:='bsc_aw_job_dim_'||bsc_aw_utility.get_session_id||'_'||i;
125: l_process:='bsc_aw_load_dim.load_aw_dim('''||p_dim_list(i)||''','||i||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
126: bsc_aw_utility.start_job(l_job_name,i,l_process,null);
127: end loop;
128: --wait (this will lock and wait)
129: bsc_aw_utility.wait_on_jobs(null,l_job_status);
122: bsc_aw_utility.clean_up_jobs('all');
123: for i in 1..p_dim_list.count loop
124: l_job_name:='bsc_aw_job_dim_'||bsc_aw_utility.get_session_id||'_'||i;
125: l_process:='bsc_aw_load_dim.load_aw_dim('''||p_dim_list(i)||''','||i||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
126: bsc_aw_utility.start_job(l_job_name,i,l_process,null);
127: end loop;
128: --wait (this will lock and wait)
129: bsc_aw_utility.wait_on_jobs(null,l_job_status);
130: --check the status
125: l_process:='bsc_aw_load_dim.load_aw_dim('''||p_dim_list(i)||''','||i||','''||l_job_name||''','''||bsc_aw_utility.get_option_string||''');';
126: bsc_aw_utility.start_job(l_job_name,i,l_process,null);
127: end loop;
128: --wait (this will lock and wait)
129: bsc_aw_utility.wait_on_jobs(null,l_job_status);
130: --check the status
131: for i in 1..l_job_status.count loop
132: if l_job_status(i).status='error' then
133: --raise error
162: --for the dim, get the program name,
163: --get the lock on the dim objects
164: if p_run_id is not null then
165: --this is a dbms job. we have to do the initializations since this is a new session
166: bsc_aw_utility.g_options.delete;
167: bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
168: bsc_aw_utility.open_file(p_dim||'_'||p_run_id);
169: bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
170: bsc_aw_utility.init_all_procedures;
163: --get the lock on the dim objects
164: if p_run_id is not null then
165: --this is a dbms job. we have to do the initializations since this is a new session
166: bsc_aw_utility.g_options.delete;
167: bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
168: bsc_aw_utility.open_file(p_dim||'_'||p_run_id);
169: bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
170: bsc_aw_utility.init_all_procedures;
171: end if;
164: if p_run_id is not null then
165: --this is a dbms job. we have to do the initializations since this is a new session
166: bsc_aw_utility.g_options.delete;
167: bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
168: bsc_aw_utility.open_file(p_dim||'_'||p_run_id);
169: bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
170: bsc_aw_utility.init_all_procedures;
171: end if;
172: log_n('load_aw_dim, p_dim='||p_dim||', p_run_id='||p_run_id||', p_job_name='||p_job_name||
165: --this is a dbms job. we have to do the initializations since this is a new session
166: bsc_aw_utility.g_options.delete;
167: bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
168: bsc_aw_utility.open_file(p_dim||'_'||p_run_id);
169: bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
170: bsc_aw_utility.init_all_procedures;
171: end if;
172: log_n('load_aw_dim, p_dim='||p_dim||', p_run_id='||p_run_id||', p_job_name='||p_job_name||
173: ', p_options='||p_options);
166: bsc_aw_utility.g_options.delete;
167: bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
168: bsc_aw_utility.open_file(p_dim||'_'||p_run_id);
169: bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
170: bsc_aw_utility.init_all_procedures;
171: end if;
172: log_n('load_aw_dim, p_dim='||p_dim||', p_run_id='||p_run_id||', p_job_name='||p_job_name||
173: ', p_options='||p_options);
174: l_oo.delete;
195: merge_delete_values_to_levels(l_dim_level_delete);
196: end if;
197: l_oo.delete;
198: /*if this is a rec dim and implemented with normal hier, we need to populate bsc_aw_temp_pc */
199: if nvl(bsc_aw_utility.get_parameter_value(l_dim_property,'recursive',','),'N')='Y' and
200: nvl(bsc_aw_utility.get_parameter_value(l_dim_property,'normal hier',','),'N')='Y' then
201: l_denorm_src:=bsc_aw_utility.get_parameter_value(l_dim_property,'denorm source',',');
202: if l_denorm_src is not null then --dbi rec dim
203: load_recursive_norm_hier(replace(l_denorm_src,'*^',','),
196: end if;
197: l_oo.delete;
198: /*if this is a rec dim and implemented with normal hier, we need to populate bsc_aw_temp_pc */
199: if nvl(bsc_aw_utility.get_parameter_value(l_dim_property,'recursive',','),'N')='Y' and
200: nvl(bsc_aw_utility.get_parameter_value(l_dim_property,'normal hier',','),'N')='Y' then
201: l_denorm_src:=bsc_aw_utility.get_parameter_value(l_dim_property,'denorm source',',');
202: if l_denorm_src is not null then --dbi rec dim
203: load_recursive_norm_hier(replace(l_denorm_src,'*^',','),
204: bsc_aw_utility.get_parameter_value(l_dim_property,'child col',','),
197: l_oo.delete;
198: /*if this is a rec dim and implemented with normal hier, we need to populate bsc_aw_temp_pc */
199: if nvl(bsc_aw_utility.get_parameter_value(l_dim_property,'recursive',','),'N')='Y' and
200: nvl(bsc_aw_utility.get_parameter_value(l_dim_property,'normal hier',','),'N')='Y' then
201: l_denorm_src:=bsc_aw_utility.get_parameter_value(l_dim_property,'denorm source',',');
202: if l_denorm_src is not null then --dbi rec dim
203: load_recursive_norm_hier(replace(l_denorm_src,'*^',','),
204: bsc_aw_utility.get_parameter_value(l_dim_property,'child col',','),
205: bsc_aw_utility.get_parameter_value(l_dim_property,'parent col',','));
200: nvl(bsc_aw_utility.get_parameter_value(l_dim_property,'normal hier',','),'N')='Y' then
201: l_denorm_src:=bsc_aw_utility.get_parameter_value(l_dim_property,'denorm source',',');
202: if l_denorm_src is not null then --dbi rec dim
203: load_recursive_norm_hier(replace(l_denorm_src,'*^',','),
204: bsc_aw_utility.get_parameter_value(l_dim_property,'child col',','),
205: bsc_aw_utility.get_parameter_value(l_dim_property,'parent col',','));
206: end if;
207: end if;
208: /*4646329 for some reason, the dim levels were missing the value 0. maybe the dim was purged. we need a way to get the std values back into the dim
201: l_denorm_src:=bsc_aw_utility.get_parameter_value(l_dim_property,'denorm source',',');
202: if l_denorm_src is not null then --dbi rec dim
203: load_recursive_norm_hier(replace(l_denorm_src,'*^',','),
204: bsc_aw_utility.get_parameter_value(l_dim_property,'child col',','),
205: bsc_aw_utility.get_parameter_value(l_dim_property,'parent col',','));
206: end if;
207: end if;
208: /*4646329 for some reason, the dim levels were missing the value 0. maybe the dim was purged. we need a way to get the std values back into the dim
209: we do this for std and custom dim for now. projection dim is not loaded via program. now we assume text datatype. later we can have property as
240: --if this is a job, send success message to pipe...update bsc_olap_object saying operation_flag='loaded'
241: mark_dim_loaded(p_dim);
242: --
243: if p_run_id is not null then
244: bsc_aw_utility.send_pipe_message(p_job_name,'status=success');
245: end if;
246: commit;
247: Exception when others then
248: log_n('Exception in load_aw_dim '||sqlerrm);
246: commit;
247: Exception when others then
248: log_n('Exception in load_aw_dim '||sqlerrm);
249: if p_run_id is not null then
250: bsc_aw_utility.send_pipe_message(p_job_name,'status=error,sqlcode='||sqlcode||',message='||sqlerrm);
251: rollback;
252: bsc_aw_management.detach_workspace;
253: else
254: raise;
286: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);
287: --
288: if l_oo.count>0 then
289: for i in 1..l_oo.count loop
290: bsc_aw_utility.merge_value(p_dim_level_delete('ALL').delete_values,l_oo(i).object);--ALL will hold the levels involved
291: load_delete_dim_level_value(l_oo(i).object,upper(l_oo(i).object),p_dim_level_delete);
292: if p_dim_level_delete(l_oo(i).object).delete_values.count>0 then
293: p_delete_flag:=true;
294: end if;
293: p_delete_flag:=true;
294: end if;
295: end loop;
296: --
297: if nvl(bsc_aw_utility.get_parameter_value(p_dim_property,'recursive',','),'N')='Y' then
298: l_level:=l_oo(1).object;
299: l_oo.delete;
300: bsc_aw_md_api.get_bsc_olap_object(null,'recursive level',p_dim,'dimension',l_oo);
301: for i in 1..l_oo.count loop
298: l_level:=l_oo(1).object;
299: l_oo.delete;
300: bsc_aw_md_api.get_bsc_olap_object(null,'recursive level',p_dim,'dimension',l_oo);
301: for i in 1..l_oo.count loop
302: bsc_aw_utility.merge_value(p_dim_level_delete('ALL').delete_values,l_oo(i).object);--ALL will hold the levels involved
303: load_delete_dim_level_value(l_oo(i).object,upper(l_level),p_dim_level_delete);
304: end loop;
305: end if;
306: end if;
319: cv CurTyp;
320: Begin
321: l_stmt:='select distinct delete_value from bsc_aw_dim_delete where dim_level=:1';
322: if g_debug then
323: log(l_stmt||' '||p_select_level||bsc_aw_utility.get_time);
324: end if;
325: p_dim_level_delete(p_dim_level).delete_name:=p_dim_level;
326: p_dim_level_delete(p_dim_level).delete_values.delete;
327: open cv for l_stmt using p_select_level;
329: fetch cv bulk collect into p_dim_level_delete(p_dim_level).delete_values;
330: exit when cv%notfound;
331: end loop;
332: if g_debug then
333: log('Fetched '||p_dim_level_delete(p_dim_level).delete_values.count||' rows'||bsc_aw_utility.get_time);
334: end if;
335: close cv;
336: Exception when others then
337: log_n('Exception in load_delete_dim_level_value '||sqlerrm);
370: l_levels:=p_dim_level_delete('ALL').delete_values;
371: for i in 1..l_levels.count loop
372: l_stmt:='delete bsc_aw_dim_delete where dim_level=:1';
373: if g_debug then
374: log(l_stmt||' '||l_levels(i)||bsc_aw_utility.get_time);
375: end if;
376: execute immediate l_stmt using l_levels(i);
377: if g_debug then
378: log('Deleted '||sql%rowcount||' rows '||bsc_aw_utility.get_time);
374: log(l_stmt||' '||l_levels(i)||bsc_aw_utility.get_time);
375: end if;
376: execute immediate l_stmt using l_levels(i);
377: if g_debug then
378: log('Deleted '||sql%rowcount||' rows '||bsc_aw_utility.get_time);
379: end if;
380: end loop;
381: Exception when others then
382: log_n('Exception in clean_bsc_aw_dim_delete '||sqlerrm);
403: l_dim varchar2(300);
404: Begin
405: for i in 1..p_dim_level_list.count loop
406: bsc_aw_md_api.get_dim_for_level(p_dim_level_list(i),l_dim);
407: if bsc_aw_utility.in_array(l_dim_list,l_dim)=false then
408: l_dim_list(l_dim_list.count+1):=l_dim;
409: end if;
410: end loop;
411: for i in 1..l_dim_list.count loop
646: l_objects(l_objects.count+1):=l_kpi_aggregate_markers(i);
647: end loop;
648: --
649: for i in 1..l_objects.count loop
650: if bsc_aw_utility.in_array(p_lock_objects,l_objects(i))=false then
651: p_lock_objects(p_lock_objects.count+1):=l_objects(i);
652: end if;
653: end loop;
654: Exception when others then
673: --
674: l_stmt varchar2(8000);
675: Begin
676: if p_denorm_source is not null then
677: bsc_aw_utility.execute_stmt('delete bsc_aw_temp_pc');
678: bsc_aw_utility.execute_stmt('delete bsc_aw_temp_vn');
679: --
680: l_stmt:='insert into bsc_aw_temp_vn(name,id) select '||p_child_col||',count(*) from '||p_denorm_source||' group by '||p_child_col;
681: bsc_aw_utility.execute_stmt(l_stmt);
674: l_stmt varchar2(8000);
675: Begin
676: if p_denorm_source is not null then
677: bsc_aw_utility.execute_stmt('delete bsc_aw_temp_pc');
678: bsc_aw_utility.execute_stmt('delete bsc_aw_temp_vn');
679: --
680: l_stmt:='insert into bsc_aw_temp_vn(name,id) select '||p_child_col||',count(*) from '||p_denorm_source||' group by '||p_child_col;
681: bsc_aw_utility.execute_stmt(l_stmt);
682: --
677: bsc_aw_utility.execute_stmt('delete bsc_aw_temp_pc');
678: bsc_aw_utility.execute_stmt('delete bsc_aw_temp_vn');
679: --
680: l_stmt:='insert into bsc_aw_temp_vn(name,id) select '||p_child_col||',count(*) from '||p_denorm_source||' group by '||p_child_col;
681: bsc_aw_utility.execute_stmt(l_stmt);
682: --
683: l_stmt:='insert into bsc_aw_temp_pc(parent,child,id) select '||p_parent_col||','||p_child_col||',rank() over(partition by '||
684: p_child_col||' order by '||p_parent_col||') from (select denorm.'||p_parent_col||',denorm.'||p_child_col||' from '||
685: p_denorm_source||' denorm,bsc_aw_temp_vn t1,bsc_aw_temp_vn t2 where denorm.'||p_parent_col||'=t1.name(+) and '||
683: l_stmt:='insert into bsc_aw_temp_pc(parent,child,id) select '||p_parent_col||','||p_child_col||',rank() over(partition by '||
684: p_child_col||' order by '||p_parent_col||') from (select denorm.'||p_parent_col||',denorm.'||p_child_col||' from '||
685: p_denorm_source||' denorm,bsc_aw_temp_vn t1,bsc_aw_temp_vn t2 where denorm.'||p_parent_col||'=t1.name(+) and '||
686: 'denorm.'||p_child_col||'=t2.name and t2.id=nvl(t1.id,0)+1)';
687: bsc_aw_utility.execute_stmt(l_stmt);
688: end if;
689: Exception when others then
690: log_n('Exception in load_recursive_norm_hier '||sqlerrm);
691: raise;
693:
694: function check_dim_loaded(p_dim varchar2) return varchar2 is
695: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
696: Begin
697: if bsc_aw_utility.in_array(bsc_aw_adapter_dim.get_preloaded_dim_list,p_dim)=false then
698: bsc_aw_md_api.get_bsc_olap_object(p_dim,'dimension',p_dim,'dimension',l_oo);
699: if l_oo(1).operation_flag is not null and l_oo(1).operation_flag='loaded' then
700: return 'Y';
701: else
731: l_dim_list dbms_sql.varchar2_table;
732: Begin
733: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',null,'dimension',l_oo);
734: for i in 1..l_oo.count loop
735: if bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity',',') is null then --this is not a calendar dim level
736: bsc_aw_md_api.get_dim_for_level(l_oo(i).object,l_dim);
737: bsc_aw_utility.merge_value(l_dim_list,l_dim);
738: end if;
739: end loop;
733: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',null,'dimension',l_oo);
734: for i in 1..l_oo.count loop
735: if bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity',',') is null then --this is not a calendar dim level
736: bsc_aw_md_api.get_dim_for_level(l_oo(i).object,l_dim);
737: bsc_aw_utility.merge_value(l_dim_list,l_dim);
738: end if;
739: end loop;
740: --
741: for i in 1..l_dim_list.count loop
799:
800: ------------------------------------------
801: procedure init_all is
802: Begin
803: g_debug:=bsc_aw_utility.g_debug;
804: Exception when others then
805: log_n('Exception in init_all '||sqlerrm);
806: raise;
807: End;
807: End;
808:
809: procedure log(p_message varchar2) is
810: Begin
811: bsc_aw_utility.log(p_message);
812: Exception when others then
813: null;
814: End;
815: