Reload document on user access

[dselgo_eidex]

Hello,

Recently my company has been getting more and more data that we want to restrict access to in QlikView, and I've been tasked with figuring out how best to do this. The data in question is very substantial and we have been struggling with long reload times. Our current solution is to load all of the data (2+ hour reload) and then restrict the data via set analysis in the charts. But since the user doesn't need to access any data outside of their own (and they shouldn't be allowed to), this seems like a waste of time and a detriment to performance. So the way I see things, their are two solutions:

1. Section Access. We have dabbled in section access before, but it doesn't solve the long reload time issue. I've also heard that it can cause long initial load times when accessing a document that has section access.
2. Reloads on log in. My idea is that when a user logs in, a variable is set to their ID. The login event or variable being set triggers a document reload, and the load script includes a WHERE clause using the value in that variable. I see this as being the optimal solution because it reduces the amount of data in the document thereby improving the overall performance, and it doesn't mean that we have to suffer through 2+ hours of reload whenever we get updated data (which occurs about every other day).

What do you all think? I am not extremely familiar with QlikView server so it might be that the second solution will just not work. Additionally, what happens if a user logs in and triggers a reload? Will it affect other users that are logged in at the time?

I'm going to be testing these questions, but I would like to hear any feedback or additional ideas.

[jwjackso]

I doubt that it would reduce the load time, but have you looked at the reduce by field option:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/QMC/Content/QMC_Documents_SourceDocumen...

[marcus_sommer]

I suggest of optimizing your load-approaches and the applications itself and then using section access to restrict the data on user-level and to ensure more performant UI calculations. Of course the opening-times for an application will be longer with section access as without it but it is usually not very relevant. More impact o this will have if you used compression of the qvw's or not.

I don't want to say that your 2. isn't possible but it's quite difficult to implement and I think you would get far more disadvantages with it than benefits.

For optimizing the qvw and the loadings (with incremental approaches and optimized qvd-layers) take a look here: Advanced topics for creating a qlik datamodel.

- Marcus

[dselgo_eidex]

I will say that maybe I hyperbolized the load time. It does take 2+ hours now to reload our data model, but I would say that is mostly due to adding a very large un-optimized data set on top of an existing un-optimized data model. We have done a lot of work towards optimizing our data model so I definitely don't expect it to take as long as it has in the past.

The main reason that I am exploring option 2 is because our data set will continue to grow larger and faster in the future as we gather data from numerous clients. Even if we have an extremely optimized data model and UI, eventually the large amount of data and memory usage will affect performance. And I see option 2 as being the solution to that. And as for the difficulty of implementing it, I already have a process in mind so I'm not extremely worried about that.

Am I needlessly too concerned about this?

[rwunderlich · Accepted solution]

What you are describing in #2 is "On Demand Application Generation" (ODAG) and it exists. It's more mature in Qlik Sense, but it's available in QlikView. I'm not up on the latest status of QDAG for QV, perhaps others can chime in on that.  Search ODAG QlikView,

Section Access or Publisher Loop and Reduce is bound to be easier

-Rob

[dselgo_eidex]

Thanks Rob. It's good to know that there is a name for this. I think in essence this is the same as what I am suggesting, but from the explanation I saw ODAG seems to be more about allowing the user to limit the data by making selections in a pre-app. For my company's purposes right now, we don't need to give the user control over that, we just need to limit it based around the data that they submitted to us.

I didn't know about Publisher Loop and Reduce, but I am going to be testing that along with Section Access and ODAG to see what is best for our needs. Thanks!

[dselgo_eidex]

One final question that I have on Section Access. Let's say that I have an extremely large data set. So large of a data set that it takes seconds to perform chart aggregations.

If I add Section Access, will that improve the performance because the data that is being aggregated over is "reduced." Or, would it be the same performance, even if I only have access to < 1% of the total data set.

I am wondering about this for our future projects in both QlikView and QlikSense.

[rwunderlich]

The performance should be improved because the aggregation is done over less data, just as if only %1 of the data was selected.

Note that in the ODAG scenarios you may have read about, the user makes selections in a pre-app and then you build an on-demand app from those selections.  There is no reason you can't build an on-demand app based on pre defined selections connected to the userid.  You don't have to let the user make or change those selections.  However I'm guessing Section Access if feasible would still be much easier.  I'm a big fan of Loop and Reduce myself.

-Rob

[dselgo_eidex]

Thanks again for clarifying Rob. This was my big reason for not wanting to use Section Access, because I thought it would still have performance issues for extremely large data sets, but if that isn't the case then I agree that Section Access is best for what I need right now.

And yeah, I realized that I didn't need a pre-app since I already had pre-defined selections to query against. It is still a very interesting concept though and there may be a time in the future where I find a reason to use it.

Loop and reduce is a very interesting function that I am glad I learned about, but it doesn't fit my company's needs right now.

Thanks again for all of the helpful advice.