Forum Discussion
Post PatronAlternative to merging tables
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.
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.
- EVEAdmin
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?- EVEAdmin
Anonymous 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.
