Power BI supports connecting to most of the DBMS databases such as SQL Server, Oracle, Teradata, MySQL, DB2, Sybase, Snowflake, Google BigQuery, Impala and etc.
This page summarizes the steps to connect to SQL Azure and to create the following part of the sample dashboard of this series:
https://api.kontext.tech/resource/d57ee351-09b4-5a0a-b942-b64e2ac58b01
Step-by-step guide
Open Power BI Desktop and Get Data
Click the following button in the menu:
https://api.kontext.tech/resource/3ddb71fb-cb13-5e22-be3e-89b75655acd6
In the Get Data windows, choose tab Azure and then select Azure SQL database in the panel.
https://api.kontext.tech/resource/827f0568-ede5-579a-94d0-cb657184c81d
You can also select the databases you want to connect instead of SQL Azure.
Click Connect button.
Input database connection details
Input server address and database. Choose Import as Data Connectivity mode. Ensure Include relationship columns option is checked.
https://api.kontext.tech/resource/817f7ca8-c2db-5d1b-9e60-0f675e7bbe3f
Click OK button to continue.
Input credentials to connect. For SQL Azure, ensure firewall rule is configured to allow the database connection from your current IP address.
Select data tables
In the Navigator window, select the tables you want to include into this model:
https://api.kontext.tech/resource/bd309172-c368-5d24-8066-497ad231569f
In my case, I’ve selected all the tables that are required for this dashboard.
Click Load button to load data into the model.
View relationships
Click view relationships button https://api.kontext.tech/resource/a7a87f55-7532-5cde-be3c-54f37529b976 on the left side of the workspace. The relationships of the tables are shown in a diagram:
https://api.kontext.tech/resource/1b06151c-7849-5d80-8661-d67687189c61
You can add more relationships based on requirement. The following relationships are supported:
- One to many: 1:*
- Many to one: *:1
- One to one: 1:1
Create a Card chart
Card is one of the simple visualizations you can create in Power BI. It is usually used to show a single number such as total sales, total views and etc.
For more details about Card, visit the following page:
https://docs.microsoft.com/en-us/power-bi/power-bi-visualization-card
Click the following highlighted icon to drag a Card visualization into the report.
https://api.kontext.tech/resource/5fc31822-e5e6-57c6-ad3b-75476d057123
Drag one of the field from one of the table into the Fields placeholder:
https://api.kontext.tech/resource/9ab1e419-8b79-526a-83d5-30c74d9e1acf
Change the aggregation method to Count:
https://api.kontext.tech/resource/f93c3d94-e31a-50e1-bae7-9cc12602af7b
And then click Rename in the above context menu, and change it to the name you want it to be.
You can further customize the format through the Format panel:
https://api.kontext.tech/resource/25203f29-55f1-5d8d-a471-532e69e672ca
Once it is done, you will have a similar Card visualization like the following:
https://api.kontext.tech/resource/8f5f66f2-4841-5c61-9183-e2d6c20bd6dc
Create other Card visualizations
Follow the same steps to create all the Card visualizations you need. For example, the sample dashboard contains four cards of different metrics.
https://api.kontext.tech/resource/8a7d6bdb-1e3c-58fc-8d8d-b8db6f6905f6