1 package BSC_AW_ADAPTER_KPI AS
2 /*$Header: BSCAWAKS.pls 120.24 2006/04/17 15:38 vsurendr noship $*/
3 --program runtime parameters
4 g_debug boolean;
5 g_stmt varchar2(32000);
6 g_commands dbms_sql.varchar2_table;
7 ----types----------------------------------------------------------
8 --measures are the measures involved in the agg formula
9 --we need the measures becasue if we need to do the agg on-line, we need to know what the base measurea are for a formula.
10 --we first need to aggregate them, then run the formula
11 type agg_formula_r is record(
12 agg_formula varchar2(2000),
13 sql_agg_formula varchar2(2000),/*this is the original agg formula from metadata. used when we have non std agg and partitions */
14 std_aggregation varchar2(20),--Y means sum average etc. N means its a formula like measure1/measure2
15 avg_aggregation varchar2(20),--Y means average
16 cubes dbms_sql.varchar2_table, /*the cubes in the formula */
17 measures dbms_sql.varchar2_table /*the measures in the formula */
18 );
19 --
20 /*
21 we introduce here new classes. cube_r, partition_template_r, partition_r, composite_r and partition_r. we need to support the following designs
22 9i : separate cubes , 1 composite
23 10g : separate cubes, separate composites
24 datacube. no partitons
25 datacube with partitions
26 measures will reference cubes. cubes will reference through the axis class partitions or composites or dimensions.
27 axis_name is the name of the dimension or composite or partition
28 composites, cubes and partitions will be within a dimset. there is no sharing of objects across dimset
29 we need to look at the cpu count and then decide on the number of partitions. default number of partitions=4
30 */
31 type axis_r is record(
32 axis_name varchar2(4000),
33 axis_type varchar2(100)
34 );
35 type axis_tb is table of axis_r index by pls_integer;
36 --
37 type composite_r is record(
38 composite_name varchar2(200),
39 composite_type varchar2(100), --compressed or non compressed
40 composite_dimensions dbms_sql.varchar2_table
41 );
42 type composite_tb is table of composite_r index by pls_integer;
43 --
44 /*we need to implement partitions in a whole new way. earlier we were implementing list and hash on dim keys. this will not do
45 first, avg measure goes wrong . avg becomes at reporting time ((a1+..am)/m + (b1+...bn)/n) / 2 if there are 2 partitions. the correct avg is
46 (a1+..am+b1+...bn)/(n+m) . also if we consider sum measure, though the final value is technically correct, there is enormous composite node
47 duplication across partitions.
48 new partitioning strategy will partition mostly on time. say we partition at day level. there will be no agg on time. at runtime, time agg is
49 done. though there is node duplication, its only with the other dim limited. time gives predictable load for online agg. worst case is 366 values to
50 aggregate to year level. if there is avg, have to go with maybe partition on type, since there is no agg on type. we will create generic data structures
51 so that we can aggregate on any dim at any level. we can choose to aggregate at state level and time. then the agg stop at state. country aggregation
52 will be runtime
53 hpt: hash partition*/
54 type hpt_dimension_r is record( --dimensions of the dimset which are used to hash
55 dim_name varchar2(100),
56 dim_type varchar2(40),--normal,std,time
57 level_names dbms_sql.varchar2_table, /*there can be multiple levels per dim we apply pt on. example, month and week level */
58 level_keys dbms_sql.varchar2_table
59 );
60 type hpt_dimension_tb is table of hpt_dimension_r index by pls_integer;
61 --
62 type hpt_data_r is record( --top hpt info holder
63 hpt_dimensions hpt_dimension_tb,
64 hpt_calendar hpt_dimension_r
65 );
66 --
67 type partition_r is record( --these are the individual partitions
68 partition_name varchar2(200),
69 partition_dim_value varchar2(200),
70 partition_axis axis_tb --for each value of the partition dim value , what is the name of the composite or dimensions
71 );
72 type partition_tb is table of partition_r index by pls_integer;
73 --
74 type partition_template_r is record(
75 template_name varchar2(200),
76 template_type varchar2(40), --lits or range etc
77 template_use varchar2(40), --used for datacube or countvar cube etc
78 template_dim varchar2(200), --hash dim or some range dim
79 template_dimensions dbms_sql.varchar2_table,--this just holds the dim of the template excluding partion and measure dim
80 template_partitions partition_tb,
81 hpt_data hpt_data_r --populated when there are hash partitions
82 );
83 type partition_template_tb is table of partition_template_r index by pls_integer;
84 --
85 type cube_r is record(
86 cube_name varchar2(200),
87 cube_type varchar2(40), --not used
88 cube_datatype varchar2(40),
89 cube_axis axis_tb --reference to composite or partition template or dimensions
90 );
91 type cube_tb is table of cube_r index by pls_integer;
92 --
93 --cube set contains the set of associated cubes. with each cube is a fcst cube, countvar cube. this is a logical structure
94 type cube_set_r is record(
95 cube_set_name varchar2(200),
96 cube cube_r,
97 display_cube cube_r,/*if cube is compressed and partitioned, display cube will be in the view and data is copied into it from main cube during display*/
98 fcst_cube cube_r,
99 countvar_cube cube_r,
100 cube_set_type varchar2(40), --datacube or measurecube
101 measurename_dim varchar2(200) --if the cubeset is datacube
102 );
103 type cube_set_tb is table of cube_set_r index by pls_integer;
104 --
105 type formula_r is record(
106 formula_name varchar2(200),
107 formula_expression varchar2(4000)
108 );
109 type formula_tb is table of formula_r index by pls_integer;
110 --
111 /*
112 measure_r holds measure name, formula etc
113 formula : used in creating data source.useful for the lowest level only to see how to pull data from base tables
114 agg_formula : this holds how the agg is to be done. if it has
115 Sum(BSCIC460)/Decode(Sum(BSCIC461),0,Null,Sum(BSCIC461)), it means this cube=cube_460/cube_461 > this is
116 average at lowest level
117 it may be sum or average or max or min for other columns
118 if we have average at the lowest level we are going to represent agg_formula as cube_460/cube_461.
119 the optimizer api will return the measures. we will substitute the measures with the cubes and simply execute the
120 cubes in the aggregation module. we store the agg formula in bsc olap metadata
121 measures can share cubes. so we hold cube names in measures, not cubeset in measure
122 */
123 type measure_r is record(
124 measure varchar2(100),
125 measure_type varchar2(80),--normal or balance. by default balance is end period balance
126 data_type varchar2(40),--aw data type
127 formula varchar2(400), --formula has the agg in it.. MIN(Gr_806Sim1/DECODE(Gr_806Sim2,0,NULL,Gr_806Sim2))
128 agg_formula agg_formula_r,
129 sql_aggregated varchar2(10),--if non std agg, then sql agg means the non std agg is in the view stmt. used with non std agg and partitions
130 aw_formula formula_r,--if datacube, this will be datacube(measurenamedim ''m1''), else cube name
131 forecast varchar2(10), --Y or N,
132 forecast_method varchar2(100), --this is null for now. projections in B tables
133 cube varchar2(300),
134 countvar_cube varchar2(300),
135 fcst_cube varchar2(300), -- if the measure has forecast
136 display_cube varchar2(300),/*compressed composite and partitions */
137 property bsc_aw_utility.property_tb /*if this is BALANCE LAST VALUE, this can contain balance loaded Y/N column name */
138 );
139 type measure_tb is table of measure_r index by pls_integer;
140 --
141 /*
142 these hold the aggmap oprrators like measure , its agg formula etc. used in aggmaps
143 */
144 type aggmap_operator_r is record(
145 measure_dim varchar2(300),
146 opvar varchar2(300),
147 argvar varchar2(300)
148 );
149 --
150 /*
151 we want to use opvar, argvar etc so we can support any agg method aw supports
152 */
153 --
154 type agg_map_r is record(
155 agg_map varchar2(300),
156 property varchar2(300),
157 created varchar2(10), --if the dimset has no dim except type,proj and cal, there will not be an aggmap_notime
158 aggmap_operator aggmap_operator_r
159 );
160 --
161 --dimensions in a dim set
162 --we cannot use dim adapter dim data structures since dim creation and kpi creation can happen independent
163 --of each other. so we cannot assume g_dimensions will have data in it. we will hold here the info we need
164 --dim_name is the CC dim name
165 --within a dim, a kpi will be pointing to a level. for example, in a geog dim, kpi will point to city level
166 --we need to know this level. this will be levels(1). levels will hold the aw dim name for the level
167 --zero code is at level granularity. a dim may have multiple highest levels, but the dim set may need zero code
168 --on only 1 of those highest levels.
169 --filter will be (select code from ...where ...) so we can do level.pk in filter, we will hold the filter at the
170 --lowest level of the dim set
171 type level_r is record(
172 level_name varchar2(300), --this is the aw dim level name
173 level_type varchar2(40), --normal vs time
174 pk varchar2(100), --fk=pk for now
175 fk varchar2(100),
176 data_type varchar2(40), --for now, all dim are text. so this muust be varchar2(300) needed to create olap table fn
177 zero_code varchar2(10),
178 zero_code_level varchar2(300),--the name of the aw dim that is used to represent zero level
179 rec_parent_level varchar2(300),--the name of the aw dim that is used to represent rec parent level
180 filter dbms_sql.varchar2_table, --we may have a long stmt, we dont want to constrain this to 4000 characters
181 position number, --used by the aggregation_r object in bsc_aw_load_kpi
182 aggregated varchar2(10), --Y or N
183 zero_aggregated varchar2(10), --Y or N. Use together with zero_code, if zero_code=Y and zero_aggregated=Y then <aggregate zero code>
184 property varchar2(400), --used for seeing if a level is a standalone level
185 level_source varchar2(40) --table or view etc
186 );
187 type level_tb is table of level_r index by pls_integer;
188 --
189 type parent_child_r is record(
190 parent_level varchar2(200),
191 child_level varchar2(200),
192 child_fk varchar2(200),
193 parent_pk varchar2(200)
194 );
195 type parent_child_tb is table of parent_child_r index by pls_integer;
196 type parent_child_tb_tv is table of parent_child_tb index by varchar2(200);
197 --
198 --there will also be an agg map to each dim so that we can agg on the fly. when agg on the fly, we do dim by dim
199 --first explode all dim to the levels to the position where there is rollup data. then rollup one dim. then limit this dim
200 --to the higher value. then rollup the next and so on
201 --not sure if target_limit_cube is reqd. dim_set has dim and target_dim. so will limit cube in target_dim hold the target_limit_cube
202 --the issue with that is std_dim and calendar. we then need to have std_dim and calendar replicated for targets.
203 --aggregate_marker is used in load kpi module. if aggregate_marker is set, it means there was some change to dim hierarchies
204 --necessitating a reagg. used in dim_set.aggregate_marker_program. they are scalar
205 --reset_cube is reqd to mark those dim values that have no childran anymore. when there are no children anymore, AW does not automatically
206 --clear the data. reset_cube will use the same composite as the limit cube
207 --std dim do not have reset bools since they have no agg. also we will not have this for calendar. also , none for targets since there is
208 --no rollup on target
209 type dim_r is record(
210 dim_name varchar2(300),
211 relation_name varchar2(300), --aw relation name, used to create agg maps
212 property varchar2(200), --time vs normal
213 recursive varchar2(10),
214 recursive_norm_hier varchar2(10),--rec dim implemented with normalized hier.
215 multi_level varchar2(10),
216 zero_code varchar2(10), --if any level has zero code, this is set to Y
217 concat varchar2(10),--Y or N. used in QDR when creating the programs
218 levels level_tb, --levels of a dim that the dim set for the kpi has.used in creating agg program
219 parent_child parent_child_tb,
220 limit_cube varchar2(300),
221 reset_cube varchar2(300),
222 limit_cube_composite varchar2(300), --needed for 10g. with "sparse", we cannot acquire parallel locks. will also use for 9i
223 base_value_cube varchar2(200), --holds the base value. used in the creation of the olap table function
224 aggregate_marker varchar2(300),--true or false flag. true if there is hier change in the dim and the cubes need reaggregation
225 level_name_dim varchar2(300),--this is the dim that holds level names
226 agg_map agg_map_r,
227 agg_level number --used in bsc_aw_load_kpi to do limit the levels. with this flag in the dim structure
228 --we can control agg level at a dim level
229 );
230 type dim_tb is table of dim_r index by pls_integer;
231 --
232 --we are going to keep time dim separate from dim_r
233 --from AW perspective, time and normal dim are the same not from bsc perspective
234 type periodicity_r is record(
235 periodicity number,
236 periodicity_type varchar2(40),
237 aw_dim varchar2(300),
238 lowest_level varchar2(10), --Y or N multiple levels can be lowest!!! like month and week
239 missing_level varchar2(10),--Y or N
240 aggregated varchar2(10), --Y or N
241 property varchar2(3000) --used to store current period in aggregation of balance measures
242 );
243 type periodicity_tb is table of periodicity_r index by pls_integer;
244 --
245 type cal_parent_child_r is record(
246 parent number,
247 parent_dim_name varchar2(300),
248 child number,
249 child_dim_name varchar2(300)
250 );
251 type cal_parent_child_tb is table of cal_parent_child_r index by pls_integer;
252 --
253 type calendar_r is record(
254 calendar number,
255 aw_dim varchar2(300),
256 level_name_dim varchar2(300),--the dim that will store the level names
257 end_period_level_name_dim varchar2(300),
258 relation_name varchar2(300),--rel name used to create aggmap
259 denorm_relation_name varchar2(300),--rel used for balance rollup in kpi load programs
260 periodicity periodicity_tb,
261 parent_child cal_parent_child_tb,
262 end_period_relation_name varchar2(300),--this relation is used for balance measures
263 limit_cube varchar2(300), --limit cube for time dim
264 limit_cube_composite varchar2(300), --needed for 10g. with "sparse", we cannot acquire parallel locks. will also use for 9i
265 aggregate_marker varchar2(300),
266 agg_map agg_map_r
267 );
268 --
269 --there are 3 kinds of B->measures. one B for all, B1->m1, B2->m2 or B1->m1,m2 and B2->m1,m2
270 --so with the data source, we also need to know the measures for each data set
271 --therefore dim_set_r will contain a table of data sources
272 --we have table of varchar2 for data source instead of varchar2(20000) because we want to have multiple lines
273 --aw has a limitation of 4000 characters per line. we may run out of 4000 for complex kpi
274 --we need base table info since with RSG, we will have to load by base tables. so for each dataset,
275 --we load only if the dataset contains the base tables specified. the list of base tables is passed as a
276 --parameter to the program. if a kpi itself is being loaded we will pass "ALL"
277 --
278 --levels in base_table_r stores the relevant levels that the base table has. a base table may have more keys than the
279 --dim set. in that case, we group the data across these extra keys we dont need to hold measure info here since
280 --dim_set_r has the measure info
281 --if the base table is at the same level as the lowest level of the dim set, base_table_sql=base table name
282 --if base table level is smaller, base_table_sql= (select ..from base, level where base.fk=level.pk) base_table_name
283 --we also need the measures in case the base table is at a lower level and we need to create the base table sql,
284 --we need to know what measures to pick up from the base table. dim_set.measure will hold the measure names in the dimset
285 --and will be different from what is in the base table
286 type base_table_r is record(
287 base_table_name varchar2(100),
288 levels level_tb, --all dim levels except time
289 feed_levels dbms_sql.varchar2_table,--these are the levels that feed the dimset Y or N
290 level_status dbms_sql.varchar2_table,--skip or correct or lower etc
291 periodicity periodicity_r, --the periodicity at which the base table is
292 projection_table varchar2(100),
293 current_period varchar2(40),--in period.year format. this field can be null
294 table_partition bsc_aw_utility.object_partition_r,
295 base_table_sql dbms_sql.varchar2_table
296 );
297 type base_table_tb is table of base_table_r index by pls_integer;
298 --
299 /*this data structute holds info on partition info for a DS. ie, what PT this DS is loading */
300 type data_source_PT_r is record(
301 partition_template partition_template_r,
302 dim_parent_child parent_child_tb_tv,/*index by dim name. used for rollup in DS sql */
303 cal_parent_child cal_parent_child_tb /*used for rollup in DS sql */
304 );
305 --
306 --data_source_stmt are tables of varchar2 so we can have any number of lines. aw has a limitation of
307 --4000 characters per line
308 --we will have one base table per data source for now
309 --we need to know the dim of the data source since targets at higher levels have the same dim but higher
310 --levels
311 --we need std_dim and calendar because in 10g, for targets, we will have diff program and diff limit cubes for dim and calendar
312 --datasource must know about the measurename dim and the partition dim
313 --note>>>lets assume that in a datasource, all the measures must share a partition template
317 for convinience, we can hold the partition template with a DS. DS has 2 sets of information. B table info, ie info of where data is coming from
314 /*PT belong to dimsets. cubes point to the PT they want to use. DS has measures that its loading. from this, we find the cubes and find
315 the PT. from PT we can get all info like partition dim, hpt_data etc. we assume that all the cubes a DS is loading has a similar PT
316 so each DS deals with one PT
318 target info, info where the data is going to including the dim, measures, PT */
319 type data_source_r is record(
320 dim dim_tb,
321 std_dim dim_tb,
322 calendar calendar_r,
323 measure measure_tb,
324 ds_type varchar2(40),--initial or inc
325 data_source_stmt_type dbms_sql.varchar2_table, --dimension=<dim> or measure=<measure> or limit cube=<dim> or 'partition dim'
326 data_source_stmt dbms_sql.varchar2_table, --(select k1,k2,m1,m2 from B)
327 base_tables base_table_tb,
328 data_source_PT data_source_PT_r,
329 property bsc_aw_utility.property_tb --any misc properties
330 );
331 type data_source_tb is table of data_source_r index by pls_integer;
332 --
333 --in s views we need to know what the levels are for each s view. in dim set we are not
334 --holding this relation. in dim set, we do not know how the dim and their levels are related to
335 --the s views. so with each s view, we need to know the level
336 --we need to hold dim info also to get base value cube
337 type s_view_r is record(
338 id varchar2(20),--used to uniquely create a type
339 s_view varchar2(100),
340 dim dim_tb,
341 type_name varchar2(100), -- the name of the olap type for the view
342 type_table_name varchar2(100) -- the name of the olap table type for the view
343 );
344 type s_view_tb is table of s_view_r index by pls_integer;
345 --
346 type load_program_r is record(
347 program_name varchar2(200),
348 program_type varchar2(40),
349 ds_base_tables varchar2(4000)
350 );
351 --
352 /*
353 we have a table of data sources because we can have multiple base tables feeding the cubes. B1->m1, B2-> m2,m3 etc
354 both actuals and targets have the same dimensions, so thay can use the same composite, however, they can have different
355 levels, the targets may have lesser number of levels
356 we create actuals and targets with the all the levels of the dim set. targets usually only have a subset. we control this
357 when we aggregate target cubes. we limit the levels to the levels the target cubes have
358 target_dim.levels(1) will be the lowest level in a any dim for the target
359 filter is at a dim level in the dim set. its applicable to all data sources
360 calendar is at kpi level. but just for uniformity sake,we hold it al dim set level
361 with calendar, when we talk of dim, we are never talking about periodicities
362 if forecast_method is not null, then we use AW to calculate forecast
363 forecast will be Y even in the case where the B tables have the projections. this is used by aggregations to
364 decide how to handle forecast data
365 target cubes in 10g have diff composites. so we can run their load in parallel.
366 there are no separate programs for targets. but note that the base tables for targets are different
367 base dim set will be useful for target dimsets, for regular dimsets, this is will be null.
368 in BSCAWLKB.pls, we have to see the base dim set for a target dimset. only then can we copy data from target cubes to actual cubes
369 forecast is at measure level.
370 not sure the idea to have columns like datacube_design, partitioned, compressed is a good one. This is essentially duplicating the information
371 as the structures get more complicated, we may need to obsolete them
372 */
373 type dim_set_r is record(
374 dim_set varchar2(100),
375 dim_set_name varchar2(300), --used in bsc_olap_object metadata
376 dim_set_type varchar2(100), --actual or target
377 base_dim_set varchar2(300),
378 targets_higher_levels varchar2(10),--we hold at kpi level and dimset level
379 dim dim_tb,
380 calendar calendar_r,
381 std_dim dim_tb,--these are TYPE and PROJECTION
382 measure measure_tb,
383 s_view s_view_tb,--regular MV
384 z_s_view s_view_tb,--the ZMV
385 data_source data_source_tb,
386 inc_data_source data_source_tb,
387 initial_load_program load_program_r,
388 inc_load_program load_program_r,
389 initial_load_program_parallel load_program_r, --10g, used to load measures in parallel, either per measure or per partition
390 inc_load_program_parallel load_program_r,
391 LB_resync_program varchar2(300),--needed when we have partitions
392 aggregate_marker_program varchar2(300),--used with aggregate_marker to set the limit cubes if dim hier have changed
393 aggmap_operator aggmap_operator_r,--this contains opvar argsvar etc. shared across aggmaps
394 agg_map agg_map_r, --for normal measures
395 agg_map_notime agg_map_r, --for avg measures
396 master_partition_template partition_template_tb,--holds the master PT info. partition_template will make copy from here
397 --physical implementation parameters
398 partition_template partition_template_tb,
399 composite composite_tb,
400 cube_set cube_set_tb,
401 --add properties for the dimset
402 measurename_dim varchar2(200), --this is for the datacube design
403 partition_dim varchar2(200),
404 partition_type varchar2(40),
405 cube_design varchar2(40), --datacube or single composite or multiple composite
406 number_partitions number, --if 0, no partitions
407 compressed varchar2(10), --Y or N
408 pre_calculated varchar2(10), --higher level aggregates from B tables
409 property bsc_aw_utility.property_tb
410 );
411 type dim_set_tb is table of dim_set_r index by pls_integer;
412 --
413 type kpi_r is record(
414 kpi varchar2(100),
415 parent_kpi varchar2(100),
416 calendar varchar2(20),
417 dim_set dim_set_tb, --for actuals
418 target_dim_set dim_set_tb --for targets only. if dimset(i) has target, it will be in target_dim_set(i). note the same i
419 );
420 type kpi_tb is table of kpi_r index by pls_integer;
421 --
422 /*general purpose globals used in the code */
426 g_balance_last_value_prop constant varchar2(40):='BALANCE LAST VALUE';
423 g_period_temp constant varchar2(40):='period_temp';
424 g_year_temp constant varchar2(40):='year_temp';
425 g_balance_end_period_prop constant varchar2(40):='BALANCE';
427 g_balance_loaded_column_prop constant varchar2(40):='BALANCE LOADED COLUMN';
428 --
429 --procedures-------------------------------------------------------
430 procedure drop_kpi_objects(p_kpi varchar2);
431 procedure create_kpi(p_kpi_list dbms_sql.varchar2_table);
432 procedure create_kpi(p_kpi in out nocopy kpi_r);
433 procedure get_dim_set_dim_properties(p_kpi varchar2,p_dim_set in out nocopy dim_set_r);
434 procedure get_dim_set_calendar(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r);
435 procedure get_dim_set_dims(p_kpi varchar2,p_dim_set in out nocopy dim_set_r);
436 procedure set_dim_and_level(
437 p_dim_set in out nocopy dim_set_r,
438 p_dim varchar2,
439 p_level varchar2,
440 p_mo_dim_group varchar2,
441 p_skip_level varchar2
442 );
443 procedure set_dim_order(p_dim_set in out nocopy dim_set_r);
444 procedure get_dim_set_properties(p_kpi varchar2,p_dim_set in out nocopy dim_set_r);
445 procedure get_dim_set_measures(p_kpi varchar2,p_dim_set in out nocopy dim_set_r);
446 procedure get_dim_set_targets(p_kpi in out nocopy kpi_r);
447 procedure get_s_views(p_kpi varchar2,p_dim_set in out nocopy dim_set_r);
448 procedure get_dim_set_data_source(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r);
449 procedure create_data_source_sql(
450 p_kpi varchar2,
451 p_dim_set dim_set_r,
452 p_data_source in out nocopy data_source_r
453 );
454 procedure create_base_table_sql(
455 p_dim_set dim_set_r,
456 p_data_source data_source_r,
457 p_base_table in out nocopy base_table_r
458 );
459 function is_level_in_dim(p_dim dim_r,p_level varchar2) return boolean;
460 function get_dim_given_dim_name(p_dim varchar2,p_dim_set dim_set_r) return dim_r;
461 function is_dim_in_dimset(p_dim_set dim_set_r,p_dim varchar2) return boolean;
462 procedure create_aw_object_names(p_kpi in out nocopy kpi_r);
463 procedure create_kpi_objects(p_kpi in out nocopy kpi_r);
464 procedure create_kpi_objects(
465 p_kpi kpi_r,
466 p_dim_set in out nocopy dim_set_r);
467 procedure create_composite(
468 p_kpi kpi_r,
469 p_dim_set in out nocopy dim_set_r);
470 procedure create_cube(
471 p_kpi kpi_r,
472 p_dim_set in out nocopy dim_set_r);
473 procedure create_kpi_program(p_kpi in out nocopy kpi_r);
474 procedure create_kpi_program(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r,p_mode varchar2);
475 procedure create_kpi_program(p_kpi kpi_r,p_dim_set dim_set_r,p_data_source data_source_r);
476 procedure create_kpi_view(p_kpi in out nocopy kpi_r);
477 procedure create_kpi_view(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r);
478 procedure create_db_type(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r);
479 procedure dmp_kpi(p_kpi kpi_r);
480 procedure dmp_dimset(p_dim_set dim_set_r);
481 procedure dmp_dim(p_dim dim_r);
482 procedure dmp_data_source(p_data_source data_source_tb);
483 procedure dmp_data_source(p_data_source data_source_r);
484 procedure create_agg_map(
485 p_kpi kpi_r,
486 p_dim_set in out nocopy dim_set_r);
487 function check_kpi_create(p_kpi varchar2) return boolean;
488 procedure get_dim_set_std_dims(
489 p_kpi varchar2,
490 p_dim_set in out nocopy dim_set_r);
491 procedure get_dim_set_std_dim_type(
492 p_kpi varchar2,
493 p_dim in out nocopy dim_r);
494 procedure get_dim_set_std_dim_projection(
495 p_kpi varchar2,
496 p_dim in out nocopy dim_r);
497 procedure dmp_agg_map(p_agg_map agg_map_r) ;
498 procedure create_agg_map(p_dim_set dim_set_r,p_agg_map in out nocopy agg_map_r);
499 procedure dmp_calendar(p_calendar calendar_r);
500 procedure create_aggmap_operators(p_kpi kpi_r,p_dim_set dim_set_r);
501 procedure make_agg_formula(
502 p_kpi in out nocopy kpi_r
503 );
504 procedure make_agg_formula(
505 p_dim_set in out nocopy dim_set_r
506 ) ;
507 procedure make_agg_formula(
508 p_measure in out nocopy measure_r,
509 p_all_measures measure_tb,
510 p_dim_set dim_set_r
511 );
512 procedure set_dim_level_positions(p_kpi varchar2,p_dim_set in out nocopy dim_set_r);
513 procedure set_dim_level_positions(p_dim in out nocopy dim_r) ;
514 procedure set_dim_agg_level(p_kpi in out nocopy kpi_r);
515 procedure init_agg_level(p_dim_set in out nocopy dim_set_r);
516 procedure dmp_measure(p_measure measure_r) ;
517 procedure create_kpi_aw(p_kpi in out nocopy kpi_r);
518 procedure drop_kpi_objects_aw(p_kpi varchar2);
519 procedure get_dim_properties(p_kpi varchar2,p_dim in out nocopy dim_r);
520 procedure create_db_type(p_kpi varchar2,p_dim_set dim_set_r,p_s_view in out nocopy s_view_r) ;
521 procedure create_kpi_view(p_kpi kpi_r,p_dim_set dim_set_r,p_s_view s_view_r,p_type varchar2);
522 procedure create_missing_dim_levels(p_kpi varchar2,p_dim_set in out nocopy dim_set_r);
523 procedure get_dim_mo_dim_groups(
524 p_dim dim_r,
525 p_distict_mo_dim_groups out nocopy dbms_sql.varchar2_table);
526 procedure identify_standalone_levels(
527 p_kpi varchar2,
528 p_dim_set in out nocopy dim_set_r);
529 procedure create_dmp_program(
530 p_kpi varchar2,
531 p_dimset varchar2,
532 p_dim_levels dbms_sql.varchar2_table,
533 p_name varchar2,
534 p_table_name varchar2);
535 procedure get_missing_periodicity(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r);
536 function get_periodicity_r(p_periodicity periodicity_tb,p_periodicity_dim varchar2) return periodicity_r;
537 function get_periodicity_r(p_periodicity periodicity_tb,p_periodicity_id number) return periodicity_r;
538 function is_filter_in_data_source(
539 p_data_source data_source_r
540 )return varchar2;
541 procedure dmp_agg_map_operator(p_agg_map_operator aggmap_operator_r);
542 procedure drop_kpi_objects_relational(p_kpi varchar2);
543 procedure create_aggregate_marker_pgm(
544 p_kpi kpi_r,
545 p_dim_set dim_set_r
546 );
547 procedure create_dim_match_header(p_data_source data_source_r);
548 procedure create_limit_cube_tail(p_data_source data_source_r);
549 procedure create_kpi_program_parallel(p_kpi in out nocopy kpi_r);
550 procedure create_kpi_program_cube(
551 p_kpi kpi_r,
552 p_dim_set in out nocopy dim_set_r,
553 p_mode varchar2);
554 procedure create_kpi_program_cube(
555 p_kpi kpi_r,
556 p_dim_set dim_set_r,
557 p_cube_set cube_set_r,
558 p_measures measure_tb,
559 p_data_source data_source_r);
560 procedure create_kpi_program_limit_cube(
561 p_kpi kpi_r,
562 p_dim_set dim_set_r,
563 p_data_source data_source_r);
564 function get_comp_dimensions(p_dim_set in out nocopy dim_set_r) return dbms_sql.varchar2_table;
565 procedure create_cube(
566 p_kpi kpi_r,
567 p_dim_set dim_set_r,
568 p_cube cube_r);
569 procedure create_measure_dim(
570 p_kpi kpi_r,
571 p_dim_set in out nocopy dim_set_r);
572 procedure dmp_partition_template(p_partition_template partition_template_r);
573 procedure dmp_partition(p_partition partition_r);
574 procedure dmp_axis(p_axis axis_r);
575 procedure dmp_composite(p_composite composite_r);
576 procedure dmp_cube_set(p_cube_set cube_set_r);
577 procedure dmp_cube(p_cube cube_r);
578 procedure create_PT_comp_names(p_kpi varchar2,p_dimset in out nocopy dim_set_r);
579 procedure create_partition_template(
580 p_kpi kpi_r,
581 p_dim_set in out nocopy dim_set_r) ;
582 procedure create_partition_template(
583 p_kpi kpi_r,
584 p_dim_set in out nocopy dim_set_r,
585 p_partition_template in out nocopy partition_template_r
586 );
587 procedure create_kpi_program_partition(
588 p_kpi kpi_r,
589 p_dim_set in out nocopy dim_set_r,
590 p_mode varchar2) ;
591 procedure create_kpi_program_partition(
592 p_kpi kpi_r,
593 template_partition partition_r,
594 p_dim_set in out nocopy dim_set_r,
595 p_data_source data_source_r,
596 partition_index number);
597 function get_cube_set_r(p_cube_name varchar2,p_dimset dim_set_r) return cube_set_r;
598 function get_composite_r(p_composite_name varchar2,p_dimset dim_set_r) return composite_r;
599 function get_partition_template_r(p_partition_template varchar2,p_dimset dim_set_r) return partition_template_r;
600 function get_cube_axis(p_cube_name varchar2,p_dimset dim_set_r,p_axis_type varchar2) return varchar2 ;
601 procedure set_dimset_partition_info(p_kpi varchar2,p_actual_dim_set in out nocopy dim_set_r,p_target_dim_set in out nocopy dim_set_r) ;
602 procedure get_measures_for_cube(
603 p_cube varchar2,
604 p_dim_set dim_set_r,
605 p_measures out nocopy measure_tb);
606 function get_cube_set_for_measure(p_measure varchar2,p_dim_set dim_set_r) return cube_set_r;
607 function get_cube_pt_comp(p_cube_name varchar2,p_dimset dim_set_r,p_type out nocopy varchar2) return varchar2;
608 procedure create_measure_formula(
609 p_kpi kpi_r,
610 p_dim_set in out nocopy dim_set_r);
611 procedure create_kpi_program_LB_resync(
612 p_kpi kpi_r,
613 p_dim_set in out nocopy dim_set_r
614 );
615 procedure create_dimset_data_source_sql(
616 p_kpi varchar2,
617 p_dim_set dim_set_r,
618 p_data_source in out nocopy data_source_tb
619 );
620 procedure create_dimset_data_source_sql(
621 p_kpi varchar2,
622 p_dim_set dim_set_r,
623 p_data_source data_source_tb,
624 p_new_data_source in out nocopy data_source_r
625 );
626 procedure set_program_property(p_load_program in out nocopy load_program_r,p_data_source data_source_tb);
627 function get_kpi_level_dim_r(
628 p_dim_set dim_set_r,
629 p_level varchar2) return dim_r;
630 function get_dim_level_r(
631 p_dim dim_r,
632 p_level varchar2) return level_r;
633 procedure check_compressed_composite(p_actual_dim_set in out nocopy dim_set_r,p_target_dim_set in out nocopy dim_set_r);
634 function check_countvar_cube_needed(p_dimset dim_set_r) return boolean;
635 procedure set_DS_dim_levels(p_kpi varchar2,p_dim_set in out nocopy dim_set_r,p_data_source in out nocopy data_source_r);
636 procedure set_DS_dim_levels(p_kpi varchar2,p_dim_set dim_set_r,p_data_source data_source_r,p_base_table in out nocopy base_table_r);
637 function get_dim_given_dim_name(p_dim varchar2,p_dim_t dim_tb) return dim_r;
638 function is_dimset_precalculated(p_dim_set dim_set_r) return boolean;
639 procedure set_dim_set_properties(p_dim_set in out nocopy dim_set_r);
640 procedure set_dim_set_properties(p_kpi in out nocopy kpi_r);
641 procedure dmp_table_partition(p_partition bsc_aw_utility.object_partition_r);
642 procedure dmp_partition_set(p_partition_set bsc_aw_utility.partition_set_r);
643 procedure set_dim_set_data_source(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r);
644 procedure get_relevant_cal_hier(p_periodicity periodicity_tb,p_pc cal_parent_child_tb,p_relevant_pc out nocopy cal_parent_child_tb);
645 procedure get_upper_cal_hier(p_pc cal_parent_child_tb,p_child varchar2,p_upper_hier out nocopy cal_parent_child_tb);
646 procedure create_balance_aggregation(p_dim_set dim_set_r, p_data_source data_source_r,p_measures measure_tb);
647 procedure create_temp_variables(p_dim_set dim_set_r,p_data_source data_source_r);
648 function is_balance_last_value_in_DS(p_data_source data_source_r) return varchar2;
649 procedure upgrade(p_new_version number,p_old_version number);
650 procedure set_DS_dim_levels(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r);
651 function is_higher_level_preloaded(p_dim_set dim_set_r) return boolean;
652 function is_higher_period_preloaded(p_dim_set dim_set_r) return boolean;
653 procedure set_dim_agg_level(p_dim_set in out nocopy dim_set_r);
654 procedure set_dim_agg_level(p_dim in out nocopy dim_r);
655 procedure set_calendar_agg_level(p_kpi in out nocopy kpi_r);
656 procedure set_calendar_agg_level(p_dim_set in out nocopy dim_set_r);
657 procedure dmp_hpt_data(p_hpt_data hpt_data_r);
658 function get_DS_partition_template(p_dim_set dim_set_r,p_data_source data_source_r) return partition_template_r;
659 procedure load_master_PT(p_actual_dim_set in out nocopy dim_set_r,p_target_dim_set in out nocopy dim_set_r);
660 function check_partition_possible(p_actual_dim_set in out nocopy dim_set_r,p_target_dim_set in out nocopy dim_set_r) return boolean;
661 procedure set_pt_type_count(p_dim_set in out nocopy dim_set_r);
662 procedure set_master_PT(p_dim_set in out nocopy dim_set_r);
663 function set_PT_hash_dimensions(p_dim_set dim_set_r,p_partition_template in out nocopy partition_template_r) return boolean;
664 function get_partition_count return number;
665 procedure set_data_source_PT(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r);
666 procedure set_data_source_PT(p_dim_set dim_set_r,p_data_source in out nocopy data_source_r);
667 procedure set_DS_hpt_rollup_data(p_dim_set dim_set_r,p_data_source in out nocopy data_source_r);
668 procedure set_DS_hpt_rollup_data(p_DS_dim dim_tb,p_hpt_data hpt_data_r,p_data_source in out nocopy data_source_r);
669 procedure set_DS_hpt_rollup_data(p_calendar calendar_r,p_DS_calendar calendar_r,p_hpt_data hpt_data_r,p_data_source in out nocopy data_source_r);
670 function get_DS_PT_hash_stmt(p_dim_set dim_set_r,p_data_source data_source_r)return varchar2;
671 procedure set_PT_dim_aggregated(p_dim_set in out nocopy dim_set_r,p_partition_template partition_template_r);
672 procedure set_PT_dim_aggregated(p_dim in out nocopy dim_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table);
673 procedure set_PT_calendar_aggregated(p_dim_set in out nocopy dim_set_r,p_partition_template partition_template_r);
674 procedure set_PT_calendar_aggregated(p_calendar in out nocopy calendar_r,p_dim_set dim_set_r,p_levels dbms_sql.varchar2_table);
675 function is_dim_aggregated(p_dim dim_r) return boolean;
676 function is_calendar_aggregated(p_calendar calendar_r) return boolean;
677 function get_projection_dim(p_dim_set dim_set_r) return varchar2;
678 procedure create_cube(p_kpi kpi_r,p_dim_set dim_set_r,p_cube cube_r,p_cube_axis axis_tb);
679 function make_display_cube_axis(p_dim_set dim_set_r,p_cube cube_r) return axis_tb;
680 procedure set_sql_aggregations(p_kpi in out nocopy kpi_r,p_action varchar2);
681 procedure set_sql_aggregations(p_kpi kpi_r,p_dim_set in out nocopy dim_set_r,p_action varchar2);
682 procedure reset_PT_template(p_partition_template in out nocopy partition_template_r);
683 procedure set_calendar_agg_level(p_dim_set in out nocopy dim_set_r,p_target_dim_set dim_set_r);
684 function is_display_cube_required(p_dim_set dim_set_r,p_cube varchar2) return boolean;
685 function is_display_cube_possible(p_dim_set dim_set_r) return boolean;
686 function get_dim_set_lowest_periodicity(p_dim_set dim_set_r) return periodicity_tb;
687 --procedures-------------------------------------------------------
688 procedure init_all ;
689 procedure log(p_message varchar2);
690 procedure log_n(p_message varchar2);
691 -------------------------------------------------------------------
692
693 END BSC_AW_ADAPTER_KPI;
[Home] [Help]