Forum Discussion
Post PatronAlternative 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.
7 Replies
- 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?
