11 Replies Latest reply on Sep 6, 2013 1:21 PM by rareddy

    connect to the cubing services using the teiid designer

    latharamachandran

      I have a need to connect to the cubing server and to get the mdx query by the teiid designer .

       

      Teiid designer version 7.7.3

       

      jboss server version 5.0.2

        • 1. Re: connect to the cubing services using the teiid designer
          rareddy

          Teiid does not support JBoss AS 5.0.2. You have to use JBoss AS 5.1.0.

           

          Support for MDX is passive, that means there is no import you can do from cube service in the Teiid Designer. In Designer you need to manually create view that uses a native procedure invocation using MDX as transformation code and get results out. Teiid uses JDBC4OLAP for the accessing your cube server, so either it needs to support this driver or XMLA through this driver.

           

          For example the transformation for your view could be something like

           

          SELECT x.* FROM (call pm1.invokeMDX('<mdx-query>')) w, ARRAYTABLE(w.tuple COLUMNS "col1" string , "col2" string) AS x
          

           

           

          Ramesh..

          • 2. Re: connect to the cubing services using the teiid designer
            latharamachandran

            hai

             

            thank u for your quick reply,but could u pls explain this in detail about connecting to that cubing server

             

            server : JBoss AS 5.3.1

            designer : 5.0.2

            teiid : 7.7.3

            • 3. Re: connect to the cubing services using the teiid designer
              rareddy

              Latha,

               

              1) You need to first figure out if your source supports the JDBC4OLAP driver. If yes download and install this JDBC driver in the JBoss AS7. If not supported, does your source support XMLA, beacuse JDBC4OLAP can issue XMLA based requests.

               

              If you are using the Designer

               

              2) Using Teiid Designer create a source model. Create a procedure called "invokeMDX" with two parameters, "request" as string, "parameters" as object and vararg, and result "tuple" as object.

              3) Now create view model, and create view. In the view's transformation use the transformation as above comment.

              4) Build VDB, deploy it. Create data source for source model using JDBC4OLAP driver.

               

              then issue the query.

               

              if you are going to use Dynamic VDB the vdb will look something like this

               

              <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
              <vdb name="cube" version="1">
                  <description>issue mdx queries</description>
                  <property name="UseConnectorMetadata" value="cached" />
              
                  <model name="analytics">
                      <source name="production" translator-name="olap" connection-jndi-name="java:/cube-ds"/>
                  </model>
              
                  <model name="mycube" type="VIRTUAL">
                  <metadata type="DDL"><![CDATA[
                      CREATE VIEW myview (
                           col1 string,
                           col2 string
                      )  AS SELECT x.col1, x.col2 FROM (call analytics.invokeMDX('<mdx-query>')) w, ARRAYTABLE(w.tuple COLUMNS "col1" string , "col2" string) AS x
                   ]]> 
                  </metadata>
                  </model>
              </vdb>
              

               

               

              Note that in Dynamic VDB, the "invokeMDX" procedure is automatically added.

               

              You still need to create data source for this that connects to your cube serice. This step is needed in both Designer based or Dynamic VDB. For a template to create a olap source, see "<jboss-as>/docs/teiid/datasources/olap" directory, you will find templates to create both jdbc and XMLA based conenctions. The JNDI name of this source should be "java:/cube-ds" per above Dynamic VDB.

               

              If you are new Teiid, go through a simple example first and learn all the components involved, then you can replace the source model for OLAP, jumping straight without some knowledge of Teiid can seem confusing.

               

              Hope this helps.

               

              Ramesh..

              • 4. Re: connect to the cubing services using the teiid designer
                latharamachandran

                Hai,

                 

                Thank You for the response.I'll work out with this and get back to you if any clarifications needed.

                 

                 

                Thanks

                Latha

                • 5. Re: connect to the cubing services using the teiid designer
                  rareddy
                  • 6. Re: connect to the cubing services using the teiid designer
                    latharamachandran

                    Hai,

                     

                    Thanks for your response...

                     

                    I have created the -ds source file(pointing to the ISW cubing server) which is in the deploy directory of the server,I have created the source and in the view's transformation I have used the transformation with the mdx query,deployed the VDB with the source and the view model,with the JNDI name as data soure name and the translator as OLAP,but it doesn't work out.When I make a query to that VDB its giving the error as mentioned below

                     

                    Remote org.teiid.core.TeiidProcessingException: Not a wrapper for: org.olap4j.OlapWrapper

                     

                    Could you pls send the cube-ds which you have created as a reference to me.If there is any worked out example it will be more helpful for me.

                     

                    Thanks & Regards

                    Latha

                    • 7. Re: connect to the cubing services using the teiid designer
                      shawkins

                      Latha,

                       

                      There may be an issue with our olap translator module.xml.  Can you try adding <module name="org.olap4j" /> to the dependencies under modules/system/layers/base/org/jboss/teiid/translator/oldap/main/module.xml

                       

                      Steve

                      • 8. Re: connect to the cubing services using the teiid designer
                        shawkins

                        Latha,

                         

                        Were you successful?

                         

                        Otherwise the jndi reference is likely not correct and is not providing olap4j connections.

                         

                        Steve

                        • 9. Re: connect to the cubing services using the teiid designer
                          latharamachandran

                          Steven,

                           

                          Thanks for your response but still this issue is there.How to give the jndi reference to support olap4j?

                           

                          Could u pls provide me any reference data source for connecting to the cubing server using the olap4j driver.

                           

                          Thanks & Regards

                          Latha

                          • 10. Re: connect to the cubing services using the teiid designer
                            shawkins

                            Latha,

                             

                            You would follow the information under docs/teiid/datasources/olap in the kit.  It has a readme describing the creation of the olap4j module and then provides example ds xmls for either an embedded mondrian connection or a remote xmla.  Is this what you initial followed?  Can you upload what you used as your -ds.xml file?

                             

                            Steve

                            • 11. Re: connect to the cubing services using the teiid designer
                              rareddy

                              Steve &Latha,

                               

                              I have updated the documentation and also made changes to Teiid kit's configuration to include the olap4j jar files. Please see new instructions and a sample VDB at https://github.com/teiid/teiid/tree/master/build/kits/jboss-as7/docs/teiid/datasources/olap

                               

                              Latha: if you use the next version of  Teiid 8.5 CR2, you will see these changes. However, you can still make it work with your version if follow the instructions and updated files above. BTW, what type of analytics cube server are u trying to connect to?