Dimensional Modeling
Dimensional Modeling is a data structure approach specifically designed for data warehouse storage. The goal of dimensional modeling is to speed up the retrieval of data in the database. Ralph Kimball came up with the idea of “fact” and “dimension” tables when he established Dimensional Modelling.
To read, summarize, and analyze numerical data like values, balances, counts, weights, and so on that is stored in a data warehouse, you need to utilize a dimensional model. When it comes to real-time online transaction systems, relational models are better suited to handle the adding, updating, and deletion of data.
It is advantageous to use these dimensional and relational models since they have a unique approach to data storage.
Data redundancy is reduced in relational mode via normalization and ER models. A data warehouse’s dimensions are used to make it easier for users to find information and generate reports.
As a result, dimensional models are more commonly found in data warehouses than in relational databases.
Dimensional Modeling’s Purpose
Dimensional modeling serves the following purposes:
To provide a database design that is simple to comprehend and use for both developers and end-users.
In order to increase the efficiency of inquiries. By reducing the number of tables and interactions between them, it is able to accomplish these aims.
Various types of Dimensions in a Data Warehouse
Data Warehouse Dimensions fall into the following categories:
- Constructed Shape
- The Outrigger Dimensions
- Step Dimension
- Dimensions Diminished
- Swappable Dimension
- Tables of Dimensions
- Degenerate Dimension
- Junk Dimension
Features of a Dimensional Data Model
Fact
Measurements/Metrics or facts from your business process are known as facts. Using quarterly sales numbers as a metric in a sales process would be appropriate.
Dimension
A business process event’s context is provided by Dimension. To put it another way, they tell us who, what, and where something is true. Dimensions would be used in the sales process because of the quarterly sales number.
To put it another way, a dimension is a lens through which we can peer at data.
Attributes
In dimensional data modeling, attributes are the distinct features of a dimension.
Characteristics can be mapped to the location dimension
- A ZIP code
- City
- State, and more.
Facts can be searched, filtered, or classified using attributes. Attributes are included in Dimension Tables.
Fact Table
Dimensional modeling relies heavily on the fact table.
A Fact Table is a collection of information.
- Measurements/facts
- Key to dimension table’s foreign key
The Fact table’s characteristics
- There are numerical values in the fact table for each measurement. If a fact value of 20 indicates that 20 widgets have been sold, then that would be the case.
- Each fact table contains the corresponding dimension table’s key. In the fact table, these are referred to as foreign keys.
- The number of columns in a fact table is usually limited.
- Fact tables feature a huge amount of rows as compared to dimension tables.
Measure
Dimensions are used to depict the company’s performance or conduct in relation to its dimensions.
Dimensional modeling relies on two basic models, both of which take the relational context into account:
- Snowflake and Star Models
A dimensional model’s foundation is the star model. The principal table (fact table) is surrounded by several smaller tables (dimensions) in a radial arrangement. To arrive at the snowflake model, one or more dimensions must be decomposed.
Dimension Table
Tables of dimensions provide context for the information presented. Details are stored in dimensional tables.
The Dimension table’s properties
The specifics of the data are laid out in dimension tables. Using this as an example, data and reports can be better understood by business analysts.
Descriptive information is included in dimension tables, which are separate from fact tables. In other words, the facts themselves are contained in these statements As an example, the dimensions tables for a marketing analysis function may comprise attributes such as time, marketing region, and product type.
A dimension table record typically contains a significant number of columns due to the fact that it is DE normalized. In comparison to the fact table, the dimension tables contain a much smaller number of rows of data.
An attribute used as a row or column headings in a document or query results presentation can be found in a dimension table
Dimensional Modeling’s Pros and Cons
Dimensional modeling has the following advantages:
Dimensional modeling is straightforward
Using dimensional modeling techniques, warehouse designers can create database schemas that are easily understandable to business clients and so increase their productivity. Visualizing data is not difficult, and there is no need to undergo rigorous training in order to comprehend diagrams and data visualizations.
Data quality is improved by using Dimensional Modeling
Data warehouse managers can use the star schema to enforce data integrity checks. In order for a factual record to be loaded, it must be accompanied by the matching dimensions records that have been properly described and are likewise present in the database.
By implementing foreign key constraints, data warehouse DBAs provide an additional line of defense against corrupted warehouse data.
Aggregates can be used to improve performance
The optimization of data warehouse performance becomes increasingly crucial as the warehouse grows in size. Customers who are forced to wait for hours for a solution to a question will eventually lose faith in the company in the long run. Using aggregates to enhance query performance is one of the most straightforward methods of doing so.
Dimensional modeling has the following disadvantages:
- Data warehouses must be loaded with records from a variety of operating systems in order to maintain the integrity of the facts and figures contained inside them.
- It is extremely difficult to adapt the data warehouse operation if the firm that has adopted the dimensional technique changes the manner in which it conducts its operations.
