I need a recommendation how to build report processing system that would scale to 10,000+ account reports. The requirements are following: there are 10,000 account with history going back 1 year. An average each account has 400,000 historic records but some accounts have as many as 20mln records. Reports are accessed through our Web portal using AJAX client to QV. Data in reports should be relatively fresh, so the report should be no older than 1 hour. All reports share the same design. Many new accounts are created on daily basis.
What we have done so far:
Data Preparation: segment all data to AccountId_Day text files. Convert each to QVD using QVE.EXE /vAccountId=12345. Combine all but last 2 days to AccountId_historic.QVD. Generate AccountId.QVW using QVE.EXE based on some Master.QVW that has the layout,
Every time any of the source text files has changed, we regenerate daily AccountId_Day.QVD, AccountId_Historic.QVD, AccountId.QVW. Depending on application this is done either by automatically or by request from portal/user.
Now, while it does work, this solution is not stable. We have it in production just for few of our customers and at least twice a week we have to manually intervene to "push" the flow or reload the box. Issues:
- Does not matter what flags we use to suppress error message in QVE.EXE, it still in certain scenarios shows modal popup dialog box. LOAD FAILED. And QVE.EXE process becomes frozen.
- Any small temporary blip in network - QVE is failing with no good message why. In other cases we have half-baked QVD files with not all the data processed
- We ended up building our processing framework to ensure that Qlikview boxes are evenly loaded, bit not overloaded. We really don't want to be in this business
- We would love to use QV Publisher to manage this and trigger reloads using EDX but we (and Qliktech so far) was not able to show us how to use QVPublisher to refresh a QVW without creating a task for each individual QVW. (10,000, new accounts are added every day through our portal app). We cannot pass a variable to EDX that would specify AccountId the way we do it with QVE.EXE. Also EDX is an asynchronous service, I cannot get success/error and error code using it.
- Most of the processing time (90+%) goes to TXT->QVD conversion. If there would be an utility just to convert text file to QVD, we could have offloaded this simple process to a cluster of machines and process it much faster without dealing with QVE.EXE or QVPublisher
We did try to get help from Qliktech on different levels, but 18 months later no much progress. I would love to hear from other people who either run into the same architectural issues or, even better, were able to resolve them.