30 April, 2008

JPivot + MS Analysis 2000

Introduction

JPivot is one of rich features OLAP clients in the market that comes up with web based interface. Its capability to run in multiple platform and compliant with XMLA standard as its data source make JPivot a viable and affordable solution for every organizations that already implemented XMLA OLAP server but needed more robust client solution.

MS SQL Server 2000 and MS 2000 Analysis Server is one of the mostly wide used database and OLAP server in enterprise market. And since Microsoft is also the initiator in this XMLA technology, it is not surprising that MS Analysis Server also support the standard.

But the drawback is that both products don't come out with any OLAP client. Hence, since JPivot is XMLA compliant we can use it nicely to fill the gap.

Setup IIS and MS 2000 Analysis Server

First you need to download Microsoft XMLA sdk and set it up with IIS and MS Analysis Server. I will not go through this step as you can see more detailed instructions in this MSDN article.

For my working sample I use XMLA 1.1 SDK version. And if you have installed, configured and running all the things correctly then try browse to msxisapi.dll from your XMLA virtual host. In my case my virtual directory name is set to xmla and located in my local desktop so I'll browse to http://localhost/xmla/msxisapi.dll and should see result like the picture below


Figure 1: URL Testing for SQL Server 2000 XMLA Implementation

or if you are using Firefox you may get result like in figure 2.


Figure 2: URL Testing for SQL Server 2000 XMLA Implementation (Firefox)


Figure 3 is a screenshot of my IIS config for this xmla virtual directory.



Figure 3: IIS xmla Virtual Directory


And for your complete reference this is the list of prerequisite applications that I use :
  • Windows XP Professional SP2
  • Microsoft Internet Information Services 5.1 that comes with Windows XP
  • Microsoft SQL 2000 SP4 with Microsoft Analysis installed
  • Java Development Kit 1.6
  • Apache Tomcat 5.5.17
  • JPivot 1.8
JPivot Query File

Now you can create a flat jsp file named salesCube.jsp and put it into your $JPIVOT/WEB-INF/queries folder. In my case the folder is located in C:\apache-tomcat-5.5.17\webapps\jpivot\WEB-INF\queries.

Listing 1 : salesCube.jsp
<%@ 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" %>


select
{[Measures].[Unit Sales], [Measures].[Store Cost]} on columns,
{[Customer].[All Customer]} ON rows
from Sales


MS Analysis Services Cube: Sales (via XMLA)

Pay attention to above listing for uri and catalog attribute of jp:xmlaQuery tag. For uri we will use the msixisapi.dll reference url that we already tested before. And for catalog I'm using Tutorial which is Ms Analysis Server catalog for Foodmart database sample. Make sure that in this example you already have setup Sales cube with Unit Sales and Store Cost measures and Customer dimension.

Get the result by executing http://localhost:8080/jpivot/testpage.jsp?query=salesCube in your browser. If everything working fine you will get the result like figure 4 and figure 5 below. Enjoy the great features of JPivot including drill down option and graphichal cart generation.



Figure 4: JPivot with Axis in hierarchical mode


Figure 5: JPivot with Generated Bar Chart


Conclusion

JPivot is an OLAP web based client that can work perfectly with XMLA standard OLAP provider like Microsoft Analysis Server 2000. You will need an additional installation of Microsoft XMLA SDK and a virtual directory IIS configuration to make it work.

Here I have shown how you can consume MS Analysis's OLAP source using XMLA with a standard JPivot installation. Create a jsp file, specify your jp:xmlaQuery tag's attributes and specify an MDX query. You now have all the rich features of JPivot.




Online Resources

Mondrian as XMLA Provider

Mondrian is an open source Java OLAP server under Pentaho. One of its great feature is that it can be act as XML/A (XML for Analysis) server that can be consumed by a number of independent XML/A compliant clients.

From Mondrian's documentation I find it quite simple to set up Mondrian as XMLA provider. But I didn't find any adequate information on how to test it directly from Mondrian's own bundled tool.

For any beginner, it will be hard and time consuming to find out whether the provider is already and rightly set up.

