Best Practice Returning Large Datasets

Mon, 20 May 2013 14:55:12 GMT

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?

Tables Involved, Examples, and Query:

/////////////////////////////////////////////////////////////////////////////

The tables involved are:

CustomerValues-

UDFID*, CustomerID, UDFValue

Definitions-

UDFID*, UDFName, FieldType

(* not stable, changes with each deployment)

/////////////////////////////////////////////////////////////////////////////

Examples:

Client A (db A)

CustomerValues-

UDFID, CustomerID, UDFValue

11,111222,Mazda

Definitions-

UDFID,UDF Name, FieldType

11,Cars,String

Client B (db B)

CustomerValues-

UDFID, CustomerID, UDFValue

717,4325,3.14

Definitions-

UDFID,UDF Name, FieldType

717,PiValue,Decimal(14,2)

Query/Load Statement:

/////////////////////////////////////////////////////////////////////////////


//SQLDefinitions is a dummy table to represent the definition table in the sql database
SQLDefinitions:
SQL SELECT TOP 3 * FROM UDFDefinitions ORDER BY UDFName;
//add a record number so we can map the definations to the field names
UDFDefinitions:
Load *, RecNo() as UDFFieldID Resident SQLDefinitions;
//SQLValues is a dummy table to represent the data in the sql database
SQLValues:
SQL SELECT * FROM UDFCustomerValues;
LET vRowCount = NoOfRows('UDFDefinitions');
FOR counter = 1 to vRowCount
    LET vUDFID = Peek('UDFDefinitionID', counter -1, 'UDFDefinitions');
    LET vUDFieldId = Peek('UDFFieldID', counter -1, 'UDFDefinitions');
    LET vUDFieldName$(vUDFieldId) = Peek('UDFName', counter -1, 'UDFDefinitions');
        UDFValues$(vUDFieldId):
        NoConcatenate
        Load CustomerID, UDFValue as UDFField$(vUDFieldId) resident SQLValues where UDFDefinitionID = $(vUDFID);
NEXT
drop Tables SQLValues, SQLDefinitions;
;

topic Best Practice Returning Large Datasets in QlikView

Best Practice Returning Large Datasets