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

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”

About these ads