Best practice for importing data from multiple databases

[HarrisonBi]

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 🙂

[HarrisonBi]

Thanks for your reply.

Does this mean that data is stored twice? Once, in the source table and once in the appended table?

[camargos88]

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

[samdthompson · Accepted solution]

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.