What tools support data warehouse-specific modeling techniques? When considering a data modeling tool for a warehouse-specific implementation, what techniques are needed and what features should we look for?


Chuck Kelley’s Answer: Any of the main modeling tools support data warehouse-specific modeling techniques. The ones that come to mind are ERwin (Computer Associates), System Architect (Popkin), PowerDesigner (Sybase) and Oracle Designer (Oracle).

Les Barbusinski’s Answer: The only additional data modeling technique that data warehousing utilizes that is not used in modeling other types of systems is dimensional modeling (i.e., the graphic depiction of fact and dimension tables in star schema and snowflake arrangements). To my knowledge, only two data modeling tools support this: CA ERwin and Sybase PowerDesigner. However, this should not be a limiting factor in your choice of modeling tool, because this capability – though convenient – frequently limits flexibility. For example, the tool may not allow you to copy tables from a dimensional schema into a non-dimensional schema … even if the target database is the same. In other words, having a dimensional modeling capability in the tool is not a big deal. You can achieve much the same results in your data model without using the dimensional modeling capability.

Other capabilities are much more important in a good data modeling tool. These include the following:

  • Support for all major RDBMSs (e.g., Oracle, DB2, Teradata, SQL Server, Sybase, Informix, etc.) and all RDBMS objects (e.g., tables, views, indexes, tablespaces, triggers, etc.).
  • The ability to add user-defined properties (e.g., domains of value, aliases, source table/column names, update methods and frequencies, business rules, transformation algorithms, etc.) to data model objects such as tables and columns.
  • The ability to accurately transform selected objects in a logical data model into one or more physical data models.
  • The ability to define the storage properties of tables and/or tablespaces (e.g., extent sizes, freespace values, caching parameters, partition ranges, etc.).
  • The ability to store volumetrics for various object types.
  • The ability to reverse engineer a physical data model directly from a database.
  • A UML-compliant model repository with object- level check-in/check-out capability (with object "locking") and an import/export capability that lets you exchange information with other UML- compliant tools such as ETL and BI meta data repositories

The three most widely used data modeling tools – ERwin, PowerDesigner and Embarcadero’s ER/Studio – all have these capabilities to some degree. It’s up to you to choose the one that best meets your needs. Hope this helps.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access