Defining Member Properties for an Attribute in SSAS ( Duplicate error Attribute)

Question

Defining Member Properties for an Attribute in SSAS ( Duplicate error Attribute)

srinivas.sqlbidev

SSC Enthusiast

Points: 128
More actions
October 13, 2015 at 8:29 pm

#312209

Hi ,
I have an Issue While creating member properties for Dimension.
I have a dimension table name DimCustomer.
It has Columns
( DimCustomerKey(identity), CusomerCategory,CustomerName, CustomerNumber)
It has values
(1, Private, Scott, 1001),
(2,Private,Scott,1002),
(3,Public,Nancy,1110)
Now I need to create a hierachy
CustomerCategory --> CustomerName
And I want CustomerNumber as a member property for CustomerName
I create a Member property for CustomerNumber by creating a new attribute relationship between
CustomerName(Source) --> CustomerNumber(RelatedAttribute)
and under CustomerNumber properties window i disabled that AttributeHierarchyEnabled --> False
and DiscouargeGroupBehaviour .
*********************************
When Processing the Cube Im getting Duplicate Attribute error ( because of Duplicate data i.e Scott customer name has two customer numbers (1001,1002) )
please help me how to resolve this.
If I make a composite ColumnKey for CustomerName as ( CustomerName + CusomerNumber) it works but Same CustomerName Scott appears Multiple times
at Customer Hierachy , User dont want to display duplicates.
Please help me.
Thanks in Advance..!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

Firstly, you have already fixed this in one way:

If I make a composite ColumnKey for CustomerName as ( CustomerName + CusomerNumber) it works but Same CustomerName Scott appears Multiple times
at Customer Hierachy , User dont want to display duplicates.

This is correct behaviour - you're not really seeing a duplicate. You are seeing distinct members in a hierarchy.

A quick fix to this would be to change the error configuration from "Custom" (the default post-SQL 2005) to "Default" as this allows duplicates. But bear in mind that because you are making a set of CustomerCategory+CustomerName then the value for "Scott" is a genuine duplicate (2 x "Private"+"Scott"). Perhaps look for something more unique to go into the hierarchy between them or don't have a hierarchy at all and create the correct composite keys and let the presentation layer work out the natural hierarchy.

I'm on LinkedIn

srinivas.sqlbidev SSC Enthusiast Points: 128 More actions · Answer 2

I know those are genuine duplicates , but users don't want to see those repetitions.

There is one more column called SalesShipToCity that they need to have as member attribute.

So in that case SCOTT has 15 cities , in that case SCOTT name repeats 15 times.

Even though it is genuine repetitions but Users dont want to see same SCOTT 15 times.

Thanks

Srinivas

PB_BI SSCoach Points: 17463 More actions · Answer 3

srinivas.sqlbidev (10/14/2015)
I know those are genuine duplicates , but users don't want to see those repetitions.
There is one more column called SalesShipToCity that they need to have as member attribute.
So in that case SCOTT has 15 cities , in that case SCOTT name repeats 15 times.
Even though it is genuine repetitions but Users dont want to see same SCOTT 15 times.
Thanks
Srinivas

I have already suggested an approach to solve this issue and I don't see any additional questions above. Have you looked at your hierarchy in anything other than the browser in SSDT? It doesn't sound as if you have. The browser in SSDT is very literal whereas a presentation layer (Excel for example, even SSRS) is much more forgiving.

I'm on LinkedIn