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
Anonymous I hope others do chime in, its a good topic. From my understanding of things, PQ and the Vertipaq engine are two seperate entities. The issue you have rests in PQ. It hasn't even been loaded as a final model in the SSAS Vertipaq engine yet. So, I would expect the same issues with your other approach. So, again, I would push this way upstream so that all PQ has to do is connect to the data source and extract the 19 million rows from the fact table 1x, and the other tables would be much smaller as they are distinct lists of relevant values for the dimensions.
Based on my reading and understanding of PQ, it sounds like it is extracting the full 19million records for each reference, at which point you are just spending unnecessary cycles with that approach.
Maybe ImkeF can shed some additional light or opinion. Otherwise I know DataChant is an uber PQ user as well. They might have decenting opinions or other methods they know of to try.
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