Using OLAP translator in Teiid with SAP BW

[bpiepers]

I am investigating the possibilities of exposing a SAP BW data source (cubes) in Teiid, without the possibility of communicating with a SAP Gateway component. It should be possible to use the OLAP translator for that (also see: OLAP Translator - Teiid 8.9 (draft) - Project Documentation Editor) but what I would like to know is how exactly this is used in the Teiid designer. There doesn't seem to be a standard tool to import this is a data source so I think one should manually create the invokeMDX procedure. Can anyone point me to an example or explain me which parameters etc. I should use? I am using Teiid Designer 8.3.3 Final.

Also, I would be very interested in contacting someone who has experience with integrating JDV/Teiid with SAP BW, particularly SAP BW version 7.30.

Any help is much appreciated.

[rareddy]

Bas,

There is not much tooling support for this, other than using the Teiid Connection Importer and retrieving the source model that includes the "invokeMDX" procedure call. Teiid uses OLAP4J driver for the access. You want first install this driver in server like other JDBC drivers, like creating a module and adding in the standalone.xml file etc. Then follow instructions in this page teiid/build/kits/jboss-as7/docs/teiid/datasources/olap at master · teiid/teiid · GitHub for connection. You need to figure out what is going the the URL for SAP-BW.

Coming back to Designer, in the view model, you need to device your Views such that you use invokeMDX procedure, by embedding the MDX query that you want to query against SAP-BW. Note that since Teiid does not understand MDX, there will be no further optimizations to the embedded MDX query, it will be executed as is, then any filtering is applied. Here is sample Dynamic VDB I used for testing. The below can be easily translated to Designer work.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="foodmart" version="1">
    <description>Connects to FoodMart Mondrian database</description>
    <model name="xmla">
        <!-- This model introduces a invokeMDX procedure when deployed -->
        <source name="foodmart" translator-name="olap" connection-jndi-name="java:/xmlaDS"/>
    </model>
    <model name="anylytics" type="VIRTUAL">
      <metadata type="DDL"><![CDATA[
        CREATE VIEW UnitSales (
            unit string,
            amount decimal
        )  AS SELECT x.col1 as unit, x.col2 as amount FROM (call xmla.invokeMDX('SELECT {[Measures].[Unit Sales]} ON 0,{[Product].Children} ON 1 FROM [Sales]')) w,
            ARRAYTABLE(w.tuple COLUMNS "col1" string , "col2" decimal) AS x;
      ]]> </metadata>
    </model>
</vdb>

HTH. Please do let us know how this goes.

Ramesh..

[bpiepers]

Thanks Ramesh, I will definitely give any feedback as soon as I have something. We are still in the initial phase so it will probably take some time before we get to the point of actually implementing anything.