HOW can I using of Teiid for Connect to Microsoft Analysis service and read OLAP Data

[abedini]

Hi

I wand Connect to Microsoft Analysis service and Import OLAP Data in VDB (in teiid ).

Please explain.

Because I have Several articles in this issue ,But failed to do.

Thanks in Advance

[rareddy]

m,

Please take look a this thread for some back ground Using OLAP in 8.1 embedded version

The support for "Microsoft Analysis Services" is done through OLAP4J driver, and Teiid provides a translator for it. See here for some instructions on how to create data source and sample VDB etc. teiid/build/kits/jboss-as7/docs/teiid/datasources/olap at master · teiid/teiid · GitHub This shows a sample usage of dynamic vdb.

Note that Teiid Designer integration for this is limited. Once you create a data source in JBoss EAP/Teiid server, you can use Teiid Designer's "Teiid Connection Importer" to import metadata from the data source you created. However, this will only create one procedure called "invokeMDX" in a source model, then create view model and create a view(s) and define transformation like defined in "foodmart-xmla-vdb.xml" from link above. That shows an example usage of "invokeMDX" procedure.

Once you figure out XML URL, if you want to post here, then I will update Teiid documentation. Let us know if you have any more questions.

Ramesh..

[abedini]

hi

Thank you for the answer

now I had a few other questions

1- have Teiid Designer any Wizard Tools for connect to OLAP like wizard Tool for connect to MYSQL?

2- how can i create an "invoke MDX" procedure ?

3- how can i introduction and use of OLAP4J JDBC Driver to Teiid Designer?

Thank you very much for your answer

[rareddy]

Have you created the resource adapter connection in the JBoss EAP/Teiid Server based on the instructions I provided above? If "yes" then proceed to below

File --> Import --> Teiid Designer --> Teiid Connection Importer

Then you should see data source you created on the first screen, select that then in next screen select "olap" as translator and follow the wizard.

[abedini]

hi

Thank you very much

but now i have tow problem

1-

I have Problem for do this part:

"Now edit the "standalone-teiid.xml" file in the "datasources" section add datasource as defined in "mondrian.xml" file"

when i do this , my server cant start.can you give me a sample of "standalone-teiid.xml" when is change with "mondrian.xml" Content.

2-

when in "Import usaing Teiid Connection" window select Olap and do steps . in last step show me this error

"the import VDB failed to deploy- Please check server log"

and in server log was this error

"JBAS014901 content REMOVE FROM ......"

AND

"JBAS018558 undeploy "importVDB-vdb.xml" "

please Help me

[rareddy]

Have you read the readme.txt file in that directory? It asks you to use olap-xmla.xml file, then why would you use mondrian.xml? mondrian.xml if for connecting to Mondrian, not Microsoft Analysis Service.

To use olap-xmla.xml file, as I mentioned before, you need to figure out XMLA URL to connect to Microsoft Analysis Service, and then use that in your configuration. Then based on the "foodmart-xmla-vdb.xml" create your dynamic VDB that issues a mdx query against MS Analysis server, and test that out. Once this works you can be sure that your connection is working correctly. Then you can use Designer import as said in previous comment.

Ramesh..

[abedini]

For connect to OLAP in Jboss Environment do this Steps:

1-   At the first i create an OLAP site in IIS

And paste “msmdpump.dll” for Access to OLAP

2-    In the next step for create Data Source use of “olap-xmla.xml” in “standalone-teiid.xml ”

Note: “standalone-teiid.xml” was in this Address “ D:\workspace\jboss-7.1.1.Final\docs\teiid\datasources\olap ”

<datasource jndi-name="java:/olapDS" pool-name="olapDS">

        - <!-- note here that you need to also create datasource for source, this is for mondrian as source for teiid -->

        - <connectionurl>jdbc:xmla:Server=http://127.0.0.1:8080/mondrian/xmla;Provider=Mondrian;DataSource=java:foodmart;Catalog=FoodMart;

</connection-url>

        + <datasource jndi-name="java:/xmlaDS" pool-name="xmlaDS">

        + <!-- This is XMLA URL for your OLAP CUBE; Example shown is for a Mondrian Foodmart

        + from Mondrian Examples -->

         <connectionurl>jdbc:xmla:Server=http://localhost:8000/mondrian/xmla

