1

I have an indexed datetime column - lets call it "CreatedDateUTC" in table "Entity" in MSSQL db. Column contains UTC datetime. User connecting to db has known timezone offset. I have another table - DateDim with primary key column Date. This is to support date dimension for SSAS/Tabular model/Direct query scenario.

So on model level "Entity" is defined as SQL query

SELECT CAST(fn_AdjustCurrentTimezoneOffset(CreatedDateUTC) as Date) as CreatedDate 
FROM dbo.Entity

The SQL query featuring DateDim from report then looks like

SELECT * FROM [Entity] e 
JOIN [DateDim] d on CAST(fn_AdjustCurrentTimezoneOffset(e.CreatedDateUTC) as Date) = d.Date

which is unbearably slow when [Entity] table grows large. Even if i resign on adjusting timezone (which I don't want to), it doesn't get much better - i.e. this query is slow as well:

SELECT * FROM [Entity] e 
JOIN [DateDim] d on CAST(e.CreatedDateUTC as Date) = d.Date

My feeling is the issue here is with CreatedDateUTC datetime - it needs to be converted to indexed CreatedDateDayUTC date upfront (not a problem since I query special reporting db). My problem is with the timezone - since users have different timezone offset I cannot simply precalculate this.

On SQL level I would be able to get around this by this ugly hack - precalculate three dates (Date1=DATEADD(day,-1,CreatedDateDayUTC)/Date2=CreatedDateDayUTC/Date3=DATEADD(day,1,CreatedDateDayUTC)) on Entity table and adjusting join to something like:

SELECT * FROM [Entity] e 
JOIN [DateDim] d on CAST(e.CreatedDateUTC as Date) = d.Date 
  and (e.Date1 = d.Date or e.Date2 = d.Date and e.Date3 = d.Date)

However this can't be translated to query defining "Entity" on model level (there has to be just one column which can create relationship do DateDim Date column).

Any idea how to get around this ?

  • What if you changed your date dimension to one row per hour per day? If you only care about full hour time zone offsets then you could round EntityDateUTC to the nearest hour (actually store it rounded to the hour) and do a straight join. Then could you have separate columns in your Date dimension for each time zone and make the user pick? – GregGalloway May 5 '17 at 1:42
  • @GregGalloway - great idea. But there is like 130 time zones in system and I kind of want the report designers to be shielded from fact there is such thing as timezone (i.e. I want to show them DateDim as DateDim in their time zone). Will think about your approach more if I can utilize it somehow. – Ondrej Svejdar May 5 '17 at 9:20
  • @GregGalloway - also there are some crazy timezones that have minutes offset like 30 minutes offset or 45 offset (Nepal, EUCLA), so more like 15 minutes periods instead of hours. – Ondrej Svejdar May 5 '17 at 11:03
  • do you have users in Nepal? If so I guess you need to store the fact table rounded to 15 minute increments. – GregGalloway May 5 '17 at 12:48
  • what about moving the time zone function to an attribute on the Date dimension (a column that's not the join key) instead of on the fact table. Does that perform better? – GregGalloway May 5 '17 at 12:50
1

Thanks to @GregGalloway to set me to right track. I ended up with following schema:

1) I've added new mapping table [DateMap] that materialize mapping between UTC datetime (in those weird quarter-hours) to local time zone (again in quarter-hours)

CREATE TABLE [dbo].[DateMap] (
  -- time zone code (set as context info for given user)
  [TimeZoneCode] smallint NOT NULL,
  -- quarter hours means datetime trimmed to xx:00, xx:15, xx:30, xx:45
  [UTC_QuarterHours] smalldatetime NOT NULL,
  -- local time zone
  [LTZ_QuarterHours] smalldatetime NOT NULL,
  CONSTRAINT [PK_tmp_DateMap] PRIMARY KEY ([TimeZoneCode] ASC, [UTC_QuarterHours] ASC)
)

2) [DimDate] has primary key set to LTZ_QuarterHours

CREATE TABLE [dbo].[DimDates](
  [LTZ_QuarterHours] smalldatetime NOT NULL,
  [Date] date NOT NULL,
  [Year] int NOT NULL,
  [Month] int NOT NULL,
  [Quarter] int NOT NULL,
  CONSTRAINT PK_tmp_DimDates PRIMARY KEY ([LTZ_QuarterHours] ASC)
)

3) [Entity] table has new column [CreatedDateUTC_QuarterHours] - which means original utc date trimmed to quarter hours

CREATE TABLE [dbo].[Entity] (
  [EntityID] int NOT NULL PRIMARY KEY,
  [CreatedDateUTC] datetime,
  [CreatedDateUTC_QuarterHours] smalldatetime
)

4) Entity in model is defined as SQL query like

SELECT e.*, m.[LTZ_QuarterHours] FROM [Entity] e
JOIN [DateMap] m on e.CreatedDateUTC_QuarterHours = m.UTC_QuarterHours and 
  m.TimeZoneCode = fn_GetCurrentUserTimeZoneCode()

Which is pretty fast.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.