Skip to main content

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.
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");