Uses of a data modeling tool

I'm currently researching data modeling tools and my company is hesitant to purchase one because they're comfortable with scripting everything out then doing a reverse engineer to show the model in something like Visio. I'm curious what tools everyone is using and what are some of the advantages seen in using the tool and using it before putting together DDL.

I've used ERWin, Embarcadero's ERStudio, and Sybase Power Center.

ERWin is probably the most widely used, but my personal opinion is ERStudio is the best of the three. It's GUI is stable and it does a really good job of producing user friendly model documentation. One of the documentation features is it will produce HTML that displays the model (and subject areas) with drill downs into column and table descriptions and comments, so it is real easy to make it available on the web.

The big plus is visually being able to see your data. Much like how BI visualizations enable business folks to visualize their KPI's, data models can help you easily understand complex data relationships and dependencies. I've spent many hours reverse engineering tables (that have no RI) to determine how to join the tables. You randomly guess at the join columns until you think you find the right combination. You never really know unless you dig into ETL or application code to discover the true relationships. A data model will make this a simple task and save countless hours.

Thanks for the great input! Have you found it quicker to do the model for a star schema first and have it generate DDL, or quicker to write the DDL and create the tables then do a reverse engineer (with having FK's)? I realize there's an advantage to having the model first as then if there's changes through a model review it could be simpler to alter the model and have it ready first prior to doing DDL ... but to some maybe this is just as easily done by writing the DDL and making those changes that way rather than through a model and then just reverse engineer every time they want to show the updates. Just curious what you guys have found to be more efficient. Thanks!

Personally I prefer to script out the model in DDL first and reverse engineer back to diagram for communicating the design with others. I found it's far quicker to type DDL scripts in any text editor than in modeling tools, Erwin for example.

I think most modeling tools are trying to forward engineer entire DDL by setting properties visually, assuming modelers don't bother, or don't know how, to write DDL. Well, I can't imagine a good modeler can get way from writing DDL. I guess it would take less time to google out how to script a particular DDL feature than to find it out in the tools, and knowing how to write DDL is more important.

I guess what would be really helpful with modeling tools is more focus on how the model can be made less cluttered with minimal cross lines. At the end of the day, modeling tool is about how to make the picture clearer with minimal effort, not how to implement the model design in DW, or RDBMS in general. A good RDBMS should make the drawing part of the modeling as less painful as possible.

I believe there is a lot of market hype in every part of DW, even in the modeling area where no vendor specific skills should be required.

We use DeZign to do our database modelling. It's pretty simple to use and produces good results. Worth a trial if you don't want to spend that much money.

Hi jball,

I've been using the utilities that came with the Kimball MSDWTK book for several years on many projects with quite good results. For software you only need Excel so you can save a lot of time and money on software, training etc.

Here's the link:
http://www.kimballgroup.com/html/booksMDWTtools.html

In particular, Chapter 2, Dimensional modeling spreadsheet - this can be used to generate usable DDL. You can then use the spreadsheet "as is" for your Metadata reference, or even feed your model metadata from Excel to a Metadata database and SSRS Reports (see Chapter 15—Metadata Plan).

Excel is actually quite a good tool for the design task, as the spreadsheet layout encourages comparison and consistency of definitions, and it's very easy to copy complete or partial definitions.

Good luck!
Mike

Hang, Hang, Hang. We need to go have a beer so I can get you back on the path of righteousness. It has nothing to do with knowing how to write DDL. It is all about building consistent, reusable, data structures, as well as capturing corporate metadata. Did I mention I just converted the DDL from DB2 AIX to Teradata for 50 tables? It took 10 seconds with ERwin. :-) (ok, I did have to go into every table and define the primary index).

hang wrote:Personally I prefer to script out the model in DDL first and reverse engineer back to diagram for communicating the design with others. I found it's far quicker to type DDL scripts in any text editor than in modeling tools, Erwin for example.

I think most modeling tools are trying to forward engineer entire DDL by setting properties visually, assuming modelers don't bother, or don't know how, to write DDL. Well, I can't imagine a good modeler can get way from writing DDL. I guess it would take less time to google out how to script a particular DDL feature than to find it out in the tools, and knowing how to write DDL is more important.

I guess what would be really helpful with modeling tools is more focus on how the model can be made less cluttered with minimal cross lines. At the end of the day, modeling tool is about how to make the picture clearer with minimal effort, not how to implement the model design in DW, or RDBMS in general. A good RDBMS should make the drawing part of the modeling as less painful as possible.

I believe there is a lot of market hype in every part of DW, even in the modeling area where no vendor specific skills should be required.

I start always with graphical design of my model. Even several times with

1.a logical data model, where I can represent the „conceptual” dimensions with all hierarchy levels, and I can validate it with the business users ( it is like a snowflake dimensional model)

2. we can generate the next physical level, where we can make several changes e.g.
• denormalize the dimensions,
• introduce various technical dimension (mini, junk),
• introduce views (for instantence for role playing dimension) e.t.c

3. third level is of course the DDL level which can be generated automatically.

As I normally implement the DW in Oracle ralational DB, I use Oracle modelling tools( Designer or SQL Developer Data Modeller).