Dimension Attribute vs Fact Table Key

[meb97me]

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

[cjrinpdx]

daily snapshot FACT table

Is this a periodic or accumulating snapshot fact table?

[meb97me]

Daily periodic snapshot

[BoxesAndLines]

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.

[meb97me]

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

[BoxesAndLines]

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

[Sponsored content]