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

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 …

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

Pentaho BI Server: Using action sequences as a web service with PHP

For my masterthesis I had to figure out, how to use the action sequences as webservice with PHP. According to the documentation you can receive soap messages but the action sequences don't offer a WSDL that would help you building your client. I also had problems with the http basic authentication, that Pentaho uses.
After a couple hours of research and try and error, I found a solution. I doubt thats the best way to go, but at least it works. All you need is the PEAR HTTP Request class.
Here is the code:

//PEAR Request
require_once 'Request.php';
$response = $req->sendRequest();

if (PEAR::isError($response)) {
echo $response->getMessage();
} else {
$req->clearPostData();
$req->setURL("localhost:8080/pentaho/ServiceAction");
$req->addQueryString("solution", "bi-developers");
$req->addQueryString("path", "reporting");
$req->addQueryString("action", "Testreport.xaction");