Skip to Content

If the purpose of your project is to send some data from BW infocubes/BEx queries to other applications via web services, there are a few options available to you.

Without going into too much detail (there are some pretty detailed Howto documents on SDN), I will briefly go over the options, providing some MDX samples to retrieve data from the BW cubes, followed by some of the performance metrics/time that it takes to retrieve the BW data.

 

Option 1: XML for Analysis :

Reference: http://help.sap.com/saphelp_dimp50/helpdata/en/9f/9b8f3c855f0454e10000000a11405a/content.htm

Using this method, you write custom MDX statements, and pass them to SAP BW.

I’ve used a third party tool to test the MDX/Web service. You can also use transaction MDXTEST in BW to test the MDX statements.

 

image

Fig 1: Screenshot of third party tool to pass MDX statements to SAP BW.

 

Click on ‘select file’ and select the XML file that has the MDX statement as shown in Fig 2.

 

image

Fig 2: XML file. MDX statement in Execute method.

 

Reference:

http://help.sap.com/saphelp_dimp50/helpdata/en/14/a3639e028a144d8c8b7dd403b22a1a/content.htm

MDX samples :

SELECT{ [Measures].[ZHRS] } ON AXIS(0) ,

NON EMPTY

FILTER (

   [0EQUIPMENT].[LEVEL01].MEMBERS

* [0ACTTYPE].[LEVEL01].MEMBERS

* [0CS_ORDER].[LEVEL01].MEMBERS

* [0CALDAY].[LEVEL01].MEMBERS

, [0CALDAY].[20CALDAY] > 20110101) 

properties

[0ACTTYPE].[2ZACTTYPGP],

[0EQUIPMENT].[20EQUIPMENT],

[0CS_ORDER].[20CS_ORDER]

ON AXIS(1)

FROM [$Infocubename]

WHERE [0EQUIPMENT].[0000000000123456789]

—————————————————————

SELECT NON EMPTY

 [0EQUIPMENT].[LEVEL01].MEMBERS *

 [ZME_ORDER].[LEVEL01].MEMBERS *

 [ZME_ITEM].[LEVEL01].MEMBERS *

 [0SHIP_TO].[LEVEL01].MEMBERS

properties [0EQUIPMENT].[20EQUIPMENT] ON axis(1)

FROM [$infocubename]

WHERE {[0SHIP_TO].[0012345],

                [0SHIP_TO].[0012346]}*

{( [ZME_ORDER].&[001234567], [ZME_ITEM].&[000010] ),

 ( [ZME_ORDER].&[001234569], [ZME_ITEM].&[000010] )} 

 

 

Option 2 :XMLA: Write MDX statement based on BEx query

It’s very similar to option 1. The only difference being this time you first create a BEx Query and write your MDX statement as shown in the sample below: The MDX statement can also be auto generated in transaction MDXTEST in SAP BW after the query is created.

SELECT [Measures].MEMBERS ON AXIS(0), NON EMPTY

 [0EQUIPMENT].[LEVEL01].MEMBERS *

[0ACTTYPE].[LEVEL01].MEMBERS *

[0CS_ORDER].[LEVEL01].MEMBERS *

[0CALDAY].[LEVEL01].MEMBERS

properties

 [0ACTTYPE].[2ZACTTYPGP],

 [0EQUIPMENT].[20EQUIPMENT],

 [0CS_ORDER].[20CS_ORDER],

 [0CS_ORDER].[20PRIORITY],

 [0CS_ORDER].[1ZSTATZSS2] ON AXIS(1)

FROM [YDCCSA30/YSUMMARY]

 SAP VARIABLES

[0I_DAYS] INCLUDING [0CALDAY].[20100508]:[0CALDAY].[20110518]

[0I_EQUI] INCLUDING [0EQUIPMENT].[0000000000123456]

[0I_EQUI] INCLUDING [0EQUIPMENT].[0000000000123457]

[0I_EQUI] INCLUDING [0EQUIPMENT].[0000000000123458]

 

YDCCSA30 – Infocube name.

YSUMMARY – BEx query name.

You can also use SAP variables (0I_DAYS) as you can see from the MDX statement above.

For reference please check the below link:

http://help.sap.com/saphelp_dimp50/helpdata/en/a7/18533d6dd60610e10000000a114084/frameset.htm

 

Option 3: Using query_view_data

Using this method, you do not need to write any MDX statements.

Reference:http://help.sap.com/saphelp_dimp50/helpdata/en/d8/3bfc3f8fc2c542e10000000a1550b0/content.htm

You would need to create a BEx query and the web service takes some input parameters such as the query name, query variables etc..,

TIP: When using these web services the data is received in an XML list output form. Depending on the number of records that are retrieved the XML list output size can be really large (~MB).  In transaction SICF, you have an option of turning on compression for the service, which would dramatically reduce the size of the file that needs to be transferred to the application that is consuming the web service. By turning compression on you will see significant improvements in response times.

image

 

Comparison of the 3 options discussed above:

  • Same queries/data set for all 3 options discussed above were executed.
  • Data was not cached during the run.

 

 

 

 

 

 

 

 

 

 

 

 

 

Options

 

 

Without   Compression/Average response time

 

 

With   Compression/Average response time

 

 

Option   1: XMLA-custom MDX

 

 

~   60 seconds

 

 

~26   seconds

 

 

Option   2:XMLA with BEx query-custom MDX

 

 

~15   seconds

 

 

~8   seconds

 

 

Option 3: query_view_data: No MDX

 

 

 

 

~2   seconds

 

 

The figures above represent only the time that elapses between the call to BW and the caching of the resulting data to the application.

Using query_view_data is a clear winner. It does not require any custom MDX and can be used to pass different selection criteria. And don’t forget to turn on compression.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

    1. Kamal Nanduri Post author
      1)Yes both are web services –
      XML/A uses the MDX processor and
      query_view_data uses BICS.
      2) Easy Query uses BICS as well. Very similar to query_view_data. In fact, easy query appears to be much more easier to parse when I look at the API.
      – my blog was based on 3.5 system hence i’ve used query_view_data. Correct me if I’m wrong but I think Easy query is from 7.3 ???

      The take away is anything that goes through BICS is a winner in terms of the response times for retrieving the data.

      (0) 

Leave a Reply