Dimension attribute with duplicate values allowed in SSAS 2008, gets error in 2005

0
votes

I have a cube in SQL Server Analysis services 2008 that contains a time dimension comprised of years and quarters. There is a separate YearQuarterID column as well.

The Quarter attribute (type Quarters) consists of integer values 1 - 4 for each year, as you might expect. Since this is not the key attribute for the dimension, the non-uniqueness shouldn't be a problem, and in fact the cube builds cleanly.

Inspecting the properties for this attribute in BIDS 2008, I see that it does have a KeyColumns property. (Not sure what this means for a non-key attribute.) It says "DimYearQuarter.Quarter (Integer)".

Now, I'm trying to retrofit this cube to SSAS 2005. I have a similar dimension, created from a similar relational database table with similar values. As far as I can tell, the properties for the attribute are identical in BIDS 2005, nevertheless, when I try to process the cube, I get the error:

Errors in the OLAP storage engine: The attribute key is a duplicate: Table: dbo_DimYearQuarter, Column: Quarter, Value: 1.

I did notice that the Properties window in VS2005 doesn't allow the KeyColumns property to be expanded. So I can't drill down to compare details of the property between versions.

If the attribute is not a key for the dimension, why are duplicates a problem in 2005?

1
ssasolapbids

1 Answers

2
votes

Humm, this is unexpected. Usually 2005 lets you do things that 2008 doesn't (duplicate key errors is one of them, believe me, I've been through this road)

Regarding the key, it should be the combination of year-quarter, because quarter 1 appears in more than one year.

Answering your Not sure what this means for a non-key attribute comment, every attribute has a key, even though it isn't the Dimension's key. The key (on the attribute) will indicate how the SELECT DISTINCT query will be built to get the dimension's members