There have been entire books and methodologies dedicated to dimensional modeling. This is not intended to expand or endorse any particular methodology, but to give a brief overview of dimensional modeling techniques, for those who are not familiar with them. Part 2 (to be published at a later date) will provide additional details on “levels” in dimensions, as well as practical guidance on when and how to use these modeling techniques (as well as when not to use them).
Dimensional modeling has only two types of tables: dimensions and facts. Facts contain only foreign keys to dimensions and measures, which are values that are aggregated across the combination of dimensional keys that exist in the fact. For example, a sales fact table may contain “measures” such as units sold and sales amount (measured in currency). In its simplest form, dimensional modeling results in what is commonly referred to as a “star schema”, as opposed to various forms of “snowflake schema”. The simple examples that follow are based on a basic “star schema” design.
Dimensions can be modeled in various forms or “types”, in theory. The two most common types are “Type 1” static dimensions and “Type 2” slowly changing dimensions. Changes to “Type 1” static dimensions are treated as corrections, rather than different “versions” of the same record. These dimensions are updated in place, or new values are added. “Type 2” slowly changing dimensions, on the other hand, are treated as separate states “or versions” that can change over time.
The most basic and always necessary example of a “Type 1” static dimension is time. This is the most common dimension in any dimensional model and needs to be very static. It should contain all values for historical and future dates. In lieu of populating it with years into the future, a simple maintenance procedure can be written to add values to the time dimension slightly before they will be used. An example of the logical representation of time may be:
TIME_DIM_ID int identity
This assumes that the fact or fact(s) are aggregated by day. The values are easily generated, but assume the following changes as corrections (to the data and the script that populates the dimension):
TIME_ID DATE_VALUE MONTH QUARTER YEAR
1 2013-01-01 Jan2013 Q1 2013
366 2014-01-01 Jan2014 Q1 2014
731 2015-01-01 Jan2015 Q1 2015
TIME_ ID DATE_VALUE MONTH QUARTER YEAR
1 2014-01-01 January 2013 2013 Q1 2013
366 2014-01-01 January 2014 2014 Q1 2014
371 2015-01-01 January 2015 2015 Q1 2015
This correction may be necessary for proper formatting and sort order in reporting. It is not necessary to “version” such data, as it has no impact on any fact tables using this dimension. The keys are unchanged, only dimensional values are corrected to satisfy formatting and sorting requirements. In this example, the TIME_DIM_ID may be an identity column or sequence generated number. It has no meaning. However, it might be the integer representation of a date in ISO format, such as 20140101.
“Type 2” slowly changing dimensions are treated differently when updates occur, because changes must be accurately reflected in the related facts. There are several ways to represent “Type 2” dimensions. For example, assume that the product dimension is based on new car sales, and is logically modeled as:
PRODUCT_ID int identity
Changes to attributes in this type of dimension require new keys and versioning (based on begin_date and end_date in this simple example). When building a new schema, unknown start dates are often represented as existing from the beginning of time. Unknown end dates are often represented as the end of time. These values will vary based on the database vendor, but consider the following values as an example of a “Type 2” change, with this state as of 2015-01-01:
PRODUCT_ID YEAR MODEL MAKE TYPE BEGIN_DATE END_DATE
1 2014 Jetta Volkswagon Sedan 2013-10-01 9999-12-31
2 2015 Jetta Volkswagon Sedan 2014-10-01 9999-12-31
3 2015 Touareg Volkswagon SUV 2014-10-01 9999-12-31
All changes are versioned. Assume that there are changes and additions to this product dimension on 2015-02-01, they would be reflected as below:
PRODUCT_ID YEAR MODEL MAKE TYPE BEGIN_DATE END_DATE
1 2014 Jetta Volkswagon Sedan 2013-10-01 2014-01-31
2 2015 Jetta Volkswagon Sedan 2014-10-01 2014-01-31
3 2015 Touareg Volkswagon SUV 2014-10-01 2014-01-31
4 2015 Jetta SE Volkswagon Sedan 2015-02-01 9999-12-31
5 2014 Touareg V6 Volkswagon SUV 2015-02-01 9999-12-31
6 2015 Jetta TDI S Volkswagon Sedan 2105-02-01 9999-12-31
What do these changes represent? For product id 1, it is no longer being sold by the business. Product id 2 and 3 were not properly classified, so old versions were “closed off” and new versions were created. In other words, the 2015 model values were not specific enough in the original data, and have been made more specific going forward. The changes in ID (the primary key) result in the proper representation reference in facts based on this dimension. All fact rows prior to 2015-02-01 will reference the old versions (id from 1-3), while all fact rows from 2015-02-01 will reference the new versions (id 4-6).
For purposes of example, consider a third dimension, also “Type 2”, based on location within the US, with the following structure:
LOCATION_ID int identity
Assume further that a fact table is created, using only these three dimensions, containing only two facts or “measures”:
TIME_ID int -- references the time dimension
PRODUCT_ID int -- references the product dimension
LOCATION_ID int -- references the location dimension
UNITS_SOLD int -- a fact
SALES_AMOUNT money -- a fact
Fact tables designed this way assume that the combination of dimensional keys are unique (which can be physically enforced) and that the “Facts” are aggregate numbers, aggregated over the combination of dimensions. This simple design can answer many questions at a high level:
- What is my best/worst selling product, this day, this month, this year?
- What location generates the most/least new car revenue, this day, this month, this year?
These questions can be asked at any level of detail within the dimensions. Time was included in the example, but consider the attributes (or levels, to be detailed in later installments) available in the product and location dimensions. The same questions can be addressed at the year/model/specific location level, or at a much higher level, such as vehicle type / region level. The example data may be hundreds or thousands of rows in each dimension, depending on the size of the business.
This type of design is not new and it wasn’t new in the mid-90’s when the terminology around “dimensions” and “facts” was invented. For further understanding of the terminology and methodology related to this type of modeling, please refer to the following references from the “founding fathers” of dimensional modeling:
For Additional Blog Post please check out NVISIA's Enterprise Data Management Page