Accumulating Snapshot Fact with Dimension at Same Grain
3 posters
Page 1 of 1
Accumulating Snapshot Fact with Dimension at Same Grain
We have a customer dimension which is used with a variety of transactional and other facts. The customer dimension has a rich set of customer related attributes.
I need a customer accumulating snapshot fact to track the customer journey through various key milestone events and to provide a count of customers irrespective of whether they have records in any of the transactional fact tables. One of Ralph Kimball's recommendations is to avoid having a fact and dimension at the same grain, but I want to use the customer dimension with the customer AS fact. Can anyone see any problems with this? Is there a better approach?
Thanks
I need a customer accumulating snapshot fact to track the customer journey through various key milestone events and to provide a count of customers irrespective of whether they have records in any of the transactional fact tables. One of Ralph Kimball's recommendations is to avoid having a fact and dimension at the same grain, but I want to use the customer dimension with the customer AS fact. Can anyone see any problems with this? Is there a better approach?
Thanks
Andrew W- Posts : 3
Join date : 2015-09-01
Re: Accumulating Snapshot Fact with Dimension at Same Grain
How many customer's do you have? Kimball recommends against doing this for performance reasons. If you can get good performance, I would imagine the Doc would be just fine with it.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Accumulating Snapshot Fact with Dimension at Same Grain
We have around 40,000 customers, so not huge numbers
Andrew W- Posts : 3
Join date : 2015-09-01
Re: Accumulating Snapshot Fact with Dimension at Same Grain
And if this is an accumulating snapshot fact, your grain isn't solely customer anyway. So the relationship isn't 1:1.
The 1:1 thing is more about modeling transactional facts. A modeler might get lazy and create a 'transaction' dimension to hold status flags and dates rather than breaking it down into multiple dimensions and carrying the keys on the fact. That is what he is warning against.
The other thing is a fact table that has only one dimension is, in Ralph's words, "not very interesting".
The 1:1 thing is more about modeling transactional facts. A modeler might get lazy and create a 'transaction' dimension to hold status flags and dates rather than breaking it down into multiple dimensions and carrying the keys on the fact. That is what he is warning against.
The other thing is a fact table that has only one dimension is, in Ralph's words, "not very interesting".
Re: Accumulating Snapshot Fact with Dimension at Same Grain
Thank you, that's helpful.
Andrew W- Posts : 3
Join date : 2015-09-01
Similar topics
» Tracking Support Tickets: accumulating snapshot and transaction grain fact (a presentation question)
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Time in fact or dimension? Accumulating snapshot
» Subscription as an Accumulating Snapshot Fact or a Dimension?
» Accumulating Snapshot Fact OR Type 3 Dimension?
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Time in fact or dimension? Accumulating snapshot
» Subscription as an Accumulating Snapshot Fact or a Dimension?
» Accumulating Snapshot Fact OR Type 3 Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum