Pages

Wednesday, October 31, 2007

Optimising Aggregations in OLAP

I have been investigating two very useful interesting, and possibly useful, procedures this week. They are part of the DBMS_AW package and allow you to tune your aggregation process. One of the many questions that get asked during OLAP projects is: which levels to precompute and which levels to compute at query time? Most people seem to think you must pre-compute all levels across all their dimensions. Well, the flip side of this approach is the data load and aggregations stage can take more time than you expected and possibly have available. So is it possible to balance the time taken for the data load/aggregation and pre-solving as much as possible to maintain query performance? As part of the DBMS_AW package there are two procedures that can help you optimise this aggregation process by identifying the most expensive members within a dimension. The two packages are:
  • ADVISE_CUBE
  • ADVISE_REL
Both allow you to define a percentage value to precompute as part of the aggregation step that forms the normal build process. In 11g this is all included directly within AWM, but for those customers using 10g here is a quick overview of how to use these procedures.


Advise_Cube
The ADVISE_CUBE procedure helps you determine how to preaggregate a standard form cube in an analytic workspace. The procedure takes two arguments:
  • Aggmap_name:The name of an aggmap associated with the cube.
  • Precompute_percentage: The percentage of the cube's data to preaggregate. The default is 20%.
Each RELATION statement in the aggmap must have a pre-compute clause containing a valueset. ADVISE_CUBE returns a list of dimension members in each valueset. If the valueset is not empty, ADVISE_CUBE deletes its contents before adding new values.

The aggmap must have a pre-compute clause in each of its RELATION statements. The precompute clause must consist of a valueset. Based on the precompute percentage that you specify, ADVISE_CUBE returns a set of dimension members in each valueset.

Now I tried this using the common schema and ran into a few problems in getting the correct results. For each measure within a cube there is a formula that refers to an AGGMAP object, as shown below where the aggmap is OBJ1123208571

DEFINE SALES_PRT_TOPFRML FORMULA DECIMAL
EQ aggregate(this_aw!SALES_PRT_TOPVAR using this_aw!OBJ1124208571)

The aggmap looks like this:

