Skip to main content

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.
Post a Comment

Popular posts from this blog

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("'"&…

Creating YTD transformation tables

The other day I had to setup a new data warehouse that will be used for reporting with MicroStrategy. Part of it was setting up the date dimension including the transformation tables. I had a quick look online and couldn't find any script doing the work for me so I created them myself (with the help of a colleague). All you need is an existing date dimension with date_id, year_id, quarter_id, month_id and week_id, you can find plenty of scripts for that online. YTD tableselect t1.day_id, t2.day_id
INTO YTD_DAY
from LU_DAY t1, LU_DAY t2
where t1.day_id >= t2.day_id
and t1.year_id = t2.year_id QTD table select t1.day_id, t2.day_id as qtd_day_id
INTO QTD_DAY
from LU_DAY t1, LU_DAY t2
where t1.day_id >= t2.day_id
and t1.QUARTER_id = t2.QUARTER_id  MTD tableselect t1.day_id, t2.day_id as mtd_day_id
INTO MTD_DAY
from LU_DAY t1, LU_DAY t2
where t1.day_id >= t2.day_id
and t1.month_id = t2.month_id  WTD tableselect t1.day_id, t2.day_id as wtd_day_id
INTO WTD_DAY
from LU_DAY t1, LU_DAY t2
where …

Pentaho Data Integration - Multi-part Form submission with file upload using the User Defined Java Class Step

I recently needed to use Pentaho Data Integration (PDI) to send a file to a server for processing using HTTP Post. I spent several hours trying to use the existing steps HTTP Post, HTTP Client & Rest Client but I couldn't get it to work. After some more research I came across the issue PDI-10120 - Support for Multi-part Form Submittal In Web Service Steps and I thought I was out of luck.

I previously wrote a small Java client for a similar use case and remembered the PDI has a step called User Defined Java Class (UDJC). After reading this great tutorial I created the following basic transaction. I have a dataset with the URL and the full file path and use the UDJC to make the HTTP call.


The Java class handles the actual HTTP Post. It uses 2 input variables, the URL (url) which is used for the call and the file name (longFileName). The HTTP call then contains the file (line 30) and the file name (line 31). I included some basic error handling based on the HTTP status code.

1 …