MDX : YTD of the previous year on a specific hierarchy

This formula can avoid the hour spent to Search for it! This one directly calculate the aggregated members.

aggregate

({

openingperiod([Date].[Calendar Soc].[Day],ancestor([Date].[Calendar Soc].CurrentMember,[Date].[Calendar Soc].[Year Soc]).prevmember)

:

ClosingPeriod( [Date].[Calendar Soc].[Day],cousin([Date].[Calendar Soc].CurrentMember,ancestor([Date].[Calendar Soc].CurrentMember,[Date].[Calendar Soc].[Year Soc]).prevmember))

},[Measures].[…])

Advertisements

A duplicate attribute key has been found when processing SSAS Cube Dimension

I had a strange issue at a customer on a SSAS cube.
I was trying to migrate the cube from dev environment to test, the processing of the cube starts to fail with this error message:
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Client’, Column: ‘Name’, Value: ‘JàRnïpâ’.
The thing is that in dev with the same data it was succeeding!!!!!!!
The error was coming from the cube collation, it was setup to accent insensitive and the source database was in Accent sensitive.
Here is the desription of the bug:
To create the agrregation of the cube, SSAS send “Select distinct” queries to the database, as the source database is in accent sensitive, here is the result:
JàRnïpâ
JaRnïpâ
–> those 2 entries are different for “select distinct” result with the option accent sensitive enabled!!
But for the cube, as it is in incensitive, those records are considered as the same!!!!
This is why you got back duplicate attribute error :
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Client’, Column: ‘Name’, Value: ‘JàRnïpâ’.
Damned!