</connection-url>

        <driver>olap</driver>

         <driver-class>org.olap4j.driver.xmla.XmlaOlap4jDriver</driver-class>

        <transaction-isolation>-1</transaction-isolation>

        <pool>

        <prefill>false</prefill>

        <use-strict-min>false</use-strict-min>

        <flush-strategy>FailingConnectionOnly</flush-strategy>

        </pool>

         <!--

        <security>

        <user-name>{user}</user-name>

        <password>{password}</password>

        </security>

         -->

        </datasource>

        <drivers>

         <driver name="olap" module="org.olap4j"/>

         </drivers>

         <driver name="olap" module="org.olap4j">

         <driver-class>org.olap4j.driver.xmla.XmlaOlap4jDriver</driver-class>

         </driver>

         </drivers>

        </datasources>

QUESTIONS:

1-    Below Address for Connected to OLAP is true?

<connectionurl>jdbc:xmla:Server=http://127.0.0.1:8080/olap/msmdpump.dll

2-    In this connection string for provider most write OLAP?

<connectionurl>jdbc:xmla:Server=http://127.0.0.1:8080/mondrian/xmla;Provider=Mondrian;DataSource=java:foodmart;Catalog=FoodMart;

3-    I want connect to AdventureWorksDW2008-1>> Cubes>>Adventure Works. Who most Replace “catalog=foodmart” ?

4-    Below Address for OLAP4j Driver is true?

“E:\JBoosWorkspace\jboss-7.1.1.Final\docs\teiid\datasources\olap\modules\org\olap4j\main”

5-    Why in “standalone-teiid.xml” we are have to Connection url?

<connectionurl>jdbc:xmla:Server=http://127.0.0.1:8080/mondrian/xmla;Provider=Mondrian;DataSource=java:foodmart;Catalog=FoodMart;

</connection-url>

And

<connectionurl>jdbc:xmla:Server=http://localhost:8000/mondrian/xmla

</connection-url>

thanks

[rareddy]

m,

1, 2, 3) Should be like "jdbc:xmla:Server=http://127.0.0.1:8080/xmla/msxisapi.dll;Catalog=Adventure Works DW Standard Edition;DataSource=Local Analysis Server" However this just an example, based on your server location name this may be different.

4) yes

5) Because you want to use Teiid? If you want use Teiid to access MS SQL Analysis server, then you must configure in Teiid's configuration how Teiid can access this server. You configure that in the standalone-teiid.xml as data source. Then in turn Teiid can access this data source using the JNDI name you specify in the configuration and as well as in the VDB.

Your xml should be like

<subsystem xmlns="urn:jboss:domain:datasources:1.0">
    <datasources>
        <datasource jndi-name="java:/xmlaDS" pool-name="xmlaDS">
            <connection-url>jdbc:xmla:Server=http://127.0.0.1:8080/xmla/msxisapi.dll;Catalog=Adventure Works DW Standard Edition;DataSource=Local Analysis Server</connection-url>
            <driver>olap</driver>
            <transaction-isolation>-1</transaction-isolation>
            <pool>
                <prefill>false</prefill>
                <use-strict-min>false</use-strict-min>
                <flush-strategy>FailingConnectionOnly</flush-strategy>
            </pool>
            <!--
            <security>
                <user-name>{user}</user-name>
                <password>{password}</password>
            </security>
            -->
        </datasource>                             
        <drivers>
            <driver name="olap" module="org.olap4j">
                <driver-class>org.olap4j.driver.xmla.XmlaOlap4jDriver</driver-class>
            </driver>       
        </drivers>
    </datasources>
</subsystem>

