Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
morenoju
Partner - Specialist
Partner - Specialist

Too much data in memory - bringing old data only when requested

Hi all,

I have a challenge when working with Qlik Sense regarding the amount of data loaded in memory.

Most of the time my users work with data from the current year and the previous year. That's all I load in the load script, even though the data hub has data for several previous years.

Every now and then, a user will want to compare recent data to data way in the past (e.g. 5 years ago). Right now, it is not possible, because that data is in the DB but not in memory. Loading everything that is in the DB into Qlik is not feasible for performance reasons.

My ideal solution would be being able to run queries, or a piece of a script on demand. So if a user goes to a date for which there's no data available in memory, it is brought in at that moment.

Hoewever all I could see about this so far was related to on-demand app generation which is not exactly what I'm looking for. My scenario isn't having an overview and then letting users create detailed apps when they narrow down results. What I'm looking is for a way to bring old data when requested.

What would you be your approach for this?

Thanks much!

Juan

Labels (3)
5 Replies
Lisa_P
Employee
Employee

As your data source is a database, you could look into the direct discovery method.

See link to help ..

https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/DirectDiscovery/diff...

 

 

marcus_sommer

Before going in this direction I would check if the datamodel couldn't be more optimized - and often there is a significantly potential for it. A further method to reduce the size of the datamodel is to use a mixed granularity for the data - maybe to keep the current year / last n month on a transactional level, the previous time-frame to it on a daily level and the older ones on a monthly level - for the most business views this will be sufficient for the users.

- Marcus

morenoju
Partner - Specialist
Partner - Specialist
Author

Hi @marcus_sommer , that was actually my first thought, to aggregate old data. However, in the concrete case I'm dealing with, I'm going to need the whole granularity for the old data even if it's requested less often.

I'll have a look at what @Lisa_P suggested. Sounds like an approach that could suit my case.

Thank you both!

marcus_sommer

I'm not familiar with the direct discovery feature but AFAIK it's aimed to pull rather small datasets within the datamodel like some aggregated results or live data like the actual exchange-rate for currencies or similar stuff. Therefore I doubt that it would be very suitable for pulling really large datasets  - and it might not only the Qlik-side which struggles else the database and the network would need to perform quite fast, too (would they, really ?). Nevertheless just give it a try.

Further alternatives might be some kind of document chaining - maybe with multiple applications - or also to trigger some (EDX) reloads ... But like already mentioned before thinking of anything like that I would ensure that the used datamodel is really optimized (IMO it's easier to optimize an application as bypassing the issues with such "external" measures and it's from principle a sustainable approach).

- Marcus

morenoju
Partner - Specialist
Partner - Specialist
Author

I tried the Direct Query but it is giving me an error whren trying the fields on a straight table: "There are too many rows to display this visualization".

Document chaining seems to be similar to On Demand Apps, which is what I'm going to try now. When reading about it, I don't like the idea of the users having to go from the selection app to the detail app to see the results for the data they selected. I think they might get confused. But still, I haven't been able to find an alternative so far.