6 Replies Latest reply: Sep 9, 2013 11:32 AM by Gerasimos Moulas RSS

    Limit results with OLAP connector

    Gerasimos Moulas

      Dear experts.

      I have the following statement running against an SAP infocube using the OLAP connector.

       

      [$ZCOPA_C01]:

      Load *;

      Select PseudoMDX D (

      Dimensions (

        [0COMP_CODE] (),

        [0DISTR_CHAN] (),

        [0DIVISION] (),

        [0MATERIAL] (),

        [0PLANT] (),

        [0PSTNG_DATE] ()),

      Measures (

        //[0MEASURES0000000000000000].[0INVCD9QTY000000000000000], //Sales Quantity

        [0MEASURES0000000000000000].[0G9QABSMG0000000000000000]), //Sales quantity

      From ($ZCOPA_C01)) where  [0PSTNG_DATE]='01-Jan-2013';

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

      //Drop table [$ZCOPA_C01];

       

      //*****

       

      The fact table of this infocube has 4 billion rows.

      Is there a way to alter this statement in order to read only the 1st 1,000,000 rows or put a filter on specific codes, in order to retrieve fewer results from the database and load them into Qlikview?

      It is very important to limit the results on the database level and not on QlikView.

       

      Thank you in advance for your help.

       

      Makis.

        • Re: Limit results with OLAP connector
          Clive Rouse

          Hi,

          I'm new to Qlikview but reading the Qlikview Connector manual 5.8IR, section 5.2.6, Optimising query, there are a number of suggestions of how to improve performance. Firstly the D parameter in 'Select PseudoMDX D' should be removed as this is only intended for small loads. This allows automatic data slicing using a time char or you can force it to use a specific dimension by adding a 'S' after a specific dimension, for example: [0COMP_CODE]  s (). This will then split the load by company code.

          Hope this helps.

            • Re: Limit results with OLAP connector
              Gerasimos Moulas

              Hi Cive and thank you for your response.

              I have seen these recommendations and I am planning to implement them for sure.

              My major concern (at least for now) is how I can limit the query that goes to the database. What is the actual syntax for this, because whatever I have tried does not seem to work. I am looking for something like a where clause statement that is shipped to the RDBMS level.

               

              Thanks again,

               

              Makis.

                • Re: Limit results with OLAP connector
                  Clive Rouse

                  Hi Gerasimos,

                  I'm not able to try this in Qlikview but there is a transaction: MDXTEST that you can use if you log in to SAP BW. I tried the following MDX statement in BW and it returned a result ok. So maybe you just need to change the syntax of you where clause.

                   

                  SELECT

                  [Measures].MEMBERS ON COLUMNS,

                  NON EMPTY [0CURRENCY].MEMBERS ON ROWS

                  FROM [$ZSM_C01]

                  WHERE [0PSTNG_DATE].[20130101]

              • Re: Limit results with OLAP connector

                Hi Gerasimos,

                You can use the Slices () statement in the MDX query. To take your example above you might want to split into several loads by groups of divisions and load just one Company Code:

                 

                Select PseudoMDX D (

                Dimensions (

                  [0COMP_CODE] (),

                  [0DISTR_CHAN] (),

                  [0DIVISION] (),

                  [0MATERIAL] (),

                  [0PLANT] (),

                  [0PSTNG_DATE] ()),

                Slices(

                  [[0COMP_CODE].[1000],   //Select only Company 1000

                  [0DIVISION].[01]:[0DIVISION].[10]), //Select only Division 01 - 10

                Measures (

                  //[0MEASURES0000000000000000].[0INVCD9QTY000000000000000], //Sales Quantity

                  [0MEASURES0000000000000000].[0G9QABSMG0000000000000000]), //Sales quantity

                From ($ZCOPA_C01)) where  [0PSTNG_DATE]='01-Jan-2013';

                 

                I am not sure if the where statement works with the SAP OLAP connector, but Slices() does. The values selected in Slices() are considered in the DB select statements.

                 

                BR, Thomas