Multiple Facts tables (related between each other) with multiple dates

[angelompcunha]

Hello everyone,

I'm having a hard time making a proper data model for my application.

For context, I've developed other applications in the past where I had a single facts table with multiple dates and I've used successfully a link table with a DateType to link all those dates to a Master Calendar.

What I'm facing now is having multiple facts tables, which are related to each other, and each of those tables contains multiple dates and dimensions to be used in the applications.

Below is an example of how the data is structured:

Although a link table works perfectly when I have a single facts table, in my current use case it is causing me a lot of headaches because of the circular references and I can't find a way of structuring my data model.

Concatenating them all together is not an option because except for the date columns and on or other dimension, the remaining columns are very different. 

Using a link table would be an option if the facts tables were not related to each other. Preferably I want to maintain these relationships because it allows me, for example, to see for TICKET_ID 1 what it's CHILD_TICKET_ID are and so on and so forth, which with the link table I can't do

Besides the dates, I also have, as you can see in the schema example, dimensions that apply to more than one column in each fact table which makes the topic even more complex.

I've looked a lot in the community forum and the web but couldn't find any examples for this specific use case.

One possible solution that came to my mind was using multiple link tables and eventually multiple calendars to then link them all together but I always end up with a circular reference problem.

I also thought of separating each facts table into 2 but then when it comes to the relationship with the parent fact table it gets all messed up.

I'd be very appreciated for your hep

[marcus_sommer · Accepted solution]

Personally I would concatenate the ticket-tables because they contain all the same essential information and even if they have some different extra fields and contain a different granularity it's neither technically nor logically a show-stopper to go in this direction.

To concatenate the facts doesn't mean that various additionally transformation are useful/necessary. For example adding the source-information to each load, adding the Ticket_ID to the grand children and maybe also counting/concatenating the tickets from the lower granularity, checking the exists against each other, calculating the duration between the multiple dates and/or the offsets and many other things and in general harmonizing everything possible.

[angelompcunha]

Thanks for the feedback.

I do really see a lot of people concatenating different facts tables but wouldn't that make it impossible to do something like a sankey chart with CHILD_TICKETS per TICKET like in the screenshot below?

[marcus_sommer]

I never built a sankey chart and have therefore no own experience with it but I don't think that it mandatory needs associated tables instead of a single table. Just try it - it's only a matter of a few minutes to merge the (reduced) datasets within a prototype and checking if it's directly possible or may more or less difficult to create.

[angelompcunha]

Unfortunately it doens't work

[marcus_sommer]

Don't give up to early. Maybe there is something missing in your data, for example that the grandchilds haven't enriched with the ID from the parents.

[angelompcunha]

Agreed, probably I just need to work out how to do this.

I'll mark this as solved with your first reply