Connecting to OLAP-based data sources is something Red Hat JBoss Data Virtualization (JDV) has supported for some time. The use of the invokeMdx() function of JDV allows a user to connect to and execute Cubes or BEx queries defined in SAP BW. The following steps show you how to create a connection, generate your MDX query and model your JDV procedure to pull back your data for SAP BW. This examples pulls data from a BEx query, but of course you can execute directly against a Cube if required. All you need is the MDX for the Cube.

Setting Up Your Test Environment

1. Creating an InfoCube and BEx query in SAP BW.

If you don't already have an InfoCube and BEx query, here are some ways to create sample data. I created a cube with 1,000,000 rows and add a BEx query on top of it using these steps.

a. Create InfoCube. I created a Cube based on the pre-requisite specified InfoCube in step b. Basically, it required an InfoCube that looks like this:

b. Generate test data. Using CUBE_SAMPLE_CREATE in the SAP UI, I was able to generate a cube with 1,000,000 rows. You can specify whatever number of rows you would like though.

2. Create BEx query. This is a really nice video that walks you through creating a BEx query.

3. Get the MDX for your BEx query. You can do this using SAP transaction MDXTEST or RSCRM_BAPI. For RSCRM_BAPI, you need to enable debug. To do that, go to transaction RSCRMDEBUG. Enter your SAP username and the Debugging Options as mdx_gen. Choose Insert and execute. Then exit this screen and go to transaction RSCRM_BAPI.

Accessing from Red Hat JBoss Data Virtualization

1. Setup a JDV server and connect from JBDS.
2. Add the driver directly into the server configuration file with an entry as shown below:

OR Run the following CLI command in $JDV_HOME/bin folder:

3. Import the source connection for JBDS (Import->Teiid Connection->Source Model)
   • Add the source connection information
     • JDBC URL: jdbc:xmla:Server=http://{bw_host}:{bw_port}/sap/bw/xml/soap/xmla
     • Credentials: sap_user/sap_password
4. Create the view model and add your virtual table.

• The format for the JDV virtual query is:
  • SELECT x.column1 AS column1, x.column2 AS column2, x.column3 AS column3 FROM (EXEC {source_model}.invokeMDX('{MDX_FOR_CUBE_OR_BEx_REPORT}')) AS x, ARRAYTABLE(w.tuple COLUMNS column1 type, column2 type, column3 type) as x

• If you are using my example, your SQL will look something like the following. Notice the root hierarchy value has been changed to "Measures". This is because the enterprise id used in SAP for the root are not resolvable from JDV. Also note that your other enterprise ids used in your SAP system will be different:

SELECT x.zcustid AS cust_id, x.zprdid AS prod_id, x.price AS price, x.quantity AS   quantity

FROM(EXEC SAP_BW_SOURCE.invokeMDX('SELECT

{[Measures].[058YFS98ML8W8QUZ11JQ6UHM1],[Measures].[058YFS98ML8W8QUZ11JQ6UNXL]}  ON COLUMNS,
  NON EMPTY CROSSJOIN({[ZCUSTID].LEVELS(01).MEMBERS},{[ZPRDID].LEVELS(01).MEMBERS})  ON ROWS
FROM [ZIC_TEST/Z_CUST_ORDER_LIST]')) AS w, ARRAYTABLE(w.tuple COLUMNS zcustid string, zprdid string, price decimal, quantity decimal) AS x

5. Save, right-click on your table and preview data.

That's it! You have successfully consumed SAP BW data in JDV!