Complex Cube desing Question

Question

The Analysis team wants to analyze sales pipeline data. Our CRM system(Source system) send the Data Warehouse team transaction as the Product moves through the different stages.

To mention a few: Creation, CallMade,LeadEngaged,CreditCheck,PapersReceived,Closed.There are more than 100 tracking statuses like mentioned above.

Sample transactions fact table:

ID LeadID TransDateTime Status TransPersonID

1 111 5/8/2008 Creation 123

2 111 5/9/2008 CallMade 123

3 112 5/9/2008 Creation 124

4 111 5/10/2008 LeadEngaged 123

5 112 5/10/2008 LeadEngaged 124

6 111 5/11/2008 CreditCheck 123

7 111 5/12/2008 PapersReceived 123

8 111 5/12/2008 Closed 123

Problem: Team wants to analyze:

•How long it took to move from One Status To Another.(Between Statuses) •How many leads moved from one status to another(Between Statuses). •Leads can move back and forth between statues. •For Eg: Then what to know at any given point of time how many leads moved between Creation and LeadEngaged. Any help is appreciated.

Answer 1

This can be done relatively easily with SSAS. Set up a time dimension with TransDate. Set up a salescount measure so that you can easily see number of transactions.

Unsure about the changing leads. If there is a paper trail type of table that shows what they used to be and what they are now or some type of status change table, you can make a dimension with that.