If you’ve been at least paying attention to my blog a little bit, you’ve probably noticed I occasionally blog about Power BI. Part of the reason I blog so often about Power BI is because that’s the topic of a whole bunch of conversations I’m having with my customers. The other reason is that the customer I’m speaking with have lots of questions about Power BI. So consider this one of my efforts at scaling myself.
View and download my latest Power BI architecture diagram here!
If you enjoy this “Power BI tips and tricks” type posts, check out my first five Power BI tips and then my second five Power BI tips. You’ll probably enjoy those if you found this post helpful. So without further ado, I present to you more Power BI tips that I truly hope you’ll find helpful.
1. Use a Dashboard as a Navigation Pane for Your BI and Reporting Content
One of the great things about Power BI Dashboards is that you can customize the link or links a tile can use. For instance, in the dashboard below you’ll notice that I have a tile titled “Dashboard Index”. This tile is being used to link to specific pages within a single report.
Adding a custom tile is easy. First navigate to the dashboard you want to customize. Next, click the + Add Tile button in the top right of the Dashboard.
Then click the type of custom tile you’d like to add. If you want to add a Dashboard index to facilitate navigation through your Power BI content like I did, select Text Box on the right. Click Next.
Enter your Title and Subtitle if you need to. Key in your text, highlight the text you’d like to use for your hyperlink, and then click the insert link button. Then just enter your link into the text box that will appear. Click Apply when you’re done.
Alternatively, you can also cause an entire tile to link to a single location. Just click the check box next to Set custom link, provide a URL, and the entire tile will link to a single location.
There’s a lot of different things you can do with your Power BI Dashboards to improve usability and add value for your users. Dashboard tiles can also be used to link to videos (YouTube or Vimeo), custom images, or other custom web content. Visit the documentation here to learn more about all the cool things you can do with Dashboard tiles.
2. Use OneDrive for Business as Source Control for your Power BI Desktop Files
A question I hear somewhat frequently is, “How do I keep source control on my Power BI Desktop files?” Sure, you could use any of the traditional source control products out there like TFS, SVN, or whatever else. But an option that you may have overlooked and may already be in your backpocket is OneDrive for Business. If your organization is using OneDrive for Business, you already have the functionality to track previous versions of documents stored in OneDrive.
Versioning is enabled by default in OneDrive for Business, but if for some reason versioning is turned off, you may be able to turn it on by using these instructions found here.
Using OneDrive, I can view previous versions of a file or even restore a previous version of a file. To view a previous version of a file, navigate to OneDrive from your Office 365 portal and find the file in which you’re interested. Right-click on the file and select Version History.
Then I will see all the previous versions of the file. In this case, the file is a .pbix file I use for customer demos.
If you want to simply download the previous version of the .pbix file (or any other file for that matter) and poke around, just click the Modified date next the version you’re interested in. This will download the previous version of the file.
If you click the drop down arrow next to the Modified date associated with a previous version of the file, you’ll expose a few options:
- View: This will allow me to view the properties of the previous version of the file, which is really not all that useful.
- Restore: This will let me make the selected version the most current version.
- Delete: This deletes a previous version of your file. Pretty self-explanatory here.
3. Disable Load to Report Option to Eliminate Loading of Duplicate Data and Reduce Power BI Model Size
If you’re querying multiple data sets and then merging the results into a single query within Power BI, you may be storing duplicate copies of your data and unnecessarily inflating the size of your Power BI model. We can configure a query within Power BI, however, to not be loaded into the model, thus reducing the size of the Power BI model.
For example, take this Power BI Desktop file. I have two queries that are each pulling from a .csv file. One query has some data related to sales in France and the other query has some data for sales in Mexico. In this example, I’m merging the query results from MX Sales into FR Sales.
This means that the results of the query MX Sales are stored in the MX Sales table in my model and the results of both queries, FR Sales and MX Sales, are stored in the FR Sales table which means I’ve got a bit of bloat in my model. If I check the size of my .pbix file, you’ll see that the file is 3,697 KB.
If I go back to the Query Editor, I can disable Enable Load for the MX Sales query. This will prevent this query from being loaded as a table in my model. The results of the query, however, will still be merged in the FR Sales query.
Once I disable Enable Load for MX Sales, you’ll see the query gray’ed out.
After I save my Power BI Desktop file, you can now see that the size of the .pbix file is significantly smaller.
And the revenue numbers for both countries are still included in the FR Sales table, as you can see here.
4. Enable Assume Referential Integrity to Improve DirectQuery Performance
A feature was added to Power BI Desktop in March last year that is worth pointing out. The feature is a setting on relationships called “Assume Referential Integrity”. This setting is exposed in your models configured to use DirectQuery and it allows you to explicitly specify that your model should assume reference integrity in the underlying tables.
To view the setting, go to Manage Relationships in Power BI Desktop, select a relationship, and click Edit.
So what does this setting do for you? Well by default, when Power BI queries two tables that have a relationship, the two tables are joined using an outer join. Take for example, this chart, which leverages two tables: FactDiscipline and DimDisciplineActionCode. These two tables share a one to many relationship.
If I take a look at the query generated by this visualization using the magic of SQL Server Profiler, I can clearly see that the two tables used are joined using a left outer join.
If I check the option to Assume Referential Integrity on the relationship between the table FactDiscipline and DimDisciplineActionCode, like so…
When I refresh the chart, I can see that instead of an outer join, the query uses an inner join:
Now depending on your data, this could provide a performance benefit generating a more efficient query. Obviously, however, if you have mismatched data between the two tables or NULL values on one side of the join, you could get an incorrect result.
Check out this link to learn more about some of the specifics of Assume Referential Integrity and some of the gotchas you could run into if you set this setting incorrectly.
5. Use On-Prem Data Gateway PerfMon Counters to Monitor Gateway Performance
There are a number of Performance Monitor counters that we can use to determine Data Gateway performance. These counters won’t tell us how long specific queries are taking, but they will give us an indication on the resources being used on the machine the Data Gateway is installed and if we should set up another Data Gateway in order to spread the workload across multiple machines and Data Gateways.
These counters can be used to measure things like the number of DirectQuery queries against on-prem data sources, the number of connections being opened on your SQL Server Analysis Services assets, or the number of failed queries per second, for example. If you’re noticing that performance is suffering while users are browsing models in the Power BI service that use a DirectQuery connection when performance is usually exceptional, then its time to take a look at the PerfMon counters for the Data Gateway.
To open Performance Monitor (PerfMon for short), do a search in Windows on the machine where the Data Gateway is installed for “perfmon”. Select Performance Monitor on the left. Then right-click within the graph area and select Add Counters.
Then browse through the list of available counters and find On-premises data gateway.
Select the counters you’re interested in and click Add. Then click OK. Now you’re ready to monitor your data gateway performance!
As you begin to roll out Power BI to more and more users within your organization, this will be a handy tool to have in your belt. I’ve had customers who have unexpectedly run into performance issues with their Power BI reports because the workloads and usage crept up on them without them realizing it. The remedy was to move the On-premises Data Gateway to another machine with more resources available.
You can read more about the Performance Counters here.
Resources
Check out my first 5 Power BI tips: https://sqldusty.com/2015/08/28/5-tips-for-powerbi/
And of course you need to check out the second 5 Power BI tips: https://sqldusty.com/2016/06/29/5-more-power-bi-tips/
This article on troubleshooting Power BI is really great and I recommend you take a look: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-tshoot/
And here’s the article on Assume Referential Integrity: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-assume-referential-integrity/
Feedback?
I hope you found this useful. Feel free to leave a comment, thought, or bit of feedback down below! Thanks for reading!
