How to Build OLAP Application Using Mondrian + XMLA + SpagoBI? (3)

5. Config XMLAEngine to Access OLAP Server via XMLA

On previous section, we have deployed OLAP schema file on Mondrian OLAP server and tested the OLAP cube from XMLA interface.

At this section, we will config the OLAP schema on SpagoBI environment to enable the OLAP front-end tool (SpagoBI) to perform OLAP analysis against Mondrian OLAP server.

1) Install and config the SpagoBI XMLAEngine; for detail information, please refer to the document about XMLAEngine.

2) Edit the file "engine-config.xml" (under: TOMCAT_HOME/webapps/SpagoBIXmlaEngine/WEBINF/classes/). Make it looks like:

<ENGINE-CONFIGURATION>

<CONNECTIONS-CONFIGURATION defaultConnectionName="mondrianXMLA" >    
<CONNECTION name="mondrianXMLA"  type="xmla"
 xmlaServerUrl="http://localhost:8080/mondrian/xmla" />
</CONNECTIONS-CONFIGURATION>
   
    ...

<SCHEMAS>
<SCHEMA catalogUri="/WEB-INF/queries/SalesHistory.xml" name="SalesHistory" />
</SCHEMAS>

</ENGINE-CONFIGURATION>

3) Save the changes and re-start the Tomcat server;

4) To test that the "SalesHistory" OLAP schema on Mondrian server is visible from SpagoBI environment via XMLA interface, modify the file "xmla.jsp" under: TOMCAT_HOME/webapps/SpagoBIJPXMLAEngine/WEB-INF/queries;

Replace the MDX query with the following one:

SELECT {[Measures].[Quantity Sold], [Measures].[Amount Sold]} ON columns,
Hierarchize({([Gender].[All Genders], [Location].[All Locations])}) ON rows FROM [Sales]
WHERE ([Time].[All Times])

Save the change, and click the following URL:

http://localhost:8080/SpagoBIJPXMLAEngine/testpage.jsp?query=xmla

We should see the same display as:



6. Build OLAP Document from SpagoBI

We have configured and confirmed that everything is working so far. The last thing to do is to create an OLAP document from SpagoBI dashboard. This OLAP document will be used by users to perform OLAP analysis against the OLAP cube (and data warehouse) we defined before.

1) Insert an OLAP document by clicking the "Insert" button:


2) Config the document detail page as shown below:


3) Save the document, and click the "Template Build" button to config & save a template file:

4) Save the template (or add parameters if need):

5) Execute the newly build OLAP document (application).


(Complete)

How to Build OLAP Application Using Mondrian + XMLA + SpagoBI? (2)

3. Design Aggregation Tables

When the fact table contains huge number of rows, then the query to calculate at higher dimension levels will take longer time, because of the need to scan full dataset; another reason is with how ROLAP works, without aggreation (pre-calculation), ROLAP will read detail data from database tables and calculate on the fly.

To solve the performance issue with huge fact tables, we can build aggregate tables which contain pre-calculated summary data. The build of aggregate tables should be a part of the ETL process that populate / refresh the data warehouse.

We will use "Aggregation Designer" to help design the aggregate tables.

1) Start "Pentaho Aggregation Designer"; (can be downloaded from http://mondrian.pentaho.com)

2) Config the "Database Properties"; In this sample, we use Oracle database.

3) Connect to Data Source, using the OLAP schema file that we created before, as shown below:



4) then click "Connect" button. Once the schema has been consumed and parsed, the Aggregation Designer performs a series of validations to ensure the database is suitable for creation and the validation result is displayed.

5) we can use "Aggregation Designer" in two ways - Manual or Automated (Advisor). For demo purpose, I manually created two Aggregations.



6) to export the aggregation design, click "Export" button.
From the "Export and Publish" window, we can Preview the scripts for creating the aggregation tables; Create the aggregation tables; or Populate the aggregation tables; or Export (save) the Mondrian Schema file (with definition for aggregations) for deploy on Mondrian OLAP server.





4. Deploy Schema File in Mondrian

Suppose that Mondrian OLAP server is setup. (for how to install & config Mondrian OLAP server, please see my other article.)

1) Put the schema file "SalesHistory.xml" under: TOMCAT_HOME/webapps/mondrian/WEB-INF/queries;

2) Create file "datasources.xml" under: TOMCAT_HOME/webapps/mondrian/WEB-INF. This file may look like this:

<DataSources>
<DataSource>
    <DataSourceName>Provider=Mondrian;DataSource=MondrianFoodMart;</DataSourceName>

    <DataSourceDescription>Mondrian FoodMart Data Warehouse</DataSourceDescription>

    <URL>http://localhost:8080/mondrian/xmla</URL>

    <DataSourceInfo>Provider=mondrian;Jdbc=jdbc:oracle:thin:@localhost:1521:orcl1;
            JdbcUser=sh;JdbcPassword=xxx;JdbcDrivers=oracle.jdbc.OracleDriver;
            Catalog=/WEB-INF/queries/SalesHistory.xml
     </DataSourceInfo>

    <ProviderName>Mondrian</ProviderName>

    <ProviderType>MDP</ProviderType>

    <AuthenticationMode>Unauthenticated</AuthenticationMode>

    <Catalogs>

        <Catalog name="SalesHistory">
             <DataSourceInfo>Provider=mondrian;Jdbc=jdbc:oracle:thin:@localhost:1521:orcl1;
                  JdbcUser=sh;JdbcPassword=xxx;JdbcDrivers=oracle.jdbc.OracleDriver;
                  Catalog=/WEB-INF/queries/SalesHistory.xml
             </DataSourceInfo>
             <Definition>/WEB-INF/queries/SalesHistory.xml</Definition>
        </Catalog>

     </Catalogs>
