Skip to main content

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
Post a Comment

Popular posts from this blog

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 Data Integration - Multi-part Form submission with file upload using the User Defined Java Class Step

I recently needed to use Pentaho Data Integration (PDI) to send a file to a server for processing using HTTP Post. I spent several hours trying to use the existing steps HTTP Post, HTTP Client & Rest Client but I couldn't get it to work. After some more research I came across the issue PDI-10120 - Support for Multi-part Form Submittal In Web Service Steps and I thought I was out of luck.

I previously wrote a small Java client for a similar use case and remembered the PDI has a step called User Defined Java Class (UDJC). After reading this great tutorial I created the following basic transaction. I have a dataset with the URL and the full file path and use the UDJC to make the HTTP call.


The Java class handles the actual HTTP Post. It uses 2 input variables, the URL (url) which is used for the call and the file name (longFileName). The HTTP call then contains the file (line 30) and the file name (line 31). I included some basic error handling based on the HTTP status code.

1 …

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 …