SCD 2 Surrogate key on Fact Table

View previous topic View next topic Go down

SCD 2 Surrogate key on Fact Table

Post  MSBIDeveloper1981 on Wed Oct 14, 2015 11:49 pm

I am currently working on a data warehouse initiative for Claims, where we have a bunch of scd 2 dimensions defined. For the following example, provider dimension captures the changes of county , so for every change in county we capture a new record for the provider and terminate the previous record. We also have fact claims where we take snapshot of every day ( just the business requirement) so we do have multiple rows for the same claim in our fact if anything has changed at the end of the day.
Since we do not have a data mart, we decided to expose the current state of the record ( active record) from dimension and Fact through views for operational reporting and another set of views to expose everything for analytics folks.
So here is my question( i am sure i am missing some fundamental of modeling). Lets say we have provider John in 2 different counties X and Y. He is active in county Y. We have received claims from that provider when he was in county X but don't have anything from County Y. So through the view where we expose current information, Surrogate key from Dimension for the row Y will not have records associated in fact. So if some one wants a report of all claims submitted by Just that provider irrespective of the county, they will not see any data since no association is there between active dimension record and fact.
I need some help to understand how these scenario is handled, i am very sure this is common design in lot of data marts\ ware houses.

Thanks

MSBIDeveloper1981

Posts : 2
Join date : 2015-10-14

View user profile

Back to top Go down

Re: SCD 2 Surrogate key on Fact Table

Post  ngalemmo on Thu Oct 15, 2015 12:06 am

The current information is accessed via a self-join on the dimension using the natural key (or a stable surrogate), not by joins from the fact.

In other words, join from the facts to the x row, then join the natural key in the dimension to itself and filter for the current row.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD 2 Surrogate key on Fact Table

Post  MSBIDeveloper1981 on Thu Oct 15, 2015 1:00 am

So are you saying the current view for that dimension will actually follow that path from fact-dimension ( that is what we have decided ), but is not that going to impact performance ?

MSBIDeveloper1981

Posts : 2
Join date : 2015-10-14

View user profile

Back to top Go down

Re: SCD 2 Surrogate key on Fact Table

Post  ngalemmo on Thu Oct 15, 2015 8:24 am

There are alternate methods to handling this. The simplest is to maintain both a type 1 and type 2 version of the dimension and store both keys on the fact. But usually the performance hit is not that much that it matters.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD 2 Surrogate key on Fact Table

Post  Sponsored content


Sponsored content


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