Forum Discussion
Best practice for importing data from multiple databases
Hey guys,
I am looking to understand the best practices for data modelling for my Power Bi report. In my situation, we have 3 databases from the same ERP system but are differentiated by geo region. For example; one database is Asia, one is Europe and one is America. They all contain the same tables.
Essentially we want to create a report that uses a couple of tables and be able to filter by country. What is the best practice for doing this? Our Azure environment does not let us query from multiple databases (so I cannot just write an SQL query that unions these tables and add this via SQL server statement).
I understand that you can add the tables in to Power Bi 3 times for each table, add a custom column in each of them to display the country name and append them all into one table - but I am unsure this is the best way to do this.
Could you please advise me on the best practices?
Thanks 🙂
Hello, your understanding is bang on. Thats the way to do it:
1. Import the tables.
2. For each Table add the custom column: Add Column >> Custom >> [NewColumnName]"Region Name" Custom column Formula ="Name you are needing"
3. Home >> Append Queries >> Append queries as new >> Three or more tables. Select all the tables you want to add to the append, click add and click ok.
3 Replies
Hello, your understanding is bang on. Thats the way to do it:
1. Import the tables.
2. For each Table add the custom column: Add Column >> Custom >> [NewColumnName]"Region Name" Custom column Formula ="Name you are needing"
3. Home >> Append Queries >> Append queries as new >> Three or more tables. Select all the tables you want to add to the append, click add and click ok.
Thanks for your reply.
Does this mean that data is stored twice? Once, in the source table and once in the appended table?Hi HarrisonBi ,
No, you are gonna just reference the table, not duplicating it. Also you can disable the load of the base table.
https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up
