cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yacoubi
Helper I
Helper I

Date table performance: SQL vs M vs DAX

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.

2 ACCEPTED SOLUTIONS
v-eachen-msft
Community Support
Community Support

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."

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

parry2k
Super User
Super User

@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. 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@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. 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

D_PBI
Helper IV
Helper IV

@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
Community Support
Community Support

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."

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

parry2k
Super User
Super User

@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.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!