Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
2 posters
Page 1 of 1
Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
Hi,
Below is the Sample Data. Idea is to create a Monthly Snapshot table (Start of Month) for the below dimensions and Measures.
I do not think using Dim_Surrogate_foreign keys in Snapshot will be right since the Dimensional attributes are changing. Any ideas?
Note:
1) All facts and dimensions are SCD Type 1, I will not be able to change any of it to SCD-2 due to time constraints.
2) All the Dimensional attributes are frequently changed, it is just that changes are not captured.
Desired Metrics Required: Start_of_Month_Date, Account_type, Account_status, Asset_status, Product_Code, Product_Release, SUM_SEATS
Sample:
Start_of_Month_Date_Key | Account_type | Account_status | Asset_status | Product_Code | Product_Release | SUM_SEATS |
Jan-2014 | Enterprise | Active | Registered | XYZ | 2014 | 100 |
Jan-2014 | Small Business | Inactive | upgraded | ABC | 2013 | 100 |
Jan-2014 | Internal | Dormant | UnRegistered | LMN | 2012 | 100 |
Feb-2014 | Enterprise | Active | Registered | XYZ | 2014 | 200 |
Feb-2014 | Small Business | Inactive | upgraded | ABC | 2013 | 200 |
Feb-2014 | Internal | Dormant | UnRegistered | LMN | 2012 | 200 |
Mar-2014 | Enterprise | Active | Registered | XYZ | 2014 | 300 |
Mar-2014 | Small Business | Inactive | upgraded | ABC | 2013 | 300 |
Mar-2014 | Internal | Dormant | UnRegistered | LMN | 2012 | 300 |
Below is the Sample Data. Idea is to create a Monthly Snapshot table (Start of Month) for the below dimensions and Measures.
I do not think using Dim_Surrogate_foreign keys in Snapshot will be right since the Dimensional attributes are changing. Any ideas?
- Dimension 1: Account (Account_key, Account_Type, Account_Status)
Dimension 2: Asset (Asset_Key,Asset_Status)
Dimension 3: Product (Product_key, Product_Code, Product_Release)
Fact 1: (Asset_Key, Account_Key, Product_Key, No_of_Seats)
Note:
1) All facts and dimensions are SCD Type 1, I will not be able to change any of it to SCD-2 due to time constraints.
2) All the Dimensional attributes are frequently changed, it is just that changes are not captured.
Desired Metrics Required: Start_of_Month_Date, Account_type, Account_status, Asset_status, Product_Code, Product_Release, SUM_SEATS
Sample:
Start_of_Month_Date_Key | Account_type | Account_status | Asset_status | Product_Code | Product_Release | SUM_SEATS |
Jan-2014 | Enterprise | Active | Registered | XYZ | 2014 | 100 |
Jan-2014 | Small Business | Inactive | upgraded | ABC | 2013 | 100 |
Jan-2014 | Internal | Dormant | UnRegistered | LMN | 2012 | 100 |
Feb-2014 | Enterprise | Active | Registered | XYZ | 2014 | 200 |
Feb-2014 | Small Business | Inactive | upgraded | ABC | 2013 | 200 |
Feb-2014 | Internal | Dormant | UnRegistered | LMN | 2012 | 200 |
Mar-2014 | Enterprise | Active | Registered | XYZ | 2014 | 300 |
Mar-2014 | Small Business | Inactive | upgraded | ABC | 2013 | 300 |
Mar-2014 | Internal | Dormant | UnRegistered | LMN | 2012 | 300 |
Last edited by bestin.jacobi@gmail.com on Thu Feb 06, 2014 9:15 pm; edited 1 time in total (Reason for editing : added sample)
bestin.jacobi@gmail.com- Posts : 1
Join date : 2014-02-06
Re: Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
Hi,
looking at your sample data it appears that you are not creating a record in your snapshot per account but are instead summarising data. If this is the case then I think the problem goes away:
1. Create an Account Profile Dim containing every combination of Account_Type and Account_Status
2. Create an Asset status Dim
3. I assume Product release year doesn't change over time so you can use you existing Product Dim (possibly put a view on top of it to hide any attributes that do change or you could create a subtype Product Dim just holding the non-changing attributes). Alternatively, using a Product Release version of your Date Dim may be appropriate
Join these three Dims to your snapshot fact via SKs in the normal way and it should all work
looking at your sample data it appears that you are not creating a record in your snapshot per account but are instead summarising data. If this is the case then I think the problem goes away:
1. Create an Account Profile Dim containing every combination of Account_Type and Account_Status
2. Create an Asset status Dim
3. I assume Product release year doesn't change over time so you can use you existing Product Dim (possibly put a view on top of it to hide any attributes that do change or you could create a subtype Product Dim just holding the non-changing attributes). Alternatively, using a Product Release version of your Date Dim may be appropriate
Join these three Dims to your snapshot fact via SKs in the normal way and it should all work
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
![-](https://2img.net/i/empty.gif)
» Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion
» Accumulating Snapshot and Transaction Fact tables : question to design and use them together
» What is the proper way to connect a periodic snapshot table to a date dimension?
» Dimension design question
» Can you combine an accumulating and periodic snapshot into a single accumulating periodic snapshot table?!
» Accumulating Snapshot and Transaction Fact tables : question to design and use them together
» What is the proper way to connect a periodic snapshot table to a date dimension?
» Dimension design question
» Can you combine an accumulating and periodic snapshot into a single accumulating periodic snapshot table?!
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum