"IsAggregatable" to false and strange behavior with cube? RRS feed

  • Question

  • This is complicated to explain, but here goes.

    I have DimStore, which includes the following columns:

    StoreKey      StoreName     StoreId
    1                   Macys             MA001
    2                   Sears             SE001

    Our users use Excel to browse the cube, and they only use [StoreName] and [StoreId] for grouping, aggregations, filters, etc.

    Everything works great.

    Then a user asked to add how many locations each store has. So, for example, "Macys" has 20 locations. So I added column [StoreCount] to [DimStore], like so:

    StoreKey      StoreName     StoreId       StoreCount
    1                   Macys             MA001        20
    2                   Sears             SE001           35

    I added this attribute to the dimension, and set property "IsAggregatable" to false since I don't want to group by store count; it's an FYI about the store. So now, my store dimension looks like this:

    I process the cube and refresh in Excel. I thought everything was working fine and then someone called me that there are stores that are not being displayed.

    I reproduced the issue: without "Store Count" OR with "Store Count" and "IsAggregatable" set to True, the two StoreIds that I selected in the Excel Filter are showing in my Excel. This is correct.

    The issue: Once I set "IsAggregatable" to False, only one store is shown IF I only search by StoreId.

    Here are the Excel screenshots. In this example, I selected two stores (065 and 066). As you can see, I can only see Store Id 065 and I'm only display "Store Id" in the Excel sheet. 066 is missing:

    I then drag "Store Name" into "Rows" pane, and store Id "066" pops up:

    It only displayed because I added the name to the Excel sheet. Also, the grand total is not considering the 560 from Sears.

    This same behavior is seen in the SSDT's cube browser.

    The fix is to change "IsAggregatable" to true for the "Store Count" attribute, but then Excel will display totals when this is selected. Deleting the "Store Count" attribute also fixes it.

    Why does setting "IsAggregatable" to false eliminate stores from the cube? And what's the solution to the issue?

    I'm including the "Attribute Relationships" tab, maybe it'll help:




    Friday, May 29, 2020 7:23 PM

All replies

  • So the behaviour you are seeing is expected based on your attribute relationships. Setting IsAggregatable to false only has a set of very narrow use cases. I typically only use it in scenarios where it having the results aggregate produces incorrect results.

    So as I see it you have 3 options

    1. You can set IsAggregatable=true and just turn off the groups/subtotals in the pivottable as the extra subtotals are not incorrect, just unwanted.

    2. You could alter the relationships so that you have a chain with Store count at the end. In this way attribute overwriting will kick in and set the correct StoreCount. Your relationships would need to be 

    Store Name -> Store Id
    Store Id -> Store Count

    Although I'm not sure why Store Id is not the key attribute... Normally an Id is static while a name could change over time.

     3. But I think probably the best option in this scenario might be to make Store Count a measure. You can have multiple measure groups in a cube and a single table (like DimStore) can play the role of both a Dim and Fact at the same time. In this way you will not get grouping by store count, but you will also get a grand total of all stores in the current selection. - please mark correct answers

    Sunday, May 31, 2020 10:41 PM
  • One other option is to turn off attribute hierarchy for Store Count and use it as a field property in pivot tables.

    Expect me to help you solve your problems, not to solve your problems for you.

    Monday, June 1, 2020 7:32 AM