Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.

View previous topic View next topic Go down

Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.

Post  bestin.jacobi@gmail.com on Thu Feb 06, 2014 9:04 pm

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?


  1. 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

View user profile

Back to top Go down

Re: Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.

Post  nick_white on Fri Feb 07, 2014 4:17 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

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