Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gmu
Former Employee
Former 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

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

View solution in original post

6 Replies
Anonymous
Not applicable

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.

gmu
Former Employee
Former Employee
Author

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.

Anonymous
Not applicable

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]

gmu
Former Employee
Former Employee
Author

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

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

gmu
Former Employee
Former Employee
Author

Crystal clear.

Thanks a lot Tomas.

I will try it out.

Makis.