Authoring Reports in Power BI Desktop: Do It Right
Gartner recognizes Microsoft Power BI as a leader for the fourteenth consecutive year in the 2021 Gartner Magic Quadrant for Analytics and Business Intelligence Platforms. It is, without a doubt, one of the key business intelligence (BI) tools currently in the market.
Organizations use Power BI to build robust reports and custom dashboards, making it easy to get answers to complex questions. As a self-service analytics tool, it is highly accessible by business users, and it shines in the hands of data analysts, who are often referred to as report creators.
Typically, you use Power BI Desktop (a free download from the Microsoft Store) on your workstation to create both the data model and the reports that visualize the data.
The data model is built by specifying the data sources (often tables, views, or queries of a database, or data files like CSVs or Excel workbooks), organizing the source data into tables, relating those tables together, and often creating measures that are calculations on columns in the tables. Then, after loading the data from the sources, you switch to the reports page and start making things of beauty! Finally, you publish the data model and reports to a workspace in App.PowerBI.com.
By the way, building a good data model can be harder than it looks. Imaginet can help with our Data Engineering services.However, what if there are several people who are tasked to create reports?
Maybe you each have a copy of the Power BI Desktop file (.pbix file) that was used to create the data model and contains the reports as published. Or maybe each person downloads the pbix file from App.PowerBI.com.
If it’s a big data set, then that can take a while to generate and download. Or it’s a really large file to put into a source code repository and share that way.
People who played the report creator role at least once when using Power BI Desktop presumably created a report in a new tab and likely came across one or more reports in the original data model file.
The Problems
Teams that maintain multiple reports in a single pbix file and publish them to a Power BI Workspace sooner or later hit the wall on the following problems:
Speed
When testing new measures and validating recent data, the model needs to be updated, and often a table that has millions of rows can take a long time to load. In extreme cases, you might choose to sample or restrict the data to reduce the processing time during development. This adds complexity and increases the possibility of configuration errors during publishing. (Oops, you just published the data model with only one month of data, not five years!)
Version Management
If you are working with other report creators on the same data model file, there is always a risk for one of you to overwrite each other’s changes. Some extra communication is required to manage the most recent version of the data model file and what could be added/deleted.
The Solution
To solve this problem, Microsoft allows you to connect a new Power BI file to an existing published dataset in a Power BI workspace so the authoring process can be flexible and simple.
A good practice is to create the data model without any reports first and publish it. Then, in a new file, connect to that published dataset.
The Benefits
Connecting to a dataset is perfect for ad-hoc analysis and measure validation. Reports get fresh data without loading times. The data will always be up to date when scheduled refresh is enabled. And you can take advantage of a great recent feature called DirectQuery for Power BI datasets (preview).
When you are authoring reports in a pbix file that is connected to a dataset, you will notice that the Data tab is not available as the dataset already contains all the data from various sources. The Model tab shows the table relationships, and the Report tab has all the original tables and measures from the data model (non-editable) but you can still create new measures in a single report.
You can create a pbix file for each different business domain so report creators can author and publish reports separately. The size of these pbix files is small because they contain no data (only the report definitions) so they are nicely manageable in file systems or source code repositories.
Thank you for reading this post! If you enjoyed it, I encourage you to check out some of our other content on this blog. We have a range of articles on various topics that I think you’ll find interesting. Don’t forget to subscribe to our newsletter to stay updated with all of the latest information on Imaginet’s recent successful projects
Related Articles:
- Using Microsoft Power BI – Business Data Analytics for Better Reporting
- Power BI Helper – Documenting Power BI PBIX file Details & Services
discover more
SQL Saturday Part 2: Learning About Microsoft Fabric
SQL Saturday Part 2: Learning About Microsoft Fabric February 29, 2024 I’ve been digging into Microsoft Fabric recently – well overdue, since it was first released about a year ago.…
My Trip to SQL Saturday Atlanta (BI Edition): Part 1
My Trip to SQL Saturday Atlanta (BI Edition): Part 1 February 23, 2024 Recently, I had the opportunity to attend SQL Saturday Atlanta (BI edition), a free annual event for…
Enabling BitLocker Encryption with Microsoft Intune
Enabling BitLocker Encryption with Microsoft Intune February 15, 2024 In today’s data-driven world, safeguarding sensitive information is paramount, especially with the increase in remote work following the pandemic and the…
Let’s build something amazing together
From concept to handoff, we’d love to learn more about what you are working on.
Send us a message below or call us at 1-800-989-6022.