Team file sharing and version control may be managed through Power BI workspace integration with OneDrive and SharePoint team sites. In this post, I will show you how to get started and setup a new project.
In this demonstration, I’m going to show you how to set up a workspace and a team collaboration site so that you can share Power BI files among members of your team and manage version control. In another post, we’ll discuss the nuances and the differences in version control, build processes and DevOps comparing a Business Intelligence project with an application development project – and how those are different experiences. An important lesson is to learn how to work with this tool the way that it was designed to be used rather than to try to force Power BI to work with incompatible build management and version control tools. Power BI, along with companion technologies like Analysis Services and Reporting Services, can participate in an application life cycle (ALM) and continuous integration (CI) framework but sometimes not in the way we might imagine. This post addresses a piece of that larger puzzle.
Get Started in the Office Portal
If your organization uses office 365, you have all the tools that you need. First thing is to go to the Office Portal at office.com. Click on the App menu and choose “SharePoint”. Here, we’re going to create a new site. I’m going to choose the Team Site template and this is going to be for my Contoso Sales project. We’ll give the site the name: “Contoso Sales Project” and click “Next”. The only other thing I really need to do here is add some users as owners to this site. You can add an office group or you can add users later on. I’ll go ahead and add a couple of users and we’ll finish so that creates the site in SharePoint Online. Here, you see my new site. What happens is in Azure Active Directory, a new group is created for this site and we don’t need to do anything extra. You’re going to see that here in the Power BI portal.
Create a New Workspace and Assign a OneDrive Group Alias
I’m going to create a new workspace for development. Depending on the formality of your project, you could have a DEV a TEST and a Production workspace. You can create one or perhaps two workspaces for a project. I’ll postfix the name to indicate that this is going to be my DEV workspace. In the Workspace Settings, I’m going to choose the Contoso Sales Project group that was automatically created for my site as my workspace OneDrive group. That gives every member of that group access to this shared space and gives me the ability to sync files with this workspace.
Add a Folder and Setup Sync
Let’s go back to SharePoint Online and view the team site. Since the site was just setup, you’ll see that it shows up at the top of the most recent list. The next thing that I’m going to do is add a folder that I can sync with my desktop so I can share files with other members of my team. We’ll go to the Documents library within the new site and here I’m going to add a new folder. I’ll click new and folder and we’ll give the folder a name so this is going to me my Contoso Sales Project Files folder. We’ll create that folder and then the next thing I’m going to do is configure that folder to sync with my desktop. Each member of your team can do the same thing which will enable them to put copies of the files within this synched folder onto their desktops. You always want to use Power BI Desktop to work in a local file folder. So, there’s my my synced folder on my desktop. It’s probably a good idea to add that to my Windows Quick Launch bar or add it to my Favorites so that I can get to it quickly.
I’ll put a PBIX file into this folder and in the future, I’ll go to do all of my Power BI development work. I have a file from an existing project that I’ll use for simplicity sake. I’m just going to add a .PBIX file and designate this is my dataset file. In another post, I’ll talk about separating datasets from reports – which is something that I routinely do, especially in larger more formal projects. I’ll go ahead and just copy and paste a .PBIX file and then rename that file eventually I’ll have one dataset .PBIX file that will contain all of my data, and that’s what this file is for. It currently contains a report with pages in it. When I make a change to that file, it immediately synchs – which you can see in the OneDrive app. If you just click on the OneDrive icon, you’ll see that that will sync-up. This is a fairly small file so it goes pretty quickly.
Publish a File from the Synchronized Folder
Back to the workspace: I’m on the Datasets page and I’m going to click “Get Data” which takes us to a page where we can see the Files tile. I’m going to click “Get” on that tileand that’s going to show me that there’s a new OneDrive tile with the name of the new team site. I’ll click that and it takes me to the team site where I’ll see the folder that I had created. I click that and now I see the .PBIX file.
When I choose “Connect”, that actually imports the file as a new dataset in the service. Using this method to publish the dataset will keep that file synced up and any changes I make will automatically be deployed. There is no more need to deploy updated dataset or report files from Power BI desktop. This file will remain synced-up all of the time. I can set-up a gateway, schedule refresh and all of the other things that I would normally do with a Power BI file as if I had deployed it from Desktop. Using this method, it’s going to remain synched-up through this SharePoint Online folder, which is managed through the OneDrive For Business application.
Testing and Demonstrating Synchronization
I’m going to open this file in Power BI Desktop and just make a simple change and save the file. We should see that it automatically gets synched-up. I’ll do that a couple of times and then we can go take a look at the automatic version control that takes place through SharePoint Online. OneDrive For Business is actually SharePoint Online under the hood. When you create a team site, it essentially creates a OneDrive For Business folder. That’s how all of that magic happens.
Incidentally, while synching the file – as you can see, I just got an alert and an invitation from SharePoint that welcomes me to the site. Each member of your group will automatically get an invitation like this with a link to the folder that I’ve shared with them. I’ve changed the folder view to large icons and you can see the little green check box icon that shows that that file is synced-up with my desktop.
Make sure to always open the .PBIX file from the synced folder on your computer rather than from the SharePoint site through your browser. If you make a change to the file in Power BI Desktop, you’ll see the changes in seconds to minutes, depending on the file size. It doesn’t really matter what kind of change I make… I’ll just go to the data model diagram view and make a change… I moved a table around (imagine that I’m adding a measure or adding a relationship, adding into the table anything that would constitute a change)… this flags this file and causes me to be prompted to save it. Now we go back to File Explorer and notice the little sync icon which I’m going to get for a few seconds in this case. Because it’s a small file, now it’s synced-up again.
Edit Local Files in the Synchronized Folder
After making changes, always close Power BI Desktop so the file isn’t locked by the application. That releases the file so that it can be locked momentarily by the OneDrive process and synched-up with the online service. You can watch the progress here in the OneDrive app. If this were a large file, you’d see a little progress bar and it might take a couple of minutes to synchronize. All members of your team will see the new file here in the file system afterward.
Using Version Control and Team Collaboration Features
From File Explorer, choose the “View Online” menu option to go to the SharePoint library in your browser. You can use the ellipsis menu next to the file name to check-out the file. This prevents changes by other team members while you have it locked. You can use the “Version History” menu option like I’m doing here. You can see that every single change generates a new version of the file. If I have admin access – as I do here – I could choose a version and restore or revert back to that version. You have complete control here so this gives us a really good version control story for Power BI and it gives us the ability to collaborate as members of a team.
Should this work for pbix files containing only reports that reference a published dataset? I’m able to import them into the service from a sharepoint folder and they get added to the Reports tab, but changes don’t seem to flow from the desktop version, even after onedrive finished syncing.
Hi Paul,
Thank you for your post, it made me really happy as it gives me thoughts on designing a controlled selfservice BI proces/environment. So I went ahead and gave it a shot. Unfortunately I’m getting stuck.
Initially I got stuck at the folder selection stage, the loading circle just kept circling :(.
We’ve opened a ticket for this with MS, because we couldn’t find anything in any of the office365 logs to give us a clue what was going on.
Today in response to an update on my support ticket I tried it again and I’m not even seeing the ‘OneDrive – ‘ tile anymore when I click on ‘Get Data’->’Files’
Do you have any thoughts on what the problem might be here? (for both problems actually :P)
My last?.. for today.
Regarding your statement
“In the Workspace Settings, I’m going to choose the Contoso Sales Project group that was automatically created for my site as my workspace OneDrive group. That gives every member of that group access to this shared space and gives me the ability to sync files with this workspace.”
While I understand that everyone in that group could access the report from the SharePoint site and modifications would be picked up from the Power BI service. I don’t see where that SharePoint group gets access to the Power BI workspaces by default because I referenced the group under the OneDrive setting. I have looked under the many places on the workspace where permissions can hide and I don’t see any permissions other than the people or groups I added to the workspace directly.
How can you tell if a dataset\report is being synced to the Power BI service from OneDrive or if it was published from the Power BI application. I thought I would see it in the refresh history, but I just connected a report using this process and the Refresh history\OneDrive doesn’t have any entries. Maybe it will after future updates, but is there a place I can look now to see where it was sourced from?
I have this same question. Has anyone found a way to determine the publish method for a give dataset/report?
The answer to your question is there : https://radacad.com/power-bi-get-data-from-multiple-files-in-a-folder-on-onedrive-for-business-no-gateway-needed
Great post. I am looking forward to the next post on promoting changes to QA and PROD.
? Do you update the data connection strings for QA and PROD sources in the report before deploying to those environments or after? I would guess before because you might risk a refresh firing off with the wrong environment. Or if you have deployed this before, will the existing parameters for the connection string setup on the scheduled refresh page stick, and the current parameter in the report is ignored?
Hi Phillip. I appreciate your comments and questions, and sorry that it took so long to respond. My experience has been that the OneDrive synchronization is a fairly maintenance-free experience as you leave the PBIX files in the synchronized folder and don’t make changes to offline copies and and then copy and replace the files into the folder. I’m pretty sure that parameters set in the service will be over-written so I’d check each time you make changes. I use incremental refresh policies which takes over parameter management and I don’t have to worry about it. Also, switching the connected dataset to a different published dataset PBIX will change the report ID and cause duplicate copies of the report PBIX to be published. I’ve talked to the product team and they tell me they are working on fixing this behavior.
Hi, can you help me with article on guidance to setup power bi versioning using stash tool. It’s been used in our project to maintain dynamics code.
Hi Ray, I don’t have experience with Stash but I’m sure that the same challenges exist. Only check small files into source control – maybe reports but not large datasets. Don’t compare, branch and merge PBIX files. You can use parameters to keep dataset PBIX dataset files small. Take a look at Asgeir’s presentation in my latest post.
Fantastic post. I am sure to implement this with my team the minute I get to work today. Thanks for sharing!
This is probably one of the best ways I’ve seen to handle the problem – but the real issue is that Microsoft is so hyper-focused on making Power BI an end user tool that they haven’t done anything to make the transition from SSRS to Power BI any easier for BI shops who are used to handling version control in Visual Studio with SSDT. There needs to be better ties into Azure DevOps and/or GitHub to make this whole process more seamless IMO. And we’re not even using using the PBI Service right now, we’re just trying to manage some basic PBI reports inside of Power BI Report Server, on premises. It’s a little frustrating at the moment.
Go watch this! I’m with you on the lack of formal version control, but it’s in the works!
https://myignite.techcommunity.microsoft.com/sessions/83502?source=sessions
Thanks for sharing this Jason. I’ve seen earlier versions from Nimrod and other product team leaders, and this shows that they are moving forward on the ALM capabilities in the service. It will be tough to convince all customers to use a unique cloud-based solution rather than their own versioning tools but this is indeed forward progress.
I couldn’t reply to Paul’s response below, but I spoke to one of the developers on the Product team at Ignite last month about this issue. I suggested that, whilst it’s a nice improvement for end-users who didn’t have anything, most professional BI developers are (and have been) dying to see proper GIT integration for quite some time and this solution is not actually a solution for that problem.
He told me to be patient, they are aware of it and working on it, this is just a first step on the way to making that happen.
Having said that…there are already ways to get there, we’re working on it internally as well… They’re just not very pretty or simple to design and implement.
Hi Paul, thank you for sharing.
Little thought/question about this post compare to your previous one about the version control (naming Power BI file as _v.1.x.x):
1/This one is more native and user friendly than the other one but users cannot add any notes about why they modify the file in sharepoint.
2/Since OneDrive for Business has a lot of sync issues, what is your recommendations/best practices if we want to do version control through OneDrive for Business?
3/I’m thinking about a combination of your 2 methods :
– in Power BI Desktop you still have a “Enter data” table that give you the version and the reason of the modification.
– and the file remains the same name as it is to sync to OneDrive for Business
– it will not only make the version control easier, it will also let Power BI Service benefits all the native function like RLS etc.
Best,
Dung Anh
1. I tried that versioning process with using Vx.x.x and I ran into issues when renaming it and deploying it. It might have been me. But I thought it caused the report to be duplicated with the same name. So now when I need to make changes, I copy the Prod report fro mthe service to the OneDrive DEV folder, use the power BI compare tool to make sure someone didn’t mess with the Prod report, make my changes, copy it to \QA, and once it is approved I copy it over the Prod version. Then if there are no issues with Prod after a few refreshes, I delete the DEV and QA reprots. You could save the reports from DEV and QA as templates\.PBIT and delete the PBIX file, but I think that would more of a hassle.
2. I found using an Excel spreadsheet in the DEV SharePoint site synced to the DEV workspace\workbooks works well. I believe you can edit it in the power BI service or the OneDrive folder and the changes go both ways.
Excellent Post!