Correct way to handle a Date and Time dimensions

[Anonymous]

Hi,

I have column which contains a value in the format of 'dd/mm/yyyy hh:mm:ss'.

I understand that creating a date dimension table is correct method. Any slicer/filter tools should be mapped to the dimension table, which in turn connects to the joining field in the fact table. I usually create the date dimension table using DAX. In this case, I need to be able to slice/filter on date and time (i.e. so rather than just filter by, say, 01/01/2017 I need to be able to filter by '01/01/2017 11:33'.

I see two solutions to this (A and B):

Solution A:

Create the Date dimension so it creates a date column with the many many values like:

01/01/2017 00:00:01

01/01/2017 00:00:02

01/01/2017 00:00:03

on so on...

But this would make the date dimension huge, however it would be a simple join between the date dimension and fact table columns with the date format of dd/mm/yy hh:mm:ss.

Solution B:

With my starting point being 'dd/mm/yyyy hh:mm:ss', I intend to separate the date and time fields. So I have a column with dd/mm/yyyy, and another column with hh:mm:ss.

I then create a Date dimension table and a Time dimension table.

Date Dimension:

01/01/2017

02/01/2017

03/01/2017

and so on...

Time dimension:

00:00:01

00:00:02

00:00:03

and so on...

and join the newly split columns to their relating date or time dimension tables.

Which is the correct method to use? Or is there another method?

And finally, which ever method to use, how do I do it?  Does anyone know of a link to explain this please?  Preferably in Power Query as it's a modelling action.

Thanks in advance.

[Anonymous · Accepted solution]

Hi Eric,

Thanks for the reponse.

The customer mentioned they would like to filter by time, as well as date, and as the time has hh:mm:ss I thought it would be nice to give them that luxury. I'm sure the customer will be happy with filtering on hh:mm alone.

So you are confirming my Solution B as the right approach. Do you have any links to explain how to do this in Power Query?  Just links to the creation of the a Date and separate Time table in Power Query. I will Google it but just in case you know of a good link.

Thanks.

[Eric_Zhang]

@Anonymous

Using individual date and time dimension tables(or maybe date, hour, minute, second dimension tables) would be a better option. You'll have to spit the datetime column in fact table into data and time columns and connect to the two dimension tables with the two tables.

By the way, why you'd like to narrow down to seconds? As in Power BI, AFAIK, I'd say there's no good slicer for time. 

[Anonymous · Accepted solution]

Hi Eric,

Thanks for the reponse.

The customer mentioned they would like to filter by time, as well as date, and as the time has hh:mm:ss I thought it would be nice to give them that luxury. I'm sure the customer will be happy with filtering on hh:mm alone.

So you are confirming my Solution B as the right approach. Do you have any links to explain how to do this in Power Query?  Just links to the creation of the a Date and separate Time table in Power Query. I will Google it but just in case you know of a good link.

Thanks.

[Anonymous · Accepted solution]

After a little Googling, this is the winner:

https://ginameronek.com/2014/10/01/its-just-a-matter-of-time-power-bi-date-time-dimension-toolkit/

It did the job so I will now close this outstanding question. Thanks.