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