Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
GulianiG
Helper II
Helper II

Should Date Keys be used as Numbers or Dates?

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

2 ACCEPTED SOLUTIONS
danextian
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

3 REPLIES 3
danextian
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@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

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.