Dimensional Modeling Question

Question

Hello:

I am new to dimensional modeling and trying to design a model based on the existing OLTP system.

Below are the links to ERDs for existing OLTP system and a dimension model that I have developed. Can someone expert in this area take a quick look and offer me suggestions please? It won't take much time as the model is simple. I am especially worried about the inclusion of bridge table STORE_DEPARTMENT in the model and the dimension tables referencing other dimension tables. Is this normal or I am doing it wrong? I am trying to check if the model can answer some of the DSS questions but your suggestions would really help me if I am going in a wrong direction.

Thanks in advance for your help :)

Regards,

Ramesh

http://www.picpanda.com/viewer.php?file=hfjhxg285pgfk5z4jtdj.jpg

http://www.picpanda.com/viewer.php?file=pkq437un72alxm34ur3.jpg

Answer 1

Picture 2 is a better design.

---

Cheers, Raunak Jhawar | t:@raunakjhawar

Answer 2

Ramesh,

  Second one is far better than the 1st. But I have some suggestions on the second 

1.  I don’t think the relation between EMPLOYEE_DIM and STORE_DIM is required. If you are trying to know employees are related to which store, then it can be handled through SALES_FACT.

2. The same is applicable for the relation between EMPLOYEE_DIM and DEPARTMENT_DIM.

3. In my sense the same is applicable for STORE_DEPARTMENT also. You can totally avoid that. DEPT_TEL attribute should be in DEPARTMENT_DIM.

I still believe that you might have some reason to have the bridge .What I suggest is, just change your design like the way I suggested….. Think over again…. All of your analyzing requirements are still not satisfied, please post them.

Best of luck

 Tinto

 

Answer 3

Hi Tinto:

Thank you so much for taking time in looking at this. The idea is to demonstrate basic dimensional modeling concepts through simple example based on the existing OLTP model (Diagram #1).

I have altered the design and here is what I am up to. I have two facts now. One for daily sales and another aggregate fact for monthly sales.

Can you please review quickly and send your feedback?

I have one question also. Do I need to have another DATE dimension for aggregate fact or the same one used for daily sales would work?

OLTP Model
http://imageshack.us/f/851/oltperd.jpg/

DIMENSIONAL Model
http://imageshack.us/photo/my-images/831/dimensionalmodelver1.jpg/

Thank you again,
Ramesh

Answer 4

Hi Ramesh,

 I apologize; I thought you were asking to compare the two designs in your first post.

On your design, you need to have only one fact FACT_SALES. The aggregations will be handled in SSAS and that is the whole purpose of SSAS. Meaning, the sales amount of one month will be calculated from the total sales amount of each day of the month. That means you are doing an aggregation of your Sales amount on Time dimension. Same is applicable for Year.

Your last question will not arise, if you have only one fact. But still I suggest you to check the term “Role playing dimension” in google.

Best of luck

Tinto

Answer 5

Thanks again Tinto.

Regarding using a separate aggregate fact for monthly sales, my idea was to have the calculated values separately in another table instead of having them calculate each time. For example, suppose users want to compare Feb 2011 sales with Feb 2010 sales then with monthly sales fact in existence I already have the calculated values ready for comparison and don't need to calculate them every time users request such query. I am sorry but I am not familiar with SQL server and this question I have asked is in a general sense.

I have searched and read about "Role Playing Dimension" but don't think I have fully understood the concept. However, I feel I can use the same DATE dimension for two facts in question if I go by the route of using two facts.

Appreciate the time you took in replying back. Many thanks :)

Answer 6

Hi Ramesh,

  If you are planning to use SSAS, (that what I assume since you have posted questions here) the aggregations (sum/ count etc.) will be taken care automatically by that tool itself. SSAS Cube will pre-aggregate the expected values (in your case Month wise) and show the aggregated data whenever required.  This pre-aggregation will happen in the processing time so the required aggregated values will be present in no time when you slice and dice the cube.

  And this is one of the main advantages of SSAS.

Tinto

Please mark posts as answers / helpful as it appears.

Answer 7

Thanks a bunch Tinto. I got a fair idea of design concepts now :)