question

LoanID TaskID LoanStatus TaskStatus Task Created Date Task Modified Date Loan Status Created Date Loan Status Modified Date Assinged to Loan Processor 1 10 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null Group1 Shan 1 11 Processing Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM 1/1/2012 10:00AM Group1 Shan 1 11 Processing Completed 1/1/2012 10:00AM 1/2/2012 10:00AM 1/1/2012 9:00AM 1/1/2012 10:00AM Mary Shan 1 10 Processing Canceled 1/1/2012 10:00AM 1/2/2012 10:30AM 1/1/2012 9:00AM 1/1/2012 10:00AM Mary Shan 1 12 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null John Shan 1 12 Processing Assinged to 1/1/2012 10:00AM 1/1/2012 6:00PM 1/1/2012 9:00AM 1/1/2012 10:00AM Mary Shan 1 12 Completed Completed 1/1/2012 10:00AM 1/3/2012 10:00AM 1/1/2012 9:00AM 1/3/2012 10:00AM Mary Shan 2 10 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null Group1 Jessy 2 11 Processing Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM 1/1/2012 10:00AM Group1 Jessy 2 11 Processing Assinged to 1/1/2012 10:00AM 1/1/2012 4:00PM 1/1/2012 9:00AM 1/1/2012 10:00AM Kevin Jessy 2 11 Processing Completed 1/1/2012 10:00AM 1/2/2012 10:00AM 1/1/2012 9:00AM 1/1/2012 10:00AM Kevin Jessy 2 12 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null John Jessy 2 12 Processing Assinged to 1/1/2012 10:00AM 1/1/2012 6:00PM 1/1/2012 9:00AM 1/1/2012 10:00AM Mat Jessy 2 12 Completed Completed 1/1/2012 10:00AM 1/3/2012 10:00AM 1/1/2012 9:00AM 1/3/2012 10:00AM Mat Jessy 3 10 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null Group1 Albert 3 11 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null Group1 Albert 3 12 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null Group1 Albert 4 10 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null Group1 Shan 4 11 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null Group1 Shan 4 12 Created Created 1/1/2012 10:00AM Null 1/1/2012 9:00AM Null Group1 Shan

This is my detail level data

I need to design Fact and dimension Table and then create cube for my busness analyst to analysis the data

Using Cube, My business analyst should analize below senario

Total Loans? Expected Answer based on above Data:

Total Loans 4

Total Tasks? Expected Answer based on above Data:

Total Tasks 20

Total Unique Tasks? Expected Answer based on above Data:

Total Unique Tasks 3

Loans by Current Loan Status? Expected Answer based on above Data:

LoanStatus Total Loans Created 2 Processing 0 Completed 2

Tasks by Current Task Status? Expected Answer based on above Data:

LTaskStatus Total Tasks Created 7 Assinged to 0 Completed 4 Canceled 1

Total Tasks by Loan Processor and Current Task Status? Expected Answer based on above Data:

Created Assinged to Completed Canceled Shan 3 0 2 1 Jessy 1 0 2 0 Albert 3 0 0 0

Avg Time taken for complete each Task? Expected Answer:

TaskName Avg Time in Minutes 10 500 11 200 12 300

Avg Time taken for complete each Task by Assinged to User? Expected Answer:

Note: Filter Task Status : Completed

Task Completed BY TaskName Avg Time in Minutes Mary 10 500 Kevin 10 200 Mat 10 300

How many Task completed by today?

These are the possible analysis might able to do using cube.

I know there are lot of data warehouse genius in this forum. Please help me to design my fact and dimension for this.