Introduction to Mondrian OLAP Schemaby Yogaraj khanal on December 4th, 2011IntroductionThis article demonstrates how do you C An design Mondrian OLAP schema using schema Workbench. I am using MySQL database for this article purpose. Backgroundmondrian in Java based OLAP engine which executes MDX queries from RDBMS. Detailin This article I am showing if you can create a basic Mondrian schema using schemas Workbench by connecting to MySQL RDBMS and show the data using Saiku as plug-in in Pentaho BI Suite which works on this schema. Also I'll show you what to publish the schema in the series of steps.
Prerequisite for this article:
MySQL Database and Query Browser (optional) which can be freely downloaded from here:
http://dev.mysql.com/downloads/mysql/
Http://dev.mysql.com/downloads/gui-tools/5.0.html
Use this DDL to create a database and a table to being used for the purpose of this tutorial
CREATE database Mondrian; Use Mondrian; ( ID INT not NULL auto_increment PRIMARY KEY, name varchar(+), address varchar(
100
), data VARCHAR()ENGINE=InnoDB;
Insert some data and verify that they is there using these DML statements
INSERT into Mondrian(name,address,data) values(' Yogi ',' USA ' ,' Mondrian article '); *from Mondrian;
I am Keeping database this, and clean for this article purpose.
Now let's get into the Schema Workbench part. I am using the latest stable build to date version 3.3.0 for this article. Download the latest stable build of the Schema Workbench from here:
Http://wiki.pentaho.com/display/COM/Latest+Stable+Builds
http://sourceforge.net/projects/mondrian/files/schema%20workbench/
Download and extract put the drivers for the database. I have the three drivers here:
MySQL driver is required for this tutorial. This can is downloaded from bottom part of this article where I have provided distribution of the MySQL JDBC driver, SQL s Cript and actual schema that can import from Schema Workbench and viewed.
This-screen can is viewed from by clicking Options->connection providing your credentials-connect to database and CL Icking on the test button shown above.
You can does the FILE->NEW->JDBC Explorer and expand the tree to view your table as shown above on the screen shot.
In the screens above I did File->new->schema and gave a Schema a name Mondrians.
Also Notice the * on the first highlight from the top indicating so you had to save it.
Right click and to Add the cube and give it a name in this case Mondrianc.
In this screen I do right click on the cube and added the table that we just created and connected which should is shown In drop under the name section if you did everything correctly.
In this screen I has added a dimension called name and gave it nameD corresponding to our table column name. Also hierarchy is Also added by default which I gave name called Nameh. IF you notice the error in the bottom of the page that says ' at least one level must is set for Hierarchy Go to add level you'll see it grayed out and the work around are posted in this post which took me That's limitation of Schema Workbench. So let's try the work around shown below.
http://jira.pentaho.com/browse/MONDRIAN-662
Let's first add a table before adding the level and then lets delete the table as shown on screen shot below.
The points to "this" screen was the yellow highlighted column name is required field which should being available in DRO P down. Also i has deleted the temporary table I added in the screen shot above.
I have repeated the same steps explained above to add II more dimension ADDRESSD and Datad that corresponds to the column In our database we created.
The last important thing, that's required to add is measure. So let ' s go ahead and add it.
You have to right click on the cube to add it. Yellow highlighted part shows it required field that should is available from drop down.
Now let's save our schema somewhere and the run basic MDX query to make sure it have no errors in it. FILE->NEW->MDX Query
If you followed everything you should is able to see no error like in the screen above.
Observer that is selecting the dimension one on ROWS and one on the columns from a Cube.
Now it's time to publish our schemas in Pentaho BI and use Saiku plugin to view the data.
Please follow this article the "How to" install Saiku in Pentaho BI.
Http://codeissue.com/articles/a04e87158bb8552/pentaho-bi-ctools-cdf-cda-cde-saiku-analytics-etc-using-cygwin
You have a click Schema then go to file->publish to get the screen to Publish the schema as shown on screen shot below.
I assumed that is running Pentaho on localhost in port 8080 and using default Pentaho test login Joe/password if not Use a PAC to set and adjust the information accordingly.
You have to set the password
<pentaho_home>\biserver-ce\pentaho-solutions\system\publisher_config.xml and use same in Publish Password field Above.
You can-right-click in PUC-Create a new folder to place your published file. Let's create a folder name Mondrianschema folder to publish our schema which would be is created in Pentaho Solutions folder.
Let's create a folder name Mondrianschema folder to publish our schema which would be is created in Pentaho-solutions folder.
Yellow highlighted part shows our new folder, the We is going to the use to publish schema.
You should is able to browser to, the folder now from the Schema Workbench as shown on the screen shot above. See the second yellow highlight sections which we should correct first to publish the schema. You should has preexisting JNDI Data Source and has same exact name here to publish otherwise it won ' t like it. So let ' s go ahead and create one using PUC using option:
File->new->datasource
Let's select data to make sure we can see it from here.
Now we can use the this JNDI data Source name, we just created with confidence in the Schema Workbench to publish data. After clicking Close and Finish button make sure to save default model and restart the BI server to make sure that JNDI re Source exist in many cases it gives problem if you don ' t does so. So take it as required important step.
Here I am getting publishing error since I didn ' t start the Pentaho BI server running on Tomcat which you can see in scree N shot below.
Also I need to is inside the Mondrian Schema folder to publish it as shown on screen shot below.
In above screens I was outside of the folder and didn ' t restart the BI server that's why publishing filed I am documenting These steps here's show you all scenario.
If you those all the steps I do you should is able to see this message.
Now you can use Saiku in PUC to view the data as shown in screen shot below.
I hope this article servers The purpose of helping you learn creating OLAP schemas and short and simple and complete enough To grasp the concept and able to use Saiku in PUC to view your data.
Conclusionthis article demonstrates how can I build the basic Mondrian cube and publish it to the Pentaho BI server and Use Saiku plugin installed in it to view your data. Please send me your comments, suggestions and questions through this website. Referenceshttp://wiki.pentaho.com/display/com/latest+stable+builds
http://sourceforge.net/projects/mondrian/files/schema%20workbench/
http://dev.mysql.com/downloads/mysql/
Http://dev.mysql.com/downloads/gui-tools/5.0.html
Http://type-exit.org/adventures-with-open-source-bi/2010/07/creating-a-basic-mondrian-olap-cube/DownloadClick Here to download source code
"Turn" Pentaho Schame Workbench examples of getting Started
