Oh Power View, we hardly knew you.
From Microsoft support:
“The Power View button in the Reports group has been removed from the ribbon in Excel 2016 for Windows. The interactive visual experience provided by Power View is now available as part of Power BI Desktop.”
This support article goes on to say that Power View can still be turned on in Excel 2016 and is still supported. But this means that creating new projects based on Power View is now questionable. Though, the article is correct: the functionality of Power View is indeed available in Power BI. It’s essentially the same tool from a usage perspective, so if a user knows how to build dashboards in Power View, they’ll be up and running with Power Bi in minutes. Though, while building dashboards is the same, figuring out how data is refreshed and ramping up on licensing is a bit more challenging. So, following are different usage scenarios for refreshing data, starting with Power View in an Excel workbook that’s not hosted in SharePoint, and progresses though different options up to Power BI. Note, in all cases the basic scenario is the same: a dashboard that gets its data from an on-prem SQL Server database.
- If Excel with a Power View sheet is used without SharePoint, then an employee could open the Excel workbook which could be stored anywhere or even emailed to users. The workbook could be configured to automatically refresh the data from the database as the user opened the workbook.
- If Excel with a Power View sheet is used with either SharePoint on-prem or Office 365, users would be able to view the dashboard in their browser. However, the dashboard would contain the data as it existed the last time the workbook was refreshed. To refresh the workbook, a user would have to open the Excel file in Excel, refresh the data connections, and save the file.
- If everything is set up correctly and if the moon is full, users could refresh the workbook in their browser. However, the refreshed data is not saved back to the workbook. So if Sal refreshes the workbook in the browser, he’ll see the refreshed data but when Sue views the same dashboards several minutes later, it won’t have the refreshed data, and will only display the data as it existed the last time the workbook was saved.
- As an alternative to having a user opening the file and refreshing, a PowerShell script could be used.
- If SharePoint on-prem has PowerPivot for SharePoint installed, then the data connections could be set to refresh on a schedule. Note that PowerPivot for SharePoint has additional licensing and hardware requirements. If it is not already installed, installing it is not a trivial process.
- This leaves out a few technical details. For example, automatic refreshing of data sources isn’t supported for Excel workbooks that have Power View worksheets in them. But a Power View could be created in SharePoint outside of the Excel file.
- If the free version of Power BI is used, users would be able to view the dashboard in their browser. However, the dashboard would contain the data as it existed the last time the workbook was refreshed. To refresh the workbook, a licensed user would have to navigate to the Power BI site and click a link to refresh the data connection. To view the dashboard, users would only need to sign in to Office 365, and click a link to navigate to the dashboard. (The documentation is confusing on this point, but this is the behavior I’ve seen. To be clear, when I tested it, one user who had a Pro license was able to refresh the dataset, while other users who only had the free version of Power BI were able to view it).
- If everyone who is going to view the dashboard has a PowerBI Pro license, then the dashboard can be set to refresh automatically on a schedule. Users would only need to sign in to Office 365, and click a link to view the dashboards in Power BI. This is the nicest options for users. However, if users are not already licensed for PowerBI, getting licenses for all users is not a trivial cost.
- Or, as an alternative, use one of the several available methods for getting data into Azure SQL. Power BI can refresh data from Azure SQL once per day without a Pro license. This does mean having to pay for an Azure SQL instance, but that could wind up being quite a bit cheaper than paying for the Power BI Pro licenses.