Should Date Keys be used as Numbers or Dates?

[GulianiG]

I have a composite key made up of employee ID & Date
Like 1001_20210331
Is it better to store the text key as above with long length as it helps understand date instantly or should we store the date in number form like
1001_43402

Thanks

[danextian · Accepted solution]

Hi @GulianiG 

I personally prefer 20210331 as it is easier to understand - easier understanding leads to easier troubleshooting, debugging or whatever the proper term is.  With 43402 I would have to go to Excel, paste that and change the format to date to find out that it meant Oct 29, 2018 - additional non-value adding steps. But why not just use dates instead of keys and also, I think, the bigger question is why combine employee ID  & Date? They are normally separate dimensions.

[danextian · Accepted solution]

Hi @GulianiG 

I personally prefer 20210331 as it is easier to understand - easier understanding leads to easier troubleshooting, debugging or whatever the proper term is.  With 43402 I would have to go to Excel, paste that and change the format to date to find out that it meant Oct 29, 2018 - additional non-value adding steps. But why not just use dates instead of keys and also, I think, the bigger question is why combine employee ID  & Date? They are normally separate dimensions.

[GulianiG]

@danextian Are there no performance concerns of making it longer, given that it would be stored as Text because I want a separator(_) between ID & Date
I need to combine employee ID and date because I am creating a reporting line based on an employee joining the organization till he terminates. He/ she changes multiple supervisors during the period. This lineage helps us establish the leadership hierarchy the employee was working under for a given month end

[danextian · Accepted solution]

It is on a case-to-case basis. There is no one size fits all approach. Of course, having those two combined means a higher cardinality as opposed to having just two separate columns. A higher cardinality, requires a longer refresh time as it also takes longer to write it into memory.