New application architecture advice

[mitch2300]

Hi all,

Starting my first serious PBI application and could use some advice on optimally architecting the contemplated combination of tools, as follows:

PBI Desktop - development

PBI Service - contains full model developed in PBI desktop and links to data sources stored in OneDrive

Excel - Front end reporting interface linked to model developed in desktop and stored in PBI service

OneDrive for Business - cloud file storage 

Several questions come out of this.  For example, should I use PBI service to establish connections to the OneDrive files then connect desktop to that data set for development.  When I periodically publish the model from desktop to service, are there any considerations I need to be aware of (e.g. does publishing overwrite the data connections in service?).  How should I best connect the Excel front end to PBI service?  

These are just some questions off the top of my head but I'm sure there are other issues to work through.  Any advice from others who have implemented a similar architecture would be greatly appreciated!

Best,

Mitch

[lbendlin]

Your data source is not "Excel file in OneDrive for Business" but "some file on the web that likely can be interpreted as an excel file".  There are many different ways to do that, but the "proper" way for OneDrive files is to use the Sharepoint Folder connector.

Let's assume for a second that your pbix file is equivalent to a dataset  (not entirely accurate, but close enough).  Datasets can contain any number of queries against data sources. Data sources can be on-premise (say, a company SQL server) or "in the cloud" (meaning accessible to Azure Service Bus without a gateway)  like Sharepoint Online/Onedrive.

Gateways technically work with data sources and don't really care which dataset they come from, but there is currently a weird limitation that you cannot have connections to multiple gateways in the same dataset.

Please change your data source to Sharepoint Folder and see if you still get the credentials error.

[lbendlin]

For the first question:  BOTH your desktop development and the service should point to the same OneDrive storage. If you'd wanted to re-use an existing data source that is already in the service then you treat that as a separate process.

Excellent question on the republishing.  You need to distinguish between data changes (like with dataset refreshes) and meta data changes (modifications to the report design or the data model).  In general, updating from Power BI desktop to the workspace impacts both. There are special tools like ALM toolkit that allow you more granular control

- updating a data model or design on the desktop and publishing it to the workspace MANDATES that you also re-publish the app. Otherwise the meta data will be out of sync, with broken visuals as a result

- you CAN update from the desktop to the workspace with stale/old data which will potentially overwrite the newly refreshed data on the service. This will be corrected with the next refresh which is often automatically initiated

- if you use incremental refresh then there's a whole choreography that happens when you push design changes. The first data refresh afer this will be a FULL refresh, and only the second refresh will again be incremental. Make sure to plan accordingly.

Best connection of the Excel frontend is to use "Analyze in Excel". Make sure to teach users how to set the same default filters in the Excel file as you have in the published report.

[mitch2300]

Thanks for the advice.  Interesting you zeroed right in on data connections since that is indeed the forst problem I'm having.  Established connection in desktop then published to service.  Data source is an Excel file stored in OneDrive for Business.  Power Query Source step specifies the the source as:

= Excel.Workbook(Web.Contents("https://[full path to file name]"), null, true)

This works perfectly in desktop but cannot refresh dataset in service after published thereto.  Error says "Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.".  I tried to update credentials with no luck (message siad failed to update credentials).  Ugh. 

As an aside, the only refresh option in service appears to be for the entire dataset and not components thereof.  Not sure how this works when the dataset pucblished from desktop could potentially contain several different data sources each with different credentials so the option to update credentials on service for an entire dataset wouldn't work.  I'm sure that this newbie is missing something obvious.