Dimensional Model Validation

View previous topic View next topic Go down

Dimensional Model Validation

Post  manickam on Thu Jan 16, 2014 7:37 am

Based on the below sample data i have to prepare a dimensional model.


ISSUE_ID--|||--REGION--|||--  ASSET--|||--  START_DATE --|||-- END_DATE--|||--  PRIORITY--|||-- SEVERITY--|||--    COST
---------------------------------------------------------------------------------------------------------------------------------------------
   iss123--|||--Chennai;Mumbai--|||--     asset101--|||--      1-Jan-14--|||--  4-Jan-14--|||--        low--|||--   medium--|||--    100
   iss456--|||--Chennai;Pondy;Delhi--|||-- asset102--|||--      2-Jan-14--|||--  5-Jan-14 --|||--       high--|||-- low--|||--    200



I have planned to have dimension table for REGION, ASSET, DATE.  

FACT table will be having the issue id with all the keys of the above dimension table and textual measures as PRIORITY, SEVERITY.


Ideally the FACT table should have entry for each region, in the sample data sheet region is given as multivalued attribute.


So for this scenario should we consider the issue itself a seperate dimension table or not?

If the FACT table is going to have multiple entry for each region than where can we store the COST data?

manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Dimensional Model Validation

Post  ngalemmo on Thu Jan 16, 2014 12:15 pm

Issues is a degenerate dimension. Priority and severity should be put in a mini/junk dimension. Cost is your measure. For region you need to construct a bridge with an allocation factor to distribute cost, alternately you have multiple rows in the fact (one region per) and an allocated cost.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimensional Model Validation

Post  manickam on Fri Jan 17, 2014 1:45 am

Please let me know if my understanding is correct.

Having issues as a degenerate dimension is nothing but the fact table will contain all the issue information? Again can we have seperate dimension for issue table since it will have other attributes like description, comment , etc..

As per our requirement users are more interested in severity and priority, so still can we have this in junk dimension?

Constructing bridge table means we have to have bridge table that connects FACT table and DIMENSION table. This bridge table would be like a assocation table, is it correct.


manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Dimensional Model Validation

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