SQL Server Magazine

In "Discover the Star Schema," July 2007, InstantDoc ID 96112, I reviewed the basics of dimensional modeling. You can use dimensional modeling to create an enterprise data warehouse that makes it easier for business intelligence (BI) end users to access and understand data. Now let's examine the differences between entity relationship (ER) and dimensional models, the components that make up the dimensional model, and why those pieces are designed the way they are.

Dimensional vs. ER Modeling
Dimensional modeling is a logical design technique. Unlike ER modeling, which consists of conceptual, logical, and physical data modeling, dimensional modeling is made up of only logical and physical modeling.

There are sharp contrasts between ER and dimensional modeling. ER modeling is a design discipline that seeks to represent business rules as highly detailed relationships between business elements that are materialized as database tables. You can extrapolate the business rules from the types and cardinalities of the relationships in an ER model. The primary goal of ER modeling is to remove all non-key data redundancy.

Dimensional modeling, however, seeks to represent data in a logical, understandable manner. In dimensional modeling, you can control data redundancy by conforming dimension and fact tables. A table that's been conformed can be used in more than one dimensional data model and is the same no matter how it's used. The relationships in a dimensional model don't represent business rules; instead, they're navigational paths used to help write reports or create graphs.

Many data modeling software packages support dimensional modeling. Some even let you generate SQL Data Definition Language (DDL) scripts so that all you have to do to create a data warehouse/data mart is run those scripts.

Determining the Facts
In the world of BI, data elements that can be summed, averaged, or otherwise mathematically manipulated are called measures. A dimensional model is designed to deliver numbers to BI users, and measures are the core of the model. In Figure 1, the fact table Reseller_Sales contains both measures and keys. Measures are data elements such as OrderQuantity, DiscountAmount, and TaxAmount that are used to determine all types of statistical information, such as the percentage of sales that were discounted or the total sales tax collected per region. Like foreign keys in an ER diagram (ERD), keys in a dimensional model are expressions of a relationship to a dimension table. In contrast to foreign keys in an ERD, however, the relationship isn't intended to enforce referential integrity; after all, the data has already been through the extraction, transformation, and loading (ETL) process, having been scrubbed and validated before being loaded into the data warehouse. Instead, the relationship's function is to associate keys in the fact table with the expanded definitions, which are found in the dimension tables.

In a data warehouse environment, you rarely retrieve just one record. Typically, hundreds, thousands, or even millions of records are retrieved in a single query, and the most logical thing to do with record sets that large is to perform a mathematical process on their data. That's why the section of a fact table that contains the numeric and additive columns is arguably the most important section.

You can start your dimensional design by reviewing the ER model of the transactional data source (the operational database). You can usually identify potential fact tables by locating the associative tables that represent the many-to-many (M:N) relationship in an ER model. An ER model will break down into multiple dimensional diagrams: The number of diagrams is determined by the number of functions in the organization and the organization's BI reporting needs.

To create a dimensional model from an ER model, first separate the ER model into its discrete business processes. Each business process can be expressed as a data mart—a modular, highly focused, richly detailed, incrementally designed component of the enterprise data warehouse. Although there are many ways to approach and implement an enterprise data warehouse, the data mart approach lets you tackle the data warehouse project business-process-by-business-process and produce deliverables for your user community.

If this is your first excursion into dimensional modeling, start with a single-source data mart; don't try to tackle multiple-source data marts until you've acquired the skill set necessary to design a complex data warehouse. Examples of single-source data marts include retail sales, purchase orders, shipments, and payments. An example of a multiple-source data mart is customer profitability, which combines revenue and costs that often come from separate transactional sources (e.g., the sales database and the inventory database, respectively).

For each preliminary data mart, identify the M:N relationships from the transactional model that are comprised of numeric and additive non-key data. These relationships will most likely be the associative tables in the ER model. Designate these as fact tables. It's not unusual for a large ER model to produce a dimensional model that has from 10 to 25—or even more—fact tables.

   Prev. page   [1] 2     next page