1

I have a difficult time understanding why a simple MDX query returns different results if a dimension has an additional attribute which has KeyColumns same as KeyColumns of the key attribute.

Here is an example

  1. Create a simple cube called Adventure Works based on the AdventureWorksDW2017 database with just one measure Internet Sales Count and one dimension Product which has two attributes Product and Style.
  2. Now run the following MDX query
WITH MEMBER TEST AS 
(
[Product].[Style].&[U], [Measures].[Internet Sales Count]
)
MEMBER TEST2 AS 
(
[Product].[Style].&[U], [Measures].[Internet Sales Count], [Product].[Product].CurrentMember
)
SELECT { [Measures].[Internet Sales Count], TEST, TEST2} on COLUMNS,
NON EMPTY {[Product].[Product].[All].Children} on ROWS
FROM [Adventure Works]
WHERE ([Product].[Style].&[U])

Notice that TEST measure returns the same value for all products.

enter image description here

  1. Now go back to the project and add a Product Key attribute to the dimension. There is nothing special about this attribute. It gets created with default attribute relationship.

enter image description here

There is nothing special about it on the Dimension Usage tab

enter image description here

Deploy the changes and process the cube. Now the same query returns a different result. Note all three measures return the same value.

enter image description here

Can anyone explain please what is going on here or point to the documentation which describes this behavior? Could it be a bug in SQL Server Analysis Services?

I am using SQL Server 2017 Developer Edition CU20. Here is a link to the multidimensional project

Alex
  • 123
  • 2
  • 3
  • 9
  • What type of measure(which agregate function) is your "Internet Sales Count". Are you sure you have pasted the correct query. I checked it and didnt get same results for Test – MoazRub May 18 '20 at 18:43
  • @MoazRub the aggregation type is Count, I've updated the post with a link to a sample project. I double checked the query, it is correct. You may get a different numbers if you use a different version of AdventureWorksDw database. The main point is that in scenario #1 TEST returns a fixed number for all products, in scenario #2, it returns data only applicable products only. What is the version of SQL Server are you testing on? – Alex May 18 '20 at 21:57
  • Can you check the dimension usage tab, if they are linked? Usually same value is repeated when they are not linked – MoazRub May 19 '20 at 00:01
  • Nothing special there. The dimension is linked to the measure group. I've updated the post with more screenshots. – Alex May 19 '20 at 07:22
  • @MoazRub just want to add that if you want to reproduce it in the Adventure Works multidimensional sample project from Microsoft, then you need to remove almost all attributes from the Product dimension including Large Image which has KeyColumns set to ProductKey. In the sample OLAP database from Microsoft, if you remove certain attributes from the Product dimension, you also may need to remove Data Mining structures, KPIs and some calculations. This is the reason I created my own repro. – Alex May 19 '20 at 07:47

0 Answers0