Dear all,

I am familiar with Dimensional Modelling but here is a type of question I have not had to address before and I don't quite know how to model/handle this.

We supply financial products to customers. A customer applies through an "Application".

Depending on various factors, an Application may require an action from an Operator in order to progress. We call this a "Task".

Once a "Task" is fullfilled, another "Task" maybe necessary and there may be a delay before that next task starts (the end of one task is not necessarily the beginning of the next).

Tasks are grouped in Categories.

We would like to be able to answer questions like: "What were the Applications that moved from a Task of category X to a Task of category Y within a particular period?"

My guess would be that I will have a Fact table of Tasks with the following columns (foreign key to dimension tables)

- TaskApplication -> DimApplication (which application does this task applies to)

- Task_StartDateTime -> DimDate

- Task_EndDateTime -> DimDate

- TaskType -> DimTask (Type and Category)

My difficulty is then: how do I (quickly) identify all applications that moved from one task of type X to another of type Y.

Am I right in thinking that I should have a column in my fact table indicating the Task Rank for each Application (first task for that application is 1, second 2, etc)?

Eric

Hi Eric,

Your Fact Table looks just right to me. You simply need a query to answer your question. I see no reason why you can't have a query that interrogates the Fact table twice - something like:

SELECT DISTINCT FACT_FROM.TaskApplication

FROM FACT_TABLE FACT_FROM,

FACT_TABLE FACT_TO,

DIMDATE DATE_FROM_START,

DIMDATE DATE_TO_START,

DIMDATE DATE_TO_END,

DIMTASK TASK_FROM,

DIMTASK TASK_TO

WHERE FACT_FROM.STARTDATETIME = DATE_FROM_START.DATETIME

AND FACT_TO.STARTDATETIME = DATE_TO_START.DATETIME

AND FACT_TO.ENDDATETIME = DATE_TO_END.DATETIME

AND FACT_FROM.TaskApplication = FACT_TO.TaskApplication

AND DATE_TO_START.DATETIME >= @DATE_FROM

AND DATE_TO_END.DATETIME <= @DATE_TO

AND DATE_FROM_START.DATETIME <= DATE_TO_START.DATETIME -- Make sure X occurs before Y

AND FACT_FROM.TASK_TYPE_KEY = TASK_FROM.TASK_KEY

AND FACT_TO.TASK_TYPE_KEY = TASK_TO.TASK_KEY

AND TASK_FROM.TASK_TYPE = 'X'

AND TASK_TO.TASK_TYPE = 'Y'

I know the query above is not perfect and I've not used ANSI joins etc but hopefully it shows how you can get the answer to your question using your chosen fact table structure.