[Home] [Help]
89: for i in 1..p_dimension.level_groups.count loop
90: for j in 1..p_dimension.level_groups(i).levels.count loop
91: l_dim.delete;
92: get_dims_for_level(p_dimension.level_groups(i).levels(j).level_name,l_dim);
93: bsc_aw_utility.merge_array(p_dim_list,l_dim);
94: end loop;
95: end loop;
96: Exception when others then
97: log_n('Exception in get_ccdim_for_levels '||sqlerrm);
111: for i in 1..l_oo_level.count loop
112: if l_oo_level(i).parent_object_type='dimension' then
113: l_oo_dim.delete;
114: get_bsc_olap_object(l_oo_level(i).parent_object,'dimension',l_oo_level(i).parent_object,'dimension',l_oo_dim);
115: if nvl(bsc_aw_utility.get_parameter_value(l_oo_dim(1).property1,'corrected',','),'N')='N' then
116: p_dim:=l_oo_dim(1).object;
117: return;
118: end if;
119: end if;
166: l_position number;
167: Begin
168: g_olap_object.delete;
169: get_bsc_olap_object(p_dim_level,'dimension level',null,null,g_olap_object);
170: l_position:=bsc_aw_utility.get_parameter_value(g_olap_object(1).property1,'position',',');
171: if l_position is null then
172: return null;
173: else
174: return to_number(l_position);
189: End;
190:
191: procedure get_kpi_olap_objects(
192: p_kpi varchar2,
193: p_objects out nocopy bsc_aw_utility.object_tb,
194: p_type varchar2
195: ) is
196: Begin
197: g_olap_object.delete;
237: given a dim find out all the properties
238: from bsc olap metadata
239: */
240: procedure get_dim_properties(p_dim in out nocopy bsc_aw_adapter_kpi.dim_r) is
241: l_property bsc_aw_utility.value_tb;
242: Begin
243: g_olap_object.delete;
244: --get_bsc_olap_object(p_dim.dim_name,'dimension',null,null,g_olap_object);
245: get_bsc_olap_object(null,null,p_dim.dim_name,'dimension',g_olap_object);
249: p_dim.concat:='Y';
250: else
251: p_dim.concat:='N';
252: end if;
253: bsc_aw_utility.parse_parameter_values(g_olap_object(i).property1,',',l_property);
254: p_dim.property:=bsc_aw_utility.get_parameter_value(l_property,'dimension type'); --normal vs time
255: p_dim.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_property,'recursive'),'N'); --Y or N
256: p_dim.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_property,'normal hier'),'N');
257: p_dim.multi_level:=nvl(bsc_aw_utility.get_parameter_value(l_property,'multi level'),'N'); --Y or N
250: else
251: p_dim.concat:='N';
252: end if;
253: bsc_aw_utility.parse_parameter_values(g_olap_object(i).property1,',',l_property);
254: p_dim.property:=bsc_aw_utility.get_parameter_value(l_property,'dimension type'); --normal vs time
255: p_dim.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_property,'recursive'),'N'); --Y or N
256: p_dim.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_property,'normal hier'),'N');
257: p_dim.multi_level:=nvl(bsc_aw_utility.get_parameter_value(l_property,'multi level'),'N'); --Y or N
258: elsif g_olap_object(i).object_type='relation' then
251: p_dim.concat:='N';
252: end if;
253: bsc_aw_utility.parse_parameter_values(g_olap_object(i).property1,',',l_property);
254: p_dim.property:=bsc_aw_utility.get_parameter_value(l_property,'dimension type'); --normal vs time
255: p_dim.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_property,'recursive'),'N'); --Y or N
256: p_dim.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_property,'normal hier'),'N');
257: p_dim.multi_level:=nvl(bsc_aw_utility.get_parameter_value(l_property,'multi level'),'N'); --Y or N
258: elsif g_olap_object(i).object_type='relation' then
259: --relation name can be null if there is no rollup on this dim
252: end if;
253: bsc_aw_utility.parse_parameter_values(g_olap_object(i).property1,',',l_property);
254: p_dim.property:=bsc_aw_utility.get_parameter_value(l_property,'dimension type'); --normal vs time
255: p_dim.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_property,'recursive'),'N'); --Y or N
256: p_dim.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_property,'normal hier'),'N');
257: p_dim.multi_level:=nvl(bsc_aw_utility.get_parameter_value(l_property,'multi level'),'N'); --Y or N
258: elsif g_olap_object(i).object_type='relation' then
259: --relation name can be null if there is no rollup on this dim
260: p_dim.relation_name:=g_olap_object(i).object;
253: bsc_aw_utility.parse_parameter_values(g_olap_object(i).property1,',',l_property);
254: p_dim.property:=bsc_aw_utility.get_parameter_value(l_property,'dimension type'); --normal vs time
255: p_dim.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_property,'recursive'),'N'); --Y or N
256: p_dim.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_property,'normal hier'),'N');
257: p_dim.multi_level:=nvl(bsc_aw_utility.get_parameter_value(l_property,'multi level'),'N'); --Y or N
258: elsif g_olap_object(i).object_type='relation' then
259: --relation name can be null if there is no rollup on this dim
260: p_dim.relation_name:=g_olap_object(i).object;
261: elsif g_olap_object(i).object_type='level name dim' then
274: from bsc olap metadata
275: Input : calendar.aw_dim like bsc_calendar_1 NOT the calendar id
276: */
277: procedure get_calendar_properties(p_calendar in out nocopy bsc_aw_adapter_kpi.calendar_r) is
278: l_property bsc_aw_utility.value_tb;
279: Begin
280: g_olap_object.delete;
281: get_bsc_olap_object(null,null,p_calendar.aw_dim,'dimension',g_olap_object);
282: for i in 1..g_olap_object.count loop
280: g_olap_object.delete;
281: get_bsc_olap_object(null,null,p_calendar.aw_dim,'dimension',g_olap_object);
282: for i in 1..g_olap_object.count loop
283: if g_olap_object(i).object=p_calendar.aw_dim and g_olap_object(i).object_type='dimension' then
284: p_calendar.calendar:=bsc_aw_utility.get_parameter_value(g_olap_object(i).property1,'calendar',',');
285: elsif g_olap_object(i).object_type='relation' then
286: p_calendar.relation_name:=g_olap_object(i).object;
287: elsif g_olap_object(i).object_type='denorm relation' then
288: p_calendar.denorm_relation_name:=g_olap_object(i).object;
317: get_bsc_olap_object_relation(l_oo(i).object,l_oo(i).object_type,'parent level',p_dim,'dimension',l_oor);
318: for j in 1..l_oor.count loop
319: p_parent_child(p_parent_child.count+1).child_level:=l_oor(j).object;
320: p_parent_child(p_parent_child.count).parent_level:=l_oor(j).relation_object;
321: p_parent_child(p_parent_child.count).child_fk:=bsc_aw_utility.get_parameter_value(l_oor(j).property1,'fk',',');
322: p_parent_child(p_parent_child.count).parent_pk:=bsc_aw_utility.get_parameter_value(l_oor(j).property1,'pk',',');
323: end loop;
324: end loop;
325: Exception when others then
318: for j in 1..l_oor.count loop
319: p_parent_child(p_parent_child.count+1).child_level:=l_oor(j).object;
320: p_parent_child(p_parent_child.count).parent_level:=l_oor(j).relation_object;
321: p_parent_child(p_parent_child.count).child_fk:=bsc_aw_utility.get_parameter_value(l_oor(j).property1,'fk',',');
322: p_parent_child(p_parent_child.count).parent_pk:=bsc_aw_utility.get_parameter_value(l_oor(j).property1,'pk',',');
323: end loop;
324: end loop;
325: Exception when others then
326: log_n('Exception in get_dim_parent_child '||sqlerrm);
525: get_bsc_olap_object(null,'dimension level',p_dim_set.calendar.aw_dim,'dimension',l_oo);
526: for i in 1..l_oo.count loop
527: l_periodicity(i):=null;
528: l_periodicity_type(i):=null;
529: l_periodicity(i):=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity',',');
530: l_periodicity_type(i):=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity_type',',');
531: end loop;
532: for i in 1..p_dim_set.calendar.periodicity.count loop
533: if p_dim_set.calendar.periodicity(i).aw_dim is null then
526: for i in 1..l_oo.count loop
527: l_periodicity(i):=null;
528: l_periodicity_type(i):=null;
529: l_periodicity(i):=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity',',');
530: l_periodicity_type(i):=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity_type',',');
531: end loop;
532: for i in 1..p_dim_set.calendar.periodicity.count loop
533: if p_dim_set.calendar.periodicity(i).aw_dim is null then
534: for j in 1..l_periodicity.count loop
555: for i in 1..l_olap_object_relation.count loop
556: p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count+1).parent_dim_name:=l_olap_object_relation(i).relation_object;
557: p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count).child_dim_name:=l_olap_object_relation(i).object;
558: p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count).parent:=to_number(
559: bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'parent periodicity',','));
560: p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count).child:=to_number(
561: bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'child periodicity',','));
562: end loop;
563: Exception when others then
557: p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count).child_dim_name:=l_olap_object_relation(i).object;
558: p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count).parent:=to_number(
559: bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'parent periodicity',','));
560: p_dim_set.calendar.parent_child(p_dim_set.calendar.parent_child.count).child:=to_number(
561: bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'child periodicity',','));
562: end loop;
563: Exception when others then
564: log_n('Exception in get_dim_set_calendar '||sqlerrm);
565: raise;
602: l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
603: Begin
604: get_kpi_dimset(p_kpi,l_bsc_olap_object);
605: for i in 1..l_bsc_olap_object.count loop
606: if bsc_aw_utility.get_parameter_value(l_bsc_olap_object(i).property1,'dim set type',',')='actual' then
607: p_bsc_olap_object(p_bsc_olap_object.count+1):=l_bsc_olap_object(i);
608: end if;
609: end loop;
610: Exception when others then
684: p_measure out nocopy bsc_aw_adapter_kpi.measure_tb
685: ) is
686: --
687: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
688: l_values bsc_aw_utility.value_tb;
689: l_property varchar2(2000);
690: Begin
691: get_bsc_olap_object_relation(p_dimset,'kpi dimension set','dim set measure',p_kpi,'kpi',l_olap_object_relation);
692: --l_agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
688: l_values bsc_aw_utility.value_tb;
689: l_property varchar2(2000);
690: Begin
691: get_bsc_olap_object_relation(p_dimset,'kpi dimension set','dim set measure',p_kpi,'kpi',l_olap_object_relation);
692: --l_agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
693: for i in 1..l_olap_object_relation.count loop
694: p_measure(p_measure.count+1).measure:=l_olap_object_relation(i).relation_object;
695: p_measure(p_measure.count).measure_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measure type',',');
696: p_measure(p_measure.count).forecast:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast',',');
691: get_bsc_olap_object_relation(p_dimset,'kpi dimension set','dim set measure',p_kpi,'kpi',l_olap_object_relation);
692: --l_agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
693: for i in 1..l_olap_object_relation.count loop
694: p_measure(p_measure.count+1).measure:=l_olap_object_relation(i).relation_object;
695: p_measure(p_measure.count).measure_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measure type',',');
696: p_measure(p_measure.count).forecast:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast',',');
697: p_measure(p_measure.count).forecast_method:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast method',',');
698: p_measure(p_measure.count).sql_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'sql aggregated',','),'N');
699: p_measure(p_measure.count).agg_formula.agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
692: --l_agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
693: for i in 1..l_olap_object_relation.count loop
694: p_measure(p_measure.count+1).measure:=l_olap_object_relation(i).relation_object;
695: p_measure(p_measure.count).measure_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measure type',',');
696: p_measure(p_measure.count).forecast:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast',',');
697: p_measure(p_measure.count).forecast_method:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast method',',');
698: p_measure(p_measure.count).sql_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'sql aggregated',','),'N');
699: p_measure(p_measure.count).agg_formula.agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
700: p_measure(p_measure.count).agg_formula.std_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'std agg',',');
693: for i in 1..l_olap_object_relation.count loop
694: p_measure(p_measure.count+1).measure:=l_olap_object_relation(i).relation_object;
695: p_measure(p_measure.count).measure_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measure type',',');
696: p_measure(p_measure.count).forecast:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast',',');
697: p_measure(p_measure.count).forecast_method:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast method',',');
698: p_measure(p_measure.count).sql_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'sql aggregated',','),'N');
699: p_measure(p_measure.count).agg_formula.agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
700: p_measure(p_measure.count).agg_formula.std_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'std agg',',');
701: p_measure(p_measure.count).agg_formula.avg_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'avg agg',',');
694: p_measure(p_measure.count+1).measure:=l_olap_object_relation(i).relation_object;
695: p_measure(p_measure.count).measure_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measure type',',');
696: p_measure(p_measure.count).forecast:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast',',');
697: p_measure(p_measure.count).forecast_method:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast method',',');
698: p_measure(p_measure.count).sql_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'sql aggregated',','),'N');
699: p_measure(p_measure.count).agg_formula.agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
700: p_measure(p_measure.count).agg_formula.std_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'std agg',',');
701: p_measure(p_measure.count).agg_formula.avg_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'avg agg',',');
702: l_values.delete;
695: p_measure(p_measure.count).measure_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measure type',',');
696: p_measure(p_measure.count).forecast:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast',',');
697: p_measure(p_measure.count).forecast_method:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast method',',');
698: p_measure(p_measure.count).sql_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'sql aggregated',','),'N');
699: p_measure(p_measure.count).agg_formula.agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
700: p_measure(p_measure.count).agg_formula.std_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'std agg',',');
701: p_measure(p_measure.count).agg_formula.avg_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'avg agg',',');
702: l_values.delete;
703: bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
696: p_measure(p_measure.count).forecast:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast',',');
697: p_measure(p_measure.count).forecast_method:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast method',',');
698: p_measure(p_measure.count).sql_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'sql aggregated',','),'N');
699: p_measure(p_measure.count).agg_formula.agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
700: p_measure(p_measure.count).agg_formula.std_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'std agg',',');
701: p_measure(p_measure.count).agg_formula.avg_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'avg agg',',');
702: l_values.delete;
703: bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
704: 'agg formula cubes',','),'+',l_values);
697: p_measure(p_measure.count).forecast_method:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'forecast method',',');
698: p_measure(p_measure.count).sql_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'sql aggregated',','),'N');
699: p_measure(p_measure.count).agg_formula.agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
700: p_measure(p_measure.count).agg_formula.std_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'std agg',',');
701: p_measure(p_measure.count).agg_formula.avg_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'avg agg',',');
702: l_values.delete;
703: bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
704: 'agg formula cubes',','),'+',l_values);
705: if l_values.count>0 then
699: p_measure(p_measure.count).agg_formula.agg_formula:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg formula',',');
700: p_measure(p_measure.count).agg_formula.std_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'std agg',',');
701: p_measure(p_measure.count).agg_formula.avg_aggregation:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'avg agg',',');
702: l_values.delete;
703: bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
704: 'agg formula cubes',','),'+',l_values);
705: if l_values.count>0 then
706: for j in 1..l_values.count loop
707: p_measure(p_measure.count).agg_formula.cubes(p_measure(p_measure.count).agg_formula.cubes.count+1):=l_values(j).parameter;
707: p_measure(p_measure.count).agg_formula.cubes(p_measure(p_measure.count).agg_formula.cubes.count+1):=l_values(j).parameter;
708: end loop;
709: end if;
710: l_values.delete;
711: bsc_aw_utility.parse_parameter_values(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
712: 'agg formula measures',','),'+',l_values);
713: if l_values.count>0 then
714: for j in 1..l_values.count loop
715: p_measure(p_measure.count).agg_formula.measures(p_measure(p_measure.count).agg_formula.measures.count+1):=l_values(j).parameter;
716: end loop;
717: end if;
718: --cannot change get_parameter_value to scanning bsc_olap_object even though bsc_olap_object has the cube and fcst cube etc
719: --in bsc_olap_object, we cannot know which measure belongs to which dimset
720: p_measure(p_measure.count).cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube',',');
721: p_measure(p_measure.count).fcst_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'fcst cube',',');
722: p_measure(p_measure.count).countvar_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'countvar cube',',');
723: p_measure(p_measure.count).display_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'display cube',',');
724: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'balance loaded column',',');
717: end if;
718: --cannot change get_parameter_value to scanning bsc_olap_object even though bsc_olap_object has the cube and fcst cube etc
719: --in bsc_olap_object, we cannot know which measure belongs to which dimset
720: p_measure(p_measure.count).cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube',',');
721: p_measure(p_measure.count).fcst_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'fcst cube',',');
722: p_measure(p_measure.count).countvar_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'countvar cube',',');
723: p_measure(p_measure.count).display_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'display cube',',');
724: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'balance loaded column',',');
725: if l_property is not null then
718: --cannot change get_parameter_value to scanning bsc_olap_object even though bsc_olap_object has the cube and fcst cube etc
719: --in bsc_olap_object, we cannot know which measure belongs to which dimset
720: p_measure(p_measure.count).cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube',',');
721: p_measure(p_measure.count).fcst_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'fcst cube',',');
722: p_measure(p_measure.count).countvar_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'countvar cube',',');
723: p_measure(p_measure.count).display_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'display cube',',');
724: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'balance loaded column',',');
725: if l_property is not null then
726: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'balance loaded column',null,l_property);
719: --in bsc_olap_object, we cannot know which measure belongs to which dimset
720: p_measure(p_measure.count).cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube',',');
721: p_measure(p_measure.count).fcst_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'fcst cube',',');
722: p_measure(p_measure.count).countvar_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'countvar cube',',');
723: p_measure(p_measure.count).display_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'display cube',',');
724: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'balance loaded column',',');
725: if l_property is not null then
726: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'balance loaded column',null,l_property);
727: end if;
720: p_measure(p_measure.count).cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube',',');
721: p_measure(p_measure.count).fcst_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'fcst cube',',');
722: p_measure(p_measure.count).countvar_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'countvar cube',',');
723: p_measure(p_measure.count).display_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'display cube',',');
724: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'balance loaded column',',');
725: if l_property is not null then
726: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'balance loaded column',null,l_property);
727: end if;
728: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'year cube',',');
722: p_measure(p_measure.count).countvar_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'countvar cube',',');
723: p_measure(p_measure.count).display_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'display cube',',');
724: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'balance loaded column',',');
725: if l_property is not null then
726: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'balance loaded column',null,l_property);
727: end if;
728: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'year cube',',');
729: if l_property is not null then
730: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'year cube',null,l_property);
724: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'balance loaded column',',');
725: if l_property is not null then
726: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'balance loaded column',null,l_property);
727: end if;
728: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'year cube',',');
729: if l_property is not null then
730: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'year cube',null,l_property);
731: end if;
732: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'period cube',',');
726: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'balance loaded column',null,l_property);
727: end if;
728: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'year cube',',');
729: if l_property is not null then
730: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'year cube',null,l_property);
731: end if;
732: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'period cube',',');
733: if l_property is not null then
734: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'period cube',null,l_property);
728: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'year cube',',');
729: if l_property is not null then
730: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'year cube',null,l_property);
731: end if;
732: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'period cube',',');
733: if l_property is not null then
734: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'period cube',null,l_property);
735: end if;
736: --in 10g we have composites per measure
730: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'year cube',null,l_property);
731: end if;
732: l_property:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'period cube',',');
733: if l_property is not null then
734: bsc_aw_utility.merge_property(p_measure(p_measure.count).property,'period cube',null,l_property);
735: end if;
736: --in 10g we have composites per measure
737: end loop;
738: Exception when others then
764: get_bsc_olap_object(p_dimset_name,'kpi dimension set',p_kpi,'kpi',l_olap_object);
765: get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
766: p_dimset.dim_set_name:=p_dimset_name;
767: --dimset properties
768: p_dimset.dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set',',');
769: p_dimset.dim_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set type',',');
770: p_dimset.base_dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'base dim set',',');
771: p_dimset.targets_higher_levels:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'targets',','),'N');
772: p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
765: get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
766: p_dimset.dim_set_name:=p_dimset_name;
767: --dimset properties
768: p_dimset.dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set',',');
769: p_dimset.dim_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set type',',');
770: p_dimset.base_dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'base dim set',',');
771: p_dimset.targets_higher_levels:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'targets',','),'N');
772: p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
773: p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
766: p_dimset.dim_set_name:=p_dimset_name;
767: --dimset properties
768: p_dimset.dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set',',');
769: p_dimset.dim_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set type',',');
770: p_dimset.base_dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'base dim set',',');
771: p_dimset.targets_higher_levels:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'targets',','),'N');
772: p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
773: p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
774: p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
767: --dimset properties
768: p_dimset.dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set',',');
769: p_dimset.dim_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set type',',');
770: p_dimset.base_dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'base dim set',',');
771: p_dimset.targets_higher_levels:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'targets',','),'N');
772: p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
773: p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
774: p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
775: p_dimset.number_partitions:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'number partitions',',');
768: p_dimset.dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set',',');
769: p_dimset.dim_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set type',',');
770: p_dimset.base_dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'base dim set',',');
771: p_dimset.targets_higher_levels:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'targets',','),'N');
772: p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
773: p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
774: p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
775: p_dimset.number_partitions:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'number partitions',',');
776: p_dimset.partition_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition type',',');
769: p_dimset.dim_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'dim set type',',');
770: p_dimset.base_dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'base dim set',',');
771: p_dimset.targets_higher_levels:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'targets',','),'N');
772: p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
773: p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
774: p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
775: p_dimset.number_partitions:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'number partitions',',');
776: p_dimset.partition_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition type',',');
777: p_dimset.compressed:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'compressed',',');
770: p_dimset.base_dim_set:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'base dim set',',');
771: p_dimset.targets_higher_levels:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'targets',','),'N');
772: p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
773: p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
774: p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
775: p_dimset.number_partitions:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'number partitions',',');
776: p_dimset.partition_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition type',',');
777: p_dimset.compressed:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'compressed',',');
778: p_dimset.pre_calculated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'pre calculated',','),'N');
771: p_dimset.targets_higher_levels:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'targets',','),'N');
772: p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
773: p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
774: p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
775: p_dimset.number_partitions:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'number partitions',',');
776: p_dimset.partition_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition type',',');
777: p_dimset.compressed:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'compressed',',');
778: p_dimset.pre_calculated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'pre calculated',','),'N');
779: --we are not looking at agg maps defined per dim
772: p_dimset.measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'measurename dim',',');
773: p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
774: p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
775: p_dimset.number_partitions:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'number partitions',',');
776: p_dimset.partition_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition type',',');
777: p_dimset.compressed:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'compressed',',');
778: p_dimset.pre_calculated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'pre calculated',','),'N');
779: --we are not looking at agg maps defined per dim
780: --agg_map_average is usedfor AVERAGE aggregation
773: p_dimset.partition_dim:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition dim',',');
774: p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
775: p_dimset.number_partitions:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'number partitions',',');
776: p_dimset.partition_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition type',',');
777: p_dimset.compressed:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'compressed',',');
778: p_dimset.pre_calculated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'pre calculated',','),'N');
779: --we are not looking at agg maps defined per dim
780: --agg_map_average is usedfor AVERAGE aggregation
781: --agg_map_notime is used for balance measures
774: p_dimset.cube_design:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'cube design',',');
775: p_dimset.number_partitions:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'number partitions',',');
776: p_dimset.partition_type:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'partition type',',');
777: p_dimset.compressed:=bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'compressed',',');
778: p_dimset.pre_calculated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object(1).property1,'pre calculated',','),'N');
779: --we are not looking at agg maps defined per dim
780: --agg_map_average is usedfor AVERAGE aggregation
781: --agg_map_notime is used for balance measures
782: for i in 1..l_olap_object_relation.count loop
827: get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
828: for i in 1..l_olap_object_relation.count loop
829: if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set partition template' then
830: p_partition_template(p_partition_template.count+1).template_name:=l_olap_object_relation(i).relation_object;
831: p_partition_template(p_partition_template.count).template_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
832: 'template type',',');
833: p_partition_template(p_partition_template.count).template_use:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
834: 'template use',',');
835: p_partition_template(p_partition_template.count).template_dim:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
829: if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set partition template' then
830: p_partition_template(p_partition_template.count+1).template_name:=l_olap_object_relation(i).relation_object;
831: p_partition_template(p_partition_template.count).template_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
832: 'template type',',');
833: p_partition_template(p_partition_template.count).template_use:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
834: 'template use',',');
835: p_partition_template(p_partition_template.count).template_dim:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
836: 'template dim',',');
837: --now, the template partitions
831: p_partition_template(p_partition_template.count).template_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
832: 'template type',',');
833: p_partition_template(p_partition_template.count).template_use:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
834: 'template use',',');
835: p_partition_template(p_partition_template.count).template_dim:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
836: 'template dim',',');
837: --now, the template partitions
838: for j in 1..l_olap_object_relation.count loop
839: if l_olap_object_relation(j).object=p_partition_template(p_partition_template.count).template_name and
841: p_partition_template(p_partition_template.count).template_partitions(
842: p_partition_template(p_partition_template.count).template_partitions.count+1).partition_name:=l_olap_object_relation(j).relation_object;
843: p_partition_template(p_partition_template.count).template_partitions(
844: p_partition_template(p_partition_template.count).template_partitions.count).partition_dim_value:=
845: bsc_aw_utility.get_parameter_value(l_olap_object_relation(j).property1,'partition dim value',',');
846: end if;
847: end loop;
848: --we are not loading the partition axis info here. this info is not really reqd
849: end if;
851: --get the composite info
852: for i in 1..l_olap_object_relation.count loop
853: if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set measure composite' then
854: p_composite(p_composite.count+1).composite_name:=l_olap_object_relation(i).relation_object;
855: p_composite(p_composite.count).composite_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'composite type',',');
856: end if;
857: end loop;
858: Exception when others then
859: log_n('Exception in get_dimset_comp_PT '||sqlerrm);
873: get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
874: for i in 1..l_olap_object_relation.count loop
875: if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set cube set' then
876: p_cube_set(p_cube_set.count+1).cube_set_name:=l_olap_object_relation(i).relation_object;
877: p_cube_set(p_cube_set.count).cube_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube set type',',');
878: p_cube_set(p_cube_set.count).measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measurename dim',',');
879: --cubes
880: get_dimset_cube(p_kpi,p_dimset_name,p_cube_set(p_cube_set.count).cube_set_name,'cube set measure cube',
881: p_cube_set(p_cube_set.count).cube);
874: for i in 1..l_olap_object_relation.count loop
875: if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set cube set' then
876: p_cube_set(p_cube_set.count+1).cube_set_name:=l_olap_object_relation(i).relation_object;
877: p_cube_set(p_cube_set.count).cube_set_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube set type',',');
878: p_cube_set(p_cube_set.count).measurename_dim:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'measurename dim',',');
879: --cubes
880: get_dimset_cube(p_kpi,p_dimset_name,p_cube_set(p_cube_set.count).cube_set_name,'cube set measure cube',
881: p_cube_set(p_cube_set.count).cube);
882: get_dimset_cube(p_kpi,p_dimset_name,p_cube_set(p_cube_set.count).cube_set_name,'cube set countvar cube',
905: get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
906: for i in 1..l_olap_object_relation.count loop
907: if l_olap_object_relation(i).object=p_cube_set_name and l_olap_object_relation(i).relation_type=p_cube_type then
908: p_cube.cube_name:=l_olap_object_relation(i).relation_object;
909: p_cube.cube_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube type',',');
910: p_cube.cube_datatype:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube datatype',',');
911: end if;
912: end loop;
913: --get the axis
906: for i in 1..l_olap_object_relation.count loop
907: if l_olap_object_relation(i).object=p_cube_set_name and l_olap_object_relation(i).relation_type=p_cube_type then
908: p_cube.cube_name:=l_olap_object_relation(i).relation_object;
909: p_cube.cube_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube type',',');
910: p_cube.cube_datatype:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'cube datatype',',');
911: end if;
912: end loop;
913: --get the axis
914: for i in 1..l_olap_object_relation.count loop
913: --get the axis
914: for i in 1..l_olap_object_relation.count loop
915: if l_olap_object_relation(i).object=p_cube.cube_name and l_olap_object_relation(i).relation_type='cube axis' then
916: p_cube.cube_axis(p_cube.cube_axis.count+1).axis_name:=l_olap_object_relation(i).relation_object;
917: p_cube.cube_axis(p_cube.cube_axis.count).axis_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'axis type',',');
918: end if;
919: end loop;
920: Exception when others then
921: log_n('Exception in get_dimset_cube '||sqlerrm);
953:
954: /*
955: given a dim, get the properties and levels
956: p_dim.dim_name is known
957: p_dim.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',') is reqd since in
958: bsc_olap_object we do not know which limit cube is tied to which dim
959:
960: zero code is stored at the dim level, not at the dim level
961: p_level_type is "dim set dim level"
979: get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_olap_object_relation);
980: for i in 1..l_olap_object_relation.count loop
981: if l_olap_object_relation(i).relation_type=p_dim_type
982: and l_olap_object_relation(i).relation_object=p_dim.dim_name and l_olap_object_relation(i).object=p_dimset_name then
983: p_dim.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
984: p_dim.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
985: p_dim.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
986: p_dim.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
987: p_dim.agg_level:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg level',',');
980: for i in 1..l_olap_object_relation.count loop
981: if l_olap_object_relation(i).relation_type=p_dim_type
982: and l_olap_object_relation(i).relation_object=p_dim.dim_name and l_olap_object_relation(i).object=p_dimset_name then
983: p_dim.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
984: p_dim.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
985: p_dim.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
986: p_dim.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
987: p_dim.agg_level:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg level',',');
988: exit;
981: if l_olap_object_relation(i).relation_type=p_dim_type
982: and l_olap_object_relation(i).relation_object=p_dim.dim_name and l_olap_object_relation(i).object=p_dimset_name then
983: p_dim.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
984: p_dim.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
985: p_dim.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
986: p_dim.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
987: p_dim.agg_level:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg level',',');
988: exit;
989: end if;
982: and l_olap_object_relation(i).relation_object=p_dim.dim_name and l_olap_object_relation(i).object=p_dimset_name then
983: p_dim.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
984: p_dim.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
985: p_dim.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
986: p_dim.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
987: p_dim.agg_level:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg level',',');
988: exit;
989: end if;
990: end loop;
983: p_dim.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
984: p_dim.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
985: p_dim.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
986: p_dim.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
987: p_dim.agg_level:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg level',',');
988: exit;
989: end if;
990: end loop;
991: --get the levels
1004: l_index:=p_dim.levels.count+1;--start with a min of 2
1005: end if;
1006: p_dim.levels(l_index).level_name:=l_olap_object_relation(i).relation_object;
1007: --p_dim.levels(l_index).position:=nvl(get_level_position(p_dim.levels(l_index).level_name),1);
1008: p_dim.levels(l_index).position:=nvl(to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'position',',')),1);
1009: p_dim.levels(l_index).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'aggregated',','),'Y');
1010: p_dim.levels(l_index).zero_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero_aggregated',','),'Y');
1011: /*nvl(Y) for zero_aggregated for backward compatibility */
1012: p_dim.levels(l_index).zero_code:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero code',','),'N'); --Y or N
1005: end if;
1006: p_dim.levels(l_index).level_name:=l_olap_object_relation(i).relation_object;
1007: --p_dim.levels(l_index).position:=nvl(get_level_position(p_dim.levels(l_index).level_name),1);
1008: p_dim.levels(l_index).position:=nvl(to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'position',',')),1);
1009: p_dim.levels(l_index).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'aggregated',','),'Y');
1010: p_dim.levels(l_index).zero_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero_aggregated',','),'Y');
1011: /*nvl(Y) for zero_aggregated for backward compatibility */
1012: p_dim.levels(l_index).zero_code:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero code',','),'N'); --Y or N
1013: if p_dim.levels(l_index).zero_code='Y' then
1006: p_dim.levels(l_index).level_name:=l_olap_object_relation(i).relation_object;
1007: --p_dim.levels(l_index).position:=nvl(get_level_position(p_dim.levels(l_index).level_name),1);
1008: p_dim.levels(l_index).position:=nvl(to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'position',',')),1);
1009: p_dim.levels(l_index).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'aggregated',','),'Y');
1010: p_dim.levels(l_index).zero_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero_aggregated',','),'Y');
1011: /*nvl(Y) for zero_aggregated for backward compatibility */
1012: p_dim.levels(l_index).zero_code:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero code',','),'N'); --Y or N
1013: if p_dim.levels(l_index).zero_code='Y' then
1014: l_oor_zero_code.delete;
1008: p_dim.levels(l_index).position:=nvl(to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'position',',')),1);
1009: p_dim.levels(l_index).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'aggregated',','),'Y');
1010: p_dim.levels(l_index).zero_aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero_aggregated',','),'Y');
1011: /*nvl(Y) for zero_aggregated for backward compatibility */
1012: p_dim.levels(l_index).zero_code:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'zero code',','),'N'); --Y or N
1013: if p_dim.levels(l_index).zero_code='Y' then
1014: l_oor_zero_code.delete;
1015: get_bsc_olap_object_relation(p_dim.levels(l_index).level_name,'dimension level','zero code level',p_dim.dim_name,'dimension',l_oor_zero_code);
1016: p_dim.levels(l_index).zero_code_level:=l_oor_zero_code(1).relation_object;
1072: for i in 1..l_olap_object_relation.count loop
1073: if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set calendar' then
1074: p_calendar.aw_dim:=l_olap_object_relation(i).relation_object;
1075: get_calendar_properties(p_calendar);
1076: p_calendar.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
1077: p_calendar.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
1078: p_calendar.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
1079: p_calendar.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
1080: exit;
1073: if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set calendar' then
1074: p_calendar.aw_dim:=l_olap_object_relation(i).relation_object;
1075: get_calendar_properties(p_calendar);
1076: p_calendar.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
1077: p_calendar.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
1078: p_calendar.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
1079: p_calendar.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
1080: exit;
1081: end if;
1074: p_calendar.aw_dim:=l_olap_object_relation(i).relation_object;
1075: get_calendar_properties(p_calendar);
1076: p_calendar.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
1077: p_calendar.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
1078: p_calendar.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
1079: p_calendar.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
1080: exit;
1081: end if;
1082: end loop;
1075: get_calendar_properties(p_calendar);
1076: p_calendar.limit_cube:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube',',');
1077: p_calendar.limit_cube_composite:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'limit cube composite',',');
1078: p_calendar.aggregate_marker:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'dim aggregate marker',',');
1079: p_calendar.agg_map.agg_map:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'agg map',',');
1080: exit;
1081: end if;
1082: end loop;
1083: --get the level name dim
1087: --remember...for periodicities, lowest level is not periodicity(1). its indicated by the property lowest level only
1088: for i in 1..l_olap_object_relation.count loop
1089: if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set periodicity' then
1090: p_calendar.periodicity(p_calendar.periodicity.count+1).aw_dim:=l_olap_object_relation(i).relation_object;
1091: p_calendar.periodicity(p_calendar.periodicity.count).periodicity:=to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1092: 'periodicity',','));
1093: p_calendar.periodicity(p_calendar.periodicity.count).lowest_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1094: 'lowest level',','),'N');
1095: p_calendar.periodicity(p_calendar.periodicity.count).missing_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1089: if l_olap_object_relation(i).object=p_dimset_name and l_olap_object_relation(i).relation_type='dim set periodicity' then
1090: p_calendar.periodicity(p_calendar.periodicity.count+1).aw_dim:=l_olap_object_relation(i).relation_object;
1091: p_calendar.periodicity(p_calendar.periodicity.count).periodicity:=to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1092: 'periodicity',','));
1093: p_calendar.periodicity(p_calendar.periodicity.count).lowest_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1094: 'lowest level',','),'N');
1095: p_calendar.periodicity(p_calendar.periodicity.count).missing_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1096: 'missing level',','),'N');
1097: p_calendar.periodicity(p_calendar.periodicity.count).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1091: p_calendar.periodicity(p_calendar.periodicity.count).periodicity:=to_number(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1092: 'periodicity',','));
1093: p_calendar.periodicity(p_calendar.periodicity.count).lowest_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1094: 'lowest level',','),'N');
1095: p_calendar.periodicity(p_calendar.periodicity.count).missing_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1096: 'missing level',','),'N');
1097: p_calendar.periodicity(p_calendar.periodicity.count).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1098: 'aggregated',','),'Y');
1099: p_calendar.periodicity(p_calendar.periodicity.count).periodicity_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1093: p_calendar.periodicity(p_calendar.periodicity.count).lowest_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1094: 'lowest level',','),'N');
1095: p_calendar.periodicity(p_calendar.periodicity.count).missing_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1096: 'missing level',','),'N');
1097: p_calendar.periodicity(p_calendar.periodicity.count).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1098: 'aggregated',','),'Y');
1099: p_calendar.periodicity(p_calendar.periodicity.count).periodicity_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1100: 'periodicity_type',',');
1101: end if;
1095: p_calendar.periodicity(p_calendar.periodicity.count).missing_level:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1096: 'missing level',','),'N');
1097: p_calendar.periodicity(p_calendar.periodicity.count).aggregated:=nvl(bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1098: 'aggregated',','),'Y');
1099: p_calendar.periodicity(p_calendar.periodicity.count).periodicity_type:=bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,
1100: 'periodicity_type',',');
1101: end if;
1102: end loop;
1103: --load the periodicity relations
1109: is_periodicity_in_dim(p_calendar,l_olap_object_relation(i).object) then
1110: p_calendar.parent_child(p_calendar.parent_child.count+1).parent_dim_name:=l_olap_object_relation(i).relation_object;
1111: p_calendar.parent_child(p_calendar.parent_child.count).child_dim_name:=l_olap_object_relation(i).object;
1112: p_calendar.parent_child(p_calendar.parent_child.count).parent:=to_number(
1113: bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'parent periodicity',','));
1114: p_calendar.parent_child(p_calendar.parent_child.count).child:=to_number(
1115: bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'child periodicity',','));
1116: end if;
1117: end loop;
1111: p_calendar.parent_child(p_calendar.parent_child.count).child_dim_name:=l_olap_object_relation(i).object;
1112: p_calendar.parent_child(p_calendar.parent_child.count).parent:=to_number(
1113: bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'parent periodicity',','));
1114: p_calendar.parent_child(p_calendar.parent_child.count).child:=to_number(
1115: bsc_aw_utility.get_parameter_value(l_olap_object_relation(i).property1,'child periodicity',','));
1116: end if;
1117: end loop;
1118: Exception when others then
1119: log_n('Exception in get_kpi_dimset_calendar_md '||sqlerrm);
1174: end if;
1175: get_bsc_olap_object(p_kpi.kpi,'kpi',p_kpi.kpi,'kpi',l_oo);
1176: if l_oo.count=0 then
1177: log('Could not find kpi info');
1178: raise bsc_aw_utility.g_exception;
1179: end if;
1180: p_kpi.parent_kpi:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'parent kpi',','); --could be null
1181: p_kpi.calendar:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'calendar',',');
1182: --now the dimsets
1176: if l_oo.count=0 then
1177: log('Could not find kpi info');
1178: raise bsc_aw_utility.g_exception;
1179: end if;
1180: p_kpi.parent_kpi:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'parent kpi',','); --could be null
1181: p_kpi.calendar:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'calendar',',');
1182: --now the dimsets
1183: l_oo.delete;
1184: get_kpi_dimset(p_kpi.kpi,l_oo);
1177: log('Could not find kpi info');
1178: raise bsc_aw_utility.g_exception;
1179: end if;
1180: p_kpi.parent_kpi:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'parent kpi',','); --could be null
1181: p_kpi.calendar:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'calendar',',');
1182: --now the dimsets
1183: l_oo.delete;
1184: get_kpi_dimset(p_kpi.kpi,l_oo);
1185: for i in 1..l_oo.count loop
1182: --now the dimsets
1183: l_oo.delete;
1184: get_kpi_dimset(p_kpi.kpi,l_oo);
1185: for i in 1..l_oo.count loop
1186: if bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dim set type',',')='actual' then
1187: l_dim_set:=l_oo(i).object;
1188: get_kpi_dimset_md(p_kpi.kpi,l_dim_set,p_kpi.dim_set(p_kpi.dim_set.count+1));
1189: end if;
1190: end loop;
1189: end if;
1190: end loop;
1191: --targets
1192: for i in 1..l_oo.count loop
1193: if bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dim set type',',')='target' then
1194: l_dim_set:=l_oo(i).object;
1195: get_kpi_dimset_md(p_kpi.kpi,l_dim_set,p_kpi.target_dim_set(p_kpi.target_dim_set.count+1));
1196: end if;
1197: end loop;
1262: l_set_columns dbms_sql.varchar2_table;
1263: l_set_values dbms_sql.varchar2_table;
1264: Begin
1265: clear_all_cache;
1266: bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1267: bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1268: bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1269: bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1270: bsc_aw_md_wrapper.update_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,
1263: l_set_values dbms_sql.varchar2_table;
1264: Begin
1265: clear_all_cache;
1266: bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1267: bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1268: bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1269: bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1270: bsc_aw_md_wrapper.update_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,
1271: l_match_columns,l_match_values,l_set_columns,l_set_values);
1264: Begin
1265: clear_all_cache;
1266: bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1267: bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1268: bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1269: bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1270: bsc_aw_md_wrapper.update_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,
1271: l_match_columns,l_match_values,l_set_columns,l_set_values);
1272: --invalidate cache
1265: clear_all_cache;
1266: bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1267: bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1268: bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1269: bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1270: bsc_aw_md_wrapper.update_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,
1271: l_match_columns,l_match_values,l_set_columns,l_set_values);
1272: --invalidate cache
1273: clear_all_cache;
1303: l_set_columns dbms_sql.varchar2_table;
1304: l_set_values dbms_sql.varchar2_table;
1305: Begin
1306: clear_all_cache;
1307: bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1308: bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1309: bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1310: bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1311: bsc_aw_md_wrapper.update_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,
1304: l_set_values dbms_sql.varchar2_table;
1305: Begin
1306: clear_all_cache;
1307: bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1308: bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1309: bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1310: bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1311: bsc_aw_md_wrapper.update_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,
1312: l_match_columns,l_match_values,l_set_columns,l_set_values);
1305: Begin
1306: clear_all_cache;
1307: bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1308: bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1309: bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1310: bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1311: bsc_aw_md_wrapper.update_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,
1312: l_match_columns,l_match_values,l_set_columns,l_set_values);
1313: clear_all_cache;
1306: clear_all_cache;
1307: bsc_aw_utility.parse_parameter_values(p_match_columns,',',l_match_columns);
1308: bsc_aw_utility.parse_parameter_values(p_match_values,',',l_match_values);
1309: bsc_aw_utility.parse_parameter_values(p_set_columns,',',l_set_columns);
1310: bsc_aw_utility.parse_parameter_values(p_set_values,'^',l_set_values);
1311: bsc_aw_md_wrapper.update_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,
1312: l_match_columns,l_match_values,l_set_columns,l_set_values);
1313: clear_all_cache;
1314: Exception when others then
1467: */
1468: procedure get_dim_md(p_dim_name varchar2,p_dimension out nocopy bsc_aw_adapter_dim.dimension_r) is
1469: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
1470: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
1471: l_lg_index bsc_aw_utility.number_table; --level group
1472: l_rl_index bsc_aw_utility.number_table;--rec level
1473: l_lg varchar2(200);
1474: l_default_lg_name varchar2(200);
1475: l_level_name_dim varchar2(300);
1468: procedure get_dim_md(p_dim_name varchar2,p_dimension out nocopy bsc_aw_adapter_dim.dimension_r) is
1469: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
1470: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
1471: l_lg_index bsc_aw_utility.number_table; --level group
1472: l_rl_index bsc_aw_utility.number_table;--rec level
1473: l_lg varchar2(200);
1474: l_default_lg_name varchar2(200);
1475: l_level_name_dim varchar2(300);
1476: Begin
1486: else
1487: p_dimension.concat:='N';
1488: end if;
1489: p_dimension.property:=l_oo(i).property1;
1490: p_dimension.dim_type:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dimension source type',',');
1491: p_dimension.corrected:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'corrected',','),'N');
1492: p_dimension.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'recursive',','),'N');
1493: p_dimension.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'normal hier',','),'N');
1494: p_dimension.relation_name:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'relation name',',');
1487: p_dimension.concat:='N';
1488: end if;
1489: p_dimension.property:=l_oo(i).property1;
1490: p_dimension.dim_type:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dimension source type',',');
1491: p_dimension.corrected:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'corrected',','),'N');
1492: p_dimension.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'recursive',','),'N');
1493: p_dimension.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'normal hier',','),'N');
1494: p_dimension.relation_name:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'relation name',',');
1495: p_dimension.level_name_dim:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level name dim',',');
1488: end if;
1489: p_dimension.property:=l_oo(i).property1;
1490: p_dimension.dim_type:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dimension source type',',');
1491: p_dimension.corrected:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'corrected',','),'N');
1492: p_dimension.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'recursive',','),'N');
1493: p_dimension.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'normal hier',','),'N');
1494: p_dimension.relation_name:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'relation name',',');
1495: p_dimension.level_name_dim:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level name dim',',');
1496: elsif l_oo(i).object_type='filter cube' then
1489: p_dimension.property:=l_oo(i).property1;
1490: p_dimension.dim_type:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dimension source type',',');
1491: p_dimension.corrected:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'corrected',','),'N');
1492: p_dimension.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'recursive',','),'N');
1493: p_dimension.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'normal hier',','),'N');
1494: p_dimension.relation_name:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'relation name',',');
1495: p_dimension.level_name_dim:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level name dim',',');
1496: elsif l_oo(i).object_type='filter cube' then
1497: p_dimension.filter_variable:=l_oo(i).object;
1490: p_dimension.dim_type:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'dimension source type',',');
1491: p_dimension.corrected:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'corrected',','),'N');
1492: p_dimension.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'recursive',','),'N');
1493: p_dimension.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'normal hier',','),'N');
1494: p_dimension.relation_name:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'relation name',',');
1495: p_dimension.level_name_dim:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level name dim',',');
1496: elsif l_oo(i).object_type='filter cube' then
1497: p_dimension.filter_variable:=l_oo(i).object;
1498: elsif l_oo(i).object_type='limit cube' then
1491: p_dimension.corrected:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'corrected',','),'N');
1492: p_dimension.recursive:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'recursive',','),'N');
1493: p_dimension.recursive_norm_hier:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'normal hier',','),'N');
1494: p_dimension.relation_name:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'relation name',',');
1495: p_dimension.level_name_dim:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level name dim',',');
1496: elsif l_oo(i).object_type='filter cube' then
1497: p_dimension.filter_variable:=l_oo(i).object;
1498: elsif l_oo(i).object_type='limit cube' then
1499: p_dimension.limit_variable:=l_oo(i).object;
1530: end if;
1531: --get level group levels, relations, data source
1532: for i in 1..l_oo.count loop
1533: if l_oo(i).object_type='dimension level' then
1534: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level group',','),l_default_lg_name);
1535: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count+1).level_name:=l_oo(i).object;
1536: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count).position:=
1537: to_number(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'position',','));
1538: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count).pk.pk:=
1533: if l_oo(i).object_type='dimension level' then
1534: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level group',','),l_default_lg_name);
1535: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count+1).level_name:=l_oo(i).object;
1536: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count).position:=
1537: to_number(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'position',','));
1538: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count).pk.pk:=
1539: bsc_aw_utility.get_parameter_value(l_oo(i).property1,'pk',',');
1540: elsif l_oo(i).object_type='recursive level' then
1541: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level group',','),l_default_lg_name);
1535: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count+1).level_name:=l_oo(i).object;
1536: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count).position:=
1537: to_number(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'position',','));
1538: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count).pk.pk:=
1539: bsc_aw_utility.get_parameter_value(l_oo(i).property1,'pk',',');
1540: elsif l_oo(i).object_type='recursive level' then
1541: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level group',','),l_default_lg_name);
1542: p_dimension.level_groups(l_lg_index(l_lg)).rec_levels(p_dimension.level_groups(l_lg_index(l_lg)).rec_levels.count+1).level_name:=
1543: l_oo(i).object;
1537: to_number(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'position',','));
1538: p_dimension.level_groups(l_lg_index(l_lg)).levels(p_dimension.level_groups(l_lg_index(l_lg)).levels.count).pk.pk:=
1539: bsc_aw_utility.get_parameter_value(l_oo(i).property1,'pk',',');
1540: elsif l_oo(i).object_type='recursive level' then
1541: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'level group',','),l_default_lg_name);
1542: p_dimension.level_groups(l_lg_index(l_lg)).rec_levels(p_dimension.level_groups(l_lg_index(l_lg)).rec_levels.count+1).level_name:=
1543: l_oo(i).object;
1544: l_rl_index(l_oo(i).object):=p_dimension.level_groups(l_lg_index(l_lg)).rec_levels.count;
1545: end if;
1548: for i in 1..l_oor.count loop
1549: if l_oor(i).relation_type='dimension kpi' then
1550: p_dimension.kpi_for_dim(p_dimension.kpi_for_dim.count+1).kpi:=l_oor(i).relation_object;
1551: elsif l_oor(i).relation_type='zero code level' then
1552: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'level group',','),l_default_lg_name);
1553: p_dimension.level_groups(l_lg_index(l_lg)).zero_levels(p_dimension.level_groups(l_lg_index(l_lg)).zero_levels.count+1).level_name:=
1554: l_oor(i).relation_object;
1555: p_dimension.level_groups(l_lg_index(l_lg)).zero_levels(p_dimension.level_groups(l_lg_index(l_lg)).zero_levels.count).child_level_name:=
1556: l_oor(i).object;
1554: l_oor(i).relation_object;
1555: p_dimension.level_groups(l_lg_index(l_lg)).zero_levels(p_dimension.level_groups(l_lg_index(l_lg)).zero_levels.count).child_level_name:=
1556: l_oor(i).object;
1557: elsif l_oor(i).relation_type='recursive parent level' then
1558: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'level group',','),l_default_lg_name);
1559: p_dimension.level_groups(l_lg_index(l_lg)).rec_levels(l_rl_index(l_oor(i).relation_object)).child_level_name:=l_oor(i).object;
1560: elsif l_oor(i).relation_type='parent level' then
1561: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'level group',','),l_default_lg_name);
1562: p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count+1).child_level:=
1557: elsif l_oor(i).relation_type='recursive parent level' then
1558: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'level group',','),l_default_lg_name);
1559: p_dimension.level_groups(l_lg_index(l_lg)).rec_levels(l_rl_index(l_oor(i).relation_object)).child_level_name:=l_oor(i).object;
1560: elsif l_oor(i).relation_type='parent level' then
1561: l_lg:=nvl(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'level group',','),l_default_lg_name);
1562: p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count+1).child_level:=
1563: l_oor(i).object;
1564: p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count).parent_level:=
1565: l_oor(i).relation_object;
1563: l_oor(i).object;
1564: p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count).parent_level:=
1565: l_oor(i).relation_object;
1566: p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count).parent_pk:=
1567: bsc_aw_utility.get_parameter_value(l_oor(i).property1,'pk',',');
1568: p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count).child_fk:=
1569: bsc_aw_utility.get_parameter_value(l_oor(i).property1,'fk',',');
1570: end if;
1571: end loop;
1565: l_oor(i).relation_object;
1566: p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count).parent_pk:=
1567: bsc_aw_utility.get_parameter_value(l_oor(i).property1,'pk',',');
1568: p_dimension.level_groups(l_lg_index(l_lg)).parent_child(p_dimension.level_groups(l_lg_index(l_lg)).parent_child.count).child_fk:=
1569: bsc_aw_utility.get_parameter_value(l_oor(i).property1,'fk',',');
1570: end if;
1571: end loop;
1572: --get the snowflake info also
1573: for i in 1..p_dimension.level_groups.count loop
1573: for i in 1..p_dimension.level_groups.count loop
1574: for j in 1..p_dimension.level_groups(i).levels.count loop
1575: l_oo.delete;
1576: get_bsc_olap_object(null,'dimension',p_dimension.level_groups(i).levels(j).level_name,'dimension',l_oo);
1577: p_dimension.level_groups(i).levels(j).relation_name:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'relation name',',');
1578: p_dimension.level_groups(i).levels(j).level_name_dim:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'level name dim',',');
1579: end loop;
1580: end loop;
1581: Exception when others then
1574: for j in 1..p_dimension.level_groups(i).levels.count loop
1575: l_oo.delete;
1576: get_bsc_olap_object(null,'dimension',p_dimension.level_groups(i).levels(j).level_name,'dimension',l_oo);
1577: p_dimension.level_groups(i).levels(j).relation_name:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'relation name',',');
1578: p_dimension.level_groups(i).levels(j).level_name_dim:=bsc_aw_utility.get_parameter_value(l_oo(1).property1,'level name dim',',');
1579: end loop;
1580: end loop;
1581: Exception when others then
1582: log_n('Exception in get_dim_md '||sqlerrm);
1610: Begin
1611: bsc_aw_md_api.get_bsc_olap_object('bsc aw version','bsc aw version','bsc aw version','bsc aw version',l_oo);
1612: if l_oo.count>0 then
1613: update_olap_object('bsc aw version','bsc aw version','bsc aw version','bsc aw version',
1614: null,null,'olap_object,olap_object_type',bsc_aw_utility.g_upgrade_version||'^bsc aw version');
1615: else /*first time*/
1616: insert_olap_object('bsc aw version','bsc aw version',to_char(bsc_aw_utility.g_upgrade_version),'bsc aw version','bsc aw version',
1617: 'bsc aw version',null);
1618: end if;
1612: if l_oo.count>0 then
1613: update_olap_object('bsc aw version','bsc aw version','bsc aw version','bsc aw version',
1614: null,null,'olap_object,olap_object_type',bsc_aw_utility.g_upgrade_version||'^bsc aw version');
1615: else /*first time*/
1616: insert_olap_object('bsc aw version','bsc aw version',to_char(bsc_aw_utility.g_upgrade_version),'bsc aw version','bsc aw version',
1617: 'bsc aw version',null);
1618: end if;
1619: Exception when others then
1620: log_n('Exception in set_upgrade_version '||sqlerrm);
1624: -----------------------------
1625: procedure init_all is
1626: Begin
1627: bsc_aw_md_wrapper.set_context('AW');
1628: g_debug:=bsc_aw_utility.g_debug;
1629: Exception when others then
1630: null;
1631: End;
1632:
1631: End;
1632:
1633: procedure log(p_message varchar2) is
1634: Begin
1635: bsc_aw_utility.log(p_message);
1636: Exception when others then
1637: null;
1638: End;
1639: