Tuesday, August 25, 2009

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.
  • 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. (This procedure is supposed to get easier in a later release of Power Architect).
    • Enter a caption and select correct foreign key out of the fact table.
    • Follow the instructions for the other dimensions.
  • Measures
    • To add measures to your cube, right click on the cube name, select the aggregator and the correct column from your fact table.
  • Your schema should look similar to the following screenshot.
  • Export the schema using the last icon (or by right clicking in the schema) to your hard drive.
  • Save the project and close Power*Architect
Step 4 - Testing the Mondrian schema
  • Install and start Wabit.
  • Create a new Workspace with a new OLAP database connection
  • Select "In-process Mondrian-Server", the database connection you already created in Power*Architect, and select your Mondrian Schema you exported earlier. You can see an example in the screenshot
  • Click on "Start"
  • Wabit automatically starts in the query mode.
  • Select the created cube in the right hand bar. All measures and Dimensions will be listed. underneath.
  • Drag&Drop the measures and dimensions into the OLAP query editor and see the results. Your result should look similar to mine (I'm using a slightly different version of Wabit).


Step 5 - Troubleshooting
If you run into problems check the following:
  • HSQLDB doesn't like multiple connections too much. Make sure you have only one open connection.
  • If you can't create a query with Wabit, load the Schema into the Mondrian Schema Workbench and check if it's working there. Maybe you missed something when you created the schema.
  • Can't fix it? Write a comment or use the SQL Power forum.

Monday, August 24, 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:
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 screenshot). Make sure you enter the right folder name.

  • Test the connection and click on Ok.
  • Close the Database Connection Manager.
  • Open the Connections menu -> Add source connection and select the created connection
Step 2 - Visualize the data model (Optional)
If you don't know the data model, I recommend using Power*Architect to visualize it. This step is not needed for the creation of Mondrian schemas.
  • Unfold the database connection and drag&drop the needed tables to the right hand PlayPen. In my example I use the tables orders, customers, dim_time and orderfact.
  • Add the relationships between the tables: Click on (Non identifying relationship), on the key in the dimension table and then on the related column in the fact table.
  • Add the end the model should look similar to the following screenshot.
  • You can find more details on how to use Power*Architect in the help and manual.
The second part tomorrow will cover the actual schema creation and testing. Comments are welcome.