I have the following assignment for Business Intelligence Class:
"The CSUF Business Association is conducting a babysitter service as a fundraiser for different clubs in college. When a customer is entered into the system, the CSUF Club coordinator gets name, address, and phone. The coordinator also records each babysitting job, the amount paid for it and the sitter assigned to the job. Each person may sign up to credit only one club and the system keeps the contact person and phone number for each participating club.
The treasurer wants a data warehouse for this. He would like to be able to determine how much each customer was billed by week, month, or year. How much each employee (sitter) earned, also summed by time periods. He is interested in how much work is done on weekends, holidays or other special days.
Develop a data warehouse using the methodology you prefer to provide this information (including fact and dimension tables, their attributes, keys, and relationships), referring the name of the schema that was adopted."
I was thinking about the following fact tables:
| Fact_CustBill |
|---|
| Dim_Time_ID |
| Dim_Club_ID |
| Dim_Cust_ID |
| Fact_BilledAmount |
| Fact_EmployeeEarns |
|---|
| Dim_Time_ID |
| Dim_Club_ID |
| Dim_Emp_ID |
| Fact_EarnedAmount |
| Fact_WorkSpecial |
|---|
| Dim_TimeSpecial_ID |
| Fact_HoursWorked |
I'm thinking about these fact tables just based on an example I was given during class. I'm not sure about Dim_Club_ID being necessary on the employee tables and I'm also not 100% about the "Fact_WorkSpecial". Is my reasoning correct or not really?
You can have multiple fact tables. And you should have multiple fact tables if you’re recording data of different granularity.
But in this case one fact table is enough for both employee revenues and amounts biller to customers. The grain is the same: the babysitting job.
So you can have one fact table linking to club, date, customer and employee dimensions, with multiple measures: hours worked, amount billed to client and amount earned by employee.
When you want to report you can pick only one of those measures and query on it. But it will give you more info: whether some clients prefer to work with certain employees.
The trick here is your date dimension. It needs calendar year, month and day, but also year of week (iso 8601), week number and day of the week.