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 ?
Best Answer
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)2)
[DimDate]
has primary key set toLTZ_QuarterHours
3)
[Entity]
table has new column[CreatedDateUTC_QuarterHours]
- which means original utc date trimmed to quarter hours4)
Entity
in model is defined as SQL query likeWhich is pretty fast.