Bens Blog

A few months ago I first mentioned that you can create Mondrian schemas with Power*Architect. Finally I get the time to create a step by step guide for a small Mondrian schema. This post covers the preparation of Power*Architect to create a new schema, the coming blog post will cover the schema creation and testing. What you need to get started: Nightly build from Power*Architect  [EDIT: The latest stable version should do it too.] A database, e.g. MySQL or PostgreSQL with the data you want to analyze. In this example I'll use the Pentaho sample data which is included in the Pentaho BI Server 3.x Wabit 0.97 (includes Mondrian built in) or a running Mondrian instance and a Mondrian client of your choice Step 1 - Setting up the database connection Start Power*Architect Open the Connections menu and start the Database Connection Manager . Click on New . Add the database connection details. For the Pentaho sample data enter the data as shown in the screenshot (Click on it ...

This is part two of my step by step guide on creating a Mondrian Schema with Power*Architect. Step 3 - Creating the Mondrian schema Go to OLAP-> Edit Schema -> New Schema. Enter a Schema name and select the database connection you created earlier. Dimensions Right click on the PlayPen, select New Dimension , and click on the PlayPen again Enter the dimension name, select TimeDimension as the dimension type and click on ok. Right click on the created dimension and add a hierarchy. Select the time table (public.dim_time) and select the correct primary key. Now you can add levels. Right click on the hierarchy and select Add level. Enter the name, column and correct level type. The procedure is the same for all dimensions. Cube Right click on the PlayPen, select New Cube , and click on the PlayPen again Enter the Cube name and select the fact table (public.orderfact in my example) Add the dimensions to the cube. Click on (Dimension Usage), then on dimension and finally on the cube....

The open source business intelligence reporting tool Wabit added support for Mondrian in it's newest release 0.97. Users can now either connect to the integrated Mondrian server or to any available XMLA datasource. In the OLAP query, measures and dimensions can easily dragged into the query which automatically updates the results. It is the first version including the OLAP functionality so there are still some features missing, including filters, calculated members, saving of manually created MDX queries but Wabit already shows some nice results. With it's upcoming release 0.98 - scheduled for September 09 - most missing OLAP features will be added and Wabit should be a nice AND feature rich reporting application for Mondrian and all other OLAP engines supporting XMLA.

After not working with Mondrian for a few months I had to design a schema for Mondrian again. As usual I first used the Schema Workbench . U can do everything with it but the user interface is a pain I think, especially if you are used to the other Open Source BI tools that are around for other tasks. I remembered that at least version 2 of the Pentaho Metadata Editor has a hidden Schema Editor , you have to press CTRL-ALT-O to activate it. If you check out the newest version (Version 3 RC1) you will be surprised how much you can do with it already! After building your relational model in the Metadata editor you can quickly design your cube and reuse your relational model which can save you a lot of time. But remember, it's not supported by Pentaho yet and you have to copy and paste your schema to test it. I'm sure in the near future Pentaho will have a solution for it. Another open source tool I found (I started working for the company recently) is Power*Architect from SQLPo...