1

I have two tables with 1:M relation in my DB:

GROUP (GroupId, GroupName, AccountId)

USER (UserId, UserName, GroupId, AccountId)

and ACCOUNT (AccountId, AccountName).

I added all needed links from one to another table in DSV and created 3 dimensions for each of the tables. Then I added one hierarchy for GROUP and USER dimensions like 'Account - Group' and 'Account - User'.

The strange behavior that I see is that everytime I query USER dimension with filter by AccountId it just returns me results for GROUP dimension, like there is no relation between USER and ACCOUNT at all! It seems like SSAS engine sees GroupId in my USER table and resolves relation with ACCOUNT using GroupId instead of UserId.

SELECT { } ON COLUMNS,

[User].[Account - User].[Account].&[11].Children ON ROWS

FROM Cube

This query returns users that are in the groups linked to account with id 11, not users linked to account 11 directly.

In other words, what I am trying to get is to get results like if tables GROUP and USER were independently related to ACCOUNT and my hierarchies works properly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.