Jon Baker's Outer Join

I recently assisted in a review of data modeling tools for a customer.  I won't mention which one we chose, but I would like to give a general review of the tools.  The 3 major tools on the market for data modeling are CA Allfusion Data Modeler, Sybase PowerDesigner, and Embarcadero ER/Studio, and we looked at these 3 competitively.  Each has their own pros and cons. Some provide better SQL Server 2005 support than others.  Some are just plain easier to use than others.  I won't go into a diatribe about data modeling, but if you are looking for a new tool, or if you are looking to begin data modeling for your development projects, you might want to read on to see which tool might be a good fit for you.

CA AllFusion Data Modeler R7

This is the tool formerly known as ERWIN. If you've been in data management for very long, you've probably seen this tool at least once before, most likely as shelf-ware along with many other data management products.  That's not to say ERWIN isn't a fine tool, just that it is underutilized.  ERWIN has gone through a beleaguered history bouncing from LogicWorks to Platinum and then being consumed by Computer Associates.  It was really the first of the PC-based graphical data modeling CASE tools.  ERWIN, or I should say AllFusion Data Modeler as CA now calls it, is in it's 5th major version, but it's being called R7 (mostly I think to keep up with the Joneses - see versions of the other products being reviewed).  The latest version provides additional support for DBMS platforms other than SQL Server and it has little (if any) support for SQL Server 2005 new features. ERWIN's strengths continue to lie in green-field development.  It is very fast at creating a new logical data model, converting that to a physical data model, and generating the schema to create the database objects.  ERWIN's weaknesses continue to lie in metadata management, reporting, graphical layout of the model, and creating ALTER TABLE schema when comparing to an existing database.  The latter is the reason it is difficult to use for anything other than green-field development.  Both of the other tools provide much better means for developing enterprise databases for the long-haul.

Pros

Easy to create new structures
Long History
Full-Featured logical modeler
New Schema DDL Generation

Cons

Metadata Management
Poor Support from helpdesk or website
Graphical Layout - one method, not easy to read
CASE tool to generate ALTER DDL
Lack of SQL 2005 support
Price - it was the most expensive of the 3 solutions

Sybase PowerDesigner 12

Sybase has put a lot of effort into designing a comprehensive modeling suite. Their enterprise edition modeling tools allow for collaborative modeling through all phases of the SDLC.  The ideal scenario for PowerDesigner is to start by capturing Use Cases and modeling the results in PD.  The Use Case Model then pushes down to a number of sub-models, including a Conceptual Data Model.  Data Items can be defined by the business analyst in the Use Case, and the data modeler then adds the Data Item to the appropriate entity.  This is an incredibly empowering scenario for the business, as IT is no longer the only one with data definition capability and responsibility.  It also takes a lot of work off the data modelers' plate as now they have less work to do defining the nitty-gritty of each attribute. 

Once a Conceptual Model is complete, it can be pushed down to a Logical Model for further definition.  A complete Logical Model can be pushed further down to a physical model based on a specific DBMS.  All of these models are decoupled, in that changes to one do not affect the other(s).  PD does provide a link and synch tool to push changes up and down the model chain, allowing you to model at any level, while still keeping all the levels in synch. 

PD is an excellent end-to-end, formal modeling solution providing a collaboration repository.  Its strengths are many. The rich feature set allows the modeler to do just about anything you'd ever want to do.  Besides data modeling, the CASE tools provide Object Modeling and code generation in virtually all the major languages, making it a great place to start any project.  The downsides are just as you might expect.  The capabilities of this tool bring much complexity.  Ripping out a simple data model for a new project is not all that easy.  The tool really pushes you down a process based development pattern.  Also, the interface is not all that intuitive. I have been data modeling for many years, and it took me a  significant amount of time to be able to create entities and relationships for 20 entities.  Lastly it struggled much like ERWIN in creating ALTER DDL.  It was quick good at creating new DDL, but struggled when comparing a model to an existing database.

Pros

Complete business proccess and IT modeling tool
Rich Collaboration
Exceptional Metadata Management (Blobs and Definitions and search capability)
Flexibility in configuration
OK SQL 2005 support

Cons
Complexity
User Interface
Comparison and Script Generation

