Search code examples
sql-serverdatetimecubessas-tabular

How to force unique DateTime Values with Daylight Savings SSAS Tabular

I have an interesting problem arising around daylight savings times.

So I have some SQL Server tables that I use to record calls for a 24hr Telephone service line. I have a FactCalls table and a dimTime table (split into 15 min intervals). dimTime looks like the following; (only showing 28th October, during the daylight savings transition)

TimeIntervalID   DateTime
274272           2018-10-28 00:45:00.0000000 +01:00
274273           2018-10-28 01:00:00.0000000 +01:00
274274           2018-10-28 01:15:00.0000000 +01:00
274275           2018-10-28 01:30:00.0000000 +01:00
274276           2018-10-28 01:45:00.0000000 +01:00
274277           2018-10-28 01:00:00.0000000 +00:00
274278           2018-10-28 01:15:00.0000000 +00:00
274279           2018-10-28 01:30:00.0000000 +00:00
274280           2018-10-28 01:45:00.0000000 +00:00
274281           2018-10-28 02:00:00.0000000 +00:00

So the DimTime table stores the datetime value as DATETIMEOFFSET to take into account the change in daylight savings (GMT to BST). The Fact table joins to the DimTime table on the TimeIntervalID (as we are only interested in 15 min blocks)

Now Ive created a Tabular Cube in SSAS importing the DimTime and FactCalls tables. I want to mark the DimTime table as a date table but when im selecting the datetime column above as the unique date value, its throwing me the following error;

ssas_tabluar_cube_unique_date_error

How can I make the cube store the daylight savings hours as unique values? As there doesn't appear to be an option to format the date as DATETIMEOFFSET like there is in the SQL database engine (see screen above)

Solution

  • The default conversion from datetimeoffset to datetime simply truncates the offset and returns the local time part of the datetime offset, resulting in duplicate values during DST changes.

    As the error says, you will need to have a single row in DimTime for each 15min interval.

    You'll need to convert all the datetimes to a single timezone to load the date table. The normal magical incantation for this in SQL Server is convert(datetime2(0), [dto_column],1), which will end up in UTC time.

    eg:

    declare @t table(dso datetimeoffset)
insert into @t values ('2018-10-28 01:45:00.0000000 +01:00'),('2018-10-28 01:00:00.0000000 +00:00')
select dso, 
       convert(datetime2(0), dso,1) dt_utc
from @t

    outputs

    dso                                dt_utc
---------------------------------- ---------------------------
2018-10-28 01:45:00.0000000 +01:00 2018-10-28 00:45:00
2018-10-28 01:00:00.0000000 +00:00 2018-10-28 01:00:00

    Then if you really need to know what hour-of-day a particular event occurred on, that would be stored in the fact, not the date dimension. But for most scenarios it's not important for reporting.