Post reply

ParallelPeriod dynamic set very odd behaviour

  • October 24, 2016 at 5:14 am

    #331278

    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

    #1908327

    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

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 24, 2016 at 9:08 am

    #1908403

    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