Embarcadero ER/Studio 7.1
I believe ER/Studio is the youngest of these modeling tools, but it certainly can hold its own with the older brothers.  ER/Studio provides rich data modeling capabilities, metadata management, reporting, and automation.  The last point is a feature I really like, the macros.  Users can write their own productivity and standard enforcement scripts to change models in whatever way necessary.  Embarcadero opens up the object model for access to program your own, but they also provide many helpful macros in the box.

Like PD, Embarcadero decouples the physical model from the logical model, providing compare and synch capabilities between models and the target database.  This last point makes ER/Studio stand-out.  It does a great job of creating alter DDL when comparing to an existing database.  Many tools will rename the table, recreate the table, and re-insert the data for even the simplest changes.  ER/Studio does a pretty good job of knowing when you just need an ALTER TABLE ADD COLUMN script.

The user interface and model layout features of ER/Studio are beyond belief.  There are six ways to layout a model. All of them are meanigful and fast, even with large models.  There are some areas of the user interface that take longer to do things than say ERWIN or PD, but in general it is intuitive and easy to use.

Lastly, ER/Studio now comes in a just for SQL Server edition, which is much less expensive than the competitors, which charge for all the databases, even if you never plan to use them.

Pros

Great Model Layout
Automation Macros
Good Comparison tool and DDL Generation
OK Metadata management
Great Helpdesk and feature enhancement support
SQL 2005 support
Price (SQL Server Edition)

Cons

Some tedious areas of the interface
A little buggy (needed to call support a few times)

As you can see, all of these tools can provide an organization with good data management abilities.  One thing I didn't mention in the reviews is that data management is more than implementing a tool.  It takes discipline and process enforcement.  None of these tools will do that for you. That's the people part of this industry.  Hopefully, this review will help others as they look to choose a tool to implement in their SDLC.

Jon

A little advertised session at PASS Friday was the real hidden gem of the conference.  Ken Henderson  and Bart Duncan presented a new tool released today by PSS to analyze SQLDIAG captures.  For those not familiar with SQLDiag, it captures traces, blocking, wait types ,etc.  SQL Nexus allows you to import all this data into a database.  It then provides Reporting Services reports on top of the tables to analyze the data out of SQLDiag.  These reports are graphical, parameterized, and very meaningful.  Think READ80Trace on steroids!  In addition to SQLDiag stuff, it can report on CPU stats and other performance data coming out of DMVs inside SQL Server. 

I am completely blown away by this tool.  It is a must have for any DBA.  You gotta download it right now.

Steve Ballmer was the keynote today. SteveB talked business and the future of Microsoft. There was a lot of info about how Microsoft is working to enable the information worker and business people to be more productive. He made the comparison to the days before PCs in the workplace, when cut and paste involved scissors and glue! This all seemed a bit ethereal, until the demo. They showed Vista and Office 2007 integration with Sharepoint, SSAS, and SSRS. This appeared seemless and powerful. Let’s hope it’s really that cool, because it would change how we work, today. The final question for Steve and Paul Flessner’s Q&A was if Steve was going to do a DBA dance (see PDC 2006 – “Developers, Developers, Developers…). Steve turned a bright shade of red, then gave his best Arnold Schwarzenegger impression saying “DBAs Baby!”

I saw a presentation on SQL Server for the SOA environment. Marin Mamic is obviously a developer and not a DBA, though his session was listed in the DBA track. He featured HTTP endpoints for exposing SPs as service methods. I like this to some degree, in that it doesn’t require IIS anymore; however, the hard-core DBA in me still has some fears. Marin also displayed Service Broker for some transactions. It was not a deep dive into any of the technologies, but just showing that it is possible. Interesting stuff.

During lunch, I just happened to sit down at a table with Joe Celko.  What an incredible guy!  I love SQL For Smarties, and it was great to talk with one of the fathers of our industry.  I tried not to ask questions, but just listen to what things interest him and how he attacks problems.  He was conversing with a developer from MSFT who had questions about a programming problem.  I thoroughly enjoyed just listening.

