I solved a request for a customer, but my data model is not according to ‘the rules’.
Which means it is not according to the classical star schema.
My customer is happy and everything works like expected, but I’m a bit unhappy, because I could not manage to build a classical star schema.
1 table with Site information (customer name, street, city…)
1 table with working hours
All working hours are related to the Site table using a unique Site No.
But there are always 2 values that are kept on the Site level (entered later after a Site is finished).
First one is the invoice amount for that Site and any additional costs (as a sum).
That means, fact data is also present in a DIM table.
Following the Forums on Enterprise DNA this seems not to be a good idea.
But until now I don’t have a good idea how to deal with this situation.
For sure I could set up a separate table for the invoices and costs for a Site, but then I end up with 2 FACT tables (also not a good idea).
So, maybe someone has an idea how to deal with this situation.
I think that multiple fact tables in a report is not badpractice, as long as you keep a star schema for each fact table. What is bad practice however, is having multiple fact tables which are linked together.
However, it might not be needed for your example to have multiple fact tables.
Do you want to see the additional costs/invoice amount only on a per site level? Or do you want to filter them by date and other dimensions as well?
If you ony need to see those values per site, it makes perfect sense to keep them as an attribute of the dimension. So what you currently have would be good. However, if you need to do more specific analysis, you need to model them as a fact table.
This is the type of problem that eventually most report designers encounter - they discovering data that requires you to move from ‘ideal’ to ‘it works’.
My primary dataset is not a star schema, it is actually a snowflake, in two different ways:
Dimension Tables - Customer table is associated with both Ship-To and Department tables, they cannot be combined without making a monster table that is really not very useful (there is not a one-to-one match of ShipTo and Department, and both are needed for reports).
Fact Tables - There is a lot of detail in Invoice Header tables that I need, and it would bloat my Invoice Line tables unnecessarily. So again, I kept this as two tables, with the header table filtering the line table…
This is the reality of my data, so after much head-scratching, I determined it was best to accept this.
And yes, you certainly can have multiple fact tables, consider the case of a dataset with both invoice and budget data - those would almost certainly be in separate fact tables.
My logic is that I always TRY to strive for the ideal first, but when I determine that the ideal method is not realistic, then I will allow myself to find a more realistic solution.
.