cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EVEAdmin
Helper V
Helper V

Alternative to merging tables

Hi all,

I am running 3 MS SQL servers:

  • SQL 1
  • SQL 2
  • SQL 3

On each MS SQL server, I am running a dynamic query that will select data and insert them into a table in a database dedicated to PBI. 

Then, on PBI, I am querying that table, on each server and import the data.
Therefore, in PBI, I have 3 tables, one from each server.

As final step, I merge those 3 tables in 1 single final table. I am using this final table to build the report, measures, visualizations, etc.  The current total number of records, from each of the 3 tables, is about 10 millions. The PBI file is about 150MB in size.

I'd like to ask whether this approach is best and whether there is an alternative.

Thank you.

 

1 ACCEPTED SOLUTION
EricHulshof
Solution Sage
Solution Sage

You could merge the tables on the SQL server already, and if possible even do some calculations already. The more calculations you dont have to do in Power BI the better the performance.

 


Quality over Quantity


Did I answer your question? Mark my post as a solution!


View solution in original post

7 REPLIES 7
EricHulshof
Solution Sage
Solution Sage

You could merge the tables on the SQL server already, and if possible even do some calculations already. The more calculations you dont have to do in Power BI the better the performance.

 


Quality over Quantity


Did I answer your question? Mark my post as a solution!


View solution in original post

It is actually 3 SQL servers, in 3 different locations. 

So, I would have to run the dynamic query on each server and then insert the results into the 1 SQL server, building 1 database only, where each table stores data.

Good news is the 3 MS SQL servers are linked and it should be possible to move data across the 3 servers.

That will save me from merging the 3 tables in PBI, using the Append option.

 

On each MS SQL server, I am running a dynamic query that will select data and insert them into a table in a database dedicated to PBI. 

This made me think you already had a place where the three datasets came together. 

It is also possible not to merge them but create relationships between the tables in Power BI. But if that is a valueable option depends highly on the performance you have now and the way the data is shaped in the first place. 

Why are you looking for alternatives by the way? Is the current way to slow? or do you just think it could be better?

 


Quality over Quantity


Did I answer your question? Mark my post as a solution!


@EricHulshof first of all, thank you for your help.

That is right, currently, on each MS SQL server, there is 1 PBI database where I store the dynamic query results. Then PBI, is quering each of those 3 databases and merging the 3 tables into 1 large table.

I believe this process could be optimized and be faster.

 

Probably, as you first suggested, it would be best to create a table in 1 MS SQL server and store the data, from each server, in that table. Then, PBI will only query 1 table.

As for creating a relationship between each table, what is your suggestion ?

Thank you again.

 

@EricHulshof Thank you again,

 

the more I think about, the more I am convinced that running the merge in the SQL server is the way to go, definetely a better approach. So, I accepted your first reply as the solution to this post, thank you.

 

Thankyou! 

And yes i do believe that is the best option. Also consider doing calculations there. SQL server is way faster then PBI for that kind of stuff.

 


Quality over Quantity


Did I answer your question? Mark my post as a solution!


@EricHulshof most welcome,

 

in SQL, as far as calculations go, all I need to do, on this specific report, is some GROUP BY to aggregate records by month and year. Simple enough.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.