Gert Drapers presented Schema management in DataDude (VSTS Database Professional Edition). This is an interesting new tool for compare and schema management, making source code the definitive source. I would like to see a data modeling tool on top of this, but Gert was adamant that he will not develop it. CA apparently is taking that up as an add on for ERWIN. Hopefully other software vendors will jump in to add to DataDude modeling tools (remember, I’m not an ERWIN fan!).

Dave Fackler presentations of Attribute Hiearchies was great. I met Dave at the Detroit Launch event last year. He does a great job, especially making relevant demos. I’m really catching the bug for SSAS. I’ve not spent a lot of real work time on AS, but I’m itching!

Ted Malone’s TDD for SQL developers really appealed to me. I like the ideas behind agile development. I also like that Microsoft is investing in it with DataDude.

For dinner, Jeff Mayer and I went to Benihana.  While we were talking geek stuff, the guy next to us made a couple of interesting faces.  I asked him if he was a technical person.  He said no, but he works for Microsoft in the areas of business products (Office, Great Plains, etc.) He asked about the demo in Ballmer's presentation, which of course we told him was great.  I mentioned one of my customers was looking at GP to replace a home grown accounting system.  He was interested, since the customer has a famous brand (can't share, sorry). I asked him for a card, and Jeff Raikes, President of Microsoft Business Products kindly did!  How cool is that!!!

I think my brain is getting full here at the conference. Not sure how much more I can stand, especially since I’m really distracted due to the OSU game this weekend. GO BUCKS!!!!

The day started with Paul Flessner’s Keynote, where he introduced his successor, Ted Kummert. There was also a demo of DB mirroring that bombed miserably. Still not sure I like that feature in its current state.

The sessions were great today. I saw Joy Mundy give a session on SSIS ETL for Fact Tables and source extraction. This was awesome. It dug deeper even than her book.

I also Allen White present on SMO for automation of daily administration. This was interesting to me as I research RMO to replace the debacle that is Replication Monitor 2005. Allen is a great guy, whom I’ve enjoyed connecting with through SQLJunkies and here at the conference. If you are looking for him at PASS, look for the guy that looks like Bill Gates. For those Buckeye Fans staying in Seattle during the game on Saturday, Allen says the Alumni Association is watching the game at a bar near the Space Needle.

Later in the afternoon I saw sessions on improving performance of SSAS and some methodologies (ADAPT and SPINE) for modeling multi-dimensional databases (Dan Bulos). These were great. I spend a lot of time ER Modeling, now I can do the same in the MD space.

Later in the evening was the exhibitor session. Quest is giving away shirts and pins. If you wear their T-shirt, you could win $1000. I thought about wearing the shirt today, but its blue, and well, being a Buckeye fan, I just can’t do it this week. Sorry Mike Walsh!

I also got to meet Adam Machanic face-to-face. We’ve emailed and responded to each other’s blogs, but finally I can put a face with the name. Nice to meet you Adam!

After the exhibit was the PASS volunteer night at Gameworks. I was really tired and everyone else in the room seemed to be, too. I didn’t stay long, but it looked like a good time. Thanks to PASS for recognizing volunteers. This continues to be a first rate organization.

So after much turmoil and headache getting through O'Hare Monday night, I finally made it to Seattle. To my surprise, it's raining. :O) I still love this city though. It's full of culture and there's so much to do.

Today, I sat in on Erik Veerman's SSIS class. I think my ears are bleeding. He filled us with so much good stuff. The best session was actually the last one. Erik dug into the internals of SSIS and taught us some tuning and troubleshooting that I've not seen anywhere else. Definitely worth the price of admission. Most of his examples came from ProjectREAL or the Kimball book, which surprised me, since he helped author the Wrox book (which I won in the give away, woohooo!).

I had the chance to chat briefly with Rick Heiges. He is a busy guy this week. Sounds like another great conference on tap. Over 1700 people are signed up as of right now. They are expecting near 2000 folks. This used to be a small intimate conference. Somebody let the secret out.

Some info from the PASS rumor mill. I had dinner with Jeff Mayer who is on the membership committee. He tells me they are working to make membership more meaningful. I'm looking forward to that. Can't wait to see what the team comes up with.

I'll try to blog as the battery life allows this week. PASS has gone paperless, so the only way to access slides is to print your own or carry a laptop. I've elected for the latter, but with the competition for power today, this might have been a bad idea!

Jon