October 24, 2016 at 5:14 am
Within an SSAS cube I administer, there are many Time dimensions with named sets. One of which returns previous week current year. Below example of set being used.
WITH DYNAMIC SET [Test] AS
StrToSet
(
"
ParallelPeriod
(
[Finance Date].[Year-Week].[Week],
1,
StrToMember('[Finance Date].[Year-Week].[" + Cstr(datepart("yyyy",now())) + " Week " + Cstr(datepart("ww",now())) + "]')
)
"
)
SELECT {[Test]} ON COLUMNS
FROM [cube];
Very simple. This should return Week 43 from the dimension.
When I run this on a SQL Server 2012 instance it correctly returns Week43.
I have restored the same SSAS cube to a 2016 Server, still pointing to the same datasource of the SQL Server 2012, and it's returning Week 42.
I have tried a Full re-processing of the measures and dimensions but this hasn't helped.
I have checked the system clock on the server.
Any suggestions??
October 24, 2016 at 5:44 am
The weeknumber is determined different around the world, mostly depending on what is considered as first day of the week. Check the LOCALE settings of both environments.
information: http://www.epochconverter.com/weeknumbers
October 24, 2016 at 9:08 am
Thanks for the reply. Looking at it, it's the only logical explanation that the datepart(ww... is returning the incorrect number.
I have checked the regional settings on the windows server and they are identical to the older server.
The DATEPART(wk, getdate()) when run on the SQL instance returns correctly 44.
Running a similar datepart("ww", NOW()) on the SSAS database returns 43.
Even if the start of week was Sunday or Monday, it still should return 44.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply