Dimensional modeling is a database design technique that supports business users to query data in a 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 dimensional modeling does not necessarily depend on relational databases. The dimensional modeling approach, at the logical level, can be applied to any physical form 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., the 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 four-step process is commonly used in dimensional modeling design:
- Select the business process
- Declare the grain
- Identify the dimensions
- Identify the Fact
Let’s examine each step in the modeling process in greater detail.
- Select the business process to a model – the business process is daily activities performed in your company 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.
- 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 associated with facts in the fact table.
- Identify the dimensions – in this step, we add some 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 several good examples of common dimensions.
- Identify the facts – 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
- The 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 more easily.
- The dimensional model allows for boosted query performance – the dimensional model is more denormalized therefore it is optimized for querying. In addition, the predictable framework of a dimensional model allows the database engine to make a strong assumption about the data; this helps the database engine boost query performance.
- The Dimensional model is extensible.
In this article, we’ve discussed dimensional modeling and its important concepts such as facts and measures. We’ve also examined the four steps of the dimensional modeling process.
