SharePoint Version Control and Power BI Report Administration
This Blog post is part 2 of our “Leveraging Sharepoint for Version Control and Report Administration” series. For part one click here.
Publishing a Report Stored in SharePoint Online
With the knowledge of using SharePoint online for version control of several types of files, Power BI has a specific potential administration strategy for your reports and possibly even your workspaces/apps. This method of administration and governance is much different than what most Power BI developers and admins are used to. For some, it can even provide some insight into how the Power Platform integrates into greater Microsoft Office Online platform.
If you are familiar with Power BI service, you have seen the “Get Data” button in many forms either on the front page, or in the left-hand navigation menu underneath the workspace menu.
When you click the Get Data Button, you are greeted with the Get Data screen that you most likely saw when opening Power BI service for the first time, as well as in the header image for this blog post. If you’ve ever participated in a Microsoft Dashboard In A Day training seminar, you’ve seen this screen and used it to bring in a file from your local desktop. To refresh your memory, this is how the Social.pbix is brought into your workspace in the training.
It is imperative that you are aware of what workspace you are operating in inside of Power BI Service not only for this walkthrough, but when administrating Power BI Service reports in general. The default workspace when opening Power BI Service is typically the personal workspace called “My Workspace.” In the example that follows, I will be operating inside of My Workspace and publishing the report there.
After clicking “Get” under the Files tab, you are shown the following screen. The option we are interested in for this experience is the SharePoint – Team Sites button.
After clicking on the SharePoint -Team Sites button, you will be prompted to enter your team site URL or if you do not have the URL handy, you can click the Connect button to view a list of the content you have access to at the Root Level.
After connecting to the appropriate SharePoint Team Site, you will be shown a Navigation tree within the same screen. Use this navigation tree to navigate to the appropriate folder, where you will see only .pbix files available to be selected. After clicking a report, the Connect Button in the upper right will be available. Click the Connect button to connect to the .pbix you are publishing.
After waiting for the report to publish, a Dashboard, Report, and Dataset will be created in the workspace that you began in.
Publishing and Administrating a Report Stored in SharePoint Online
Publishing a report that is stored in SharePoint Online creates some differences in how you republish or update the report in the future. This method of report administration and publishing provides some insight into how Power BI Service fundamentally functions. If you’ve worked with IronEdge Business Intelligence through consulting engagements or training seminars, you’ll know that we commonly state that the .pbix file in desktop is actually made up of two components, one being the Dataset that is created through Power Query, Relationship Modeling, and DAX Modeling, the other being the Report as it is viewed in the display layer, or the actual “Report.”
Using the method of publishing a report from SharePoint online, you will see the dataset was indeed split into a Report and a Dataset with the ability to edit both in Power BI Service, just like any other report. You can edit the visuals in the web editor, and even set up refresh schedules and connect the dataset to a Data Gateway.
The difference between a report that is published from Power BI Desktop, or from a local file on a machine using the Get Data button and publishing a file hosted in SharePoint online is actually in how you update/overwrite/re-publish the report. In a typical administration environment, a report developer will make edits to a report, click the Publish button and push it to the appropriate workspace. But when attempting to overwrite a dataset/report that is connected to a .pbix file that is hosted in SharePoint online, the developer is met with the following error:
The error reads “Couldn’t publish to Power BI A dataset or report named [filename] already exists and is set up with cloud refresh. You can save this file with a different name and publish again.
This error message does not do an excellent job at explaining what is going on but is essentially stating that it cannot be overwritten because the report in question is stored in a SharePoint or OneDrive location. The report must be overwritten where the report is hosted, in this case, the SharePoint Team Site.
Where SharePoint Version Control and Report Administration Meet
This is where things can get kind of cool. Because a report is configured/published from SharePoint Online, it must be replaced in the SharePoint location to be updated in the Power BI service. However, this allows for some benefits for report developers working on in-production reports, as well as the admins responsible for the apps.
As any Power BI developer will tell you: working on in-production reports can be a high wire act at times, especially when workspace access is given to the audience rather than utilizing apps as a method of separating development and production ready reports. This is where the SharePoint method of rudimentary version control really comes in handy. Since the production report is being pulled from SharePoint pushing a new version of the report to the SharePoint folder updates or re-publishes the report in the Workspace in Power BI Service.
What are the benefits?
If there is a potentially report-breaking update pushed to the SharePoint location and the stable version of the file is lost to the developer on their local desktop, the most recent stable version can instantly be rolled-back to from the SharePoint version history screen. This can save a lot of headache and time from having to rebuild or revert the changes made in the recent update, all without having to make a new version of the file clogging up your repositories.
To rollback a version of the report to a stable version, open the context menu of the report and navigate to the Version History Dialogue screen. After getting to the list of versions, you can open a new context menu by clicking the dropdown icon that appears next to the Timestamps of each version. In the dropdown context menu there will be options for View, Restore, or Delete. The View opens a new virtualized window within the same browser view of a legacy sharepoint view. Delete deletes a version, but cannot delete the most recent version. Restore operates like a rollback button, by clicking Restore and clicking on the popup message that says you are about to replace the current version you will have effectively “Rolled-Back” the version that is currently in production.
After successfully restoring, the Version History will include a new numbered version that will not have any comments unless you checked the file out before restoring. The comments for the rollback can be added after restoring the file and checking back in.
If your organization or BI Team has little access to common developer tools, or if you need a manageable starting point for administrating your new Power BI service implementation, these methods can be extremely useful to safely get off the ground with no extra overhead or procurement. There are many ways to administrate your Power BI implementation, but being organized from launch can prevent any organizational disasters. The methods outlined in this post are not meant to be a panacea for mature BI organizations, but it can be a boon for starting the Power BI journey.
If you or your team are having issues with version control or administration strategies, IronEdge Business Intelligence can help get you off the ground. Get in touch with our BI team by emailing us here. Learn more about our Business Intelligence services here.