[Home] [Help]
35: default_context_if_null;
36: if g_debug then
37: log('Drop metadata for Dim '||p_dim_name);
38: end if;
39: delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;
40: --with the concept of level groups, we do not remove snowflake implementation
41: --delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object in (select object from
42: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
43: --and context=g_context)
37: log('Drop metadata for Dim '||p_dim_name);
38: end if;
39: delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;
40: --with the concept of level groups, we do not remove snowflake implementation
41: --delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object in (select object from
42: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
43: --and context=g_context)
44: --and context=g_context;
45: --delete bsc_olap_object where parent_object_type='dimension' and parent_object in (select object from
214: if g_debug then
215: log('drop kpi metadata '||p_kpi||bsc_aw_utility.get_time);
216: end if;
217: if p_kpi is not null then
218: delete bsc_olap_object_relation where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
219: delete bsc_olap_object_relation where object=p_kpi and object_type='kpi' and relation_object=p_kpi and relation_object_type='kpi' and
220: relation_type='dimension kpi';
221: delete bsc_olap_object where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
222: end if;
215: log('drop kpi metadata '||p_kpi||bsc_aw_utility.get_time);
216: end if;
217: if p_kpi is not null then
218: delete bsc_olap_object_relation where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
219: delete bsc_olap_object_relation where object=p_kpi and object_type='kpi' and relation_object=p_kpi and relation_object_type='kpi' and
220: relation_type='dimension kpi';
221: delete bsc_olap_object where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
222: end if;
223: Exception when others then
381: obj not specified. rel type not specified. par obj specified
382: obj not specified. rel type specified. par obj specified
383: obj specified. rel type not specified. par obj not specified
384: */
385: procedure get_bsc_olap_object_relation(
386: p_object varchar2,
387: p_object_type varchar2,
388: p_relation_type varchar2,
389: p_parent_object varchar2,
387: p_object_type varchar2,
388: p_relation_type varchar2,
389: p_parent_object varchar2,
390: p_parent_object_type varchar2,
391: p_bsc_olap_object_relation out nocopy bsc_olap_object_relation_tb
392: ) is
393: --
394: cursor c1 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
395: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
390: p_parent_object_type varchar2,
391: p_bsc_olap_object_relation out nocopy bsc_olap_object_relation_tb
392: ) is
393: --
394: cursor c1 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
395: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
396: and context=g_context;
397: cursor c2 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
398: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
393: --
394: cursor c1 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
395: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
396: and context=g_context;
397: cursor c2 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
398: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
399: and relation_type=p_relation_type and context=g_context;
400: cursor c3 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
401: and relation_type=p_relation_type and context=g_context;
396: and context=g_context;
397: cursor c2 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
398: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
399: and relation_type=p_relation_type and context=g_context;
400: cursor c3 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
401: and relation_type=p_relation_type and context=g_context;
402: cursor c4 is select * from bsc_olap_object_relation where parent_object=p_parent_object
403: and parent_object_type=p_parent_object_type
404: and context=g_context;
398: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
399: and relation_type=p_relation_type and context=g_context;
400: cursor c3 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
401: and relation_type=p_relation_type and context=g_context;
402: cursor c4 is select * from bsc_olap_object_relation where parent_object=p_parent_object
403: and parent_object_type=p_parent_object_type
404: and context=g_context;
405: cursor c5 is select * from bsc_olap_object_relation where relation_type=p_relation_type
406: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
401: and relation_type=p_relation_type and context=g_context;
402: cursor c4 is select * from bsc_olap_object_relation where parent_object=p_parent_object
403: and parent_object_type=p_parent_object_type
404: and context=g_context;
405: cursor c5 is select * from bsc_olap_object_relation where relation_type=p_relation_type
406: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
407: and context=g_context;
408: cursor c6 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
409: and context=g_context;
404: and context=g_context;
405: cursor c5 is select * from bsc_olap_object_relation where relation_type=p_relation_type
406: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
407: and context=g_context;
408: cursor c6 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
409: and context=g_context;
410: --
411: Begin
412: default_context_if_null;
410: --
411: Begin
412: default_context_if_null;
413: if g_debug and bsc_aw_utility.g_debug_level='all' then
414: log(' get_bsc_olap_object_relation object='||p_object||' type='||p_object_type||' rel_type='||
415: p_relation_type||' par_obj='||p_parent_object||' par_obj_type='||p_parent_object_type);
416: end if;
417: if p_object is not null and p_relation_type is null and p_parent_object is not null then
418: open c1;
416: end if;
417: if p_object is not null and p_relation_type is null and p_parent_object is not null then
418: open c1;
419: loop
420: fetch c1 bulk collect into p_bsc_olap_object_relation;
421: exit when c1%notfound;
422: end loop;
423: close c1;
424: elsif p_object is not null and p_relation_type is not null and p_parent_object is not null then
423: close c1;
424: elsif p_object is not null and p_relation_type is not null and p_parent_object is not null then
425: open c2;
426: loop
427: fetch c2 bulk collect into p_bsc_olap_object_relation;
428: exit when c2%notfound;
429: end loop;
430: close c2;
431: elsif p_object is not null and p_relation_type is not null and p_parent_object is null then
430: close c2;
431: elsif p_object is not null and p_relation_type is not null and p_parent_object is null then
432: open c3;
433: loop
434: fetch c3 bulk collect into p_bsc_olap_object_relation;
435: exit when c3%notfound;
436: end loop;
437: close c3;
438: elsif p_object is null and p_relation_type is null and p_parent_object is not null then
437: close c3;
438: elsif p_object is null and p_relation_type is null and p_parent_object is not null then
439: open c4;
440: loop
441: fetch c4 bulk collect into p_bsc_olap_object_relation;
442: exit when c4%notfound;
443: end loop;
444: close c4;
445: elsif p_object is null and p_relation_type is not null and p_parent_object is not null then
444: close c4;
445: elsif p_object is null and p_relation_type is not null and p_parent_object is not null then
446: open c5;
447: loop
448: fetch c5 bulk collect into p_bsc_olap_object_relation;
449: exit when c5%notfound;
450: end loop;
451: close c5;
452: elsif p_object is not null and p_relation_type is null and p_parent_object is null then
451: close c5;
452: elsif p_object is not null and p_relation_type is null and p_parent_object is null then
453: open c6;
454: loop
455: fetch c6 bulk collect into p_bsc_olap_object_relation;
456: exit when c6%notfound;
457: end loop;
458: close c6;
459: end if;
457: end loop;
458: close c6;
459: end if;
460: Exception when others then
461: log_n('Exception in get_bsc_olap_object_relation '||sqlerrm);
462: raise;
463: End;
464:
465: procedure create_kpi(p_kpi bsc_aw_adapter_kpi.kpi_r) is
487: procedure create_kpi(p_kpi varchar2,p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
488: l_property varchar2(4000);
489: l_comp_added dbms_sql.varchar2_table; --so we insert into oo distinct composite names
490: l_dimset_name_property varchar2(400);
491: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
492: Begin
493: default_context_if_null;
494: --=======bsc olap objects====================
495: --we need to have all the objects entered in bsc_olap_object that needs to be dropped
665: insert_olap_object(p_dim_set.z_s_view(i).s_view,'relational view',null,null,p_kpi,'kpi',l_dimset_name_property||'zero code');
666: end if;
667: end loop;
668: --
669: --=======bsc olap object relations===========
670: /*
671: we enter the following
672: given a dim set, the dimensions. for each dim, the levels. for each dim set, the cubes and the programs.
673: we also say if the level has zero code
923: end loop;
924: insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',
925: p_dim_set.dim_set_name,'dimension set','base table dim set',p_kpi,'kpi',l_property);
926: --for each base table, create an entry that will hold the current load set id (in change_vector column)
927: l_bsc_olap_object_relation.delete;
928: get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',
929: p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);
930: --if the entry is new, create one, we enter 0 as the current change vector value
931: --these base table entries need to be periodically validated and cleaned up. a base table may no longer be used
924: insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',
925: p_dim_set.dim_set_name,'dimension set','base table dim set',p_kpi,'kpi',l_property);
926: --for each base table, create an entry that will hold the current load set id (in change_vector column)
927: l_bsc_olap_object_relation.delete;
928: get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',
929: p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);
930: --if the entry is new, create one, we enter 0 as the current change vector value
931: --these base table entries need to be periodically validated and cleaned up. a base table may no longer be used
932: if l_bsc_olap_object_relation.count=0 then
925: p_dim_set.dim_set_name,'dimension set','base table dim set',p_kpi,'kpi',l_property);
926: --for each base table, create an entry that will hold the current load set id (in change_vector column)
927: l_bsc_olap_object_relation.delete;
928: get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',
929: p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);
930: --if the entry is new, create one, we enter 0 as the current change vector value
931: --these base table entries need to be periodically validated and cleaned up. a base table may no longer be used
932: if l_bsc_olap_object_relation.count=0 then
933: insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',
928: get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',
929: p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);
930: --if the entry is new, create one, we enter 0 as the current change vector value
931: --these base table entries need to be periodically validated and cleaned up. a base table may no longer be used
932: if l_bsc_olap_object_relation.count=0 then
933: insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',
934: '0','change vector','base table change vector',p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',null);
935: end if;
936: end if;
1009: ) is
1010: Begin
1011: default_context_if_null;
1012: if p_object is not null then
1013: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,
1014: relation_type,parent_object,parent_object_type,property1,
1015: CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
1016: values (p_object,p_object_type,p_relation_object,p_relation_object_type,
1017: p_relation_type,p_parent_object,p_parent_object_type,p_property1,
1043: p_parent_object_type varchar2,
1044: p_property1 varchar2
1045: ) is
1046: --
1047: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
1048: Begin
1049: default_context_if_null;
1050: get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
1051: if l_bsc_olap_object_relation.count>0 then
1046: --
1047: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
1048: Begin
1049: default_context_if_null;
1050: get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
1051: if l_bsc_olap_object_relation.count>0 then
1052: null;
1053: else
1054: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,
1047: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
1048: Begin
1049: default_context_if_null;
1050: get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
1051: if l_bsc_olap_object_relation.count>0 then
1052: null;
1053: else
1054: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,
1055: relation_type,parent_object,parent_object_type,property1,
1050: get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
1051: if l_bsc_olap_object_relation.count>0 then
1052: null;
1053: else
1054: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,
1055: relation_type,parent_object,parent_object_type,property1,
1056: CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
1057: values (p_object,p_object_type,p_relation_object,p_relation_object_type,
1058: p_relation_type,p_parent_object,p_parent_object_type,p_property1,
1090: --
1091: l_stmt varchar2(8000);
1092: Begin
1093: default_context_if_null;
1094: l_stmt:='update bsc_olap_object_relation set ';
1095: for i in 1..p_set_columns.count loop
1096: l_stmt:=l_stmt||p_set_columns(i)||'='''||p_set_values(i)||''',';
1097: end loop;
1098: l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
1182: ', p_relation_type='||p_relation_type||', p_relation_object='||p_relation_object||
1183: ', p_relation_object_type='||p_relation_object_type||', p_parent_object='||p_parent_object||
1184: ', p_parent_object_type='||p_parent_object_type||bsc_aw_utility.get_time);
1185: end if;
1186: delete bsc_olap_object_relation
1187: where object=nvl(p_object,object)
1188: and object_type=nvl(p_object_type,object_type)
1189: and relation_type=nvl(p_relation_type,relation_type)
1190: and relation_object=nvl(p_relation_object,relation_object)
1295:
1296: procedure analyze_md_tables is
1297: Begin
1298: bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT',60);
1299: bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT_RELATION',60);
1300: Exception when others then
1301: log_n('Exception in analyze_md_tables '||sqlerrm);
1302: raise;
1303: End;