Combination of SCD2, partial snowflake, Effective and end dates in fact table
Page 1 of 1
Combination of SCD2, partial snowflake, Effective and end dates in fact table
We are creating dataware housing for multitanant based systems for HR module.
Requirement ?Able to report on historical data for any given data time.
Active employees headcount till given date. (as of that day)
Onhire and termination based on perticular point in datetime.
In order to do that, I am planning to maintain scd2 in dimension tables and also effective and end dates in fact tables to track all the employee changes and when that perticular change is effective or ended.
Many of my dimensional values client,company(branch) based.
Dimensional relationship
1.Client -< company -< cost center
A.client dimension
cl_id cl_oid cl_nm eff-date end-date
1 10 Cl1 1/1/2010 1/30/2010
cl_Id+cl_OID =PK
B.company dimension
cl_Id+cl_OID (FK from client)
cMP_id+Comp_oid (PK)
Cl_id Cl_oid cMP_id Comp_oid comp_cd eff-date end-date
1 10 100 1000 C1 1/1/2010 1/30/2010
1 10 100 1000 C2 1/1/2010 1/30/2010
With the above design, if client cl1 having 100 companies and later changed cl2- I need not to insert the records company dimension table .
Similarway desinged Costcenter table linked company dimension table.
Client -< company codes -< business unit
I have 10 dimension linked to company dim as above and another 10 dimension are not linked to company.
I am planning to maintain scd2 for all client company based dimensions.
Step1 - Fact
ID FK of company FK of costcenter FK of businessunit empid SAL hire_dt row_effective Row_end-dt
1 100 10000 100 10K 1/1/2010 1/1/2010 1/1/2099
Later salary got changed to 15k after 6 months
Step 2- Fact
ID FK_company FK of costcenter FK of businessunit empid SAL hire_dt row_effective Row_end-dt
1 100 10000 100 10K 1/1/2010 1/1/2010 6/30/2010
1 100 10000 100 15K 1/1/2010 7/1/2010 1/1/2099
Step-3 Now client name got changed from cl1 to cl2 - Now I need not to insert the record again into fact, scd2 in client and company will take care of it
Similarly, I am capturing all the transactional changes into fact(except dimensional changes)
My questions are
1.Is it good to maintain snowflake structure for client > company with scd2 ?
2. Is it OK to have FK with company also ? or I should to go through cost center or business unit to find our who is client and company ? Similar way can I have FK in fact from client dim table also, I thought of having client key also fact might helpful for security purpose.
3.Is it ok to maintain effective and end dates in fact ? One disadvantage is to consider effective and end dates of fact and dimension all the time to report any analysis query ?
4.Will there be any diffculties in reporting with the above.
5.Is it good go with scd2 to track the dimensional changes or better to insert record into fact for each and every dimensional change also ?
Overll above approach is good or not ? any advise.
Your thoughts are much appriciated.
Requirement ?Able to report on historical data for any given data time.
Active employees headcount till given date. (as of that day)
Onhire and termination based on perticular point in datetime.
In order to do that, I am planning to maintain scd2 in dimension tables and also effective and end dates in fact tables to track all the employee changes and when that perticular change is effective or ended.
Many of my dimensional values client,company(branch) based.
Dimensional relationship
1.Client -< company -< cost center
A.client dimension
cl_id cl_oid cl_nm eff-date end-date
1 10 Cl1 1/1/2010 1/30/2010
cl_Id+cl_OID =PK
B.company dimension
cl_Id+cl_OID (FK from client)
cMP_id+Comp_oid (PK)
Cl_id Cl_oid cMP_id Comp_oid comp_cd eff-date end-date
1 10 100 1000 C1 1/1/2010 1/30/2010
1 10 100 1000 C2 1/1/2010 1/30/2010
With the above design, if client cl1 having 100 companies and later changed cl2- I need not to insert the records company dimension table .
Similarway desinged Costcenter table linked company dimension table.
Client -< company codes -< business unit
I have 10 dimension linked to company dim as above and another 10 dimension are not linked to company.
I am planning to maintain scd2 for all client company based dimensions.
Step1 - Fact
ID FK of company FK of costcenter FK of businessunit empid SAL hire_dt row_effective Row_end-dt
1 100 10000 100 10K 1/1/2010 1/1/2010 1/1/2099
Later salary got changed to 15k after 6 months
Step 2- Fact
ID FK_company FK of costcenter FK of businessunit empid SAL hire_dt row_effective Row_end-dt
1 100 10000 100 10K 1/1/2010 1/1/2010 6/30/2010
1 100 10000 100 15K 1/1/2010 7/1/2010 1/1/2099
Step-3 Now client name got changed from cl1 to cl2 - Now I need not to insert the record again into fact, scd2 in client and company will take care of it
Similarly, I am capturing all the transactional changes into fact(except dimensional changes)
My questions are
1.Is it good to maintain snowflake structure for client > company with scd2 ?
2. Is it OK to have FK with company also ? or I should to go through cost center or business unit to find our who is client and company ? Similar way can I have FK in fact from client dim table also, I thought of having client key also fact might helpful for security purpose.
3.Is it ok to maintain effective and end dates in fact ? One disadvantage is to consider effective and end dates of fact and dimension all the time to report any analysis query ?
4.Will there be any diffculties in reporting with the above.
5.Is it good go with scd2 to track the dimensional changes or better to insert record into fact for each and every dimensional change also ?
Overll above approach is good or not ? any advise.
Your thoughts are much appriciated.
kasi- Posts : 2
Join date : 2010-03-03
Similar topics
» can i use other two effective dates column in SCD2
» Dimensions with effective and end dates - best design and best way to join to fact tables?
» [SOLVED] SCD2 Dimension and Fact Table
» SCD2 and partial information
» more than fact table and Hierarchy snowflake
» Dimensions with effective and end dates - best design and best way to join to fact tables?
» [SOLVED] SCD2 Dimension and Fact Table
» SCD2 and partial information
» more than fact table and Hierarchy snowflake
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum