cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HarrisonBi
New Member

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 🙂

1 ACCEPTED SOLUTION
samdthompson
Solution Sage
Solution Sage

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.

 

 

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

3 REPLIES 3
HarrisonBi
New Member

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



samdthompson
Solution Sage
Solution Sage

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.

 

 

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors