4 Replies Latest reply: Dec 20, 2012 1:00 PM by s j RSS

    OLAP Delta Load

      Hello community,

      I´m trying to use the OLAP Delta load on the basis of Mr. Wahlstedts Tutorial. But it fails at the point I´ve marked red. I replaced the PseudoMDX with my Statements... I also made sure that the User preferences allows this. What is wrong? Can you please help me.

       

      ///$tab Info

      // ============================================

      //  This application demonstrates how to

      //  handle inceremental load using

      //    QlikView's OLAPConnecor.

      //     The only things you have to change are the

      //  PseudoMDX statements on tabs

      //      - define which IDs to load

      //      - --->LOAD CUBE<---

      //    

      //    Make sure that Request ID is available

      //  in every used Query!

      //

      //  (C) 2008, QlikTech AB

      // ============================================

      ///$tab Main

      SET ThousandSep=' ';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='£#,##0.00;-£#,##0.00';

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='DD.MM.YYYY';

      SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

       

      // enter your SAP-Access here:

      CUSTOM CONNECT TO "Provider=QvSAPOLAPConnector.dll;ASHOST=10.55.11.53;SYSNR=00;CLIENT=100;Log=1;XUserId=cIBPabVMCLZEHWFZ;XPassword=YBcbAGdKBbSIDbQGWBWA;";

       

       

       

      ///$tab parameters full load

      //=================== FULL LOAD ============================

      If vFullLoad = 'yes' Then

       

      trace =====================================;

      trace ==========! FULL LOAD !==============;

      trace  delete all existing [RequestID].QVD ;

      //trace          is a good idea :-)               ;

      trace =====================================;

      sleep 1000;

       

      // load empty ID-Table

      LoadedIDs:

          Load * Inline [

              LoadedID

          ];

       

      // no slicing needed for full load:

      set vComment = '//';

       

      Else

      ///$tab parameters delta load

      // ================= DELTA LOAD ============================

      // with slicing

      set vComment = '';

       

      // load allready loaded IDs from history

      LoadedIDs:

          LOAD

              LoadedID

          FROM LoadedIDs.qvd (qvd);

       

      End if

       

      ///$tab define which IDs to load

      // =================================================================

      // REPLACE WITH YOUR MDX STATEMENT!!!

      //==================================================================

       

      [$ZAUFMON]:

      Load *;

      Select PseudoMDX (

      Dimensions (

        [0CREATEDON] (),

        [0DISTR_CHAN] (),

        [0DIVISION] (),

        [0DLV_STS] (),

        [0INFOPROV] (),

        [0MATERIAL] (),

        [0PLANT] (),

        [0SALESORG] (),

        [0SOLD_TO] (),

        [0STS_DLV_C] (),

        [0DOC_CURRCY] (),

        [0SALES_UNIT] (),

        [0CALMONTH] ()),

        Slices ([0CALMONTH].[201201],[0CALMONTH].[201202],[0CALMONTH].[201203],[0CALMONTH].[201204],[0CALMONTH].[201205],[0CALMONTH].[201206],[0CALMONTH].[201207],[0CALMONTH].[201208],[0CALMONTH].[201209],[0CALMONTH].[201210],[0CALMONTH].[201211],[0CALMONTH].[201212]),

      Measures (

        [0MEASURES0000000000000000].[0ORDERS000000000000000000], //Anzahl Aufträge

        [0MEASURES0000000000000000].[0DOC9ITEMS000000000000000], //Anzahl Belegpositionen

        [0MEASURES0000000000000000].[1ROWCOUNT0000000000000000], //Anzahl Sätze

        [0MEASURES0000000000000000].[0ICOASREQ0000000000000000], //Bestätigt wie gewünscht

        [0MEASURES0000000000000000].[0DLV924000000000000000000], //Lieferung innerhalb von 24 Stunden

        [0MEASURES0000000000000000].[0QCOASREQ0000000000000000], //Menge bestätigt wie gewünscht

        [0MEASURES0000000000000000].[0QTY9RET00000000000000000], //Mengen Retouren

        [0MEASURES0000000000000000].[0NET9VALUE000000000000000], //Nettowert der Auftragsposition in Belegwährung

        [0MEASURES0000000000000000].[0NET9VAL9HD00000000000000], //Nettowert des Auftrags in Belegwährung

        [0MEASURES0000000000000000].[0DLVVPOF00000000000000000], //Perfect Order Fulfillment (Wert)

        [0MEASURES0000000000000000].[0DLVIPOF00000000000000000], //Perfect Order Fulfillment(Anzahl)

        [0MEASURES0000000000000000].[0DLV9QTY00000000000000000], //Tatsächlich gelieferte Menge in VME

        [0MEASURES0000000000000000].[0DLV9VAL00000000000000000], //Tatsächlich gelieferter Wert

        [0MEASURES0000000000000000].[0VCOASREQ0000000000000000], //Wert bestätigt wie gewünscht

        [0MEASURES0000000000000000].[0VAL924000000000000000000], //Wert mit Lieferung innerhalb von 24 Stunden

        [0MEASURES0000000000000000].[0REQU9VAL0000000000000000], //Wunschlieferwert

        [0MEASURES0000000000000000].[0DLVILESC0000000000000000], //Zu spät geliefert nach Einteilungsdatum

        [0MEASURES0000000000000000].[0DLVILECR0000000000000000], //Zu spät geliefert nach Wunschlieferdatum

        [0MEASURES0000000000000000].[0DLVQLESC0000000000000000], //Zu spät gelieferte Menge nach Einteilungsdatum

        [0MEASURES0000000000000000].[0DLVQLECR0000000000000000], //Zu spät gelieferte Menge nach Wunschlieferdatum

        [0MEASURES0000000000000000].[0DLVVLESC0000000000000000], //Zu spät gelieferter Wert nach Einteilungsdatum

        [0MEASURES0000000000000000].[0DLVVLECR0000000000000000]), //Zu spät gelieferter Wert nach Wunschlieferdatum

      From ($ZAUFMON));

      Store * from [$ZAUFMON] into $Full.QVD;

       

      // read all RequestIDs, including ALL Measures:

      ID: IT FAILS HERE!

      Select PseudoMDX (

      Dimensions (

        [0REQUID] ()),

      from ($ZAUFMON));

      Store * From [$ZAUFMON] into $LOADEDIDs.qvd;

       

      // and keep just the new ones:

      NewIDs:

      Load

          [Request ID - Request ID Level 01 (Text)] as NewIDCaption,

          [Request ID - Request ID Level 01 (Key)] as NewIDUnique

      Resident

          ID

      Where

          NOT Exists(LoadedID, [Request ID - Request ID Level 01 (Text)]);

       

      Drop table ID;

      ///$tab define loop for each Request ID

      Cube:

       

      // loop through all Request IDs:

      Let vNoOfRows = NoOfRows('NewIDs');

       

      // full load -> no loop needed:

      If vFullLoad = 'yes' Then

          set  vNoOfRows = '1';

      endif

       

      For i = 1 to vNoOfRows

          Let vLoadID = Peek('NewIDUnique', $(i)-1, 'NewIDs');

         

          // name of the QVD file:

          Let vStoreID = Peek('NewIDCaption', $(i)-1, 'NewIDs') & '.qvd';

       

      ///$tab ---> LOAD CUBE <---

      // =================================================================

      // REPLACE WITH YOUR MDX STATEMENT!!!

      //==================================================================

       

      [$ZAUFMON]:

      Load *;

      Select PseudoMDX (

      Dimensions (

        [0REQUID] ()

        [0CREATEDON] (),

        [0DISTR_CHAN] (),

        [0DIVISION] (),

        [0DLV_STS] (),

        [0INFOPROV] (),

        [0MATERIAL] (),

        [0PLANT] (),

        [0SALESORG] (),

        [0SOLD_TO] (),

        [0STS_DLV_C] (),

        [0DOC_CURRCY] (),

        [0SALES_UNIT] (),

        [0CALMONTH] ()),

        Slices ([0CALMONTH].[201201],[0CALMONTH].[201202],[0CALMONTH].[201203],[0CALMONTH].[201204],[0CALMONTH].[201205],[0CALMONTH].[201206],[0CALMONTH].[201207],[0CALMONTH].[201208],[0CALMONTH].[201209],[0CALMONTH].[201210],[0CALMONTH].[201211],[0CALMONTH].[201212]),

      Measures (

        [0MEASURES0000000000000000].[0ORDERS000000000000000000], //Anzahl Aufträge

        [0MEASURES0000000000000000].[0DOC9ITEMS000000000000000], //Anzahl Belegpositionen

        [0MEASURES0000000000000000].[1ROWCOUNT0000000000000000], //Anzahl Sätze

        [0MEASURES0000000000000000].[0ICOASREQ0000000000000000], //Bestätigt wie gewünscht

        [0MEASURES0000000000000000].[0DLV924000000000000000000], //Lieferung innerhalb von 24 Stunden

        [0MEASURES0000000000000000].[0QCOASREQ0000000000000000], //Menge bestätigt wie gewünscht

        [0MEASURES0000000000000000].[0QTY9RET00000000000000000], //Mengen Retouren

        [0MEASURES0000000000000000].[0NET9VALUE000000000000000], //Nettowert der Auftragsposition in Belegwährung

        [0MEASURES0000000000000000].[0NET9VAL9HD00000000000000], //Nettowert des Auftrags in Belegwährung

        [0MEASURES0000000000000000].[0DLVVPOF00000000000000000], //Perfect Order Fulfillment (Wert)

        [0MEASURES0000000000000000].[0DLVIPOF00000000000000000], //Perfect Order Fulfillment(Anzahl)

        [0MEASURES0000000000000000].[0DLV9QTY00000000000000000], //Tatsächlich gelieferte Menge in VME

        [0MEASURES0000000000000000].[0DLV9VAL00000000000000000], //Tatsächlich gelieferter Wert

        [0MEASURES0000000000000000].[0VCOASREQ0000000000000000], //Wert bestätigt wie gewünscht

        [0MEASURES0000000000000000].[0VAL924000000000000000000], //Wert mit Lieferung innerhalb von 24 Stunden

        [0MEASURES0000000000000000].[0REQU9VAL0000000000000000], //Wunschlieferwert

        [0MEASURES0000000000000000].[0DLVILESC0000000000000000], //Zu spät geliefert nach Einteilungsdatum

        [0MEASURES0000000000000000].[0DLVILECR0000000000000000], //Zu spät geliefert nach Wunschlieferdatum

        [0MEASURES0000000000000000].[0DLVQLESC0000000000000000], //Zu spät gelieferte Menge nach Einteilungsdatum

        [0MEASURES0000000000000000].[0DLVQLECR0000000000000000], //Zu spät gelieferte Menge nach Wunschlieferdatum

        [0MEASURES0000000000000000].[0DLVVLESC0000000000000000], //Zu spät gelieferter Wert nach Einteilungsdatum

        [0MEASURES0000000000000000].[0DLVVLECR0000000000000000]), //Zu spät gelieferter Wert nach Wunschlieferdatum

      From ($ZAUFMON));

      Store * from [$ZAUFMON] into $ZAUFMON.QVD;

       

       

      // slice only if delta load:

      $(vComment)    Slices ($(vLoadID)),

          

           From ($ZAUFMON));

       

       

      ///$tab store cube

      // =============================================

      //                     store the cube

      // =============================================

       

      // create a subfolder for QVDs:

      execute cmd /c mkdir Data;

       

       

      // store QVDs:

      if vFullLoad = 'yes' then

          Store * from Cube into ./Data/Full.qvd;

      else

          Store * from Cube into ./Data/$(vStoreID);

      endif

       

      // clear RAM:

      drop table Cube;

       

      // load next slice:

          next

       

      ///$tab store IDs

      // add the previously loaded IDs   

      Concatenate (LoadedIDs)

      Load

          NewIDCaption as LoadedID

      Resident

          NewIDs;

       

      // and store the actual list of loaded Request IDs   

      Store LoadedIDs into LoadedIDs.qvd;

       

      // not used anymore, since the IDs are stored into table LoadedID

      drop table NewIDs;

       

      What is wrong with that?

      Please help me!

        • Re: OLAP Delta Load

          I will be out of the office starting  12/11/2012 and will not return until

          14/11/2012.

           

          For issues please use the SURS:

          http://dedarhubs01.eu.ad.vwr.com/application/is/surs.nsf/SuperUsersWeb?OpenView

           

           

          In case of emergencies please contact Vincent Davin.

           

          Thank you,

          Kathleen

          • Re: OLAP Delta Load

            Just wondering, why dont you use the extractor connector or alternatively pure sql? The olap connector is for my understanding, not a good way, performance issues and not a good architecture.

             

            Sent from my Android phone using TouchDown (www.nitrodesk.com)

            QlikCommunity<http://community.qlik.com/index.jspa>

             

            OLAP Delta Load

            erstellt von l.weber0815<http://community.qlik.com/people/l.weber0815> in SAP - vollen Thread anzeigen<http://community.qlik.com/message/280650#280650>

              • Re: OLAP Delta Load

                I marked the line, that gives the error.

                 

                ///$tab Info

                // ============================================

                //  This application demonstrates how to

                //  handle inceremental load using

                //    QlikView's OLAPConnecor.

                //     The only things you have to change are the

                //  PseudoMDX statements on tabs

                //      - define which IDs to load

                //      - --->LOAD CUBE<---

                //    

                //    Make sure that Request ID is available

                //  in every used Query!

                //

                //  (C) 2008, QlikTech AB

                // ============================================

                ///$tab Main

                SET ThousandSep=' ';

                SET DecimalSep='.';

                SET MoneyThousandSep=',';

                SET MoneyDecimalSep='.';

                SET MoneyFormat='£#,##0.00;-£#,##0.00';

                SET TimeFormat='hh:mm:ss';

                SET DateFormat='DD/MM/YYYY';

                SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                 

                // enter your SAP-Access here:

                CUSTOM CONNECT TO "Provider=QvSAPOLAPConnector.dll;ASHOST=10.55.11.53;SYSNR=00;CLIENT=100;Log=1;XUserId=dHbGSbVMCLZEHWVB;XPassword=AOBXFGdKBbSIDbQGWZQB;";

                 

                ///$tab parameters full load

                //=================== FULL LOAD ============================

                If vFullLoad = 'yes' Then

                 

                trace =====================================;

                trace ==========! FULL LOAD !==============;

                trace  delete all existing [RequestID].QVD ;

                trace =====================================;

                sleep 1000;

                 

                // load empty ID-Table

                LoadedIDs:

                    Load * Inline [LoadedID];

                 

                // no slicing needed for full load:

                set vComment = '//';

                 

                Else

                ///$tab parameters delta load

                // ================= DELTA LOAD ============================

                // with slicing

                set vComment = '';

                 

                // load allready loaded IDs from history

                LoadedIDs:

                    LOAD

                        [LoadedID]

                    FROM LoadedIDs.qvd (qvd);

                 

                End if

                 

                ///$tab define which IDs to load

                // =================================================================

                // REPLACE WITH YOUR MDX STATEMENT!!!

                //==================================================================

                 

                // read all RequestIDs, including ALL Measures:

                 

                ID:

                 

                Load *;

                Select PseudoMDX (

                 

                Dimensions (

                  [0REQUID] ()),

                Measures (

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6K7POHGWTLJUCN989BEZNX], //KUZU>0?

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6KISXJM0NIHI5DNX1TBTHP], //Kundenzufriedenheit

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6I3WIO4GXZQ2IUFMIE9C0D], //Kundenzufriedenheit Menge

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6I3WIO4FEMHF07CRA9EJGT], //Liefertreue Auftragsmenge WLD

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6I3WIO4G6B3QRIW6WBTXQL], //Retourenquote

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6I3WIO4EMXV38VTBO6Z571]), //Servicegrad Mengen 

                // Slices (

                //  [0CALMONTH].[201201],

                //  [0CALMONTH].[201202],

                //  [0CALMONTH].[201203],

                //  [0CALMONTH].[201204],

                //  [0CALMONTH].[201205],

                //  [0CALMONTH].[201206],

                //  [0CALMONTH].[201207],

                //  [0CALMONTH].[201208],

                //  [0CALMONTH].[201209],

                //  [0CALMONTH].[201210]),

                From (ZAUFMON/ZAUFKUZU));

                 

                 

                // and keep just the new ones:

                Here is the Problem: field not found <Request ID - Request ID Level 01 (TEXT)>

                NewIDs:

                Load

                    [Request ID - Request ID Level 01 (Text)] as NewIDCaption,

                    [Request ID - Request ID Level 01 (Key)] as NewIDUnique

                Resident

                    ID

                 

                Where

                    NOT Exists ([LoadedID], [Request ID - Request ID Level 01 (Text)]);

                   

                DROP Table ID;

                 

                 

                 

                 

                ///$tab define loop for each Request ID

                 

                Cube:

                Load*;

                // loop through all Request IDs:

                vNoOfRows = NoOfRows('NewIDs');

                 

                // full load -> no loop needed:

                If vFullLoad = 'yes' Then

                    set  vNoOfRows = '1';

                endif

                 

                For i = 1 to vNoOfRows

                vLoadID = Peek('NewIDUnique', $(i)-1, 'NewIDs');

                   

                    // name of the QVD file:

                vStoreID = Peek('NewIDCaption', $(i)-1, 'NewIDs') & '.qvd';

                 

                ///$tab ---> LOAD CUBE <---

                // =================================================================

                // REPLACE WITH YOUR MDX STATEMENT!!!

                //==================================================================

                // 0REQUID has to be one of the dimension!

                       

                [ZAUFMON/ZAUFKUZU]:

                Load *;

                Select PseudoMDX (

                Dimensions (

                  [0REQUID] (),

                  [0DISTR_CHAN] (),

                  [0DIVISION] (),

                  [0MATERIAL] (),

                  [0PLANT] (),

                  [0SALESORG] (),

                  [0SOLD_TO] (),

                  [0CALMONTH] ()),

                Measures (

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6K7POHGWTLJUCN989BEZNX], //KUZU>0?

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6KISXJM0NIHI5DNX1TBTHP], //Kundenzufriedenheit

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6I3WIO4GXZQ2IUFMIE9C0D], //Kundenzufriedenheit Menge

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6I3WIO4FEMHF07CRA9EJGT], //Liefertreue Auftragsmenge WLD

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6I3WIO4G6B3QRIW6WBTXQL], //Retourenquote

                  [DDV6I3WIO4DV98RHK9W24JQX9].[DDV6I3WIO4EMXV38VTBO6Z571]), //Servicegrad Mengen 

                // Slices (

                //  [0CALMONTH].[201201],

                //  [0CALMONTH].[201202],

                //  [0CALMONTH].[201203],

                //  [0CALMONTH].[201204],

                //  [0CALMONTH].[201205],

                //  [0CALMONTH].[201206],

                //  [0CALMONTH].[201207],

                //  [0CALMONTH].[201208],

                //  [0CALMONTH].[201209],

                //  [0CALMONTH].[201210]),

                From (ZAUFMON/ZAUFKUZU));

                Store * from [ZAUFMON/ZAUFKUZU] into .\Data\Full.qvd;

                 

                // slice only if delta load:

                //$(vComment)    Slices ($(vLoadID))

                    

                //     From (ZAUFMON/ZAUFKUZU);

                 

                 

                ///$tab store cube

                // =============================================

                //                     store the cube

                // =============================================

                 

                // create a subfolder for QVDs:

                execute cmd /c mkdir Data;

                 

                 

                // store QVDs:

                if vFullLoad = 'yes' then

                    Store * from Cube into .\Data\Full.qvd;

                else

                    Store * from Cube into .\Data\$(vStoreID);

                endif

                 

                // clear RAM:

                drop table Cube;

                 

                // load next slice:

                    next

                 

                ///$tab store IDs

                // add the previously loaded IDs   

                Concatenate (LoadedIDs)

                Load

                    NewIDCaption as LoadedID

                Resident

                    NewIDs;

                 

                // and store the actual list of loaded Request IDs   

                Store LoadedIDs into LoadedIDs.qvd;

                 

                // not used anymore, since the IDs are stored into table LoadedID

                drop table NewIDs;

                 

                What is wrong?

              • Re: OLAP Delta Load
                s j

                I will not suggest this way to extract delta load from cube.

                 

                if data archived i.e convert from F to E . this logic will not work.

                 

                it is better to to extract delta load of fact and dimension table seperately from cube.