| 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 Tasks? Expected Answer based on above Data:
Total Unique Tasks? Expected Answer based on above Data:
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.
Submitted under:
Microsoft SQL Server · SSAS · · ·