Year: 2015

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

How to Set an Hourly Refresh Schedule with Power BI

Published September 3, 2015

One of the advantages of Power BI “V2” over “V1” is the ability to refresh from data sources more frequently than once per day. Daily was the maximum in the first version of the service, and continues to be the maximum for the free version. If you have a Pro license however, refreshes can be performed more frequently. Unfortunately, it isn’t immediately obvious as to how this is done.

If we navigate to one of our datasets in Power BI, and select “Schedule Refresh” we’re presented with the user interface for setting this up (assuming that it’s a refreshable source). First we turn on scheduled refresh, and then next, we select our frequency.

Wait – there’s no “hourly” option in the dropdown. Didn’t I just say that it can be more frequent than daily? It can. You just need to be more explicit about it. The way to enable refreshes more granularly than daily is by explicitly adding the times for the refresh using the “Add another time” link.

Therefore, in order to have true hourly refresh, you will need to add 24 refresh times for the data source. That’s not all that appealing, is it? Unfortunately, 8 is all you’re allowed. I can only assume that this was by design in order to dissuade people from overtaxing the system when it isn’t required. You do get refreshes more frequently than daily, but hourly isn’t an option either.

Therefore, the minimum amount of data latency that can be achieved by using refresh with Power BI is, as of this writing 24/8 = 3 hours. Of course, this does not apply to direct connected sources.

2 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

Where Did Power View Go in Excel 2016?

Published July 29, 2015

If you’ve been using the Excel 2016 Preview or just Excel 2016 (depending on when you read this), you may have noticed that there is no longer an option to insert a Power View report into o workbook. The reason is that it has been removed from the default ribbon in Excel 2016. It used to be on the Insert tab in the Reports Section, right beside Power Map.

Power View in Excel 2013

However, opening the Insert tab in Excel 2016 reveals it to be missing.

Power View Missing in Excel 2016

Did Microsoft remove Power View from Excel? What’s going on? Power View is still very much a part of Excel; the only change is that now it is no longer a default ribbon option. The good news is that it’s simple enough to add it back in. To do so, we need to edit the ribbon. Click on File-Options, and then select Advanced Options. The ribbon editor will appear. We can add Power View to any tab that we would like, or even create a new one, but here we’re just going to add it back to the Insert menu. To do so, expand the Insert menu. Each command must be added to a group, so we need to click the “New Group” button. Next, because I don’t think anyone will want their group named “New Group”, we want to rename it. In this case, we’ll rename it to “Reports”, the way that it used to be.

Adding a new group to the Insert tab

Next, we need to add Power View into the group. The easiest way to do this is to select “Commands Not in the Ribbon” from the “Choose commands from” dropdown. It’s a long list of items to choose from, and you’ll be tempted to look under “P” for Power View. You will be disappointed. The correct command is actually to be found in the “I”s, and it is “Insert a Power View Report”. Select that option, and click the “Add” button.

Once this is complete, Power View should once again appear in the Insert tab, in the Reports section.

I have no idea why Power View has been removed from the ribbon by default. It may just be temporary given that we’re not yet at release, but it could signal some other change. In any event, if you work with both Power View and Excel 2016, you can continue to do so.

31 Comments

Connecting to Analysis Services in Power BI

Published July 24, 2015

At the moment (July 2015), SQL Server Analysis Services (SSAS) data has the widest range of connection options in Power BI. This is a good thing, but the flip side of this is that great power tends to lead to complexity. In this post, I hope to clarify some of the complexity, and clearly spell out the different data connection options for it.

To begin with, SSAS running in Tabular mode is currently the only on-premises data source that supports direct query data. When operating in this fashion, at no point is data persisted or cached in the Power BI Service. Reports exist in the service, but every interaction with the reports goes back to the SSAS on-premises server for execution, and the results are streamed live back through the service to the requesting client. Operating in this mode has several advantages. Since you are maintaining your own SSAS server, there are no total capacity limits on your data models, compared to the 250 Mb limit on most models in the Power BI service. All data is stored on-premises, so it does not count against your total overall storage limits (10 GB for Pro users). Data is served up in real time, so there is no need to wait for a refresh process. Finally, since data is persisted on-premises, there are also no data sensitivity or sovereignty issues. You can use the cloud based Power BI services, and still maintain a policy of having no sensitive data stored in the cloud, or out of country.

