Date table performance: SQL vs M vs DAX

[yacoubi]

Hi. I need a date dim table in my model and want to know which is preferred and why:

1. Create table in SQL
2. Create in M (Power Query)
3. Create via DAX

Thank you.

[v-eachen-msft · Accepted solution]

Hi @yacoubi ,

You could refer to this blog about sql, m and DAX:

https://sqlserverbi.blog/2018/05/12/sql-m-or-dax/

"If you have a SQL Server data warehouse, you can use SQL to create date dimension tables.  It’s usually best to unify all of the reporting data in the data warehouse or data mart to create a single version of the truth for reporting.  You can also use tools like SSIS or Azure Data Factory to build and manage these objects before the data is imported into Power BI or the Analysis Services data model."

[parry2k · Accepted solution]

@D_PBI thanks for the input. Regarding your first point on SQL, that particular use case is applicable for any dimension/fact you are bringing from backend system. That is the nature of work you have to do if you are make changes to your underline data structure, doesn't matter it is a date dimension or any other table, so not fully agree with that point. Most of the time this kind of dimension especially date dimension is very well defined upfront and very less likely there will be a change in it, if there, yes, you have to open each report and update the model. Planning planning and planning.

In my personal experience, I'm providing solutions to mid to large enterprises for the last 5 years and I don't remember any single instance where I have added new columns in the date dimension and for that very reason, I have to update the model. We cannot predict everything and that's why it is called best practice, no solution is perfect.

Just my 2 cents. 

[D_PBI]

@parry2k 
I see the OP is a year old now but I just want to add my thoughts, for confirmation or correction.

1) Although creating a Date Dimension table in SQL would mean you create/amend in a single place (so handle any change(s) just the once), which is good, you may still have to cater for any changes in M dependant on the SQL change made - i.e. if you've added a new date column, if you've SELECT * FROM that table, then the PQ Applied Steps made need manipulating to handle the extra date column brought in, which would mean still having to open each report and configure accordingly.

2) When using CALENDARAUTO() in DAX, it will scan the full range of dates used in your data model. If you were to create the Dat Dimension in SQL or M you would need to explicity specify the start and end dates which may be dynamic.
Also if you, say, set the date range to be 19000101 to 19991231 upfront but the, say, Order Date only contained dates between 19800101 and 19891231 then when you add the Date Dimension table to a visual to use as a slicer on the Order Date wouldn't the visual show the full range of between 19000101 and 19991231 - no doubt the customer would request only relevant Order Date values (i.e. 19800101 to 19891231) be shown in the visual.

3) If you wanted a Time Dimension table, the last I knew, this couldn't be completed using DAX, so you would need to complete this in SQL or M.

Any feedback (confirmation/correction/experience) on the above would be useful.

[v-eachen-msft · Accepted solution]

Hi @yacoubi ,

You could refer to this blog about sql, m and DAX:

https://sqlserverbi.blog/2018/05/12/sql-m-or-dax/

"If you have a SQL Server data warehouse, you can use SQL to create date dimension tables.  It’s usually best to unify all of the reporting data in the data warehouse or data mart to create a single version of the truth for reporting.  You can also use tools like SSIS or Azure Data Factory to build and manage these objects before the data is imported into Power BI or the Analysis Services data model."

[parry2k]

@yacoubi I don't think it matters but if you can do at source like sql, you can use the same view/table in multiple reports, but with DAX and M, you have to make sure to do this step in every report and in future, if you add another column or something, you need to change in every report, assuming each report is having its own dataset.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