Hi @lena2a2a2
Very interesting question, I found this document in the Qlik Community Direct Query or Direct Discovery ; which contains a very interesting and illustrative PDF document I found enlightening, (I attached the PDF to this reply); based on reading that document:
1. So could you be so kind and clarify how Direct Query works, where and when are queries calculated ?
- Direct Query are calculated at the backend database, so their performance depend on the fine-tuning of the backend database;
- Direct Query divides the query in 4 sections: Dimension; Measure and Detail; Qlik load the Direct Query its Dimensions during the script load, the Measure and Detail fields are loaded on-demand from the UI.
2. Should we make one huge flat table from our five fact tables in our data base?
- Interesting question, but we need more information about your five fact table prior to guiding you; according to the documentation, you can make a huge flat table but the time taking to build it depends on the Dimensions retrieved from each fact table and the effectiveness and tuning of the indexes between these fact tables. We need to know-understand what problem are you trying to resolve by implementing Direct Query.
3. Will Direct query work if fact table contains more then 150 attributes?
- I did not find any constraints limiting the number of Dimension, Measure and Detail fields your Direct Query define; We should understand that Dimension fields are loaded into the Qlik Application's in memory tables, while Measure and Details fields are loaded, on-demand from the User Interface driven by the charts selected by the users; If you are building a solution with charts with lots of dimensions or several measures you will be faced with the solution complexity to understand the data.
- Once again, we need to know more about the solution to provide a better answer.
4. Why do we need reload script when we do correction in our script?
5. How often should we reload app with Direct Query?
- Based on the documentation, if your Direct Query's DIMENSION fields are very static or change very little over time you do not need to reload the app, as the way Direct Query works, it calculates the Measures on demands, issuing SQL queries to the back-end database when required.
6. Where i can read more information about direct query except qlik.help.com?
- I hope the attached PDF document give you a better understanding of Direct Query.
7. Is "dynamic views" in Qlik Sense the same as Direct Query?
- They seems to be different beasts, my Qlik Sense Business and Desktop failed to show me the "Dynamic View" option, if I managed to find out why, I will share my finding in this thread.
8. What else can i use instead of Direct Query to achieve real time reloading of data?
- This is a very interesting question, requiring a clear definition of "real time" with the stakeholders; Do they want to know the precise figure (KPI, value or amount) of some measures-dimension combinations as they were 1 seconds ago? or will they be happy knowing the 30 minutes old figure (KPI, value or amount)? e.g. you refresh the application every 30 minutes!
- There is a cost element involved when answering the previous question; How much will it cost the business to have their data up to the previous second or the last 30 minutes? I usually ask them; Would somebody die if the KPI is know 10 seconds later?
- Once, the "real time" is defined and fully understood between stakeholders and the developers, the next step is to identify the contribution of each fact table to the result the stakeholders expect; and the number of records each fact table growth over time; if each fact table growth 1000K records per minute you got a real challenge, but I expect you to find their growth is variable, one of them will have a fast growing speed, while others grow very slowly (I am thinking you are dealing with a manufacturing process and collecting data from 5 different machines, I could be wrong).
- Once you and the stakeholders are satisfied working out the contribution from each fact table into the expected KPIs, you prioritized their impact in calculating the KPIs, some of these fact tables' contribution to the KPIs are very important, so you and the stakeholders should build a priority list of each fact table weight into the KPIs calculations, and prioritize their extraction.
- Finally, you will be able to write QVD building applications that you can schedule (with the Task Schedule) to run on the agree frequency. (1 minute, 5 minutes, 30 minutes). Note, be prepare to adjust your running frequency based on the time to build the QVD from the first fact table, if building this QVD takes 15 minutes, then your frequency should be >= 15 minutes.
It is a lot of fun! One last thing, read the Direct Discovery PDF, there are some suggestions-warnings on when to implement this feature.
Hope this helps!
Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
