Tuesday, August 25, 2009

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.

Monday, August 24, 2009

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.

Tuesday, April 28, 2009

Oracles Acquisition of Sun

Well, King Larry is at it again, swooping down and buying up Sun Microsystems. To the best of my knowledge, Oracle really hasn't made a major mis-step with any acquisition that it's done. And this one is a whopper, picking up one of Silicon Valley's stalwarts and a darling of the tech crowd.

But what does it really mean?

Well, for Oracle, it gives them a number of key items:

  1. A full stack, hardware on up, to allow them to offer an almost turnkey system (Solaris OS and Java). Oh, and if you want ERP solutions to run, they've got that too!
  2. An entry level, easy to use, multi platform database with over 2 million installations in MySQL. In addition to acquiring the subscription revenue stream, Oracle gets access to a huge user list, and a product that has deep global penetration at all levels of business.
  3. Open Office. While not a big part of the deal, nonetheless an interesting tool to jab at Microsoft with - Microsoft's Office cash cow is now under serious pressure from Google's thin client offerings at the consumer level and with Oracle's reach into corporations the Seattle boys could face considerable pressure on the desktop.
  4. A bunch of other interesting technologies, including the ZFS file system, that have potential for Oracle.

Now, being an open source proponent, one has to wonder about the future of Sun's open technologies and MySQL in particular. I've seen two trains of thought on the acquisition's impact on MySQL:

A. Oracle, being the evil, mean, nasty closed source commercial giant (whew!) close sources MySQL or lets it wither and die.
B. Oracle embraces MySQL as its open source offering and continues to nurture it.

I really can't see Oracle killing off MySQL. Let's face it, they would really p*ss off a lot of people by doing that. I could see Postgres gathering steam in that scenario, or another MySQL variant rising from the ashes. And we would be right back where we are today. Looking back, everyone expected Oracle to quietly bury InnoDB when they purchased them, and that hasn't happened. In fact, InnoDB continues to be a viable engine for MySQL. I foresee an improved MySQL offering under Oracle, as Oracle's culture won't tolerate the infighting that has plagued MySQL over the past few years, and has hurt product development.

I am interested in seeing the competition's reactions to the news. Will this reinvigorate Microsoft's interest in acquiring Yahoo? What will HP, a hardware partner with Oracle, do know that their partner competes with them? And what will IBM do next?

Interesting times...