Dimensional Modeling in a Nutshell
Dimensional Modeling is the process of creating de-normalized relational data models that allow the convenient capture and presentation of business indicators and their attributes.
Measures, also known as facts – or measurements when using the grammatically correct. These are the indicators defined by the business to study a particular business process. Measurements can be additive, semi-additive, and non-additive.
Dimensions give business context to Measures and essentially represent the descriptive value by which measures are decomposed or filtered.
A Star Schema is a de-normalized data model of a business process. It is normally made of one central fact table connected to multiple dimension tables.
Slowly Changing Dimensions are a set of common and proven techniques to capture historical information on a dimensional model.
Fact tables record the values associated to measures at each intersection of a set of dimensions. Fact tables are made of Dimension columns and measure columns.
Grain is the level of detail recorded in a fact table; that is, the grain is the atomic view of a business process and drives the set of dimensions associated to a measurement.
Drilling Across is the process of comparing measures from different business processes (start schema). Back in the days, this used to be a fairly manual process, today drilling across is normally automatically implemented by most BI tools by taking advantage of conformed dimensions.
- Special attention must be given to the modeling of business processes. Overall, we have the following major scenarios:
a. Business processes with different grains require different fact tables.
b. Business processes that are measured at different intervals require different fact tables.
c. Identifying sub-processes in a business process is normally a sign that it can be broken down into different fact tables as items a or b will be true.
- It is extremely important to choose the right fact table type when modeling a business process as this is critical to guarantee your success, keep in mind that you may need different complementary fact tables to model properly the same business process. Overall, we have the following fact table types:
a. Transaction Fact Table.
a. Periodic Snapshot Fact Table.
b. Accumulated Snapshot Fact Table.
c. Factless Fact / Coverage Fact Table
c. Aggregate Fact Table
c. Core and Custom Fact Tables