There is of course a cost to all of this flexibility. Live connections require a Power BI Pro license (currently $9.99 US/user/month), and anyone consuming these reports must have such a license. You must of course maintain an SSAS server, which has its own added cost, and you must also install and maintain an SSAS Connector somewhere within your environment. Power BI uses Azure Active Directory for authentication, while SSAS uses only Windows authentication. Therefore, your directory needs to be federated with AAD (same as Office 365 directory federation) in order to use it, although this excellent post by Greg Galloway describes a workaround for test scenarios.

Of course, direct query is not the only way to work with SSAS data. SSAS data can also be loaded into a data model, and refreshed on a periodic basis, like most other data sources. In this mode, the data model is loaded into the Power BI service, and is refreshed periodically through the Power BI Personal Gateway. This approach is common to all on-premises based data sources.

On the tooling front, there are currently three different tools that can be used to connect to SSAS data, with only one of them supporting both the direct query and imported modes. Let’s walk through each one.

Excel

Excel can connect to SSAS three different ways. You can use the traditional Data – From Analysis Services on the Data tab, through Power Query and through Power Pivot. The first method connects directly to the SSAS server and is unfortunately not supported at all by Power BI, even with the SSAS Connector installed.

Direct Connection to SSAS in Excel

Power Query will import data from SSAS into a workbook, a data model, or both. Power BI works with the data model, so if Power Query is used the data should be loaded into the model, and not the workbook. There is one special case where data can be loaded into the workbook, and that is if Power BI connects to the workbook and uses Excel Services instead of importing it. When a workbook, is imported, only the data model and Power Views are brought in.

Power Query SSAS Import in Excel

Finally, PowerPivot in Excel can be used directly to import data from SSAS. PowerPivot only loads data into the model, so there is no confusion here.

PowerPivot SSAS Import in Excel

Once the model is created, and visualizations created, you can either import it or connect to it in the Power BI service. Since Excel does not support the direct query mode at all, only import, the data will need to be refreshed periodically. Refresh can be performed whether the Excel file has been imported, or if it is connected through Excel Services. Reports created in Excel can work with SSAS whether it is running in Tabular Mode, or Multidimensional (OLAP) mode.

Power BI Desktop

Power BI Desktop is a client application that allows you to import, or connect to live data, create and edit data models, and produce reports. You can use it to connect to Analysis Services in both live query mode, and in imported mode. Once launched, simply select “Get Data” and then select SQL Server Analysis Services Database and click “Connect”.

The next dialog is important to determine the behaviour of your Power BI Report. Here you enter the name of the SQL Server Analysis Services Server that you need to connect to, and then the way that you can connect. You MUST be able to connect to this from your client – communication does not flow through the SSAS Gateway at this point.

SSAS Connection Options in Power BI Desktop

The first option will connect to the SSAS using Live Query Mode. IN order to use this option from Power BI, you will need to be using the SSAS Gateway somewhere on your premises. The second option will import the data into a model in the same manner that Excel does. This mode will NOT require the SSAS Gateway, but it WILL require the Personal Gateway in order to keep the data in the Power BI model refreshed.

In addition, the Live Query approach will ONLY work with SSAS Servers running in Tabular mode. If your SSAS server is running in Multidimensional mode, the second option (import) is your only choice.

Once the report is created, it can be published to the service using the publish button, or the created .pbix file can be imported via the same mechanism as Excel.

Power BI User Interface

If you have an SSAS Connector registered somewhere within your organization, you can create a connection to it directly from the Power BI user interface. The Power BI interface can only connect to Analysis Services in Live Query mode, imported models must be created using either Excel or Power BI Desktop.

In order to browse an on-premises SSAS server, first Select “Get Data” in the Power BI user interface, then select “Get” in the Databases section. Then select the “SQL Server Analysis Services” option, and click “Connect”. When you do, you’ll be presented with a screen containing all SSAS servers that have been registered with your organization.

SSAS Servers Registered with Power BI

Click on the one that you wish to connect to, and you’ll be presented with a list of data models to connect to.

SSAS Model Selection

Once selected, the model will appear in the list of Datasets in Power BI.

Clicking on it will allow you to browse the model, and to start building reports. Since the Dataset uses Live Query, there is no need to schedule any sort of a refresh, your reports will always be as fresh as the data on SSAS.

Summary

We can summarize the various SSAS connection options for the various Power BI design tools in the following table.

Design Tool	Live Query	Imported
Excel	No	Power Query Load to ModelPower Pivot
Power BI Desktop	Yes (Tabular mode)	Yes (all modes)
Power BI UI	Yes (Tabular mode)	No

I expect some of this information to change over time, but at the initial launch of Power BI V2, this is where Analysis Services fits in.