Dimensional Modeling |Part 1: Introduction and Fact Types

Dimensional Modeling

Dimensional modeling is one of the data modeling techniques used for designing the data warehouses, It also considered a suitable technique for representing analytic data, because it understandably delivers data for users and is optimized for query performance which increases the data retrieval speed.

Normalized databases are very useful in transactional processing because any data manipulation operation hits the database in one place due to table normalization. But the normalized form is complicated for BI queries and data warehousing (read-intensive), due to normalization the query performance isn’t optimal because when we need to retrieve data from multiple tables to satisfy user need we have to write many join queries which will overwhelm the performance and retrieval time. Fortunately, Dimensional modeling addresses this problem and solves it with its de-normalized format.

Dimensional model is created using the normalized system information itself, and it’s all about de-normalizing the 3NF system to improve query performance and increase end-user understandability.

Benefits of Dimensional modeling

• Maximize understandability to the end-user.
• The de-normalized model improves query performance.
• Easily handle new data.

There are two key components of dimensional modeling: Fact and Dimensions tables.

So now We can say that dimensional modeling is a special data model that can be considered as an alternative to a 3NF model, a Model that consisting of ER information itself, but combines the data in an abbreviated form that makes the model more understandable to perform the queries efficiently, and has the flexibility to change.

Fact Tables

The fact table is the main table in our dimensional model which Located at the center of our dimensional schema and surrounded by dimensions. Fact table stores the measurements resulting from an organization’s business process events.

The term fact represents a business measure.

Each row in a fact table corresponds to a measurement event. Ex: Every row in our fact table could be a product sold in a transactional process. Facts Defined by their grain or a specific level of detail.

Grain: Is the level of detail or the depth of the information that’s stored in the data warehouse.

Grain answer this type of questions:

• Do we store all products or specific categories?
• We will use data from week or month or year?
• We will hold sales per day or per product or per store?

Only facts consistent with the declared grain are allowed.

All the rows in the same fact table must be at the same grain, Different grains must not be mixed in the same fact table, For example, we cannot have the same Fact table that has measures about sales and about use complaints, these are two different grains cannot co-exist in the same Fact table.

Dimension Tables

Dimension tables are the Complementary factors of the fact table. Dimension tables stored the context related to the business process measurement. It answers the questions (who, what, where, when, how, and why) to cover all aspects of the business process.

Example: When the user needs to see all sold products from a specific store, We should have this store as a dimension attribute in our dimensional model. Another Example: If the user needs to calculate the total profit from some products in a specific year, we should have this year and products as dimension attributes.

Dimension table defined by a single primary key which ensures the referential integrity with the fact table to which it is joined.

All fact tables have foreign keys that refers to the dimension tables primary keys to easily connect them to produce specific measure.

Each dimension is an entry point to the fact table, which gives us great handling of the complexity of queries.

Example

If our business process is calculating the sales amount of each product in each store on a daily basis. So we have two aspects of the process:

• Measures: Quantity Value, Value-added tax, and Total order value.
• Business process details.
  • What was the product? (product name)
  • When did the transaction happen? (Date)
  • Where did the product been sold? (Shop)
  • Who did the people that create the transaction? (Customer) and (Employee)

According to the above details, we can say that:

• The measurements or facts (Quantity Value, VAT, and Total order value).
• The Dimensions that answered the questions about the business process (Date, Employee, Customer, Shop, Product).

In this example, Fact grain set at a single selling transaction.

The Dimensional modeling for this business process.

Types of Fact Tables

There are three main types of fact tables:

• Transaction
• Periodic Snapshot
• Accumulated Snapshot

Transaction Fact Table

The transaction fact table is the most basic and the simplest type, In the Transaction fact table, every row corresponding to a measurement event at a point in space and time which means a grain set at a single transaction. The row is added only if there’s a transaction is happened by a customer or for a product.

Row in Transaction fact table represents transaction and it dimensions.

Example: If we have a retail store, on Sunday we sold 40 items, and on Monday we sold 15 items, So on Sunday there are 40 transaction rows had been added to our fact table and on Monday there are 15 transaction rows had been added to our table, There are no aggregate values, we are storing the transactions data.

An example of a transaction fact table, the user wants all selling transactions that are happening in the stores, Our dimensions will be the date or time of transaction -> (when), store -> (where), product -> (what), and customers -> (who).

Transactional_Sales_Fact

dateKey	storeKey	productKey	customerKey	SalesAmount
12	3	59	157	33$
14	5	64	245	124$
3	2	24	157	25$

Every row in our transaction fact table is corresponding to a transaction, Let us see the first row in the above table Transactional_Sales_Fact, The row reflects transaction that happened by customer 157 when he bought product 59 from store 3 at time 13 and the item cost was 33$ (the fact), So our row covers all aspects of the single transaction. And like we said before every foreign key in our fact table refers to one of the dimension tables.

Because of low granularity, we can monitor detailed business processes. But because we have rows for each transaction that happened, It causes performance issues due to the large size of data.

Periodic Snapshot Fact Table

In the periodic table, We have lower granularity which means that the row in the periodic table is a summarization of data over a period of time (day, week, month, etc). Our grain here is periodic data summarization, not single transactions. It helps to review the aggregate performance of the business process at intervals of time.

Example: If we want to know the quantity that been sold from specific product through the last week. Our grain is a week.

Rows in Periodic table represent performance of an activity at the end of specific period.

An example of Periodic Snapshot, If the user asks for the total quantity of each product that has been sold from our stores in month grain. It’s obvious that the dimensions are items(what), stores(where), periods of time(months, grain, when).

Quantity Fact

monthKey	storeKey	productKey	Total_Quantity
4	12	5	25
6	4	35	124
4	12	28	80

