none
too much "Cells calculated"

    Question

  • a measures is [measures].[hold],that means is customer hold a product
    dimensions is [product] and [customer] and [counter]
    the mdx is:
    with MEMBER MEASURES.[is Hold] AS 
    Sum
    (
    [Customer]. [Customer]
    ,IIF
    (
    [Measures].[Hold] > 0
    ,1
    ,0
    )
    )
    ELSE 0
    END 
    select MEASURES.[is Hold] on 0,
    [counter].[counter].allmembers on 1
    from [cube]

    now ,use mdxstudio the perfmon window shows the Cells calculated is more then 20000000
    is looks is calculated every customer hold info,and mdx runs very slow

    is there another way to calculate the hold info?
    • Edited by bjguoguo Monday, April 21, 2014 3:10 AM
    Monday, April 21, 2014 2:45 AM

Answers

  • I'm still insisting that _in your query_ replacing [counter].[counter].allmembers on 1  with NONEMPTY {[counter].[counter].memberson 1 will give you "the result that you want" but faster, and that you don't need Allmembers, members is enough :) .... did you try it ? what did you get?!

    ----------------------------------------------------------------------------------------------------------
    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

     
    Tuesday, April 22, 2014 4:27 AM

All replies

  • First, do you really need to use AllMembers?!
    Just to remind you, AllMembers evaluates either a hierarchy or a level expression and returns a set that contains all members of the specified hierarchy or level, which includes all calculated members in the hierarchy or level. Do you have calculated members that you want to show?!

    In your query, please replace [counter].[counter].allmembers on 1
    with
    NON EMPTY {[counter].[counter].members} on 1 
    or if you don't want the [All] member try this also 
    NON EMPTY {[counter].[counter].children} on 1
    should be even faster

    ----------------------------------------------------------------------------------------------------------
    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Butmah Monday, April 21, 2014 5:29 AM
    Monday, April 21, 2014 5:28 AM
  • First, do you really need to use AllMembers?!
    Just to remind you, AllMembers evaluates either a hierarchy or a level expression and returns a set that contains all members of the specified hierarchy or level, which includes all calculated members in the hierarchy or level. Do you have calculated members that you want to show?!

    In your query, please replace [counter].[counter].allmembers on 1
    with
    NON EMPTY {[counter].[counter].members} on 1 
    or if you don't want the [All] member try this also 
    NON EMPTY {[counter].[counter].children} on 1
    should be even faster

    ----------------------------------------------------------------------------------------------------------
    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    yes,I need count the all Counter。Customers belong Counters。I need count

    each Counter's Customer hold Product info.

    Tuesday, April 22, 2014 2:09 AM
  • If you want it for only selected customers, use [Customer].[Customer].currentMember

    Saurabh Kamath

    Tuesday, April 22, 2014 2:52 AM
  • I'm still insisting that _in your query_ replacing [counter].[counter].allmembers on 1  with NONEMPTY {[counter].[counter].memberson 1 will give you "the result that you want" but faster, and that you don't need Allmembers, members is enough :) .... did you try it ? what did you get?!

    ----------------------------------------------------------------------------------------------------------
    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

     
    Tuesday, April 22, 2014 4:27 AM
  • If you want it for only selected customers, use [Customer].[Customer].currentMember

    Saurabh Kamath

    use [Customer].[Customer].currentMember can't return the correct value。

    [Count Of Cust Proddetail] is discount customer value of each counter hold product A and B in last 12 months

    [Hold1] is mark as customer hold product A or B at current month.


    Count Of Cust Proddetail  Lastmonthbal Lastmonthavg Hold1
    PCRM0160 0
    11025662 0
    11013098 0
    11008586 6 4237232.388 4000000 3( if use [Customer].[Customer].currentMember this cell will be 1)
    PBNK3454 0
    11006415 0
    PBNK0142 0
    PCRM0144 1
    11019540 0

    the relation database like this


    Counter Customer Product Hold
    11008586         001 A 0
    11008586 002 A 1
    11008586 001 B 1
    11008586 002 B 1
    11008586 001 C 0
    11008586 002 C 0



    • Edited by bjguoguo Tuesday, April 22, 2014 5:02 AM
    Tuesday, April 22, 2014 4:49 AM
  • I'm still insisting that _in your query_ replacing [counter].[counter].allmembers on 1  with NONEMPTY {[counter].[counter].memberson 1 will give you "the result that you want" but faster, and that you don't need Allmembers, members is enough :) .... did you try it ? what did you get?!

    ----------------------------------------------------------------------------------------------------------
    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

     
    Excellent,I’ll test the data。
    Tuesday, April 22, 2014 7:37 AM
  • very Interesting thing,I replace 

    IIF
    (
    [Measures].[Hold] > 0
    ,1
    ,0 

    with

    IIF
    (
    [Measures].[Hold] > 0
    ,1
    ,null

    now,I still use [counter].[counter].allmembers,is fast than before。

    can some one explan why ?

    Wednesday, April 23, 2014 3:27 AM