Qlik Community

QlikView Connectors

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Employee
Employee

Limit results with OLAP connector

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.

1 Solution

Accepted Solutions
Not applicable

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

6 Replies
c_rouse
New Contributor III

Re: Limit results with OLAP connector

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.

Employee
Employee

Re: Limit results with OLAP connector

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.

c_rouse
New Contributor III

Re: Limit results with OLAP connector

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]

Employee
Employee

Re: Limit results with OLAP connector

Ok. So you suggest substituting the "=" with a "." in the where clause.

I will try it out.

By the way, are you sure that this function pushes down the filter to be executed on the DB level or does BW bring all the results and then filters them out?

Thanks,

Makis.

Not applicable

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

Employee
Employee

Re: Limit results with OLAP connector

Crystal clear.

Thanks a lot Tomas.

I will try it out.

Makis.

Community Browser