Why we use Dimensional Model over De-normalized relational Model ?

View previous topic View next topic Go down

Why we use Dimensional Model over De-normalized relational Model ?

Post  mrAladin on Sat Jul 13, 2013 3:49 am

Sir,

After posting these questions on data warehousing forums and getting no satisfactory replies, finally I am consulting here.

1) If your relational model is also De-normalize then why you prefer dimensional model ?
2) What is the reason you prefer dimensional model over relational model ?
3) Your historical data is also stored in OLTP and you can perform reporting easily on any OLTP then why why you use dimensional model and data warehouse ?
4) What is the difference between a dimension and a de-normalized table ?
5) Why you don't prefer OLTP for the purpose of analysis ?

Hope I will get answers of all of my questions.

Thanks in Advance

mrAladin

Posts : 3
Join date : 2013-07-13

View user profile

Back to top Go down

Re: Why we use Dimensional Model over De-normalized relational Model ?

Post  umutiscan on Sat Jul 13, 2013 5:53 am

Hi,

1) If your relational model is also De-normalize then why you prefer dimensional model ?
Dimensional model is not just about denormalization. We cannot say dimensional model is normalized or denormalized. it may have some normalized and denormalized parts. It is not a relational model, it is what it is.
 
2) What is the reason you prefer dimensional model over relational model ?
Dimensional model is design for reporting purposes, it is easier to understand, to query and to adapt to reporting tools.
 
3) Your historical data is also stored in OLTP and you can perform reporting easily on any OLTP then why why you use dimensional model and data warehouse ?
You can not find the history of everything in OLTP systems. You may keep all transactions but you usually see the final status of the transactions, you cannot find intermediate stages. And you cannot find the history of all dimensional values. They are usually updated in OLTP systems, because OLTP systems are designed for decreasing the duration of transactions, not for reporting purposes.
 
Another reason; data warehouses are not fed from one single OLTP system. Reporting systems require more than one source at the same time like CRM, Billing, Invoicing, etc.. You may need to report customer demographics with the customer invoices or credit card transations..
 
And another important reason; you don't busy your OLTP systems with your reporting queries.
 
4) What is the difference between a dimension and a de-normalized table ?
Yes dimensions are generally (but not always) denormalized but a dimension is not just a simple denormalized table.
 
5) Why you don't prefer OLTP for the purpose of analysis ?
Same reasons with 3..

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Why we use Dimensional Model over De-normalized relational Model ?

Post  mrAladin on Sat Jul 13, 2013 8:07 am

Thanks umutiscan

I got your answers well. We know many ERP Systems which have Sales, Purchase, Orders modules and also provide reporting tools in it. If these built in tools help us for reporting then why we build Data Warehouse for reporting and analysis of data ?
On the other hand, for example 3 or 4 OLTP system are integrated like Sales, Purchase, Billing etc into a single OLTP system then by querying on this system we can analyze data like "What is the profit given by the Person P in two years who is buying Product X ". So why not we use our system for reporting and analysis ? Why we should develop a data warehouse ?

Thanks :-)

mrAladin

Posts : 3
Join date : 2013-07-13

View user profile

Back to top Go down

Re: Why we use Dimensional Model over De-normalized relational Model ?

Post  BoxesAndLines on Sun Jul 14, 2013 12:53 pm

If you do not have business drivers to develop a data warehouse, you should not build one. Unlike baseball, if you build it, they will not come.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Why we use Dimensional Model over De-normalized relational Model ?

Post  ravibkulkarni on Wed Aug 07, 2013 4:54 pm

OLTP/ transaction systems are / should be designed to perform well for transaction processing.
Typical example of eCommerce
when user browses web catalog, products are categorized, then there are sales promotions etc. when they add product in shopping cart, when they buy those products, pay for it, get payment confirmation, then POs are issued, then products are shipped, returned etc. etc. All these transactions are typically modeled as 3nf relational tables to get best transactional performance.
On the other hand, in reporting/analytical world, you would hardly ask question which product xyz user bought on 8/7 and using which credit card but questions would typically be
sale
by product
by category, sub category
by geo
by demographics
which payment instrument they used
average time spent on site before transaction
device type they used
campaign effectiveness etc.
All of these questions can be typically answered from OLTP but by someone who has complete understanding of those 100s of tables and relationships. Hard to find business users (consumers of this data) to have that kind of understanding. Dimensional model are defined to capture individual business processes and easy to understand/slice/dice.
There are many other challenges in OLTP systems, which typically doesn't preserve history (if product a was in a category for 3 months and then moved to another category, in OLTP system, this might not be preserved and hence you won't be able to compare performance) so on.
Another point which someone else mentioned is, if you run reporting on top of transactional system, one bad query can adversely impact performance of transactional system resulting in potential revenue loss etc.
Makes sense?

ravibkulkarni

Posts : 2
Join date : 2013-08-07

View user profile

Back to top Go down

Re: Why we use Dimensional Model over De-normalized relational Model ?

Post  mrAladin on Thu Aug 08, 2013 12:51 am

Thank you so much ravibkulkarni :-) I understood the difference.

mrAladin

Posts : 3
Join date : 2013-07-13

View user profile

Back to top Go down

Re: Why we use Dimensional Model over De-normalized relational Model ?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum