Tag: Excel Services

What’s Happening to Excel Services in SharePoint 2016

Published September 26, 2015

Excel Services is dead – long live Excel Online.

With the release of the first public preview of SharePoint Online, and the release of this TechNet article, the news rapidly went out that Excel Services was dead. And while this is technically true, there is very little cause for concern. Unfortunately, this has led to quite a bit of misunderstanding, some of which can be cleared up by reading more of the article than the parts in bold, and others by digging around a bit. The bottom line here is, there is nothing to worry about.

The first point to make is that everything that you know and love about Excel Services lives on. While Microsoft IS removing Excel Services, what’s it’s doing is reducing both complexity and confusion by moving the relevant capabilities of Excel Services to the Office Online Server. When Excel Services was first introduced in SharePoint 2007, it allowed (among other things) for Excel workbooks to be rendered and interacted with in a browser without the need to use and Excel client. When Office Web App server was introduced in SharePoint 2010, it also allowed for workbook rendering in a browser, along with all of the other Office file formats. It also allowed browser editing of those files. What it didn’t do was allow for data connections and interactions with pivot tables, etc. For this, we still needed Excel Services. In fact, in SharePoint 2013, this required an additional configuration step of turning off XLSX file rendering for OWA.

Moving the necessary capabilities to Office Online Server (this is the name for Office Web App Server) is simply a matter of consolidation and clarification, and I welcome it. However, the new server name is confusing for people, and it leads to the second big misconception, which is that Microsoft is forcing us to use Excel in the cloud to make all this work. Let me make is clear – that is not the case.

When the deprecation article was published that talked about this move to the Office Online Server, the Office Online Server was not yet available in preview (it has been released since). This fact, combined with the name “Office Online Server” led many to the conclusion was that the Office Online services would be required for browser rendering of Office documents. The important word to note in the name is “server” as opposed to “services”. The server can in fact be installed in your domain, and in fact, must be for those data connections and interactivity capabilities. I’m not sure that it’s even possible to bind an on-prem SharePoint farm to Office Online Services, but I could see that as being an interesting scenario

While this fact was not always clear, Microsoft has in fact been doubling down lately on their on-prem BI strategy – Look no further than the BI investments in SQL Server 2016. SharePoint remains the primary deployment platform for these BI investments moving forward.

10 Comments

Working with Excel Files in Power BI V2

Published August 11, 2015

In the beginning, Excel was at the center of Power BI. In the “V1” version of Power BI, the entire product was all about enabling analytics in the cloud, and Excel/Excel services was the delivery vehicle for those analytics. The authoring tools were all Excel add-ins, and the service revolved around updating data models in Excel, and allowing you to work with data models greater than 10 MB. Power BI “V2” changed that focus completely, completely removing that dependency on Office 365 and Excel. However, all of the Excel goodness that was within the “V1” product is still there, it just may have moved around a bit, and it works a little differently now.

In the early days of Power BI V1, I wrote up a post about the limitations of the product, specifically the file size limitations. Reviewing this article recently, I was actually struck by how little has changed. The maximum data model size remains 250 MB. In fact, 250 MB is the maximum size of any data model in Power BI, whether or not it originated in Excel. What has changed, rather drastically is the means of enabling this 250 MB limit for an Excel file, which we’ll get to below.

Let’s have a look at some of the major differences between Power BI “V2” and Power BI “V1” as it pertains to Excel.

More than just pretty face

In V1, Excel was almost always used as a means of presenting data. It was possible to use Excel as a data source, but the presentation of that data would inevitably be through another Excel file.

In V2, Excel is far more commonly used as a data source. The Power BI Designer, or the web interface can connect to Excel files, and then import the data into data models stored in the service. Native Power BI visualizations are then used for data presentation. These Excel files can be local, in OneDrive, or in OneDrive for Business. If the files are in OneDrive, or OneDrive for Business, the data models can be automatically refreshed when the source workbooks change. However, we can continue to work with Excel as a presentation mechanism.

If the Excel file is stored in OneDrive for Business (as opposed to OneDrive), you can connect the Power BI interface to the workbook in place. Once connected, the workbook can be viewed and interacted with through Excel Services right within the Power BI interface. To do this, from the dashboard, select “Get Data”, choose Files, select the OneDrive for Business option, select the desired workbook, and finally, select the “Connect” button”. You will then be presented with two options.

Importing data from Excel vs connecting to a workbook in place

Note that these two options will ONLY appear if the repository for the workbook is OneDrive for Business. Selecting “Import” will import the data contained in the file into a service based data model. This is the operation that will occur for all other repositories, and it uses Excel as a data source. Selecting “Connect” however connects to the workbook in place, and it adds the workbook as a report to the Power BI user interface.

Excel Workbook in the Power BI section

The workbook appears in the Reports section with a small Excel icon beside it. To view the workbook in Excel Services, click on the ellipsis to the right of the name, and select View. The workbook should load in a new window in full fidelity, and allow interaction.

No artifacts are created in the Datasets or Dashboards sections, the workbook is a self-contained unit. This is important because personal sharing can only be done through Dashboards. Therefore, Excel Services based reports can only be shared through Office 365 Groups.

Office 365 Groups

One of the biggest changes that Power BI “V1” users will notice (and need to deal with) is the fact that in order to work with Power BI in the same manner, Excel workbooks MUST be stored in a OneDrive for Business repository. With “V1”, a workbook could be stored within any SharePoint Online repository, and enabled for use with Power BI. Therefore, all “V1” users will need to move these workbooks into OneDrive repositories before the “V1” service is deprecate (Dec 31, 2015).

Every Office 365 user gets a OneDrive repository by default, and these repositories work just fine for personal use, but most current users that are using SharePoint Online will have workbooks in shared libraries in a collaboration environment. Office 365 Groups also each have their own OneDrive for Business repository, and all group members have access to that repository. Power BI V2 fully supports Groups, so this is the logical place to store all of the “V1” workbooks for collaboration purposes.

Connecting to a workbook in a Groups OneDrive is identical to the process above, but first, you need to navigate to the Group’s context in the Power BI UI. This is done by clicking on the My Workspace button (and not entirely obvious).

Groups Selector

Simply select the group and you will be working in that Group’s context.

Increased File Size

One of the biggest benefits of the original Power BI “V1” was the ability to work with workbooks that contained data models larger than 10 MB. The way that this was done was by “enabling” the workbook for Power BI as I outlined in this article. The enablement mechanism no longer exists, but the benefits are still there. The process of connecting a workbook outlined above intrinsically enables the workbook for Power BI and increases the maximum model size from 10 MB to 250 MB. In addition, the workbook can also be opened and interacted with directly from OneDrive. It is also possible to share that workbook with others in your organization, but in order to open those large workbooks in a browser, those users will need a Power BI licence.

Workbook Refresh

The ability to refresh workbooks stored in the cloud from data stored on-premises was, at the time of its introduction, the most important feature of Power BI. Each workbook would be enabled for refresh through an administrative interface, and if the data source matched a registered data source, the service would call an on-premises Data Management Gateway, which would facilitate the refresh of the workbook. This worked, but was somewhat difficult to get set up. Power BI “V2” has simplified the process tremendously. This does however mean that the process has changed.

Most refreshes are performed on a Dataset, but as mentioned above, an Excel report is self-contained, so the refresh options are available from the report itself. Simply click on the ellipsis to the right of the report and select “Schedule Refresh”.

Report refresh options

The first time this is done, you will need to enter the Data Source credentials. Once entered these will be used for subsequent refreshes. This will also need to be completed before the report can be refreshed on demand. You can also schedule the refresh time here.

If the data source is a supported cloud source, no further steps will be necessary. If it is on-premises, then it is necessary to install the Power BI Personal Gateway. This does not require administrative permissions to run (although it’s best if you do), nor does it require any special permission on the service side. The Personal Gateway is meant to be just that – personal and easy to use.

Hopefully this covers most of the major differences of working with Excel workbooks in V2 of the service. It looks like a big change, but most things are still possible, and some things significantly enhanced.

1 Comment

Schedule Data Refresh for SSAS Connected Excel Workbooks with PowerPivot for SharePoint

Published March 24, 2015

Using Excel Services, SharePoint users have been able to share workbooks that are connected to back end data since SharePoint 2007. Typically, the connection is made to SQL Server, or to Analysis services although a wide variety of sources are available. It’s also possible to publish individual components from these workbooks anywhere within the site collection through the Excel Web Access web part. Users can navigate to a dashboard page that contains all sorts of elements including an Excel chart that is connected to back end data. Well, to be precise, it was connected to back end data, the last time the workbook was saved. The workbook itself can be refreshed, but only manually.

When you open an Excel workbook in a browser through Excel services, by default, you’ll see the visualizations and any stored data in precisely the way that the workbook was when it was last saved. If you need to see more up to date data, you can select “Refresh Connections”. If (and sometimes that’s a big if) the server and connections are set up properly, the server will fetch updated data and update the workbook.

This works well enough, but the problem is that when you, or anyone else opens the workbook again, they’ll still see the old version of the workbook, and will need to manually refresh the date again. In addition, any visualizations published elsewhere on a dashboard will also continue to show old data unless manually refreshed. If the amount of data is significant, this poses a serious performance issue to the server(s). There’s also a significant usability impact in that it’s a pretty big ask of an end user to have them constantly hitting a refresh button.

To get around this issue, one option is to set the refresh options in the data connections of the workbook. Excel Services respects these options. There are two settings that we need to be aware of, periodic refresh, and refresh on open. Connection properties can be accessed within the Excel client by selecting the Data tab, choosing Connections, then highlighting the connection in question and selecting Properties.

Periodic refresh will allow the workbook to be automatically refreshed in the background while it is opened in the browser. This can be useful when the source data is changing frequently. Refresh on opening will have the greatest impact in our scenario, as it will automatically refresh the data in the workbook whenever the file is opened. This will also work with published objects (Excel Web Access web parts) – every time that the web part is opened, the data will be automatically refreshed. This solves the usability problem above because the user no longer needs to manually update the data. However, it does not affect the server load problem.

Due to the fact that the data and visualizations retain the state that they had when the workbook was last saved, it also affects search. When the search indexer runs, it will only index the data that is saved in the workbook. It has no means of refreshing the data. Finally, in addition to the load imposed on the servers by constant refreshes, if the quantity of data being refreshed is large, users can experience significant lags when loading the file. This obviously introduces another usability option. While the refresh options in Excel are helpful, they don’t fully solve the problem. What is needed is a way to automatically open the file for editing, refresh the data, and resave it to SharePoint.

If you have ever used Power Pivot for SharePoint, you know that it can do exactly that. Power Pivot for SharePoint contains two primary elements – a specialized instance of SQL Server Analysis Services that allows users to interact with workbooks that contain embedded PowerPivot models, and a SharePoint service application that among other things, keeps those embedded models refreshed. Using the PowerPivot Gallery (enabled when PowerPivot for SharePoint is installed), you can configure a workbook’s refresh options by clicking on the icon in the Gallery view, or by selecting “Manage PowerPivot Data Refresh” in the simple All Documents view.

Data Refresh options in PowerPivot Gallery View

Data Refresh options in All Documents View

Once configured, the PowerPivot for SharePoint Service will refresh the data model in the workbook on a periodic basis (no more than once per day). The service essentially opens the workbook in edit mode, refreshes all of the data connections, and saves the workbook back to the library. If versioning is enabled, it will be saved as a new version. Unfortunately, if you’re not using a PowerPivot data model, the options are unavailable. In Gallery view, the icons are simply unavailable, and while the option is available in the All Documents view, selecting it results in an error.

On the surface, it would seem that using workbooks with PowerPivot is the only option for keeping large volumes of back-end data up to date in Excel visualizations. However, there is a small loophole that you can take advantage of.

The refresh function in PowerPivot for SharePoint refreshes all of the connections in a workbook. While this option is unavailable if the workbook has no embedded PowerPivot model, when it does, it refreshes ALL of the data connections in the workbook, whether they connect to a model, a back end SSAS server, SQL server or whatever. So therefore, if you want to keep your connected data refreshed, the solution is to add a dummy PowerPivot model to your workbook.

Simply open up the PowerPivot window, import some small amount of data from an external source, and save it. Once saved, the PowerPivot refresh options will appear, and you’ll be able to schedule data refresh for your workbook. You can even deselect the refresh of the source data for your dummy model, and the other connections will work just fine.

Once your workbooks are being updated automatically, your users will be presented with up-to date data on load with no delays, all dashboard visualizations will be up to date and quick to render, and the visible data will be picked up by your search crawler. All will be well with the world.

3 Comments

How to Use a Wildcard Certificate With Office Web Apps 2013

Published October 7, 2014

There is a lot of guidance out there that indicates that it isn’t possible to use wildcard certificates with Office Web Apps 2013. Much of this stems from one of the certificate requirements listed in this Technet document:

“The FQDN in the SAN field can’t begin with an asterisk (*).”

On first blush, this would in fact rule out wildcard certificated since their SAN (Subject Alt Name) or friendly name is the FQDN of the wildcard (*.domain.com). Indeed, there is no way in the IIS manager to alter the name of the certificate . Issuing a PowerShell command the create a new Office Web Apps server farm with the wildcard name results in the certificate being invisible to the command, and you receive the following error:

However, in my experience, wildcard certificates work just fine with Office web apps. There is just a trick to getting them to work.

