Declaring the GrainIt's the most important dimensional design step after identifying data sourcesContinued from Page 1 The discipline of insisting on the grain declaration at the beginning of a dimensional design keeps you from making this kind of mistake. A model of billable doctor visits that included treatment outcome would look like a dimensional model but it wouldn't be implementable. This is my main gripe with many of the current offerings of "standard schemas" in books and CDs. Because they have no grain discipline, they often combine entities that don't coexist in real data sources. Every fact table design must be rooted in the realities of available physical data sources. A second major insight from the doctor bill line item grain declaration is that this very atomic grain gives rise to many dimensions! I listed 10 dimensions, and experts in health care billing probably know of a few more. It's an interesting realization that the smaller and more atomic the measurement (fact table record), the more things you know for sure, and the more dimensions you have. This phenomenon is another way of explaining why atomic data resists the ad hoc attack by end users. Atomic data has the most dimensionality and so it can be constrained and rolled up in every way possible for that data source. Atomic data is a perfect match for the dimensional approach. In the doctor bill line item example with 10 dimensions, you wouldn't expect the primary key of the fact table to consist of all 10 dimensional foreign keys. Logically from a business rules perspective, perhaps we know that the combination of date, doctor, patient, and procedure are enough to guarantee a unique record. So these fields could implement the fact table primary key. Or, it is possible that we have extra "degenerate" keys for the patient bill number and the line item number that would, by themselves, implement an acceptable physical fact table key. But we are confident that we can add these degenerate dimensions to the design because they are consistent with our grain declaration. The grain declaration is like a contract! The grain declaration lets us think creatively about adding dimensions to a fact table design that may not obviously be present in the source data. In retail sales data, marketplace causal factors like promotions and competitive effects may be very important to understanding the data, but this information may not be present in a literal sense in the data extract. The grain definition (see the first example in our list of grains) tells us that we can indeed add a causal "store condition" dimension to the fact table as long the store condition descriptions vary appropriately by time, product, and store location. A weather dimension can be added to many fact table designs using the same logic. Once such a new dimension is identified, it is incumbent on the data warehouse designer to find the appropriate store condition or weather data source and insert it into the backroom data staging applications that build the fact tables. All of the grain declarations listed in this article represent the lowest possible granularity of their respective data sources. These data measurements are "atomic" and cannot be divided further. But it is quite possible to declare higher level grains for each of these data sources that represent aggregations of atomic data:
These higher levels of aggregation will usually have fewer, smaller dimensions. The doctor example might end up with only the dimensions:
It would be nonsensical in an aggregated fact table to try to include all the original dimensions of the atomic data, because you would find yourself recapitulating the atomic level of the data! Useful aggregations necessarily shrink dimensions and remove dimensions; therefore, aggregated data always needs to be used in conjunction with its base atomic data because aggregated data has less dimensional detail. Some developers get confused on this point, and after declaring that data marts necessarily consist of aggregated data, they criticize the data marts for "anticipating the business question." All these misunderstandings disappear when aggregated data is made available along with the atomic data from which it's derived. Keep Facts True to the GrainThe most important result of declaring the grain of the fact table is anchoring the discussion of the dimensions. But declaring the grain lets you be equally clear about the measured numeric facts. Simply put, the facts must be true to the grain. In the doctor example, the most obvious measured fact would be "billed amount," relating to the specific line item. Other facts relating to the treatment that patient received at that time may also exist. But helpful facts, such as the amount billed year-to-date to this patient for all treatments, aren't true to the grain. When a reporting application combines fact records arbitrarily, these untrue-to-the-grain facts produce nonsensical, useless results. Viewed in this way, these facts are dangerous because they invite the end user to make mistakes. Omit them from the design. Calculate such aggregate measures in your application. Brief RecapIn summary, try to do your dimensional designs using the following four steps, in order: 1. Decide on your sources of data. 2. Declare the grain of the fact table (preferably at the most atomic level). 3. Add dimensions for "everything you know" about this grain. 4. Add numeric measured facts true to the grain. Write to me (ralph@ralphkimball.com) with questions or comments about declaring the grain. Ralph Kimball (founder of the Ralph Kimball Group) co-invented the Star Workstation at Xerox and founded Red Brick Systems. He has three best-selling data warehousing books in print, including The Data Warehouse Toolkit, Second Edition (Wiley, 2002). He teaches dimensional data warehouse design through Kimball University and critically reviews large data warehouse projects. You can reach him through his Web site, www.ralphkimball.com.
|
 |
Most Popular This Week
IE Weekly Newsletter
Subscribe to the newsletter
| |
| ||||||||||||||||||||||||||||||||
