none
how to speed up cubevalue

    Question

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







    • Edited by soon5 Tuesday, February 28, 2012 5:36 AM
    Monday, February 27, 2012 7:03 AM

Answers

  • 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(...),...)

    You could read this blog http://sqlblog.com/blogs/mosha/archive/2006/10/09/mdx-nonempty-exists-and-evil-nonemptycrossjoin.aspx to learn more about nonemtpy function.

    Thanks,
    Jerry

    Monday, March 05, 2012 2:46 AM
    Moderator