-
15. Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service and read OLAP Data
rareddy Nov 2, 2014 11:22 AM (in response to abedini)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 Nov 9, 2014 12:09 AM (in response to rareddy)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 Nov 10, 2014 1:37 AM (in response to 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 Nov 10, 2014 9:40 AM (in response to abedini)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 Nov 11, 2014 2:32 AM (in response to rareddy)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:
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 :
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 Nov 11, 2014 4:12 AM (in response to rareddy)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:
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 Nov 11, 2014 11:23 AM (in response to abedini)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 Nov 11, 2014 11:27 PM (in response to abedini)part2
Then run server and :
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 Nov 11, 2014 11:31 PM (in response to abedini)see the sample foodmart-vdb.xml for example MDX. No provider needed.
-
25. Re: Re: Re: Re: Re: HOW can I using of Teiid for Connect to Microsoft Analysis service and read OLAP Data
rareddy Nov 19, 2014 12:22 PM (in response to abedini)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 Nov 26, 2014 5:06 AM (in response to rareddy)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 Nov 26, 2014 11:16 AM (in response to abedini)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..