Introduction

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 :

• Java Development Kit 5 or later
• Apache Tomcat
• MySQL 5
• MySQL 5 JDBC driver
• Windows XP Pro SP2 Edition

MySQL Foodmart Database Sample

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

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

cd C:mysqlbin
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

cd c:apache-tomcat-5.5.17bin
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.

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

Configure JSP File

• 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=rootpassword="
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.

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.

Article source: http://business-intelligence.phi-integration.com/2008/04/mondrian-mysql-setup.html

From Mondrian’s documentationI 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

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

DataSourceName tag

Connection String

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.

39431.6712


Sheri Nowmer


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.

Article source: http://business-intelligence.phi-integration.com/2008/04/testing-mondrian-as-xmla-provider.html

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 XMLAstandard 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

Listing 1 : salesCube.jsp

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.

Figure 4: JPivot with Axis in hierarchical mode

Figure 5: JPivot with Generated Bar Chart

Conclusion

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

• Julian Hyde Blog
• Javier Gimenez’s StrateBI

Article source: http://business-intelligence.phi-integration.com/2008/04/jpivot-and-microsoft-analysis-service.html

For this purpose I’ll create a series of article showing the use of this database with Mondrian and Kettle. To begin with, here I will show how to get this sample database, creating a very basic cube definition and how to query it – all without altering the underlying data.

See our complete wiki article here.

Article source: http://business-intelligence.phi-integration.com/2008/06/mondrian-mysql-sakilas-cube.html

Kettle (Pentaho Data Integration) is a very popular open source ETL tool. Built on Java platform, it can run in multiple OS such as Windows, Linux, Mac, and other Unix based platform.

Regular expression is a powerful construct to manipulate any text and is supported in Java language. With the capability of Regex evaluation step and Java scripting in Kettle, I give a simple example on how to read an “unstructured” log file and make it tabular using the steps.

Get the sample transformation file from this wiki page.

Article source: http://business-intelligence.phi-integration.com/2008/07/kettle-regex-sample.html

Introduction

MDX (Multidimensional Expression) is a language construct to query OLAP cubes. It is much like SQL is a language construct to query relational database.

MDX was an initiative of Microsoft as part of the OLE DB for OLAP (ODBO) specification in 1997. Mosha Pasumansky is one of the architects of the language. MDX was soon implemented by many OLAP vendors as their standard OLAP query language including Mondrian.

Example of simple MDX query is shown below :

select {[Measures].[Unit Sales]} ON COLUMNS,
{[Product].[All Products]} ON ROWS
from [Sales]
where [Time].[1997]

This will query from “Sales” cube a “Unit Sales” measure value from “All Products” dimension that happened in 1997. You can try it against Foodmart sample database.

MDX entry in Mondrian JPivot’s Sample

You can type your MDX query in JPivot JSP page in two ways :

• Type it under jp:mondrianQuery tag
• Type it under MDX editor

Using MDX Editor

• Start your Mondrian OLAP server
• Browse to one of your sample JSP file, for example “JPivot pivot table”
• Click on MDX button in the above page toolbar, it will show up an MDX Editor
• Type the previous sample MDX query here, and click Apply button
  
  
  
• You will now have a new slice and dice table view of that MDX query

More Resources

Check on the following web resources for more information about MDX :

• Mondrian’s MDX Documentation
• Mosha Pasumansky
• Microsoft MDX Reference
• MDX Calculation Sample
• Nicholas Goodmans’s Blog

Article source: http://business-intelligence.phi-integration.com/2008/08/mdx-in-mondrian.html

Hope this experience may help some of you who need to do the same thing as I did.

Feris Thia

Article source: http://business-intelligence.phi-integration.com/2008/08/ms-as-2000-and-mysql-odbc-problem.html

I use Calc – the spreadsheet application in the suite – extensively in my daily activities to analyze data subsets in data warehousing projects and proved to be very helpful. But the drawback of using this application is that you have to use it as it is – no point to have much complaints. But now I’m facing the stability and reliability issue that almost drive me nuts – an unstopable recovery process.

If you have a document that is recognized as a damaged one by OpenOffice you will be shown a dialog to recover it. And if you agree to recover it, almost only few of the times it behaves nicely .

But if you are unlucky enough then this process will take forever without any clue to be stopped in hand. I face it now… and even restarting my system several times didn’t bring any good. Then I got this helpful page by googling and it relief me from the problem.

If you have the same issue, try this :

• If you are on Windows environment, delete Documents and settings/username/Application data/Openoffice.org2/user/registry/data/org/openoffice/Office/Recovery.xcu file.
• And if you are a Linux user, delete /home/username/.openoffice.org2/user/registry/data/org/openoffice/Office/Recovery.xcu

Done.

Till next article…

Feris

Article source: http://business-intelligence.phi-integration.com/2008/08/openoffice-recovery-takes-forever.html

propinsi.csv

Kode_Propinsi,Deskripsi
P01,Bali
P02,Bengkulu
P03,Banten
P04,Gorontalo
P05,Irian Jaya Barat
P06,Papua
P07,Jambi
P08,Jawa Barat
P09,Jawa Tengah
P10,Jawa Timur
P11,Kalimantan Barat
P12,Kalimantan Tengah
P13,Kalimantan Timur
P14,Kalimantan Selatan
P15,Kepulauan Bangka Belitung
P16,Kepulauan Riau
P17,Lampung
P18,Maluku
P19,Maluku Utara
P20,Nusa Tenggara Barat
P21,Nusa Tenggara Timur
P22,Riau
P23,Sulawesi Barat
P24,Sulawesi Tengah
P25,Sulawesi Tenggara
P26,Sulawesi Selatan
P27,Sulawesi Utara
P28,Sumatra Barat
P29,Sumatra Selatan
P30,Sumatra Utara
P31,DI Yogyakarta
P32,DKI Jakarta
P33,Nanggroe Aceh Darussalam

Now here are the steps to create a transformation handling the csv files :

• To create a transformation you can do it with several ways :
  • Choose File | New | Transformation from the pulldown menu  

     

    

  • Click on  New | Transformation on the toolbar 

     

    

  • With a CTRL + N shortcut key 

• A new Transformation 1 workspace tab will show up, you rename it by saving our newly created transformation. Press CTRL + S and save file as c:contoh_kettlebaca_propinsi.ktr.
• Notice that now our tab is changed to baca_propinsi.