Dimension Attribute vs Fact Table Key

View previous topic View next topic Go down

Dimension Attribute vs Fact Table Key

Post  meb97me on Wed May 08, 2013 6:45 am

I appreciate this has probably been covered in various forms previously but i wonder if i could get some advice on my particular implementation.

We have a fairly large customer dimension ~9 million records

There are lots of date attributes we'd like to capture but i'm struggling to understand whether they would sit as an attribute on the dimension, or within a mini dimension or as a role playing dimension on the account status daily snapshot FACT table.

We're looking at attributes such as
Last Date Paid
Last Date Contacted By Phone
First Inbound Letter
Last Inbound Letter
First Outbound Letter
Last Outbound Letter
Welcome Letter Sent Date
etc

theres probably 60+ of these sort of dates

Now on a large dimension such as ours, some of these dates are going to be changing on a daily basis, so we're potentially going to have have to split them out into a mini dimension as otherwise we're going to have an unweildy monster dimension

However with 60+date fields dates and dates potentially spanning a couple of years at least, the mini dimension itself would not be so mini, as the likelihood of the exact combination of dates across all fields matching between customers is pretty unlikly so in effect we're still going to have 9 million+ combinations of dates and it will grow as the dates change, so in this case is there any benefit in having a mini dimension?

Or alternatively if we put them on account status fact table we would have loads of role playing dimensions which could make end user interaction with the model very difficult.

As always thanks for your valid input

meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

Re: Dimension Attribute vs Fact Table Key

Post  cjrinpdx on Tue May 14, 2013 3:04 pm

meb97me wrote:daily snapshot FACT table

Is this a periodic or accumulating snapshot fact table?

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: Dimension Attribute vs Fact Table Key

Post  meb97me on Tue May 14, 2013 6:04 pm

Daily periodic snapshot

meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

Re: Dimension Attribute vs Fact Table Key

Post  BoxesAndLines on Tue May 14, 2013 9:11 pm

I would put the letter dates in the fact table where I track letter metrics. The last paid date I would put in the fact where I track customer payments, and so on. Hopefully, these are not all in one fact table.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Dimension Attribute vs Fact Table Key

Post  meb97me on Wed May 15, 2013 3:13 am

The plan is to have specific FACT tables supporting each of these individual processes ie a transactional fact table for phonecalls, one for letters, one for payments etc which would capture the detailed metrics and appropriate supporting dimensions

However these first and last dates are really what the business use alot to slice and dice the customers by, so rather than have to derive these values each time from the different transactional fact data we're keen to provide them as daily persisted values which can be selected or filtered by.

So the issue's where's best to keep them

As an attribute of the Account Dimension (but as they're likely to change frequently (ie daily) have them as a seperate mini dimension)
or a datekey on a daily period snapshot FACT Table (grain is per account per day) which provides the status of each account on a daily basis




meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

Re: Dimension Attribute vs Fact Table Key

Post  BoxesAndLines on Wed May 15, 2013 8:21 am

If users want to slice and dice by a date, I normally relate it to the date dimension off of the fact table.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Dimension Attribute vs Fact Table Key

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