Overview & Purpose
- A Dimensional Model is a database structure mechanism that is optimized for online queries (OLAP) and Data Warehousing tools.
- Dimensional Models are designed for reading, summarized and analyzing numeric/measures information.
- The central attraction of the dimensional model of a business is its simplicity that simplicity is the fundamental key that allows users to understand databases, and allows software to navigate databases efficiently.
- Advantage of storing data in such a fashion that it is easier to retrieve the information from the data once the data is stored in database.
- This is the reason why dimensional modeling is used mostly in data warehouses built for reporting.
- Relational Models are optimized for adding and maintaining data using real-time operational systems.
- It is comprises of Fact and Dimension tables.
- In Dimensional Data Modeling hierarchies for dimensions are stored in the dimensions itself.
- Dimensional Data Modeling is used for calculating summarized data.
- Performance of Dimensional Data Modeling can be increased when materialized view are used.
- Materialized view contains pre calculated aggregated values from combination of dimension and fact tables.
Dimensional Model Benefits
- Understand Ability – Compared to the normalized model, the dimensional model is easier to understand and more intuitive. In this model data/information is grouped into coherent business categories or dimensions which makes it easier to read and interpret. Simplicity also allows software to navigate databases efficiently. In normalized models, data is divided into many discrete entities and even a simple business process might result in dozens of tables joined together in a complex way.
- Faster Data Retrieval: Data stored in Multidimensional model produces very fast result as data is already stored in optimized way for retrieval in terms of OLAP Cubes.
- Query performance – Dimensional models are more DE normalized and optimized for data querying, while normalized models seek to eliminate data redundancies and are optimized for transaction loading and updating. The predictable framework of a dimensional model allows the database to make strong assumptions about the data which may have a positive impact on performance. Each dimension is an equivalent entry point into the fact table, and this symmetrical structure allows effective handling of complex queries. Query optimization for star joined databases is simple, predictable, and controllable.
- Extensibility – Dimensional models are scalable and easily accommodate unexpected new data. Existing tables can be changed in place either by simply adding new data rows into the table or executing SQL alter table commands. No queries or applications that sit on top of the data warehouse need to be reprogrammed to accommodate changes. Old queries and applications continue to run without yielding different results. But in normalized models each modification should be considered carefully, because of the complex dependencies between database tables.
Dimensional Model Approach
- Identify the dimensions
- Identify the measures
- Identify the attributes or properties of dimensions
- Identify the granularity of the measures
- History Preservation (Optional) -Slowly Changing Dimensions (SCD)