The problem isn’t the wildcard certificate per se, it’s the fact that the friendly name can’t contain a wildcard. All that we really need to do is to change it. It’s not immediately obvious how that’s done, but it can be done through the MMC snap in.

If the certificate is already on the server, then great, but if not, you’ll need to import it. In this example, we’re importing a certificate that’s been exported from another server, a common enough scenario for wildcard certificates. However, the origin doesn’t matter.

The first thing you’ll need to do is to run MMC (Microsoft Management Console). To do this, from a command prompt, type MMC and hit enter. Then load the Certificates snap in, selecting the Local Computer store when prompted.

The location of the certificate is important. If it’s not in the right place, the new farm command won’t see it, and you’ll receive exactly the same error as above. The certificate needs to be imported into the Personal folder of Local Computer. Right click on the Certificates folder, hover over All Tasks, and select Import. Then, go through the prompts and select the certificate to import.

Once imported, we want to change its Friendly Name. We can do that by selecting the certificate, right clicking it, and selecting Properties.

From here, it’s a simple matter of changing the friendly name from the wildcard address, to something with significantly fewer asterisks, in our case xxxxxxwildcard

Once done, click OK, and close the MMC. Your certificate now has a friendly name that you can use to create your Office Web Apps farm.

New-OfficeWebAppsFarm -InternalUrl "https://xxx.xxxxxxxx.com" -ExternalUrl "https://xxx.xxxxxxxx.com" -CertificateName "xxxxxxWildcard" –EditingEnabled

Once created, you can continue to configure your Office Web Apps farm, and then bind SharePoint to it.

While I’m on the topic, and because it comes up frequently, once you have bound your SharePoint farm to the Office Web Apps server, it’s important to turn off view rendering for Excel files if your farm uses Excel Services. This is because the Office web apps don’t support data connections in Excel files, or PowerPivot models, and data interactivity won’t work. By issuing the following command:

New-SPWOPISuppressionSetting -extension xlsx -action view

we tell the SharePoint farm to use Excel Services (which supports data connections) when viewing xlsx files, not Office Web Apps.

Finally, if this is being added to an existing farm, you’ll want to run a full crawl to repopulate your search index with the new rendering mechanism for Office documents.

14 Comments

How to Hide Objects From the Power BI Mobile Apps

Published September 13, 2013

If you’ve worked at all with the Power BI mobile app, you’ll find that it’s quite straightforward to use. What it does in essence is to render out objects from Excel workbooks in a manner that is easy to consume from a mobile device. It uses Excel Services to render the content, but it doesn’t do so in a manner that may be familiar to Excel Services users, which is to essentially replicate the spreadsheet editing environment in a browser. Instead, each named object is presented as a distinct object, and in favourites, the workbook is represented as a section.

Clicking on any of the objects opens it in full screen mode. Swiping down from the top, or right clicking on the report opens a bar at the top that allows you to navigate to any of the other objects.

By default, any named object will appear, as well as Power View reports. (It should be noted that as of right now at least, Power Map objects are not rendered by the Mobile app). However, what happens if we don’t want an object to be rendered in the application? If you use Power Query, and you follow my recommendations of loading the data directly into the data model, you’ll likely bump into the need to do this fairly quickly. Queries are named objects in the workbook, and are rendered by the mobile app.

In the screen above, there are actually 7 objects in the workbook, but the favourites view can only display 6. Three query stubs are blocking a Power View report. Not an ideal situation. What we want to do is to hide these queries, but how to do so is not immediately obvious. The BI app uses Excel Services to render the objects, and since the early days of Excel Services, Excel has had a mechanism to control what gets rendered by Excel Services.

If we open the source workbook in Excel we will see that all of the named objects are contained in worksheets named “Pivots” and “Pie” another worksheet, “Power View 1” contains the Power View report. The queries are all stored in different worksheets. In order to control what gets rendered, we first click on the “File” tab in the ribbon, and click the “Browser view options” button.

You are then presented with a dialog that allows you to determine what is rendered by Excel Services. The default is “Entire Workbook”, but you can select specific “Sheets”, or for very fine grained control, “Items in the workbook”.

In our case, we just want to turn off items in the unnamed sheets, so we deselect them. Once this is done, all that is necessary is to save the workbook back into its library, and the results become immediately apparent on a refresh of the app.

This is a bit of a brute force approach. It affects not only the mobile app, but also anyone using the workbook from a browser – those sheets will be invisible to any users using the Excel Web App. I can foresee a need to hide things from the mobile app but not from the browser. I can also see a need to control the layout of the objects, but these are early days. For now, I’ll take this approach.

1 Comment