locked
counting degenerate dimensions RRS feed

  • Question

  • Hi,

    In my fact table I have two degenerate dimension (OrderNo, ItemNo). The grain is at billing level, so 1 OrderNo can have many rows, same with the ItemNo.

    Question, how do I best represent these as measures in my cube (OrderNo Count and ItemNo count). I can do a distinct count to create both measures, however I'm trying to avoid distinct counts, one reason this will create two new measure groups, so I assume processing and query performance will decrease (my fact table is roughly 200million rows).

    These are important measures for me because many questions are about about the production... how many orders last month, how many orders for supplier x and so on...

    Are there alternatives to distinct counts?

    Tuesday, April 16, 2013 9:46 AM

Answers

  • Hi Toro,

    OK.  This could be because you have filtered on an attribute of the [Dim Order] dimension.  In this case it would be fixed by adding Existing to the NonEmpty() function ie try this.  Also, probably safest to add the measure you are using for NonEmpty.

    count(nonempty(Existing [Dim Order].[ORDER_ID].[ORDER_ID].members,Measures.Orders))


    Richard


    • Edited by RichardLees Monday, April 22, 2013 12:49 AM correction
    • Marked as answer by Elvis Long Thursday, April 25, 2013 9:09 AM
    Monday, April 22, 2013 12:48 AM

All replies

  • Hi Toro, If I understand correctly, each Order can have multiple Items.  ie. Item belongs to an Order?  If this is the case, I would be thinking of having one dimension with OrderNo, ItemNo as the composite key.  Then, any time you wanted to know how many Orders there were, or how many Items in an Order(s) it is just a matter of counting the nonempty() members of the dimension.

    Regarding performance of this design, it will somewhat depend on the size of the OrderItem dimension.  However, a query that is asking for the number of items in an Order will perform very well as ssas will restrict the count nonempty to the OrderItems for that particular Order.  Similarly, queries that filter on attributes of Order or Item will run faster than non filtered queries as ssas will scan a smaller number of OrderItem members.  For this reason, you might want to make OrderMonth etc a properties of the Order.

    Hope that helps,

    http://RichardLees.blogspot.com 


    Richard

    Thursday, April 18, 2013 4:04 AM
  • Hi Richard,

    Thanks for the reply. Ok I have tried your suggestion, however maybe I'm missing something...

    I have created the dimension and a calculated member as follows (count(nonempty([Dim Order].[ORDER_ID].members)))

    When browsing the cube, the calculated member returns the correct total, however when I introduce any other dimensions to slice the data, the calculated member continues to returns the full total

    e.g.

    total orders = 100 (OK)

    introduce dimension date

    2012 orders = 100 (not ok, should be 40)

    2013 orders = 100 (not ok, should be 60)

    thanks

    Thursday, April 18, 2013 10:05 AM
  • Hi Toro,

    OK.  This could be because you have filtered on an attribute of the [Dim Order] dimension.  In this case it would be fixed by adding Existing to the NonEmpty() function ie try this.  Also, probably safest to add the measure you are using for NonEmpty.

    count(nonempty(Existing [Dim Order].[ORDER_ID].[ORDER_ID].members,Measures.Orders))


    Richard


    • Edited by RichardLees Monday, April 22, 2013 12:49 AM correction
    • Marked as answer by Elvis Long Thursday, April 25, 2013 9:09 AM
    Monday, April 22, 2013 12:48 AM