I write this article in order to help those with that problem, complementing Mondrian's own documentation .

Setup

Open your /WEB-INF/datasources.xml file and add a data source :

<?xml version="1.0"?>
<DataSources>
<DataSource>
<DataSourceName>
Provider=Mondrian;DataSource=MySQLTest;</DataSourceName>
<DataSourceDescription>Mondrian FoodMart Data
Warehouse</DataSourceDescription>
<URL>http://localhost:8080/mondrian/xmla</URL>
<DataSourceInfo>
Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=root;JdbcPassword=;JdbcDrivers=com.mysql.jdbc.Driver;</DataSourceInfo>
<ProviderName>Mondrian</ProviderName>
<ProviderType>MDP</ProviderType>
<AuthenticationMode>Unauthenticated</AuthenticationMode>
<Catalogs>
<Catalog name="FoodMart">
<Definition>/WEB-INF/queries/FoodMart.xml</Definition>
</Catalog>
</Catalogs>
</DataSource>
</DataSources>


DataSourceName tag

Although DataSourceName tag can have entry of any name - which can be detected by Mondrian form test - but we will use the "Provider=[value];DataSource=[value]" format instead.

Connection String

Since I use MySQL as my cube's storage that I setup before, so in this particular line I specified connection detail to the server. I specified in the JDBC's URL my host (localhost), database schema (foodmart_mondrian), user (root) with no password and the the driver class itself (com.mysql.jdbc.Driver).

Validate Data Source

I assume that you already have Mondrian running under Tomcat in your local computer. Try browse to http://localhost:8080/mondrian/xmlaTest.jsp. Select "1.discoverDataSource" option from the combo list then click "show request" button until you see the XMLA construct like below.

After that, you might proceed by clicking "Run" to see if your XMLA provider - specified in your DataSourceName - is recognized.

A screen shot below indicated that Mondrian XMLA provider is recognized and ready to be sent any OLAP/MDX query.

Query Testing

Back to your XMLA test page, select "2. executeHR-tabular option list. Click on "Show Request" button.

Once you see the XMLA generated construct you need to modify one thing: DataSourceInfo entry.


Change the tag's value of "DataSource=MondrianFoodMart;" into "DataSource=MySQLTest;". Continue by clicking on "run" button.



If all going right you will have a returning XML SOAP result like below. This means that Mondrian has successfully executed your query.































<_x005b_measures_x005d_._x005b_org_x0020_salary_x005d_ type="xsd:double">
39431.6712




<_x005b_employees_x005d_._x005b_employee_x0020_id_x005d_._x005b_member_caption_x005d_>
Sheri Nowmer

<_x005b_measures_x005d_._x005b_org_x0020_salary_x005d_ type="xsd:double">
39431.6712









Conclusion

Setup Mondrian as XMLA provider is easy and quite straightforward.

However, in order to test it we sometimes at a lost. Since we don't have any adequate information provided with the bundled documentation. This article will serve as a complement for that.

Hope you find this blog entry helpful. Any comment or suggestion to improve this article is highly appreciated.


29 April, 2008

Mondrian + MySQL Setup

Introduction

Mondrian is a very popular open source OLAP Server and is also one of the products that are very oftenly asked in several discussion forums. One of those including installation and initial configuration issues.

In this article I will try to guide step by step on how to get Mondrian installed and configured using MySQL community database server and Windows XP as its operating system platform.

Prerequisites

These are the applications that are needed before you can using Mondrian, follow on the link for more information on how to setup each application :



MySQL Foodmart Database Sample


Mondrian provided Foodmart database sample in MS Access format. But I have provided a full generated MySQL script populate Foodmart so you use it in MySQL Community database server. You can download the tar.gz compressed file from my wiki site (pictured).



Extract the script file using your favorite compression utility - 7zip is the one I would highly recommend.

Create a database and execute the script. Here I show you a sample commands to create a Foodmart database - assuming that your mysql is running and your mysql.exe client is located in C:\mysql\bin with your previously extracted script file in C:\foodmart_mysql.sql.

Open command line prompt (Start | Run | type cmd | click OK)

