connection to multiple sql azure database

[snifer]

As per title, my goal is to connect to multiple database SQL on Azure, I wish direct query, is it possible to connect to more database instead of 1? After connection, my goal is to combine the info between the database and displayed on a dashboard.

I'm beginner user, thanks for the help!

[v-yuezhe-msft]

@snifer,

"combine the info between the database"

Do you want to merge tables of different databases in a single query? If that is the case, it is not possible to use DirectQuery mode. In DirectQuery mode, all data should come from a single database. To work around this issue, you can consider to create a view in your data source, then connect to the view using DirectQuery in Power BI Desktop.

In addition, the similar thread you post is about how to dynamically change imported table using parameter.


Regards,
Lydia

[snifer]

what I want is to do is to read tables from the different database on Azure, and then, for example, calculate the net profit for a period from all database.

in specific every database will have a table with a column called profit, so basically, I wanna use the direct query to sum all profit column together from all database. is that possible? if yes can u explain to me how?

many thanks!

[v-yuezhe-msft · Accepted solution]

@snifer,

You would need to use import mode in Power BI Desktop and create measure to calculate all profilt.

If you persist to use DirectQuery mode, you can create a view that appending all data from these database in Azure SQL, then connect to the view in Power BI Desktop.

Regards,
Lydia

[snifer]

If you persist to use DirectQuery mode, you can create a view that appending all data from these database in Azure SQL, then connect to the view in Power BI Desktop.

thank you so much for help

could u please, if possible send me a link or guide how to do this?

[v-yuezhe-msft]

@snifer,

About how to create the view in Azure SQL, you can post question in Azure SQL database forum to get better support.

Regards,
Lydia

[snifer]

my situation looks pretty similar to this:

http://community.powerbi.com/t5/Desktop/How-to-connect-multiple-database-with-same-table-in-Power-BI...

but how to handle it is not very clear to me, can someone explain my step by step from 0 how to connected

NOTE all my database are hosted on Azure