Bens Blog

After several months of development SQL Power released a new version of the Power Architect which is now available in 2 different version, the "old" Community Edition  (CE, also available on Google Code) and the brand new Enterprise Edition  (EE). Today I want to give you an overview of the most exciting new functions in SQL Power Architect EE. Central project repository & Security  In the Enterprise Edition all projects are stored on the server and you can create users and groups and give them different access rights to the projects depending on their role. Data Types and Domains This is one of the most important features in the new version. Prior to version 1.0 EE you could only work with a set of standard column types that all (most databases would understand). With Data Types and Domains you are finally able to define your own type and specify how this type should be forward engineered in the different database management systems. For example the type " Nu...

Last weekend SQL Power released an improved version of SQL Power DQguru (formerly known as SQL Power MatchMaker), one of the few open source tools for data cleansing and master data management (MDM) available. Version 0.96 brings a new feature that allows you to run SQL Power DQguru from command line. This allows you to integrate it into batch scripts and your ETL jobs. As a BI consultant for SQL Power I have used SQL Power DQguru in different projects and it has made my job a lot easier. Some of the features I like the most are: Easy connection to any database with JDBC drivers, incl. SQL Server, Oracle, MySQL, Postgres Lets you create complex merge rules so your dependent data will always be updated when you merge records. You can combine over 25 steps to find possible duplicate data with a match rule, for example: Word Count Regular Expressions Substrings Retain certain characters Translate Words, you can create your own translation rules. You can preview how your data ...

Last week I had some time to create a short screencast to show some features of SQL Power's Wabit open source version. The video could be more professional (My headset didn't like me too much) but I was to busy with other projects to  have more revisions. You are very welcome to share your ideas, critics and comments. Here you go: (Watch on Youtube: SQL Power's Wabit - Feature overview )

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 .

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: Nightly build from Power*Architect  [EDIT: The latest stable version should do it too.] A database, e.g. MySQL or PostgreSQL with the data you want to analyze. In this example I'll use the Pentaho sample data which is included in the Pentaho BI Server 3.x Wabit 0.97 (includes Mondrian built in) or a running Mondrian instance and a Mondrian client of your choice 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 ...

The open source business intelligence reporting tool Wabit added support for Mondrian in it's newest release 0.97. Users can now either connect to the integrated Mondrian server or to any available XMLA datasource. In the OLAP query, measures and dimensions can easily dragged into the query which automatically updates the results. It is the first version including the OLAP functionality so there are still some features missing, including filters, calculated members, saving of manually created MDX queries but Wabit already shows some nice results. With it's upcoming release 0.98 - scheduled for September 09 - most missing OLAP features will be added and Wabit should be a nice AND feature rich reporting application for Mondrian and all other OLAP engines supporting XMLA.

After not working with Mondrian for a few months I had to design a schema for Mondrian again. As usual I first used the Schema Workbench . U can do everything with it but the user interface is a pain I think, especially if you are used to the other Open Source BI tools that are around for other tasks. I remembered that at least version 2 of the Pentaho Metadata Editor has a hidden Schema Editor , you have to press CTRL-ALT-O to activate it. If you check out the newest version (Version 3 RC1) you will be surprised how much you can do with it already! After building your relational model in the Metadata editor you can quickly design your cube and reuse your relational model which can save you a lot of time. But remember, it's not supported by Pentaho yet and you have to copy and paste your schema to test it. I'm sure in the near future Pentaho will have a solution for it. Another open source tool I found (I started working for the company recently) is Power*Architect from SQLPo...