Drilling across fact tables concept in Qlik Sense?

[user467341]

Hi Qlik community,

I have an App with two separate FACT tables using same dimensions, but are loaded twice (e.g an employee dimension table is loaded two times, one for FACT1 and one for FACT2). This avoids circular reference.

According to Kimball data warehouse, FACT tables can be drill across using conformed dimensions. In this case, is there a method or strategy I can implement to drill across my two FACT tables? What is the Qlik way of doing this?

Thanks.

[marcus_sommer]

Like previously already suggested the recommended data-model is a star-scheme with a single (horizontally + vertically merged) fact-table. Probably there are ways to sync multiple fact-tables and associated multiple dimension-tables, but it will have various side-effects in regard to the development-efforts, performance und usability. Therefore, why doing simple things much more complicated as needed ...

- Marcus

[user467341]

Hi Marcus, thanks for the reply.

I understand your point, but due to reason I need to understand this too. Besides the the method you suggested, I will like to know these alternatives (even if it's complicated). This will allow me to have more flexibility. 🙂

Right now I have two stars, and they are not associated as I have loaded duplicate dimensions to these facts to avoid circular reference.

[marcus_sommer]

For me there are a lot of reasons to use a star-scheme, for example:

• avoiding synthetic keys and circular loops by design
• simple handling + usability within the script and the UI with no duplicated fields which needs to be synchronized for dimensions and/or selections and/or adding multiple measure-fields within calculations
• easiness and speed within the application-development - because it's generally so simple to start by merging the fact-tables and looking then with a few table-boxes and pivot-charts which data are there and wouldn't it already enough for the final report
• no risks to change the number of records (at least if the very powerful mappings are used by any horizontal merging)
• no trouble by missing key-values between the fact-tables because there is no association-attempt against NULL
• readability of the data-model and the used logic
• maintainability (each more complex logic will cause more efforts to maintain the application)
• performance - nearly none other approach will perform better and if it's really possible it will require much more efforts and a quite high degree of experience

- Marcus