Creating a PowerBI report with DirectQuery and multiple SQL Database sources using Elastic Query
This post explains how to create a PowerBI report which sources data from two separate Azure SQL Databases.
PowerBI offers two data access mechanisms; Import and DirectQuery.
DirectQuery provides a range of benefits, the chief of which is that data is automatically refreshed. DirectQuery doesn't import any data into PowerBI, instead it queries the data sources as you interact with visualisations, making it the best option for larger datasets.
However, it is only possible to connect to one SQL Database as a data source when you use DirectQuery. If you try to add a second database as a data source, you'll be notified that it would be necessary to switch to Import mode to continue. This is the case even if both databases are on the same server.
Azure SQL Database Elastic Query provides a way round this problem. It allows you to run T-SQL queries across multiple databases, though adding an external table to one of the databases, which draws its data from a table in the other database. By setting up an external table in a database, you can create a PowerBI report which uses DirectQuery, but can indirectly access data from another database through it.
Following the Azure Elastic Query docs, imagine that we want to create a PowerBI report, using DirectQuery, which primarily draws from an Orders database, but which also uses data from a Customers database. Without Elastic Query, you can only get data from the Orders database.
We'll set up an external table so that Customer information can be added to the report as well, without having to switch to Import mode.
If you want to follow along end to end, the first step is to create two Azure SQL Server Databases through the Azure portal. They can either be on the same or different servers.
Add a firewall rule to the server, so you can access it from your machine. Then using a tool such as SQL Management Studio, add an OrderInformation table to the Orders database, and a CustomerInformation table to the Customers database (there are scripts for this in the Azure Elastic Query docs).
Once the databases and tables are set up, the steps to set up an external table in the Orders database are as follows:
- Create a master key and scoped credential in the Orders database, using the credentials for the Customers database.
- Create an external data source using the credential created above
Location should be the full server location e.g. 'MyServer.database.windows.net'.
- Create an external table in the Orders database, which has the same schema as the CustomerInformation table in the Customers database.
If a table with the same name existed already in the Orders database, you'd have to give it a different name..
These steps let you access the data in the CustomerInformation table in the Customers database as if it were a table in the Orders database.
Obviously you can go on and add other external tables if you want more data from the other database.
Please note that to view the external table in SQL Server Management Studio, you will need version 2016 or above.
The external CustomerInformation table will now be available in your PowerBI report.
To add it, edit the report and add a new query – select the same server, and choose the external table, which will be displayed in the same way as a normal table.
You can then create a relationship between the two tables in PowerBI.
This gives you the option to show customer names rather than IDs in visualisations, and produce a nicer report.
P.s. if you followed along from the start, remember to delete the new SQL Databases and Server you created!
This is just scratching the surface of Elastic Query, which can also be used to push SQL parameters to remote databases, execute remote stored procedures or call remote functions, and refer to remote tables with a different schema.
