[Home] [Help]
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
136: return false; --do not create the kpi. already present
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
225: if p_dim.levels(i).level_name=l_olap_object(j).object then
226: p_dim.levels(i).position:=bsc_aw_utility.get_parameter_value(l_olap_object(j).property1,'position',',');
227: exit;
228: end if;
229: end loop;
230: end loop;
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);
256: if p_dim_set.calendar.periodicity.count>1 and l_pc.count=0 then
257: log('correct_relevant_cal_hier could not get the relevant cal hier');
258: raise bsc_aw_utility.g_exception;
259: end if;
260: p_dim_set.calendar.parent_child.delete;
261: for i in 1..l_pc.count loop
262: p_dim_set.calendar.parent_child(i):=l_pc(i);
415: /*if there are BALANCE LAST VALUE measures add the .period and .year property, used in measure name dim or cubes in 9i */
416: /* NOTE the period cube and year cube name change in create_PT_comp_names for 9i*/
417: for i in 1..p_dim_set.measure.count loop
418: if p_dim_set.measure(i).measure_type=g_balance_last_value_prop then
419: bsc_aw_utility.merge_property(p_dim_set.measure(i).property,'period cube',null,p_dim_set.measure(i).measure||'.period');
420: bsc_aw_utility.merge_property(p_dim_set.measure(i).property,'year cube',null,p_dim_set.measure(i).measure||'.year');
421: end if;
422: end loop;
423: Exception when others then
416: /* NOTE the period cube and year cube name change in create_PT_comp_names for 9i*/
417: for i in 1..p_dim_set.measure.count loop
418: if p_dim_set.measure(i).measure_type=g_balance_last_value_prop then
419: bsc_aw_utility.merge_property(p_dim_set.measure(i).property,'period cube',null,p_dim_set.measure(i).measure||'.period');
420: bsc_aw_utility.merge_property(p_dim_set.measure(i).property,'year cube',null,p_dim_set.measure(i).measure||'.year');
421: end if;
422: end loop;
423: Exception when others then
424: log_n('Exception in get_dim_set_measures '||sqlerrm);
583: end if;
584: end loop;
585: --set lower and correct keys
586: for i in 1..p_data_source.dim.count loop
587: if bsc_aw_utility.in_array(l_ds_dim_set,p_data_source.dim(i).dim_name)=false then
588: l_level:=p_data_source.dim(i).levels(1);
589: p_data_source.dim(i).levels.delete;
590: p_data_source.dim(i).levels(p_data_source.dim(i).levels.count+1):=l_level;
591: end if;
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
645: p_base_table.level_status(i):='extra';
646: else
647: p_base_table.level_status(i):='skip';
648: end if;
689: l_level_string(i):=l_level_string(i)||p_data_source(i).std_dim(j).levels(1).level_name||'.';
690: end loop;
691: l_level_string(i):=l_level_string(i)||p_data_source(i).calendar.periodicity(1).periodicity;
692: --
693: bsc_aw_utility.merge_value(l_distinct_levels,l_level_string(i));
694: end loop;
695: if g_debug then
696: log('create_dimset_data_source_sql: Distinct level combinations');
697: for i in 1..l_distinct_levels.count loop
732: p_new_data_source in out nocopy data_source_r
733: ) is
734: --
735: j integer;
736: l_measure_index bsc_aw_utility.number_table;
737: l_balance_loaded_column dbms_sql.varchar2_table;
738: Begin
739: p_new_data_source.dim:=p_data_source(1).dim;
740: p_new_data_source.std_dim:=p_data_source(1).std_dim;
742: p_new_data_source.data_source_PT:=p_data_source(1).data_source_PT;
743: /*here we make a copy of data_source_PT from the first DS of the dimset. to make a new DS with many DS in it, all the DS must share the
744: same PT characteristics */
745: --get the B tables
746: bsc_aw_utility.init_is_new_value(1);
747: for i in 1..p_data_source.count loop
748: for j in 1..p_data_source(i).base_tables.count loop
749: if bsc_aw_utility.is_new_value(p_data_source(i).base_tables(j).base_table_name,1) then
750: p_new_data_source.base_tables(p_new_data_source.base_tables.count+1):=p_data_source(i).base_tables(j);
745: --get the B tables
746: bsc_aw_utility.init_is_new_value(1);
747: for i in 1..p_data_source.count loop
748: for j in 1..p_data_source(i).base_tables.count loop
749: if bsc_aw_utility.is_new_value(p_data_source(i).base_tables(j).base_table_name,1) then
750: p_new_data_source.base_tables(p_new_data_source.base_tables.count+1):=p_data_source(i).base_tables(j);
751: end if;
752: end loop;
753: end loop;
751: end if;
752: end loop;
753: end loop;
754: --get the measures in all the datasource
755: bsc_aw_utility.init_is_new_value(1);
756: for i in 1..p_data_source.count loop
757: for j in 1..p_data_source(i).measure.count loop
758: if bsc_aw_utility.is_new_value(p_data_source(i).measure(j).measure,1) then
759: p_new_data_source.measure(p_new_data_source.measure.count+1):=
754: --get the measures in all the datasource
755: bsc_aw_utility.init_is_new_value(1);
756: for i in 1..p_data_source.count loop
757: for j in 1..p_data_source(i).measure.count loop
758: if bsc_aw_utility.is_new_value(p_data_source(i).measure(j).measure,1) then
759: p_new_data_source.measure(p_new_data_source.measure.count+1):=
760: p_data_source(i).measure(j);
761: end if;
762: end loop;
763: end loop;
764: /*merge all the properties like dimension filter or balance last value */
765: for i in 1..p_data_source.count loop
766: for j in 1..p_data_source(i).property.count loop
767: bsc_aw_utility.merge_property(p_new_data_source.property,p_data_source(i).property(j).property_name,p_data_source(i).property(j).property_type,
768: p_data_source(i).property(j).property_value);
769: end loop;
770: end loop;
771: if g_debug then
787: end loop;
788: --time
789: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):='period,';
790: p_new_data_source.data_source_stmt_type(p_new_data_source.data_source_stmt.count):='dimension=time';
791: if bsc_aw_utility.get_property(p_new_data_source.property,g_balance_last_value_prop).property_value='Y' then
792: /*period.temp and year.temp */
793: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):=g_period_temp||',';
794: p_new_data_source.data_source_stmt_type(p_new_data_source.data_source_stmt.count):='temp time='||g_period_temp;
795: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):=g_year_temp||',';
811: /*balance loaded column */
812: for i in 1..p_new_data_source.measure.count loop
813: l_balance_loaded_column(i):=null;
814: if p_new_data_source.measure(i).measure_type=g_balance_last_value_prop then
815: l_balance_loaded_column(i):=bsc_aw_utility.get_property(p_new_data_source.measure(i).property,g_balance_loaded_column_prop).property_value;
816: if l_balance_loaded_column(i) is not null then /*balance loaded column is always summed up */
817: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):='SUM('||l_balance_loaded_column(i)||') '||
818: l_balance_loaded_column(i)||',';
819: p_new_data_source.data_source_stmt_type(p_new_data_source.data_source_stmt.count):='temp balance loaded column='||
868: end if;
869: end loop;
870: l_measure_index.delete;
871: /*balance loaded column temp balance loaded column=*/
872: if bsc_aw_utility.get_property(p_new_data_source.property,g_balance_last_value_prop).property_value='Y' then
873: for k in 1..p_new_data_source.measure.count loop
874: if p_new_data_source.measure(k).measure_type=g_balance_last_value_prop and l_balance_loaded_column(k) is not null then
875: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):=
876: '0 '||l_balance_loaded_column(k)||',';
909: p_new_data_source.data_source_stmt_type(p_new_data_source.data_source_stmt.count):=p_data_source(i).data_source_stmt_type(j);
910: /*if the new data source has balance last value and the individual data source does not, we have to force adding period temp and year temp
911: p_data_source(i) will not have period_temp and year_temp*/
912: if p_data_source(i).data_source_stmt_type(j)='dimension=time' then
913: if bsc_aw_utility.get_property(p_new_data_source.property,g_balance_last_value_prop).property_value='Y'
914: and bsc_aw_utility.get_property(p_data_source(i).property,g_balance_last_value_prop).property_value is null then
915: if p_data_source(i).data_source_stmt_type(j+1)<>'temp time='||g_period_temp then
916: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):='period '||g_period_temp||',';
917: p_new_data_source.data_source_stmt_type(p_new_data_source.data_source_stmt.count):='temp time='||g_period_temp;
910: /*if the new data source has balance last value and the individual data source does not, we have to force adding period temp and year temp
911: p_data_source(i) will not have period_temp and year_temp*/
912: if p_data_source(i).data_source_stmt_type(j)='dimension=time' then
913: if bsc_aw_utility.get_property(p_new_data_source.property,g_balance_last_value_prop).property_value='Y'
914: and bsc_aw_utility.get_property(p_data_source(i).property,g_balance_last_value_prop).property_value is null then
915: if p_data_source(i).data_source_stmt_type(j+1)<>'temp time='||g_period_temp then
916: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):='period '||g_period_temp||',';
917: p_new_data_source.data_source_stmt_type(p_new_data_source.data_source_stmt.count):='temp time='||g_period_temp;
918: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):='year '||g_year_temp||',';
941: end loop;
942: --time
943: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):='period,';
944: p_new_data_source.data_source_stmt_type(p_new_data_source.data_source_stmt.count):='sql group by stmt';
945: if bsc_aw_utility.get_property(p_new_data_source.property,g_balance_last_value_prop).property_value='Y' then
946: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):=g_period_temp||',';
947: p_new_data_source.data_source_stmt_type(p_new_data_source.data_source_stmt.count):='sql group by stmt';
948: p_new_data_source.data_source_stmt(p_new_data_source.data_source_stmt.count+1):=g_year_temp||',';
949: p_new_data_source.data_source_stmt_type(p_new_data_source.data_source_stmt.count):='sql group by stmt';
981: p_data_source.data_source_stmt.delete;
982: p_data_source.data_source_stmt_type.delete;
983: /*set the properties */
984: if is_filter_in_data_source(p_data_source)='Y' then
985: bsc_aw_utility.merge_property(p_data_source.property,'dimension filter',null,'Y');
986: end if;
987: if is_balance_last_value_in_DS(p_data_source)='Y' then
988: bsc_aw_utility.merge_property(p_data_source.property,g_balance_last_value_prop,null,'Y');
989: end if;
984: if is_filter_in_data_source(p_data_source)='Y' then
985: bsc_aw_utility.merge_property(p_data_source.property,'dimension filter',null,'Y');
986: end if;
987: if is_balance_last_value_in_DS(p_data_source)='Y' then
988: bsc_aw_utility.merge_property(p_data_source.property,g_balance_last_value_prop,null,'Y');
989: end if;
990: --for now, we only have 1 base table per data source. not any more...we can have the prj table as the second b table in the DS
991: for i in 1..p_data_source.base_tables.count loop
992: if i=1 then
1017: end loop;
1018: --time
1019: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count+1):='period||\''.\''||year period,';
1020: p_data_source.data_source_stmt_type(p_data_source.data_source_stmt.count):='dimension=time';
1021: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
1022: /*select period and year separately to load period.temp and year.temp. used for BALANCE LAST VALUE
1023: Q: classified as temp or measure? keep temp for now*/
1024: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count+1):='period '||g_period_temp||',';
1025: p_data_source.data_source_stmt_type(p_data_source.data_source_stmt.count):='temp time='||g_period_temp;
1067: end loop;
1068: /*balance loaded column */
1069: for j in 1..p_data_source.measure.count loop
1070: if p_data_source.measure(j).measure_type=g_balance_last_value_prop then
1071: l_balance_loaded_column:=bsc_aw_utility.get_property(p_data_source.measure(j).property,g_balance_loaded_column_prop).property_value;
1072: if l_balance_loaded_column is not null then
1073: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count+1):=l_balance_loaded_column||',';
1074: p_data_source.data_source_stmt_type(p_data_source.data_source_stmt.count):='temp balance loaded column='||p_data_source.measure(j).measure;
1075: end if;
1103: l_DS_dim_parent_child.delete;
1104: l_DS_dim_parent_child:=p_data_source.data_source_PT.dim_parent_child(
1105: p_data_source.data_source_PT.partition_template.hpt_data.hpt_dimensions(j).dim_name);
1106: if l_DS_dim_parent_child.count>0 then
1107: bsc_aw_utility.init_is_new_value(1);
1108: for k in 1..l_DS_dim_parent_child.count loop
1109: if bsc_aw_utility.is_new_value(l_DS_dim_parent_child(k).child_level,1) then
1110: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count+1):=','||l_DS_dim_parent_child(k).child_level;
1111: p_data_source.data_source_stmt_type(p_data_source.data_source_stmt.count):='sql from table';
1105: p_data_source.data_source_PT.partition_template.hpt_data.hpt_dimensions(j).dim_name);
1106: if l_DS_dim_parent_child.count>0 then
1107: bsc_aw_utility.init_is_new_value(1);
1108: for k in 1..l_DS_dim_parent_child.count loop
1109: if bsc_aw_utility.is_new_value(l_DS_dim_parent_child(k).child_level,1) then
1110: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count+1):=','||l_DS_dim_parent_child(k).child_level;
1111: p_data_source.data_source_stmt_type(p_data_source.data_source_stmt.count):='sql from table';
1112: end if;
1113: end loop;
1114: end if;
1115: end loop;
1116: if p_data_source.data_source_PT.cal_parent_child.count>0 then
1117: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count+1):=',(select distinct year pt_year';
1118: bsc_aw_utility.init_is_new_value(1);
1119: for j in 1..p_data_source.data_source_PT.cal_parent_child.count loop
1120: if bsc_aw_utility.is_new_value(p_data_source.data_source_PT.cal_parent_child(j).child_dim_name,1) then /*child_dim_name is db_column_name */
1121: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count):=p_data_source.data_source_stmt(p_data_source.data_source_stmt.count)||','||
1122: p_data_source.data_source_PT.cal_parent_child(j).child_dim_name;
1116: if p_data_source.data_source_PT.cal_parent_child.count>0 then
1117: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count+1):=',(select distinct year pt_year';
1118: bsc_aw_utility.init_is_new_value(1);
1119: for j in 1..p_data_source.data_source_PT.cal_parent_child.count loop
1120: if bsc_aw_utility.is_new_value(p_data_source.data_source_PT.cal_parent_child(j).child_dim_name,1) then /*child_dim_name is db_column_name */
1121: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count):=p_data_source.data_source_stmt(p_data_source.data_source_stmt.count)||','||
1122: p_data_source.data_source_PT.cal_parent_child(j).child_dim_name;
1123: end if;
1124: if bsc_aw_utility.is_new_value(p_data_source.data_source_PT.cal_parent_child(j).parent_dim_name,1) then /*parent_dim_name is db_column_name */
1120: if bsc_aw_utility.is_new_value(p_data_source.data_source_PT.cal_parent_child(j).child_dim_name,1) then /*child_dim_name is db_column_name */
1121: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count):=p_data_source.data_source_stmt(p_data_source.data_source_stmt.count)||','||
1122: p_data_source.data_source_PT.cal_parent_child(j).child_dim_name;
1123: end if;
1124: if bsc_aw_utility.is_new_value(p_data_source.data_source_PT.cal_parent_child(j).parent_dim_name,1) then /*parent_dim_name is db_column_name */
1125: p_data_source.data_source_stmt(p_data_source.data_source_stmt.count):=p_data_source.data_source_stmt(p_data_source.data_source_stmt.count)||','||
1126: p_data_source.data_source_PT.cal_parent_child(j).parent_dim_name;
1127: end if;
1128: end loop;
1242: Begin
1243: p_base_table.base_table_sql.delete;
1244: l_aw_table_fk_driver_only:=false;
1245: if p_data_source.ds_type='inc' then
1246: if bsc_aw_utility.in_array(p_base_table.level_status,'extra') then
1247: l_aw_table_fk_driver_only:=true;
1248: end if;
1249: end if;
1250: l_from_sql.delete;
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);
1278: raise bsc_aw_utility.g_exception;
1279: end if;
1280: l_base_aw_join_flag(i):='Y';
1281: --first entry is the data source level
1282: p_base_table.base_table_sql(p_base_table.base_table_sql.count+1):=l_pc_subset(1).child_level||'.'||l_pc_subset(1).child_fk||',';
1384: end if;
1385: end if;
1386: --now the measures
1387: l_remove_agg_flag:=false;
1388: if bsc_aw_utility.in_array(p_base_table.level_status,'lower')=false and bsc_aw_utility.in_array(p_base_table.level_status,'extra')=false
1389: and p_base_table.periodicity.periodicity=p_data_source.calendar.periodicity(1).periodicity then
1390: --there are no keys with rollup or there are no extra keys and the periodicity of base is same as dimset
1391: --this means we can remove the agg function
1392: l_remove_agg_flag:=true;
1392: l_remove_agg_flag:=true;
1393: --see if count is a part of the agg function. if yes, we have to force agg, make l_remove_agg_flag:=false
1394: --we saw if we can hardcode 1 in parse_out_agg_function. but AW threw error
1395: --ORA-34738: (NOUPDATE) A severe problem has been detected. Analytic workspace operations have been disabled.
1396: --ORA-06512: at "APPS.BSC_AW_UTILITY", line 466
1397: --ORA-06512: at "APPS.BSC_AW_LOAD", line 112. it was confusing 1 with true/false 1 so we are forced to have agg when count is involved
1398: --maybe we can use sql fetch c1 loop into...but for now, lets keep the agg when count is involved
1399: --database does not suffer too much in perf with a group by. note here that there is no joins involbed. its just group by on the
1400: --base table
1405: end if;
1406: end loop;
1407: end if;
1408: for i in 1..p_data_source.measure.count loop
1409: l_balance_loaded_column:=bsc_aw_utility.get_property(p_data_source.measure(i).property,g_balance_loaded_column_prop).property_value;
1410: if l_remove_agg_flag then
1411: --l_formula has no agg, we can have 3 types of agg in BSC
1412: --Apply aggregation method to the each element of the formula, e.g.: SUM(source_column1)/SUM(source_column2)
1413: --Apply aggregation method to the overall formula, e.g.: SUM(source_column1/source_column2)
1411: --l_formula has no agg, we can have 3 types of agg in BSC
1412: --Apply aggregation method to the each element of the formula, e.g.: SUM(source_column1)/SUM(source_column2)
1413: --Apply aggregation method to the overall formula, e.g.: SUM(source_column1/source_column2)
1414: --Formulas between 2 calculated Measures e.g.: SUM(source_col1/source_col2)/AVG(source_col3+source_col4)
1415: bsc_aw_utility.parse_out_agg_function(p_data_source.measure(i).formula,l_formula);
1416: p_base_table.base_table_sql(p_base_table.base_table_sql.count+1):=l_formula||' '||p_data_source.measure(i).measure||',';
1417: if p_data_source.measure(i).measure_type=g_balance_last_value_prop and l_balance_loaded_column is not null then
1418: p_base_table.base_table_sql(p_base_table.base_table_sql.count+1):='decode('||l_balance_loaded_column||',\''Y\'',1,0) '||
1419: l_balance_loaded_column||',';
1518: End;
1519:
1520: procedure drop_kpi_objects_aw(p_kpi varchar2) is
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
1529: --get_kpi_olap_objects will only populate l_objects when olap object type is not null
1530: for i in 1..l_objects.count loop
1531: if l_flag(i)='N' and l_objects(i).object_type <> 'partition template' and
1532: l_objects(i).object_type <> 'dimension' and l_objects(i).object_type <> 'composite' then
1533: bsc_aw_utility.delete_aw_object(l_objects(i).object_name);
1534: l_flag(i):='Y';
1535: end if;
1536: end loop;
1537: --partition template
1536: end loop;
1537: --partition template
1538: for i in 1..l_objects.count loop
1539: if l_flag(i)='N' and l_objects(i).object_type = 'partition template' then
1540: bsc_aw_utility.delete_aw_object(l_objects(i).object_name);
1541: l_flag(i):='Y';
1542: end if;
1543: end loop;
1544: --composite
1543: end loop;
1544: --composite
1545: for i in 1..l_objects.count loop
1546: if l_flag(i)='N' and l_objects(i).object_type = 'composite' then
1547: bsc_aw_utility.delete_aw_object(l_objects(i).object_name);
1548: l_flag(i):='Y';
1549: end if;
1550: end loop;
1551: --dimensions
1550: end loop;
1551: --dimensions
1552: for i in 1..l_objects.count loop
1553: if l_flag(i)='N' and l_objects(i).object_type = 'dimension' then
1554: bsc_aw_utility.delete_aw_object(l_objects(i).object_name);
1555: l_flag(i):='Y';
1556: end if;
1557: end loop;
1558: --all the other objects
1557: end loop;
1558: --all the other objects
1559: for i in 1..l_objects.count loop
1560: if l_flag(i)='N' then
1561: bsc_aw_utility.delete_aw_object(l_objects(i).object_name);
1562: l_flag(i):='Y';
1563: end if;
1564: end loop;
1565: /*
1589: end loop;
1590: --
1591: for i in 1..l_olap_object.count loop
1592: if l_flag(i)='N' and l_olap_object(i).object_type='relational view' then
1593: bsc_aw_utility.execute_stmt_ne('drop view '||l_olap_object(i).object);
1594: l_flag(i):='Y';
1595: end if;
1596: end loop;
1597: --
1596: end loop;
1597: --
1598: for i in 1..l_olap_object.count loop
1599: if l_flag(i)='N' and l_olap_object(i).object_type='relational type' then
1600: bsc_aw_utility.execute_stmt_ne('drop type '||l_olap_object(i).object||'_tab'); --assume naming convention
1601: bsc_aw_utility.execute_stmt_ne('drop type '||l_olap_object(i).object);
1602: l_flag(i):='Y';
1603: end if;
1604: end loop;
1597: --
1598: for i in 1..l_olap_object.count loop
1599: if l_flag(i)='N' and l_olap_object(i).object_type='relational type' then
1600: bsc_aw_utility.execute_stmt_ne('drop type '||l_olap_object(i).object||'_tab'); --assume naming convention
1601: bsc_aw_utility.execute_stmt_ne('drop type '||l_olap_object(i).object);
1602: l_flag(i):='Y';
1603: end if;
1604: end loop;
1605: --
1646: create_aw_object_names gives names to cubes, programs etc
1647: */
1648: procedure create_aw_object_names(p_kpi in out nocopy kpi_r) is
1649: --
1650: l_dim_index bsc_aw_utility.number_table;--used to populate limit cubes into data source.dim
1651: l_measure_index bsc_aw_utility.number_table;--used to populate cubes into data source.measure
1652: Begin
1653: for i in 1..p_kpi.dim_set.count loop
1654: --
1647: */
1648: procedure create_aw_object_names(p_kpi in out nocopy kpi_r) is
1649: --
1650: l_dim_index bsc_aw_utility.number_table;--used to populate limit cubes into data source.dim
1651: l_measure_index bsc_aw_utility.number_table;--used to populate cubes into data source.measure
1652: Begin
1653: for i in 1..p_kpi.dim_set.count loop
1654: --
1655: p_kpi.dim_set(i).aggmap_operator.measure_dim:='measuredim.'||p_kpi.dim_set(i).dim_set||'.'||p_kpi.kpi;
1673: for j in 1..p_kpi.dim_set(i).dim.count loop
1674: l_dim_index(p_kpi.dim_set(i).dim(j).dim_name):=j;
1675: p_kpi.dim_set(i).dim(j).limit_cube:='kpi.'||p_kpi.kpi||'.'||p_kpi.dim_set(i).dim_set||'.'||p_kpi.dim_set(i).dim(j).dim_name||'.LB';
1676: p_kpi.dim_set(i).dim(j).reset_cube:='kpi.'||p_kpi.kpi||'.'||p_kpi.dim_set(i).dim_set||'.'||p_kpi.dim_set(i).dim(j).dim_name||'.RB';
1677: if nvl(bsc_aw_utility.get_parameter_value('NO LIMIT CUBE COMPOSITE'),'N')='N' then
1678: p_kpi.dim_set(i).dim(j).limit_cube_composite:='c.'||p_kpi.dim_set(i).dim(j).limit_cube;
1679: end if;
1680: p_kpi.dim_set(i).dim(j).aggregate_marker:='aggmark.'||p_kpi.kpi||'.'||p_kpi.dim_set(i).dim_set||'.'||p_kpi.dim_set(i).dim(j).dim_name;
1681: p_kpi.dim_set(i).dim(j).agg_map.agg_map:='aggmap.'||p_kpi.dim_set(i).dim(j).dim_name||'.'||p_kpi.dim_set(i).dim_set||'.'||p_kpi.kpi;
1684: --std dim
1685: for j in 1..p_kpi.dim_set(i).std_dim.count loop
1686: l_dim_index(p_kpi.dim_set(i).std_dim(j).dim_name):=j;
1687: p_kpi.dim_set(i).std_dim(j).limit_cube:='kpi.'||p_kpi.kpi||'.'||p_kpi.dim_set(i).dim_set||'.'||p_kpi.dim_set(i).std_dim(j).dim_name||'.LB';
1688: if nvl(bsc_aw_utility.get_parameter_value('NO LIMIT CUBE COMPOSITE'),'N')='N' then
1689: p_kpi.dim_set(i).std_dim(j).limit_cube_composite:='c.'||p_kpi.dim_set(i).std_dim(j).limit_cube;
1690: end if;
1691: end loop;
1692: --time limit cube
1690: end if;
1691: end loop;
1692: --time limit cube
1693: p_kpi.dim_set(i).calendar.limit_cube:='kpi.'||p_kpi.kpi||'.'||p_kpi.dim_set(i).dim_set||'.'||p_kpi.dim_set(i).calendar.aw_dim||'.LB';
1694: if nvl(bsc_aw_utility.get_parameter_value('NO LIMIT CUBE COMPOSITE'),'N')='N' then
1695: p_kpi.dim_set(i).calendar.limit_cube_composite:='c.'||p_kpi.dim_set(i).calendar.limit_cube;
1696: end if;
1697: p_kpi.dim_set(i).calendar.aggregate_marker:='aggmark.'||p_kpi.kpi||'.'||p_kpi.dim_set(i).dim_set||'.'||p_kpi.dim_set(i).calendar.aw_dim;
1698: /*also add aggmap for calendar. used to aggregate non bal measures uptime if bal measure also present in dimset */
1751: p_kpi.target_dim_set(i).inc_load_program_parallel.program_name:=p_kpi.dim_set(i).inc_load_program_parallel.program_name||'.tgt';
1752: --for 10g, targets will need a separate program so it can run in parallel
1753: for j in 1..p_kpi.target_dim_set(i).dim.count loop
1754: p_kpi.target_dim_set(i).dim(j).limit_cube:=p_kpi.dim_set(i).dim(j).limit_cube||'.tgt';
1755: if nvl(bsc_aw_utility.get_parameter_value('NO LIMIT CUBE COMPOSITE'),'N')='N' then
1756: p_kpi.target_dim_set(i).dim(j).limit_cube_composite:='c.'||p_kpi.target_dim_set(i).dim(j).limit_cube;
1757: end if;
1758: end loop;
1759: --std dim
1758: end loop;
1759: --std dim
1760: for j in 1..p_kpi.target_dim_set(i).std_dim.count loop
1761: p_kpi.target_dim_set(i).std_dim(j).limit_cube:=p_kpi.dim_set(i).std_dim(j).limit_cube||'.tgt';
1762: if nvl(bsc_aw_utility.get_parameter_value('NO LIMIT CUBE COMPOSITE'),'N')='N' then
1763: p_kpi.target_dim_set(i).std_dim(j).limit_cube_composite:='c.'||p_kpi.target_dim_set(i).std_dim(j).limit_cube;
1764: end if;
1765: end loop;
1766: --time limit cube
1764: end if;
1765: end loop;
1766: --time limit cube
1767: p_kpi.target_dim_set(i).calendar.limit_cube:=p_kpi.dim_set(i).calendar.limit_cube||'.tgt';
1768: if nvl(bsc_aw_utility.get_parameter_value('NO LIMIT CUBE COMPOSITE'),'N')='N' then
1769: p_kpi.target_dim_set(i).calendar.limit_cube_composite:='c.'||p_kpi.target_dim_set(i).calendar.limit_cube;
1770: end if;
1771: --
1772: create_PT_comp_names(p_kpi.kpi,p_kpi.target_dim_set(i));
1821: procedure create_PT_comp_names(p_kpi varchar2,p_dimset in out nocopy dim_set_r) is
1822: l_kpi varchar2(100); --just used to name objects
1823: l_composite_name varchar2(200);
1824: l_countvar_flag boolean;
1825: composite_index bsc_aw_utility.number_table;
1826: Begin
1827: l_kpi:=p_kpi;
1828: if p_dimset.dim_set_type='target' then
1829: l_kpi:=l_kpi||'.tgt';
1837: p_dimset.measurename_dim:='measurename.'||p_dimset.dim_set||'.'||p_kpi;
1838: l_composite_name:='comp.'||p_dimset.dim_set||'.'||l_kpi;
1839: --
1840: l_countvar_flag:=check_countvar_cube_needed(p_dimset);
1841: if bsc_aw_utility.get_db_version >=10 and nvl(bsc_aw_utility.get_parameter_value('NO DATACUBE'),'N')='N' then
1842: --
1843: p_dimset.cube_design:='datacube';
1844: if p_dimset.number_partitions>0 then
1845: --dimset can have multiple partition templates to support PT for countvar cubes when we have to use compressed composites
1930: p_dimset.cube_set(1).cube.cube_datatype:='number';
1931: p_dimset.cube_set(1).fcst_cube.cube_name:=p_dimset.cube_set(1).cube.cube_name||'.fcst';
1932: p_dimset.cube_set(1).fcst_cube.cube_datatype:='number';
1933: if l_countvar_flag then
1934: if bsc_aw_utility.get_property(p_dimset.property,'aggcount').property_value='Y' then
1935: null;
1936: else
1937: p_dimset.cube_set(1).countvar_cube.cube_name:=p_dimset.cube_set(1).cube.cube_name||'.countvar'; --countvar not used in targets
1938: p_dimset.cube_set(1).countvar_cube.cube_datatype:='integer';
2223: Begin
2224: l_comp_dimensions:=get_comp_dimensions(p_dim_set);
2225: --first create the composites
2226: for i in 1..p_dim_set.composite.count loop
2227: if bsc_aw_utility.in_array(l_comp_created,p_dim_set.composite(i).composite_name)=false then
2228: p_dim_set.composite(i).composite_dimensions:=l_comp_dimensions;
2229: g_stmt:='dfn '||p_dim_set.composite(i).composite_name||' composite <';
2230: for j in 1..p_dim_set.composite(i).composite_dimensions.count loop
2231: g_stmt:=g_stmt||p_dim_set.composite(i).composite_dimensions(j)||' ';
2277: g_stmt:='dfn '||p_partition_template.template_name||' PARTITION TEMPLATE <';
2278: for i in 1..p_partition_template.template_dimensions.count loop
2279: g_stmt:=g_stmt||' '||p_partition_template.template_dimensions(i);
2280: end loop;
2281: g_stmt:=g_stmt||'> -'||bsc_aw_utility.g_newline;
2282: g_stmt:=g_stmt||'partition by '||p_partition_template.template_type||' ('||p_partition_template.template_dim||') -'||bsc_aw_utility.g_newline;
2283: g_stmt:=g_stmt||'( -'||bsc_aw_utility.g_newline;
2284: for i in 1..p_partition_template.template_partitions.count loop
2285: g_stmt:=g_stmt||'PARTITION '||p_partition_template.template_partitions(i).partition_name||' VALUES ('||
2278: for i in 1..p_partition_template.template_dimensions.count loop
2279: g_stmt:=g_stmt||' '||p_partition_template.template_dimensions(i);
2280: end loop;
2281: g_stmt:=g_stmt||'> -'||bsc_aw_utility.g_newline;
2282: g_stmt:=g_stmt||'partition by '||p_partition_template.template_type||' ('||p_partition_template.template_dim||') -'||bsc_aw_utility.g_newline;
2283: g_stmt:=g_stmt||'( -'||bsc_aw_utility.g_newline;
2284: for i in 1..p_partition_template.template_partitions.count loop
2285: g_stmt:=g_stmt||'PARTITION '||p_partition_template.template_partitions(i).partition_name||' VALUES ('||
2286: p_partition_template.template_partitions(i).partition_dim_value||') <';
2279: g_stmt:=g_stmt||' '||p_partition_template.template_dimensions(i);
2280: end loop;
2281: g_stmt:=g_stmt||'> -'||bsc_aw_utility.g_newline;
2282: g_stmt:=g_stmt||'partition by '||p_partition_template.template_type||' ('||p_partition_template.template_dim||') -'||bsc_aw_utility.g_newline;
2283: g_stmt:=g_stmt||'( -'||bsc_aw_utility.g_newline;
2284: for i in 1..p_partition_template.template_partitions.count loop
2285: g_stmt:=g_stmt||'PARTITION '||p_partition_template.template_partitions(i).partition_name||' VALUES ('||
2286: p_partition_template.template_partitions(i).partition_dim_value||') <';
2287: for j in 1..p_partition_template.template_partitions(i).partition_axis.count loop
2294: end loop;
2295: g_stmt:=g_stmt||'>';
2296: end if;
2297: end loop;
2298: g_stmt:=g_stmt||'> -'||bsc_aw_utility.g_newline;
2299: end loop;
2300: g_stmt:=g_stmt||')';
2301: bsc_aw_dbms_aw.execute(g_stmt);
2302: end if;
2410: l_stmt:=l_stmt||' >';
2411: end if;
2412: end loop;
2413: l_stmt:=l_stmt||' >';
2414: if bsc_aw_utility.get_property(p_dim_set.property,'aggcount').property_value='Y' then
2415: l_stmt:=l_stmt||' WITH AGGCOUNT';
2416: end if;
2417: bsc_aw_dbms_aw.execute(l_stmt);
2418: Exception when others then
2457: --if the dimset has 4 cubes and we want to agg only 1, we will limit p_agg_map.measure_dim to just that cube
2458: for i in 1..p_dim_set.dim.count loop
2459: if p_dim_set.dim(i).agg_map.created='Y' then --create_composite has already set this flag
2460: g_commands.delete;
2461: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.dim(i).agg_map.agg_map||' aggmap ');
2462: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.dim(i).relation_name||' OPERATOR '||
2463: p_dim_set.dim(i).agg_map.aggmap_operator.opvar||' ARGS '||p_dim_set.dim(i).agg_map.aggmap_operator.argvar);
2464: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_dim_set.dim(i).agg_map.aggmap_operator.measure_dim);
2465: bsc_aw_utility.exec_aggmap_commands(p_dim_set.dim(i).agg_map.agg_map,g_commands);
2458: for i in 1..p_dim_set.dim.count loop
2459: if p_dim_set.dim(i).agg_map.created='Y' then --create_composite has already set this flag
2460: g_commands.delete;
2461: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.dim(i).agg_map.agg_map||' aggmap ');
2462: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.dim(i).relation_name||' OPERATOR '||
2463: p_dim_set.dim(i).agg_map.aggmap_operator.opvar||' ARGS '||p_dim_set.dim(i).agg_map.aggmap_operator.argvar);
2464: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_dim_set.dim(i).agg_map.aggmap_operator.measure_dim);
2465: bsc_aw_utility.exec_aggmap_commands(p_dim_set.dim(i).agg_map.agg_map,g_commands);
2466: /*had aggindex=no. from olap doc, if aggindex=no, then dim outside the composite are aggregated on the fly when natrigger property
2460: g_commands.delete;
2461: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.dim(i).agg_map.agg_map||' aggmap ');
2462: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.dim(i).relation_name||' OPERATOR '||
2463: p_dim_set.dim(i).agg_map.aggmap_operator.opvar||' ARGS '||p_dim_set.dim(i).agg_map.aggmap_operator.argvar);
2464: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_dim_set.dim(i).agg_map.aggmap_operator.measure_dim);
2465: bsc_aw_utility.exec_aggmap_commands(p_dim_set.dim(i).agg_map.agg_map,g_commands);
2466: /*had aggindex=no. from olap doc, if aggindex=no, then dim outside the composite are aggregated on the fly when natrigger property
2467: is set. for us, all dim are in composite. so no need to have this */
2468: end if;
2461: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.dim(i).agg_map.agg_map||' aggmap ');
2462: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.dim(i).relation_name||' OPERATOR '||
2463: p_dim_set.dim(i).agg_map.aggmap_operator.opvar||' ARGS '||p_dim_set.dim(i).agg_map.aggmap_operator.argvar);
2464: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_dim_set.dim(i).agg_map.aggmap_operator.measure_dim);
2465: bsc_aw_utility.exec_aggmap_commands(p_dim_set.dim(i).agg_map.agg_map,g_commands);
2466: /*had aggindex=no. from olap doc, if aggindex=no, then dim outside the composite are aggregated on the fly when natrigger property
2467: is set. for us, all dim are in composite. so no need to have this */
2468: end if;
2469: end loop;
2468: end if;
2469: end loop;
2470: /*create aggmap of calendar */
2471: g_commands.delete;
2472: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.calendar.agg_map.agg_map||' aggmap ');
2473: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.calendar.relation_name||' OPERATOR '||
2474: p_dim_set.calendar.agg_map.aggmap_operator.opvar||' ARGS '||p_dim_set.calendar.agg_map.aggmap_operator.argvar);
2475: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_dim_set.calendar.agg_map.aggmap_operator.measure_dim);
2476: bsc_aw_utility.exec_aggmap_commands(p_dim_set.calendar.agg_map.agg_map,g_commands);
2469: end loop;
2470: /*create aggmap of calendar */
2471: g_commands.delete;
2472: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.calendar.agg_map.agg_map||' aggmap ');
2473: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.calendar.relation_name||' OPERATOR '||
2474: p_dim_set.calendar.agg_map.aggmap_operator.opvar||' ARGS '||p_dim_set.calendar.agg_map.aggmap_operator.argvar);
2475: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_dim_set.calendar.agg_map.aggmap_operator.measure_dim);
2476: bsc_aw_utility.exec_aggmap_commands(p_dim_set.calendar.agg_map.agg_map,g_commands);
2477: end if;
2471: g_commands.delete;
2472: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.calendar.agg_map.agg_map||' aggmap ');
2473: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.calendar.relation_name||' OPERATOR '||
2474: p_dim_set.calendar.agg_map.aggmap_operator.opvar||' ARGS '||p_dim_set.calendar.agg_map.aggmap_operator.argvar);
2475: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_dim_set.calendar.agg_map.aggmap_operator.measure_dim);
2476: bsc_aw_utility.exec_aggmap_commands(p_dim_set.calendar.agg_map.agg_map,g_commands);
2477: end if;
2478: Exception when others then
2479: log_n('Exception in create_agg_map '||sqlerrm);
2472: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.calendar.agg_map.agg_map||' aggmap ');
2473: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.calendar.relation_name||' OPERATOR '||
2474: p_dim_set.calendar.agg_map.aggmap_operator.opvar||' ARGS '||p_dim_set.calendar.agg_map.aggmap_operator.argvar);
2475: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_dim_set.calendar.agg_map.aggmap_operator.measure_dim);
2476: bsc_aw_utility.exec_aggmap_commands(p_dim_set.calendar.agg_map.agg_map,g_commands);
2477: end if;
2478: Exception when others then
2479: log_n('Exception in create_agg_map '||sqlerrm);
2480: raise;
2554: Begin
2555: --create the agg map
2556: g_commands.delete;
2557: l_flag:=false;
2558: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_agg_map.agg_map||' aggmap ');
2559: if p_dim_set.compressed='Y' then /* 5236161*/
2560: for i in 1..p_dim_set.measure.count loop
2561: if bsc_aw_utility.is_std_aggregation_function(p_dim_set.measure(i).agg_formula.agg_formula)='Y' then
2562: agg_formula:=p_dim_set.measure(i).agg_formula.agg_formula;
2557: l_flag:=false;
2558: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_agg_map.agg_map||' aggmap ');
2559: if p_dim_set.compressed='Y' then /* 5236161*/
2560: for i in 1..p_dim_set.measure.count loop
2561: if bsc_aw_utility.is_std_aggregation_function(p_dim_set.measure(i).agg_formula.agg_formula)='Y' then
2562: agg_formula:=p_dim_set.measure(i).agg_formula.agg_formula;
2563: if agg_formula is not null then
2564: exit;
2565: end if;
2566: end if;
2567: end loop;
2568: if agg_formula is null then
2569: log('Could not get an agg formula with std aggregation for CC');
2570: raise bsc_aw_utility.g_exception;
2571: end if;
2572: end if;
2573: --if regular aggmap, add time relation also
2574: if p_agg_map.property='normal' then
2576: l_flag:=true;
2577: if p_dim_set.compressed='Y' then
2578: --cannot have opvar, argvar or measuredim. so we have restricted implementation. all measures must have the same agg
2579: --formula. if the agg formula is diff, create_PT_comp_names would have set compressed to N
2580: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.calendar.relation_name||' OPERATOR '||
2581: agg_formula);
2582: else
2583: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.calendar.relation_name||' OPERATOR '||
2584: p_agg_map.aggmap_operator.opvar||' ARGS '||p_agg_map.aggmap_operator.argvar);
2579: --formula. if the agg formula is diff, create_PT_comp_names would have set compressed to N
2580: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.calendar.relation_name||' OPERATOR '||
2581: agg_formula);
2582: else
2583: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.calendar.relation_name||' OPERATOR '||
2584: p_agg_map.aggmap_operator.opvar||' ARGS '||p_agg_map.aggmap_operator.argvar);
2585: end if;
2586: end if;
2587: end if;
2588: for i in 1..p_dim_set.dim.count loop
2589: if p_dim_set.dim(i).agg_map.created='Y' then --create_composite has already set this flag
2590: l_flag:=true;
2591: if p_dim_set.compressed='Y' then
2592: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.dim(i).relation_name||' OPERATOR '||
2593: agg_formula);
2594: else
2595: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.dim(i).relation_name||' OPERATOR '||
2596: p_agg_map.aggmap_operator.opvar||' ARGS '||p_agg_map.aggmap_operator.argvar);
2591: if p_dim_set.compressed='Y' then
2592: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.dim(i).relation_name||' OPERATOR '||
2593: agg_formula);
2594: else
2595: bsc_aw_utility.add_g_commands(g_commands,'relation '||p_dim_set.dim(i).relation_name||' OPERATOR '||
2596: p_agg_map.aggmap_operator.opvar||' ARGS '||p_agg_map.aggmap_operator.argvar);
2597: end if;
2598: end if;
2599: end loop;
2599: end loop;
2600: if p_dim_set.compressed='Y' then
2601: null;
2602: else
2603: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_agg_map.aggmap_operator.measure_dim);
2604: end if;
2605: if l_flag then
2606: p_agg_map.created:='Y';
2607: bsc_aw_utility.exec_aggmap_commands(p_agg_map.agg_map,g_commands);
2603: bsc_aw_utility.add_g_commands(g_commands,'MEASUREDIM '||p_agg_map.aggmap_operator.measure_dim);
2604: end if;
2605: if l_flag then
2606: p_agg_map.created:='Y';
2607: bsc_aw_utility.exec_aggmap_commands(p_agg_map.agg_map,g_commands);
2608: else
2609: p_agg_map.created:='N';
2610: end if;
2611: Exception when others then
2652: g_commands.delete;
2653: if p_mode='initial' then
2654: set_program_property(p_dim_set.initial_load_program,p_dim_set.data_source);
2655: l_pgm:=p_dim_set.initial_load_program.program_name;
2656: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_pgm||' program');
2657: for i in 1..p_dim_set.data_source.count loop
2658: create_kpi_program(p_kpi,p_dim_set,p_dim_set.data_source(i));
2659: end loop;
2660: else
2659: end loop;
2660: else
2661: set_program_property(p_dim_set.inc_load_program,p_dim_set.inc_data_source);
2662: l_pgm:=p_dim_set.inc_load_program.program_name;
2663: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_pgm||' program');
2664: for i in 1..p_dim_set.inc_data_source.count loop
2665: create_kpi_program(p_kpi,p_dim_set,p_dim_set.inc_data_source(i));
2666: end loop;
2667: end if;
2664: for i in 1..p_dim_set.inc_data_source.count loop
2665: create_kpi_program(p_kpi,p_dim_set,p_dim_set.inc_data_source(i));
2666: end loop;
2667: end if;
2668: bsc_aw_utility.exec_program_commands(l_pgm,g_commands);
2669: Exception when others then
2670: log_n('Exception in create_kpi_program 2 '||sqlerrm);
2671: raise;
2672: End;
2691: l_ordered_b_tables dbms_sql.varchar2_table;
2692: l_balance_loaded_column varchar2(40);
2693: Begin
2694: for i in 1..p_data_source.base_tables.count loop
2695: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
2696: end loop;
2697: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
2698: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
2699: --see if there are any additional filter properties defined..used when partitions are involved
2693: Begin
2694: for i in 1..p_data_source.base_tables.count loop
2695: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
2696: end loop;
2697: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
2698: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
2699: --see if there are any additional filter properties defined..used when partitions are involved
2700: l_filter:=bsc_aw_utility.get_property(p_data_source.property,'datasource filter').property_value;
2701: if l_filter is not null then
2694: for i in 1..p_data_source.base_tables.count loop
2695: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
2696: end loop;
2697: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
2698: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
2699: --see if there are any additional filter properties defined..used when partitions are involved
2700: l_filter:=bsc_aw_utility.get_property(p_data_source.property,'datasource filter').property_value;
2701: if l_filter is not null then
2702: bsc_aw_utility.add_g_commands(g_commands,l_filter||' --');
2696: end loop;
2697: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
2698: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
2699: --see if there are any additional filter properties defined..used when partitions are involved
2700: l_filter:=bsc_aw_utility.get_property(p_data_source.property,'datasource filter').property_value;
2701: if l_filter is not null then
2702: bsc_aw_utility.add_g_commands(g_commands,l_filter||' --');
2703: end if;
2704: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2698: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
2699: --see if there are any additional filter properties defined..used when partitions are involved
2700: l_filter:=bsc_aw_utility.get_property(p_data_source.property,'datasource filter').property_value;
2701: if l_filter is not null then
2702: bsc_aw_utility.add_g_commands(g_commands,l_filter||' --');
2703: end if;
2704: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2705: bsc_aw_utility.add_g_commands(g_commands,'then do');
2706: /*temp variables period.temp and year.temp hold the period and year values. they are present in all cube loading programs. they are used when we
2700: l_filter:=bsc_aw_utility.get_property(p_data_source.property,'datasource filter').property_value;
2701: if l_filter is not null then
2702: bsc_aw_utility.add_g_commands(g_commands,l_filter||' --');
2703: end if;
2704: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2705: bsc_aw_utility.add_g_commands(g_commands,'then do');
2706: /*temp variables period.temp and year.temp hold the period and year values. they are present in all cube loading programs. they are used when we
2707: have BALANCE LAST VALUE type measure */
2708: create_temp_variables(p_dim_set,p_data_source);
2701: if l_filter is not null then
2702: bsc_aw_utility.add_g_commands(g_commands,l_filter||' --');
2703: end if;
2704: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2705: bsc_aw_utility.add_g_commands(g_commands,'then do');
2706: /*temp variables period.temp and year.temp hold the period and year values. they are present in all cube loading programs. they are used when we
2707: have BALANCE LAST VALUE type measure */
2708: create_temp_variables(p_dim_set,p_data_source);
2709: bsc_aw_utility.add_g_commands(g_commands,'allstat');
2705: bsc_aw_utility.add_g_commands(g_commands,'then do');
2706: /*temp variables period.temp and year.temp hold the period and year values. they are present in all cube loading programs. they are used when we
2707: have BALANCE LAST VALUE type measure */
2708: create_temp_variables(p_dim_set,p_data_source);
2709: bsc_aw_utility.add_g_commands(g_commands,'allstat');
2710: --if compressed composite, clear the aggregates. if < 10.2
2711: if p_dim_set.compressed='Y' and bsc_aw_utility.get_db_version<10.2 then
2712: bsc_aw_utility.init_is_new_value(1);
2713: for i in 1..p_data_source.measure.count loop
2707: have BALANCE LAST VALUE type measure */
2708: create_temp_variables(p_dim_set,p_data_source);
2709: bsc_aw_utility.add_g_commands(g_commands,'allstat');
2710: --if compressed composite, clear the aggregates. if < 10.2
2711: if p_dim_set.compressed='Y' and bsc_aw_utility.get_db_version<10.2 then
2712: bsc_aw_utility.init_is_new_value(1);
2713: for i in 1..p_data_source.measure.count loop
2714: if bsc_aw_utility.is_new_value(p_data_source.measure(i).cube,1) then
2715: bsc_aw_utility.add_g_commands(g_commands,'clear all aggregates from '||p_data_source.measure(i).cube);
2708: create_temp_variables(p_dim_set,p_data_source);
2709: bsc_aw_utility.add_g_commands(g_commands,'allstat');
2710: --if compressed composite, clear the aggregates. if < 10.2
2711: if p_dim_set.compressed='Y' and bsc_aw_utility.get_db_version<10.2 then
2712: bsc_aw_utility.init_is_new_value(1);
2713: for i in 1..p_data_source.measure.count loop
2714: if bsc_aw_utility.is_new_value(p_data_source.measure(i).cube,1) then
2715: bsc_aw_utility.add_g_commands(g_commands,'clear all aggregates from '||p_data_source.measure(i).cube);
2716: /*here we clear all aggregates from the cubes without looking at which measures are involved. if we load only 1
2710: --if compressed composite, clear the aggregates. if < 10.2
2711: if p_dim_set.compressed='Y' and bsc_aw_utility.get_db_version<10.2 then
2712: bsc_aw_utility.init_is_new_value(1);
2713: for i in 1..p_data_source.measure.count loop
2714: if bsc_aw_utility.is_new_value(p_data_source.measure(i).cube,1) then
2715: bsc_aw_utility.add_g_commands(g_commands,'clear all aggregates from '||p_data_source.measure(i).cube);
2716: /*here we clear all aggregates from the cubes without looking at which measures are involved. if we load only 1
2717: B, we aggregate all measures. this is ok, since cost is not in the arthmetic, but in composite build
2718: */
2711: if p_dim_set.compressed='Y' and bsc_aw_utility.get_db_version<10.2 then
2712: bsc_aw_utility.init_is_new_value(1);
2713: for i in 1..p_data_source.measure.count loop
2714: if bsc_aw_utility.is_new_value(p_data_source.measure(i).cube,1) then
2715: bsc_aw_utility.add_g_commands(g_commands,'clear all aggregates from '||p_data_source.measure(i).cube);
2716: /*here we clear all aggregates from the cubes without looking at which measures are involved. if we load only 1
2717: B, we aggregate all measures. this is ok, since cost is not in the arthmetic, but in composite build
2718: */
2719: end if;
2719: end if;
2720: end loop;
2721: end if;
2722: for i in 1..p_data_source.data_source_stmt.count loop
2723: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
2724: end loop;
2725: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2726: create_dim_match_header(p_data_source);
2727: /*
2721: end if;
2722: for i in 1..p_data_source.data_source_stmt.count loop
2723: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
2724: end loop;
2725: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2726: create_dim_match_header(p_data_source);
2727: /*
2728: if the dimset has partitions, data source stmt will have the partition key
2729: if the data source partition dim is not null, it means partitions are implemented in the dimset
2728: if the dimset has partitions, data source stmt will have the partition key
2729: if the data source partition dim is not null, it means partitions are implemented in the dimset
2730: */
2731: if p_dim_set.number_partitions>0 and p_data_source.data_source_PT.partition_template.template_dim is not null then
2732: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.data_source_PT.partition_template.template_dim||' --');
2733: end if;
2734: --now the cubes
2735: --every dim has CC dim. we have this so that we can do zero code on any of them.
2736: for i in 1..p_data_source.measure.count loop
2750: end loop;
2751: --time
2752: l_stmt:=l_stmt||p_data_source.calendar.aw_dim||' '||p_data_source.calendar.periodicity(1).aw_dim||' ';
2753: l_stmt:=l_stmt||') --';
2754: bsc_aw_utility.add_g_commands(g_commands,l_stmt);
2755: end loop;
2756: for i in 1..p_data_source.measure.count loop
2757: if p_data_source.measure(i).measure_type=g_balance_last_value_prop then
2758: /*if this is a BALANCE LAST VALUE column, also grab the loaded Y/N column */
2755: end loop;
2756: for i in 1..p_data_source.measure.count loop
2757: if p_data_source.measure(i).measure_type=g_balance_last_value_prop then
2758: /*if this is a BALANCE LAST VALUE column, also grab the loaded Y/N column */
2759: l_balance_loaded_column:=bsc_aw_utility.get_property(p_data_source.measure(i).property,g_balance_loaded_column_prop).property_value;
2760: if l_balance_loaded_column is not null then
2761: bsc_aw_utility.add_g_commands(g_commands,':'||l_balance_loaded_column||' --');
2762: end if;
2763: end if;
2757: if p_data_source.measure(i).measure_type=g_balance_last_value_prop then
2758: /*if this is a BALANCE LAST VALUE column, also grab the loaded Y/N column */
2759: l_balance_loaded_column:=bsc_aw_utility.get_property(p_data_source.measure(i).property,g_balance_loaded_column_prop).property_value;
2760: if l_balance_loaded_column is not null then
2761: bsc_aw_utility.add_g_commands(g_commands,':'||l_balance_loaded_column||' --');
2762: end if;
2763: end if;
2764: end loop;
2765: --markers...limit cubes
2764: end loop;
2765: --markers...limit cubes
2766: create_limit_cube_tail(p_data_source);
2767: /*have a then stmt. if there are balance measures, we need the then */
2768: bsc_aw_utility.add_g_commands(g_commands,'then --');
2769: bsc_aw_utility.add_g_commands(g_commands,'temp_number=NA --');
2770: --if there is balance, add the balance aggregation statements
2771: create_balance_aggregation(p_dim_set,p_data_source,p_data_source.measure);
2772: --
2765: --markers...limit cubes
2766: create_limit_cube_tail(p_data_source);
2767: /*have a then stmt. if there are balance measures, we need the then */
2768: bsc_aw_utility.add_g_commands(g_commands,'then --');
2769: bsc_aw_utility.add_g_commands(g_commands,'temp_number=NA --');
2770: --if there is balance, add the balance aggregation statements
2771: create_balance_aggregation(p_dim_set,p_data_source,p_data_source.measure);
2772: --
2773: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2769: bsc_aw_utility.add_g_commands(g_commands,'temp_number=NA --');
2770: --if there is balance, add the balance aggregation statements
2771: create_balance_aggregation(p_dim_set,p_data_source,p_data_source.measure);
2772: --
2773: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2774: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
2775: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
2776: bsc_aw_utility.add_g_commands(g_commands,'doend');
2777: Exception when others then
2770: --if there is balance, add the balance aggregation statements
2771: create_balance_aggregation(p_dim_set,p_data_source,p_data_source.measure);
2772: --
2773: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2774: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
2775: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
2776: bsc_aw_utility.add_g_commands(g_commands,'doend');
2777: Exception when others then
2778: log_n('Exception in create_kpi_program 3 ,dimset='||p_dim_set.dim_set_name||' '||sqlerrm);
2771: create_balance_aggregation(p_dim_set,p_data_source,p_data_source.measure);
2772: --
2773: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2774: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
2775: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
2776: bsc_aw_utility.add_g_commands(g_commands,'doend');
2777: Exception when others then
2778: log_n('Exception in create_kpi_program 3 ,dimset='||p_dim_set.dim_set_name||' '||sqlerrm);
2779: raise;
2772: --
2773: bsc_aw_utility.trim_g_commands(g_commands,3,null);
2774: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
2775: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
2776: bsc_aw_utility.add_g_commands(g_commands,'doend');
2777: Exception when others then
2778: log_n('Exception in create_kpi_program 3 ,dimset='||p_dim_set.dim_set_name||' '||sqlerrm);
2779: raise;
2780: End;
2794: Begin
2795: for i in 1..p_data_source.calendar.parent_child.count loop
2796: if p_data_source.calendar.parent_child(i).parent_dim_name is not null
2797: and p_data_source.calendar.parent_child(i).parent_dim_name<>p_data_source.calendar.periodicity(1).aw_dim then
2798: bsc_aw_utility.merge_value(l_upper_periodicity,p_data_source.calendar.parent_child(i).parent_dim_name);
2799: end if;
2800: if p_data_source.calendar.parent_child(i).child_dim_name is not null
2801: and p_data_source.calendar.parent_child(i).child_dim_name<>p_data_source.calendar.periodicity(1).aw_dim then
2802: bsc_aw_utility.merge_value(l_upper_periodicity,p_data_source.calendar.parent_child(i).child_dim_name);
2798: bsc_aw_utility.merge_value(l_upper_periodicity,p_data_source.calendar.parent_child(i).parent_dim_name);
2799: end if;
2800: if p_data_source.calendar.parent_child(i).child_dim_name is not null
2801: and p_data_source.calendar.parent_child(i).child_dim_name<>p_data_source.calendar.periodicity(1).aw_dim then
2802: bsc_aw_utility.merge_value(l_upper_periodicity,p_data_source.calendar.parent_child(i).child_dim_name);
2803: end if;
2804: end loop;
2805: if g_debug then
2806: log('In create_balance_aggregation dimset '||p_dim_set.dim_set_name||', upper periodicities:-');
2852: 'EQ '||p_data_source.calendar.end_period_relation_name||'('||p_data_source.calendar.aw_dim||' '||
2853: p_data_source.calendar.denorm_relation_name||'('||p_data_source.calendar.aw_dim||' '||p_data_source.calendar.periodicity(1).aw_dim||' '||
2854: p_data_source.calendar.end_period_level_name_dim||' \'''||l_upper_periodicity(j)||'\'') '||
2855: p_data_source.calendar.end_period_level_name_dim||' \'''||p_data_source.calendar.periodicity(1).aw_dim||'\'') --';
2856: bsc_aw_utility.add_g_commands(g_commands,l_stmt);
2857: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2858: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2859: /*set the limit cube of time also to true. this means we are simulating the time agg to come from the B table. this is important
2860: later for aggregation and target copy */
2853: p_data_source.calendar.denorm_relation_name||'('||p_data_source.calendar.aw_dim||' '||p_data_source.calendar.periodicity(1).aw_dim||' '||
2854: p_data_source.calendar.end_period_level_name_dim||' \'''||l_upper_periodicity(j)||'\'') '||
2855: p_data_source.calendar.end_period_level_name_dim||' \'''||p_data_source.calendar.periodicity(1).aw_dim||'\'') --';
2856: bsc_aw_utility.add_g_commands(g_commands,l_stmt);
2857: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2858: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2859: /*set the limit cube of time also to true. this means we are simulating the time agg to come from the B table. this is important
2860: later for aggregation and target copy */
2861: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2854: p_data_source.calendar.end_period_level_name_dim||' \'''||l_upper_periodicity(j)||'\'') '||
2855: p_data_source.calendar.end_period_level_name_dim||' \'''||p_data_source.calendar.periodicity(1).aw_dim||'\'') --';
2856: bsc_aw_utility.add_g_commands(g_commands,l_stmt);
2857: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2858: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2859: /*set the limit cube of time also to true. this means we are simulating the time agg to come from the B table. this is important
2860: later for aggregation and target copy */
2861: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2862: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2857: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2858: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2859: /*set the limit cube of time also to true. this means we are simulating the time agg to come from the B table. this is important
2860: later for aggregation and target copy */
2861: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2862: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2863: end loop;
2864: elsif p_measures(i).measure_type=g_balance_last_value_prop then --last value balance
2865: l_balance_loaded_column:=bsc_aw_utility.get_property(p_measures(i).property,g_balance_loaded_column_prop).property_value;
2858: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2859: /*set the limit cube of time also to true. this means we are simulating the time agg to come from the B table. this is important
2860: later for aggregation and target copy */
2861: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2862: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2863: end loop;
2864: elsif p_measures(i).measure_type=g_balance_last_value_prop then --last value balance
2865: l_balance_loaded_column:=bsc_aw_utility.get_property(p_measures(i).property,g_balance_loaded_column_prop).property_value;
2866: l_year_cube:=bsc_aw_utility.get_property(p_measures(i).property,'year cube').property_value;
2861: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2862: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2863: end loop;
2864: elsif p_measures(i).measure_type=g_balance_last_value_prop then --last value balance
2865: l_balance_loaded_column:=bsc_aw_utility.get_property(p_measures(i).property,g_balance_loaded_column_prop).property_value;
2866: l_year_cube:=bsc_aw_utility.get_property(p_measures(i).property,'year cube').property_value;
2867: l_period_cube:=bsc_aw_utility.get_property(p_measures(i).property,'period cube').property_value;
2868: /*l_year_cube and l_period_cube cannot be null */
2869: if l_balance_loaded_column is not null then
2862: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2863: end loop;
2864: elsif p_measures(i).measure_type=g_balance_last_value_prop then --last value balance
2865: l_balance_loaded_column:=bsc_aw_utility.get_property(p_measures(i).property,g_balance_loaded_column_prop).property_value;
2866: l_year_cube:=bsc_aw_utility.get_property(p_measures(i).property,'year cube').property_value;
2867: l_period_cube:=bsc_aw_utility.get_property(p_measures(i).property,'period cube').property_value;
2868: /*l_year_cube and l_period_cube cannot be null */
2869: if l_balance_loaded_column is not null then
2870: bsc_aw_utility.add_g_commands(g_commands,'if '||l_balance_loaded_column||' GT 0 --');
2863: end loop;
2864: elsif p_measures(i).measure_type=g_balance_last_value_prop then --last value balance
2865: l_balance_loaded_column:=bsc_aw_utility.get_property(p_measures(i).property,g_balance_loaded_column_prop).property_value;
2866: l_year_cube:=bsc_aw_utility.get_property(p_measures(i).property,'year cube').property_value;
2867: l_period_cube:=bsc_aw_utility.get_property(p_measures(i).property,'period cube').property_value;
2868: /*l_year_cube and l_period_cube cannot be null */
2869: if l_balance_loaded_column is not null then
2870: bsc_aw_utility.add_g_commands(g_commands,'if '||l_balance_loaded_column||' GT 0 --');
2871: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2866: l_year_cube:=bsc_aw_utility.get_property(p_measures(i).property,'year cube').property_value;
2867: l_period_cube:=bsc_aw_utility.get_property(p_measures(i).property,'period cube').property_value;
2868: /*l_year_cube and l_period_cube cannot be null */
2869: if l_balance_loaded_column is not null then
2870: bsc_aw_utility.add_g_commands(g_commands,'if '||l_balance_loaded_column||' GT 0 --');
2871: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2872: end if;
2873: for j in 1..l_upper_periodicity.count loop
2874: if l_cube_set.cube_set_type='datacube' then
2867: l_period_cube:=bsc_aw_utility.get_property(p_measures(i).property,'period cube').property_value;
2868: /*l_year_cube and l_period_cube cannot be null */
2869: if l_balance_loaded_column is not null then
2870: bsc_aw_utility.add_g_commands(g_commands,'if '||l_balance_loaded_column||' GT 0 --');
2871: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2872: end if;
2873: for j in 1..l_upper_periodicity.count loop
2874: if l_cube_set.cube_set_type='datacube' then
2875: l_year_cube_stmt:=p_measures(i).cube||'('||l_cube_set.measurename_dim||' \'''||l_year_cube||'\'' ';
2889: p_data_source.calendar.aw_dim||' '||p_data_source.calendar.periodicity(1).aw_dim||' '||
2890: p_data_source.calendar.end_period_level_name_dim||' \'''||l_upper_periodicity(j)||'\''))';
2891: l_year_cube_stmt:=l_year_cube_stmt||l_stmt;
2892: l_period_cube_stmt:=l_period_cube_stmt||l_stmt;
2893: bsc_aw_utility.add_g_commands(g_commands,'if '||l_year_cube_stmt||' EQ NA OR --');
2894: bsc_aw_utility.add_g_commands(g_commands,g_year_temp||' GT '||l_year_cube_stmt||' OR --');
2895: bsc_aw_utility.add_g_commands(g_commands,'('||g_year_temp||' EQ '||l_year_cube_stmt||' AND --');
2896: bsc_aw_utility.add_g_commands(g_commands,g_period_temp||' GT '||l_period_cube_stmt||') --');
2897: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2890: p_data_source.calendar.end_period_level_name_dim||' \'''||l_upper_periodicity(j)||'\''))';
2891: l_year_cube_stmt:=l_year_cube_stmt||l_stmt;
2892: l_period_cube_stmt:=l_period_cube_stmt||l_stmt;
2893: bsc_aw_utility.add_g_commands(g_commands,'if '||l_year_cube_stmt||' EQ NA OR --');
2894: bsc_aw_utility.add_g_commands(g_commands,g_year_temp||' GT '||l_year_cube_stmt||' OR --');
2895: bsc_aw_utility.add_g_commands(g_commands,'('||g_year_temp||' EQ '||l_year_cube_stmt||' AND --');
2896: bsc_aw_utility.add_g_commands(g_commands,g_period_temp||' GT '||l_period_cube_stmt||') --');
2897: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2898: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2891: l_year_cube_stmt:=l_year_cube_stmt||l_stmt;
2892: l_period_cube_stmt:=l_period_cube_stmt||l_stmt;
2893: bsc_aw_utility.add_g_commands(g_commands,'if '||l_year_cube_stmt||' EQ NA OR --');
2894: bsc_aw_utility.add_g_commands(g_commands,g_year_temp||' GT '||l_year_cube_stmt||' OR --');
2895: bsc_aw_utility.add_g_commands(g_commands,'('||g_year_temp||' EQ '||l_year_cube_stmt||' AND --');
2896: bsc_aw_utility.add_g_commands(g_commands,g_period_temp||' GT '||l_period_cube_stmt||') --');
2897: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2898: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2899: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2892: l_period_cube_stmt:=l_period_cube_stmt||l_stmt;
2893: bsc_aw_utility.add_g_commands(g_commands,'if '||l_year_cube_stmt||' EQ NA OR --');
2894: bsc_aw_utility.add_g_commands(g_commands,g_year_temp||' GT '||l_year_cube_stmt||' OR --');
2895: bsc_aw_utility.add_g_commands(g_commands,'('||g_year_temp||' EQ '||l_year_cube_stmt||' AND --');
2896: bsc_aw_utility.add_g_commands(g_commands,g_period_temp||' GT '||l_period_cube_stmt||') --');
2897: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2898: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2899: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2900: /*we must set the upper year and period values also */
2893: bsc_aw_utility.add_g_commands(g_commands,'if '||l_year_cube_stmt||' EQ NA OR --');
2894: bsc_aw_utility.add_g_commands(g_commands,g_year_temp||' GT '||l_year_cube_stmt||' OR --');
2895: bsc_aw_utility.add_g_commands(g_commands,'('||g_year_temp||' EQ '||l_year_cube_stmt||' AND --');
2896: bsc_aw_utility.add_g_commands(g_commands,g_period_temp||' GT '||l_period_cube_stmt||') --');
2897: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2898: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2899: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2900: /*we must set the upper year and period values also */
2901: bsc_aw_utility.add_g_commands(g_commands,l_year_cube_stmt||'='||g_year_temp||' --');
2894: bsc_aw_utility.add_g_commands(g_commands,g_year_temp||' GT '||l_year_cube_stmt||' OR --');
2895: bsc_aw_utility.add_g_commands(g_commands,'('||g_year_temp||' EQ '||l_year_cube_stmt||' AND --');
2896: bsc_aw_utility.add_g_commands(g_commands,g_period_temp||' GT '||l_period_cube_stmt||') --');
2897: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2898: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2899: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2900: /*we must set the upper year and period values also */
2901: bsc_aw_utility.add_g_commands(g_commands,l_year_cube_stmt||'='||g_year_temp||' --');
2902: bsc_aw_utility.add_g_commands(g_commands,l_period_cube_stmt||'='||g_period_temp||' --');
2895: bsc_aw_utility.add_g_commands(g_commands,'('||g_year_temp||' EQ '||l_year_cube_stmt||' AND --');
2896: bsc_aw_utility.add_g_commands(g_commands,g_period_temp||' GT '||l_period_cube_stmt||') --');
2897: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2898: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2899: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2900: /*we must set the upper year and period values also */
2901: bsc_aw_utility.add_g_commands(g_commands,l_year_cube_stmt||'='||g_year_temp||' --');
2902: bsc_aw_utility.add_g_commands(g_commands,l_period_cube_stmt||'='||g_period_temp||' --');
2903: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2897: bsc_aw_utility.add_g_commands(g_commands,'then do --');
2898: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2899: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2900: /*we must set the upper year and period values also */
2901: bsc_aw_utility.add_g_commands(g_commands,l_year_cube_stmt||'='||g_year_temp||' --');
2902: bsc_aw_utility.add_g_commands(g_commands,l_period_cube_stmt||'='||g_period_temp||' --');
2903: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2904: end loop;
2905: if l_balance_loaded_column is not null then
2898: bsc_aw_utility.add_g_commands(g_commands,l_cube_copy_stmt(j));
2899: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2900: /*we must set the upper year and period values also */
2901: bsc_aw_utility.add_g_commands(g_commands,l_year_cube_stmt||'='||g_year_temp||' --');
2902: bsc_aw_utility.add_g_commands(g_commands,l_period_cube_stmt||'='||g_period_temp||' --');
2903: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2904: end loop;
2905: if l_balance_loaded_column is not null then
2906: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2899: bsc_aw_utility.add_g_commands(g_commands,l_limit_cube_copy_stmt(j));
2900: /*we must set the upper year and period values also */
2901: bsc_aw_utility.add_g_commands(g_commands,l_year_cube_stmt||'='||g_year_temp||' --');
2902: bsc_aw_utility.add_g_commands(g_commands,l_period_cube_stmt||'='||g_period_temp||' --');
2903: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2904: end loop;
2905: if l_balance_loaded_column is not null then
2906: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2907: end if;
2902: bsc_aw_utility.add_g_commands(g_commands,l_period_cube_stmt||'='||g_period_temp||' --');
2903: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2904: end loop;
2905: if l_balance_loaded_column is not null then
2906: bsc_aw_utility.add_g_commands(g_commands,'doend --');
2907: end if;
2908: end if;
2909: end loop;
2910: Exception when others then
2920: we have this so we can avoid repeating the code
2921: */
2922: procedure create_dim_match_header(p_data_source data_source_r) is
2923: Begin
2924: bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
2925: --even after arun changed the filter to be like (select code from dim_view), import does not work. this means
2926: --if there is filter, we have to have fetch loop...
2927: if bsc_aw_utility.get_property(p_data_source.property,'dimension filter').property_value='Y'
2928: or bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2923: Begin
2924: bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
2925: --even after arun changed the filter to be like (select code from dim_view), import does not work. this means
2926: --if there is filter, we have to have fetch loop...
2927: if bsc_aw_utility.get_property(p_data_source.property,'dimension filter').property_value='Y'
2928: or bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2929: bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
2930: else
2931: bsc_aw_utility.add_g_commands(g_commands,'sql import c1 into --');
2924: bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
2925: --even after arun changed the filter to be like (select code from dim_view), import does not work. this means
2926: --if there is filter, we have to have fetch loop...
2927: if bsc_aw_utility.get_property(p_data_source.property,'dimension filter').property_value='Y'
2928: or bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2929: bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
2930: else
2931: bsc_aw_utility.add_g_commands(g_commands,'sql import c1 into --');
2932: end if;
2925: --even after arun changed the filter to be like (select code from dim_view), import does not work. this means
2926: --if there is filter, we have to have fetch loop...
2927: if bsc_aw_utility.get_property(p_data_source.property,'dimension filter').property_value='Y'
2928: or bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2929: bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
2930: else
2931: bsc_aw_utility.add_g_commands(g_commands,'sql import c1 into --');
2932: end if;
2933: for i in 1..p_data_source.dim.count loop
2927: if bsc_aw_utility.get_property(p_data_source.property,'dimension filter').property_value='Y'
2928: or bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2929: bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
2930: else
2931: bsc_aw_utility.add_g_commands(g_commands,'sql import c1 into --');
2932: end if;
2933: for i in 1..p_data_source.dim.count loop
2934: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.dim(i).levels(1).level_name||' --');
2935: end loop;
2930: else
2931: bsc_aw_utility.add_g_commands(g_commands,'sql import c1 into --');
2932: end if;
2933: for i in 1..p_data_source.dim.count loop
2934: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.dim(i).levels(1).level_name||' --');
2935: end loop;
2936: --std dim
2937: for i in 1..p_data_source.std_dim.count loop
2938: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.std_dim(i).levels(1).level_name||' --');
2934: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.dim(i).levels(1).level_name||' --');
2935: end loop;
2936: --std dim
2937: for i in 1..p_data_source.std_dim.count loop
2938: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.std_dim(i).levels(1).level_name||' --');
2939: end loop;
2940: --time dim. data source will only have 1 periodicity, the periodicity of the base table
2941: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.calendar.periodicity(1).aw_dim||' --');
2942: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2937: for i in 1..p_data_source.std_dim.count loop
2938: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.std_dim(i).levels(1).level_name||' --');
2939: end loop;
2940: --time dim. data source will only have 1 periodicity, the periodicity of the base table
2941: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.calendar.periodicity(1).aw_dim||' --');
2942: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2943: /*we need to place period and year values into the temp variables */
2944: bsc_aw_utility.add_g_commands(g_commands,':'||g_period_temp||' --');
2945: bsc_aw_utility.add_g_commands(g_commands,':'||g_year_temp||' --');
2938: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.std_dim(i).levels(1).level_name||' --');
2939: end loop;
2940: --time dim. data source will only have 1 periodicity, the periodicity of the base table
2941: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.calendar.periodicity(1).aw_dim||' --');
2942: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2943: /*we need to place period and year values into the temp variables */
2944: bsc_aw_utility.add_g_commands(g_commands,':'||g_period_temp||' --');
2945: bsc_aw_utility.add_g_commands(g_commands,':'||g_year_temp||' --');
2946: end if;
2940: --time dim. data source will only have 1 periodicity, the periodicity of the base table
2941: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.calendar.periodicity(1).aw_dim||' --');
2942: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2943: /*we need to place period and year values into the temp variables */
2944: bsc_aw_utility.add_g_commands(g_commands,':'||g_period_temp||' --');
2945: bsc_aw_utility.add_g_commands(g_commands,':'||g_year_temp||' --');
2946: end if;
2947: Exception when others then
2948: log_n('Exception in create_dim_match_header '||sqlerrm);
2941: bsc_aw_utility.add_g_commands(g_commands,':match '||p_data_source.calendar.periodicity(1).aw_dim||' --');
2942: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
2943: /*we need to place period and year values into the temp variables */
2944: bsc_aw_utility.add_g_commands(g_commands,':'||g_period_temp||' --');
2945: bsc_aw_utility.add_g_commands(g_commands,':'||g_year_temp||' --');
2946: end if;
2947: Exception when others then
2948: log_n('Exception in create_dim_match_header '||sqlerrm);
2949: raise;
2956: procedure create_limit_cube_tail(p_data_source data_source_r) is
2957: Begin
2958: for i in 1..p_data_source.dim.count loop
2959: if p_data_source.dim(i).concat='Y' then
2960: bsc_aw_utility.add_g_commands(g_commands,':'||p_data_source.dim(i).limit_cube||'('||
2961: p_data_source.dim(i).dim_name||' '||p_data_source.dim(i).levels(1).level_name||') --');
2962: else
2963: bsc_aw_utility.add_g_commands(g_commands,':'||p_data_source.dim(i).limit_cube||' --');
2964: end if;
2959: if p_data_source.dim(i).concat='Y' then
2960: bsc_aw_utility.add_g_commands(g_commands,':'||p_data_source.dim(i).limit_cube||'('||
2961: p_data_source.dim(i).dim_name||' '||p_data_source.dim(i).levels(1).level_name||') --');
2962: else
2963: bsc_aw_utility.add_g_commands(g_commands,':'||p_data_source.dim(i).limit_cube||' --');
2964: end if;
2965: end loop;
2966: --limit cubes for std dim...std dim are not concat
2967: for i in 1..p_data_source.std_dim.count loop
2964: end if;
2965: end loop;
2966: --limit cubes for std dim...std dim are not concat
2967: for i in 1..p_data_source.std_dim.count loop
2968: bsc_aw_utility.add_g_commands(g_commands,':'||p_data_source.std_dim(i).limit_cube||' --');
2969: end loop;
2970: --we dont need to have std dim since they do not have concat dim
2971: --time dim
2972: bsc_aw_utility.add_g_commands(g_commands,':'||p_data_source.calendar.limit_cube||'('||
2968: bsc_aw_utility.add_g_commands(g_commands,':'||p_data_source.std_dim(i).limit_cube||' --');
2969: end loop;
2970: --we dont need to have std dim since they do not have concat dim
2971: --time dim
2972: bsc_aw_utility.add_g_commands(g_commands,':'||p_data_source.calendar.limit_cube||'('||
2973: p_data_source.calendar.aw_dim||' '||p_data_source.calendar.periodicity(1).aw_dim||') --');
2974: Exception when others then
2975: log_n('Exception in create_limit_cube_tail '||sqlerrm);
2976: raise;
3059: set_program_property(p_dim_set.inc_load_program_parallel,p_dim_set.inc_data_source);
3060: l_pgm:=p_dim_set.inc_load_program_parallel.program_name;
3061: l_data_source:=p_dim_set.inc_data_source;
3062: end if;
3063: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_pgm||' program');
3064: for i in 1..l_data_source.count loop
3065: l_cube_considered.delete;
3066: for j in 1..l_data_source(i).measure.count loop
3067: if bsc_aw_utility.in_array(l_cube_considered,l_data_source(i).measure(j).cube)=false then
3063: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_pgm||' program');
3064: for i in 1..l_data_source.count loop
3065: l_cube_considered.delete;
3066: for j in 1..l_data_source(i).measure.count loop
3067: if bsc_aw_utility.in_array(l_cube_considered,l_data_source(i).measure(j).cube)=false then
3068: l_cube_considered(l_cube_considered.count+1):=l_data_source(i).measure(j).cube;
3069: end if;
3070: end loop;
3071: for j in 1..l_cube_considered.count loop
3079: end loop;
3080: --limit cubes
3081: create_kpi_program_limit_cube(p_kpi,p_dim_set,l_data_source(i));
3082: end loop;
3083: bsc_aw_utility.exec_program_commands(l_pgm,g_commands);
3084: Exception when others then
3085: log_n('Exception in create_kpi_program_cube 2 '||sqlerrm);
3086: raise;
3087: End;
3106: for i in 1..p_measures.count loop
3107: l_measures(i):=p_measures(i).measure;
3108: end loop;
3109: for i in 1..p_data_source.base_tables.count loop
3110: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
3111: end loop;
3112: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3113: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3114: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \'''||upper(p_cube_set.cube.cube_name)||',\'' ');
3108: end loop;
3109: for i in 1..p_data_source.base_tables.count loop
3110: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
3111: end loop;
3112: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3113: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3114: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \'''||upper(p_cube_set.cube.cube_name)||',\'' ');
3115: bsc_aw_utility.add_g_commands(g_commands,'then do');
3116: /*temp variables period.temp and year.temp hold the period and year values. they are present in all cube loading programs. they are used when we
3109: for i in 1..p_data_source.base_tables.count loop
3110: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
3111: end loop;
3112: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3113: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3114: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \'''||upper(p_cube_set.cube.cube_name)||',\'' ');
3115: bsc_aw_utility.add_g_commands(g_commands,'then do');
3116: /*temp variables period.temp and year.temp hold the period and year values. they are present in all cube loading programs. they are used when we
3117: have BALANCE LAST VALUE type measure */
3110: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
3111: end loop;
3112: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3113: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3114: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \'''||upper(p_cube_set.cube.cube_name)||',\'' ');
3115: bsc_aw_utility.add_g_commands(g_commands,'then do');
3116: /*temp variables period.temp and year.temp hold the period and year values. they are present in all cube loading programs. they are used when we
3117: have BALANCE LAST VALUE type measure */
3118: create_temp_variables(p_dim_set,p_data_source);
3111: end loop;
3112: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3113: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3114: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \'''||upper(p_cube_set.cube.cube_name)||',\'' ');
3115: bsc_aw_utility.add_g_commands(g_commands,'then do');
3116: /*temp variables period.temp and year.temp hold the period and year values. they are present in all cube loading programs. they are used when we
3117: have BALANCE LAST VALUE type measure */
3118: create_temp_variables(p_dim_set,p_data_source);
3119: --if compressed composite, clear the aggregates. if < 10.2
3116: /*temp variables period.temp and year.temp hold the period and year values. they are present in all cube loading programs. they are used when we
3117: have BALANCE LAST VALUE type measure */
3118: create_temp_variables(p_dim_set,p_data_source);
3119: --if compressed composite, clear the aggregates. if < 10.2
3120: if p_dim_set.compressed='Y' and bsc_aw_utility.get_db_version<10.2 then
3121: bsc_aw_utility.add_g_commands(g_commands,'clear all aggregates from '||p_cube_set.cube.cube_name);
3122: end if;
3123: --we do not have limit cubes. only the sql, dim and the measure we are looking at
3124: for i in 1..p_data_source.data_source_stmt.count loop
3117: have BALANCE LAST VALUE type measure */
3118: create_temp_variables(p_dim_set,p_data_source);
3119: --if compressed composite, clear the aggregates. if < 10.2
3120: if p_dim_set.compressed='Y' and bsc_aw_utility.get_db_version<10.2 then
3121: bsc_aw_utility.add_g_commands(g_commands,'clear all aggregates from '||p_cube_set.cube.cube_name);
3122: end if;
3123: --we do not have limit cubes. only the sql, dim and the measure we are looking at
3124: for i in 1..p_data_source.data_source_stmt.count loop
3125: if substr(p_data_source.data_source_stmt_type(i),1,3)='sql' or substr(p_data_source.data_source_stmt_type(i),1,10)='dimension=' or
3124: for i in 1..p_data_source.data_source_stmt.count loop
3125: if substr(p_data_source.data_source_stmt_type(i),1,3)='sql' or substr(p_data_source.data_source_stmt_type(i),1,10)='dimension=' or
3126: substr(p_data_source.data_source_stmt_type(i),1,10)='temp time=' then
3127: if p_data_source.data_source_stmt_type(i)='sql from' then
3128: if substr(bsc_aw_utility.get_g_commands(g_commands,null),-4)=', --' then
3129: bsc_aw_utility.trim_g_commands(g_commands,4,' --'); --remove the trailing ,
3130: end if;
3131: end if;
3132: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3125: if substr(p_data_source.data_source_stmt_type(i),1,3)='sql' or substr(p_data_source.data_source_stmt_type(i),1,10)='dimension=' or
3126: substr(p_data_source.data_source_stmt_type(i),1,10)='temp time=' then
3127: if p_data_source.data_source_stmt_type(i)='sql from' then
3128: if substr(bsc_aw_utility.get_g_commands(g_commands,null),-4)=', --' then
3129: bsc_aw_utility.trim_g_commands(g_commands,4,' --'); --remove the trailing ,
3130: end if;
3131: end if;
3132: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3133: elsif substr(p_data_source.data_source_stmt_type(i),1,8)='measure=' then
3128: if substr(bsc_aw_utility.get_g_commands(g_commands,null),-4)=', --' then
3129: bsc_aw_utility.trim_g_commands(g_commands,4,' --'); --remove the trailing ,
3130: end if;
3131: end if;
3132: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3133: elsif substr(p_data_source.data_source_stmt_type(i),1,8)='measure=' then
3134: if bsc_aw_utility.in_array(l_measures,substr(p_data_source.data_source_stmt_type(i),9,length(p_data_source.data_source_stmt_type(i)))) then
3135: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3136: end if;
3130: end if;
3131: end if;
3132: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3133: elsif substr(p_data_source.data_source_stmt_type(i),1,8)='measure=' then
3134: if bsc_aw_utility.in_array(l_measures,substr(p_data_source.data_source_stmt_type(i),9,length(p_data_source.data_source_stmt_type(i)))) then
3135: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3136: end if;
3137: elsif substr(p_data_source.data_source_stmt_type(i),1,27)='temp balance loaded column=' then /*temp balance loaded column=measure name */
3138: if bsc_aw_utility.in_array(l_measures,substr(p_data_source.data_source_stmt_type(i),28,length(p_data_source.data_source_stmt_type(i)))) then
3131: end if;
3132: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3133: elsif substr(p_data_source.data_source_stmt_type(i),1,8)='measure=' then
3134: if bsc_aw_utility.in_array(l_measures,substr(p_data_source.data_source_stmt_type(i),9,length(p_data_source.data_source_stmt_type(i)))) then
3135: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3136: end if;
3137: elsif substr(p_data_source.data_source_stmt_type(i),1,27)='temp balance loaded column=' then /*temp balance loaded column=measure name */
3138: if bsc_aw_utility.in_array(l_measures,substr(p_data_source.data_source_stmt_type(i),28,length(p_data_source.data_source_stmt_type(i)))) then
3139: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3134: if bsc_aw_utility.in_array(l_measures,substr(p_data_source.data_source_stmt_type(i),9,length(p_data_source.data_source_stmt_type(i)))) then
3135: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3136: end if;
3137: elsif substr(p_data_source.data_source_stmt_type(i),1,27)='temp balance loaded column=' then /*temp balance loaded column=measure name */
3138: if bsc_aw_utility.in_array(l_measures,substr(p_data_source.data_source_stmt_type(i),28,length(p_data_source.data_source_stmt_type(i)))) then
3139: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3140: end if;
3141: end if;
3142: end loop;
3135: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3136: end if;
3137: elsif substr(p_data_source.data_source_stmt_type(i),1,27)='temp balance loaded column=' then /*temp balance loaded column=measure name */
3138: if bsc_aw_utility.in_array(l_measures,substr(p_data_source.data_source_stmt_type(i),28,length(p_data_source.data_source_stmt_type(i)))) then
3139: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3140: end if;
3141: end if;
3142: end loop;
3143: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3139: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3140: end if;
3141: end if;
3142: end loop;
3143: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3144: create_dim_match_header(p_data_source);
3145: --now the cube for the measures
3146: for i in 1..p_measures.count loop
3147: l_stmt:=':'||p_cube_set.cube.cube_name||'('; --time will always be concat
3155: end loop;
3156: --time
3157: l_stmt:=l_stmt||p_data_source.calendar.aw_dim||' '||p_data_source.calendar.periodicity(1).aw_dim||' ';
3158: l_stmt:=l_stmt||') --';
3159: bsc_aw_utility.add_g_commands(g_commands,l_stmt);
3160: end loop;
3161: for i in 1..p_measures.count loop
3162: if p_measures(i).measure_type=g_balance_last_value_prop then
3163: /*if this is a BALANCE LAST VALUE column, also grab the loaded Y/N column */
3160: end loop;
3161: for i in 1..p_measures.count loop
3162: if p_measures(i).measure_type=g_balance_last_value_prop then
3163: /*if this is a BALANCE LAST VALUE column, also grab the loaded Y/N column */
3164: l_balance_loaded_column:=bsc_aw_utility.get_property(p_measures(i).property,g_balance_loaded_column_prop).property_value;
3165: if l_balance_loaded_column is not null then
3166: bsc_aw_utility.add_g_commands(g_commands,':'||l_balance_loaded_column||' --');
3167: end if;
3168: end if;
3162: if p_measures(i).measure_type=g_balance_last_value_prop then
3163: /*if this is a BALANCE LAST VALUE column, also grab the loaded Y/N column */
3164: l_balance_loaded_column:=bsc_aw_utility.get_property(p_measures(i).property,g_balance_loaded_column_prop).property_value;
3165: if l_balance_loaded_column is not null then
3166: bsc_aw_utility.add_g_commands(g_commands,':'||l_balance_loaded_column||' --');
3167: end if;
3168: end if;
3169: end loop;
3170: /*have a then stmt. if there are balance measures, we need the then */
3167: end if;
3168: end if;
3169: end loop;
3170: /*have a then stmt. if there are balance measures, we need the then */
3171: bsc_aw_utility.add_g_commands(g_commands,'then --');
3172: bsc_aw_utility.add_g_commands(g_commands,'temp_number=NA --');
3173: --if there is balance, add the balance aggregation statements
3174: create_balance_aggregation(p_dim_set,p_data_source,p_measures);
3175: --
3168: end if;
3169: end loop;
3170: /*have a then stmt. if there are balance measures, we need the then */
3171: bsc_aw_utility.add_g_commands(g_commands,'then --');
3172: bsc_aw_utility.add_g_commands(g_commands,'temp_number=NA --');
3173: --if there is balance, add the balance aggregation statements
3174: create_balance_aggregation(p_dim_set,p_data_source,p_measures);
3175: --
3176: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3172: bsc_aw_utility.add_g_commands(g_commands,'temp_number=NA --');
3173: --if there is balance, add the balance aggregation statements
3174: create_balance_aggregation(p_dim_set,p_data_source,p_measures);
3175: --
3176: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3177: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
3178: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
3179: bsc_aw_utility.add_g_commands(g_commands,'doend');
3180: Exception when others then
3173: --if there is balance, add the balance aggregation statements
3174: create_balance_aggregation(p_dim_set,p_data_source,p_measures);
3175: --
3176: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3177: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
3178: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
3179: bsc_aw_utility.add_g_commands(g_commands,'doend');
3180: Exception when others then
3181: log_n('Exception in create_kpi_program_cube 3 '||sqlerrm);
3174: create_balance_aggregation(p_dim_set,p_data_source,p_measures);
3175: --
3176: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3177: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
3178: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
3179: bsc_aw_utility.add_g_commands(g_commands,'doend');
3180: Exception when others then
3181: log_n('Exception in create_kpi_program_cube 3 '||sqlerrm);
3182: raise;
3175: --
3176: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3177: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
3178: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
3179: bsc_aw_utility.add_g_commands(g_commands,'doend');
3180: Exception when others then
3181: log_n('Exception in create_kpi_program_cube 3 '||sqlerrm);
3182: raise;
3183: End;
3201: l_kpi:=l_kpi||'.tgt';
3202: end if;
3203: p_dim_set.LB_resync_program:='LB.resync.'||p_dim_set.dim_set||'.'||l_kpi;
3204: g_commands.delete;
3205: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.LB_resync_program||' program');
3206: --all LB of the dimset
3207: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''PRE\'' ');
3208: bsc_aw_utility.add_g_commands(g_commands,'then do');
3209: for i in 1..p_dim_set.dim.count loop
3203: p_dim_set.LB_resync_program:='LB.resync.'||p_dim_set.dim_set||'.'||l_kpi;
3204: g_commands.delete;
3205: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.LB_resync_program||' program');
3206: --all LB of the dimset
3207: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''PRE\'' ');
3208: bsc_aw_utility.add_g_commands(g_commands,'then do');
3209: for i in 1..p_dim_set.dim.count loop
3210: l_name:=p_dim_set.dim(i).limit_cube||'.S';
3211: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3204: g_commands.delete;
3205: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.LB_resync_program||' program');
3206: --all LB of the dimset
3207: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''PRE\'' ');
3208: bsc_aw_utility.add_g_commands(g_commands,'then do');
3209: for i in 1..p_dim_set.dim.count loop
3210: l_name:=p_dim_set.dim(i).limit_cube||'.S';
3211: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3212: bsc_aw_utility.add_g_commands(g_commands,'then do');
3207: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''PRE\'' ');
3208: bsc_aw_utility.add_g_commands(g_commands,'then do');
3209: for i in 1..p_dim_set.dim.count loop
3210: l_name:=p_dim_set.dim(i).limit_cube||'.S';
3211: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3212: bsc_aw_utility.add_g_commands(g_commands,'then do');
3213: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.dim(i).dim_name||'> session');
3214: bsc_aw_utility.add_g_commands(g_commands,'doend');
3215: end loop;
3208: bsc_aw_utility.add_g_commands(g_commands,'then do');
3209: for i in 1..p_dim_set.dim.count loop
3210: l_name:=p_dim_set.dim(i).limit_cube||'.S';
3211: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3212: bsc_aw_utility.add_g_commands(g_commands,'then do');
3213: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.dim(i).dim_name||'> session');
3214: bsc_aw_utility.add_g_commands(g_commands,'doend');
3215: end loop;
3216: for i in 1..p_dim_set.std_dim.count loop
3209: for i in 1..p_dim_set.dim.count loop
3210: l_name:=p_dim_set.dim(i).limit_cube||'.S';
3211: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3212: bsc_aw_utility.add_g_commands(g_commands,'then do');
3213: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.dim(i).dim_name||'> session');
3214: bsc_aw_utility.add_g_commands(g_commands,'doend');
3215: end loop;
3216: for i in 1..p_dim_set.std_dim.count loop
3217: l_name:=p_dim_set.std_dim(i).limit_cube||'.S';
3210: l_name:=p_dim_set.dim(i).limit_cube||'.S';
3211: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3212: bsc_aw_utility.add_g_commands(g_commands,'then do');
3213: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.dim(i).dim_name||'> session');
3214: bsc_aw_utility.add_g_commands(g_commands,'doend');
3215: end loop;
3216: for i in 1..p_dim_set.std_dim.count loop
3217: l_name:=p_dim_set.std_dim(i).limit_cube||'.S';
3218: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3214: bsc_aw_utility.add_g_commands(g_commands,'doend');
3215: end loop;
3216: for i in 1..p_dim_set.std_dim.count loop
3217: l_name:=p_dim_set.std_dim(i).limit_cube||'.S';
3218: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3219: bsc_aw_utility.add_g_commands(g_commands,'then do');
3220: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.std_dim(i).dim_name||'> session');
3221: bsc_aw_utility.add_g_commands(g_commands,'doend');
3222: end loop;
3215: end loop;
3216: for i in 1..p_dim_set.std_dim.count loop
3217: l_name:=p_dim_set.std_dim(i).limit_cube||'.S';
3218: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3219: bsc_aw_utility.add_g_commands(g_commands,'then do');
3220: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.std_dim(i).dim_name||'> session');
3221: bsc_aw_utility.add_g_commands(g_commands,'doend');
3222: end loop;
3223: l_name:=p_dim_set.calendar.limit_cube||'.S';
3216: for i in 1..p_dim_set.std_dim.count loop
3217: l_name:=p_dim_set.std_dim(i).limit_cube||'.S';
3218: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3219: bsc_aw_utility.add_g_commands(g_commands,'then do');
3220: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.std_dim(i).dim_name||'> session');
3221: bsc_aw_utility.add_g_commands(g_commands,'doend');
3222: end loop;
3223: l_name:=p_dim_set.calendar.limit_cube||'.S';
3224: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3217: l_name:=p_dim_set.std_dim(i).limit_cube||'.S';
3218: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3219: bsc_aw_utility.add_g_commands(g_commands,'then do');
3220: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.std_dim(i).dim_name||'> session');
3221: bsc_aw_utility.add_g_commands(g_commands,'doend');
3222: end loop;
3223: l_name:=p_dim_set.calendar.limit_cube||'.S';
3224: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3225: bsc_aw_utility.add_g_commands(g_commands,'then do');
3220: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.std_dim(i).dim_name||'> session');
3221: bsc_aw_utility.add_g_commands(g_commands,'doend');
3222: end loop;
3223: l_name:=p_dim_set.calendar.limit_cube||'.S';
3224: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3225: bsc_aw_utility.add_g_commands(g_commands,'then do');
3226: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.calendar.aw_dim||'> session');
3227: bsc_aw_utility.add_g_commands(g_commands,'doend');
3228: --
3221: bsc_aw_utility.add_g_commands(g_commands,'doend');
3222: end loop;
3223: l_name:=p_dim_set.calendar.limit_cube||'.S';
3224: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3225: bsc_aw_utility.add_g_commands(g_commands,'then do');
3226: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.calendar.aw_dim||'> session');
3227: bsc_aw_utility.add_g_commands(g_commands,'doend');
3228: --
3229: for i in 1..p_dim_set.dim.count loop
3222: end loop;
3223: l_name:=p_dim_set.calendar.limit_cube||'.S';
3224: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3225: bsc_aw_utility.add_g_commands(g_commands,'then do');
3226: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.calendar.aw_dim||'> session');
3227: bsc_aw_utility.add_g_commands(g_commands,'doend');
3228: --
3229: for i in 1..p_dim_set.dim.count loop
3230: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3223: l_name:=p_dim_set.calendar.limit_cube||'.S';
3224: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
3225: bsc_aw_utility.add_g_commands(g_commands,'then do');
3226: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.calendar.aw_dim||'> session');
3227: bsc_aw_utility.add_g_commands(g_commands,'doend');
3228: --
3229: for i in 1..p_dim_set.dim.count loop
3230: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3231: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
3226: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_name||' boolean <'||p_dim_set.calendar.aw_dim||'> session');
3227: bsc_aw_utility.add_g_commands(g_commands,'doend');
3228: --
3229: for i in 1..p_dim_set.dim.count loop
3230: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3231: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
3232: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'.S=true');
3233: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3234: end loop;
3227: bsc_aw_utility.add_g_commands(g_commands,'doend');
3228: --
3229: for i in 1..p_dim_set.dim.count loop
3230: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3231: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
3232: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'.S=true');
3233: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3234: end loop;
3235: for i in 1..p_dim_set.std_dim.count loop
3228: --
3229: for i in 1..p_dim_set.dim.count loop
3230: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3231: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
3232: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'.S=true');
3233: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3234: end loop;
3235: for i in 1..p_dim_set.std_dim.count loop
3236: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3229: for i in 1..p_dim_set.dim.count loop
3230: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3231: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
3232: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'.S=true');
3233: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3234: end loop;
3235: for i in 1..p_dim_set.std_dim.count loop
3236: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3237: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube);
3232: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'.S=true');
3233: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3234: end loop;
3235: for i in 1..p_dim_set.std_dim.count loop
3236: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3237: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube);
3238: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'.S=true');
3239: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3240: end loop;
3233: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3234: end loop;
3235: for i in 1..p_dim_set.std_dim.count loop
3236: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3237: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube);
3238: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'.S=true');
3239: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3240: end loop;
3241: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3234: end loop;
3235: for i in 1..p_dim_set.std_dim.count loop
3236: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3237: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube);
3238: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'.S=true');
3239: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3240: end loop;
3241: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3242: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube);
3235: for i in 1..p_dim_set.std_dim.count loop
3236: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3237: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube);
3238: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'.S=true');
3239: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3240: end loop;
3241: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3242: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube);
3243: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'.S=true');
3237: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube);
3238: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'.S=true');
3239: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3240: end loop;
3241: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3242: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube);
3243: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'.S=true');
3244: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3245: --
3238: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'.S=true');
3239: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3240: end loop;
3241: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3242: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube);
3243: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'.S=true');
3244: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3245: --
3246: bsc_aw_utility.add_g_commands(g_commands,'doend');
3239: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3240: end loop;
3241: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3242: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube);
3243: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'.S=true');
3244: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3245: --
3246: bsc_aw_utility.add_g_commands(g_commands,'doend');
3247: --
3240: end loop;
3241: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3242: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube);
3243: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'.S=true');
3244: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3245: --
3246: bsc_aw_utility.add_g_commands(g_commands,'doend');
3247: --
3248: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''POST\'' ');
3242: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube);
3243: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'.S=true');
3244: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3245: --
3246: bsc_aw_utility.add_g_commands(g_commands,'doend');
3247: --
3248: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''POST\'' ');
3249: bsc_aw_utility.add_g_commands(g_commands,'then do');
3250: for i in 1..p_dim_set.dim.count loop
3244: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3245: --
3246: bsc_aw_utility.add_g_commands(g_commands,'doend');
3247: --
3248: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''POST\'' ');
3249: bsc_aw_utility.add_g_commands(g_commands,'then do');
3250: for i in 1..p_dim_set.dim.count loop
3251: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3252: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube||'.S');
3245: --
3246: bsc_aw_utility.add_g_commands(g_commands,'doend');
3247: --
3248: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''POST\'' ');
3249: bsc_aw_utility.add_g_commands(g_commands,'then do');
3250: for i in 1..p_dim_set.dim.count loop
3251: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3252: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube||'.S');
3253: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'=true');
3247: --
3248: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''POST\'' ');
3249: bsc_aw_utility.add_g_commands(g_commands,'then do');
3250: for i in 1..p_dim_set.dim.count loop
3251: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3252: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube||'.S');
3253: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'=true');
3254: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3255: end loop;
3248: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \''POST\'' ');
3249: bsc_aw_utility.add_g_commands(g_commands,'then do');
3250: for i in 1..p_dim_set.dim.count loop
3251: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3252: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube||'.S');
3253: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'=true');
3254: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3255: end loop;
3256: for i in 1..p_dim_set.std_dim.count loop
3249: bsc_aw_utility.add_g_commands(g_commands,'then do');
3250: for i in 1..p_dim_set.dim.count loop
3251: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3252: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube||'.S');
3253: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'=true');
3254: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3255: end loop;
3256: for i in 1..p_dim_set.std_dim.count loop
3257: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3250: for i in 1..p_dim_set.dim.count loop
3251: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.dim(i).dim_name);
3252: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube||'.S');
3253: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'=true');
3254: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3255: end loop;
3256: for i in 1..p_dim_set.std_dim.count loop
3257: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3258: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube||'.S');
3253: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).limit_cube||'=true');
3254: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3255: end loop;
3256: for i in 1..p_dim_set.std_dim.count loop
3257: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3258: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube||'.S');
3259: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'=true');
3260: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3261: end loop;
3254: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.dim(i).dim_name);
3255: end loop;
3256: for i in 1..p_dim_set.std_dim.count loop
3257: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3258: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube||'.S');
3259: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'=true');
3260: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3261: end loop;
3262: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3255: end loop;
3256: for i in 1..p_dim_set.std_dim.count loop
3257: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3258: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube||'.S');
3259: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'=true');
3260: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3261: end loop;
3262: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3263: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube||'.S');
3256: for i in 1..p_dim_set.std_dim.count loop
3257: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.std_dim(i).dim_name);
3258: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube||'.S');
3259: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'=true');
3260: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3261: end loop;
3262: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3263: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube||'.S');
3264: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true');
3258: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.std_dim(i).dim_name||' to '||p_dim_set.std_dim(i).limit_cube||'.S');
3259: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'=true');
3260: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3261: end loop;
3262: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3263: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube||'.S');
3264: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true');
3265: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3266: bsc_aw_utility.add_g_commands(g_commands,'doend');
3259: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(i).limit_cube||'=true');
3260: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3261: end loop;
3262: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3263: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube||'.S');
3264: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true');
3265: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3266: bsc_aw_utility.add_g_commands(g_commands,'doend');
3267: --
3260: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.std_dim(i).dim_name);
3261: end loop;
3262: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3263: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube||'.S');
3264: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true');
3265: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3266: bsc_aw_utility.add_g_commands(g_commands,'doend');
3267: --
3268: bsc_aw_utility.exec_program_commands(p_dim_set.LB_resync_program,g_commands);
3261: end loop;
3262: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3263: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube||'.S');
3264: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true');
3265: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3266: bsc_aw_utility.add_g_commands(g_commands,'doend');
3267: --
3268: bsc_aw_utility.exec_program_commands(p_dim_set.LB_resync_program,g_commands);
3269: Exception when others then
3262: bsc_aw_utility.add_g_commands(g_commands,'push '||p_dim_set.calendar.aw_dim);
3263: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.calendar.aw_dim||' to '||p_dim_set.calendar.limit_cube||'.S');
3264: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true');
3265: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3266: bsc_aw_utility.add_g_commands(g_commands,'doend');
3267: --
3268: bsc_aw_utility.exec_program_commands(p_dim_set.LB_resync_program,g_commands);
3269: Exception when others then
3270: log_n('Exception in create_kpi_program_LB_resync '||sqlerrm);
3264: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true');
3265: bsc_aw_utility.add_g_commands(g_commands,'pop '||p_dim_set.calendar.aw_dim);
3266: bsc_aw_utility.add_g_commands(g_commands,'doend');
3267: --
3268: bsc_aw_utility.exec_program_commands(p_dim_set.LB_resync_program,g_commands);
3269: Exception when others then
3270: log_n('Exception in create_kpi_program_LB_resync '||sqlerrm);
3271: raise;
3272: End;
3290: set_program_property(p_dim_set.inc_load_program_parallel,p_dim_set.inc_data_source);
3291: l_pgm:=p_dim_set.inc_load_program_parallel.program_name;
3292: l_data_source:=p_dim_set.inc_data_source;
3293: end if;
3294: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_pgm||' program');
3295: --
3296: for i in 1..l_data_source.count loop
3297: l_pt_name:=get_cube_axis(l_data_source(i).measure(1).cube,p_dim_set,'partition template');
3298: --note>>> all measures of a data source share a PT. a cube can have at most 1 PT
3300: for j in 1..l_partition_template.template_partitions.count loop
3301: create_kpi_program_partition(p_kpi,l_partition_template.template_partitions(j),p_dim_set,l_data_source(i),j);
3302: end loop;
3303: end loop;
3304: bsc_aw_utility.exec_program_commands(l_pgm,g_commands);
3305: Exception when others then
3306: log_n('Exception in create_kpi_program_partition '||sqlerrm);
3307: raise;
3308: End;
3363: end if;
3364: end loop;
3365: --
3366: for i in 1..l_data_source.measure.count loop
3367: if bsc_aw_utility.in_array(l_cubes,l_data_source.measure(i).cube)=false then
3368: l_cubes(l_cubes.count+1):=l_data_source.measure(i).cube;
3369: end if;
3370: end loop;
3371: l_property_value:='AND (';
3373: l_property_value:=l_property_value||'arg(2) EQ \'''||l_cubes(i)||',\'' OR ';
3374: end loop;
3375: l_property_value:=substr(l_property_value,1,length(l_property_value)-3);
3376: l_property_value:=l_property_value||') AND arg(3) EQ \''partition='||template_partition.partition_name||',\'' ';
3377: bsc_aw_utility.merge_property(l_data_source.property,'datasource filter',null,l_property_value);
3378: bsc_aw_utility.merge_property(l_data_source.property,'partition',null,template_partition.partition_name);
3379: --
3380: create_kpi_program(p_kpi,p_dim_set,l_data_source);
3381: Exception when others then
3374: end loop;
3375: l_property_value:=substr(l_property_value,1,length(l_property_value)-3);
3376: l_property_value:=l_property_value||') AND arg(3) EQ \''partition='||template_partition.partition_name||',\'' ';
3377: bsc_aw_utility.merge_property(l_data_source.property,'datasource filter',null,l_property_value);
3378: bsc_aw_utility.merge_property(l_data_source.property,'partition',null,template_partition.partition_name);
3379: --
3380: create_kpi_program(p_kpi,p_dim_set,l_data_source);
3381: Exception when others then
3382: log_n('Exception in create_kpi_program_partition '||sqlerrm);
3392: p_data_source data_source_r) is
3393: l_ordered_b_tables dbms_sql.varchar2_table;
3394: Begin
3395: for i in 1..p_data_source.base_tables.count loop
3396: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
3397: end loop;
3398: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3399: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3400: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \''LIMIT CUBE\'' ');
3394: Begin
3395: for i in 1..p_data_source.base_tables.count loop
3396: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
3397: end loop;
3398: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3399: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3400: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \''LIMIT CUBE\'' ');
3401: bsc_aw_utility.add_g_commands(g_commands,'then do');
3402: create_temp_variables(p_dim_set,p_data_source);
3395: for i in 1..p_data_source.base_tables.count loop
3396: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
3397: end loop;
3398: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3399: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3400: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \''LIMIT CUBE\'' ');
3401: bsc_aw_utility.add_g_commands(g_commands,'then do');
3402: create_temp_variables(p_dim_set,p_data_source);
3403: for i in 1..p_data_source.data_source_stmt.count loop
3396: bsc_aw_utility.merge_value(l_ordered_b_tables,p_data_source.base_tables(i).base_table_name);
3397: end loop;
3398: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3399: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3400: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \''LIMIT CUBE\'' ');
3401: bsc_aw_utility.add_g_commands(g_commands,'then do');
3402: create_temp_variables(p_dim_set,p_data_source);
3403: for i in 1..p_data_source.data_source_stmt.count loop
3404: if p_data_source.data_source_stmt_type(i)='sql from' then
3397: end loop;
3398: l_ordered_b_tables:=bsc_aw_utility.order_array(l_ordered_b_tables);
3399: bsc_aw_utility.add_g_commands(g_commands,'if arg(1) EQ \'''||bsc_aw_utility.make_string_from_list(l_ordered_b_tables)||'\'' --');
3400: bsc_aw_utility.add_g_commands(g_commands,' AND arg(2) EQ \''LIMIT CUBE\'' ');
3401: bsc_aw_utility.add_g_commands(g_commands,'then do');
3402: create_temp_variables(p_dim_set,p_data_source);
3403: for i in 1..p_data_source.data_source_stmt.count loop
3404: if p_data_source.data_source_stmt_type(i)='sql from' then
3405: if substr(bsc_aw_utility.get_g_commands(g_commands,null),-4)=', --' then
3401: bsc_aw_utility.add_g_commands(g_commands,'then do');
3402: create_temp_variables(p_dim_set,p_data_source);
3403: for i in 1..p_data_source.data_source_stmt.count loop
3404: if p_data_source.data_source_stmt_type(i)='sql from' then
3405: if substr(bsc_aw_utility.get_g_commands(g_commands,null),-4)=', --' then
3406: bsc_aw_utility.trim_g_commands(g_commands,4,' --'); --remove the trailing ,
3407: end if;
3408: end if;
3409: if substr(p_data_source.data_source_stmt_type(i),1,3)='sql' or
3402: create_temp_variables(p_dim_set,p_data_source);
3403: for i in 1..p_data_source.data_source_stmt.count loop
3404: if p_data_source.data_source_stmt_type(i)='sql from' then
3405: if substr(bsc_aw_utility.get_g_commands(g_commands,null),-4)=', --' then
3406: bsc_aw_utility.trim_g_commands(g_commands,4,' --'); --remove the trailing ,
3407: end if;
3408: end if;
3409: if substr(p_data_source.data_source_stmt_type(i),1,3)='sql' or
3410: substr(p_data_source.data_source_stmt_type(i),1,10)='dimension=' or
3409: if substr(p_data_source.data_source_stmt_type(i),1,3)='sql' or
3410: substr(p_data_source.data_source_stmt_type(i),1,10)='dimension=' or
3411: substr(p_data_source.data_source_stmt_type(i),1,10)='temp time=' or
3412: substr(p_data_source.data_source_stmt_type(i),1,11)='limit cube=' then
3413: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3414: end if;
3415: end loop;
3416: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3417: create_dim_match_header(p_data_source);
3412: substr(p_data_source.data_source_stmt_type(i),1,11)='limit cube=' then
3413: bsc_aw_utility.add_g_commands(g_commands,p_data_source.data_source_stmt(i)||' --');
3414: end if;
3415: end loop;
3416: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3417: create_dim_match_header(p_data_source);
3418: create_limit_cube_tail(p_data_source);
3419: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3420: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
3415: end loop;
3416: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3417: create_dim_match_header(p_data_source);
3418: create_limit_cube_tail(p_data_source);
3419: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3420: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
3421: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
3422: bsc_aw_utility.add_g_commands(g_commands,'doend');
3423: Exception when others then
3416: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3417: create_dim_match_header(p_data_source);
3418: create_limit_cube_tail(p_data_source);
3419: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3420: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
3421: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
3422: bsc_aw_utility.add_g_commands(g_commands,'doend');
3423: Exception when others then
3424: log_n('Exception in create_kpi_program_limit_cube '||sqlerrm);
3417: create_dim_match_header(p_data_source);
3418: create_limit_cube_tail(p_data_source);
3419: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3420: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
3421: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
3422: bsc_aw_utility.add_g_commands(g_commands,'doend');
3423: Exception when others then
3424: log_n('Exception in create_kpi_program_limit_cube '||sqlerrm);
3425: raise;
3418: create_limit_cube_tail(p_data_source);
3419: bsc_aw_utility.trim_g_commands(g_commands,3,null);
3420: bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
3421: bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
3422: bsc_aw_utility.add_g_commands(g_commands,'doend');
3423: Exception when others then
3424: log_n('Exception in create_kpi_program_limit_cube '||sqlerrm);
3425: raise;
3426: End;
3517: for i in 1..p_s_view.dim.count loop
3518: if p_s_view.dim(i).levels(1).level_type='normal' then
3519: if p_s_view.dim(i).base_value_cube is not null then
3520: --use get_hash to make sure we are within 30 chars
3521: l_name:='D_'||bsc_aw_utility.get_hash_value(p_s_view.dim(i).dim_name||'.'||p_s_view.dim(i).levels(1).level_name||'.'||i,
3522: 100,1073741824);
3523: l_inner_stmt:=l_inner_stmt||' dimension '||l_name||' from '||p_s_view.dim(i).dim_name||bsc_aw_utility.g_newline;
3524: l_dimensions(l_dimensions.count+1):=l_name;
3525: l_inner_stmt:=l_inner_stmt||' with attribute '||p_s_view.dim(i).levels(1).fk||' from '||p_s_view.dim(i).base_value_cube||
3519: if p_s_view.dim(i).base_value_cube is not null then
3520: --use get_hash to make sure we are within 30 chars
3521: l_name:='D_'||bsc_aw_utility.get_hash_value(p_s_view.dim(i).dim_name||'.'||p_s_view.dim(i).levels(1).level_name||'.'||i,
3522: 100,1073741824);
3523: l_inner_stmt:=l_inner_stmt||' dimension '||l_name||' from '||p_s_view.dim(i).dim_name||bsc_aw_utility.g_newline;
3524: l_dimensions(l_dimensions.count+1):=l_name;
3525: l_inner_stmt:=l_inner_stmt||' with attribute '||p_s_view.dim(i).levels(1).fk||' from '||p_s_view.dim(i).base_value_cube||
3526: bsc_aw_utility.g_newline;
3527: l_inner_measures(l_inner_measures.count+1):=p_s_view.dim(i).levels(1).fk;
3522: 100,1073741824);
3523: l_inner_stmt:=l_inner_stmt||' dimension '||l_name||' from '||p_s_view.dim(i).dim_name||bsc_aw_utility.g_newline;
3524: l_dimensions(l_dimensions.count+1):=l_name;
3525: l_inner_stmt:=l_inner_stmt||' with attribute '||p_s_view.dim(i).levels(1).fk||' from '||p_s_view.dim(i).base_value_cube||
3526: bsc_aw_utility.g_newline;
3527: l_inner_measures(l_inner_measures.count+1):=p_s_view.dim(i).levels(1).fk;
3528: l_inner_select_columns(l_inner_select_columns.count+1):=p_s_view.dim(i).levels(1).fk;
3529: l_inner_select_column_types(l_inner_select_column_types.count+1):='fk';
3530: else
3595: l_outer_measure_agg_types(l_outer_measure_agg_types.count+1):='non std';
3596: end if;
3597: end loop;
3598: l_inner_stmt:=l_inner_stmt||'''))';
3599: if bsc_aw_utility.get_db_version>10.1 and l_dimensions.count>0 and l_inner_measures.count>0 then
3600: l_inner_stmt:=l_inner_stmt||bsc_aw_utility.g_newline;
3601: l_inner_stmt:=l_inner_stmt||'model'||bsc_aw_utility.g_newline;
3602: l_inner_stmt:=l_inner_stmt||'UNIQUE SINGLE REFERENCE'||bsc_aw_utility.g_newline;
3603: l_inner_stmt:=l_inner_stmt||'dimension by('||bsc_aw_utility.g_newline;
3596: end if;
3597: end loop;
3598: l_inner_stmt:=l_inner_stmt||'''))';
3599: if bsc_aw_utility.get_db_version>10.1 and l_dimensions.count>0 and l_inner_measures.count>0 then
3600: l_inner_stmt:=l_inner_stmt||bsc_aw_utility.g_newline;
3601: l_inner_stmt:=l_inner_stmt||'model'||bsc_aw_utility.g_newline;
3602: l_inner_stmt:=l_inner_stmt||'UNIQUE SINGLE REFERENCE'||bsc_aw_utility.g_newline;
3603: l_inner_stmt:=l_inner_stmt||'dimension by('||bsc_aw_utility.g_newline;
3604: for i in 1..l_dimensions.count loop
3597: end loop;
3598: l_inner_stmt:=l_inner_stmt||'''))';
3599: if bsc_aw_utility.get_db_version>10.1 and l_dimensions.count>0 and l_inner_measures.count>0 then
3600: l_inner_stmt:=l_inner_stmt||bsc_aw_utility.g_newline;
3601: l_inner_stmt:=l_inner_stmt||'model'||bsc_aw_utility.g_newline;
3602: l_inner_stmt:=l_inner_stmt||'UNIQUE SINGLE REFERENCE'||bsc_aw_utility.g_newline;
3603: l_inner_stmt:=l_inner_stmt||'dimension by('||bsc_aw_utility.g_newline;
3604: for i in 1..l_dimensions.count loop
3605: if i=1 then
3598: l_inner_stmt:=l_inner_stmt||'''))';
3599: if bsc_aw_utility.get_db_version>10.1 and l_dimensions.count>0 and l_inner_measures.count>0 then
3600: l_inner_stmt:=l_inner_stmt||bsc_aw_utility.g_newline;
3601: l_inner_stmt:=l_inner_stmt||'model'||bsc_aw_utility.g_newline;
3602: l_inner_stmt:=l_inner_stmt||'UNIQUE SINGLE REFERENCE'||bsc_aw_utility.g_newline;
3603: l_inner_stmt:=l_inner_stmt||'dimension by('||bsc_aw_utility.g_newline;
3604: for i in 1..l_dimensions.count loop
3605: if i=1 then
3606: l_inner_stmt:=l_inner_stmt||l_dimensions(i)||bsc_aw_utility.g_newline;
3599: if bsc_aw_utility.get_db_version>10.1 and l_dimensions.count>0 and l_inner_measures.count>0 then
3600: l_inner_stmt:=l_inner_stmt||bsc_aw_utility.g_newline;
3601: l_inner_stmt:=l_inner_stmt||'model'||bsc_aw_utility.g_newline;
3602: l_inner_stmt:=l_inner_stmt||'UNIQUE SINGLE REFERENCE'||bsc_aw_utility.g_newline;
3603: l_inner_stmt:=l_inner_stmt||'dimension by('||bsc_aw_utility.g_newline;
3604: for i in 1..l_dimensions.count loop
3605: if i=1 then
3606: l_inner_stmt:=l_inner_stmt||l_dimensions(i)||bsc_aw_utility.g_newline;
3607: else
3602: l_inner_stmt:=l_inner_stmt||'UNIQUE SINGLE REFERENCE'||bsc_aw_utility.g_newline;
3603: l_inner_stmt:=l_inner_stmt||'dimension by('||bsc_aw_utility.g_newline;
3604: for i in 1..l_dimensions.count loop
3605: if i=1 then
3606: l_inner_stmt:=l_inner_stmt||l_dimensions(i)||bsc_aw_utility.g_newline;
3607: else
3608: l_inner_stmt:=l_inner_stmt||','||l_dimensions(i)||bsc_aw_utility.g_newline;
3609: end if;
3610: end loop;
3604: for i in 1..l_dimensions.count loop
3605: if i=1 then
3606: l_inner_stmt:=l_inner_stmt||l_dimensions(i)||bsc_aw_utility.g_newline;
3607: else
3608: l_inner_stmt:=l_inner_stmt||','||l_dimensions(i)||bsc_aw_utility.g_newline;
3609: end if;
3610: end loop;
3611: l_inner_stmt:=l_inner_stmt||')'||bsc_aw_utility.g_newline;
3612: l_inner_stmt:=l_inner_stmt||'measures('||bsc_aw_utility.g_newline;
3607: else
3608: l_inner_stmt:=l_inner_stmt||','||l_dimensions(i)||bsc_aw_utility.g_newline;
3609: end if;
3610: end loop;
3611: l_inner_stmt:=l_inner_stmt||')'||bsc_aw_utility.g_newline;
3612: l_inner_stmt:=l_inner_stmt||'measures('||bsc_aw_utility.g_newline;
3613: for i in 1..l_inner_measures.count loop
3614: if i=1 then
3615: l_inner_stmt:=l_inner_stmt||l_inner_measures(i)||bsc_aw_utility.g_newline;
3608: l_inner_stmt:=l_inner_stmt||','||l_dimensions(i)||bsc_aw_utility.g_newline;
3609: end if;
3610: end loop;
3611: l_inner_stmt:=l_inner_stmt||')'||bsc_aw_utility.g_newline;
3612: l_inner_stmt:=l_inner_stmt||'measures('||bsc_aw_utility.g_newline;
3613: for i in 1..l_inner_measures.count loop
3614: if i=1 then
3615: l_inner_stmt:=l_inner_stmt||l_inner_measures(i)||bsc_aw_utility.g_newline;
3616: else
3611: l_inner_stmt:=l_inner_stmt||')'||bsc_aw_utility.g_newline;
3612: l_inner_stmt:=l_inner_stmt||'measures('||bsc_aw_utility.g_newline;
3613: for i in 1..l_inner_measures.count loop
3614: if i=1 then
3615: l_inner_stmt:=l_inner_stmt||l_inner_measures(i)||bsc_aw_utility.g_newline;
3616: else
3617: l_inner_stmt:=l_inner_stmt||','||l_inner_measures(i)||bsc_aw_utility.g_newline;
3618: end if;
3619: end loop;
3613: for i in 1..l_inner_measures.count loop
3614: if i=1 then
3615: l_inner_stmt:=l_inner_stmt||l_inner_measures(i)||bsc_aw_utility.g_newline;
3616: else
3617: l_inner_stmt:=l_inner_stmt||','||l_inner_measures(i)||bsc_aw_utility.g_newline;
3618: end if;
3619: end loop;
3620: l_inner_stmt:=l_inner_stmt||')'||bsc_aw_utility.g_newline;
3621: l_inner_stmt:=l_inner_stmt||'rules update sequential order()';
3616: else
3617: l_inner_stmt:=l_inner_stmt||','||l_inner_measures(i)||bsc_aw_utility.g_newline;
3618: end if;
3619: end loop;
3620: l_inner_stmt:=l_inner_stmt||')'||bsc_aw_utility.g_newline;
3621: l_inner_stmt:=l_inner_stmt||'rules update sequential order()';
3622: end if;
3623: /*if there is sql agg for non std measures (when there are non std agg and partitions), we need to create the stmt as (AVGBUG is the formula)
3624: create or replace view testv as
3648: end if;
3649: end loop;
3650: /*now the second part */
3651: g_stmt:=substr(g_stmt,1,length(g_stmt)-1);
3652: g_stmt:=g_stmt||bsc_aw_utility.g_newline;
3653: g_stmt:=g_stmt||'from (select ';
3654: for i in 1..l_inner_select_columns.count loop
3655: if l_inner_select_column_types(i)='fk' then
3656: g_stmt:=g_stmt||l_inner_select_columns(i)||',';
3661: g_stmt:=g_stmt||l_outer_measure_aggregations(i)||'('||l_outer_measures(i)||') '||l_outer_measures(i)||',';
3662: end if;
3663: end loop;
3664: g_stmt:=substr(g_stmt,1,length(g_stmt)-1);
3665: g_stmt:=g_stmt||bsc_aw_utility.g_newline;
3666: g_stmt:=g_stmt||'from (select ';
3667: end if;
3668: for i in 1..l_inner_select_columns.count loop
3669: g_stmt:=g_stmt||l_inner_select_columns(i)||',';
3670: end loop;
3671: g_stmt:=substr(g_stmt,1,length(g_stmt)-1);
3672: g_stmt:=g_stmt||l_inner_stmt;
3673: if sql_aggregation then
3674: g_stmt:=g_stmt||')'||bsc_aw_utility.g_newline;
3675: g_stmt:=g_stmt||'group by ';
3676: for i in 1..l_inner_select_columns.count loop
3677: if l_inner_select_column_types(i)='fk' then
3678: g_stmt:=g_stmt||l_inner_select_columns(i)||',';
3680: end loop;
3681: g_stmt:=substr(g_stmt,1,length(g_stmt)-1);
3682: g_stmt:=g_stmt||')';
3683: end if;
3684: bsc_aw_utility.execute_ddl(g_stmt);
3685: Exception when others then
3686: log_n('Exception in create_kpi_view '||sqlerrm);
3687: raise;
3688: End;
3722: Begin
3723: p_s_view.type_name:='type_'||p_kpi||'_'||p_dim_set.dim_set||'_'||p_s_view.id;
3724: p_s_view.type_table_name:=p_s_view.type_name||'_tab';
3725: g_stmt:='drop type '||p_s_view.type_table_name;
3726: bsc_aw_utility.execute_ddl_ne(g_stmt);
3727: g_stmt:='drop type '||p_s_view.type_name;
3728: bsc_aw_utility.execute_ddl_ne(g_stmt);
3729: g_stmt:='create or replace type '||p_s_view.type_name||' as object (';
3730: for i in 1..p_s_view.dim.count loop
3724: p_s_view.type_table_name:=p_s_view.type_name||'_tab';
3725: g_stmt:='drop type '||p_s_view.type_table_name;
3726: bsc_aw_utility.execute_ddl_ne(g_stmt);
3727: g_stmt:='drop type '||p_s_view.type_name;
3728: bsc_aw_utility.execute_ddl_ne(g_stmt);
3729: g_stmt:='create or replace type '||p_s_view.type_name||' as object (';
3730: for i in 1..p_s_view.dim.count loop
3731: if p_s_view.dim(i).levels(1).level_type='normal' then
3732: if p_s_view.dim(i).base_value_cube is not null then
3729: g_stmt:='create or replace type '||p_s_view.type_name||' as object (';
3730: for i in 1..p_s_view.dim.count loop
3731: if p_s_view.dim(i).levels(1).level_type='normal' then
3732: if p_s_view.dim(i).base_value_cube is not null then
3733: l_name:='D_'||bsc_aw_utility.get_hash_value(p_s_view.dim(i).dim_name||'.'||p_s_view.dim(i).levels(1).level_name||'.'||i,
3734: 100,1073741824);
3735: g_stmt:=g_stmt||l_name||' varchar2(800),';
3736: end if;
3737: g_stmt:=g_stmt||p_s_view.dim(i).levels(1).fk||' '||p_s_view.dim(i).levels(1).data_type||',';
3746: for j in 1..p_dim_set.measure.count loop
3747: g_stmt:=g_stmt||p_dim_set.measure(j).measure||' '||p_dim_set.measure(j).data_type||',';
3748: end loop;
3749: g_stmt:=substr(g_stmt,1,length(g_stmt)-1)||')';
3750: bsc_aw_utility.execute_ddl(g_stmt);
3751: --create the table of type
3752: g_stmt:='create or replace type '||p_s_view.type_table_name||' as table of '||p_s_view.type_name;
3753: bsc_aw_utility.execute_ddl(g_stmt);
3754: Exception when others then
3749: g_stmt:=substr(g_stmt,1,length(g_stmt)-1)||')';
3750: bsc_aw_utility.execute_ddl(g_stmt);
3751: --create the table of type
3752: g_stmt:='create or replace type '||p_s_view.type_table_name||' as table of '||p_s_view.type_name;
3753: bsc_aw_utility.execute_ddl(g_stmt);
3754: Exception when others then
3755: log_n('Exception in create_db_type '||sqlerrm);
3756: raise;
3757: End;
3984: log_n('Exception in dmp_data_source '||sqlerrm);
3985: raise;
3986: End;
3987:
3988: procedure dmp_table_partition(p_partition bsc_aw_utility.object_partition_r) is
3989: Begin
3990: log('Table partitions');
3991: log('Main partition:-');
3992: dmp_partition_set(p_partition.main_partition);
3996: log_n('Exception in dmp_table_partition '||sqlerrm);
3997: raise;
3998: End;
3999:
4000: procedure dmp_partition_set(p_partition_set bsc_aw_utility.partition_set_r) is
4001: Begin
4002: log('Partition set '||p_partition_set.set_name);
4003: if p_partition_set.set_name is not null then
4004: log('Partition type '||p_partition_set.partition_type);
4247: end if;
4248: --search for each measure and substitute it
4249: for i in 1..p_all_measures.count loop
4250: l_start_array.delete;
4251: if bsc_aw_utility.is_string_present(l_agg_formula,p_all_measures(i).measure,l_start_array) then
4252: if l_start_array.count>0 then
4253: l_cube_set:=get_cube_set_r(p_all_measures(i).cube,p_dim_set);
4254: if l_cube_set.cube_set_type='datacube' then
4255: l_cube:=p_all_measures(i).cube||'('||l_cube_set.measurename_dim||' '''||p_all_measures(i).measure||''')';
4256: else
4257: l_cube:=p_all_measures(i).cube;
4258: end if;
4259: --we leave the agg_formula.cubes as the original name of the cube
4260: bsc_aw_utility.merge_value(p_measure.agg_formula.cubes,p_all_measures(i).cube);
4261: bsc_aw_utility.merge_value(p_measure.agg_formula.measures,p_all_measures(i).measure);
4262: bsc_aw_utility.replace_string(l_agg_formula,p_all_measures(i).measure,l_cube,l_start_array);
4263: end if;
4264: end if;
4257: l_cube:=p_all_measures(i).cube;
4258: end if;
4259: --we leave the agg_formula.cubes as the original name of the cube
4260: bsc_aw_utility.merge_value(p_measure.agg_formula.cubes,p_all_measures(i).cube);
4261: bsc_aw_utility.merge_value(p_measure.agg_formula.measures,p_all_measures(i).measure);
4262: bsc_aw_utility.replace_string(l_agg_formula,p_all_measures(i).measure,l_cube,l_start_array);
4263: end if;
4264: end if;
4265: end loop;
4258: end if;
4259: --we leave the agg_formula.cubes as the original name of the cube
4260: bsc_aw_utility.merge_value(p_measure.agg_formula.cubes,p_all_measures(i).cube);
4261: bsc_aw_utility.merge_value(p_measure.agg_formula.measures,p_all_measures(i).measure);
4262: bsc_aw_utility.replace_string(l_agg_formula,p_all_measures(i).measure,l_cube,l_start_array);
4263: end if;
4264: end if;
4265: end loop;
4266: if g_debug then
4385: /*by default, we will not aggregate on time, unless explicitly specified */
4386: procedure set_calendar_agg_level(p_dim_set in out nocopy dim_set_r) is
4387: aggregated boolean;
4388: Begin
4389: if bsc_aw_utility.get_parameter_value('AGGREGATE TIME')='Y' then
4390: aggregated:=true;
4391: else
4392: aggregated:=false;
4393: end if;
4409:
4410: /*if there are targets, we need to make sure that the level at the target is aggregated. if target is at month, we cannot just agg to
4411: day level */
4412: procedure set_calendar_agg_level(p_dim_set in out nocopy dim_set_r,p_target_dim_set dim_set_r) is
4413: l_pc bsc_aw_utility.parent_child_tb;
4414: l_child_levels dbms_sql.varchar2_table;
4415: Begin
4416: set_calendar_agg_level(p_dim_set);
4417: if p_dim_set.targets_higher_levels='Y' then
4434: exit;
4435: end if;
4436: end loop;
4437: l_child_levels.delete;
4438: bsc_aw_utility.get_all_children(l_pc,p_target_dim_set.calendar.periodicity(i).aw_dim,l_child_levels);
4439: if l_child_levels.count>0 then
4440: for j in 1..p_dim_set.calendar.periodicity.count loop
4441: if bsc_aw_utility.in_array(l_child_levels,p_dim_set.calendar.periodicity(j).aw_dim) then
4442: p_dim_set.calendar.periodicity(j).aggregated:='Y';
4437: l_child_levels.delete;
4438: bsc_aw_utility.get_all_children(l_pc,p_target_dim_set.calendar.periodicity(i).aw_dim,l_child_levels);
4439: if l_child_levels.count>0 then
4440: for j in 1..p_dim_set.calendar.periodicity.count loop
4441: if bsc_aw_utility.in_array(l_child_levels,p_dim_set.calendar.periodicity(j).aw_dim) then
4442: p_dim_set.calendar.periodicity(j).aggregated:='Y';
4443: if g_debug then
4444: log('Due to targets('||p_dim_set.dim_set_name||'), setting '||p_dim_set.calendar.periodicity(j).aw_dim||' aggregated flag to ''Y''');
4445: end if;
4457: procedure init_agg_level(p_dim_set in out nocopy dim_set_r) is
4458: --
4459: l_adv_sum_profile number;
4460: Begin
4461: l_adv_sum_profile:=bsc_aw_utility.get_adv_sum_profile;
4462: --init the agg level to adv sum profile first
4463: for i in 1..p_dim_set.dim.count loop
4464: if p_dim_set.dim(i).recursive='Y' then
4465: p_dim_set.dim(i).agg_level:=1000000; --hard coded. agg all levels for rec dim
4536: l_levels level_tb;
4537: l_levels_for_new_dim level_tb;
4538: l_dim_to_split dim_tb;
4539: l_dim_copy dim_tb;
4540: l_flag bsc_aw_utility.boolean_table;
4541: l_skip_flag varchar2(40);
4542: Begin
4543: --for each dim, get a list of distinct mo
4544: for i in 1..p_dim_set.dim.count loop
4566: --now split the dim
4567: l_levels_for_new_dim.delete;
4568: for i in 1..l_dim_to_split.count loop
4569: for j in 1..l_dim_to_split(i).levels.count loop
4570: l_skip_flag:=bsc_aw_utility.get_parameter_value(l_dim_to_split(i).levels(j).property,'skip level',',');
4571: --ignore the levels to skip
4572: if l_skip_flag='N' then
4573: l_levels_for_new_dim(l_levels_for_new_dim.count+1):=l_dim_to_split(i).levels(j);
4574: end if;
4605: --
4606: l_mo_dim_group varchar2(200);
4607: Begin
4608: for i in 1..p_dim.levels.count loop
4609: l_mo_dim_group:=bsc_aw_utility.get_parameter_value(p_dim.levels(i).property,'mo dim group',',');
4610: if bsc_aw_utility.in_array(p_distict_mo_dim_groups,l_mo_dim_group)=false then
4611: p_distict_mo_dim_groups(p_distict_mo_dim_groups.count+1):=l_mo_dim_group;
4612: end if;
4613: end loop;
4606: l_mo_dim_group varchar2(200);
4607: Begin
4608: for i in 1..p_dim.levels.count loop
4609: l_mo_dim_group:=bsc_aw_utility.get_parameter_value(p_dim.levels(i).property,'mo dim group',',');
4610: if bsc_aw_utility.in_array(p_distict_mo_dim_groups,l_mo_dim_group)=false then
4611: p_distict_mo_dim_groups(p_distict_mo_dim_groups.count+1):=l_mo_dim_group;
4612: end if;
4613: end loop;
4614: Exception when others then
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;
4651: exit;
4652: end if;
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;
4651: exit;
4652: end if;
4653: 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
4674: end if;
4675: end loop;
4676: --calendar
4677: for i in 1..l_oo_relation.count loop
4678: if l_oo_relation(i).object=l_dimset and l_oo_relation(i).relation_type='dim set calendar' then
4679: l_calendar_id:=to_number(bsc_aw_utility.get_parameter_value(l_oo_relation(i).property1,'calendar',','));
4680: exit;
4681: end if;
4682: end loop;
4683: --get the measures and cubes
4683: --get the measures and cubes
4684: for i in 1..l_oo_relation.count loop
4685: if l_oo_relation(i).object=l_dimset and l_oo_relation(i).relation_type='dim set measure' then
4686: l_measures(l_measures.count+1):=l_oo_relation(i).relation_object;
4687: l_cubes(l_cubes.count+1):=bsc_aw_utility.get_parameter_value(l_oo_relation(i).property1,'cube',',');
4688: l_cube_formula(l_cube_formula.count+1):=bsc_aw_utility.get_parameter_value(l_oo_relation(i).property1,'aw formula',',');
4689: for j in 1..l_oo_relation.count loop
4690: if l_oo_relation(j).object=l_cubes(l_cubes.count) and l_oo_relation(j).relation_type='cube axis' then
4691: l_axis_type:=bsc_aw_utility.get_parameter_value(l_oo_relation(j).property1,'axis type',',');
4684: for i in 1..l_oo_relation.count loop
4685: if l_oo_relation(i).object=l_dimset and l_oo_relation(i).relation_type='dim set measure' then
4686: l_measures(l_measures.count+1):=l_oo_relation(i).relation_object;
4687: l_cubes(l_cubes.count+1):=bsc_aw_utility.get_parameter_value(l_oo_relation(i).property1,'cube',',');
4688: l_cube_formula(l_cube_formula.count+1):=bsc_aw_utility.get_parameter_value(l_oo_relation(i).property1,'aw formula',',');
4689: for j in 1..l_oo_relation.count loop
4690: if l_oo_relation(j).object=l_cubes(l_cubes.count) and l_oo_relation(j).relation_type='cube axis' then
4691: l_axis_type:=bsc_aw_utility.get_parameter_value(l_oo_relation(j).property1,'axis type',',');
4692: if l_axis_type='partition template' or l_axis_type='composite' then
4687: l_cubes(l_cubes.count+1):=bsc_aw_utility.get_parameter_value(l_oo_relation(i).property1,'cube',',');
4688: l_cube_formula(l_cube_formula.count+1):=bsc_aw_utility.get_parameter_value(l_oo_relation(i).property1,'aw formula',',');
4689: for j in 1..l_oo_relation.count loop
4690: if l_oo_relation(j).object=l_cubes(l_cubes.count) and l_oo_relation(j).relation_type='cube axis' then
4691: l_axis_type:=bsc_aw_utility.get_parameter_value(l_oo_relation(j).property1,'axis type',',');
4692: if l_axis_type='partition template' or l_axis_type='composite' then
4693: l_cube_composites(l_cubes.count):=l_oo_relation(j).relation_object;
4694: exit;
4695: end if;
4697: end loop;
4698: end if;
4699: end loop;
4700: --create the table first
4701: bsc_aw_utility.execute_ddl_ne('drop table '||p_table_name);
4702: l_stmt:='create table '||p_table_name||'(
4703: ';
4704: for i in 1..l_dim_levels.count loop
4705: l_stmt:=l_stmt||l_dim_levels(i)||' varchar2(300),
4709: l_stmt:=l_stmt||l_measures(i)||' number,
4710: ';
4711: end loop;
4712: l_stmt:=l_stmt||'period number,year number,periodicity_id number)';
4713: bsc_aw_utility.execute_ddl(l_stmt);
4714: --create the program
4715: g_commands.delete;
4716: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_name||' program');
4717: bsc_aw_utility.add_g_commands(g_commands,'sql prepare c1 from --');
4712: l_stmt:=l_stmt||'period number,year number,periodicity_id number)';
4713: bsc_aw_utility.execute_ddl(l_stmt);
4714: --create the program
4715: g_commands.delete;
4716: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_name||' program');
4717: bsc_aw_utility.add_g_commands(g_commands,'sql prepare c1 from --');
4718: bsc_aw_utility.add_g_commands(g_commands,'insert into '||p_table_name||' values ( --');
4719: for i in 1..l_dim_levels.count loop
4720: if l_dim_level_dims(i) <> l_dim_levels(i) then
4713: bsc_aw_utility.execute_ddl(l_stmt);
4714: --create the program
4715: g_commands.delete;
4716: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_name||' program');
4717: bsc_aw_utility.add_g_commands(g_commands,'sql prepare c1 from --');
4718: bsc_aw_utility.add_g_commands(g_commands,'insert into '||p_table_name||' values ( --');
4719: for i in 1..l_dim_levels.count loop
4720: if l_dim_level_dims(i) <> l_dim_levels(i) then
4721: bsc_aw_utility.add_g_commands(g_commands,':key('||l_dim_level_dims(i)||' '||l_dim_levels(i)||'), --');
4714: --create the program
4715: g_commands.delete;
4716: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_name||' program');
4717: bsc_aw_utility.add_g_commands(g_commands,'sql prepare c1 from --');
4718: bsc_aw_utility.add_g_commands(g_commands,'insert into '||p_table_name||' values ( --');
4719: for i in 1..l_dim_levels.count loop
4720: if l_dim_level_dims(i) <> l_dim_levels(i) then
4721: bsc_aw_utility.add_g_commands(g_commands,':key('||l_dim_level_dims(i)||' '||l_dim_levels(i)||'), --');
4722: else
4717: bsc_aw_utility.add_g_commands(g_commands,'sql prepare c1 from --');
4718: bsc_aw_utility.add_g_commands(g_commands,'insert into '||p_table_name||' values ( --');
4719: for i in 1..l_dim_levels.count loop
4720: if l_dim_level_dims(i) <> l_dim_levels(i) then
4721: bsc_aw_utility.add_g_commands(g_commands,':key('||l_dim_level_dims(i)||' '||l_dim_levels(i)||'), --');
4722: else
4723: bsc_aw_utility.add_g_commands(g_commands,':'||l_dim_levels(i)||', --');
4724: end if;
4725: end loop;
4719: for i in 1..l_dim_levels.count loop
4720: if l_dim_level_dims(i) <> l_dim_levels(i) then
4721: bsc_aw_utility.add_g_commands(g_commands,':key('||l_dim_level_dims(i)||' '||l_dim_levels(i)||'), --');
4722: else
4723: bsc_aw_utility.add_g_commands(g_commands,':'||l_dim_levels(i)||', --');
4724: end if;
4725: end loop;
4726: for i in 1..l_measures.count loop
4727: if l_cube_formula(i) is not null then
4724: end if;
4725: end loop;
4726: for i in 1..l_measures.count loop
4727: if l_cube_formula(i) is not null then
4728: bsc_aw_utility.add_g_commands(g_commands,':'||l_cube_formula(i)||', --');
4729: else
4730: bsc_aw_utility.add_g_commands(g_commands,':'||l_cubes(i)||', --');
4731: end if;
4732: end loop;
4726: for i in 1..l_measures.count loop
4727: if l_cube_formula(i) is not null then
4728: bsc_aw_utility.add_g_commands(g_commands,':'||l_cube_formula(i)||', --');
4729: else
4730: bsc_aw_utility.add_g_commands(g_commands,':'||l_cubes(i)||', --');
4731: end if;
4732: end loop;
4733: bsc_aw_utility.add_g_commands(g_commands,':period_cal_'||l_calendar_id||',:year_cal_'||l_calendar_id||',:periodicity_cal_'||l_calendar_id||')');
4734: bsc_aw_utility.add_g_commands(g_commands,'for '||l_cube_composites(1));
4729: else
4730: bsc_aw_utility.add_g_commands(g_commands,':'||l_cubes(i)||', --');
4731: end if;
4732: end loop;
4733: bsc_aw_utility.add_g_commands(g_commands,':period_cal_'||l_calendar_id||',:year_cal_'||l_calendar_id||',:periodicity_cal_'||l_calendar_id||')');
4734: bsc_aw_utility.add_g_commands(g_commands,'for '||l_cube_composites(1));
4735: bsc_aw_utility.add_g_commands(g_commands,'do');
4736: bsc_aw_utility.add_g_commands(g_commands,'sql execute c1');
4737: bsc_aw_utility.add_g_commands(g_commands,'if sqlcode ne 0');
4730: bsc_aw_utility.add_g_commands(g_commands,':'||l_cubes(i)||', --');
4731: end if;
4732: end loop;
4733: bsc_aw_utility.add_g_commands(g_commands,':period_cal_'||l_calendar_id||',:year_cal_'||l_calendar_id||',:periodicity_cal_'||l_calendar_id||')');
4734: bsc_aw_utility.add_g_commands(g_commands,'for '||l_cube_composites(1));
4735: bsc_aw_utility.add_g_commands(g_commands,'do');
4736: bsc_aw_utility.add_g_commands(g_commands,'sql execute c1');
4737: bsc_aw_utility.add_g_commands(g_commands,'if sqlcode ne 0');
4738: bsc_aw_utility.add_g_commands(g_commands,'then break');
4731: end if;
4732: end loop;
4733: bsc_aw_utility.add_g_commands(g_commands,':period_cal_'||l_calendar_id||',:year_cal_'||l_calendar_id||',:periodicity_cal_'||l_calendar_id||')');
4734: bsc_aw_utility.add_g_commands(g_commands,'for '||l_cube_composites(1));
4735: bsc_aw_utility.add_g_commands(g_commands,'do');
4736: bsc_aw_utility.add_g_commands(g_commands,'sql execute c1');
4737: bsc_aw_utility.add_g_commands(g_commands,'if sqlcode ne 0');
4738: bsc_aw_utility.add_g_commands(g_commands,'then break');
4739: bsc_aw_utility.add_g_commands(g_commands,'doend');
4732: end loop;
4733: bsc_aw_utility.add_g_commands(g_commands,':period_cal_'||l_calendar_id||',:year_cal_'||l_calendar_id||',:periodicity_cal_'||l_calendar_id||')');
4734: bsc_aw_utility.add_g_commands(g_commands,'for '||l_cube_composites(1));
4735: bsc_aw_utility.add_g_commands(g_commands,'do');
4736: bsc_aw_utility.add_g_commands(g_commands,'sql execute c1');
4737: bsc_aw_utility.add_g_commands(g_commands,'if sqlcode ne 0');
4738: bsc_aw_utility.add_g_commands(g_commands,'then break');
4739: bsc_aw_utility.add_g_commands(g_commands,'doend');
4740: bsc_aw_utility.exec_program_commands(p_name,g_commands);
4733: bsc_aw_utility.add_g_commands(g_commands,':period_cal_'||l_calendar_id||',:year_cal_'||l_calendar_id||',:periodicity_cal_'||l_calendar_id||')');
4734: bsc_aw_utility.add_g_commands(g_commands,'for '||l_cube_composites(1));
4735: bsc_aw_utility.add_g_commands(g_commands,'do');
4736: bsc_aw_utility.add_g_commands(g_commands,'sql execute c1');
4737: bsc_aw_utility.add_g_commands(g_commands,'if sqlcode ne 0');
4738: bsc_aw_utility.add_g_commands(g_commands,'then break');
4739: bsc_aw_utility.add_g_commands(g_commands,'doend');
4740: bsc_aw_utility.exec_program_commands(p_name,g_commands);
4741: --limit the dimensions
4734: bsc_aw_utility.add_g_commands(g_commands,'for '||l_cube_composites(1));
4735: bsc_aw_utility.add_g_commands(g_commands,'do');
4736: bsc_aw_utility.add_g_commands(g_commands,'sql execute c1');
4737: bsc_aw_utility.add_g_commands(g_commands,'if sqlcode ne 0');
4738: bsc_aw_utility.add_g_commands(g_commands,'then break');
4739: bsc_aw_utility.add_g_commands(g_commands,'doend');
4740: bsc_aw_utility.exec_program_commands(p_name,g_commands);
4741: --limit the dimensions
4742: for i in 1..l_dim_levels.count loop
4735: bsc_aw_utility.add_g_commands(g_commands,'do');
4736: bsc_aw_utility.add_g_commands(g_commands,'sql execute c1');
4737: bsc_aw_utility.add_g_commands(g_commands,'if sqlcode ne 0');
4738: bsc_aw_utility.add_g_commands(g_commands,'then break');
4739: bsc_aw_utility.add_g_commands(g_commands,'doend');
4740: bsc_aw_utility.exec_program_commands(p_name,g_commands);
4741: --limit the dimensions
4742: for i in 1..l_dim_levels.count loop
4743: if l_dim_level_dims(i) <> l_dim_levels(i) then
4736: bsc_aw_utility.add_g_commands(g_commands,'sql execute c1');
4737: bsc_aw_utility.add_g_commands(g_commands,'if sqlcode ne 0');
4738: bsc_aw_utility.add_g_commands(g_commands,'then break');
4739: bsc_aw_utility.add_g_commands(g_commands,'doend');
4740: bsc_aw_utility.exec_program_commands(p_name,g_commands);
4741: --limit the dimensions
4742: for i in 1..l_dim_levels.count loop
4743: if l_dim_level_dims(i) <> l_dim_levels(i) then
4744: bsc_aw_dbms_aw.execute('limit '||l_dim_level_dims(i)||' to '||l_dim_levels(i));
4836: for i in 1..p_dim_set.measure.count loop
4837: l_cube(i):=get_cube_set_r(p_dim_set.measure(i).cube,p_dim_set).cube;
4838: for j in 1..l_cube(i).cube_axis.count loop
4839: if l_cube(i).cube_axis(j).axis_type='partition template' or l_cube(i).cube_axis(j).axis_type='composite' then
4840: if bsc_aw_utility.in_array(l_composite_name,l_cube(i).cube_axis(j).axis_name)=false then
4841: l_composite_name(l_composite_name.count+1):=l_cube(i).cube_axis(j).axis_name;
4842: end if;
4843: end if;
4844: end loop;
4845: end loop;
4846: --there must be 1 entry only for l_composite_name, either a PT or composite
4847: l_flag:=false;
4848: g_commands.delete;
4849: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.aggregate_marker_program||' program');
4850: l_flag:=true;
4851: for i in 1..p_dim_set.dim.count loop
4852: if p_dim_set.dim(i).aggregate_marker is not null then --there is a aggregate marker
4853: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4849: bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_dim_set.aggregate_marker_program||' program');
4850: l_flag:=true;
4851: for i in 1..p_dim_set.dim.count loop
4852: if p_dim_set.dim(i).aggregate_marker is not null then --there is a aggregate marker
4853: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4854: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4855: bsc_aw_utility.add_g_commands(g_commands,'then do');
4856: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
4857: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' add ancestors using '||p_dim_set.dim(i).relation_name);
4850: l_flag:=true;
4851: for i in 1..p_dim_set.dim.count loop
4852: if p_dim_set.dim(i).aggregate_marker is not null then --there is a aggregate marker
4853: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4854: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4855: bsc_aw_utility.add_g_commands(g_commands,'then do');
4856: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
4857: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' add ancestors using '||p_dim_set.dim(i).relation_name);
4858: for j in 1..p_dim_set.dim.count loop
4851: for i in 1..p_dim_set.dim.count loop
4852: if p_dim_set.dim(i).aggregate_marker is not null then --there is a aggregate marker
4853: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4854: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4855: bsc_aw_utility.add_g_commands(g_commands,'then do');
4856: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
4857: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' add ancestors using '||p_dim_set.dim(i).relation_name);
4858: for j in 1..p_dim_set.dim.count loop
4859: if i<>j then
4852: if p_dim_set.dim(i).aggregate_marker is not null then --there is a aggregate marker
4853: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4854: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4855: bsc_aw_utility.add_g_commands(g_commands,'then do');
4856: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
4857: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' add ancestors using '||p_dim_set.dim(i).relation_name);
4858: for j in 1..p_dim_set.dim.count loop
4859: if i<>j then
4860: for k in 1..l_composite_name.count loop
4853: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4854: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4855: bsc_aw_utility.add_g_commands(g_commands,'then do');
4856: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).limit_cube);
4857: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' add ancestors using '||p_dim_set.dim(i).relation_name);
4858: for j in 1..p_dim_set.dim.count loop
4859: if i<>j then
4860: for k in 1..l_composite_name.count loop
4861: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(j).limit_cube||'=true across '||l_composite_name(k));
4857: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' add ancestors using '||p_dim_set.dim(i).relation_name);
4858: for j in 1..p_dim_set.dim.count loop
4859: if i<>j then
4860: for k in 1..l_composite_name.count loop
4861: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(j).limit_cube||'=true across '||l_composite_name(k));
4862: end loop;
4863: end if;
4864: end loop;
4865: for j in 1..p_dim_set.std_dim.count loop
4863: end if;
4864: end loop;
4865: for j in 1..p_dim_set.std_dim.count loop
4866: for k in 1..l_composite_name.count loop
4867: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(j).limit_cube||'=true across '||l_composite_name(k));
4868: end loop;
4869: end loop;
4870: for j in 1..l_composite_name.count loop
4871: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true across '||l_composite_name(j));
4867: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.std_dim(j).limit_cube||'=true across '||l_composite_name(k));
4868: end loop;
4869: end loop;
4870: for j in 1..l_composite_name.count loop
4871: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true across '||l_composite_name(j));
4872: end loop;
4873: bsc_aw_utility.add_g_commands(g_commands,'doend');
4874: end if;
4875: end loop;
4869: end loop;
4870: for j in 1..l_composite_name.count loop
4871: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.calendar.limit_cube||'=true across '||l_composite_name(j));
4872: end loop;
4873: bsc_aw_utility.add_g_commands(g_commands,'doend');
4874: end if;
4875: end loop;
4876: --for the reset cubes. loop across each dim and if each dim has a situation where a parent now no longer has any child nodes,
4877: --set the value of the cube to na
4876: --for the reset cubes. loop across each dim and if each dim has a situation where a parent now no longer has any child nodes,
4877: --set the value of the cube to na
4878: for i in 1..p_dim_set.dim.count loop
4879: if p_dim_set.dim(i).reset_cube is not null and p_dim_set.dim(i).aggregate_marker is not null then --there is a aggregate marker and reset cube
4880: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4881: bsc_aw_utility.add_g_commands(g_commands,'then do');
4882: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4883: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).reset_cube);
4884: bsc_aw_utility.add_g_commands(g_commands,'if statlen('||p_dim_set.dim(i).dim_name||') GT 0');
4877: --set the value of the cube to na
4878: for i in 1..p_dim_set.dim.count loop
4879: if p_dim_set.dim(i).reset_cube is not null and p_dim_set.dim(i).aggregate_marker is not null then --there is a aggregate marker and reset cube
4880: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4881: bsc_aw_utility.add_g_commands(g_commands,'then do');
4882: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4883: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).reset_cube);
4884: bsc_aw_utility.add_g_commands(g_commands,'if statlen('||p_dim_set.dim(i).dim_name||') GT 0');
4885: bsc_aw_utility.add_g_commands(g_commands,'then do');
4878: for i in 1..p_dim_set.dim.count loop
4879: if p_dim_set.dim(i).reset_cube is not null and p_dim_set.dim(i).aggregate_marker is not null then --there is a aggregate marker and reset cube
4880: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4881: bsc_aw_utility.add_g_commands(g_commands,'then do');
4882: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4883: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).reset_cube);
4884: bsc_aw_utility.add_g_commands(g_commands,'if statlen('||p_dim_set.dim(i).dim_name||') GT 0');
4885: bsc_aw_utility.add_g_commands(g_commands,'then do');
4886: bsc_aw_utility.init_is_new_value(1);
4879: if p_dim_set.dim(i).reset_cube is not null and p_dim_set.dim(i).aggregate_marker is not null then --there is a aggregate marker and reset cube
4880: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4881: bsc_aw_utility.add_g_commands(g_commands,'then do');
4882: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4883: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).reset_cube);
4884: bsc_aw_utility.add_g_commands(g_commands,'if statlen('||p_dim_set.dim(i).dim_name||') GT 0');
4885: bsc_aw_utility.add_g_commands(g_commands,'then do');
4886: bsc_aw_utility.init_is_new_value(1);
4887: for j in 1..p_dim_set.measure.count loop
4880: bsc_aw_utility.add_g_commands(g_commands,'if '||p_dim_set.dim(i).aggregate_marker||' EQ true');
4881: bsc_aw_utility.add_g_commands(g_commands,'then do');
4882: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4883: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).reset_cube);
4884: bsc_aw_utility.add_g_commands(g_commands,'if statlen('||p_dim_set.dim(i).dim_name||') GT 0');
4885: bsc_aw_utility.add_g_commands(g_commands,'then do');
4886: bsc_aw_utility.init_is_new_value(1);
4887: for j in 1..p_dim_set.measure.count loop
4888: for k in 1..l_cube(j).cube_axis.count loop
4881: bsc_aw_utility.add_g_commands(g_commands,'then do');
4882: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4883: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).reset_cube);
4884: bsc_aw_utility.add_g_commands(g_commands,'if statlen('||p_dim_set.dim(i).dim_name||') GT 0');
4885: bsc_aw_utility.add_g_commands(g_commands,'then do');
4886: bsc_aw_utility.init_is_new_value(1);
4887: for j in 1..p_dim_set.measure.count loop
4888: for k in 1..l_cube(j).cube_axis.count loop
4889: if l_cube(j).cube_axis(k).axis_type='partition template' or l_cube(j).cube_axis(k).axis_type='composite' then
4882: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4883: bsc_aw_utility.add_g_commands(g_commands,'limit '||p_dim_set.dim(i).dim_name||' to '||p_dim_set.dim(i).reset_cube);
4884: bsc_aw_utility.add_g_commands(g_commands,'if statlen('||p_dim_set.dim(i).dim_name||') GT 0');
4885: bsc_aw_utility.add_g_commands(g_commands,'then do');
4886: bsc_aw_utility.init_is_new_value(1);
4887: for j in 1..p_dim_set.measure.count loop
4888: for k in 1..l_cube(j).cube_axis.count loop
4889: if l_cube(j).cube_axis(k).axis_type='partition template' or l_cube(j).cube_axis(k).axis_type='composite' then
4890: if bsc_aw_utility.is_new_value(p_dim_set.measure(j).cube,1) then
4886: bsc_aw_utility.init_is_new_value(1);
4887: for j in 1..p_dim_set.measure.count loop
4888: for k in 1..l_cube(j).cube_axis.count loop
4889: if l_cube(j).cube_axis(k).axis_type='partition template' or l_cube(j).cube_axis(k).axis_type='composite' then
4890: if bsc_aw_utility.is_new_value(p_dim_set.measure(j).cube,1) then
4891: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.measure(j).cube||'=NA across '||l_cube(j).cube_axis(k).axis_name);
4892: end if;
4893: end if;
4894: end loop;
4887: for j in 1..p_dim_set.measure.count loop
4888: for k in 1..l_cube(j).cube_axis.count loop
4889: if l_cube(j).cube_axis(k).axis_type='partition template' or l_cube(j).cube_axis(k).axis_type='composite' then
4890: if bsc_aw_utility.is_new_value(p_dim_set.measure(j).cube,1) then
4891: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.measure(j).cube||'=NA across '||l_cube(j).cube_axis(k).axis_name);
4892: end if;
4893: end if;
4894: end loop;
4895: end loop;
4892: end if;
4893: end if;
4894: end loop;
4895: end loop;
4896: bsc_aw_utility.add_g_commands(g_commands,'doend');
4897: bsc_aw_utility.add_g_commands(g_commands,'doend');
4898: end if;
4899: end loop;
4900: --reset values
4893: end if;
4894: end loop;
4895: end loop;
4896: bsc_aw_utility.add_g_commands(g_commands,'doend');
4897: bsc_aw_utility.add_g_commands(g_commands,'doend');
4898: end if;
4899: end loop;
4900: --reset values
4901: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4897: bsc_aw_utility.add_g_commands(g_commands,'doend');
4898: end if;
4899: end loop;
4900: --reset values
4901: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4902: for i in 1..p_dim_set.dim.count loop
4903: if p_dim_set.dim(i).aggregate_marker is not null then
4904: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).aggregate_marker||'=false');
4905: end if;
4900: --reset values
4901: bsc_aw_utility.add_g_commands(g_commands,'allstat');
4902: for i in 1..p_dim_set.dim.count loop
4903: if p_dim_set.dim(i).aggregate_marker is not null then
4904: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).aggregate_marker||'=false');
4905: end if;
4906: if p_dim_set.dim(i).reset_cube is not null then
4907: if p_dim_set.dim(i).limit_cube_composite is not null then
4908: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).reset_cube||'=false across '||p_dim_set.dim(i).limit_cube_composite);
4904: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).aggregate_marker||'=false');
4905: end if;
4906: if p_dim_set.dim(i).reset_cube is not null then
4907: if p_dim_set.dim(i).limit_cube_composite is not null then
4908: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).reset_cube||'=false across '||p_dim_set.dim(i).limit_cube_composite);
4909: else
4910: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).reset_cube||'=false');
4911: end if;
4912: end if;
4906: if p_dim_set.dim(i).reset_cube is not null then
4907: if p_dim_set.dim(i).limit_cube_composite is not null then
4908: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).reset_cube||'=false across '||p_dim_set.dim(i).limit_cube_composite);
4909: else
4910: bsc_aw_utility.add_g_commands(g_commands,p_dim_set.dim(i).reset_cube||'=false');
4911: end if;
4912: end if;
4913: end loop;
4914: if l_flag then
4911: end if;
4912: end if;
4913: end loop;
4914: if l_flag then
4915: bsc_aw_utility.exec_program_commands(p_dim_set.aggregate_marker_program,g_commands);
4916: end if;
4917: Exception when others then
4918: log_n('Exception in create_aggregate_marker_pgm '||sqlerrm);
4919: raise;
4976: Begin
4977: if g_debug then
4978: log('check_partition_possible dimset '||p_actual_dim_set.dim_set_name||', with target '||p_target_dim_set.dim_set_name);
4979: end if;
4980: if bsc_aw_utility.get_db_version<10.2 then
4981: if g_debug then
4982: log('DB < 10.2. No partitions');
4983: end if;
4984: return false;
4990: end if;
4991: return false;
4992: end if;
4993: --
4994: if nvl(bsc_aw_utility.get_parameter_value('NO PARTITION'),'N')='Y' then
4995: if g_debug then
4996: log('No partition specified.');
4997: end if;
4998: return false;
5027: end if;
5028: return false;
5029: end if;
5030: /*for now, if thereis AVERAGE or COUNT aggregation, we disable partitions */
5031: if bsc_aw_utility.is_PT_aggregation_function(p_actual_dim_set.measure(i).agg_formula.agg_formula)='N' then
5032: if g_debug then
5033: log('Non partitionable aggregation function '||p_actual_dim_set.measure(i).agg_formula.agg_formula);
5034: end if;
5035: return false;
5053: for it. we can partition when there are targets as long as actuals and targets have the same number of partitions and a given period is guaranteed
5054: to be in P.x of actuals and P.x of targets
5055: */
5056: if p_actual_dim_set.targets_higher_levels='Y' then
5057: if nvl(bsc_aw_utility.get_parameter_value('NO TARGET PARTITION'),'N')='Y' then
5058: if g_debug then
5059: log('Targets at higher level and NO TARGET PARTITION specified. No partitions');
5060: end if;
5061: return false;
5082: end loop;
5083: if g_debug then
5084: log('Partitions are possible. Only HASH partitions on Time Dimension');
5085: end if;
5086: bsc_aw_utility.merge_property(p_actual_dim_set.property,'hash partition',null,'Y');
5087: bsc_aw_utility.merge_property(p_actual_dim_set.property,'hash partition only time',null,'Y');
5088: /* */
5089: bsc_aw_utility.merge_property(p_target_dim_set.property,'hash partition',null,'Y');
5090: bsc_aw_utility.merge_property(p_target_dim_set.property,'hash partition only time',null,'Y');
5083: if g_debug then
5084: log('Partitions are possible. Only HASH partitions on Time Dimension');
5085: end if;
5086: bsc_aw_utility.merge_property(p_actual_dim_set.property,'hash partition',null,'Y');
5087: bsc_aw_utility.merge_property(p_actual_dim_set.property,'hash partition only time',null,'Y');
5088: /* */
5089: bsc_aw_utility.merge_property(p_target_dim_set.property,'hash partition',null,'Y');
5090: bsc_aw_utility.merge_property(p_target_dim_set.property,'hash partition only time',null,'Y');
5091: end if;
5085: end if;
5086: bsc_aw_utility.merge_property(p_actual_dim_set.property,'hash partition',null,'Y');
5087: bsc_aw_utility.merge_property(p_actual_dim_set.property,'hash partition only time',null,'Y');
5088: /* */
5089: bsc_aw_utility.merge_property(p_target_dim_set.property,'hash partition',null,'Y');
5090: bsc_aw_utility.merge_property(p_target_dim_set.property,'hash partition only time',null,'Y');
5091: end if;
5092: /*partition possible*/
5093: return true;
5086: bsc_aw_utility.merge_property(p_actual_dim_set.property,'hash partition',null,'Y');
5087: bsc_aw_utility.merge_property(p_actual_dim_set.property,'hash partition only time',null,'Y');
5088: /* */
5089: bsc_aw_utility.merge_property(p_target_dim_set.property,'hash partition',null,'Y');
5090: bsc_aw_utility.merge_property(p_target_dim_set.property,'hash partition only time',null,'Y');
5091: end if;
5092: /*partition possible*/
5093: return true;
5094: Exception when others then
5103: l_bt_levels dbms_sql.varchar2_table;
5104: l_reason varchar2(8000);
5105: l_partition_count number;
5106: Begin
5107: if bsc_aw_utility.get_property(p_dim_set.property,'hash partition').property_value='Y' then
5108: p_dim_set.partition_type:='hash';
5109: else
5110: p_dim_set.partition_type:='list';--default
5111: end if;
5138: end if;
5139: end if;
5140: if p_dim_set.partition_type='list' then
5141: for i in 1..p_dim_set.dim.count loop
5142: if bsc_aw_utility.in_array(l_bt_levels,p_dim_set.dim(i).levels(1).level_name)=false then
5143: p_dim_set.partition_type:='hash';
5144: l_reason:='Dimset has diff dim levels than B table. Only hash partition possible';
5145: exit;
5146: end if;
5157: exit;
5158: end if;
5159: /*check that each levels match */
5160: for k in 1..p_dim_set.data_source(i).base_tables(j).levels.count loop
5161: if bsc_aw_utility.in_array(l_bt_levels,p_dim_set.data_source(i).base_tables(j).levels(k).level_name)=false then
5162: l_reason:='B table '||p_dim_set.data_source(i).base_tables(j).base_table_name||' has level '||
5163: p_dim_set.data_source(i).base_tables(j).levels(k).level_name||' that is not in the first B table. List partitions not possible';
5164: p_dim_set.partition_type:='hash';
5165: exit;
5300: end if;
5301: end loop;
5302: partition_dim:=false; /*for now, no dim involved in hash partitions */
5303: partition_time:=true;
5304: if bsc_aw_utility.get_property(p_dim_set.property,'hash partition only time').property_value='Y' then
5305: partition_dim:=false;/*this will be the case for targets at higher levels */
5306: end if;
5307: if partition_time then
5308: p_partition_template.hpt_data.hpt_calendar.dim_name:=p_dim_set.calendar.aw_dim;
5351: End;
5352:
5353: procedure set_PT_dim_aggregated(p_dim in out nocopy dim_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table) is
5354: l_parent_child bsc_aw_adapter_dim.dim_parent_child_tb;
5355: l_pc bsc_aw_utility.parent_child_tb;
5356: l_child_levels dbms_sql.varchar2_table;
5357: zero_aggregated dbms_sql.varchar2_table;
5358: aggregated dbms_sql.varchar2_table;
5359: Begin
5365: end if;
5366: end loop;
5367: /*if there is partition at country say, we can rollup to country but not perform zero code */
5368: for i in 1..p_dim.levels.count loop
5369: if bsc_aw_utility.in_array(p_levels,p_dim.levels(i).level_name) then
5370: /*p_dim.levels(i).aggregated is left unchanged*/
5371: p_dim.levels(i).zero_aggregated:='N';/*there cannot be zero code aggregation at this level */
5372: aggregated(i):=p_dim.levels(i).aggregated;
5373: zero_aggregated(i):=p_dim.levels(i).zero_aggregated;
5384: end loop;
5385: if l_pc.count>0 then
5386: for i in 1..p_levels.count loop
5387: l_child_levels.delete;
5388: bsc_aw_utility.get_all_children(l_pc,p_levels(i),l_child_levels);
5389: if l_child_levels.count>0 then
5390: for j in 1..p_dim.levels.count loop
5391: if bsc_aw_utility.in_array(l_child_levels,p_dim.levels(j).level_name) then
5392: p_dim.levels(j).aggregated:=aggregated(j);
5387: l_child_levels.delete;
5388: bsc_aw_utility.get_all_children(l_pc,p_levels(i),l_child_levels);
5389: if l_child_levels.count>0 then
5390: for j in 1..p_dim.levels.count loop
5391: if bsc_aw_utility.in_array(l_child_levels,p_dim.levels(j).level_name) then
5392: p_dim.levels(j).aggregated:=aggregated(j);
5393: p_dim.levels(j).zero_aggregated:=zero_aggregated(j);
5394: if g_debug then
5395: log('Dimension '||p_dim.dim_name||', Child Level '||p_dim.levels(j).level_name||' aggregated='''||p_dim.levels(j).aggregated||
5416: raise;
5417: End;
5418:
5419: procedure set_PT_calendar_aggregated(p_calendar in out nocopy calendar_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table) is
5420: l_pc bsc_aw_utility.parent_child_tb;
5421: l_child_levels dbms_sql.varchar2_table;
5422: aggregated dbms_sql.varchar2_table;
5423: Begin
5424: for i in 1..p_calendar.parent_child.count loop
5427: l_pc(l_pc.count).child:=p_calendar.parent_child(i).child_dim_name;
5428: end if;
5429: end loop;
5430: for i in 1..p_calendar.periodicity.count loop
5431: if bsc_aw_utility.in_array(p_levels,p_calendar.periodicity(i).aw_dim) then
5432: /*p_calendar.periodicity(i).aggregated is left unchanged */
5433: aggregated(i):=p_calendar.periodicity(i).aggregated;/*could be Y or N */
5434: else
5435: aggregated(i):=p_calendar.periodicity(i).aggregated;
5438: end loop;
5439: if l_pc.count>0 then
5440: for i in 1..p_levels.count loop
5441: l_child_levels.delete;
5442: bsc_aw_utility.get_all_children(l_pc,p_levels(i),l_child_levels);
5443: if l_child_levels.count>0 then
5444: for j in 1..p_calendar.periodicity.count loop
5445: if bsc_aw_utility.in_array(l_child_levels,p_calendar.periodicity(j).aw_dim) then
5446: p_calendar.periodicity(j).aggregated:=aggregated(j);
5441: l_child_levels.delete;
5442: bsc_aw_utility.get_all_children(l_pc,p_levels(i),l_child_levels);
5443: if l_child_levels.count>0 then
5444: for j in 1..p_calendar.periodicity.count loop
5445: if bsc_aw_utility.in_array(l_child_levels,p_calendar.periodicity(j).aw_dim) then
5446: p_calendar.periodicity(j).aggregated:=aggregated(j);
5447: if g_debug then
5448: log('Calendar '||p_calendar.aw_dim||', Child Periodicity '||p_calendar.periodicity(j).aw_dim||' aggregated='''||
5449: p_calendar.periodicity(j).aggregated||'''');
5461: function get_partition_count return number is
5462: l_cpu_count number;
5463: l_partition_count number;
5464: Begin
5465: l_cpu_count:=bsc_aw_utility.get_cpu_count;
5466: l_partition_count:=to_number(bsc_aw_utility.get_parameter_value('NUMBER PARTITION'));--most of the time null
5467: if l_partition_count is null or l_partition_count=0 then
5468: l_partition_count:=bsc_aw_utility.get_min(bsc_aw_utility.get_closest_2_power_number(l_cpu_count),bsc_aw_utility.g_max_partitions);
5469: else
5462: l_cpu_count number;
5463: l_partition_count number;
5464: Begin
5465: l_cpu_count:=bsc_aw_utility.get_cpu_count;
5466: l_partition_count:=to_number(bsc_aw_utility.get_parameter_value('NUMBER PARTITION'));--most of the time null
5467: if l_partition_count is null or l_partition_count=0 then
5468: l_partition_count:=bsc_aw_utility.get_min(bsc_aw_utility.get_closest_2_power_number(l_cpu_count),bsc_aw_utility.g_max_partitions);
5469: else
5470: if l_partition_count>l_cpu_count then
5464: Begin
5465: l_cpu_count:=bsc_aw_utility.get_cpu_count;
5466: l_partition_count:=to_number(bsc_aw_utility.get_parameter_value('NUMBER PARTITION'));--most of the time null
5467: if l_partition_count is null or l_partition_count=0 then
5468: l_partition_count:=bsc_aw_utility.get_min(bsc_aw_utility.get_closest_2_power_number(l_cpu_count),bsc_aw_utility.g_max_partitions);
5469: else
5470: if l_partition_count>l_cpu_count then
5471: l_partition_count:=l_cpu_count; /*max partitions possible is cpu count */
5472: end if;
5470: if l_partition_count>l_cpu_count then
5471: l_partition_count:=l_cpu_count; /*max partitions possible is cpu count */
5472: end if;
5473: end if;
5474: if l_partition_count>bsc_aw_utility.g_max_partitions then
5475: l_partition_count:=bsc_aw_utility.g_max_partitions;
5476: end if;
5477: return l_partition_count;
5478: Exception when others then
5471: l_partition_count:=l_cpu_count; /*max partitions possible is cpu count */
5472: end if;
5473: end if;
5474: if l_partition_count>bsc_aw_utility.g_max_partitions then
5475: l_partition_count:=bsc_aw_utility.g_max_partitions;
5476: end if;
5477: return l_partition_count;
5478: Exception when others then
5479: log_n('Exception in get_partition_count '||sqlerrm);
5622: l_ordered_bt.delete;
5623: for j in 1..p_data_source(i).base_tables.count loop
5624: l_ordered_bt(l_ordered_bt.count+1):=upper(p_data_source(i).base_tables(j).base_table_name);
5625: end loop;
5626: l_ordered_bt:=bsc_aw_utility.order_array(l_ordered_bt);
5627: p_load_program.ds_base_tables:=p_load_program.ds_base_tables||bsc_aw_utility.make_string_from_list(l_ordered_bt,'^')||'+';
5628: end loop;
5629: if g_debug then
5630: log('Program name '||p_load_program.program_name||', DS Tables '||p_load_program.ds_base_tables);
5623: for j in 1..p_data_source(i).base_tables.count loop
5624: l_ordered_bt(l_ordered_bt.count+1):=upper(p_data_source(i).base_tables(j).base_table_name);
5625: end loop;
5626: l_ordered_bt:=bsc_aw_utility.order_array(l_ordered_bt);
5627: p_load_program.ds_base_tables:=p_load_program.ds_base_tables||bsc_aw_utility.make_string_from_list(l_ordered_bt,'^')||'+';
5628: end loop;
5629: if g_debug then
5630: log('Program name '||p_load_program.program_name||', DS Tables '||p_load_program.ds_base_tables);
5631: end if;
5678: Begin
5679: p_actual_dim_set.compressed:='N';--default
5680: p_target_dim_set.compressed:='N';--default
5681: l_countvar_flag:=check_countvar_cube_needed(p_actual_dim_set);
5682: if nvl(bsc_aw_utility.get_parameter_value('NO COMPRESSED COMPOSITE'),'N')='Y' then
5683: if g_debug then
5684: log('Non compressed composite specified');
5685: end if;
5686: return;
5684: log('Non compressed composite specified');
5685: end if;
5686: return;
5687: end if;
5688: if bsc_aw_utility.get_db_version>=10.2 or nvl(bsc_aw_utility.get_parameter_value('COMPRESSED COMPOSITE'),'N')='Y' then
5689: /*if we have average in aggregation, we need countvar. countvar cannot share the same composite as main cube. if we have
5690: diff composite, we cannot aggregate. aw throws error
5691: ORA-36168: COUNTVAR variable BSC_AW!DATACUBE.1.4014.COUNTVAR must have the same dimensionality as AGGMAP object
5692: BSC_AW!DATACUBE.1.4014. so we have to disable compressed composite
5696: p_actual_dim_set.compressed:='N';
5697: l_reason:='Target at higher level';
5698: end if;
5699: /*if l_countvar_flag then
5700: bsc_aw_utility.merge_property(p_actual_dim_set.property,'aggcount',null,'Y');
5701: end if;*/
5702: /*even with aggcount set in the cube, aw still complains that we need countvar cube when we have aggregate cube using aggmap. so for now,
5703: no CC for avg */
5704: if l_countvar_flag then
5715: exit;
5716: end if;
5717: end loop;
5718: if l_agg_formula is not null then
5719: if bsc_aw_utility.is_CC_aggregation_function(l_agg_formula)='N' then
5720: p_actual_dim_set.compressed:='N';
5721: l_reason:='Agg Formula '||l_agg_formula||' cannot have compressed composite';
5722: end if;
5723: end if;
5851: for i in 1..p_periodicity.count loop
5852: l_periodicity(l_periodicity.count+1):=p_periodicity(i).aw_dim;
5853: end loop;
5854: for i in 1..p_pc.count loop
5855: if (p_pc(i).parent_dim_name is null or bsc_aw_utility.in_array(l_periodicity,p_pc(i).parent_dim_name))
5856: and (p_pc(i).child_dim_name is null or bsc_aw_utility.in_array(l_periodicity,p_pc(i).child_dim_name)) then
5857: p_relevant_pc(p_relevant_pc.count+1):=p_pc(i);
5858: end if;
5859: end loop;
5852: l_periodicity(l_periodicity.count+1):=p_periodicity(i).aw_dim;
5853: end loop;
5854: for i in 1..p_pc.count loop
5855: if (p_pc(i).parent_dim_name is null or bsc_aw_utility.in_array(l_periodicity,p_pc(i).parent_dim_name))
5856: and (p_pc(i).child_dim_name is null or bsc_aw_utility.in_array(l_periodicity,p_pc(i).child_dim_name)) then
5857: p_relevant_pc(p_relevant_pc.count+1):=p_pc(i);
5858: end if;
5859: end loop;
5860: Exception when others then
5863: End;
5864:
5865: /*given a hier and a start value, it finds the upper hier */
5866: procedure get_upper_cal_hier(p_pc cal_parent_child_tb,p_child varchar2,p_upper_hier out nocopy cal_parent_child_tb) is
5867: l_pc bsc_aw_utility.parent_child_tb;
5868: l_trim_pc bsc_aw_utility.parent_child_tb;
5869: Begin
5870: for i in 1..p_pc.count loop
5871: l_pc(l_pc.count+1).parent:=p_pc(i).parent_dim_name;
5864:
5865: /*given a hier and a start value, it finds the upper hier */
5866: procedure get_upper_cal_hier(p_pc cal_parent_child_tb,p_child varchar2,p_upper_hier out nocopy cal_parent_child_tb) is
5867: l_pc bsc_aw_utility.parent_child_tb;
5868: l_trim_pc bsc_aw_utility.parent_child_tb;
5869: Begin
5870: for i in 1..p_pc.count loop
5871: l_pc(l_pc.count+1).parent:=p_pc(i).parent_dim_name;
5872: l_pc(l_pc.count).child:=p_pc(i).child_dim_name;
5870: for i in 1..p_pc.count loop
5871: l_pc(l_pc.count+1).parent:=p_pc(i).parent_dim_name;
5872: l_pc(l_pc.count).child:=p_pc(i).child_dim_name;
5873: end loop;
5874: bsc_aw_utility.get_upper_trim_hier(l_pc,p_child,l_trim_pc);
5875: --
5876: for i in 1..l_trim_pc.count loop
5877: for j in 1..p_pc.count loop
5878: if nvl(p_pc(j).parent_dim_name,'^^^')=nvl(l_trim_pc(i).parent,'^^^')
5891: have BALANCE LAST VALUE type measure */
5892: procedure create_temp_variables(p_dim_set dim_set_r,p_data_source data_source_r) is
5893: l_balance_loaded_column varchar2(40);
5894: Begin
5895: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
5896: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_period_temp||'\'') eq false');
5897: bsc_aw_utility.add_g_commands(g_commands,'then do');
5898: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_period_temp||' NUMBER session');
5899: bsc_aw_utility.add_g_commands(g_commands,'doend');
5892: procedure create_temp_variables(p_dim_set dim_set_r,p_data_source data_source_r) is
5893: l_balance_loaded_column varchar2(40);
5894: Begin
5895: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
5896: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_period_temp||'\'') eq false');
5897: bsc_aw_utility.add_g_commands(g_commands,'then do');
5898: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_period_temp||' NUMBER session');
5899: bsc_aw_utility.add_g_commands(g_commands,'doend');
5900: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_year_temp||'\'') eq false');
5893: l_balance_loaded_column varchar2(40);
5894: Begin
5895: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
5896: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_period_temp||'\'') eq false');
5897: bsc_aw_utility.add_g_commands(g_commands,'then do');
5898: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_period_temp||' NUMBER session');
5899: bsc_aw_utility.add_g_commands(g_commands,'doend');
5900: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_year_temp||'\'') eq false');
5901: bsc_aw_utility.add_g_commands(g_commands,'then do');
5894: Begin
5895: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
5896: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_period_temp||'\'') eq false');
5897: bsc_aw_utility.add_g_commands(g_commands,'then do');
5898: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_period_temp||' NUMBER session');
5899: bsc_aw_utility.add_g_commands(g_commands,'doend');
5900: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_year_temp||'\'') eq false');
5901: bsc_aw_utility.add_g_commands(g_commands,'then do');
5902: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_year_temp||' NUMBER session');
5895: if bsc_aw_utility.get_property(p_data_source.property,g_balance_last_value_prop).property_value='Y' then
5896: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_period_temp||'\'') eq false');
5897: bsc_aw_utility.add_g_commands(g_commands,'then do');
5898: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_period_temp||' NUMBER session');
5899: bsc_aw_utility.add_g_commands(g_commands,'doend');
5900: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_year_temp||'\'') eq false');
5901: bsc_aw_utility.add_g_commands(g_commands,'then do');
5902: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_year_temp||' NUMBER session');
5903: bsc_aw_utility.add_g_commands(g_commands,'doend');
5896: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_period_temp||'\'') eq false');
5897: bsc_aw_utility.add_g_commands(g_commands,'then do');
5898: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_period_temp||' NUMBER session');
5899: bsc_aw_utility.add_g_commands(g_commands,'doend');
5900: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_year_temp||'\'') eq false');
5901: bsc_aw_utility.add_g_commands(g_commands,'then do');
5902: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_year_temp||' NUMBER session');
5903: bsc_aw_utility.add_g_commands(g_commands,'doend');
5904: /*columns to hold balance loaded are also temp . balance loaded column will hold number. see create_base_table_sql decodes this
5897: bsc_aw_utility.add_g_commands(g_commands,'then do');
5898: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_period_temp||' NUMBER session');
5899: bsc_aw_utility.add_g_commands(g_commands,'doend');
5900: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_year_temp||'\'') eq false');
5901: bsc_aw_utility.add_g_commands(g_commands,'then do');
5902: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_year_temp||' NUMBER session');
5903: bsc_aw_utility.add_g_commands(g_commands,'doend');
5904: /*columns to hold balance loaded are also temp . balance loaded column will hold number. see create_base_table_sql decodes this
5905: to number*/
5898: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_period_temp||' NUMBER session');
5899: bsc_aw_utility.add_g_commands(g_commands,'doend');
5900: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_year_temp||'\'') eq false');
5901: bsc_aw_utility.add_g_commands(g_commands,'then do');
5902: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_year_temp||' NUMBER session');
5903: bsc_aw_utility.add_g_commands(g_commands,'doend');
5904: /*columns to hold balance loaded are also temp . balance loaded column will hold number. see create_base_table_sql decodes this
5905: to number*/
5906: for i in 1..p_data_source.measure.count loop
5899: bsc_aw_utility.add_g_commands(g_commands,'doend');
5900: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||g_year_temp||'\'') eq false');
5901: bsc_aw_utility.add_g_commands(g_commands,'then do');
5902: bsc_aw_utility.add_g_commands(g_commands,'dfn '||g_year_temp||' NUMBER session');
5903: bsc_aw_utility.add_g_commands(g_commands,'doend');
5904: /*columns to hold balance loaded are also temp . balance loaded column will hold number. see create_base_table_sql decodes this
5905: to number*/
5906: for i in 1..p_data_source.measure.count loop
5907: if p_data_source.measure(i).measure_type=g_balance_last_value_prop then
5904: /*columns to hold balance loaded are also temp . balance loaded column will hold number. see create_base_table_sql decodes this
5905: to number*/
5906: for i in 1..p_data_source.measure.count loop
5907: if p_data_source.measure(i).measure_type=g_balance_last_value_prop then
5908: l_balance_loaded_column:=bsc_aw_utility.get_property(p_data_source.measure(i).property,g_balance_loaded_column_prop).property_value;
5909: if l_balance_loaded_column is not null then
5910: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_balance_loaded_column||'\'') eq false');
5911: bsc_aw_utility.add_g_commands(g_commands,'then do');
5912: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_balance_loaded_column||' NUMBER session');
5906: for i in 1..p_data_source.measure.count loop
5907: if p_data_source.measure(i).measure_type=g_balance_last_value_prop then
5908: l_balance_loaded_column:=bsc_aw_utility.get_property(p_data_source.measure(i).property,g_balance_loaded_column_prop).property_value;
5909: if l_balance_loaded_column is not null then
5910: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_balance_loaded_column||'\'') eq false');
5911: bsc_aw_utility.add_g_commands(g_commands,'then do');
5912: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_balance_loaded_column||' NUMBER session');
5913: bsc_aw_utility.add_g_commands(g_commands,'doend');
5914: end if;
5907: if p_data_source.measure(i).measure_type=g_balance_last_value_prop then
5908: l_balance_loaded_column:=bsc_aw_utility.get_property(p_data_source.measure(i).property,g_balance_loaded_column_prop).property_value;
5909: if l_balance_loaded_column is not null then
5910: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_balance_loaded_column||'\'') eq false');
5911: bsc_aw_utility.add_g_commands(g_commands,'then do');
5912: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_balance_loaded_column||' NUMBER session');
5913: bsc_aw_utility.add_g_commands(g_commands,'doend');
5914: end if;
5915: end if;
5908: l_balance_loaded_column:=bsc_aw_utility.get_property(p_data_source.measure(i).property,g_balance_loaded_column_prop).property_value;
5909: if l_balance_loaded_column is not null then
5910: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_balance_loaded_column||'\'') eq false');
5911: bsc_aw_utility.add_g_commands(g_commands,'then do');
5912: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_balance_loaded_column||' NUMBER session');
5913: bsc_aw_utility.add_g_commands(g_commands,'doend');
5914: end if;
5915: end if;
5916: end loop;
5909: if l_balance_loaded_column is not null then
5910: bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_balance_loaded_column||'\'') eq false');
5911: bsc_aw_utility.add_g_commands(g_commands,'then do');
5912: bsc_aw_utility.add_g_commands(g_commands,'dfn '||l_balance_loaded_column||' NUMBER session');
5913: bsc_aw_utility.add_g_commands(g_commands,'doend');
5914: end if;
5915: end if;
5916: end loop;
5917: end if;
6089: p_data_source.data_source_PT.partition_template.hpt_data.hpt_dimensions(
6090: p_data_source.data_source_PT.partition_template.hpt_data.hpt_dimensions.count).dim_type:=p_hpt_data.hpt_dimensions(j).dim_type;
6091: l_dim_parent_child.delete;
6092: p_data_source.data_source_PT.dim_parent_child(p_hpt_data.hpt_dimensions(j).dim_name):=l_dim_parent_child;/*empty parent child info */
6093: l_index:=bsc_aw_utility.get_array_index(p_hpt_data.hpt_dimensions(j).level_names,p_DS_dim(i).levels(1).level_name);
6094: if l_index is not null then
6095: p_data_source.data_source_PT.partition_template.hpt_data.hpt_dimensions(
6096: p_data_source.data_source_PT.partition_template.hpt_data.hpt_dimensions.count).level_names(
6097: p_data_source.data_source_PT.partition_template.hpt_data.hpt_dimensions(
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));
6119: log('This means we cannot rollup from the dim level of the DS to the dim level at which the partition is defined. This is not allowed');
6120: raise bsc_aw_utility.g_exception;
6121: end if;
6122: l_dim_parent_child.delete;
6123: for k in 1..l_pc_subset.count loop
6124: l_dim_parent_child(l_dim_parent_child.count+1).parent_level:=l_pc_subset(k).parent_level;
6174: p_data_source.data_source_PT.partition_template.hpt_data.hpt_calendar.level_keys.delete;
6175: if p_hpt_data.hpt_calendar.dim_name is not null and p_hpt_data.hpt_calendar.dim_name=p_DS_calendar.aw_dim then
6176: p_data_source.data_source_PT.partition_template.hpt_data.hpt_calendar.dim_name:=p_hpt_data.hpt_calendar.dim_name;
6177: p_data_source.data_source_PT.partition_template.hpt_data.hpt_calendar.dim_type:=p_hpt_data.hpt_calendar.dim_type;
6178: l_index:=bsc_aw_utility.get_array_index(p_hpt_data.hpt_calendar.level_names,p_DS_calendar.periodicity(1).aw_dim);
6179: if l_index is not null then
6180: p_data_source.data_source_PT.partition_template.hpt_data.hpt_calendar.level_names(
6181: p_data_source.data_source_PT.partition_template.hpt_data.hpt_calendar.level_names.count+1):=
6182: p_hpt_data.hpt_calendar.level_names(l_index);
6326: */
6327: procedure set_sql_aggregations(p_kpi in out nocopy kpi_r,p_action varchar2) is
6328: Begin
6329: for i in 1..p_kpi.dim_set.count loop
6330: if (p_action='set' and bsc_aw_utility.get_db_version>=10.2 and p_kpi.dim_set(i).targets_higher_levels<>'Y')
6331: or (p_action='unset' and p_kpi.dim_set(i).number_partitions=0 and p_kpi.dim_set(i).compressed<>'Y') then
6332: set_sql_aggregations(p_kpi,p_kpi.dim_set(i),p_action);
6333: end if;
6334: end loop;
6397: end if;
6398: flag:=true;
6399: exit;
6400: end if;
6401: if bsc_aw_utility.is_PT_aggregation_function(measures(i).agg_formula.agg_formula)='N' then
6402: if g_debug then
6403: log('Non partitionable aggregation function '||measures(i).agg_formula.agg_formula||'. Partitions possible only with '||
6404: 'display cubes');
6405: end if;
6416: End;
6417:
6418: function is_display_cube_possible(p_dim_set dim_set_r) return boolean is
6419: Begin
6420: if nvl(bsc_aw_utility.get_parameter_value('NO DISPLAY CUBE'),'N')='Y' then
6421: return false;
6422: end if;
6423: return true;
6424: Exception when others then
6442:
6443: ------------------
6444: procedure init_all is
6445: Begin
6446: g_debug:=bsc_aw_utility.g_debug;
6447: Exception when others then
6448: null;
6449: End;
6450:
6449: End;
6450:
6451: procedure log(p_message varchar2) is
6452: Begin
6453: bsc_aw_utility.log(p_message);
6454: Exception when others then
6455: null;
6456: End;
6457: