Forum Discussion
Terrible performance when normalizing a flat table in Power Query
Hi Anonymous,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi there,
a bit busy currently, so will keep my answer short:
1) For my experience, the only dimension table you really need in Power BI is a calendar/date-table. All other dimension tables are only needed if you have to work with mulitple fact tables that you want to filter or slice and dice centrally.
2) Unfortunately, the caching of PQ lies totally in the dark, so its often difficult to predict. BUT: If you're accessing a SQL-source, your (simple) commands should fold back to the server, meaning that for the dimension tables, the DISTINCT should be done at the SQL-server-level and not in PBI. That indicates that you have some transformations in between that stop query folding to happen. Google a bit for "Power Query Folding" or post your M-code here so that I can check it out. Otherwise, a straightforward method would be to write the SQL-command for your dimension table directly in PBI:
Anonymous - Yes, I would expect the same outcome through SSAS Tabular as I've had through Power BI when using Power Query for my normalizing process. However, I'm still going to implement the SSAS Tabular version and view the result. I'll also post the result here, once I get round to it, so others will know too.
ImkeF - Yes, with the power/speed of the in-memory vertipaq engine I can see a single large flat table as acceptable when only working with a single fact table, however the requirements may start out with a single fact table but down-the-line there could be the requirement for another fact table, thus meaning you would need dimensions. I guess it's a toss-up between creating a Star Schema up front or creating when you know you will be working with multiple fact tables (I know you know this - just saying it in case it prompts you to point anything else out).
Also, in my scenario, there are additional transformations in my fact/dimension tables (removing columns/changing types, etc..) so I guess this is preventing the 'pay once' approach when referencing the original, single flat table.
For the suggestion of creating the fact and dimensions tables up-stream and maybe saving them on the source server, it's quite often the case that I do not have credentials to save the any objects (i.e. tables) on the source server. So the approach of using the SQL Statement window in the SQL Server Connection is a good idea. It will at least prompt to practice my sql.
Thanks for your responses.
Hi Anonymous,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He