1 2 Previous Next 27 Replies Latest reply on Nov 26, 2014 11:16 AM by rareddy Go to original post
      • 15. Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
        rareddy

        If enter the below in a web browser address what do you see?

         

        http://sql2008r2/olap/msmdpump.dll

         

        Also as the URL you can try


        jdbc:xmla:Server=http://SQL2008R2/olap/msmdpump.dll;Catalog=Adventure Works DW 2008;DataSource=GHA-19-014-PC\SQL2008R2


        Also check MS SQL Analysis server logs and see if you see any errors or failed attempts to connect. If find any please post here.

        • 16. Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
          abedini

          I test this url(http://sql2008r2/olap/msmdpump.dll) in web browser and see this Error:

          Server not found


          And when test this URL: http://localhost/olap/msmdpump.dll show this Error:

          <soap:Envelope>

          <soap:Body>

          <soap:Fault>

          <faultcode>XMLAnalysisError.0xc10e0002</faultcode>

          <faultstring>Parser: The syntax for 'GET' is incorrect.</faultstring>

          <detail><Error ErrorCode="3238920194" Description="Parser: The syntax for 'GET' is incorrect." Source="Unknown" HelpFile=""/></detail>

          </soap:Fault></soap:Body></soap:Envelope>


          And I see in this address D:\workspace\jboss-7.1.1.Final\docs\teiid\datasources\olap(olap-xmla.xml)

          The data source:

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

          <datasources>

          <datasourcejndi-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 -->

          <connection-url>jdbc:xmla:Server=http://127.0.0.1:8080/mondrian/xmla;Provider=Mondrian;DataSource=java:foodmart;Catalog=FoodMart;</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>

          </datasources>

          </subsystem>

           

          In my case with this URL :

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


          what provider is set ?for example:

          Mondrian  or MSOLAP6.0

           

          And I  don’t know to use olap4j-1.2.0.jar or olap4j-xmla-1.2.0.jar because when I extract olap4j-1.2.0.jar

          There is not (XmlaOlap4jDriver.class) file in this  address:org/olap4j/driver/xmla

          When I extract olap4j-xmla-1.2.0.jarther is file (XmlaOlap4jDriver.class) in this address

          org/olap4j/driver/xmla

           

          and where are the olap4j-1.2.0.jar or olap4j-xmla-1.2.0.jar files in jboss server :

          D:\workspace\jboss-7.1.1.Final\modules\com\olap\olap4j\main

          or

          D:\workspace\jboss-7.1.1.Final\docs\teiid\datasources\olap\modules\org\olap4j\main

          and when I create data source in teiid I don’t see olap4j-xmla-1.2.0.jar

           

          in driver list in this browser:

          When I click in add button to create another data source show this error:

                                                 

          thanks

          • 17. Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
            abedini

            I don’t know where deploy virtual database like (foodmart-xmla-vdb.xml) :

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

            <vdb name="foodmart" version="1">

            <description>Connects to FoodMart Mondrian database</description>

            <property name="UseConnectorMetadata" value="cached" />

             

            <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>

            And I don’t know how link this virtual database to my data source?

            Another word where insert virtual data base name in my data source?

            thanks

            • 18. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
              rareddy

              the "olap4j-xmla-1.2.0.jar" ishould be in D:\workspace\jboss-7.1.1.Final\docs\teiid\datasources\olap\modules\org\olap4j\main

              if it not there you need to add it, then edit module.xml and add the reference to the jar there.


              Also in "standalone-teiid.xml" in data sources subsystem, the XML under drivers should look like

                          <driver name="olap" module="org.olap4j">
                              <driver-class>org.olap4j.driver.xmla.XmlaOlap4jDriver</driver-class>
                          </driver>
              
              • 19. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
                abedini

                hi

                I connected to analyze service successfully service with this steps:

                1-create olap4j folder in this address:

                D:\workspace\jboss-7.1.1.Final\modules\org\olap4j

                And create folder main and create module.xml in this address:

                D:\workspace\jboss-7.1.1.Final\modules\org\olap4j\main

                Then copy files include:

                1.bmp


                And in module.xml insert this command:


                <?xml version="1.0" encoding="UTF-8"?>

                <module xmlns="urn:jboss:module:1.0" name="org.olap4j">

                <resources>

                <resource-root path="olap4j-xmla-1.2.0.jar"/>

                    <resource-root path="olap4j.jar"/>

                   

                    <resource-root path="commons-math-1.0.jar"/>

                <resource-root path="commons-vfs-1.0.jar"/>

                <resource-root path="eigenbase-properties.jar"/>

                <resource-root path="eigenbase-resgen.jar"/>

                    <resource-root path="eigenbase-xom.jar"/>

                <resource-root path="javacup.jar"/>

                <resource-root path="mondrian.jar"/>

                <resource-root path="commons-pool-1.2.jar"/>

                <resource-root path="commons-dbcp-1.2.1.jar"/>

                <resource-root path="xercesImpl.jar"/>

                 

                  </resources>

                  <dependencies>

                    <module name="javax.api"/>

                      <module name="org.apache.xerces"/>

                </dependencies>

                </module>

                 

                And in standalone-teiid.xml create data source like:

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

                <datasources>

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

                <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.jafariyan</user-name>

                <password>S@@123456789</password>

                                    </security>

                </datasource>

                <drivers>

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

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

                </driver>

                                </drivers>

                </datasources>

                       </subsystem>


                Then run server and :

                2.bmp

                b

                 

                 

                 

                 

                 

                Questions:

                 

                1-How insert MDX query for show data?

                2-What provider is set in :

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

                For example :

                Provider=Mondrian

                Provider=MSOLAP 6.0

                • 20. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
                  abedini

                  I connected to analyze service successfully service with this steps:

                  1-create olap4j folder in this address:

                  D:\workspace\jboss-7.1.1.Final\modules\org\olap4j

                  And create folder main and create module.xml in this address:

                  D:\workspace\jboss-7.1.1.Final\modules\org\olap4j\main

                  Then copy files include:

                  1.bmp

                  And in module.xml insert this command:

                  <?xml version="1.0" encoding="UTF-8"?>

                  <module xmlns="urn:jboss:module:1.0" name="org.olap4j">

                  <resources>

                  <resource-root path="olap4j-xmla-1.2.0.jar"/>

                      <resource-root path="olap4j.jar"/>

                     

                      <resource-root path="commons-math-1.0.jar"/>

                  <resource-root path="commons-vfs-1.0.jar"/>

                  <resource-root path="eigenbase-properties.jar"/>

                  <resource-root path="eigenbase-resgen.jar"/>

                  <resource-root path="eigenbase-xom.jar"/>

                  <resource-root path="javacup.jar"/>

                  <resource-root path="mondrian.jar"/>

                  <resource-root path="commons-pool-1.2.jar"/>

                  <resource-root path="commons-dbcp-1.2.1.jar"/>

                  <resource-root path="xercesImpl.jar"/>

                   

                    </resources>

                    <dependencies>

                      <module name="javax.api"/>

                        <module name="org.apache.xerces"/>

                  </dependencies>

                  </module>

                   

                  And in standalone-teiid.xml create data source like:

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

                  <datasources>

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

                  <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.jafariyan</user-name>

                  <password>S@@123456789</password>

                                      </security>

                                  </datasource>

                  <drivers>

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

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

                  </driver>

                                  </drivers>

                              </datasources>

                         </subsystem>

                  ....

                  • 21. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
                    rareddy

                    Finally!!! Good Job.

                     

                    Now write the DDL for the model in your VDB using the invokeMDX procedure as shown in the example foodmart-vdb.xml, but it should reflect your data model in SQL Analysis Server. Then you can use JDBC to access and issue SQL queries against it.


                    Ramesh..

                    • 22. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
                      abedini

                      part2

                      Then run server and :

                      2.GIF3.GIF

                      4.GIF5.GIF6.GIF

                       

                      Questions:

                       

                      1-How insert MDX query for show data?

                      2-What provider is set in :

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

                      For example :

                      Provider=Mondrian

                      Provider=MSOLAP 6.0

                      • 23. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
                        rareddy

                        see the sample foodmart-vdb.xml for example MDX. No provider needed.

                        • 24. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
                          abedini

                          i do this:

                          01.GIF02.GIF03.GIF04.GIF05.GIF

                          but How insert MDX query for show data form Olap?

                          • 25. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
                            rareddy

                            My suggestion is for you to go through an example as to how to design a view in Teiid Designer. See examples here Examples - Teiid Examples - Project Documentation Editor

                            • 26. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
                              abedini

                              Hi

                              thanks for your answer

                              I saw Examples - Teiid Examples - Project Documentation Editor but I do not know which of items in that page It is useful for me?

                              and

                              how can Write a MDX query for connect to OLAP in teiid ?

                              • 27. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service  and read OLAP Data
                                rareddy

                                m,

                                 

                                Above examples I gave so that you can learn how to define a view and its transformation in Teiid Designer. Once you learn that, then you apply that skill to use "invokeMDX" procedure in the transformation of the view. I previously already gave you the "sample" MDX query in a Dynamic VDB that can be used as example in the transformation in the Designer.  There are some videos on how to use Teiid Designer too.

                                 

                                Ramesh..

                                1 2 Previous Next