</DataSource>
</DataSources>

3) modify the file "mondrian.properties" under: TOMCAT_HOME/webapps/mondrian/WEB-INF/classes

Add the following two lines to allow the use of aggregates:

mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true

4) re-start the Tomcat server;

5) to test that the deployed OLAP schema is working, modify the file "xmla.jsp" under: TOMCAT_HOME/webapps/mondrian/WEB-INF/queries;

Replace the MDX query with the following one:

SELECT {[Measures].[Quantity Sold], [Measures].[Amount Sold]} ON columns,
Hierarchize({([Gender].[All Genders], [Location].[All Locations])}) ON rows FROM [Sales]
WHERE ([Time].[All Times])

Save the change, and click the following:

http://localhost:8080/mondrian/testpage.jsp?query=xmla



(to be continued)

How to Build OLAP Application Using Mondrian + XMLA + SpagoBI? (1)

Mondrian is an OLAP (Online Analytical Processing) server that enables business users to analyze large quantities of data in real-time. Users explore business data by drilling into and cross-tabulating information with speed-of-thought response times to complex analytical queries.

XMLA (XML for Analysis) is the industry standard for data access in analytical systems, such as OLAP and Data Mining. XMLA is based on other industry standards such as XML, SOAP and HTTP. In this tutorial, we use Mondrian OLAP server as XMLA provider.

SpagoBI use SpagoBIXMLAEngine to query external XMLA compliant server like Mondrian OLAP server.

This tutorial demonstrate the steps to build the OLAP application using Mondrian OLAP server as XMLA provider and SpagoBI as reporting frontend.


Environment
============
SpagoBI: version 2.6
Mondrian: 3.2.0.13661
Schema Workbench: 3.2.0
Aggregation Designer: 1.2.0
Database: Oracle 11g R2 + SH (Sales History) schema
Java: JDK 1.6
App Server: Tomcat 6.0.28


1. Prepare Data Warehouse

We use the Oracle sample database SH (Sales History) as our data warehouse.
The SH tables are in snowflake schema, as shown below:




2. Build OLAP Model

In Mondrian, we use schema file (in XML format) to define the OLAP model. The schema file contains a logical model, consisting of cubes, hierarchies, and members, and a mapping of this model onto a physical model.

There are two ways to build the schema file: 1) by using a text editor; or 2) by using schema workbench. In this demonstration, We use schema workbench to build the OLAP logical model. To learn more about the Mondrian schema syntax and how to use Mondrian Schema Workbench, please goto http://mondrian.pentaho.com




We can test the above schema by executing MDX query:
First, start the "MDX Query" window:



Then, test the OLAP schema as shown:



Finally, save the schema file as "SalesHistory.xml" and exit Schema Workbench.

Below is the detail schema file:

<Schema name="SalesHistory">
 <Cube name="Sales" cache="true" enabled="true">
   <Table name="SALES" schema="SH">
   </Table>
   <Dimension type="StandardDimension" foreignKey="CUST_ID" name="Gender">
     <Hierarchy hasAll="true" allMemberName="All Genders" primaryKey="CUST_ID">
       <Table name="CUSTOMERS" schema="SH">
       </Table>
       <Level name="Gender" column="CUST_GENDER" type="String"
              uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
       </Level>
     </Hierarchy>
   </Dimension>
   <Dimension type="TimeDimension" foreignKey="TIME_ID" name="Time">
     <Hierarchy hasAll="true" primaryKey="TIME_ID">
       <Table name="TIMES" schema="SH">
       </Table>
       <Level name="Year" column="CALENDAR_YEAR" type="Integer" uniqueMembers="true"
              levelType="TimeYears" hideMemberIf="Never">
       </Level>
       <Level name="Quarter" column="CALENDAR_QUARTER_NUMBER" type="Integer"
              uniqueMembers="false" levelType="TimeQuarters">
       </Level>
       <Level name="Month" column="CALENDAR_MONTH_NUMBER" uniqueMembers="false"
              type="Integer" levelType="TimeMonths" hideMemberIf="Never">
       </Level>
       <Level name="Day" column="DAYS_IN_CAL_MONTH" type="Integer"
              uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
       </Level>
     </Hierarchy>
   </Dimension>
   <Dimension type="StandardDimension" foreignKey="CUST_ID" name="Location">
     <Hierarchy hasAll="true" primaryKey="CUST_ID" primaryKeyTable="CUSTOMERS">
       <Join leftKey="COUNTRY_ID" rightKey="COUNTRY_ID">
         <Table name="CUSTOMERS" schema="SH">
         </Table>
         <Table name="COUNTRIES" schema="SH">
         </Table>
       </Join>
       <Level name="Country" table="COUNTRIES" column="COUNTRY_NAME" type="String"
              uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
       </Level>
       <Level name="State" table="CUSTOMERS" column="CUST_STATE_PROVINCE" type="String"
              uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
       </Level>
       <Level name="City" table="CUSTOMERS" column="CUST_CITY" type="String"
              uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
       </Level>
     </Hierarchy>
   </Dimension>
   <Measure name="Quantity Sold" column="QUANTITY_SOLD" datatype="Numeric"
            formatString="#,###" aggregator="sum" visible="true">
   </Measure>
   <Measure name="Amount Sold" column="AMOUNT_SOLD" datatype="Numeric"
            formatString="#,###.##" aggregator="sum" visible="true">
   </Measure>
 </Cube>
</Schema>

(to be continued)