I am working on QlikView Direct Discovery Hybrid approach with MySql data base. In the implementation of this I am facing lot of Issues and to name some of them: Direct Query failed, Too bad performance etc etc. What I am looking from you guys is your VALUEABLE inputs and suggestions.
Here we go:
I have a dashboard of the size ~1.5GB with multiple tabs and out of it one is Transaction level details. Now we are going ahead with Hybrid approach as this is the most feasible option in our case. Below are the two options which I tried:
1: Linking the Transaction table with the Fact file on a Key (Field1&Field2&Field3&Field4&Field5)
In this approach I frequently encounter with an error msg "Direct Query Failed", and upon checking error msg in the tracing file I came to know that the QlikView is unable to display results because of the complexity of the query. So I moved to another approach
2. Keeping Transaction table as a stand alone table(No link between DirectTable and Existing QVDs)
Here we still have the common Key (Field1&Field2&Field3&Field4&Field5) in DirectTable and the Fact file but the name is different so as to prevent auto linking. Now the idea is to link Transaction tab with rest of the Tabs using the "Select in Field" trigger on Key
What ever selection I make in any of the tab apart from Transaction tab will result in filtering of Key which I can easily pass on to the Key in Transaction tab using the Select in Field trigger, and it is giving us the results. But, the moment we clear the fields and start making selections in the Transaction tab, it takes between 1-15 mins to refresh the table box based on the number of records. This is where the main problem lies. How best we can optimize the performance in this scenario.
Note: We have tested applying the queries on MySql database directly to check whether the Lag is from QV or MySql, But to be very honest MySql response time is quite fast(1-5 secs)
We are using QV11.2 SR5
It would be of great help if anyone could provide logical workaround.
Any help would be highly appreciated