SpagoBI JPXMLA Engine

SpagoBIXMLAEngine is based on the popular JPivot Olap client. It can be used to query an external XMLA compliant server like MSAS or ESBASE.

XMLAServer

In order to use the SpagoBIXMLAEngine you need to have already installed a working instance of an olap server capable of expose its API through an XMLA interface like MSAS, ESBASE or Mondrian Olap server. In order to install and configure your target Olap server please refer directly to the documentation provided by your server vendor. Here after is briefly explained how to configure Mondrian server over tomcat in order to make it work as an XMLA compliant olap server that can be queried by SpagoBIXMLAEngine.

  • Download the latest binary release mondrian-version.zip from SourceForge, and unzip it
  • From the unzipped binary release, explode lib/mondrian.war to TOMCAT_HOME/webapps/mondrian of your tomcat server (version 5.0.25 or later)
  • Create or open the mondrian.properties file in TOMCAT_HOME/webapps/mondrian and customize the mondrian.jdbcDrivers properties for your target database
  • Open the web.xml file in TOMCAT_HOME/webapps/mondrian/WEB-INF and customize the two connect strings there to the same database parameters for the FoodMart database you installed following the instructions above . That is,
Provider=mondrian;Jdbc=jdbc:odbc:MondrianFoodMart;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=sun.jdbc.odbc.JdbcOdbcDriver;

becomes

Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=com.mysql.jdbc.Driver;

  • Create a mondrian schema that maps your phisycal relational schema to the dimensional one used by mondrian. You can do it by hand, starting from an existing schema and than modifying it properly, or using some graphical design tool.
  • Add your mondrian schemas under TOMCAT_HOME/webapps/mondrian/WEB-INF/queries
  • TOMCAT_HOME/webapps/mondrian/WEB-INF create a file called datasources.xml, with content like this:
<?xml version="1.0"?>
<DataSources>
  <DataSource>
    <DataSourceName>MondrianFoodMart</DataSourceName>
    <DataSourceDescription>FoodMart 2000 Data Warehouse From MS Analysis Services</DataSourceDescription>
    <URL>http://localhost:8080/mondrian/xmla</URL>
    <DataSourceInfo>Provider=mondrian; Jdbc=jdbc:odbc:MondrianFoodMart; JdbcDrivers=sun.jdbc.odbc.JdbcOdbcDriver</DataSourceInfo>
    <ProviderType>MDP</ProviderType>
    <AuthenticationMode>Unauthenticated</AuthenticationMode>
    <Catalogs>
        <Catalog name="FoodMart">
            <Definition>/WEB-INF/schema/FoodMart.xml</Definition>
        </Catalog>
        <Catalog name="Marketing">
            <DataSourceInfo>Provider=mondrian; Jdbc=jdbc:odbc:MarketingDB; JdbcDrivers=sun.jdbc.odbc.JdbcOdbcDriver</DataSourceInfo>
            <Definition>/WEB-INF/schema/Marketing.xml</Definition>
        </Catalog>
    </Catalogs>
  </DataSource>

  <DataSource>
    <DataSourceName>PostgreSQLTest</DataSourceName>
    <DataSourceDescription>Test Data Warehouse On PostgreSQL</DataSourceDescription>
    <URL>http://localhost:8080/mondrian/xmla</URL>
    <DataSourceInfo>Provider=mondrian; Jdbc=jdbc:postgresql://localhost/olap; JdbcDrivers=org.postgresql.Driver; JdbcUser=pgsql; JdbcPassword=pgsql</DataSourceInfo>
    <ProviderName>Mondrian</ProviderName>
    <ProviderType>MDP</ProviderType>
    <AuthenticationMode>Unauthenticated</AuthenticationMode>
    <Catalogs>
        <Catalog name="Test">
            <Definition>/WEB-INF/schema/TestPgsql.xml</Definition>
        <Catalog>
    </Catalogs>
  </DataSource>
</DataSources>

ProviderType and AuthenticationMode are ignored because the XML/A implementation provided by Mondrian server currently only supports 'MDP' and 'Unauthenticated'.

The name of each catalogs defined in datasources.xml must match the name attribute in the respective schema file and must be unique

  • add or un-comment the following block into web.xml
<servlet>
  <servlet-name>MondrianXmlaServlet</servlet-name>
  <servlet-class>mondrian.xmla.impl.DefaultXmlaServlet</servlet-class>
</servlet>

  • Copy the following files:
    • If needed, copy your JDBC driver JAR to TOMCAT_HOME/common/endorsed
    • Copy xalan.jar to TOMCAT_HOME/common/endorsed
  • Start your database, if needed.
  • Start Tomcat.
  • Hit http://localhost:8080/mondrian
XMLAEngine

  • Download the SpagoBIJPXMLAEngine-bin-latest from here and unzip it
  • Copy the file SpagoBIJPXMLAEngine.war under TOMCAT_HOME/webapps
  • Add the SpagoBIXmlaEngine context in file TOMCAT_HOME/conf/server.xml
<Context docBase="SpagoBIJPXMLAEngine" path="/SpagoBIJPXMLAEngine" reloadable="true" source="org.eclipse.jst.j2ee.server:SpagoBIJPXMLAEngine">
      <ResourceLink global="spagobi_resource_path" name="spagobi_resource_path" type="java.lang.String"/>
        <ResourceLink global="spagobi_sso_class" name="spagobi_sso_class" type="java.lang.String"/>
        <ResourceLink global="spagobi_service_url" name="spagobi_service_url" type="java.lang.String"/>
      </Context>

  • Add to file TOMCAT_HOME/webapps/SpagoBIXmlaEngine/WEBINF/classes/engine-config.xml file the newly created schema in the SCHEMAS configuration block as shown down here
<SCHEMAS>	  
  <SCHEMA catalogUri="/WEB-INF/queries/YourSchemaFileName.xml" name="YourSchemaName" />
</SCHEMAS>

and add also the connection to the mondrian engine:

<CONNECTIONS-CONFIGURATION defaultConnectionName="FoodmartXMLA">
    	<CONNECTION name="FoodmartXMLA" type="xmla" xmlaServerUrl="http://host:port/mondrian/xmla"/>
</CONNECTIONS-CONFIGURATION>

  • Restart the server an create a new OLAP document by choosing the SpagoBIXmlaEngine
  • In the document detail page use the template builder to automatically create the template for the given document
olapdoc.png

In olap engine the template specifies the target schema to use, the selected cube inside the target schema and the starting MDX query to exucute on the selected cube;

here it is an example of the template generated by the wizard

olaptemplate.png

  • Execute the newly created olap document
Tags:
Created by cchiarel on 2009/09/04 16:49
Last modified by Alessandra Toninelli on 2013/11/08 16:04

This wiki is licensed under a Creative Commons 2.0 license
XWiki Enterprise 2.7.33694 - Documentation