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.
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.
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.
[Measures].MEMBERS ON COLUMNS,
NON EMPTY [0CURRENCY].MEMBERS ON ROWS
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 (
[[0COMP_CODE]., //Select only Company 1000
[0DIVISION].:[0DIVISION].), //Select only Division 01 - 10
//[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.