DEFINE OBJ1124208571 AGGMAP
AGGMAP
RELATION this_aw!TIME_PARENTREL(this_aw!TIME_AGGRHIER_VSET) PRECOMPUTE(this_aw!TIME_AGGRDIM_VSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT NO
RELATION this_aw!CHANNELS_PARENTREL(this_aw!CHANNELS_AGGRHIER_VSET) PRECOMPUTE(this_aw!CHANNELS_AGGRDIM_VSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT NO
RELATION this_aw!GEOGRAPHIES_PARENTREL(this_aw!GEOGRAPHIES_AGGRHIER_VSET) PRECOMPUTE(this_aw!GEOGRAPHIES_AGGRDIM_VSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT NO
RELATION this_aw!PRODUCTS_PARENTREL(this_aw!PRODUCTS_AGGRHIER_VSET) PRECOMPUTE(this_aw!PRODUCTS_AGGRDIM_VSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT NO
MODEL this_aw!SALES_OBJ1124208571_TIME_AWXMLMODEL PRECOMPUTE(NA)
MODEL this_aw!SALES_OBJ1124208571_CHANNELS_AWXMLMODEL PRECOMPUTE(NA)
MODEL this_aw!SALES_OBJ1124208571_GEOGRAPHIES_AWXMLMODEL PRECOMPUTE(NA)
MODEL this_aw!SALES_OBJ1124208571_PRODUCTS_AWXMLMODEL PRECOMPUTE(NA)
AGGINDEX NO
CACHE SESSION
END

In the documentation the example shows a much simpler AGGMAP as follows:

DEFINE UNITS_AGG AGGMAP
RELATION product_parentrel PRECOMPUTE (prodvals)
RELATION time_parentrel PRECOMPUTE (timevals)

Executing the ADVISE_CUBE procedure against the standard form AGGMAP does not generate an error but it does not generate a result either. The only way I could get this to work was to create another AGGMAP using a different set of valusets as follows:

DEFINE SALES_REVENUE_AGGMAP AGGMAP
AGGMAP
RELATION TIME_PARENTREL PRECOMPUTE (TIME_VALS)
RELATION CHANNELS_PARENTREL PRECOMPUTE (CHANNELS_VALS)
RELATION GEOGRAPHIES_PARENTREL PRECOMPUTE (GEOGRAPHIES_VALS)
RELATION PRODUCTS_PARENTREL PRECOMPUTE (PRODUCTS_VALS)
END

And then copy the results from the new valueset to the master valueset. This is the script I used to execute the procedure:

SQL>set serveroutput on
SQL>exec dbms_aw.execute('aw detach SH_AW')
SQL>exec dbms_aw.execute('aw attach SH_AW RW')
SQL>exec dbms_aw.advise_cube ('SALES_REVENUE_AGGMAP', 40)
SQL>exec dbms_aw.execute('status PRODUCTS_VALS')
SQL>exec dbms_aw.execute('status GEOGRAPHIES_VALS')
SQL>exec dbms_aw.execute('status CHANNELS_VALS')
SQL>exec dbms_aw.execute('status TIME_VALS')
SQL>exec dbms_aw.execute('limit PRODUCT_AGGRDIM_VSET to PRODUCTS_VALS')
SQL>exec dbms_aw.execute('limit TIME_AGGRDIM_VSET to TIME_VALS')
SQL>exec dbms_aw.execute('limit CHANNELS_AGGRDIM_VSET to CHANNELS_VALS')
SQL>exec dbms_aw.execute('limit GEOGRAPHIES_AGGRDIM_VSET to GEOGRAPHIES_VALS')
SQL>exec dbms_aw.execute('update;commit;aw detach SH_AW')

I am not sure how this works, in terms of computing a cost for a specific member(s) within a dimension. Things are a little cleared with the other package, Advise_Rel.

Advise_Rel
I had a lot more success with the ADIVISE_REL procedure. This works on a specified dimension and can use the existing the valuesets referenced by the standard form aggmap. The procedure takes three arguments:
  • Family_relation_name: The name of a family relation, which specifies a dimension and the hierarchical relationships between the dimension members.
  • Valueset_name: The name of a valueset to contain the results of the procedure. The valueset must be defined from the dimension in the family relation. If the valueset is not empty, ADVISE_REL deletes its contents before adding new values.
  • Precompute_percentage: A percentage of the dimension to preaggregate. The default is 20%.
The valueset must be based on the dimension to be analyzed and in this case can be the valueset listed in the standard form aggmap. Based on the pre-compute percentage that you specify, ADVISE_REL returns a set of dimension members into the valueset specified. The values to a pre-compute are selected on the cost of drilling on a parent member. The more children a parent has the longer it will take to compute at query time.

This is the script I used to execute the procedure:

SET SERVEROUTPUT ON
spool advise_dims.log
EXEC dbms_aw.execute('aw attach SH_AW rw');
EXEC dbms_aw.advise_rel('PRODUCT_PARENTREL', 'PRODUCT_AGGRDIM_VSET', 25);
EXEC dbms_aw.advise_rel('CHANNELS_PARENTREL', 'CHANNELS_AGGRDIM_VSET', 25);
EXEC dbms_aw.advise_rel('GEOGRAPHIES_PARENTREL', 'GEOGRAPHIES_AGGRDIM_VSET', 25);
EXEC dbms_aw.advise_rel('TIME_PARENTREL', 'TIME_AGGRDIM_VSET', 25);
EXEC dbms_aw.execute('show values(PRODUCTS_AGGRDIM_VSET)');
EXEC dbms_aw.execute('show values(GEOGRAPHIES_AGGRDIM_VSET)');
EXEC dbms_aw.execute('show values(CHANNELS_AGGRDIM_VSET)');
EXEC dbms_aw.execute('show values(TIME_AGGRDIM_VSET)');
execute dbms_aw.execute('update;commit;aw detach SH_AW');
spool off
exit

Once this is complete the last step is load data into the cube and the aggregation engine will now use these values to pre-compute specific members.

For more information on these procedures check the OLAP Reference Manual:

http://download.oracle.com/docs/cd/B19306_01/olap.102/b14350/dbms_aw004.htm#CEGJCHHA
http://download-uk.oracle.com/docs/cd/B14117_01/olap.101/b10334/dbms_aw.htm#CEGFHDII

Hope this is useful

Keith

Monday, October 22, 2007

10.1.3.3.1 is now available on OTN

Over the weekend, Oracle Business Intelligence Suite Enterprise Edition, v. 10.1.3.3.1 became available on OTN (that would be the Oracle Technology Network).
You can download the Windows (x86) installable here (1.4GB). Downloads for Linux, HP, Solaris, and IBM are also available - see details at the download page here.
The documentation library is available online here.
And if you care to download the doc library as a zip file for offline viewing, it can be downloaded here (it's a 19MB zip file).

Some of the new features for this release are listed in the "New Features Guide", specifically under the 8 General New Features in Release 10.1.3.3.1 and 22 New Features in Release 10.1.3.3.1 chapters. You will now find support for additional languages in BI EE, specifically Arabic, Thai, and Hebrew.

Happy downloading, upgrading, installing.

Wednesday, October 17, 2007

BI Office in 10.1.3.3 - Secure Image

I don't like the image that appears after I have secured BI charts.

So you don't like the image and want to change it. Change is good. And you can have it.
How?

First a brief recap.
I am talking about Oracle Business Intelligence for Microsoft Office in the 10.1.3.3 release of Oracle Business Intelligence Suite Enterprise Edition.
Given a BI chart (specifically, given a chart that has been inserted into Excel or PowerPoint using the Oracle BI Office Add-in)- as a native Office chart, or as a refreshable image, or as a refreshable Flash chart - you can secure it.

And once secured, this is what appears in place of the chart data:



Refresh it and back appears the chart and the data.

What we want to do is change the image that appears. This image:

And let's say I want to put in its place this image:


To do that, for Excel to begin with, go here:


c:\program files\oracle is obviously where you installed BI Office.
You will see a file named "secure_content.png"
Make a copy of it if you please. And then copy the image of your choice (yeah... we can have the imagination run wild here. But not now) to this folder.


Now go back to Excel. And try the secure operation again.



There you go. You could do the same with PowerPoint too.



Go to this folder:



Replace the default image with this one:


Note that the secure_image.gif file is generated by the Add-in if it is not there - so if you do delete it, then it shouldn't really matter, I think, as it will be regenerated the next time you click the 'Secure Oracle BI Data' button.