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

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

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..

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 :-)

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.

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?

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