Skip to main content

Posts

Showing posts from August, 2009

Creating Mondrian Star Schemas with Power*Architect - Part 1/2

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.xWabit 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 for a larger screensh…

Creating Mondrian Star Schemas with Power*Architect - Part 2/2

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.DimensionsRight click on the PlayPen, select New Dimension, and click on the PlayPen againEnter 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.CubeRight click on the PlayPen, select New Cube, and click on the PlayPen againEnter 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. (This procedur…

Open Source BI - Wabit now reports against Mondrian (and other XMLA sources)

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.

Dynamic cell references in spreadsheets with Google Docs

During my former internships in a consulting company I had to work A LOT with Microsoft Excel and often had to use dynamic cell references over multiple worksheets. Recently I started using the spreadsheets of Google Docs to track my bank account balance and to figure out where all my money goes. I decided to have one sheet for every month + one sheet of the month I want to analyze. But how do I dynamically change the reference to the sheet (the monthly sheet) I want to analyze without editing every single formula? Here is my solution: Create the target sheets and your overview sheet
- I gave my sheets the names Month + Year (July 09)In the overview choose one cell that you want to contain the reference sheet and enter the sheet name
- cell D24 in the example
- Using the month names you might have to write 'July 09, otherwise Google will think it's a date.In the overview you can now dynamically reference to a detail sheet using the following formula:=INDIRECT("'"&…