Dimensional Data Modeling
Popular Schema – Star Schema, Snow Flake Schema
Dimensional Data Modeling is one of the data modeling techniques used in data warehouse design.
Goal: Improve the data retrieval.
The concept of Dimensional Modeling was developed by Ralph Kimball which is comprised of facts and dimension tables. Since the main goal of this modeling is to improve the data retrieval so it is optimized for SELECT OPERATION. The advantage of using this model is that we can store data in such a way that it is easier to store and retrieve the data once stored in a data warehouse. Dimensional model is the data model used by many OLAP systems.
Figure – Steps for Dimensional Model
Steps to Create Dimensional Data Modeling:
- Step-1: Identifying the business objective –
The first step is to identify the business objective. Sales, HR, Marketing, etc. are some examples as per the need of the organization. Since it is the most important step of Data Modelling the selection of business objective also depends on the quality of data available for that process.
- Step-2: Identifying Granularity –
Granularity is the lowest level of information stored in the table. The level of detail for business problem and its solution is described by Grain.
- Step-3: Identifying Dimensions and its Attributes –
Dimensions are objects or things. Dimensions categorize and describe data warehouse facts and measures in a way that supports meaningful answers to business questions. A data warehouse organizes descriptive attributes as columns in dimension tables. For Example, the data dimension may contain data like a year, month and weekday.
- Step-4: Identifying the Fact –
The measurable data is held by the fact table. Most of the fact table rows are numerical values like price or cost per unit, etc.
- Step-5: Building of Schema –
We implement the Dimension Model in this step. A schema is a database structure. There are two popular schemes: Star Schema and Snowflake Schema.
Dimensional data modeling is a technique used in data warehousing to organize and structure data in a way that makes it easy to analyze and understand. In a dimensional data model, data is organized into dimensions and facts.
Dimensions are the descriptive data elements that are used to categorize or classify the data. For example, in a sales data warehouse, the dimensions might include product, customer, time, and location. Each dimension is made up of a set of attributes that describe the dimension. For example, the product dimension might include attributes such as product name, product category, and product price.
Facts are the measurable data elements that represent the business metrics of interest. For example, in a sales data warehouse, the facts might include sales revenue, units sold, and profit margins. Each fact is associated with one or more dimensions, creating a relationship between the fact and the descriptive data.
In a dimensional data model, the fact table is the central table that contains the measures or metrics of interest, surrounded by the dimension tables that describe the attributes of the measures. The dimension tables are related to the fact table through foreign key relationships.
The advantage of dimensional data modeling is that it provides a simple, intuitive, and flexible way to organize data for analysis. The dimensional model is easy to understand and query, allowing users to quickly access and analyze the data they need. Additionally, the dimensional model can be easily extended to add new dimensions or facts as the business needs change.
Overall, dimensional data modeling is an effective technique for organizing and structuring data in a data warehouse for analysis and reporting. By providing a simple and intuitive structure for the data, the dimensional model makes it easy for users to access and understand the data they need to make informed business decisions
Please Login to comment...