Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue Logging System - OLAP Model Expand / Collapse
Author
Message
Posted Saturday, October 5, 2013 7:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:09 AM
Points: 11, Visits: 97
HI All,

We do have a reqirement to design a OLAP model for consolidating the issues identified on ASSETS from various sources\system.

Let us take one sample record;

Issue | Asset | Status |Priority |St_DT |End_Dt
ISS1 | ASS1,ASS2 |Open |High |1-Jan-13 |NULL

We have designed the below model for this scenario:

Dimension_Asset: Conformed dimesion for assets.
Dimension_Asset_Group: Since each issue can have multiple assets we have designed a group table.
Dimension_Date: Conformed and role playing dimension for start and end date
Fact_Issue: Issue_ID,Asset_Group_ID,Status,Priority,St_DT,End_DT

Please clarify the below doubts ( if at all the above model is good to go)

1. As of now client is not intersted in the history of the issue, if at all in future it requires then is it good to go with this model, by have multiple entries in the fact_issue table for same issue. Also on top of it can we build a accumulating snapshot fact table.

2. Actually issues will be associated with many deatiled text and lattributes. Do we need to capture this information in the FACT_ISSUE table as per this model. As of now we planned to move those information to another table called Dimension_Issue (though we cannot call it as dimension since its not repitative data in the fact table).
Post #1501835
Posted Thursday, October 10, 2013 9:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:34 PM
Points: 1,238, Visits: 1,276
manickam (10/5/2013)
2. Actually issues will be associated with many deatiled text and lattributes. Do we need to capture this information in the FACT_ISSUE table as per this model. As of now we planned to move those information to another table called Dimension_Issue (though we cannot call it as dimension since its not repitative data in the fact table).

For point #2, it sounds like you're on the right track; you could create a degenerate dimension that has the issue text in it. You could put it right in the fact table, but since it's probably large I'd consider moving it to a separate dimension table.

http://www.kimballgroup.com/2003/06/03/design-tip-46-another-look-at-degenerate-dimensions/

HTH,
Rob
Post #1503672
Posted Tuesday, October 15, 2013 6:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:09 AM
Points: 11, Visits: 97
Many thanks for the reply.

Need one more suggestion.


As per current requirment Issue can be logged at

1. asset level
2. applicaiton level
3. process level
4. line of business level
5. combination of each of the above entities.

Can i have dimension table for each entity (asset, application, process and line of business)
and a fact table which would have keys of all these dimensions and measures as ratings, score and status..
Post #1504699
Posted Thursday, October 17, 2013 8:25 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:34 PM
Points: 1,238, Visits: 1,276
manickam (10/15/2013)
Many thanks for the reply.

Need one more suggestion.


As per current requirment Issue can be logged at

1. asset level
2. applicaiton level
3. process level
4. line of business level
5. combination of each of the above entities.

Can i have dimension table for each entity (asset, application, process and line of business)
and a fact table which would have keys of all these dimensions and measures as ratings, score and status..

If the combination of all of these values is a relatively low number, you could create a junk dimension that has all of these values.

http://www.kimballgroup.com/2009/06/03/design-tip-113-creating-using-and-maintaining-junk-dimensions/

Or it's perfectly valid to create separate dimensions for these if the junk dimension would be too large.

HTH,
Rob
Post #1505962
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse