Accumulating Snapshot Fact with Dimension at Same Grain

View previous topic View next topic Go down

Accumulating Snapshot Fact with Dimension at Same Grain

Post  Andrew W on Tue Sep 01, 2015 6:37 am

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

Andrew W

Posts : 3
Join date : 2015-09-01

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact with Dimension at Same Grain

Post  BoxesAndLines on Tue Sep 01, 2015 3:07 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact with Dimension at Same Grain

Post  Andrew W on Wed Sep 02, 2015 3:58 am

We have around 40,000 customers, so not huge numbers

Andrew W

Posts : 3
Join date : 2015-09-01

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact with Dimension at Same Grain

Post  ngalemmo on Wed Sep 02, 2015 5:34 pm

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".
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Accumulating Snapshot Fact with Dimension at Same Grain

Post  Andrew W on Thu Sep 03, 2015 3:03 am

Thank you, that's helpful.

Andrew W

Posts : 3
Join date : 2015-09-01

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact with Dimension at Same Grain

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