Hi,

I'm modeling for a data mart which is built above the process which
tracks the stages of identification to conversion of a customer.



Scenario:

One of the significant and mandatory attribute of the customer is
segment. The segment values are predefined and are stored in a master
table. The various stages of a customer are

initial
suspect
prospect
customer
It is possible the customer will be created either in the initial or
suspect stage. It is also possible for a customer to change from
initial stage to prospect stage directly. The stage then changes to
prospect and then to customer. In the transaction application the audit
log captures the dates when the customer stage changes. The following
are some of the queries which the model will have to support



How many customer by segment?
How many customers by segment in Q1? It is possible a customer was in
prospect stage in Q1 and got converted into a customer in Q2. So when
we report for Q1 it should be reported as prospect.
Similarly How many were converted into customer in Q2? In this we need
split by customers identified in Q2 and converted into customer in Q2
and those customers who were in other stages (not customers) in Q1 and
flowed into Q2 and converted into customers.
The average lag between stages in a given Quarter or by segment
To study the combination of lag between the various stages. Like how
much time did it take for a customer to move from initial stage to
prospect stage.(sum of lag between initial to suspect, suspect to
prospect)


I feel that an accumulating snapshot type of fact table will cater to
the above requirements. How ever for the Question 3 above I had to use
sub query or union two data sets. An analysis services 2005 cube will
be built on this model to empower user for ad hoc analysis also.



Are there any known limitations with accumulating snapshot type fact
tables when there is a requirement for ad hoc analysis? Is there any
known limitation of a analysis services cube built on a accumulating
snapshot fact table?



To ensure my query is clear I have tried to provide the information in
detail.

Accumulating snapshot fact tables tend to be updated a lot in the ETL
process. Would that be an issue in analysis services CUBES.