How do I specify invalid time in a time dimension?
I am creating a time dimension only in my datastore. I already have the size of the date.
How to designate an unknown time? In my DimDate dimension I have marked 01/01/1753 as reserved for unknown dates, but I think the timing will be a little more complicated. We do not allow NULLs in our fact tables. How do I go about doing this, and what might this line be?
source to share
It can look whatever you want. Most dimensions have a "display name" so your measurements might look something like this:
create table dbo.DimDate (DateID int, DateValue date, DisplayDate nvarchar(20))
go
-- this is an unknown date; 1753-01-01 is only there because we need some valid date value
insert into dbo.DimDate values (1, '1753-01-01', 'Unknown')
go
-- this is the real date 1 Jan 1753
insert into dbo.DimDate values (2, '1753-01-01', '01 Jan 1753')
go
create table dbo.DimTime (TimeID int, TimeValue time, DisplayTime nvarchar(20))
go
-- this is an unknown time; 00:00 is only there because we need some valid time value
insert into dbo.DimTime values (1, '00:00', 'Unknown')
go
-- this is the real time value for midnight
insert into dbo.DimTime values (2, '00:00', 'Midnight')
go
Of course, this assumes that your reporting tool and users are using columns DisplayDate
and DisplayTime
for filtering instead of columns DateValue
and TimeValue
, but this is just a matter of training and standards and such a decision you make should be understood anyway.
There are other alternatives, such as a flag column for unknown values, or the convention that negative TimeID
indicates an unknown value. But they are less obvious and harder to maintain than the explicit string value in my opinion.
source to share
Just create records DimTime
with a technical surrogate key -1
and fill in the time column with a value 00:59:59.9999999
. Thus, it will be the least likely time ever recorded (precision to the last digit) by your DWH, it will always be equivalent to unknown in your reports or queries when you want to put a filter, for example
EventTime < @ReportTime AND EventTime <> '00:59:59.9999999'
Hope this is a viable solution to your problem.
source to share