[DWH] Dimensional Modeling

 

Dimensional Modeling 

Dimensional modeling is a database design technique to support business users to query data in data warehouse. The dimensional modeling is developed to be oriented around query performance and ease of use. It is important to note that the dimensional modeling is not necessary depends on relational database. The dimensional modeling approach is, at logical level, which can be applied for any physical forms such as relational and multidimensional databases.

In dimensional modeling, there are two important concepts:  facts and dimensions.

• Facts are also known as business measurements. Facts are normally (but not always) numeric values which could be aggregated.  Example of fact could be number of units sold.
• Dimensions are called context. Dimensions are business descriptors which specify the facts, for example product name, brand, quarter, etc. are components of dimensions.

Dimensional modeling process

The dimensional data model is built based on star schema with a fact table at the center surrounded by a number of dimension tables. The following four-step process is commonly used in dimensional modeling design:

1. Select the business process
2. Declare the Grain
3. Identify the dimensions
4. Identify the Fact

Let’s examine each step in the modeling process in a great detail.

1. Select the business process to model – business process is daily activities performed in your company that normally supported by an online transaction system (OLTP) or source system. In this step, we have to gather the requirements from business users to select the business process or source of measurement to model. Good examples of business processes are order processing, shipments, materials purchasing, GL…etc.
2. Declare the grain – after having a business process to model, we need to declare the grain of a business process. Declaring grain means describing exactly what a record in a fact table represents. The grains express the level of detail associating with facts in the fact table.
3. Identify the dimensions – in the third step, we add number of dimensions that represents all possible descriptions which take on single values in the context of each fact in the fact table. Date, time, product, customer, store… are several good examples of common dimensions.
4. Identify the facts – in the last step, we select the numeric facts that will be loaded into each fact table row. To identify the fact we need to answer the question “What are KPIs of the business process?” or “What are we measuring?”

Benefits of dimensional modeling

• Dimensional model have proved to be more understandable – in dimensional model, data is grouped into coherent dimensions that make it easy to read and interpret by business users.
• Dimensional model allows boost query performance – the dimensional model is more denormalized therefore it is optimized for querying.  In addition, the predictable framework of a dimensional model allows database engine to make strong assumption about the data that will help to boost query performance.
• And last but not least dimensional model is extensible.