Business Intelligence: Dimensional Modeling

Dimensional modeling is a database design technique that supports business users to query data in data warehouse system. The dimensional modeling is developed to be oriented to improve the query performance and ease of use.

It is important to note that the dimensional modeling does not necessarily depend on relational databases. The dimensional modeling approach, at the logical level, can be applied to any physical forms such as relational and multidimensional databases.

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

• Facts are business measurements. Facts are normally but not always numeric values that could be aggregated. e.g,  a number of products sold per quarter.

• Dimensions are called contexts. Dimensions are business descriptors that specify the facts, for example, product name, brand, quarter, etc.

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 ordered four steps are commonly used in dimensional modeling design:

• Select the business process:

business process is daily activities performed in companies supported by an OnLine Transactional system (OLTP) or source system. In this step, we 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, etc.

• Declare the grain

after having a business process to model, we 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.

• Identify the dimensions

in this step, we add a number of dimensions that represent all possible descriptions that take on single values in the context of each fact in the fact table. Date, time, product, customer, store, etc., are examples of common dimensions.

• Identify the Fact

in the last step, we select the numeric facts that will be loaded into the fact table. To identify the facts, we need to find the KPIs of the business process or find out what we are trying to measure.

Benefits of dimensional modeling

• Dimensional model has proved to be more understandable – in the dimensional model, data is grouped into coherent dimensions that help business users analyze the data easier.

• The dimensional model boosts query performance – the dimensional model is more denormalized therefore it is optimized for querying.

• Dimensional model is extensible, it adapts to changes.