OBIEE - Aggregation Rules

Talking of aggregation rules in OBIEE, people uses Sum, Average and Count most commonly. Some of them are not even aware in what scenario the other aggregation rules can be used. In this post I am going to show a real-life business scenario for use of some of the other aggregation rules.


Take a look at following accounting data. There's a fact holding Opening Balance, Debit Amount, Credit Amount and Closing balance for accounts on daily basis. We have a Time dimension with hierarchy Year-Month-Week-Day.





What aggregation rule will apply to "Opening Balance" measure? Ideally if I look at "Wk2", it should give me value $2,359 i.e. opening balance of first day of "Wk2". Similary, if I look at Jan 2010, it should give me value $1,000 i.e opening balance of the month. This cannot be achieved with commonly used Sum or Average aggregation rules.


Some people suggests, we can create Year, Month and Week level aggregate tables and design aggregate navigation. Technically this is right, but what if a new level (Quarter) gets introduce at later point. You will require changes at many places.


Now let see how we can solve this scenario using aggregation rules. On the aggregation tab select "Based on dimnsions" checkbox.

A list will show up with "Other" dimension with "Sum" aggregation for it. Leave this as is. Cilck on "New" button at the bottom of the dailog.

In the dimension list select Time dimension.






In the formula dropdown select formula with aggregation rule of "FIRST".






Defining "FIRST" aggregation formula on Time dimension ensures that, for the non-granular members (such as Year, Month, Week), value of its first granular member is returned.


Why "Sum" aggregation needed for "Other" dimension? The example data above just shows information of one account, but ideally we will have more such accounts and we want to sum up value of opening balance of all account to know the total opening balance at a particular time.


Finally, we can handle closing balance too in similar way by choosing "LAST" aggregation formula.

OBIEE - Schedule Purge and Re-build of cache programmatically

In a standard OBIEE implementation, we generally want to purge and re-build the cache after every ETL run. This post deals with how to programmatically purge and re-build cache and also schedule it.

Purging Cache
OBIEE provides ODBC-extension functions that can be used to purge cache programmatically. These functions can be called using NQCMD commandline tool. There are four different functions that can be used to purge cache.

• SAPurgeAllCache - Purges all cache
• SAPurgeCacheByDatabase - Purges all cache for a specific physical database
• SAPurgeCacheByTable - Purges cache for a specific physical table
• SAPurgeCacheByQuery - Purges cache for a specific query

Here's how to use the command in windows environment.

