Post reply

SSAS Multiple Cascading Many to Many Join Issue

  • February 6, 2014 at 4:40 am

    #144848

    Hi All,

    I have a tricky issue that I can't seem to resolve regarding many to many joins in a SSAS cube.

    For example I have a fact table (UserID, Value), and three Dimensions: UserType(UserTypeID, Name), Team(TeamID, Name) and Report(ReportID, Name).

    Objective:

    I want Report A to only return the users data that exist in Team A and C, and for only those users of UserType Customer.

    To do this I have four many to many fact tables. Two that join the fact table to the UserType and Team Dimensions. And two more that join the UserType and Team dimensions to the Report dimension. So in theory I can slice by the report dimension to return only the result set that I require. See diagram for a cube diagram of the structure:

    (if image doesn't show try opening the image in a new tab!)

    The problem is that when selecting the dimension usage I can only pick one path for the Report dimension back to the cube, therefore I can only get the result set to restrict by either UserType or by Team, and never both.

    I have tried to find a solution for this but there doesn't seem to be much documentation for this type of structure. Any help would be greatly appreciated.

    Thanks,

    Martin

  • February 6, 2014 at 8:24 am

    #1686632

    Why do you need separate UserTypeMapping and UserTeamMapping intermediate facts.

    Why not structure your data to have (going back a few steps to dimension modelling) one User table that has foreign keys for UserTypeId and UserTeamID? This would simplify things I think. that way you could expand your Report dimension to include UserTypeID and TeamID on each row and the join is then a referenced one to the fact. Or am I missing the point?

    I'm on LinkedIn

  • February 6, 2014 at 8:48 am

    #1686647

    Hi yayomayn, thank you for your response.

    Unfortunately I cannot put the TypeId and TeamID as foreign keys in the User table as a user may be in many teams, and have many types, therefore it would exponentially increase the size of the dimension. This would then result in the Report dimension having to contain a row for every single possibility of Report X UserType X UserTeam. Any other ideas? 🙂

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

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