how to speed up cubevalue


  • 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 ..  


    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.

    , {[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 soon5 Dienstag, 28. Februar 2012 05:36
    Montag, 27. Februar 2012 07:03


  • 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 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 -


    You could read this blog to learn more about nonemtpy function.


    Montag, 5. März 2012 02:46