Uses of a data modeling tool

View previous topic View next topic Go down

Uses of a data modeling tool

Post  jball on Sat Jul 21, 2012 6:09 am

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.

jball

Posts : 5
Join date : 2011-07-08
Location : United States

View user profile

Back to top Go down

Re: Uses of a data modeling tool

Post  ngalemmo on Sun Jul 22, 2012 12:57 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Uses of a data modeling tool

Post  BoxesAndLines on Mon Jul 23, 2012 10:24 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Uses of a data modeling tool

Post  jball on Fri Jul 27, 2012 4:20 pm

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!

jball

Posts : 5
Join date : 2011-07-08
Location : United States

View user profile

Back to top Go down

Re: Uses of a data modeling tool

Post  hang on Fri Jul 27, 2012 8:22 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Uses of a data modeling tool

Post  Dave Jermy on Wed Aug 01, 2012 3:49 am

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.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Uses of a data modeling tool

Post  Mike Honey on Thu Aug 02, 2012 10:48 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Uses of a data modeling tool

Post  BoxesAndLines on Fri Aug 03, 2012 9:08 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Uses of a data modeling tool

Post  gvarga on Tue Aug 07, 2012 9:52 am

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).




gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

Re: Uses of a data modeling tool

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum