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.

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