Status Fact?

[D_Pons]

I have a feeling I may have missed an important point in my understanding of dimensional modelling. I’m hoping someone can point me in the right direction.

We are a voluntary organisation in which the member can determine the amount they wish to pay for their membership benefits (above a lower limit). The business requirement is to analyse (profile) the existing memberships. They want to look at a mixture of details some of which are ‘as at specific stages’ (e.g. at membership recruitment) and some of which are ‘as at time of analysis’.
This applies to both measures and dimension attributes.

So for example the current value of memberships, original value of memberships (and change in value), broken down by current geographic information, original source of recruitment and source of most recent change in value (i.e. what marketing initiative caused an upgrade or downgrade in the membership value).

We are currently leaning towards a ‘status fact’ that records the status of a membership (i.e. it is not based on a particular process event). We are also leaning towards this ‘fact’ having just one row per membership which is updated daily. For the ‘at specific stages’ information I think we are creating something like an accumulating snapshot. For the ‘as at time of analysis’ I think we are creating something like a daily snapshot (without retaining previous days). So long as the dimension only contains type 1 information in it, the surrogate key in the fact will always be the same. However for measures that are changing or for type 2 dimensions, we would have to update the measures and surrogate keys.

Whilst in theory it would seem to meet the ‘brief’ I’m not comfortable with the notion of updating Facts in this way. I feel like we have ‘merged’ different types of fact into a single table to make the immediate analysis easier.

Are there other ‘patterns’ that we should consider? E.g. Is it better to ‘merge’ the facts in SSAS rather than in the dimensional model?

[We are using MS SQL Server 2008 – SSIS, SSAS, SSRS
Volumes are fairly low in this context – say 2 million memberships with half being active memberships and updated several times a year]