Power*Architect

By Andrew Clifford

Power*Architect is a free, open-source data modelling and database design tool that is easy to use and packed with features.

I am working on a data warehousing project, and need to produce data models and database designs. It is a small project, and we can not justify enterprise-class tools, such as IBM Rational System Architect or CA Erwin.

We thought we would use a drawing tool such as Microsoft Visio, but then we came across Power*Architect from SQL Power, which is free and open-source.

Power*Architect is a data modelling and database design tool. Like all such tools, you create a graphical model of your data and define tables, columns and relationships.

Power*Architect does much more than just draw pictures. It generates databases from your design, and supports all the popular databases such as Oracle, SQL Server, DB2 and MySQL. It captures existing database structures so that you can view and change them. It compares databases and models, and generates the scripts required to modify one database to match another, which is very useful during maintenance.

We have been using Power*Architect for a few days. It is very easy to use, and very reliable. It is good at layout, and has easy-to-use print features and reporting tools. We have used it for both logical data modelling, and for capturing existing database structures. We have tested the database creation and modification tools.

Power*Architect does not require complicated installation procedures. It is written in Java, and can be run on any platform. There is a good help system. The model is held in a simple file, in XML format, so you can get at the underlying data if you need to.

There are some drawbacks. Power*Architect does not distinguish between logical and physical models. It does not support sub typing or exclusive links. It only uses physical data types. There is no way of suppressing the posting of foreign keys, which makes models less readable. But we can work around all of these.

Power*Architect has many features that we have not yet tried. It supports Online Analytical Processing (OLAP) designs often used in data warehousing. It maps between source and target databases and generates definitions for Extract Transform and Load (ETL) tools. It profiles data in existing databases, to collect statistics about table sizes and key values.

Overall, Power*Architect is an excellent tool. Although we started by looking for a simple drawing tool, Power*Architect has many other features that will help us in this project.

SQL Power provide a number of other tools, both paid-for and free. The other free tools are Wabit, a reporting tool, and DQguru, a data cleansing tool. SQL Power is also planning a server-based version of Power*Architect, which will make group collaboration easier.

I am hugely impressed by Power*Architect. If you are looking for an easy-to-use, feature-rich data modelling and database design tool, have a look at Power*Architect. It competes well against other tools, and has the great advantage of being free. And it's certainly much better for data modelling than Visio.

Subscription