Dimensional Modeling Doubts

1
votes

I have a few questions regarding dimensional modeling:

  1. While designing dimensional model from existing OLTP system, do we use the same table structures in OLTP in dimension model? For example I have a customer table in OLTP and I want to include it in my dimensional model, so can I use the same table structure while designing a customer dimension table or I can change it?

  2. Can dimension tables refer each other? For example, in my OLTP I have EMP and DEPT table, EMP references DEPT, so I choose these two tables to be part of dimensional model, is it necessary that I put a FK constraint on EMP dimensional table?

  3. Now, about bridge table, suppose in my OLTP I have STORE and DEPT tables and a bridge table STORE_DEPT that joins STORE with 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?

Thanks in advance for your help.

1
dimensional-modeling
as a rule of thumb, dimensions should be fully de-normalised - so you shouldn't be doing any bridge tables or joins between them. - rejj
Thanks rejj. I understand now. - Ramesh Samane

1 Answers

3
votes
  1. No, the dimensional model is usually very different from the OLTP schema. You'll want to read about star schemas.
  2. I would say that dimensions are usually independent of each other. I wouldn't start by having them refer to each other. If you think they need to, then redesign.
  3. STORE and DEPT sound like they should be part of a LOCATION dimension. I still see no need for a bridge or JOIN.

You sound like you're trying to design your first star schema. It might be a good idea to seek out some training or guidance.