You can check in a web-browser if "http://127.0.0.1:8080/xmla/msxisapi.dll" works, I mean at least responds with some error message to make sure your Server is setup XMLA. If not look up MS SQl Server Analysis server XMLA setup. Once the above is working then you can proceed to dynamic VDB like

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="adventure" version="1">
    <description>Connects to MS SQL Analysis Server</description>
    <model name="xmla">
        <!-- This model introduces a invokeMDX procedure when deployed -->
        <source name="local" translator-name="olap" connection-jndi-name="java:/xmlaDS"/>
    </model>

    <model name="anylytics" type="VIRTUAL">
      <metadata type="DDL"><![CDATA[
        <!-- this is a sample; write your view based on the Adventure works and your needs -->
        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>

Ramesh..

[abedini]

I see on this URL (Setup XMLA Interface for OLAP Servers - OpenI Wiki.htm) three way to Setup XMLA Interface for OLAP Servers include:

1.    SQL Server Analysis Services 2005 (SSAS 2005)

http://localhost/olap/msmdpump.dll

2.    Microsoft Analysis Services 2000 (MSAS 2000)

http://localhost/xmla/msxisapi.dll

3.    Setup Mondrian for XMLA service

http://localhost:8080/mondrian/xmla

I select http://localhost/olap/msmdpump.dll and  try to connected to olap whit data source in standalone-teiid.xml file:

<subsystem xmlns="urn:jboss:domain:datasources:1.0">

            <datasources>

                <datasource jndi-name="java:/xmlaDS" pool-name="xmlaDS">

                    <connection-url>jdbc:xmla:Server= http://localhost/olap/msmdpump.dll;Catalog=Adventure Works DW 2008-1;DataSource=.\sql2008r2</connection-url>

                    <driver>olap</driver>

                    <pool>

                        <prefill>false</prefill>

                        <use-strict-min>false</use-strict-min>

                        <flush-strategy>FailingConnectionOnly</flush-strategy>

                    </pool>

                    <security>

                        <user-name>s.userName </user-name>

                        <password>123456789</password>

                    </security>

                </datasource>

                <drivers>

                    <driver name="olap" module="org.olap4j">

                        <driver-class>org.olap4j.driver.xmla.XmlaOlap4jDriver</driver-class>

                    </driver>

                </drivers>

            </datasources>

        </subsystem>

When I create virtual data base event this errore:

Metadata failed to load:

Reasen:java.lang.null pointer exeption

Questions:

1-     Below Address for connected to olap is true?

jdbc:xmla:Server=http://localhost/olap/msmdpump.dll;Catalog=Adventure Works DW 2008-1; Data Source=.\sql2008r2

2-    I need to a Solution for this error:

Metadata failed to load: Reasen:java.lang.null pointer exeption

3-i most Create this VDB manual or virtual? If manual. How most create Query (line 11 - 16)?

1.    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

2.    <vdb name="adventure" version="1">

3.    <description>Connects to MS SQL Analysis Server</description>

4.    <model name="xmla">

5.    <!-- This model introduces a invokeMDX procedure when deployed -->

6.    <source name="local" translator-name="olap" connection-jndi-name="java:/xmlaDS"/>

7.    </model>

8.   

9.    <model name="anylytics" type="VIRTUAL">

10.    <metadata type="DDL"><![CDATA[

11.    <!-- this is a sample; write your view based on the Adventure works and your needs -->

12.    CREATE VIEW UnitSales (

13.    unit string,

14.    amount decimal

15.    ) 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,

16.    ARRAYTABLE(w.tuple COLUMNS "col1" string , "col2" decimal) AS x;

17.    ]]> </metadata>

18.    </model>

19.    </vdb>

[rareddy]

Metadata load fail happens when the connection is not set correctly, so your connection URL is still wrong. I suggest you test the URL out side of Teiid using either using web-browser or tool like SOAP-UI and make sure it is correct and working. If you still can not make out the URL, try asking in forums at http://olap4j.org

Only one you have a working connection URL to SQL Analysis server, then try to use Teiid. There no use if you do not have other working.

Ramesh..

[abedini]

hi

I get this connection string from SQL analyze service(ODBC connection)

“Provider=MSOLAP.4;Data Source=.\sql2008r2;Integrated Security=SSPI;Initial Catalog=Adventure Works DW 2008-1”

Please help me to create data source whit this connection url in standalone-teiid.xml file like this data source:

<subsystem xmlns="urn:jboss:domain:datasources:1.0">

            <datasources>

                <datasource jndi-name="java:/xmlaDS" pool-name="xmlaDS">

                    <connection-url>jdbc:xmla:Server= http://localhost/olap/msmdpump.dll;Catalog=Adventure Works DW 2008-1;Integrated Security=SSPI;DataSource=.\sql2008r2</connection-url>

                    <driver>olap</driver>

                    <pool>

                        <prefill>false</prefill>

                        <use-strict-min>false</use-strict-min>

                        <flush-strategy>FailingConnectionOnly</flush-strategy>

                    </pool>

                </datasource>

                <drivers>

                    <driver name="olap" module="org.olap4j">

                        <driver-class>org.olap4j.driver.xmla.XmlaOlap4jDriver</driver-class>

                    </driver>

                </drivers>

            </datasources>

        </subsystem>

**** how convert ODBC connection URL to JDBC connectin?i need a syntax for convert ODBC to JDBC

Thank you very much

[rareddy]

The OLAP4J is JDBC driver, however it uses XMLA protocol to connect to your Microsoft SQL Analysis Server. If you have a DBA or Admin in your company for this server ask them if the XMLA protocol is turned ON?, then ask them give you a connection URL for it. That is what you need to use in above configuration. You can not convert from ODBC to JDBC.

Ramesh..

[abedini]

Hi

I test this URL “http://localhost/olap/msmdpump.dll” in two environment (Microsoft Analyze service & IReport XMLA server) like:

 
 

connection in successfully But when I create data source in standalone-teiid.xml :

<subsystem xmlns="urn:jboss:domain:datasources:1.0">

<datasources>

<datasource jndi-name="java:/xmlaDS" pool-name="xmlaDS">

<connection-url>jdbc:xmla:Server= http://localhost/olap/msmdpump.dll;Catalog=Adventure Works DW 2008;Integrated Security=SSPI;DataSource=.\sql2008r2

</connection-url>

<driver>olap</driver>

<pool>

<prefill>false</prefill>

<use-strict-min>false</use-strict-min>

<flush-strategy>FailingConnectionOnly</flush-strategy>

</pool>

<!--

<security>

<user-name>{user}</user-name>

<password>{password}</password>

</security>

-->

</datasource>

<drivers>

<driver name="olap" module="org.olap4j">

<driver-class>org.olap4j.driver.xmla.XmlaOlap4jDriver</driver-class>

</driver>

</drivers>

</datasources>

</subsystem>

Question:

1. In log server show this errore: “Meta data failed:reasen:java.lan.nullexeption “How I resolve this solution?
2. In standalone-teiid.xml there is sample data source like h2:

<subsystem xmlns="urn:jboss:domain:datasources:1.0">

<datasources>

                <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">

<connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>

<driver>h2</driver>

                    <security>

<user-name>sa</user-name>

<password>sa</password>

</security>

</datasource>

<drivers>

                    <driver name="h2" module="com.h2database.h2">

<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

</driver>

<driver name="teiid-local" module="org.jboss.teiid">

                        <driver-class>org.teiid.jdbc.TeiidDriver</driver-class>

<xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>

</driver>

<driver name="teiid" module="org.jboss.teiid.client">

<driver-class>org.teiid.jdbc.TeiidDriver</driver-class>

<xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>

</driver>

</drivers>

            </datasources>

        </subsystem>

Question:

1. Where is the h2 metadata file (jdbc:h2:mem:test) in jboss server?

[rareddy]

You are going off topic with "h2", that does not matter for your scenario, so forget about that, focus on the XMLA connection to the "SQL Anaysis Server".

1) When you say it worked with "IReport", did you supply the "userid" and "password"?

2) Is your "Teiid" server is installed on the same machine as your "SQL Analysis Server"?

3) What is server name where SQL Analysis server is installed

4) Server name where Teiid is installed?

Please answer all my questions.

You have entered the URL as

jdbc:xmla:Server= http://localhost/olap/msmdpump.dll;Catalog=Adventure Works DW 2008;Integrated Security=SSPI;DataSource=.\sql2008r2

Teiid does not support "SSPI" for login. So, it can NOT be used. In my guess the URL is something

jdbc:xmla:Server= http://sql2008r2/olap/msmdpump.dll;Catalog=Adventure Works DW 2008;DataSource=sql2008r2

Ramesh..

[abedini]

Hi

Answer

1- yes

2- yes

3-.\sql200r2

4-jboss version is 7.1.1 final  and teiid run time version is 8.3.0 final

Question

1. I test "jdbc:xmla:Server= http://sql2008r2/olap/msmdpump.dll;Catalog=Adventure Works DW 2008;DataSource=sql2008r2 " but is not connect to SQL Anaysis Server !!

thanks