Factless fact tables and SCD2

View previous topic View next topic Go down

Factless fact tables and SCD2

Post  remiby on Tue Jul 23, 2013 4:24 pm

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.

remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: Factless fact tables and SCD2

Post  LAndrews on Tue Jul 23, 2013 6:40 pm


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

View user profile

Back to top Go down

Re: Factless fact tables and SCD2

Post  ngalemmo on Tue Jul 23, 2013 8:13 pm

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.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Factless fact tables and SCD2

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