Saturday, February 18, 2012

ER/Studio Data Architect


This is part of continuing set of reviews / opinion pieces about the state of data modeling tools that began over here.

Background

Embarcadero Technologies started off with making DB development and management tools. I ran into one of their tools (DBArtisan) in 1998 for managing my Sybase instances. At my next job I insisted on getting the full suite of Embarcadero Software. And my next job too. Unfortunately after that I wasn't able to justify the cost and it has been 7 years since I used the software. Did it age poorly, similar to what happened to ERwin? Let's see.


Stability

I used it nearly every day during the evaluation period, testing out a number of the different options. There were no problems with the small model I tested with (< 50 table) while it remained fast and responsive over many days being open and used. 

Price
ER/Studio Multi-Platform: ~$4,615.00 ($3,700 + $915 maint)

ER/Studio Open Source Databases: ~$1,360 ($1,100 + $260 maint)


Features
Common Checklist

  • Logic Modeling: Yes 
  • Physical Modeling: Yes
  • Reverse Engineering: Yes 
  • Forward Engineering: Yes
    • Compare against files and live databases. 
  • Sub Models: Yes

The UI is responsive, when you grab items they do what you expect them to. It's very similar to ERwin but the UI is much more responsive and stable.


After reversing out an existing database, the initial entity arrangement is surprisingly good, they have clearly put a lot of effort into it. There are several layout options and all of them work pretty well. Ultimately you still need to re-arrange the model a bit, but far less than any other tool I've used.


The Database compare and forward engineering makes very usable scripts that respect the integrity of the original model.

Conclusion
ER/Studio is an excellent product. There are add-ons to manage the model with a centralized repository and share database development tasks. There are also tools to establishing and maintaining conventions throughout your diagram.

In conclusion, ER/Studio is a great product, the price is high but it works flawlessly.

Saturday, January 7, 2012

Visio 2011 (ERD)

This is part of continuing set of reviews / opinion pieces about the state of data modeling tools that began over here.

Background

Visio is the default business diagramming tool in most organizations. I've used it off and on for after-the-fact documentation but never to create the actual model. The newest version of Visio (2010) included a slightly remodeled UI and some tools to help with choosing colors (surprisingly, that is very useful to me). 


Stability
It's Visio, it works well for the small model I tested with (< 50 table) and had no stability issues. 


Price
Visio Professional: ~$400.00

Features
Common Checklist

  • Logic Modeling: No 
  • Physical Modeling: Yes
  • Reverse Engineering: Yes 
  • Forward Engineering: No
    • Note, there is a plug-in created by Alberto Ferrari and hosted at CodePlex which does a non-comparing forward engineer of the DB
  • Sub Models: Yes

Once you get past the Visio UI quirks, documenting an existing system using Visio is a breeze. Yes, extra clicks are required everywhere. Yes, the relationship link points are limited and not fun to reset. 



The initial arrangement of the entities is pretty bad, but after using SQL Power Architect and MySQL Workbench I can appreciate that the layout is not awful.

Microsoft has a team of writers maintaining the help system and there are several good web resources on building ERDs using Visio so there is plenty of available documentation.

The lack of a built-in forward engineering function can be worked around (as noted above) but it does not compare to the actual database. 

Conclusion
I have used Visio to document data models in the past and I maintain that it can do this at an acceptable level.

Also Visio can be a good way to get down some initial thoughts on a model if that is all you have available to you.

I do not recommend using Visio for ongoing, iterative data model development. Forward engineering without a comparison function makes this work too awkward. 

SQL Power Architect

This is part of continuing set of reviews / opinion pieces about the state of data modeling tools that began over here.


Background

I must say that SQL Power Architect came out of no where for me. I was googling around when I stumbled upon it and it sounded too good to be true. A multi-platform, open source database modeling tool with an Enterprise version available (for pay naturally) if you need advanced features. Watching some of the videos it sounds like this was a tool developed in house that evolved into a product for the company. I couldn't find much more history than that, if anyone has some let me know and I'll add it in here.


Stability
I was back to using my small (< 50 table) model and had no stability issues. Rock Solid.


Price
SQL Power Architect: ~$00.00
SQL Power Architect Enterprise: $495.00


Features
Common Checklist

  • Logic Modeling: Yes 
  • Physical Modeling: No 
  • Reverse Engineering: Yes 
  • Forward Engineering: Yes
  • Sub Models: No 
This is an interesting contrast to MySQL Workbench. SQL Power Architect insists that you do all of your work in the Logical Model (called the PlayPen Database) and generate SQL to apply to your Physical Model. Of course this gets awkward if you need to update your Logical Model from the actual database.


Once again Auto-Arrange is a very poor choice. So many of the relationships overlap that it is difficult to understand the model. 


There is no documentation for the Community edition available. To understand how this application works your best bet is to visit the YouTube SQL Power Channel and play around for a while. In all fairness the Enterprise Edition does have documentation available.


The GUI, well it is in Java, so it does not feel native. The scrolling is wrong, either it scrolls too fast or begins scrolling before you would expect it to. In most apps this is isn't a problem but as you're trying to work on your diagram this can get really awkward.


Conclusion
This is easily the best open source tool I've seen for data modeling. It is easy to setup, works with JDBC connections and allows you to compare and forward engineer your changes.


The downside is that all DB schema changes are expected to originate from it, or be added to the Logical Model manually. And the Logical Model does not support sub-models. And the GUI makes working with the models challenging.


These issues can be worked around by making a lot of small projects as sub-models. This raises it's own challenges with overlapping entities and synchronizing projects. 


All in all if you are working on an open source project with limited funds this may be your best approach but watch out, you will spend more effort managing this model than you expect.

Saturday, December 31, 2011

