4

I am completely new to SSAS an I am trying to deploy a simple cube with only one dimention comprised of multiples attributes. What I did already was to create a DSV from my data source and then I created a dimension from my fact table. It seams that no matter what happens, I get the following error message:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_Fact_Statistics', Column: 'Team', value: 'ANA'. The attribute is 'Team'.

This is my hierarchy: Id (SK) -> Player id -> Team -> Player Name -> Salary

I don't understand, obviously the problem is not that the value is null, like I've seen in other threads, telling me to set NullProcessing under KeyColumns to something else than automatic, but this is not the problem in this context.

Any help would be greatly appreciated.

Jean-François Beaulieu
  • 4,305
  • 22
  • 74
  • 107

5 Answers5

6

Probably you have Team ANA listed under multiple Player Names and/or Salary values.

This is a really tricky area of SSAS. The quickest way forward is probably to install BIDS Helper and use the "Dimension Health Check" function:

http://bidshelper.codeplex.com/wikipage?title=Dimension%20Health%20Check&referringTitle=Documentation

It will show you all the issues in your data (not just the first one which you have discovered so far) and give you some info on how to proceed.

Personally I've gone off building attribute relationships due to the difficulty of debugging and fixing these issues. I tend to build dimensions now where every attribute relates directly to the key attribute. You never see these errors and performance seems very similar. You can still present the users with hierarchies.

If that is not an option for you, then you could try adding the columns for the higher-level attributes to the Key property of all the lower levels. Technically this will work but it is awkward to set up and maintain.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • I have installed the BIDS Helper and ran a health check on my dimension. Turns out the problem is: `Attribute relationship [Team] -> [Name] is not valid because it results in a many-to-many relationship.` – Jean-François Beaulieu Nov 12 '12 at 17:15
  • 1
    Ah, you massive beauty! Totally found a supposedly-impossible many-to-many relationship for me too! – teedyay Jun 24 '14 at 12:04
  • @bartover - my larger cubes are not trivial - 110GB on disk, 1.8m rows in largest dimension (which has no multi-level attribute relationships), 100+ measures, most using LastChild aggregation (over 250+ days of history), 2,000 line MDX script. Response times for Excel Pivot Tables returning 1,000s of cells covering 6 levels of that dimension are 1-2 seconds. – Mike Honey Jun 30 '15 at 02:14
  • When I started with SSAS I also read that same advice - my field experience has been that it is actually not very important for performance. I've built several cubes of similar complexity for various clients with similar results. Aggregation Designs seem to have 100x more impact on query performance. – Mike Honey Jun 30 '15 at 02:14
  • I have essentially the same problem. If I wanted to solve it "properly" would I need to create a new field that is "Team - Player" to uniquify the values and make that the key of the Team attribute? – AaronLS Jul 19 '16 at 15:18
  • Meh. I'd just relate every attribute directly to the key attribute and move on ... – Mike Honey Jul 19 '16 at 23:33
3

This approach solved my problem:

Instead of having the attributes following chained relationships, I simply leave the relationships as they were by default.

Player id (SK) -> Conference
Player id (SK) -> Division  
Player id (SK) -> Team 
Player id (SK) -> Player Name 
Player id (SK) -> Salary
Jean-François Beaulieu
  • 4,305
  • 22
  • 74
  • 107
1

run this in sql find your douplicate for example ID... used it as my dimension key

Select id,count(*) as how_many
from [RC_Dailer_WH].[dbo].[RC_call_logs]
group by id
having count(*) > 1

(3647 row(s) affected) of which there are more than 50k records in my DB

removed duplicates then my cube processed proper

kay-B
  • 81
  • 1
  • 1
  • 12
0

go for that dimension on which it is showing error. and right click go for view code

and search for the below line in that code:

ReportAndStop

delete that XML tag and save

and reprocess it will works

supraja G
  • 1
-1

Go to dimension for which it is showing Error. and give right click - go to view code -

CTRL+F - sarch for Connection - find
ReportAndStop connection String. Delete the above Command from the Code – and Save. Process it again.

It will work.

supraja
  • 1