This tutorial will provide instructions on how to install Mondrian, an Online Analytical Processing Analysis Server, with Tomcat and Hadoop in BigSQL and enable users to analyze large quantities of data in real-time.

1. First, you will need to download the following:

a) Download Mondrian 3.5.0 Developers Version from the path http://sourceforge.net/projects/mondrian/files/latest/download

b) Extract the downloaded file to a known location.

2. Next you will need to make the following changes to Tomcat in the BigSQL package

a) Verify Tomcat server running on the BigSQL.

   
    $ ./bigsql status

        port 5432   is busy for postgresql-9.3.0
        port 2181   is busy for zookeeper-3.4.5
        port 50070  is busy for hadoop-1.2.1
        port 60010  is busy for hbase-0.94.11
        port 9083   is busy for hive-0.11.0
        port 8080   is busy for tomcat-7.0.42

b) Navigate to the downloaded folder /mondrian-3.5.0/ in Terminal Window

c) Copy mondrian.war file from mondrian-3.5.0/lib/ to bigsql/tomcat/webapps/

d) Wait for the war file to expand and create /webapps/mondrian

e) Copy the JDBC driver file from bigsql/postgres/jdbc/ directory to the /bigsql/tomcat/webapps/mondrian/WEB-INF/lib/ directory.

3. Make the following changes to Mondrian Properties

Open the mondrian.properties file in bigsql/tomcat/webapps/mondrian/WEB-INF and change the following properties.

 Jdbc=jdbc:postgresql://localhost:5432/postgres?user=postgres&password=password;JdbcDrivers=org.postgresql.Driver; Catalog=/WEB-INF/queries/mondriansample.xml;

( mondriansample.xml is the schema file for your database model )

4. Make the following changes to Mondrian Schema File

Place the schema file mondriansample.xml at location bigsql/tomcat/webapps/mondrian/WEB-INF/queries/mondriansample.xml

mondriansample.xml

<Schema name="BigSQL Sample" description="Schema for BigSQL sample ">
    <Cube name="CustomerHistory" visible="true" cache="true" enabled="true">
        <Table name="customer_history" schema="example">
        </Table>
        <Dimension type="StandardDimension" visible="true" foreignKey="h_data" highCardinality="false" name="Histid">
            <Hierarchy name="Hierchy" visible="true" hasAll="true" primaryKey="hist_id">
                <Table name="customer_history" schema="example">
                </Table>
                <Level name="Level" visible="true" table="customer_history" column="h_d_id" type="String" uniqueMembers="false" levelType="Regular" 
                        hideMemberIf="Never">
                    <Property name="New Property 0" column="h_data" type="String">
                    </Property>
                </Level>
            </Hierarchy>
        </Dimension>
        <Measure name="Amount" column="h_data" datatype="Numeric" aggregator="distinct-count" visible="true">
        </Measure>
        <Measure name="Date" column="h_date" aggregator="distinct-count" visible="true">
        </Measure>
    </Cube>
</Schema>

5. Make the following changes to Mondrian JSP File

Edit Mondrian.JSP file to change the following:

i) Connection string

ii) CatalogUri

iii) MDX Query

mondrian.jsp ( Overwrite existing mondrian.jsp file with below )

<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %> 
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %> 
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %> 

<jp:mondrianQuery id="query01" jdbcDriver="org.postgresql.Driver" jdbcUrl="jdbc:postgresql://localhost:5432/postgres?user=postgres&#38;password=password" 
    catalogUri="/WEB-INF/queries/mondriansample.xml" jdbcUser="postgres" jdbcPassword="password" connectionPooling="false"> 

SELECT

    {[Measures].[Amount],[Measures].[Date]} ON COLUMNS

FROM [CustomerHistory]

</jp:mondrianQuery>

<c:set var="title01" scope="session">Test Query uses Mondrian OLAP</c:set>

6. Make the following changes to Data Source

Replace datasource.xml configuration file which resides in bigsql/webapps/mondrian/WEB-INF/ folder with the below (datasource contains configuration for datasources and catalogs).

Datasource.xml
<DataSources> 
    <DataSource> 
        <DataSourceName>Provider=Mondrian;DataSource=postgres;</DataSourceName> 
        <DataSourceDescription>Mondrian Report For MyApp</DataSourceDescription> 
        <URL>http://localhost:8080/mondrian/xmla</URL> 
        <DataSourceInfo>Provider=mondrian;jdbc:postgresql://localhost:5432/postgres;
JdbcUser=postgres;JdbcPassword=password;JdbcDrivers=org.postgresql.Driver;Catalog=/WEB-INF/queries/mondriansample.xml</DataSourceInfo> 
        <ProviderName>Mondrian</ProviderName> 
        <ProviderType>MDP</ProviderType> 
        <AuthenticationMode>Unauthenticated</AuthenticationMode> 
        <Catalogs> 
              <Catalog name="postgres"> 
                    <!-- 
                    <DataSourceInfo>Provider=mondrian;Jdbc=jdbc:postgresql://localhost:5432/postgres;JdbcUser=postgres;JdbcPassword=password; 
                      JdbcDrivers=org.postgresql.Driver</DataSourceInfo> 
                    -->  
                <Definition>/WEB-INF/queries/mondriansample.xml</Definition> 
              </Catalog> 
        </Catalogs> 
    </DataSource> 
</DataSources>

7. The last step is to Restart BigSQL package.

   $ ./bigsql restart

8. Now, you will be able to view the mondrian test page on your browser

a) Open browser and enter: http://localhost:8080/mondrian and click on JPivot pivot table

b) Click Show Chart button below “Test Query uses Mondrian OLAP” text to view slices.