Now every row in our Periodic fact table is corresponding to summarized measurements of product, Let us see the first row in the above table QuantityFact, The row reflects the Total quantity that been sold of product 5 from store 12 at time 4.

Because of summarized data, our performance now is better than the transaction fact table. But now we have higher grain, So we loss the detailed business processes that we had in the transaction fact table.

Accumulated Snapshot Fact Table

In Accumulating snapshot fact table, Row represents an Entire process, which means that our row corresponding to measurements that occurred at defined steps between the beginning and end of the process. we use it when users need to perform pipeline and workflow analysis like Order fulfillment.

Row in Accumulating snapshot represents an Entire process

There is a date foreign key in the fact table for each critical activity in the process.

An example of Accumulating snapshot fact table

Order fulfillment covers the complete process from when a sale process takes place all the way through delivery to the customer. So here we have multiple activities in the process, First when we receive an order from a customer, then send the order to the inventory to organize it, then move the order to the shipment activity, and finally, the customer receives his order.

In this case, we have many activities each of them has a predictable start date and end date, So row in our accumulating snapshot fact table, corresponding for instance to a line on an order, Row is inserted when the order line is created. As workflow progress occurs, the accumulating fact table row is revisited and updated. This means we first create our row with empty attributes, then when the activity is finished we update the date in the fact table and repeat till the pipeline or our order line is finished successfully.

After placement of the order

customerKey	orderKey	Statue	placement_dateKey	organizing_dateKey	shipment_dateKey	received_dateKey
13	4	0	20/1/2021	–	–	–

After organizing the order to be shipped

customerKey	orderKey	Statue	placement_dateKey	organizing_dateKey	shipment_dateKey	received_dateKey
13	4	1	20/1/2021	22/1/2021	–	–

After shipping the order

customerKey	orderKey	Statue	placement_dateKey	organizing_dateKey	shipment_dateKey	received_dateKey
13	4	2	20/1/2021	22/1/2021	23/1/2021	–

After the order is received by the customer

customerKey	orderKey	Statue	placement_dateKey	organizing_dateKey	shipment_dateKey	received_dateKey
13	4	3	20/1/2021	22/1/2021	23/1/2021	24/1/2021

If we look at the process progress we will see that we have a dateKey for each activity that is set to NULL till the activity is achieved, This is the explanation of what we said above “there is a date foreign key in the fact table for each critical activity in the process”. And we have the statue to keep track of the process progress.

Accumulating Snapshot fact table helps us in complex analysis, workflow, and pipeline process, on another hand It needs high ETL process complexity.

	Transaction	Periodic	Accumulating
Row	Transaction and It dimensions.	summarized data over a period of time.	Entire process activities.
Granularity	Lowest granularity 1 row / Transaction	Higher than transaction.1 row / period	Highest granularity 1 Row / Entire process
Table Size	Largest	Smaller than Transaction	Smallest
Example	Sales amount of products on a daily basis.	Total sales amount of product through May.	Order fulfillment

Factless Fact Table

Factless facts are fact tables that haven’t any measures, It only has a foreign key for each dimension. We can say that Factless fact is only an intersection of Dimensions Or A bridge between dimension keys.

Example

Student_Attendance_fact

FK StudentKey

FK ClassKey

FK TimeKey

This fact table shows the attendance of students, There are no measurements here, Only the foreign keys that refer to dimensions.

Types of Measures

There are 4 main types of Measures in Dimensional modeling:

• Additive
• Semi-Additive
• Non-additive
• Factless

Let us demonstrate every type of them.

In the examples that we would mention below for each type, We would use real values for each dimension instead of IDs (keys) that refer to the dimension table to make it more clear and easy to be understood, But in the real life you should use the IDs (Keys) that refer to the Dimension tables, and get the value of it attributes through it (Like we did in the above examples EXCEPT accumulating snapshot).

Additive Measure

Additive measures are the most flexible and useful measures because they can be summed across any of the dimensions associated with the fact table. If we have 4 Dimensions associated with the fact table, Additive measures can be summed through 4 of them.

Example Our fact table represents the sales amount of each product in each store on a daily basis. Our dimensions (Store, Date, Product)

TransactionID	Date	Store	product	unit_price	quantity_sold	Sales_amount
2	20/1/2021	Cairo	A1	20$	4	80$
4	20/1/2021	Alexandria	A3	23$	2	46$

Here, Sales_amount measure can be summed through all dimensions (Store, Product, Date), We can get sales_amount per specific store, date, and product. So Sales_amount is an example of the additive fact.

Semi-Additive Measure

The semi-additive measure can be summed across some dimensions associated with the fact table, but not all. If we have 4 Dimensions associated with the fact table, Semi-Additive measure can be summed through 1,2,3 of them but not all of them.

Example Our fact table is the same as the above but we add measure about the quantity of product in our inventory.

TransactionID	Date	Store	product	unit_price	quantity_sold	Sales_amount	Items_Inventory
14	23/1/2021	Alexandria	C3	25$	3	75$	7
19	24/1/2021	Aswan	C3	25$	5	150$	2

Here, Items_Inventory measure can be summed through product, But Items_Inventory can’t be summed through date. So Items_Inventory is an example of the semi-additive measure.

Non-Additive Measure

Non-additive measures cannot be summed up for any of the dimensions associated with the fact table. If we have 4 Dimensions associated with the fact table, Non-Additive fact cannot be summed through 4 of them.

Example Our fact table is the same as the above.

TransactionID	Date	Store	product	unit_price	quantity_sold	Sales_amount	Items_Inventory
6	25/1/2021	Aswan	C3	25$	3	75$	7
19	26/1/2021	Cairo	C3	25$	5	150$	2

Here, unit_price measure cannot be summed through any dimension (Store, Product, Date). So unit_price is an example of Non-additive fact.

---