CODEWITHSUNDEEP
sql-serverssasolap
Pragmateek
Pragmateek

Reputation: 13364

Preventing aggregation along dimensions' attributes

Say I have this schema (sorry for the slightly convoluted example):

CREATE TABLE Sales
(
    ID INT PRIMARY KEY,
    Shop NVARCHAR(MAX),
    ShopLocationLeft NVARCHAR(MAX),
    ShopLocationRight NVARCHAR(MAX),
    Amount DECIMAL
)

INSERT INTO Sales VALUES
(1, 'Shop #1', 'New', 'York', 10000),
(2, 'Shop #2', 'New', 'Delhi', 1000),
(3, 'Shop #3', 'North', 'York', 5000)

Then I create a cube with a Shop dimension with 3 attributes:

I can explore the cube along this dimension:

SELECT
    [Amount] ON COLUMNS,
    [Shop].[Name].Children ON ROWS
FROM
    [Sales]

To get:

        Amount
Shop #1 10000
Shop #2 1000
Shop #3 5000

So far so good.

But using other attributes like Location Left:

SELECT
    [Amount] ON COLUMNS,
    [Shop].[Location Left].Children ON ROWS
FROM
    [Sales]

We get:

      Amount
New   11000
North 5000

So the cube is allowing exploration and aggregation 1 level deeper than the dimension, along the attributes, making them some kind of sub-dimensions.

Which in this case has no business meaning.

I was expecting that, like an SQL SELECT, this would display the Location Left column instead:

        Amount
New     10000
New     1000
North   5000

Because for me this dimension has 3 points:

Which should be considered atomic entities that can't be broken down further.

I understand that this behavior can be useful (e.g. for first and last name) but in this case it does not make any sense.

Or if I had defined an n-levels hierarchy for an attribute (e.g. country -> city -> location) it would be logical too as I would have explicitly asked for a deeper exploration and aggregation.

How to prevent this behavior when it would lead to non relevant results?

Upvotes: 1

Views: 106

Answers (1)

vldmrrdjcc
vldmrrdjcc

Reputation: 2112

If you have an attribute Location Left in your Shop dimension you can choose ID as the Key column and Location Left as the Name column of this attribute (in the Dimension structure tab - right click on the Location Left attribute and select properties, then you will look for KeyColumn and NameColumn properties). If you do this ,you will see 'New' being displayed multiple times in the results.

If you have an attribute say Location Left and choose the same Location Left both as the Key column and as the Name column, you will see only one entry per Location Left Name.

Upvotes: 1

Related Questions