I’m importing data via SQL import to a Power BI data model. If I group the data I can save the import 100,000 rows, but it effect the way I write my DAX queries to get the correct answer (and makes them slightly more complex). I’m after the general best practice for where to group data, pre import, or post import and allowing the DAX aggregator DAX functions to work on the whole table.
I’ve tried both options and can save about 6 seconds on the load if I group in SQL, but I needed to re-write some DAX.
Advertisement
Answer
You’re sailing into waters of the question being ‘too broad’ as the correct answer will differ with different data sets etc.
You should always try and feed your dashboards with as little data as possible to answer the question being asked. This will save processing time in the dashboard itself. If you can aggragate in SQL (SQL is good at aggregating) and save yourself some load time then great. However if it makes your DAX unmaintainable (and maintainability is improtant to you) then it might not be the best.
Feeding your dashboards with as little data as possible and making your datasets as simple as possible will ensure your dashboards remain snappy.
If you put a billion rows into a dashboard you might find the engine can handle it, but if you can make that data into 15 rows you know which one is going to be more responsive.
You can find more information about best practices here.