Skip to Content
0
Former Member
Jul 11, 2008 at 06:07 PM

BI SDK/XMLA (MDX) - Navigation attribute in BI query issue

54 Views

Environment : Portal - EP7.0 SPS 14, BI 7.0 SPS17

I have written custom report views which uses the BI SDK to execute BI querries through the portal.

Following link provides the detail about configuring the portal to access the BI system and execute queries through XMLA which used MDX statements on the BI system...

http://help.sap.com/saphelp_nw04s/helpdata/en/ba/e380e03c3a4dbf8cf082f0c910f9cf/frameset.htm

The query I am executing from the portal using BI sdk, shows the following MDX statement being executed on BI system...

SELECT NON EMPTY [Measures].MEMBERS ON AXIS(0), NON EMPTY [0MAT_SALES__0MATL_GRP_1].MEMBERS ON AXIS(1) FROM [ZBCK_M10/ZTEST] WHERE [ZMZ1PF1].[0000300131] CELL PROPERTIES CELL_ORDINAL, VALUE, FORMATTED_VALUE

The query "ZBCK_M10/ZTEST" has navigation attribute and takes around 6 minutes to return results. If we remove the the navigation attribute from the query, the query return results within few seconds.

If we execute the same query (with or without navigation attribute) through BEX, we get results within few seconds.

We traced using sm66/sm50 and observed that both portal and BEX is going through the BI accelerator. We also notice that when portal executes the above MDX statement through BI SDK (or MDX statement executed through MDXTEST BI transaction on BI server), we see long running sequential reads. The sequencial reads show that sql statements are executed which are join between master data P tables and fact tables which is taking longer to execute. When running the same query though BEX, we do not see long running sequential reads.

The question is why the MDX statements when executed for queries with navigation attributes go through the master data P table ( which are not indexed on BI accelerator) and causing long running sequential reads where as BEX query execution does not go through the same and executes faster?

Any help is really appreciated.

Thanks

Bhabesh Patel