The dimensions in the cube are giving us duplicates, there is no error while processing the cube, but when the cube is browsed we could see duplicates.
There is a hierarchy in our dimension, the hierarchy is built like Country -- State -- Area
.
In the DB, the dimension data looks something like below.
+---------+------------+-----------+
| Country | State | Subarea |
+---------+------------+-----------+
| India | Karnataka | Bangalore |
| India | Telangana | Hyderabad |
+---------+------------+-----------+
Now, when the cube is browsed with the attribute Country, we see 2 members under country as "India", when measure is selected against the attribute, the sales are shattered between these 2 values.
Can we do any work around to avoid this problem?
Best Answer
That behaviour is dependent on your
KeyColumns
setting.Given a cube with these 2 named queries in the
datasource view
Cities:
Sales:
A dimension based on cities, linked to the Sales table on the
sales.city->cities.id
relation that looks like this, using the id column asKeyColumns
:The result is this:
However, if I change the
KeyColumn
property tocountry
for that attribute like this:The result is all sales are reported under one single country:
I agree the documentation is a bit terse:
Where you should interpret it as "there is one separate attribute member for each distinct value in the key column, even if the displayed value is the same"
Do note however that changing the key column might require you to change the
NameColumn
and theOrderBy
properties.Also,
KeyColumns
can be comprised of multiple columns acting as a unique key instead of a single key like in my example.Sometimes when it's too risky for existing reports to change the structure already in place it might be a good option to add an extra attribute.