Monday, December 18, 2017

Data Modelling vs Dimensional Modelling

Data have become a real resource of interest across most industries in every field which is exactly considered the gateway to competitive advantage and disruptive strategy. Now a day, most of the enterprises are focusing on methods enabling simpler use of data to drive their business and advancement in data related technologies because data is high-volume and high-velocity and/or high-variety information assets that demand cost-effective, innovative forms of information processing that enable enhanced insight, decision-making, and process automation. 
If we are talking about the data models then it has been treated in two different terminologies such as data modeling and dimension modeling which are differ from each other because Steve Hoberman is stated in his book called Data Modeling Made Simple the distinction between the two types of models is this: 
  • Relational Data Models captures the business solution for how part of the business works, a.k.a business process
  • Dimensional Data Models capture the details the business needs to answer questions about how well it is doing

It can be claimed that a relational model can also be used as a foundation upon which to answer business questions, but at a strategic level. "How many orders are in an unfulfilled status for the customer XXX due to credit hold?" But the distinction is that of where the reporting question needs the 'native grain' of the table and when the reporting question can be answered with summarized data.
To better understanding, we can take an example of Historical Sale Data having product, state and sales cash value as a relational point-of-view-
Product
State
Sales Cash
Beer
Delhi
5000
Beer
Punjab
4500
Beer
Haryana
6000
Wine
Delhi
6500
Wine
Punjab
7500
Wine
Haryana
5000
Vodka
Delhi
4500
Vodka
Punjab
2500
Vodka
Haryana
2000
If we modified the above data as dimensional point-of-view:
Product
Delhi
Punjab
Haryana
Beer
5000
4500
6000
Wine
6500
7500
5000
Vodka
4500
2500
2000
But it seems like both points of view would nonetheless be implemented in an identical star schema and a star schema really lies at the intersection of the relational model of data and the dimensional model of data. It's really a way of starting with a dimensional model, and mapping it into SQL tables that somewhat resembles the SQL tables you get if you start from a relational model.
Product Dim



State Dim
Product Id

Sales Fact

State Id
Product Name

Product Id

State Name


State Id




Sales Cash








The standard approach to relational data modelling is not fit for purpose for Business Intelligence workloads. This is because a relational data modelling is a combination of multiple tables and multiple joins but in dimension modelling, we force to use de-normalize multiple related tables into one table to analyze the data in a very sophisticated manner.

1 comment:

  1. The 80/20 rule in business states that 80% of your results should come from 20% of your efforts. You should spend more time in the 20% category if you want to see the most effective results for your business. sales process

    ReplyDelete