4 Replies Latest reply: Oct 30, 2017 5:04 AM by Saimeenakshi Sankar RSS

    QVD: Two DB`s same table structures

    Saimeenakshi Sankar

      Dear all,

       

      I am developing an app using QDF. My application involves tables of data from Two different databases, however the tables structures, table names and field names are similar. I need to create QVDs with the data with both the databases, concatenate the data to create KPIs and charts. Kindly suggest the ideal approach to do the same.

       

      Thanks in advance!

      Regards,
      Saimeenakshi S

        • Re: QVD: Two DB`s same table structures
          Damian Waldron

          i would create an extraction and transformation layer Qvd generator. The extraction layer retrieves the data it is raw format. The transformation layer then applies business rules and ensures column names are consistent.

          • Re: QVD: Two DB`s same table structures
            An Pham

            Hi

            You can merger two table as same struct from two data source into one QVD file by using Concatenate keyword

            Script like

            
            
            LIB CONNECT TO  Datasource1;
            
            
            [QVDTable]:
            load *;
            SQL Select * from Datasource1.Tablename;
            
            
            
            LIB CONNECT TO  Datasource2;
            
            
            Concatenate
            
            
            [QVDTable]:
            load *;
            SQL Select * from Datasource2.Tablename;
            
            
            Store * from  QVDTable into 'QvdPath\QVDTable.QVD';
            
            



            • Re: QVD: Two DB`s same table structures
              Saimeenakshi Sankar

              hi,

              1. I have created qvds for both the db`s in same qvw application with (say QVD_DB2 and QVD_DB2)
              2. placed it in the extract folder of my QVDPath (business rules or data filter is part of the sql query where clause hence no separate transform step added here)
              3. stored it in Load folder of my QVDPath for presentation.
              4. Then created a new QVW application and loaded the Load folder QVD`s by concatenate keyword. however the record count doesn't seems to match.

               

              Purpose of me doing this way is to have separate count of records from both DB`s for creation of visualizations.

              Kindly advice if I am doing anything not right here?

              • Re: QVD: Two DB`s same table structures
                Saimeenakshi Sankar

                My piece of code is given below:

                DB1:
                //**********************CUSTOMER ORDER Table QVD Extract*******************************//
                CUSTOMER_ORDER_EU:
                SQL SELECT
                "BU_CODE",
                "BU_TYPE",
                "CUSORD_NO",
                "CUSORD_DATE",
                "CUSORD_STAT",
                "CTY_CODE",
                "USER_ID_REF",
                "BU_CODE_SEND",
                "BU_TYPE_SEND",
                "UPD_DATE"
                FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TRUNC(CUSORD_DATE)>=TRUNC(SYSDATE);
                STORE CUSTOMER_ORDER_EU into $(vG.QVDPath)1.Extract\CUST_ORDER_EU.qvd(qvd);
                Drop Table CUSTOMER_ORDER_EU;

                //***********************CUSTOMER ORDER Table QVD LOAD*********************************//
                CUSTOMER_ORDER_EU:
                LOAD
                "BU_CODE",
                "BU_TYPE",
                "CUSORD_NO",
                "CUSORD_DATE",
                "CUSORD_STAT",
                "CTY_CODE",
                "USER_ID_REF",
                "BU_CODE_SEND",
                "BU_TYPE_SEND",
                "UPD_DATE"
                FROM [$(vG.QVDPath)1.Extract\CUST_ORDER_EU.qvd](qvd);
                STORE CUSTOMER_ORDER_EU into $(vG.QVDPath)3.Load\CUST_ORDER_EU.qvd(qvd);
                Drop Table CUSTOMER_ORDER_EU;

                 

                DB2:
                //**********************CUSTOMER ORDER Table QVD Extract*******************************//
                CUSTOMER_ORDER_EUX:
                SQL SELECT
                "BU_CODE",
                "BU_TYPE",
                "CUSORD_NO",
                "CUSORD_DATE",
                "CUSORD_STAT",
                "CTY_CODE",
                "USER_ID_REF",
                "BU_CODE_SEND",
                "BU_TYPE_SEND",
                "UPD_DATE"
                FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TRUNC(CUSORD_DATE)>=TRUNC(SYSDATE);
                STORE CUSTOMER_ORDER_EUX into $(vG.QVDPath)1.Extract\CUST_ORDER_EUX.qvd(qvd);
                Drop Table CUSTOMER_ORDER_EUX;

                //***********************CUSTOMER ORDER Table QVD LOAD*********************************//
                CUSTOMER_ORDER_EUX:
                LOAD
                "BU_CODE",
                "BU_TYPE",
                "CUSORD_NO",
                "CUSORD_DATE",
                "CUSORD_STAT",
                "CTY_CODE",
                "USER_ID_REF",
                "BU_CODE_SEND",
                "BU_TYPE_SEND",
                "UPD_DATE"
                FROM [$(vG.QVDPath)1.Extract\CUST_ORDER_EUX.qvd](qvd);
                STORE CUSTOMER_ORDER_EUX into $(vG.QVDPath)3.Load\CUST_ORDER_EUX.qvd(qvd);
                Drop Table CUSTOMER_ORDER_EUX;

                 

                QVD concatenate in end user qvw application:

                CUSTOMER_ORDER_EU:
                LOAD
                "BU_CODE",
                "BU_TYPE",
                "CUSORD_NO",
                "CUSORD_DATE",
                "CUSORD_STAT",
                "CTY_CODE",
                "USER_ID_REF",
                "BU_CODE_SEND",
                "BU_TYPE_SEND",
                "UPD_DATE"
                FROM [$(vG.QVDPath)3.Load\CUST_ORDER_EU.qvd](qvd);
                Concatenate
                CUSTOMER_ORDER_EUX:
                LOAD
                "BU_CODE",
                "BU_TYPE",
                "CUSORD_NO",
                "CUSORD_DATE",
                "CUSORD_STAT",
                "CTY_CODE",
                "USER_ID_REF",
                "BU_CODE_SEND",
                "BU_TYPE_SEND",
                "UPD_DATE"
                FROM [$(vG.QVDPath)3.Load\CUST_ORDER_EUX.qvd](qvd);