Real-Time Business Intelligence with Wabit & SQLstream

The last week I got the chance to prepare a screencast of SQL Power's new real-time BI solution. It uses a SQLstream server as backend and Wabit as a BI reporting tool. Both the Open Source and the Enterprise Edition of Wabit can be used for it.

Here is the screencast (it's the best to watch it in full screen):



Comments are welcome!

UPDATE: The full offer is now available on SQL Power Real-Time BI solution.

Delta Generation with Kettle

In one of my current project I have to do lots delta generation to figure out if any data changed and be able to work differently with the data depending if it's similar, new, changed, or deleted. I came up with the following transformation:

Products you don't expect to be 'Made in China' - Del Monte fruit cups

Since I moved to Canada back in March I have started to realize how many products are actually made in China. Back in Germany you could also buy lots of stuff from China but you mostly had the choice between German or Europe products and Chinese products.
When I went to Food Basics in Oakville a couple weeks ago to get some apples I stood in front of a huge tray of Chinese apples! Aren't there enough apples in Ontario, Canada or the US? Even Mexico would probably be closer than China.
Another day my wife bought Del Monte fruit cups in the grocery store. I checked the label when I was going to eat it and i decided to leave it in the fridge. First of all it is 'Made in China' (again I guess no other country in this world has fruit) and second it contains artificial flavor. How bad must the fruit inside be that you need artificial flavor (and does anybody in China controls how it is made)?
For my part I'll check the labels more closely whenever I buy any kind of product, especially when it comes to food. My health but also the economy of our country (and the other western states) is too important too me to ruin them with something thats just a few cents cheaper.

(This blog entry will hopefully become part of a small series on 'Made in China' related subjects.)

My first impression of the Palo Worksheet Server 3

Recently I got a change to download Palo Worksheet Server 3, I was planning to build a small test case and impress some people at work with what Palo can do. But I'm not sure if I should really show too much of the Worksheet Server during my presentation after I started playing with it a bit more. Here is why:

• Pros:
• The frontend of Worksheet Server looks nice.
• The Charts and Micro Charts look nice.
• Many people won't think of Palo as a full BI tool if it doesn't provide it's own frontend
• Cons:
• Why did Jedox release a software that is so unstable? I'm always worried to do too much in a worksheet because I don't know whats gonna happen next. Will i get lots of "value?" Will my session end and I loose my data? (Just happened)
• If you are used to Excel & Google Docs you get impatient, some of the context menus are too slow.

After working with it for half a day I wouldn't let a client create his own reports, the frustration level would probably be too high. Am I alone with that opinion?

September issue of OSBR on Business Intelligence

This morning the September issue of the Open Source Business Resource (OSBR) - a monthly publication of the Talent First Network got released. It is all on Open Source Business Intelligence.
Have a look: OSBR September 2009 - Business Intelligence

What is the OSBR?

The Open Source Business Resource (OSBR) is a free monthly publication of the Talent First Network. The OSBR is for Canadian business owners, company executives and employees, directors of open source foundations, leaders of open source projects, open source groups, individuals and organizations that contribute to open source projects, academics and students interested in open source, technology transfer professionals, and government employees who promote wealth creation through innovation.

[Quote from the OSBR website]

Wabit - Open Source BI - New features in version 0.98

SQL Power Group, based in Toronto, Canada, released version 0.98 of it's open source business intelligence reporting tool Wabit. Here is a overview of the newest (and improved) features:

• General
• Improved UI
• Most item are drag able
• Multiple workspaces can be open at the same time
• Search in workspaces
• Reports
• Report templates available
• Extended grouping options
• Chart
• New more intuitive UI to create charts
• Pie chart support added
• OLAP queries
• Filter added
• Improved UI
• More OLAP navigation features added (Drill replace etc.)

One of the features I like the most is the new grouping and sections setting in reports as you can see in the screenshot:

There are still many improvements possible but 0.98 brought Wabit a lot closer to all the other open source reporting tools available.
What feature do you think should be added next?

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.