How to speed up excel report generation if using custom conditional formatting rules
We are using QlikView and Qlik NPrinting to generate Microsoft Excel spreadsheets. Our client wants to apply custom conditional formatting rules. But doing so, severely slows down report generation. And by severely, I mean the duration went up from a couple minutes to a couple hours.
I thought, wrongly, that Microsoft Excel's limitations are at the heart of the problem, and they need to fix their product. I have outlined my argumentation in this article:
But in the meantime I'm looking for a way to speed up report generation that allows me to continue to use custom conditional formatting rules.
We have 1 dedicated Qlik server and 1 separate dedicated NPrinting server. The Qlik document reloads data in like 3 seconds from a qvd that reloads in about 2.5 minutes. It only holds the data needed for NPrinting: no further filtering or cycling is needed or applied. There is no section access, no data reduction, and no triggers. The NPrinting server successfully caches the document's metadata from the Qlik server using the qvp protocol.
I'm in full control of every part of the documents, installation, and configuration.
I've shared other steps which precede that option that 'might' solve the issue.
One last point. it could be possible that you have an antivirus running that does not exclude key NPrinting folders. So ensure the exclude those going forward to mitigate potential file corruption in the case that they currently are not excluded.
Removing the module password and removing superfluous sheet objects and variables seems to not have had any impact. NPrinting has been attempting to generate reports now, for 10 hours. Not failing, either.
Let me attach the latest template and its QVW file. You can't reload it as you don't have the QVD files that are its source. Feel free to remove the files as soon as you copy them.
Yes, we have merged fields. I noticed the problem with merged cells a few years ago. So instead of having NPrinting write directly into them, I have it write into an adjacent cell, and then have Excel copy the contents of that into the merged cells. That has worked quite nicely.
We host the source QVD files on a shared server. Would that contribute to slow report generation? Not that I've seen that in the past. But just food for thought.
So here's what we do:
1. QMC background job reloads a loader.qvw on schedule. It pulls information in from our ERP, and stores that in a handful of QVDs on our file share. The file share is a dedicated server with a huge performance ability for I/O.
2. Small child QVWs have been created that load parts of the data from the QVD files filled by the loader process. Those child QVW have been stripped of anything not needed by its NPrinting report. They reload automatically upon successful reload of the loader process.
3. NPrinting accesses those small child QVWs through the Qlik server, using the QVP protocol. So most heavy lifting is performed on the Qlik server.
That's best practice, isn't it? Am I missing something?
1. Instead of accessing the QVW from the QlikApp server, I copied it locally to the NPrinting server. Reading connections and metadata cache from there.
2. I removed excess levels from within the report. 5 Levels that now are addressed by producing the small dedicated QVW files, and 1 that is addressed by report cycling.
Together, that reduced the report generation from over 12 HOURS to just a few minutes. Including the conditional formatting rules for Excel.
Next: revert point 1 as to see whether point 2 works just as well on its own.
NPrinting connects only to the QMC reloaded QVW only. It does not connect to the QVDs behind the scenes.
I was able to import your report now with the latest QVW and report template.
I am running tests now.
Do you have any conditions or filters directly on the report or on publish task?
Further, I noticed in a chart that was collapsed that you are accessing files located on the network location.
Does the NPrinting Service account have access to that folder and does your report included those images? If yes, then the NP service account needs full shared and security (folder and NTFS file access) to that networked file location.
Processing images from a network location can be fraught with performance issues. (I won't be able to complete successful testing likely as my service account will most certainly not have access to those networked folders. But I will let the publish task with your report keep running in any case on my end so I can see any pertinent logging on this end. (the chart ID in the QVW provided that contains the network paths is StylesAndImages). This might have some impact on the issue.
Yes I also notice the heavy use of levels. That too will impact performance.
Kind regards...
Please remember hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
The ridiculousness of #1 is that with one of our other dashboards, hosting the QVW file on the NPrinting server completely maxes out CPU and memory when regenerating the metadata cache. The difference is that that is a large dashboard with a huge amount of data, charts, and calculated dimensions, which all must be cached. I have a support case here in which the advice is, to break up the big dashboard and make small, dedicated QVWs, dedicated to the report at hand. So when I did that with the reports that lead to our current discussion, I was absolutely baffled to see that report generation was taking hours, with no progress. (Literally. It went from taking minutes to a few hours, then to over 12 hours with no progress.)
Yes, I have removed several levels and that appears to have helped. Running a test to see whether other factors also play a role.
Yes, the NPrinting service account does have access to the images folder, but no, the images themselves are not put into the report by NPrinting. NPrinting just places the link to the images, and then a self-authored Excel VBA macro converts those into the actual images. Every client has access to the images.
