SCD Type for organisational structure

View previous topic View next topic Go down

SCD Type for organisational structure

Post  cidr on 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

View user profile

Back to top Go down

Re: SCD Type for organisational structure

Post  ngalemmo on 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

View user profile

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