An unusual finding around hierarchies and attribute relationships, hopefully it will convince you to perhaps double check your attribute relationships.
In the case where a user hierarchy has not been defined properly and there’s a warning symbol glaring at you (perhaps in the hope you might fix it), it could make specific types of queries produce incorrect results. A certain scenario produces the All level total rather than the sub cube value.
The scenario seems to only occur when the following is true:
- The user Hierarchy’s attribute relationships are not set properly
- The hierarchy is in the where clause
- An attribute from the hierarchy is on columns or rows (must be at a higher level in the hierarchy)
- And brackets are used in the where clause {}
To fix it:
- Setup the attribute relationships in the user hierarchy correctly
- Or alter your MDX query, not always that easy
Code as follows (Adventure Works DW Database)
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON EMPTY [Product].[Days to Manufacture].[All Products] ON ROWS
FROM
[Adventure Works]
WHERE
[Product].[Manufacture Time].[Days to Manufacture].&[0]
Returns: $1,040,532.57.
Add {} to WHERE clause. It returns: $29,358,677.22 which is incorrect
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON EMPTY [Product].[Days to Manufacture].[All Products] ON ROWS
FROM
[Adventure Works]
WHERE
{[Product].[Manufacture Time].[Days to Manufacture].&[0]}
I couldn’t find reference to a cumulative update to this and I haven’t had a chance to test to see if it affects SSAS 2008. So I am not sure if they have fixed this.
