Impact of type 2 dimensions on factless tables

View previous topic View next topic Go down

Impact of type 2 dimensions on factless tables

Post  kiran.mv on Wed Jun 22, 2011 2:33 am

Suppose I have a SCD Type2 dimension participating in a relationship with factlessfact table defining relationship of this dimension with some other dimensions.
Any change to this dimension will lead to creation of a new row with new key.
So new rows have to be inserted in the factlessfact table for this new dimension instance. Infact, the same has to be done to every factlessfact table this dimension is part of.
Wouldn't this lead to lot of maintenance?
Is there any better way of handling this?

kiran.mv

Posts : 13
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Impact of type 2 dimensions on factless tables

Post  ngalemmo on Wed Jun 22, 2011 10:17 am

Is there any better way of handling this?

Yeah... think it through.

Why would a dimensional change require the creation of a new fact row? What does the fact row represent? In the case of factless fact tables, it is usually representing a state. Does the dimensional change affect the state? Or, is a change in state represented by some transaction from the source system? In terms of the dimension, what is it you are trying to report? The attributes at the time of the change in state or the current attributes? The former does not dictate adding new rows when attributes change, and if the latter, there are other ways of doing it besides creating new rows.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Impact of type 2 dimensions on factless tables

Post  kiran.mv on Thu Jun 23, 2011 2:30 am

I would like to report the current state of dimension.

Suppose I have a row in customer dimension with cols [Key][cust ID][cust name][cust cit]
values
1001 CHX0021 James Chicago
This customer instance is related to couple of ship-to addresses through factlessfact table 'CustAddress' having cols [cust Key][address key]
values:
......
1001 3003
1001 3005
.....

There was a change in the city of the customer, so new record is inserted in customer dimension:
2004 CHX0021 James Minneapolis

This customer row with key as 2004 has to be associated with the two address rows with keys 3003 and 3005 in 'CustAddress'
If we don't insert two new rows in the factlessfact table how would we show the relationship of the new instance with addresses having keys 3003,3005?

kiran.mv

Posts : 13
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Impact of type 2 dimensions on factless tables

Post  ngalemmo on Thu Jun 23, 2011 9:01 am

There are multiple ways to get the current dimension values from a type 2 that does not involve creating new rows in the fact table.

The most straight-forward is to perform a self join on the dimension using the natural key to locate the most current row. There are other techniques as well, all addressed at length in other threads.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Impact of type 2 dimensions on factless tables

Post  kiran.mv on Mon Jun 27, 2011 3:31 am

Hi Nick,
If the question is, given a type 2 dimension, how do you report the current version of all dimension values for all rows in the fact...your suggestion of self join and other techniques suggested by in other threads will be helpful. (I have gone through most of the techniques you have suggested in other threads for this. They have been very helpful for me).

However, here my question is different. At least as my understanding goes. I am talking of coverage factless fact tables.
Do you suggest adopting the self-join or other techniques even in this case - coverage factless fact tables.

kiran.mv

Posts : 13
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Impact of type 2 dimensions on factless tables

Post  Dave Jermy on Mon Jun 27, 2011 4:48 am

It makes no difference to the solution. In your example below, you would join from your fact table to your customer dimension using the [cust key] and then join again to the Customer dimension using the [cust ID], restricting the results to the current customer record.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Impact of type 2 dimensions on factless tables

Post  ngalemmo on Mon Jun 27, 2011 10:12 am

kiran.mv wrote:However, here my question is different. At least as my understanding goes. I am talking of coverage factless fact tables.
Do you suggest adopting the self-join or other techniques even in this case - coverage factless fact tables.

If the fact table is tracking coverage, not simply dimensional relationships, it should have other dimensions as well... such as effective and expiration dates relating to the coverage in question. A material change in the coverage would require a new fact row. Such a change is not a simple dimensional change, but rather a business transaction.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Impact of type 2 dimensions on factless tables

Post  kiran.mv on Wed Jun 29, 2011 1:35 am

Thanks for the inputs. I guess I have understood.
I am used to thinking that new rows have to be added in the coverage factless fact tables when a new type 2 dimension row is added. So now I need to change the approach.

kiran.mv

Posts : 13
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Impact of type 2 dimensions on factless tables

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