New features in SQL Power Architect

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 "Numeric" will be forward engineered to the type DECIMAL in HSQLDB but to NUMBER in Oracle.


Versioning
With version 1.0 EE you can easily revert changes you made in your project and compare different versions of your project. 


Data Model Validation
With the new data model validation you don't have to wait until you forward engineer to see if you have any errors in your data model. You can just click on the validation button and you will see where you need to do some changes. You can also select the validation rules you want to use, a column name you are using might be valid in SQL Server but not in Oracle.


Real Time Collaboration
Unlike most versioning tools where you have checkout and check-in your project Power Architect EE allows real time collaboration. You work "live" on the server, all changes you make on a project are instantly stored at the server and you watch others working on the project.

There are more features that have been added or improved like the ETL mappings and the profiling results. I think it's worth giving it a try. With the trial version you get 30 days product support and if you decide to buy it before end of October you will get a 3 user licence for the price of a 1 user licence

Wabit finally supports Parameters!

In the last days SQL Power released Wabit 1.3.1, you can download it here: SQL Power Download Website.

The most important change that came with Wabit 1.3 is the ability to parametrize reports. Below is a screenshot on how it looks like:

Report with parameters in SQL Power Wabit

You can choose a text entry field, date picker and a select box as parameter entry field. The values from a select box can be based on a query.

How to add parameters to your report:

• Add parameters to your SQL query. You can add parameters either by using the parameter dialog (Ctrl + Space) or just by using the right syntax in the query, for example "continent =${Continent->Europe}".
• ${Continent} defines the parameters "Continent"
• ${Continent->Europe} defines the parameter "Continent" and sets the default value to "Europe"

Parameter in a query

• Create a new Report
• Drag the query into the report
• Add a parameter in the parameter panel (Go to View->Docked Source List if you can't see the parameter panel) . Make sure the parameter has the same name as you defined in the query.
• To add a text entry field add a free form text.
• To add a select box add a drop down list.
• If you want to use values from a query in your select box, add a query with the values first and then select the query using "Source" and "Pick a variable" in the parameter properties.
• To add a date picker add a date selection widget.
• You will see that the result changes depending on the parameter entered.

Using a query as a select box

This functionality is available in the open source version of Wabit. Give it a try and let me know what you think of it.

Open Source tool for Data cleansing and Master Data Management

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 will look like when you apply the match rules
• Automatic Address correction (for Canadian addresses, Premium version)

Here is a example how a simple match rule could look like using some of the available steps:

Even the user interface is mostly straight forward, it might be useful to take advantage of the user guide which is available for a small fee. You will see SQL Power DQguru is very powerful if you know how to use it.

Using the Community Build Framework for Pentaho

Recently I had to prepare a installation for the Pentaho BI Server (CE) and I decided to try the Community Build Framework (CBF) from Pedro Alves. I had to install the server on a test and a production environment so it seemed to fit perfectly for my requirements.

It is working fine now and helps a lot in applying changes to the installation having a clean structure but it took me quiet a few hours till I had it working (probably because I'm not an expert when it comes to using ant & Co.)

Here are some issues you should be aware of:

• You'll need Java 1.6.
• Make sure your path to ant, java but especially the project folder doesn't contain any spaces. Spaces will only cause problems.
• Tomcat 6 is not supported yet.
• I recommend setting the solution paths to the folder "C:/...../project-client/solution" until you figured out how CBF works in detail.

You will have your CBF ready to run a lot faster than I did if you keep these issues in mind. I'm sure I'll use CBF a lot more often in the future.

If need more information on how to setup your own Pentaho installation I highly recommend checking this website http://www.prashantraju.com/ (besides the Pentaho Wiki).

Stop annoying applications in your Facebook news feed

With getting more and more friends on Facebook, I also get a whole bunch of them playing Mafia Wars, Farmville and all these other applications. And of course every day they have to share with me their latest score. I just found a good way how to stop getting these annoying updates in my news feed:

• In the entry of the application in your news feed, click on the application name (highlighted in the screenshot). This will lead you to the application page.

• On the application page you will see a link "Block Application" (Highlighted again). Click on it.

• Facebook will ask you to confirm your decision. Confirm to block it.
• Go back to your Facebook start page (news feed). All entries from this application will be gone!

SQL Power's Wabit - A feature overview

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)

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.