Dimensional Modeling Essentials

• Dimensional modeling is a logical design technique similar to enterprise relationship (ER) modeling.
• Dimensional modeling is well-suited for business intelligence (BI) applications and data warehousing (DW).
• It represents business processes and organizes data and its structure.
• The purpose of dimensional modeling is to enable BI reporting, querying, and analysis.
• The key concepts are facts, dimensions, and attributes.
• There are different types of facts, based on whether they can be added together.
• Dimensional modeling is used by data warehouse designers to build their data warehouse.
• This model is stored in two types of tables: Fact tables and Dimension tables.
• Fact tables contain business facts and measurements.
• Dimension tables contain the context of those measurements.

Dimensions

• Dimension tables describe the dimensions involved.
• Dividing a data warehouse project into dimensions provides structured information for analysis and reporting.
• End users query these dimension tables for descriptive information.
• Example E-commerce Company Dimension Tables include Customer, Product, and Date.
• Customer table contains attributes such as ID, Name and Address
• Product table contains attributes such as ID, Name, and Type
• Date table contains attributes such as order date, shipment date and delivery date

Facts

• A fact is a measure that can be summed, averaged, or manipulated.
• A fact table contains a dimension key and a measure.
• Every dimension table is linked to a fact table.
• Example Fact Table includes Dimension Product and a Dimension key: Product ID with a Measure such as Number of units sold

Types of Measurements in a Fact Table

• Additive facts can be fully summed across any dimension.
• Semi-additive facts can be summed across some dimensions but not all.
• Non-additive facts cannot be summed across any dimension.
• Additive facts can be summed up through all of the dimensions in the fact table.
• An example of this is a table to record sales amounts for each product in each store on a daily basis
• Sales_Amount is the fact, and is an additive fact as it can be summed along any of the dimensions (date, store, product).
• For example, the sum of Sales_Amount for all 7 days in a week represents the total sales amount for that week.
• Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
• An example of this is a table to record the current balance for each account at the end of each day, as well as the profit margin for each account for each day.
• Current_Balance and Profit_Margin are the facts.
• Current_Balance is a semi-additive fact, as it makes sense to add them up for all accounts (what's the total current balance for all accounts in the bank?), but it does not make sense to add them up through time (adding up all current balances for a given account for each day of the month does not give us any useful information).
• Non-additive facts cannot be summed up for any of the dimensions present in the fact table.
• Looking at the same bank example, Profit_Margin is a non-additive fact since it does not make sense to add profit margins up for the account level or the day level.

Types of Dimensions

• Conformed dimensions.
• Slowly changing dimensions (SCD).
• Fast changing dimension (Mini Dimension).
• A conformed dimension can be associated with different fact tables, maintaining the same meaning across all of them.
• A typical conformed dimension is the date, as its meaning does not vary by fact table.
• Most data warehouses have a single date dimension shared by all fact tables.

Slowly Changing Dimensions (SCD)

• These dimensions store data which, although generally stable, may change over time in an unpredictable manner.
• This contrasts with rapidly changing transactional parameters like product quantity and price, which undergo frequent updates.

Types of SCD

• Type 0: Retain original: Never change and are assigned to attributes that have durable values or are described as 'Original' e.g. Date of Birth, zipcodes, and county codes.
• Type 1: Overwrite: Data is overwritten by new data without keeping a historical record such as customer addresses
• Type 2: Dimensions are always created as a new record "row"
• If a detail in the data changes, a new row will be added to the table.
• Type 3 dimensions track changes in a row by adding a new column.
• Instead of adding a new row with a new primary key like with type 2 dimensions, the primary key remains the same and an additional column is appended.
• This good where the primary key needs to remain unique.
• Only one change can be tracked in a record rather than multiple changes over time.
• An example of this is adding a column of previous location so previous locations can be tracked.
• Type 4 dimensions exist as records in two different tables which include a current record table and a historical record table.
• All the records that are active in a given moment will be in one table and then all of the records considered historical will exist in a separate history table.

Fast Changing Dimension (Mini Dimension)

• This is used when dimensions have one or more attributes changing quickly leading to separation of attributes into one or dimensions called mini-dimensions.
• To handle FCD, start by identifying the fast changing columns in dimension and split the fast changing columns to a separate junk dimension and map the junk dimension with the main dimension using mini-dimension
• An example of this is When we have a dimension with one or more of its attributes changing very fast Patient_id, Name, Gender, BirthDate, Weight, B_Pressure, UpdateDt
• In this case the Weight and B_Pressure columns are FCD.
• To combat this, start by splitting the FCD column from the other columns
• Static columns: Patient_id, Name, Gender, BirthDate
• FCD: Patient_Key, Weight, B_Pressure
• Create a Patient Mini Dimension with Patient_id, Patient_Key, Start_Date, End_Date
• relationdim table is added to link between the static and FCD tables

Schemas

• A schema is the logical description of the entire database.
• It gives details about the constraints placed on the tables, key values present and their links.
• A database uses a relational model, while a data warehouse uses Star, Snowflake, and Fact Constellation schemas.
• Types of schemas: Star schema.