Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Bob Parsons]

We are trying to setup shared files to hand off pbix report development and data refreshes with different users. Ideally, we would like to use a common OneDrive for business or a SharePoint site to take advantage of versioning and provide access to a group of people on our BI Team.
For example:

• 1^st person looks after locating and importing the data, development of the report, and then publishing to the Power BI workspace
• 2^nd person looks after updating the data in the excel file and SQL server connections, and then refreshing the pbix report. Once complete this user publishes the Workspace App for the end users.
• End users then consume the report

 

We have tried both OneDrive for business and SharePoint without much success; in both cases we sync’d the files to users computers. Problem is that the connection to the data source is pointing to the users local sync’d file and not the file on the server. Every time a different person opens the pbix file the Data source setting needs to be updated to point to their local sync’d drive.

(i.e. Source = Excel.Workbook(File.Contents(“C:UsersUserNameSharepointSiteData.xlsx”), null, true),

 

Next instead of importing the Excel file, we used the web data connection and pointed to the SharePoint server folder that contained the file. This seemed to work at first, but only for the person who originally updated the Data Source. All others were faced with ‘permission issues’ and ‘forbidden access’.

(i.e. Source = Excel.Workbook(Web.Contents(“https://UserName.sharepoint.com/SharePointSite/Documents/Data.xlsx”), null, true),)

 

 

How are others handling version control,  sharing pbix files, and data source updates / refreshes in a team environment using Power BI Pro? 

 

 

——————————
Bob Parsons
Business Intelligence Manager
Star Produce
Calgary, AB
——————————

[Hasham Niaz]

Hi Bob !

Please watch following youtube video where i describe how to parameterize data source; watch it from 2:30 – 12:00 mins;

https://youtu.be/zSkY3mAMVjQ?t=155

Also for Share point site permission, create team site & allow access to organizational users.

Regards,

——————————
Hasham Bin Niaz
Director Data & Analytics
Karachi, Pakistan
——————————
——————————————-

[Neville Howard]

Hi Bob,

We have found that we have to treat the PBIX file, the same way we do any other Office file.  

We have one server that is used for PBI development and data connections; including the data gateway.  We use folders to distinguish between dev and production.  This allows for different users to access the same PBIX file, but if opened twice, only the first user can save the file.  If the PBIX is published to a workspace, I think different pro users can publish it to ‘refresh’ it, as long as they are admin users for the workspace.  Any data source files (eg excel and text) are on a network shared drive, and the data connections (SQL, ODBC etc) are available to all users. 

We don’t have sharepoint yet, so this works for us.  It also keeps the PBIX files ‘secured’ from other users, and makes keeping PBI Desktop up to date so much easier. 

regards,

——————————
Neville Howard
Consultant
Brisbane
0732228400
——————————
——————————————-