OBIEE 11g integration with Microsoft Analysis Services Cubes - Aggregation Rules and Count Distinct

As we know, OBIEE has great federation capabilities, and they are used to federate multiple environments. Organizations sometimes have a mix of disparate systems e.g. Oracle Databases, SQL Servers, MSAS/Essbase Cubes etc, and it is always expected to federate them all for a unified view of the data.

Federation itself is a different topic and conformed dimensions play a vital role over there. Its the idea of a different post. For this post, I want to have a look on the Microsoft Analysis Services cubes' inbuilt aggregations and how to leverage it.

My environment consists of:
1. OBIEE 11g
2. Microsoft Analysis Services 2008

I used AdventureWorks2008 database, which is a sample database for SQL Server/MSAS. I have built a basic cube with two dimensions and a measure for 'Customer Count'. The aggregation rule to build this measure in MSAS is 'DistinctCount'. It is shown below:

As we can see in the above figure, the measure 'Customer Count' is of the nature of  'COUNT DISTINCT' and so is non-additive. The lowest level for which data is stored in the cube is Date.

I imported the cube in the physical layer of the RPD. The measure 'Customer Count' is available with both the hierarchies. Following is the property of this physical cube column:

As we can see, the Aggregation Rule set in the physical layer of RPD is 'External Aggregation'.
'External Aggregation' pushes all the aggregations to be performed to the cube, and doesn't aggregate data in OBIEE. Sometimes there are doubts for this Aggregation Rule strategy, whether to set it to match with the cube (in this case COUNT DISTINCT) or leave it to External Aggregation etc...

The rule of thumb is - Push as much as possible to the Cube, especially non-additive or semi-additive measures. The cubes (either MSAS or Essbase) are created with aggregated data. The technology behind these products allows them to address a particular cell of the cube quickly.

For non-additive measures, we can aggregate data in OBIEE only when we have the lowest level of details available. In this example it would be the customer account numbers, which are eventually not available. So putting a COUNT DISTINCT over this measure in OBIEE would do nothing, but will return me COUNT DISTINCT (data in the cells). And this would result in erroneous data.

So I'll leave the aggregation rule to 'External Aggregation' in physical as well as logical layer of OBIEE.

I tried to create an analysis with Year and the Customer Count, which seems absolutely fine to me:

So we can develop the reports with this approach, we can drill down in hierarchies etc. But its bit tricky to use the CASE STATEMENTS when we use 'External Aggregation' as the aggregation rule in OBIEE.

CASE STATEMENTS are not yet function shipped to back end Cubes in MSAS.

This means that in any of the report if I'll try to aggregate data (CASE STATEMENTS belong to Custom Aggregations class) explicitly, then I am going to get function ship error.

Below is a snippet of this error -

//

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 42015] Cannot function ship the following expression: AggrExternal(Adventure Works DW2008.Customer Count by [ case when Adventure Works DW2008.English Month Name in ([ 'February 2004', 'January 2004', 'March 2004'] ) then 'Com months' else Adventure Works DW2008.English Month Name end ] ). (HY000)

//

So what I tried to do is to combine Jan, Feb, Mar 2004 in a case statement and to pass it to the Cube. OBIEE is unable to function ship this to the MSAS cube. Future releases of OBIEE are expected to address the custom aggregations, but till then there is a workaround.

How to achieve the custom aggregations by passing MDX functions to MSAS is the topic of my next post. Thanks.


The next post can be found here.