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.