[Home] [Help]
96: dmp_kpi(p_kpi);
97: end if;
98: create_kpi_aw(p_kpi);--create the aw objects
99: --create the kpi metadata
100: bsc_aw_md_api.create_kpi(p_kpi);
101: end if;
102: Exception when others then
103: log_n('Exception in create_kpi '||sqlerrm);
104: raise;
127: */
128: function check_kpi_create(p_kpi varchar2) return boolean is
129: Begin
130: --see if the kpi exists
131: if bsc_aw_md_api.is_kpi_present(p_kpi) then
132: if bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,'RECREATE KPI')='Y' then
133: drop_kpi_objects(p_kpi); --drops aw objects and olap metadata
134: return true;
135: else
149: we consider time and other dim separately. time is considered in calendars, not in dim
150:
151: here we are getting level properties in 2 parts.
152: bsc_aw_bsc_metadata.get_dim_level_properties gets it from metadata
153: for rec dim, we also do bsc_aw_md_api.get_bsc_olap_object_relation to find the rec_parent_level.
154: this is ok since rec_parent_level is not a bsc metadata property. its specific to our implementation.
155: */
156: procedure get_dim_set_dim_properties(p_kpi varchar2,p_dim_set in out nocopy dim_set_r) is
157: Begin
168: procedure get_dim_properties(p_kpi varchar2,p_dim in out nocopy dim_r) is
169: --
170: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
171: Begin
172: bsc_aw_md_api.get_dim_properties(p_dim); --recursive, multi level, time or normal, concat?, base value cube etc
173: bsc_aw_bsc_metadata.get_dim_level_properties(p_kpi,p_dim); --pk,fk,datatype for the levels and the filter
174: l_olap_object_relation.delete;
175: --'zero code level' 'recursive parent level'
176: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_dim.dim_name,'dimension',l_olap_object_relation);
172: bsc_aw_md_api.get_dim_properties(p_dim); --recursive, multi level, time or normal, concat?, base value cube etc
173: bsc_aw_bsc_metadata.get_dim_level_properties(p_kpi,p_dim); --pk,fk,datatype for the levels and the filter
174: l_olap_object_relation.delete;
175: --'zero code level' 'recursive parent level'
176: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_dim.dim_name,'dimension',l_olap_object_relation);
177: for i in 1..l_olap_object_relation.count loop
178: if l_olap_object_relation(i).object_type='dimension level' and l_olap_object_relation(i).relation_type='zero code level' then
179: for j in 1..p_dim.levels.count loop
180: if l_olap_object_relation(i).object=p_dim.levels(j).level_name then
216: procedure set_dim_level_positions(p_dim in out nocopy dim_r) is
217: l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
218: Begin
219: l_olap_object.delete;
220: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim.dim_name,'dimension',l_olap_object);
221: for i in 1..p_dim.levels.count loop
222: --standalone dim for each dim level do not have dimension levels with them.
223: p_dim.levels(i).position:=1;
224: for j in 1..l_olap_object.count loop
247: bsc_aw_bsc_metadata.get_dim_set_calendar(p_kpi.kpi,p_dim_set);
248: --give the aw dim names
249: p_dim_set.calendar.aw_dim:=bsc_aw_calendar.get_calendar_name(p_dim_set.calendar.calendar);
250: --set the relation name, periodicity aw name
251: bsc_aw_md_api.get_dim_set_calendar(p_kpi,p_dim_set);
252: --now, get the missing periodicity, and mark lowest level
253: get_missing_periodicity(p_kpi,p_dim_set);
254: /*of all hier relations in the calendar, grab only those that are relevant to the dimset */
255: get_relevant_cal_hier(p_dim_set.calendar.periodicity,p_dim_set.calendar.parent_child,l_pc);
285: l_dim varchar2(300);
286: Begin
287: bsc_aw_bsc_metadata.get_dim_set_dims(p_kpi,p_dim_set.dim_set,l_dim_level,l_mo_dim_group,l_skip_level);
288: for i in 1..l_dim_level.count loop
289: bsc_aw_md_api.get_dim_for_level(l_dim_level(i),l_dim);
290: if g_debug then
291: log('For level '||l_dim_level(i)||' got dim '||l_dim);
292: end if;
293: set_dim_and_level(p_dim_set,l_dim,l_dim_level(i),l_mo_dim_group(i),l_skip_level(i)); --level properties are set in get_dim_set_dim_properties
500: max value is the value for change vector from the base table
501: if the kpi has less keys, we will replace the aw table with in - line sql again with where change_vector between min and max
502: after loading, we will do update set last change vector=current change vector
503: when b table is loaded, it will call an api that will update the current change vector for this b table.
504: we will not call bsc_aw_md_api.get_change_vector(p_kpi.kpi,p_dim_set) or correct_change_vector(p_kpi,p_dim_set) anymore
505:
506: issue is : what if the b table already exists with data and we are creating the kpi. in this case, what will we set
507: current change vector to?
508: we will set it to 0. during load time, we check the b table for max(change_vector), set the current change vector
610: for i in 1..p_base_table.levels.count loop
611: if p_base_table.feed_levels(i)='Y' then
612: l_dim:=get_kpi_level_dim_r(p_dim_set,p_base_table.levels(i).level_name);
613: if l_dim.dim_name is null then --lower or higher
614: bsc_aw_md_api.get_dim_for_level(p_base_table.levels(i).level_name,l_oo_dim);
615: if g_debug then
616: log('Level '||p_base_table.levels(i).level_name||' has no dim in this dimset '||p_dim_set.dim_set||', found dim '||
617: l_oo_dim||' from oo metadata');
618: end if;
636: for i in 1..p_base_table.levels.count loop
637: if p_base_table.feed_levels(i)='N' then
638: l_dim:=get_kpi_level_dim_r(p_dim_set,p_base_table.levels(i).level_name);
639: if l_dim.dim_name is null then
640: bsc_aw_md_api.get_dim_for_level(p_base_table.levels(i).level_name,l_oo_dim);
641: else
642: l_oo_dim:=l_dim.dim_name;
643: end if;
644: if l_oo_dim is null or bsc_aw_utility.in_array(l_ds_dim_set,l_oo_dim)=false then
1268: l_base_aw_join_flag(i):='N';
1269: if p_base_table.level_status(i)='lower' then --the level is lower than the lowest level of dim set
1270: l_parent_child.delete;
1271: l_pc_subset.delete;
1272: bsc_aw_md_api.get_dim_for_level(p_base_table.levels(i).level_name,l_dim);
1273: bsc_aw_md_api.get_dim_parent_child(l_dim,l_parent_child);
1274: l_pc_subset:=bsc_aw_adapter_dim.get_hier_subset(l_parent_child,get_dim_given_dim_name(l_dim,p_data_source.dim).levels(1).level_name,
1275: p_base_table.levels(i).level_name);
1276: if l_pc_subset.count=0 then
1269: if p_base_table.level_status(i)='lower' then --the level is lower than the lowest level of dim set
1270: l_parent_child.delete;
1271: l_pc_subset.delete;
1272: bsc_aw_md_api.get_dim_for_level(p_base_table.levels(i).level_name,l_dim);
1273: bsc_aw_md_api.get_dim_parent_child(l_dim,l_parent_child);
1274: l_pc_subset:=bsc_aw_adapter_dim.get_hier_subset(l_parent_child,get_dim_given_dim_name(l_dim,p_data_source.dim).levels(1).level_name,
1275: p_base_table.levels(i).level_name);
1276: if l_pc_subset.count=0 then
1277: log('Could not rollup from B table to dimset '||p_base_table.base_table_name||', at level '||p_base_table.levels(i).level_name);
1510: end if;
1511: drop_kpi_objects_aw(p_kpi);
1512: drop_kpi_objects_relational(p_kpi);
1513: --drop kpi metadata
1514: bsc_aw_md_api.drop_kpi(p_kpi);
1515: Exception when others then
1516: log_n('Exception in drop_kpi_objects '||sqlerrm);
1517: raise;
1518: End;
1521: --
1522: l_objects bsc_aw_utility.object_tb; --object_t is object_name and object_type
1523: l_flag dbms_sql.varchar2_table;
1524: Begin
1525: bsc_aw_md_api.get_kpi_olap_objects(p_kpi,l_objects,'all');
1526: for i in 1..l_objects.count loop
1527: l_flag(i):='N';
1528: end loop;
1529: --get_kpi_olap_objects will only populate l_objects when olap object type is not null
1582: procedure drop_kpi_objects_relational(p_kpi varchar2) is
1583: l_flag dbms_sql.varchar2_table;
1584: l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
1585: Begin
1586: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_olap_object);
1587: for i in 1..l_olap_object.count loop
1588: l_flag(i):='N';
1589: end loop;
1590: --
4145: p_dim in out nocopy dim_r) is
4146: --
4147: l_dim varchar2(300);
4148: Begin
4149: bsc_aw_md_api.get_dim_for_level('TYPE',l_dim);
4150: p_dim.dim_name:=l_dim;
4151: p_dim.zero_code:='N';
4152: p_dim.relation_name:=null;
4153: p_dim.property:='normal';
4173: p_dim in out nocopy dim_r) is
4174: --
4175: l_dim varchar2(300);
4176: Begin
4177: bsc_aw_md_api.get_dim_for_level('PROJECTION',l_dim);
4178: p_dim.dim_name:=l_dim;
4179: p_dim.zero_code:='N';
4180: p_dim.relation_name:=null;
4181: p_dim.property:='normal';
4642: l_dim_levels dbms_sql.varchar2_table;
4643: l_axis_type varchar2(100);
4644: Begin
4645: l_dim_levels:=p_dim_levels;
4646: bsc_aw_md_api.get_kpi_dimset(p_kpi,l_oo_object);
4647: for i in 1..l_oo_object.count loop
4648: if bsc_aw_utility.get_parameter_value(l_oo_object(i).property1,'dim set',',')=p_dimset then
4649: if bsc_aw_utility.get_parameter_value(l_oo_object(i).property1,'dim set type',',')='actual' then
4650: l_dimset:=l_oo_object(i).object;
4652: end if;
4653: end if;
4654: end loop;
4655: if l_dimset is not null then
4656: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_oo_relation);
4657: for i in 1..l_dim_levels.count loop
4658: l_dim_level_dims(i):=null;
4659: for j in 1..l_oo_relation.count loop
4660: if l_oo_relation(j).relation_object=l_dim_levels(i) and l_oo_relation(j).relation_type='dim set dim level' and
4665: end if;
4666: end loop;
4667: --is this rec dim, then get the parent level
4668: l_oo_object.delete;
4669: bsc_aw_md_api.get_bsc_olap_object(l_dim_level_dims(i),'dimension',l_dim_level_dims(i),'dimension',l_oo_object);
4670: if bsc_aw_utility.get_parameter_value(l_oo_object(1).property1,'recursive',',')='Y' then
4671: l_oo_object.delete;
4672: bsc_aw_md_api.get_bsc_olap_object(null,'recursive level',l_dim_level_dims(i),'dimension',l_oo_object);
4673: l_dim_levels(i):=l_oo_object(1).object;--store the parent rec level
4668: l_oo_object.delete;
4669: bsc_aw_md_api.get_bsc_olap_object(l_dim_level_dims(i),'dimension',l_dim_level_dims(i),'dimension',l_oo_object);
4670: if bsc_aw_utility.get_parameter_value(l_oo_object(1).property1,'recursive',',')='Y' then
4671: l_oo_object.delete;
4672: bsc_aw_md_api.get_bsc_olap_object(null,'recursive level',l_dim_level_dims(i),'dimension',l_oo_object);
4673: l_dim_levels(i):=l_oo_object(1).object;--store the parent rec level
4674: end if;
4675: end loop;
4676: --calendar
4780: p_dim_set.calendar.periodicity(p_dim_set.calendar.periodicity.count).missing_level:='Y';
4781: end if;
4782: end loop;
4783: if l_initial_count < p_dim_set.calendar.periodicity.count then --we need to add periodicity id to the new dim..this is re-read cal info
4784: bsc_aw_md_api.get_dim_set_calendar(p_kpi,p_dim_set);
4785: end if;
4786: Exception when others then
4787: log_n('Exception in get_missing_periodicity '||sqlerrm);
4788: raise;
5356: l_child_levels dbms_sql.varchar2_table;
5357: zero_aggregated dbms_sql.varchar2_table;
5358: aggregated dbms_sql.varchar2_table;
5359: Begin
5360: bsc_aw_md_api.get_dim_parent_child(p_dim.dim_name,l_parent_child);
5361: for i in 1..l_parent_child.count loop
5362: if l_parent_child(i).parent_level is not null and l_parent_child(i).child_level is not null then
5363: l_pc(l_pc.count+1).parent:=l_parent_child(i).parent_level;
5364: l_pc(l_pc.count).child:=l_parent_child(i).child_level;
6110: and comp->manager and then merge the 2 pc together */
6111: l_parent_child.delete;
6112: l_pc_subset.delete;
6113: l_child_levels.delete;
6114: bsc_aw_md_api.get_dim_parent_child(p_DS_dim(i).dim_name,l_parent_child);
6115: l_child_levels(l_child_levels.count+1):=p_DS_dim(i).levels(1).level_name;
6116: l_pc_subset:=bsc_aw_adapter_dim.get_hier_subset(l_parent_child,p_hpt_data.hpt_dimensions(j).level_names,l_child_levels);
6117: if l_pc_subset.count=0 then
6118: log('Could not rollup from '||p_DS_dim(i).levels(1).level_name||' to higher levels like '||p_hpt_data.hpt_dimensions(j).level_names(1));