Setting up Power BI project Team Collaboration & Version Control

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.

13 thoughts on “Setting up Power BI project Team Collaboration & Version Control

  1. 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.

    Like

  2. 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?

    Like

  3. 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?

    Like

    • 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.

      Like

  4. 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.

    Like

    • 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.

      Like

  5. 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.

    Like

  6. 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

    Liked by 1 person

    • 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.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s