Data warehouse data modeling

Data Warehousing Concepts

Aaren Stubberfield

Data Scientist

Data models

Bottom-up, Kimball model = star & snowflake schemas
Denormalized data models

Image of two database icons with arrows pointing to an arrow labeled ETL and arrows pointing to two data mart icon with arrows pointing to a data warehouse icon with arrows pointing to an icon of a person on a computer, a reporting icon, and magnifying glass icon

It's Bravo again!

Hypothetical publicly traded company
- Sells home office furniture

Home office with a desk, and chair

¹ Photo from Pexel by Pixabay

Fact table

Measurements, metrics, or facts about an organization
Links to dimension tables for more details

Table Name: Sales_Order_Fact

Keys	ColumnName
FK	CustomerID
FK	DateID
FK	ProductID
	UnitSold
	SalesAmount
	Tax

Legend: FK = Foreign Key

Dimension table

Dimensions/attributes about a process
Holds reference data
Dimension tables add more detail to fact table

Table Name: Customer_Dim

Keys	ColumnName
PK	CustomerID
	Name
	AccountNum
	LoyaltyID
	Country
	Email

Legend: PK = Primary Key

Star schema

A central fact table, with one or more dimensional tables
Easy for business users

Image of central sales order table surrounded by product, customer, and time dimensional tables

Snowflake schema

Dimensional table connected through another dimensional table

image of central sales order table surrounded by product, customer, time dimensional tables, and country table connected to customer

Let's practice!

Data Warehousing Concepts