Sunil S. Ranka's Weblog

Superior Data Analytics is the antidote to Business Failure

Filters On Non Key Columns are NOT added to MDX Query for SAP BW

Posted by sranka on October 26, 2011

Hi All,

During BW-SAP integration , In the reports when we apply filters on the non ‘key’ columns in the reports they were not passed as filters onto the database through the MDX query but instead they are processed by the OBIEE server. This was causing a severe performance issue for all the reports.

Eg: in the report, filter is set to MATDIM=”BISERVER”, when you look at the logical sql you will “Dim – Material”.”Material Name” = “BI SERVER”, but the MDX doesn’t contain the filter condition. Since MDX doesn’t contain the filter, with logical SQL having filter, all the processing was done in the BI Server, causing severe performance issues.

Reason for Behavior:

The SAP BW allows filtering by unique name (used by OBIEE for filtering against level columns) and by key columns only. Filtering by other columns (corresponding to display attributes of characteristics in SAP) is handled internally. This is expected behavior.

There are couple of ways we could solve the issue ::

Option 1 :

A workaround here is to create reports with nested queries, such as:

SELECT DIM1_COL1, DIM2_COL2, DIM3_COL3, <measures>
where CUBE_NAME in (select <DIM COLUMN> where <DIM COL>='xyz')

Option 2 :

Create BEX query with defining required attribute on which you need to pass the value back to MDX as “Navigational  Attribute” , by doing this, every attribute will be represented as dimension in the cube. Upon defining as “Navigational  Attribute“, you will see filter getting passed.

Hope This helps.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Advertisements

One Response to “Filters On Non Key Columns are NOT added to MDX Query for SAP BW”

  1. Greg K said

    What version of obiee are you using for this?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: