Workspace dashboards with multiple data sources? Workspaces across tenants? Looking for input!

[stevesanda-pg]

Hi All,

I'm working with a client on authoring some dashboards using some of their datasets. They have granted me access to their cloud Sharepoint site through my company's O365 email address. I have been using Power BI Desktop to create dashboard file(s) that utilize multiple Excel files stored on their cloud Sharepoint site. The desktop app is also used to manage relationships between those data sources and fields. So far, so good.

We've hit a bit of a road bump when it comes to figuring out how these dashboards can end up in their Workplace (PowerBI.com service) and automatically update.

First, can a dashboard authored in Desktop maintain data connections and refresh automatically after it is published to a Workspace? If so, how do you update the connection credentials to Sharepoint within the published dashboard to have the sync function work correctly?

Second, in initial testing, the data sources didn't seem to update after getting published from the desktop app. Thus, I was anticipating needing to create a dataset in their Workspace and generate dashboards off of that data source. Can a dashboard in a Workspace manage multiple data sources and relationships?

Third, as mentioned, my work O365 email address is a member of a different tenant and we can't get our heads around giving me access to their Power BI Workspace. The hope was to just author the datasets and dashboards on their behalf inside their Workspace. To confirm, I would need to be a member of their "tenant" (or O365 org) in order to have authorship access within their Workspace -- is that correct that tenant-to-tenant Workspace sharing is not an option?

Let me be clear on the end goal: we simply need to get dashboards with multiple data sources (Excel on their Sharepoint) with managed relationships into their Workspace. Any suggestions on how to do this?

[collinq · Accepted solution]

HI @stevesanda-pg ,

I will answer your questions in order asked:

1. Yes, that is a MAJOR selling point to Power BI.  You create the report, then you publish it.  Then, you set up the Scheduled Refresh to update it at will (up to 8 times with thePRO license).  The key thought there is the "credentials".  If you are going to have a report run "forever" and don't want to deal with password changes or people leaving the company etc. then I recommend that you use a generic userid that has permissions to the dataset (read only)  that can be used to modify the report and publish it.

2. You need to use the SharePoint link(s) to and not a link to the data that is unique to your PC.  For example, if you are using a share on your PC, that doesn't exist in the cloud/SharePoint so the link won't work.  This document will help you get SharePoint Online List https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list#:~:text=%20Par...  or, this document on using SharePoint  https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-onedrive      And, to clarify - yes, it is common for Dashboards to use multiple data sources, perhaps even multiple reports with multiple separate data sources.

3.  After you create the report, use the generic userid as I mentioned in point #1.

You conclusion comment - yes, just do as above and you can create reports from SharePoint - the key is that link to the document being correct for the online version of the report.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[stevesanda-pg]

Thanks for your input here, @collinq!

Looking at the second link you sent, the site says:

"When you import your Power BI Desktop file from OneDrive or SharePoint Online, you load data and model information into a dataset in Power BI."

This implies that I've 1) used a SharePoint link as the data source when I added it to the Power BI Desktop file 2) saved the Power BI Desktop file somewhere in SharePoint and 3) imported the Power BI Desktop file into Power BI Service from SharePoint.

When I'm in Power BI Service, I can find Get Data > Create new content/files > Sharepoint. I've asked my client to take this route as I don't have tenant access as mentioned...

It sounds like, in the end, "Publish" from the desktop app is *not* the route to go?

[collinq]

Hi @stevesanda-pg ,

No, what I am saying is that you have to share the desktop file with them.  They have to open that file and publish it to their tenant.  BUT, the links to the locations of the files has to be correct for them.  So, you have to use the Sharepoint URL for the file(s) and not a share or something on your machine that is mapped.

It is true that when you use the SharePoint file it becomes part of the Power BI dataset.  But, that dataset and everything with that Power BI file (the .pbix) stays together.  So, whatever happens to that file, no matter where it is - it still uses those originating file(s).  And, that is why you have to have the link to the file and not through a share or something that is just open to you.

In your scenario, I do this all of the time when I can't access the Service.  I create a PBI file on my local computer.  Then, I upload it for the end user (your customer) to a share or OneDrive or whatever.  The user downloads the file to their computer from OneDrive and makes sure that the links all work for them to refresh.  Then, they publish it under their approved userid and all is well.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[stevesanda-pg]

Ok -- understood. I get that the SharePoint URLs must be correct, but the reason I was confused about publishing versus opening in Power BI Service is we've tested publishing a dashboard file and updating files from SharePoint in the past and it didn't seem to refresh. The data source files are on their SharePoint (which I do have access to, but not their Power BI Service/Workspace).

Edit: the data source files are Excel files shared in a sub folder inside their SharePoint site. FYI.

So, we will take another stab at it and focus on getting the link to the file on their SharePoint working on their end before publishing.

Thanks again, @collinq.

[collinq]

Hi again @stevesanda-pg ,

The Power BI file, no matter where it is, only updates when the Power BI file is refreshed.  Whether you do it manually or with a scheduled refresh, the data in the report only updates when told to update.  So, if the SharePoint file updates daily, I would recommend scheduling a refresh during the midnight hours to refresh the Power BI file (or, up to 8 times a day).  If you want immediate, to the second, updates you can buy the much more expensive Premium license or just have somebody hit the refresh button on the report (in the service is fine, doesn't have to be local and then published again).

If my help has been beneficial, can you please give me a Kudos or a "solution"?  Thanks!