Problem
What are Power PBIDS Files? Why should I use them?
Solution
Power BI brings many reporting features, tasks, and tools to end consumers allowing those consumers to self-serve their reporting needs. However, there are times when it would be more conducive for a report designer to be provided with a pre-designed connection to a data source. PBIDS files or Power BI Data Source files do just that. This setup is extremely helpful for report designers who are new to Power BI or are not familiar with the data sources that are available. Pre-defining the connections in a file allows the report creator to quickly get to what they would prefer to do, designing the report or dashboard without needing to know the connection details such as the server name. They still will be asked to authenticate for access.
Power BI Data Source File Configuration
The most opportune method to see the benefits of using PBIDS is to review several examples. However, before we get into the example, you need to be sure to download the latest version of Power BI desktop. Additionally, we will be using the WideWorldImportersDW database as a basis for our data sources; this database can be downloaded from GitHub. If you need a refresher on bringing data into Power BI please see this tip.
The first step in the process is to create the actual PBIDS files. This process is starts with create a simple text file in JSON format that outlines:
- Version
- Type of connection
- Mode
- Connection details such as the server name, protocol and database name
Nearly all available connections can be formulated into a PBIDS file except blank queries and live connections (not the same as direct query). The mode parameter defines where the query is in Direct Query (uses DirectQuery in file) or Import mode. Microsoft Provides a list of all the available Data Source connections.
For our examples we will review several potential connections including: a SQL Server table, a text file, and a website.
Power BI Data Source Configuration to SQL Server
Starting with SQL Server, the connection requires several values as shown below:
{ "version": "0.1",
"connections": [
{
"details": {
"protocol": "tds",
"address": {
"server": "server-name-here",
"database": "db-name-here (optional)",
"schema": "schema name (optional) ",
"object": "object name (optional) "
}
},
"options": {},
"mode": "Import"
}
]
}The version is set to 0.1 and nothing is immediately entered after the word connections. In the details section, the protocol for SQL Server is “tds”. The server name needs to be added and the database name, schema name, and object name can be optionally entered. Finally, mode can be either DirectQuery or Import. If the database, schema, or object is not added, it will be requested in Power BI upon startup.
{
"version": "0.1",
"connections": [
{
"details": {
"protocol": "tds",
"address": {
"server": "localhost",
"database": "WideWorldImporters",
"schema": "Sales",
"object": "Invoices"
}
},
"options": {},
"mode": "Import"
}
]
}To open a PBIDS file, you will need to open the file from Windows Explorer by double clicking on the file. For this example, you would double click on SQLServer.PBIDS.
Working with Power BI and PBIDS
After double clicking on the file, Power BI opens. Since we specified a database, a schema, and an object (a table), Power BI opens in Get Data mode with a preview of the noted object. Clicking on Load loads the data to the desktop design grid or alternately selecting Transform Data can be selected to make changes to the data set at the query level.
However, if the report designer attempts to open the PBIDS file from the File > Open menu in Power BI the below error results. Thus, all PBIDS files must be opened from Windows Explorer.
PBIDS File for a Text File Data Source
Next, let us take a look at creating a PBIDS file for a text file data source. The general settings are similar to that used by a SQL Server PBIDS connection. The version similarly uses version 0.1. The protocol is set to: file. Within the address, the path field must be set to the directory path and the file name for the data source. The mode can be set or can be removed completely.
{ "version": "0.1",
"connections": [
{
"details": {
"protocol": "file",
"address": {
"path": "C:\\Temp\\PBITextExample.txt"
}
},
"options": {},
"mode": "Import"
}
]
}You may notice the double slashes being used in the path; the double slashes have a special meaning, so an escape character must be used. Otherwise the following error will result.
If no errors occur, then the following Data Source is loaded. We can then select Load or Transform Data.
PBIDS to a Website
Finally moving to the third example of a defined PBIDS file, we will create a connection to a website. Again, the overall format is the same. Version is still 0.1 while the protocol to be used is set to http. Finally, you will need to input the URL of the website you are wanting to download data from. In this example, we are navigating to the Atlanta United site, and specifically the season stats page.
{ "version": "0.1",
"connections": [
{
"details": {
"protocol": "http",
"address": {
"url": "https://www.atlutd.com/stats/season?year=2020&group=g"
}
}
}
]
}Now the HTML based table from the website must be selected; as shown below, the Season Stats table is chosen for Loading or Transforming.
Upon selecting Load, the data set is now ready for the report designer to start using on visuals. As shown below, the HTML table from the Atlanta United site has been parsed and the individual fields (columns headers in the HTML table) can be used within a report.
Be sure once the data source file is loaded and the report designing commences, that the data source file is subsequently saved as a normal pbix file.
PBIDS Caveat
One big caveat for PBIDS files is only one data source is allowed per file. I am hoping that this restriction may be adjusted sometime in the future. Additionally, a viable alternative to using the PBIDS files is to use a template file instead, which includes all visuals and the data sources, but no data. You can find details on Power BI Template files in this tip. Power BI Template files use the PBIT extension and are designed to share a full report including all visuals and related formatting but not the actual data.
Next Steps
