0 Replies Latest reply: May 20, 2013 10:57 AM by A Sheppard RSS

    Best Practice Returning Large Datasets

      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;
      ;