Forum Discussion

nirvana_moksh's avatar
nirvana_moksh
Impactful Individual
8 years ago

Union and Merging Slowing Performance Terribly

Hello All,   I have a report which is using 20 SQL views and I am doing few transformations on each, then disabling their load and then creating a UNION of all 20 (the business need is in such a wa...
Need Help
quentin_vigne's avatar
quentin_vigne
Solution Sage
8 years ago

nirvana_moksh

 

Did you try not doing UNION and creating relation between tables on PowerBI ? 

 

Else, are you using every column on your 20 tables ? I had the same issue, and creating view of my table with only the needed columns and keeping data only after 2013 solved my problem

nirvana_moksh's avatar
nirvana_moksh
Impactful Individual
8 years ago
While troubleshooting this issue I did delete the UNION and the load was really quick but the UNION is definitely the issue, and I do need the UNION more so for making life easier when I do the row level security. Also, after this UNION process I merge it’s data with the Power Apps data as well in this model. So having the UNION is needed regardless.
  • quentin_vigne's avatar
    quentin_vigne
    Solution Sage
    8 years ago

    Maybe you could try to import the views without union and do the relationship between table on powerbi.

    You will do the RLS after that and it will still work

     

    We don't know what your data looks like so advising can be difficult

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    could it be that the union is actually fast, but the merge with PowerApps data that you mentioned makes the whole process slow?
    have you tried loading just the union data (withouth merge) to the model? if so, how fast is it?

    • nirvana_moksh's avatar
      nirvana_moksh
      Impactful Individual
      8 years ago

      I have actually removed the Power Apps data and just kept the UNION but it still takes long. I am going to redo a few things today and see where it takes me.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I am having the same issue.  The union function has slowed down my model dramatically.  I am joining multiple tables that each calculates supervisor hierarchies at different points in time (path function).  Have you explored simply a limitation with your machine?  I noticed that my CPU usage and Memory (20 Gb) is maxed out the entire time.  Prior to adding the union function, my model would max out resources but get through jobs in a manageable amount of time.   I was wondering if anyone on this string has used virtual machines, with more power, to refresh their data models - could that be a solution?  (I am a complete newb to virtual machines and looking forward to seeing what community has to say).  I am hoping there is a model solution and not a "throw more memory at it" solution.  Thank you!