date dimension best practice

[vbourbeau]

Hi,

Simple question today. Do you think it is a best practice to create date dimension in a dataflow in the service? 

My concern is because I have many reports and don't want to always copy the table from on to another.

Also do you have other best practice date dimension.

Thanks

[Kave]

Hi, Great to see your question/case. It's exactly the one that I have in my mind for my project.
As you mentioned, I created a DataFlow for DateDimention to be able to use it again and again in many reports in PBI Service. It seems to be the best solution for our case. By doing so, there is no need to update/change all the internal Date tables, but the original one. Very practical solution.
Just one issue, at the time of writing this comment!
It's not possible to add a column to the main Date table. The reports just get the updating / changing the current column, and no idea about the added columns. I have to download the report from Cloud into the desktop, then refreshing, and then get the added column. At last, I should upload it again into the Cloud.
Please let me know if I have any mistakes in the process.

Thanks...

[Greg_Deckler · Accepted solution]

@vbourbeau Yes, that would be a good solution as everybody could use it and it would benefit from compression if applicable. Things like Year columns, Month names and month numbers would compress. Things like date or unique keys would not obviously.

[selimovd]

Hey @vbourbeau ,

the problem with this approach is that your date table has to have the right length. If you have a dataset for a year and the calendar is 10 years, its very upsetting for me personally.

On the other hand if you have a data set of 10 years and the table is just 9 years your report will have errors.

I personally try to always use a dax table as date table. If you make it dynamic the table is always as big as the data set at it's updating with every refresh. So you don't have to deal with it in the future.

Check the following article with an example on how to do that. Change the calendar with MIN and MAX to get a dynamic calendar table:

How to create a date table in Power BI in 2 simple steps - Kohera

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[vbourbeau]

Thank you, really interesting.

For most of my reports, the date length is quite the same thing. I was thinking of a dataflow because I use a lot of columns of type " IsInLast5Years" or "IsInLast12Weeks"... And our reports are always a work in progress so often I add some columns to the date table and I can't reuse them in the other because it's created only in the first one. So I have a lot of differents date tables in a lot of reports. With dataflow I can centralize it and if I add a column each report can profit from it.

[Greg_Deckler]

@vbourbeau So, the best practice for a date table is to have it in the source database or create it in Power Query or basically anything but DAX. My understanding is that the reason is that DAX calculated tables do not benefit from columnar compression like tables coming in through Power Query queries. That being said, date tables tend to have lots of unique values anyway so the advantage may be minimal. But, in general, as a best practice is to not create data tables in DAX. That said, DAX is by far the easiest probably if you just need a simple date table. So, as with all best practices, it depends.

[vbourbeau]

Thank but is my idea to place the data table in a dataflow in power bi service is ok? As I said for a centralized and evolution reason.