Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SCD Type for organisational structure

2 posters

Go down

SCD Type for organisational structure Empty SCD Type for organisational structure

Post  cidr Mon Feb 04, 2013 6:57 pm


I'd like to ask a question on what SCD I should use in the following situation.

I am developing a DW based on a fairly complex SharePoint list. The list is essentially a folder list; meaning that you can click one Item which is a certain Content Type (e.g. Project) and under that item there will be child Items (e.g. Risks). The list can be quite deep with many child items.

Existing Database structure:
This list is extracted to a database table (with many columns) and there will be one column with the name of each item, one column with the ID of each Item and one column with the ParentID. For example

ID          Name              ParentID          ContentType
1          ProjectA                                  Project
2          ProjectB                                  Project
3          RiskA                1                    Risk
4          RiskB                1                    Risk
5          RiskC                2                    Risk

Therefore to find the parent and child of each item a SELF JOIN is used to reference the ParentID. (RiskA and RiskB are child records of ProjectA

There will be a requirement to allow the organisation to look at the structure from a previous date. If ProjectA only had 2 risks (RiskA and RiskB) on December 2012 and currently (Feb 2013) ProjectA has 4 more risks, they would want to see the structure as it was on December 2012.

I'm unsure how this would be implemented. However, I thought that either SCD type 2 or 3 would allow me to resolve this problem.

If anyone has any advice or tips they could give me I'd be very grateful.

Thank you


Posts : 5
Join date : 2013-01-23

Back to top Go down

SCD Type for organisational structure Empty Re: SCD Type for organisational structure

Post  ngalemmo Mon Feb 04, 2013 7:42 pm

A Type 2 dimension will give you a history of attributes, not relationships. Relationships would be maintained in a separate bridge table. You can maintain such a bridge with effective time periods to allow reporting the structure at a particular point in time.

If the dimensions are Type 1, maintaining the bridge is fairly simple. A bridge based on Type 2 dimensions gets messy.

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

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum