Forum Discussion
Multiple Fact Tables/Granularities - drill-through without a fact-fact relationship?
My facts are Tickets and Ticket Actions, with various dimensions - some common to both, some unique, e.g. Action Type relates only to Ticket Actions, but it is of interest to see the Ticket Type of the Ticket 'parent' of a Ticket Action.
I have modelled the data with separate facts, with a relationship between both. Here is a simplified view (I actually have many more dimension tables):
This all seems to work fine for what I need. I can slice Ticket Actions by Ticket dimentions as the filter propogates through to Ticket Actions. I can also drill from particular Ticket in a table visual to see the associated Actions. But two things:
1. I've read should never relate fact tables, or use one fact table as a dimension to another. Rather I should either create one big fact table (which in this case would means millions of rows so not practical ) or use an inactive relationship between the two
2. I now need to do time intelligence on the TicketActions table, which means I need to create a relationship between Calendar, complicating things a little.
Do I need to remodel my data? Are Fact-Fact relationships ok?
Many thanks
2 Replies
- Daniel29195
Community Champion
Anonymous
a good practice to know if the facts of the two tables can be set in one fact table if to follow these two conditions :
rule1 :
When 2 or more facts describe events that do not take place at the same time, they describe different processes.
when two or more facts do not occur simultaneously, they represent different processes. thus they need their own fact table.
rule2 :
When two facts exhibit different grain, they represent different processes. thus , different fact tables.
know i would suggest to create a dimtickets and link it to both fact, this way you remove the relation between the two facts.
+ you can relate calendar to ticket action on Actioncreatedon column .
let me know if this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