cd C:\mysql\bin
mysql.exe -uroot -e "create database Foodmart"
mysql.exe -uroot Foodmart < C:\foodmart_mysql.sql
mysql.exe -uroot Foodmart -e "show tables"



You should see result like below if all running well...

+-------------------------------+
| Tables_in_foodmart            |
+-------------------------------+
| account                       |
| agg_c_10_sales_fact_1997      |
| agg_c_14_sales_fact_1997      |
| agg_c_special_sales_fact_1997 |
| agg_g_ms_pcat_sales_fact_1997 |
| agg_l_03_sales_fact_1997      |
| agg_l_04_sales_fact_1997      |
| agg_l_05_sales_fact_1997      |
| agg_lc_06_sales_fact_1997     |
| agg_lc_100_sales_fact_1997    |
| agg_ll_01_sales_fact_1997     |
| agg_pl_01_sales_fact_1997     |
| category                      |
| currency                      |
| customer                      |
| days                          |
| department                    |
| employee                      |
| employee_closure              |
| expense_fact                  |
| inventory_fact_1997           |
| inventory_fact_1998           |
| position                      |
| product                       |
| product_class                 |
| promotion                     |
| region                        |
| reserve_employee              |
| salary                        |
| sales_fact_1997               |
| sales_fact_1998               |
| sales_fact_dec_1998           |
| store                         |
| store_ragged                  |
| time_by_day                   |
| warehouse                     |
| warehouse_class               |
+-------------------------------+


Configuring and Running Tomcat

You will need to copy mysql driver, i.e. mysql-connector-java-5.1.6-bin.jar file to your Tomcat's library folder.
  • Extract your MySQL zipped driver file you have downloaded before to get the jar file
  • Copy the jar driver to C:\apache-tomcat-5.5.17\common\lib folder - which is the location for any jar drivers used in our web application. For version 6 of tomcat you just put it in APACHE_TOMCAT/lib folder

Run your server by typing "catalina run" in Tomcat's bin folder.
cd c:\apache-tomcat-5.5.17\bin
catalina run


Check your running server by browsing into http://localhost:8080. If everything works fine, our next step is to deploy Mondrian web application.

Deploying Mondrian

Extract mondrian distribution package and goto lib directory. You will find mondrian.war file there.
Copy the war file into your Tomcat's webapps directory while Tomcat is still running. The war file will be extracted and deployed into Tomcat automatically.






Browse into your http://localhost:8080/mondrian and you shall get a Mondrian welcome page like below.




Configure JSP File


Browse into Mondrian's application queries folder where you can find several sample jsp files. Here I open my C:\apache-tomcat-5.5.17\webapps\mondrian\WEB-INF\queries folder and find the following jsp files :
  • fourheir.jsp
  • mondrian.jsp
  • colors.jsp
  • arrows.jsp

Edit those files using your favorite text editor, and change following line :


<jp:mondrianQuery id="query01"
jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver"
jdbcUrl="jdbc:odbc:MondrianFoodMart"
catalogUri="/WEB-INF/queries/FoodMart.xml">

..... this is your existing mdx query ....

</jp:mondrianquery>


into your MySQL database configuration. For example ...

<jp:mondrianQuery id="query01"
jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/Foodmart?user=root&password="
catalogUri="/WEB-INF/queries/FoodMart.xml">
 
..... this is your existing mdx query ....
 
</jp:mondrianQuery>




Browse Your Multi Dimensional Data

Now you are ready to to play with JPivot. Browse into your local mondrian host address : http://localhost:8080/mondrian, choose the first mondrian examples "JPivot pivot table".

It will take some time as Mondrian will need to populate its initial cache before the view shows up.

You will then see a table which you can drill through in a hierarchical way and a heading toolbar which comprises of configuration icons.


Congratulations, your Mondrian with MySQL database has been successfully setup !

Updated : More on Mondrian Sample's Configuration

To find more about configuring each sample to work well with above installation please refer to our wiki page at http://pentaho-en.phi-integration.com/mondrian/configuring-mondrian-sample.