Post reply

SSAS multi-dim referenced/regular dimensions

  • August 13, 2018 at 7:07 am

    #415246

    I have 2 dimensions - 1 measure group.
    Dim1 : Customer
    Dim2 : Group

    The grain of the measure group is customer and there is a 1-m relationship between Group and Customer.  The measure table has GroupKey and CustomerKey.
    In order to keep the aggregations working I think I need a referenced dimension in this case.  i.e. to group --> customer -> measure table.
    I understand reference dimensions result in slow queries and materialising them does increase performance without too much storage.
    Group is a tiny dim and customer is a large dim.
    This is making me think referenced dim is the way to go.
    Is my thinking correct?

  • August 13, 2018 at 9:48 pm

    #2001572

    Customer dims get big, you need something to roll-up customers before showing as an attribute, no-one likes 20,000 columns in a dashboard or report.
    I look for things to group by, like Geography, maybe Country, State, or Region, or demographic data like Male/Female, Age-bucket, etc.
    Sounds like you have a good natural candidate with Group, I would add GroupKey to your CustomerDim, and when you present your CustomerDim, expose GroupName as a top-level attribute (hierarchy), then allow drill-through to State maybe, and lastly individual Customers.
    Hope that helps,
    ChrisBecker.blog

    Chris Becker bcsdata.net

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply