Measure Group not restricted by Dimension if not on Dimension Key Attribute

Question

I am stuck on modelling a tiny example in Microsoft SQL Server Analysis Services. I do consider myself advanced in this technology, but this problem I cannot get my head around.

It comes down to a measure group which is restricted by three dimensions. Two dimensions behave exactly as expected, limiting the rows delivered back.

The third dimension does not have any effect on this measure group. For each entry on this third dimension, the same value (the total) is delivered as measure. The only noticeable difference between the dimensions is, the ones that do work link through their key element, while the one that does not does link through a non key attribute.

The item names are in German language, hope the following translation helps

Benutzer -> User
Kunde -> Customer
Bestellung -> Order

I hope the following screen shots help to show the relevant modelling details:

Dim Bestellung (the one that is not working)

Dimension Usage in Cube

MDX Query that is working

If I do query using the Kunde ("customer") dimension which is linked on key, everything behaves as expected. For the customers with 1s and 3s in their "name", there is a result, and null for the others.

SELECT {
        [Measures].[Anzahl SecKunde]
    } ON COLUMNS
    , {
        [DIM Kunde].[Hierarchie Kunde].[Kunde]
    } ON ROWS
FROM
    [BiEvaluation]
WHERE
    [DIM Benutzer].[Hierarchie Benutzer].[Benutzer].&[Domain\User1]

MDX Query not working

If I however query by Bestellung ("Order"), I get the total of 2 for each order. The orders B1, B2, B5 and B6 are from customers 1 and 3, so I'd like to see a count of 1 there!

SELECT {
        [Measures].[Anzahl SecKunde]
    } ON COLUMNS
    , {
        [DIM Bestellung].[Hierarchie Bestellung].[Bestellung].AllMembers
    } ON ROWS
FROM
    [BiEvaluation]
WHERE
    [DIM Benutzer].[Hierarchie Benutzer].[Benutzer].&[Domain\User1]

MDX Query not working "fixed"

I found a way to make it work, yet... I do not understand why. The "trick" is to include the non-key-attribute the dimension-fact link is on. This gives the result I consider correct, a 1 for B1, B2, B5 and B6 (because those are done by customers 1 and 3)

SELECT {
        [Measures].[Anzahl SecKunde]
    } ON COLUMNS
    , {
          [DIM Bestellung].[Hierarchie Bestellung].[Bestellung].AllMembers
        * [DIM Bestellung].[ID Kunde].[ID Kunde].AllMembers -- Only change needed
    } ON ROWS
FROM
    [BiEvaluation]
WHERE
    [DIM Benutzer].[Hierarchie Benutzer].[Benutzer].&[Domain\User1]

Sorry for this awful long post. If anybody can share any idea, why this is happening I'd appreciate it a lot. I could most likely work with including the attribute, but this is a huge source for problems and errors. I do not understand why having it there makes this difference.

I think it is not relevant to the question, but I'll include the versions of the tools anyway:

Visual Studio 2017 15.9.41
SQL Server Analysis Services-Designer 15.0.19623.0
SQL Server 2017 (SSDB) 14.0.3370.1 (CU22 + Security Update)
SQL Server 2017 (SSAS) 14.0.249.62 (CU22)

Measure Group not restricted by Dimension if not on Dimension Key Attribute

0 Answers