My company offers QlikView reports through an iframe in a web app running in SAAS. The QlikView QVW is housed on a QlikView Server, then picked up by a QlikView Publisher, translated to html then ported to the site. Recently I got a assignment from the owner of my company to make our standard reports include what we call UDFs (User-Defined Fields) so that each client (which resides in a different db) can have any or all of their UDFs included in a existing report without it being different from client to client or db to db. Also each client has a variable number of UDFs as they are unlimited, and the UDFID is not a stable field; rather it changes with each deployment.
The query I am using now has mitigated much of the difficulties (below). However, the query is still slow (return is approx 1:30 for only 8 udfs; more than 20 takes 45:00+). I attribute much of the slowness to the amount of data needed to be returned. In this case, the rows returned number over 6 million rows. The reason for the large return is that the client has approximately 325,000 customers with each having a variable amount of UDFs out of the 57 total UDFs possible. The report should be able to return any or all of the UDFs for each client in each report as they want the reports to appear to be ad-hoc.
My DBA has asked if a Data Warehouse would be beneficial and to diagram what the architecture would look like.
Would a Data Warehouse be beneficial?
How would getting records from two sources then linking them in a significant way to a single customer work in QlikView?
Is there a better way to accomplish what the project requires?