Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« Auto Finance - Transforming the Collection Process | Main | Migrate Oracle Hyperion Cloud Applications(PCMCS) on Autopilot!!! Using EPMAutomate »

Replace Query tracked Aggregate views of an ASO cube to another cube and aggregate

Essbase Aggregate storage Database is a Multidimensional Database. Aggregate storage enable dramatic improvements in database aggregation.

Aggregation:
Aggregation is a process of calculating and storing data of aggregate views to enhance retrieval performances.
For an ASO cube we can perform Default aggregation and Query tracking aggregation.
Aggregate view:
Aggregate view is a set of higher level intersections of dimensions in a ASO cube.

Aggregate views could be seen using Design Aggregation Wizard or by a maxl command:
query database AppName.DBName list esxisting views;
AppName is the application name and DBName is the essbase cube name.
Views will appear as below

View ID Viewlevels outline ID
0 0,0,0,1/2 1
2 0,0,2/3,0 1

View ID - is a numeric identification of a view
View levels - combimnation for each aggregate view
Outline ID - is a numeric ID of an aso outline associated with a view.

Query tracking aggregation for Essbase ASO:
Query tracking is the process to track cube usage patterns(aggregate views) and allow for aggregations which will be more efficient to the user's query patterns.
This blog explains how to capture and aggregate the views on a ASO cube using the view file that is tracked on another ASO Cube.

Tracking Queries:
Enable query tracking on the database prior to usage of cube for reporting. It could be done in two approach.
1. In Essbase administration console, select the essbase cube on which you have to track queries, and right click on Database and enable query tracking.
2. Use the maxl command, alter database AppName.DBName enable query_tracking;

Generating view file: 
Force dump the tracked usage patterns (Queries) to a File by giving appropriate file name that is not more than 8 characters length.
This could be done using Design Aggregation Wizard or by a maxl command, execute aggregate selection on database AppName.DBName based on query_data force_dump to view_file SAMPLE;

View file structure:

Number of views
Outline ID
View ID - default level 0 view
View size
View ID1
View size1
View ID2
View size2
.
.
View IDn
View sizen

Consider aggregate view file below:
5
2984637188
0
1.000000000000000
3773467
0.000000383467902
3
0.289642138867758
8
0.686095536512878
11
0.115267836885590

Here in this view file line 1 represents total number of aggregate views (In the above example 5 is total number of views).
Line 2 represent outline Id, line 3 represents default aggregate view (i.e., the view that is created when all the dimensions are kept at lower level), line 4 represents view size.

Add views of one DB to another:
To aggregate / to add views from DB to another, the outline (Total number of Dimensions and the hierarchy) should be same.
Here in this case we have chosen SAMPLE1 and SAMPLE2 both having the same outline structure.
Consider the below view files,

View file of SAMPLE1 cube: SAMPLE1.csc

2
2984637188
0
1.000000000000000
3773467
0.000000383467902

View file of SAMPLE2 cube: SAMPLE2.csc

4
2984899332
0
1.000000000000000
3
0.289642138867758
8
0.686095536512878
11
0.115267836885590

Adding view from one(cube) view file to another(cube):
Retain the Outline ID from SAMPLE1 file, add three views (View ID: 3, 8, and 11) and update the total number of views in the file. Below will be the new view file having the views from both SAMPLE1 and SAMPLE2.
SAMPLE1.csc
5
2984637188
0
1.000000000000000
3773467
0.000000383467902
3
0.289642138867758
8
0.686095536512878
11
0.115267836885590

Aggregating Essbase data base using the newly created view file:
Place the modified view file in the Application folder, and select the view file the view file(modified) for aggregating using Design Aggregation Wizard.
Or use the below maxl command to aggregate
execute aggregate build on database SAMPLE1.SAMPLE1 using view_file SAMPLE1.csc';

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles