Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
angelompcunha
Contributor III
Contributor III

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

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:

qlik example.png

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

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

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.

View solution in original post

6 Replies
marcus_sommer

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
Contributor III
Contributor III
Author

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?

 

qlik example1.png

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

I'll mark this as solved with your first reply