SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issue Logging System - OLAP Model


Issue Logging System - OLAP Model

Author
Message
manickam
manickam
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 102
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).
Rob Taylor
Rob Taylor
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3872 Visits: 1616
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
manickam
manickam
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 102
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..
Rob Taylor
Rob Taylor
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3872 Visits: 1616
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search