I'm working on creating a Star Schema for my internship that I want to use for reporting on the company's pre-sales process. The aspects of the pre-sales process that I want to report on, are:
- Sales proposals;
- Task management.
The thing that I'm having trouble with is whether or not to separate proposals and tasks from one another in my Star Schema. For this reason, I've now created two different ones and I wonder if any of them is the "correct" way of doing it.
These are the metrics that I eventually want to be able to report:
- % of tasks finished on time
- Workload over time
- Due and completed proposals
- Number of active submissions
- Potential total sales
- Potential total gross profit
- Potential average gross profit % per sales team
- % of submissions converted to orders
Which of these schema's do you guys think is better? I've been trying to wrap my head around this for some while now, but I can't figure it out. Star Schema 1 has less redundancy, but does have values that I want to use in a dimensional table. Star Schema 2, on the other hand, has a lot of redundancy because proposals can have multiple tasks (sometimes up to 20).
Also, a more general question regarding my schema's: - Do all relationships have to be visualised in the Star Schema Diagram? For example: all dates in every table must be linked to the date table. And: all references to employees must be linked to the employee table.
P.S. The application and its database are still being developed so I have no data that I can use to test it.