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 Love the detailed post, I apologize in advance for the brevity of my response but I don't quite have the time now to go through a point by point answer. First, the break out of the Fact table to different dimensions makes sense if you need to add in additional ancillary tables that the dimensions would relate to. I don't want to get into the star vs. flat as there is a use case for both.
In your particular issue, I think it is best described in this blog by Chris Webb -> https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/ It would appear that your loading times are due to PQ not being able to store the output of the intitial query in cache to be referenced by your downstream queries. As a result, your load time explodes. In your case, I would push the switch back up stream and have seperate queries or views bulit out in the database to pull the distinct values for each dimension and not rely on "reference" in PQ because of the behavior you describe.
To me this is a "loading data or Power Query" issue, and not really a "is a star schema valid or worth it", but that could just be me picking at symantics. Hope that helps, I found the blog very insightful.
Anonymous - thanks for your response. I'm aware that you're one of the main contributors on this forum so its nice to have you chipping in. The link you provided I have already seen. I like to do my research before troubling people on here. To be sure though I wanted to ask the question for my specific case.
I was hoping to have more responses before I asked this follow-up question, but being keen for the answer I'll ask the question now.
So I've explained the problem I'm facing in normalizing a Flat Table of 19 millions rows into 1 FACT and 3 DIMENSION tables. It does sound like up-streaming is a solution. I guess we should aim to have all processes as close to source as possible. This brings me on to my follow-up question.
Let's say I created an SSAS Tabular cube that executed the exact same query thus bringing into SSAS Tabular the same single Flat Table of 19 million rows. Let's then say I applied the same steps to normalized the Flat Table into 1 Fact table and 3 Dimension tables, and applied the necessary join/linking between the Fact and Dimension tables. So you can see the exact same steps are being followed in Power Query, however we are now completing the process in SSAS Tabular rather than Power BI.
Would the SSAS Tabular version being quicker? If yes, why? Or, being the same underlying technologies (i.e. Power Query and the vertipaq in-memory database engine), would the process take the same time (or in my case never completes)?
Also bear in mind here the above question is asking based on Power BI and SSAS Tabular being run from the same laptop (so the same hardware providing the same power).
And.... if running my steps results in the same completion duration between Power BI and SSAS Tabular when being executed from the same laptop, then would the SSAS Tabular approach still be the winner as you can install SSAS Tabular on a more powerful server so it complete the steps mentioned (i.e. normalizing to a Star Schema), whereas the Power BI can only be run on, less powerful, end-users laptop?
Phew.... apologies for the questions but I wanted to cover each angle.
Again, it would be great to have plenty of feedback/experiences shared on this post/topic. Thanks.
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:
