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].[…])

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!

Using .xlsx sources with SSiS (64 bit) on a 64 bit system

The only way I’ve found to run an SSIS data flow with an .xlsx as source and without changing the option ’64 runtime’ to false is to use as source an “OLE DB source” component in the data flow with this query:

select * FROM OPENROWSET (‘Microsoft.ACE.OLEDB.12.0’

, ‘Excel 12.0;Database=C:\Hello.xlsx’

, [Sheet1$]

)

By the way, to make it works, you have to install the ACE drivers(for 64 bit) from microsoft website on the machine where you execute your package. 🙂

SQL Server Convert from varchar to datetime, datetime format

Here are some queries ready for varchar to datetime or datetime to varchar conversion:

Think that a datetime is a fix format that depends of the date format  in your Windows OS and if you want to customize the way to show a datetime, use a varchar. 🙂

Varchar   format From   Varchar –> datetime from   date time –> ‘Varchar’
mon dd yyyy hh:mmAM convert(datetime,’Varchar’,100) convert(Varchar,’Date’,100)
mon dd yy hh:mmAM convert(datetime,”Varchar”,0) convert(Varchar,’Date’,0)
mm/dd/yy convert(datetime,’Varchar’,1) convert(Varchar,’Date’,1)
yy.mm.dd convert(datetime,’Varchar’,2) convert(Varchar,’Date’,2)
dd/mm/yy convert(datetime,’Varchar’,3) convert(Varchar,’Date’,3)
dd.mm.yy convert(datetime,’Varchar’,4) convert(Varchar,’Date’,4)
dd-mm-yy convert(datetime,’Varchar’,5) convert(Varchar,’Date’,5)
dd mmm yy convert(datetime,’Varchar’,6) convert(Varchar,’Date’,6)
mmm dd, yy convert(datetime,’Varchar’,7) convert(Varchar,’Date’,7)
HH:mm:ss convert(datetime,’Varchar’,8) convert(Varchar,’Date’,8)
mon dd yyyy hh:mm:ss:fffAM convert(datetime,’Varchar’,9) convert(Varchar,’Date’,9)
mm-dd-yy convert(datetime,’Varchar’,10) convert(Varchar,’Date’,10)
yy/mm/dd convert(datetime,’Varchar’,11) convert(Varchar,’Date’,11)
yymmdd convert(datetime,’Varchar’,12) convert(Varchar,’Date’,12)
dd mon yyyy HH:mm:ss:fff convert(datetime,’Varchar’,13) convert(Varchar,’Date’,13)
HH:mm:ss:fff convert(datetime,’Varchar’,14) convert(Varchar,’Date’,14)
yyyy-mm-dd HH:mm:ss convert(datetime,’Varchar’,20) convert(Varchar,’Date’,20)
yyyy-mm-dd HH:mm:ss.fff convert(datetime,’Varchar’,21) convert(Varchar,’Date’,21)
mm/dd/yyyy convert(datetime,’Varchar’,101) convert(Varchar,’Date’,101)
yyyy.mm.dd convert(datetime,’Varchar’,102) convert(Varchar,’Date’,102)
dd/mm/yyyy convert(datetime,’Varchar’,103) convert(Varchar,’Date’,103)
dd.mm.yyyy convert(datetime,’Varchar’,104) convert(Varchar,’Date’,104)
dd-mm-yyyy convert(datetime,’Varchar’,105) convert(Varchar,’Date’,105)
dd mmm yyyy convert(datetime,’Varchar’,106) convert(Varchar,’Date’,106)
mmm dd, yyyy convert(datetime,’Varchar’,107) convert(Varchar,’Date’,107)
mm-dd-yyyy convert(datetime,’Varchar’,110) convert(Varchar,’Date’,110)
yyyy/mm/dd convert(datetime,’Varchar’,111) convert(Varchar,’Date’,111)
yyyymmdd convert(datetime,’Varchar’,112) convert(Varchar,’Date’,112)
yyy-mm-ddThh:mm:ss convert(datetime,’Varchar’,126) convert(Varchar,’Date’,126)