Factless fact tables and SCD2
3 posters
Page 1 of 1
Factless fact tables and SCD2
My understanding is that links between dimensions have to be made through factless fact tables.
So for instance in a financial services model like mine ( microfinance sector) I would need factless fact tables between:
* account and clients (events)
* clients and employee (coverage) with start date and end date
* branch and client (coverage) with start date and end date
* employe and branch (coverage) with start date and end date
Those table will allow me to keep history of the relations between the different dimensions at any point in time.
My question is, what happen then when a SCD2 dimension changes. Will I have to add a new rows in my factless fact table? In this case what about my facts likes counts? They willl be wrong. Or I will have to count distinct combinations of natural keys.
* To count new accounts I will have to count distinct account_NK/client_NK
* To count employees assignations to clients I will have to count distinct employee_NK/client_NK
etc...
This is not very convenient...
What do you think?
I would kindly appreciate your help.
Thanks in advance.
So for instance in a financial services model like mine ( microfinance sector) I would need factless fact tables between:
* account and clients (events)
* clients and employee (coverage) with start date and end date
* branch and client (coverage) with start date and end date
* employe and branch (coverage) with start date and end date
Those table will allow me to keep history of the relations between the different dimensions at any point in time.
My question is, what happen then when a SCD2 dimension changes. Will I have to add a new rows in my factless fact table? In this case what about my facts likes counts? They willl be wrong. Or I will have to count distinct combinations of natural keys.
* To count new accounts I will have to count distinct account_NK/client_NK
* To count employees assignations to clients I will have to count distinct employee_NK/client_NK
etc...
This is not very convenient...
What do you think?
I would kindly appreciate your help.
Thanks in advance.
Re: Factless fact tables and SCD2
Try not to over-complicate it:)
I've never truly delivered a "factless" fact table, in my thinking, even a fact table that maintains relationships (e.g. Branch and Client) has a count (each record of the fact represents a relationship). I typically add a single count measure (value=1) - for most BI tools it works better than needing to count(attribute).
For a relationship fact, any SCD2 change in the dimension should create a new fact and update effective dates on the existing fact.
Your queries shouldn't require the distinct clause - the effective dates should not allow any duplicates at a specific point in time.
LAndrews- Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada
Re: Factless fact tables and SCD2
You do not need to create new rows if a type 2 key changes. You don't do it when there are measures, and there is no reason to do it when there are not.
Type 2 keys refer to versions of a specific entity (customer, department, whatever). Regardless of other versions that may exist for that entity, the entity referred to by the fact does not change.
When you query a type 2 reference, at issue is which version of the entity do you want to see? Do you want to see the version at the time the fact was created (a direct join) or do you want to see the current version of the entity?
For the latter case, standard practice is to do a self-join on the dimension table using the natural key and filter for the current version. There are other techniques as well mentioned in other postings.
Type 2 keys refer to versions of a specific entity (customer, department, whatever). Regardless of other versions that may exist for that entity, the entity referred to by the fact does not change.
When you query a type 2 reference, at issue is which version of the entity do you want to see? Do you want to see the version at the time the fact was created (a direct join) or do you want to see the current version of the entity?
For the latter case, standard practice is to do a self-join on the dimension table using the natural key and filter for the current version. There are other techniques as well mentioned in other postings.

» factless fact and scd2 dimensions
» Regarding Factless Fact Tables
» Joining Factless Fact tables
» Fact Tables Joining to Factless Facts
» Analyzing measures from two fact tables where records do not necessarily match due to SCD2 changes.
» Regarding Factless Fact Tables
» Joining Factless Fact tables
» Fact Tables Joining to Factless Facts
» Analyzing measures from two fact tables where records do not necessarily match due to SCD2 changes.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum