AW Loading/Solving with OWB 10gR2

This note details how AW cubes are created by OWB 10gR2, it covers how cubes can be loaded with data, solved and how large cubes can be loaded using the parallel solve capabilities of the AW in Oracle 10g.


The AW engine in 10g has an api (AWXML) that allows both client-server style usage (as in AWM) and batch like usage using java-stored procedures (this is the OWB generate/deploy model). We'll cover some details for how the AWXML is generated in order to understand facets of building an AW solution with OWB.


How is the AWXML map generated for ragged fact data in OWB?
If the natural keys option is defined for the dimension, then any cube map using this dimension will have mapping code (AWXML mapping code) generated by OWB for handling ragged fact data. The source column for the cube dimension level is actually mapped to every parent level also; this enables ragged fact data to be loaded. If the generate keys options is defined then when you define a map to load data at this level then (because of the generate surrogate keys behavior) you will be loading cube dimension members for this level, and only this level.


How are aggregations defined?
OWB allows reuse of existing dimensions without necessarily defining additional hierarchies. Aggregations are generated based on the cube dimension level references defined within OWB. Only hierarchies where the cube dimension level is a member will be included in the aggregation, and OWB goes a step further. If the cube dimension level referenced is a non-leaf level of the hierarchy, then levels lower in the hierarchy will be excluded when the cube or measures are solved. So if you have a budget cube and an actual sales cube then they can share the same dimension definitions without additional dimension hierarchy definitions.


So what is AW auto-solving, what is it all about?
An important attribute of the OLAP AWXML engine is the AW auto-solve facility, this will happen when:



·  Cube is compressed, or
·  Cube is non-compressed - all measures are set to �Solve=Yes� and the dimension levels that the cube refers to are at the leaf level of all hierarchies the level is a member of.
· Auto-solved when OWB map is executed


Incremental Aggregation of cube is dependent on auto-solve (load and aggregate in one operation). Incremental aggregation is a property of the cube operator in the mapping.


OWB can generate non-autosolved cubes;



· OWB map optionally solves these cubes
· OWB transformation can be used to solve
· Cube has to be
    Non-compressed plus some measures are set to �Solve=No� or the dimension levels that the cube refers to are non-leaf levels of a hierarchy the level is a member of.
· Incremental Aggregation is only for Auto-Solved cubes.


Solve one measure and not another / load and solve?
With compressed cube use the NOAGG operation on the cube dimension aggregation tab. With non-compressed cubes it is possible to solve one measure and not another as follows. You will need the latest database patch also for this (10.2.0.2, bug 4550247 has details for 10.1 patch) for it to work properly.  The options defined on cube measures for solve indicate which measures will be included in the primary solve. The solve indicator on the cube operator in the map however indicates whether this solve will be executed or not. So the map can just load data or load and solve the data.


So how do I solve independent of load?
There is a transformation function for executing solves, so solves can be scheduled independently from loading. It is also possible to solve measures independently from each other using this function (WB_OLAP_AW_PRECOMPUTE).
For example the following will solve the measure SALES in the SALES_CUBE



declare
  rslt varchar2(4000);
begin
�  rslt := wb_olap_aw_precompute(�MART',�SALES_CUBE','SALES');
�end;
/


This routine has parameters for parallel solve, max number of job queues etc. If parallel solve then an ASYNCHRONOUS solve job is launched and the master job ID returned via the functions return value.



What calculation plans are generated by OWB?
The following calculation plans are generated by OWB



· Calculation plan generated for the cube
· Calculation plan generated for each stored measure


This allows measures to be solved individually after a data load, or entire cubes to be solved. The actual calculation plan can also exclude levels based on OWB metadata.



How do I parallel solve, what is the degree of parallelism?
This is a configuration parameter of the cube mapping. The cube operator has a property �Allow Parallel Solve� and also a property for the �Max Job Queues Allocated�, these 2 properties control whether parallel solve is performed and also the size of the job pool (the default is to let the AWXML engine figure it out).


What is the output of an OWB AW map?
There is an output parameter AW_EXECUTE_RESULT from the mapping. When the map is executed using parallel solve, this output parameter will contain the Oracle job ID � this is ASYNCHORONOUS. The user must then, or can then use the dictionary views below to determine when the job is complete and what to do next;



all_scheduler_jobs
 (Scheduled Jobs)
all_scheduler_job_run_details
 (Job History)
all_scheduler_running_jobs
 (Running Jobs)


If the map is not executed using parallel solve, the AW_EXECUTE_RESULT output parameter will return the �Successful� tag or an error. For error view the OLAPSYS.XML_LOAD_LOG table.


Hopefully this provides some background to the different kinds of load/solve capabilities that can be incorporated into OWB.

Comments:

hi,
is it possible to send me a sample for create a cube have aggregate sum and count for example count(amount) and sum(amount) as through their dimensions.
regards,

Posted by guest on December 31, 2011 at 09:26 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today