Create a text file using notepad with name "PurgeCache.txt" and add following line to it. Save the file to local disk (Let's assume to drive C:\)

Call SAPurgeAllCache();

Start command prompt and run following command. Note, there are four parameters in the command. You must replace this with values relavent to your implementation.

-d = ODBC datasource name
-u = Admin user name
-p = Admin Password
-s = sql file

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt

Since we used SAPurgeAllCache function, it will clear all cache on the data source . Refer this link for syntax of other functions.

Re-building Cache
Once the cache is purged, you may want to re-build cache for some of the frequently used reports to avoid delay in response to the users. Re-Building cache requires executing the "logical" SQL query against the data source. We can use NQCMD same way we used for purging cache. Instead of purge function, we have to include logical SQL statements in the text file.

Create a text file using notepad with name "BuildCache.txt" and add logical SQL commands to it. You can include more than one command separated by semicolon(;). Save the file to local disk (Let's assume to drive C:\). Below is example of the text file content.

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."SalesAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."CostAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

Start command prompt and execute the command same way we did for purging the cache.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

Scheduling Purge and Re-build
You may not want to run purge/rebuild cache manually after each ETL run, so you can create a batch file using notepad, "Caching.bat" and add the two command that we ran manually to it. Save the file.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt
NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

We can schedule this file to run automatically using windows scheduling using following steps.

Go to Control Panel and double click "Schduled Tasks"

In the Schduled Tasks window, double click "Add Scheduled Task".

The Scheduled Task Wizard screen appears.
Click Next to continue.

Click Browse button on next screen. Navigate to the C:\Caching.bat and click Open button.
Next, you give the task a name and choose when to run the task.

Name the scheduled task.
Pick a name that will make sense to you later when you want to check the list or make any changes.

Choose when to run the scheduled task.
There are a number of choices as to when you would like to run the task:

Daily – once a day
Weekly – once a week
Monthly – once a month
One time only
When computer starts
When I log in

Click "Next" to continue.
Specify the day and time to run the scheduled task.

Click "Next" to continue once you have made your time selections.
Enter the name and password of the user who is authorized to run the task. Generally Administrator.

Click Next to continue.

Click Finish to complete scheduling the task.

OBIEE Aggregate Navigation (not hitting?)

Often you will find people complaining that they have setup aggregate navigation, but when they run report, OBIEE is not hitting the right fact table.

Common suggestions you will find to resolve this are:

• Go to Content tab of the Logical Table Source and check if Logical Level are set correctly set.
• Go to General tab of Dimension Level and check the value in textbox "Number of elements at this level"

In this post, I am just going to show that even after following above, the query may not hit aggregate table if you are not following modeling practice right.

Let's take following data as example. There are two sales fact tables and a time dimension. DAILY_SALES has day level sales data. WEEKLY_SALES has sales data pre-aggregated at week level.

Here's repository created on top of above physical data. The Logical Level is set correctly for WEEKLY_SALES.


Even the number of elements are set correctly for Week and Day level of Time dimension.




Let's execute following query which fetches sales by week. Since we have setup aggregate navigation, it is expected that the query should fetch data from WEEKLY_SALES table.

SELECT "Time"."WEEK_DESC", "Sales"."SALES_AMT" FROM SALESCATALOG

Now have a look at NQQuery.log. The query didn't hit WEEKLY_SALES table, in fact it is grouping data form DAILY_SALES table instead.


So what went wrong?
Our query has two columns WEEK_DESC and SALES_AMT. If you see the earlier repository screen carefully, you will see that WEEK_DESC attribute is not associated with any level of the Time dimension, so OBIEE assumes it to be most granular level attribute i.e. day level. When we query data by WEEK_DESC, OBIEE goes to fact table with day level granularity (i.e. DAILY_SALES) and fetches the data.

Let's add WEEK_DESC to Week level of the time dimension as shown below and execute the query again.


Now let's see NQQuery.log again. Bingo! it just hit the table we wanted it to.



Alternative approach with Snow-flake:
In the example I have given, the Time dimension is snow-flake, so it is also possible to set the logical level for invidual source table to make aggregate navigation work. Setting logical level "Time Week" for source table TIME_WEEK and "Time Detail" for source table TIME_DAY will do the work.

I hope this post will give a different perspective to your troubleshooting effort when you face that aggregate navigation problem again.

OLAP desiging using ADAPT

I recently came across this wonderful designing technology for OLAP databases named ADAPT by Symmetry Corporation (www.symcorp.com)

I am sure most of us limit the OLAP modeling aspect to star/snowflake schema using the traditional design methodologies such as entity-relationship (ER) diagramming.

Using ADAPT designing, you can create visual representation of almost all objects in the OLAP database (even relationships). See the sample Visio diagram below:



It helps in creating blue-print of OLAP model and validate it against the business requirements before actually jumping on to implementation, hence saves time on implementation iterations.

Definitely worth giving a try if you are not using it already.

DISCLAIMER:

A NONEXCLUSIVE COPYRIGHT LICENSE IS GRANTED TO USE ALL PROGRAMMING CODE EXAMPLES IN THIS SAMPLE FROM WHICH YOU CAN GENERATE SIMILAR FUNCTION TAILORED TO YOUR OWN SPECIFIC NEEDS. THE SAMPLE CODE PROVIDED IS FOR ILLUSTRATIVE PURPOSES ONLY. THESE EXAMPLES HAVE NOT BEEN THOROUGHLY TESTED UNDER ALL CONDITIONS. I, THEREFORE, CANNOT GUARANTEE OR IMPLY RELIABILITY, SERVICEABILITY, OR FUNCTION OF THESE PROGRAMS.ALL PROGRAMS AND INFORMATION CONTAINED HEREIN ARE PROVIDED TO YOU "AS IS" WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES OF ANY KIND. THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. I SHALL NOT BE LIABLE FOR ANY COST OR DAMAGE ARISING EITHER DIRECTLY OR INDIRECTLY FROM THE INFORMATION IN THE ARTICLE OR USE OF THE SAMPLE CODE. IT IS SOLELY YOUR RESPONSIBILITY TO EVALUATE THE ACCURACY, COMPLETENESS AND USEFULNESS OF ALL OPINIONS, ADVICE, SAMPLE CODE AND OTHER INFORMATION PROVIDED HERE. I DO NOT WARRANT THAT ANY INFORMATION OR SAMPLE CODE IS ERROR-FREE. ANY TRADEMARK OR LOGO USED IN THE ARTICLE ARE PROPERTY OF THEIR RESPECTIVE OWNER.