how to speed up cubevalue

# how to speed up cubevalue

• Montag, 27. Februar 2012 07:03

May I have question.. please, help..

I am using excel 2007, and olap user..

1. Is there a difference between these? (Sometimes I see EXITS(FILTER..) )

FILTER(EXISTS ..

EXISTS(FILTER..

2. Is there way to speed up below cubevalue..?  ( I have to use below function  250 times at once, chaging  &[iii] everytime.   I guess, It takes more than 1 minite every time.. )

=N(CUBEVALUE("connection name"

,"[i].[ii].&[&iii]" ,"[t].[&tt]", "[Measures].[m]"," --- cubeset --- "))

※the Cubeset above uses below filter, in which EXISTS used.

FILTER(EXISTS([a].[aa].[aa].members
, {[a].[aaa].[q] , [a].[aaa].[w] , [a].[aaa].[C] , [a].[aaa].[e] , [a].[aaa].[f] , [a].[aaa].[m] , [a].[aaa].[n] , [a].[aaa].[t]} ) , ([b].[bb],[bbb].[bbbb].[bbbbb],[c].[cc].[ccc],[Measures].[m])>0)

• Bearbeitet Montag, 27. Februar 2012 07:10
• Bearbeitet Montag, 27. Februar 2012 07:10
• Bearbeitet Montag, 27. Februar 2012 07:16
• Bearbeitet Montag, 27. Februar 2012 07:40
• Bearbeitet Montag, 27. Februar 2012 07:42
• Bearbeitet Dienstag, 28. Februar 2012 05:36
•

### Alle Antworten

• Montag, 5. März 2012 02:46
Moderator

Hi soon,

For question 1, even the 2 calculations have different logical, but they will return same result. The different is with their performance. I think the Filter(Exists...) is better than Exists(Filter...) in performance, because the first has less cell calculation than the second within the filter function. The Exists performs auto exists which consume minimual resource, see http://msdn.microsoft.com/en-us/library/ff487119.aspx for more information about autoexists in Analysis Serivces.

For qusition 2, the filter({set},[Measures]>0) is not good. Typically, we use NonEmpty function to replace it. So, you could write the logical cacluation like this -

Exists(NonEmpty(...),...)