The State of Data Modeling Tools (MySQL Workbench)

Background
I have only begun using MySQL and trying out the WorkBench tools from Oracle was the next logical step. This product was originally DBDesigner 4 from fabForce.net before it's developer was hired by MySQL AB to create a new set of MySQL GUI tools. This application has grown from a physical database designer for MySQL to include additional query and managment features. It is much closer to SQL Server Management Studio than it is to ERwin.


Stability
I was using a relatively small (< 50 table) model to work with and the system would crash after about on hour of use. The crashing was more frequent on the Mac side of things than it was when running on the PC but neither were very stable.


Price
MySQL Workbench: ~$00.00

Features
Common Checklist


  • Logic Modeling: No
  • Physical Modeling: Yes
  • Reverse Engineering: Yes
  • Forward Engineering: Yes *
    • * Note: Does NOT compare to existing schema. It will make fresh. new objects!
  • Sub Models: No
The auto-arrange does a terrible job distributing the tables, many were overlapping. The relationship lines are not adjustable, they go where the app assigns them to be.


The GUI makes me think of Eclipse, which is clearly not native on any platform and it generally works as expected.  The notation can be changed to fit your conventions and the automatic screen refreshing worked flawlessly (not something to take for granted after using ERwin).


Conclusion
This is a very good tool for visually showing developers how a few objects are related to each other and firmly belongs in the MySQL suite of tools.


Unfortunately, this application is just a bit too primitive to use for anything other than the most basic data modeling. The lack of a Logical Model is less than ideal, but not a deal breaker. I did find the crashing, lack of sub-models, minimal control of the relationship lines and the poor forward engineering very frustrating. I recommend using a piece of graph paper instead.

Friday, December 30, 2011

The State of Data Modeling Tools

Lately have had a chance to do some data modeling for a MySQL project and I thought I'd do a series of posts with a brief review the tools for making ERDs.

Data Modeling Tools
 Evaluation Criteria
  • Stability
  • Price
  • Features
ERwin Review
I have been using ERwin for a few years now so I am pretty familiar with this tool. Like it or not it has been around for ages (Logic Works, who originally made ERwin started in 1988) and is the benchmark all other system have to be measured against.

Stability
The most recent version has not been entirely stable, I cannot make it crash on demand but it will occasionally lock up.

Price
ERwin Data Modeler Std Edt + 1 yr Maint: ~$3,900

Features
Common Checklist
  • Logic Modeling: Yes
  • Physical Modeling: Yes
  • Reverse Engineering: Yes
  • Forward Engineering: Yes
  • Sub Models: Yes
Now having used it for a while I know a number of ERwin's quirks but stepping back and looking at it with a fresh eye, it is dated. The GUI looks like it is still built on an ancient toolkit which is likely why the work area screens require manual refreshes to clean up visual artifacts.

Conclusion
ERwin has the core features I'd like, but it's not entirely stable, the GUI is quirky and the product is expensive. For the price I would expect CA to be rolling updates more frequently to address these issues but ERwin has had minimal updates these last few years.

Sunday, January 30, 2011

NoSQL Summary

The best short description of when not to use NoSQL that I've ever seen is from a reddit comment by bucknuggets


Assumptions:

  • a key-value pair data store isn't optimal storage in the perfect world since data quality is very difficult to manage, reporting is a nightmare, and since most data doesn't map elegantly to key-values app complexity grows.
  • but it may be a very effective storage solution at certain scalability thresholds

Then for sites below those thresholds it is premature optimization - that adds unnecessary risk to the project.

Friday, January 1, 2010

The Fourth Paradigm brought to life?

I was reading "The Fourth Paradigm" and thinking about the whole data-brick concept that is needed to handle the information deluge. To their credit it is not a solution focused book, they mention Hadoop, MapReduce and Beowulf Clusters all over and hardly mention SQL Server at all. This is impressive because the man who inspired this book (Jim Gray) is the same database luminary that dragged SQL Server back from the dead with SQL Server 7.0. Now I'm only considering this in the context of business systems because that is where I have the most experience.

In the book, there are three main concerns.

  1. Capturing data
  2. Curating the data
  3. Analyzing the data

Capture
Capturing is not a problem, currently we capture a rather absurd amount of data but we don't know what to do with it. Between the Beowulf Clusters and MS Windows HPC (High Performance Clustering) Editions be know how to collect tons of information.

Curate

I think that the curating of the data is intended to be done by Master Data Services. This provides a way to setup data approval processes and de-duplication based on customized business rules. It's whole purpose in life taking care of information. Now, opening that data up so it is valuable to the world instead of a single company is a separate problem.

Data Analysis


Initially I was thinking that the data-brick concept for analyzing and retrieving the data was being brought to life as part of SQL Azure. Probably the best part of SQL Azure is that it could meet the need of centralized storage that can be rapidly accessed from anywhere in the world. Unfortunately SQL Azure has a lot of drawbacks.

  • Extremely limited DB size (10GB?)
  • Limited ability to do query tuning (Can't even see the query plan?)
  • No controlled backup strategy (the "I need data from a week ago" sceanario)
The limited database size is a real killer, this ain't it.

So what is the next option? I think Microsoft is addressing it through SQL 2008 R2 Parallel Data Warehouse Edition. Yup it's a mouth-full alright, but it is positioned in a very interesting spot. It's intended to be paired with Windows HPC (High Performance Cluster) Edition. It's based off the parallel data warehouse software from DATAllegro. It distributed the query across a bunch of nodes, collects the results and presents it. It sounds awesome, right? The price certainly is, (57k / Processor!).

I really like that MS has a coherent long-term strategy for all this stuff that I hadn't seen so clearly until reviewing Jim Gray's work.