Slowly changing sales opportunity facts or dimensions

We have built a datamart from our CRM system to query on sales opportunties. Sales opportunities look like facts in that they have dimensions (customer, product etc ) and attributes (value of opp). However, they also change status from say, RED to AMBER to GREEN and the value of the potential order can vary, over their lifecycle. We wish to be able to see how these opps have changed over time, eg increase in value of the pipeline over last month. So our conclusion was to build a history-handled table with dimensions linked to it. This way we can say from date A to date B it was RED, date B to date C it was AMBER and from C to now its GREEN.

This has worked pretty well, but it seems the 'wrong' way round in that we have what looks like a slowly changing 'fact' table. Might there have been alternative ways of approaching this?

You've created an accumulating snapshot fact table. And it works for you. How is that wrong?

twofivepie, can I clarify something?
Does your design involve creating a new fact row every time the attributes (e.g. status) or the measures change? Each row then has start date, end date and current flags?
Or do you have multiple dates to represent each change of state in a single fact row? If the latter, does this mean that the number of changes is predictable?

We have a similar scenario with Memberships, where the bussiness requirement is to profile those Memberships (count and value) against dimension attributes as at start of membership, as at now and as at end of membership. So I'm interested in what design patterns others have come up with.

We have the situation where the path an opportunity can take is unknown: for example the confidence level can go from RED to AMBER and back to RED or could just go from RED to GREEN. So we decided to create a new fact row, with the opportunity ID as the key, with a valid-from and a valid-to date for *every* status change. The 'current' row has the valid-to date set as 31-dec-9999. When that row becomes a history row, the valid-to date gets overwritten with the new current row's valid-from date. We use the history handling functionality in Business Objects Data Integrator to do this.

We then have a date dimension table and we use a between join. To get the value at a particular date in the past we look for those rows where the date dimension lies between the valid-from and valid-to dates. Because these are designed to not overlap, then for any given date, we only get back one fact row for that opportunity.

HTH

Simon

ngalemmo wrote:You've created an accumulating snapshot fact table. And it works for you. How is that wrong?

I guess I hadn't appreciated that that is what we were doing :-)

Thanks for the clarification.

FWIW, the direction we were going to take was to have a "transactional fact" that has a row created for each change in membership. This would have a surrogate key to the Type 2 dimension rows of interest. So we would have a grade change fact with a surrogate key to the Membership dimension at recruitment, the Membership dimension prior to grade change and the Membership dimension after the grade change.

For profiling memberships we had thought of an accumulating fact but up to now we had thought that this meant one row per source system record with multiple attributes to track a predictable series of events. This doesn't seem to fit your scenario or ours.

I'll have to consider the way you have done it and see if it has merit for us.

Thanks

We then have a date dimension table and we use a between join. To get the value at a particular date in the past we look for those rows where the date dimension lies between the valid-from and valid-to dates. Because these are designed to not overlap, then for any given date, we only get back one fact row for that opportunity.

Why get the date dimension involved in this? The start and end effective dates are essentially degenerate dimensions. They usually have no need for interpretation by the date dimension. To get current rows, you either select where today falls between the dates or for end-dates = 12/31/9999. If the user wants a specific as-of date, they usually just enter a date and the value is used in the between. No need for the date dimension.

[quote="ngalemmo"]

Why get the date dimension involved in this? The start and end effective dates are essentially degenerate dimensions. They usually have no need for interpretation by the date dimension. To get current rows, you either select where today falls between the dates or for end-dates = 12/31/9999. If the user wants a specific as-of date, they usually just enter a date and the value is used in the between. No need for the date dimension.

Fair question. The main reason was that it allowed us to have additional characteristics associated with the date selected by the user (eg week number, applicable quarter-end date etc). A secondary reason was that this then allows a robust universe to be developed for use by the average report writer. Without this its rather easy for a report writer to forget the date restriction and get multiple fact rows back. But I agree, its not necessary to have this.