none
Testing calculated members in cube browser RRS feed

  • Question

  • Dear SQL,

    I am new to MDX and cubes as a whole so pardon my question. I am trying to add a distinctcount of orderid's so that I can see the orders per customer. The orderid's are in a supplier dimension where the customers are in a seperate dimension. 

    When I add a measure directly in the cube structure, everything works fine and I am able to see the amount of orderid's per customer. However, when I go and add a calculated member, with DISTINCTCOUNT ( [Suppliers.OrderID.Members] ) it does not work. 

    What is the difference in the calculation which Visual Studio generates and mine? 

    Kind regards,

    Michiel

    Wednesday, August 14, 2019 3:20 PM

Answers

  • It seems like the calculated member does not take the relationships into account in some way, as it applies correctly within the same dimension of the orderid's themselves. Do you have any other suggestions? Is there more info i can provide? 

    I'd need to see what your attribute relationships look like and what sort of test query you are running in order to figure out what's going on here. However if the cube based measure works why not just use that? It's working and will be at least 10 times faster than any equivalent calculated measure.

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Michiel- Thursday, August 15, 2019 10:18 AM
    Thursday, August 15, 2019 9:46 AM
    Moderator

All replies

  • When you build the measure into the cube there are actually new storage structures generated inside the cube to support the measure. A distinct count defined in an MDX cube will always outperform a similar calculated measure.

    It would help if you were clearer on what you meant by "it does not work" but I'm assuming that the issue is that the total distinct count is repeated due to the fact that you are just counting all the members of the OrderID attribute. If you use the EXISTING keyword it will filter down the OrderID's to just those that exist in the current filter context. 

    DISTINCTCOUNT ( EXISTING [Suppliers.OrderID.Members] )


    http://darren.gosbell.com - please mark correct answers

    Wednesday, August 14, 2019 9:51 PM
    Moderator
  • Hi Michiel,

    Thanks for your question.

    Per your description, I'm not clear what you mean. Because we don't have structure of these dimension tables that you mentioned, also, we don't know the meaning of the sentence "with DISTINCTCOUNT ( [Suppliers.OrderID.Members] ) it does not work."

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 15, 2019 2:51 AM
  • Hi Darren,

    Thanks for your reply. 

    I have tried applying your definition and then did a full process. The calculated member still returns the same total order count for every customer. The measure that looks directly at the view in a SQL database returns the correct number. 

    It seems like the calculated member does not take the relationships into account in some way, as it applies correctly within the same dimension of the orderid's themselves. Do you have any other suggestions? Is there more info i can provide? 

    Kind regards,

    Michiel


    • Edited by Michiel- Thursday, August 15, 2019 9:21 AM
    Thursday, August 15, 2019 9:20 AM
  • It seems like the calculated member does not take the relationships into account in some way, as it applies correctly within the same dimension of the orderid's themselves. Do you have any other suggestions? Is there more info i can provide? 

    I'd need to see what your attribute relationships look like and what sort of test query you are running in order to figure out what's going on here. However if the cube based measure works why not just use that? It's working and will be at least 10 times faster than any equivalent calculated measure.

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Michiel- Thursday, August 15, 2019 10:18 AM
    Thursday, August 15, 2019 9:46 AM
    Moderator
  • Thank you for your replies. Will do that. There is no way to create a display folder in a measure group without using calculated members right? The groups can get quit crowded when only using cube base measures. 
    Thursday, August 15, 2019 10:18 AM
  •  There is no way to create a display folder in a measure group without using calculated members right?  
    This is not correct. Cube based measures also have a display folder property. If you click on the measure the properties window is usually displayed in the bottom left corner of SSDT and you just set the folder name there.

    http://darren.gosbell.com - please mark correct answers

    Thursday, August 15, 2019 12:36 PM
    Moderator