| Dimensional Modeling Doubts [message #538620] |
Sat, 07 January 2012 12:03 |
ramesh_samane
Messages: 63
Registered: May 2006
|
Member |
|
|
I have a few basic questions regarding dimensional modeling:
1. While designing dimensional model from existing OLTP system, do we use the same table structures as in OLTP or we modify it to suit reporting requirements? For example, I have a customer table in OLTP and I want to include it in my dimensional model as a dimension table, so can I use the same table structure while designing a customer dimension table or I need to change it?
2. Can dimension tables refer each other? For example, in my OLTP I have EMP and DEPT table, EMP references DEPT, so if I choose these two tables to be part of my dimensional model, is it necessary that I put a FK constraint on EMP dimensional table? I believe as the data coming these tables is already validated in OLTP system it is not again required to be checked. Am I correct?
3. Now, about a bridge table, suppose in my OLTP system I have STORE and DEPT tables and a bridge table STORE_DEPT that joins STORE and DEPT, that means I can have multiple departments within each store which is recorded in this bride table. Now, suppose I want to create dimension tables for STORE and DEPT in my dimensional model, do I need to include this bridge table also in the model or I need to design a single unnormalized table combining these two tables?
4. Regarding TIME (or DATE) dimension, I believe this dimension can be designed irrespective of other dimesions and will always have a fixed number of records. Am I correct?
5. What are the strategies to load dimension and fact tables? Do we design a separate view for each dimension/fact table that extracts appropriate data from the OLTP systems?
6. Where are the materialized views used in DWH scenario? Is it for reporting purposes?
Thanks in advance for your help.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
