Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.