Connecting to Analysis Services in Power BI
Friday, 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.

Sharing Power BI Content with Office 365 Groups
Thursday, July 23, 2015

The Power BI sharing story got a lot clearer this week with the changes in the service that go along with General Availability. These changes included the integration with Office 365 groups, which will in my opinion, be the preferred way to share Power BI content with others.

If you’re unfamiliar with Office 365 Groups, what you need to know is that Groups is not a product per se, but really an integration mechanism that binds together multiple elements of Office 365, and as of now, Power BI. When a group is created, a number of things happen – a distribution list is created in Exchange, a Site Collection is created in SharePoint containing that Group’s OneDrive, and an Azure Active Directory group is created for membership in AAD. Now, a Power BI workspace is created for that group as well.

How Power BI works with groups

If you’ve been working with the Power BI preview already, you are familiar with the personal workspace. This is the workspace that you see when you first log into the Power BI service, and until now, the only workspace that was available. Within the personal workspace, you can create datasets, reports, and dashboards. Dashboards can be shared to the personal workspace of other people within the organization, but now you can also switch to the workspace of an Office 365 Group. To do so, click on the Workspace selector in Power BI. Initially, it will be labelled “My Workspace”.

You’ll then be able to select from any of your Office 365 groups. All groups that you are a member of should appear here automatically, you don’t need to register them. Once selected, you’ll be working within the context of that group. If it’s empty, you’ll be prompted to add data, and if not, you’ll be taken to a default dashboard. Everything that you do at this point will be done within the context of that group, and will not affect your personal workspace. In addition, everything that you do here will be visible to all members of the group that use Power BI. There is no need to “share” anything.

Sharing to the personal dashboard vs sharing via groups

Groups represent a fundamental change to sharing in Power BI. The Personal Workspace is just that, personal. It is possible to share dashboards from here with colleagues, but the assumption is that you are the only person that may make changes. A Groups workspace turns that on its head, and assumes that everything is shared by default.

When you share a dashboard from the Personal Workspace, recipients can view the dashboard, and interact with the underlying reports. There is (currently) no mechanism to allow those recipients to make changes to those reports and dashboards. However, when working in the Groups workspace, any member of the group can make changes. Any changes made are also immediately visible to all other members of the group.

Personal OneDrive vs Group One Drive

In its original incarnation, Power BI worked with Excel files stored in SharePoint Online document libraries, including OneDrive libraries. With this version, Power BI will refresh and render Excel workbooks with full fidelity as well, but now they MUST be stored in a OneDrive library. Each user receives a single OneDrive library through Office 365, and they may also have a OneDrive personal library. In addition, each group also has a OneDrive library, and these can be used as well. The way to use them is to connect to the workbook from within the Group’s workspace.

In order to connect to an Excel Workbook from the Personal Workspace, you click on “Get Data”, click the “Get” button in the Files section, and select from Local File, OneDrive – Business, or OneDrive Personal.

Selecting from Local File or OneDrive personal will import the contents of a workbook into a Power BI dataset. That dataset will be refreshable directly from OneDrive, or through the Personal Gateway if Local File was chosen. However, selecting OneDrive – Business will allow you to select your file, then give a further two options.

“Import” is the same process as OneDrive – personal, or local file – the date is imported from the workbook into the dataset. However “Connect” establishes a report connection between the Power BI service and the OneDrive file, allowing it to be rendered in the Power BI site through Excel Services.

Once this is done, the workbook will appear in the Reports section in Power BI with a small Excel icon beside it. Unlike other sources, no dataset or dashboard are created because the report is a self-contained entity.

The experience is quite similar within a Groups workspace, with one important difference – neither OneDrive-Personal nor OneDrive – Business are options.

Instead, we are presented with the Group’s OneDrive which makes sense given that we’re in the Group workspace. The group OneDrive is backed by Office 365 which means that it functions the same way as OneDrive – Business. Excel workbooks can either be imported or connected to.

Can we use Power BI with Team Sites like before?

As mentioned above, the original Power BI service rendered workbooks from any SharePoint Online document library. The new service works with OneDrive libraries only. This means that any workbooks that are currently stored in SharePoint Online and use Power BI features will need to be moved into Group based OneDrive, or personal OneDrive in order to be able to continue to take advantage of Power BI features. In other words, Groups are REALLY important to Power BI. The original Power BI for Office 365 service will continue to be available, but will shut down on December 31, 2015.

Sharing Externally

The V1 service allowed for the external sharing of workbooks through the external sharing facilities of SharePoint. However, due to licensing restrictions, the experience wasn’t optimal. If the data model was too large, the external user would not be able to open the workbook in a browser, and would instead be required to download it in its entirety in order to open it. This was because the external user would most likely not have a Power BI license. The V2 service allows users to share dashboards from their Personal Workspaces, and to collaborate fully in Group Workspaces, but there is currently no way to share Power BI content externally, or anonymously. This has been identified as a priority, but is not available yet.

I have no specific information about how this might be done, so I am free to speculate. I suspect that the Groups mechanism will be leveraged to accomplish external content sharing. At the moment, Office 365 groups do not allow for external members, but if they did, ths would solve the external sharing problem. I’m betting that this will be the approach.

Microsoft is betting a great deal on Office 365 groups, and Power BI is one of the first services to demonstrate this deep integration. If you’re already or will be invested in Power BI, I would strongly suggest that you get familiar with them.

Sharing Power BI Content with Office 365 Groups
Thursday, July 23, 2015

The Power BI sharing story got a lot clearer this week with the changes in the service that go along with General Availability. These changes included the integration with Office 365 groups, which will in my opinion, be the preferred way to share Power BI content with others.

If you’re unfamiliar with Office 365 Groups, what you need to know is that Groups is not a product per se, but really an integration mechanism that binds together multiple elements of Office 365, and as of now, Power BI. When a group is created, a number of things happen – a distribution list is created in Exchange, a Site Collection is created in SharePoint containing that Group’s OneDrive, and an Azure Active Directory group is created for membership in AAD. Now, a Power BI workspace is created for that group as well.

How Power BI works with groups

If you’ve been working with the Power BI preview already, you are familiar with the personal workspace. This is the workspace that you see when you first log into the Power BI service, and until now, the only workspace that was available. Within the personal workspace, you can create datasets, reports, and dashboards. Dashboards can be shared to the personal workspace of other people within the organization, but now you can also switch to the workspace of an Office 365 Group. To do so, click on the Workspace selector in Power BI. Initially, it will be labelled “My Workspace”.

You’ll then be able to select from any of your Office 365 groups. All groups that you are a member of should appear here automatically, you don’t need to register them. Once selected, you’ll be working within the context of that group. If it’s empty, you’ll be prompted to add data, and if not, you’ll be taken to a default dashboard. Everything that you do at this point will be done within the context of that group, and will not affect your personal workspace. In addition, everything that you do here will be visible to all members of the group that use Power BI. There is no need to “share” anything.

Sharing to the personal dashboard vs sharing via groups

Groups represent a fundamental change to sharing in Power BI. The Personal Workspace is just that, personal. It is possible to share dashboards from here with colleagues, but the assumption is that you are the only person that may make changes. A Groups workspace turns that on its head, and assumes that everything is shared by default.

When you share a dashboard from the Personal Workspace, recipients can view the dashboard, and interact with the underlying reports. There is (currently) no mechanism to allow those recipients to make changes to those reports and dashboards. However, when working in the Groups workspace, any member of the group can make changes. Any changes made are also immediately visible to all other members of the group.

Personal OneDrive vs Group One Drive

In its original incarnation, Power BI worked with Excel files stored in SharePoint Online document libraries, including OneDrive libraries. With this version, Power BI will refresh and render Excel workbooks with full fidelity as well, but now they MUST be stored in a OneDrive library. Each user receives a single OneDrive library through Office 365, and they may also have a OneDrive personal library. In addition, each group also has a OneDrive library, and these can be used as well. The way to use them is to connect to the workbook from within the Group’s workspace.

In order to connect to an Excel Workbook from the Personal Workspace, you click on “Get Data”, click the “Get” button in the Files section, and select from Local File, OneDrive – Business, or OneDrive Personal.

Selecting from Local File or OneDrive personal will import the contents of a workbook into a Power BI dataset. That dataset will be refreshable directly from OneDrive, or through the Personal Gateway if Local File was chosen. However, selecting OneDrive – Business will allow you to select your file, then give a further two options.

“Import” is the same process as OneDrive – personal, or local file – the date is imported from the workbook into the dataset. However “Connect” establishes a report connection between the Power BI service and the OneDrive file, allowing it to be rendered in the Power BI site through Excel Services.

Once this is done, the workbook will appear in the Reports section in Power BI with a small Excel icon beside it. Unlike other sources, no dataset or dashboard are created because the report is a self-contained entity.

The experience is quite similar within a Groups workspace, with one important difference – neither OneDrive-Personal nor OneDrive – Business are options.

Instead, we are presented with the Group’s OneDrive which makes sense given that we’re in the Group workspace. The group OneDrive is backed by Office 365 which means that it functions the same way as OneDrive – Business. Excel workbooks can either be imported or connected to.

Can we use Power BI with Team Sites like before?

As mentioned above, the original Power BI service rendered workbooks from any SharePoint Online document library. The new service works with OneDrive libraries only. This means that any workbooks that are currently stored in SharePoint Online and use Power BI features will need to be moved into Group based OneDrive, or personal OneDrive in order to be able to continue to take advantage of Power BI features. In other words, Groups are REALLY important to Power BI. The original Power BI for Office 365 service will continue to be available, but will shut down on December 31, 2015.

Sharing Externally

The V1 service allowed for the external sharing of workbooks through the external sharing facilities of SharePoint. However, due to licensing restrictions, the experience wasn’t optimal. If the data model was too large, the external user would not be able to open the workbook in a browser, and would instead be required to download it in its entirety in order to open it. This was because the external user would most likely not have a Power BI license. The V2 service allows users to share dashboards from their Personal Workspaces, and to collaborate fully in Group Workspaces, but there is currently no way to share Power BI content externally, or anonymously. This has been identified as a priority, but is not available yet.

I have no specific information about how this might be done, so I am free to speculate. I suspect that the Groups mechanism will be leveraged to accomplish external content sharing. At the moment, Office 365 groups do not allow for external members, but if they did, ths would solve the external sharing problem. I’m betting that this will be the approach.

Microsoft is betting a great deal on Office 365 groups, and Power BI is one of the first services to demonstrate this deep integration. If you’re already or will be invested in Power BI, I would strongly suggest that you get familiar with them.

The New Power BI Personal Gateway – Do I Need It?
Tuesday, July 14, 2015

Last week, Microsoft released the Power BI Personal Gateway. The Personal Gateway lets you keep dashboards created in the new Power BI Dashboard service updated with data from your on-premises data sources. This is important – nobody wants to manually refresh data all of the time. However, the service already updates many data sources updated automatically – when is this tool necessary? Also, there is already a refresh tool available for Power BI called the Data Management Gateway – what’s the difference between these two tools, and when would I use one versus the other? This post is an attempt to answer these and a few other questions.

To set the stage, we need to distinguish between the original Power BI service (V1) and the Power BI service released on July 24 2015 (V2 or Power BI Dashboards). The V1 service runs (or ran, depending on when you read this) as an add-on to Office 365. Among other things, this service allows Excel files with embedded Power Pivot data models to be used from Office 365. The Data Management Gateway can be connected to the service to keep those workbooks refreshed with data on a periodic basis. The new V2 version of the service removes the dependency on Office 365 and Excel. It allows users to connect directly to their data, and to use Power Query, Power View, and (essentially) PowerPivot to transform it, visualize it, and create dashboards from it. In this new model. Office 365 is simply a repository for Excel files, which become a source for both data and reports, depending on how they are connected.

In addition to refresh capabilities, the new Power BI (V2) service supports direct querying of on premises and cloud data sources. This is significantly different than data refresh. In a live connection scenario, dashboard interactions are sent back to the data sources in real time where they are executed, and the visualizations are updated through the service in real time accordingly. In a refresh scenario, a data model that exists in the Power BI service is updated from a source on a periodic basis. This refresh has been the job of the Data Management Gateway, and is also the job of the new Personal Gateway.

Architecturally, the two services can be viewed as follows:

With this in mind, let’s answer a few anticipated questions

Will I need the Power BI Personal Gateway to do live query of on premises data?

No. The Personal Gateway, like the DMG, performs a data refresh of a model that is stored within the Power BI service. Live queries are executed against on-premises data models, so in this scenario, the Personal Gateway plays no part.

If I have the DMG, do I need the Personal Gateway?

The answer to this is that it depends. Although related, the two products do different things. The DMG is responsible for keeping the data models contained within Excel workbooks and stored in SharePoint online up to date. The refresh process in this case is the equivalent of opening the Excel workbook, selecting the Refresh All Connections button, saving it back and allowing he service to update the model stored in the service. The Personal Gateway has no workbook to update, it only updates the service based model. Therefore, if you do need to keep workbooks refreshed in Office 365, you will need to use the DMG. However, if instead you upload your workbook to the new “V2″ service, you will need to use the new Personal Gateway.

Can I install the Personal Gateway and the DMG on the same machine?

No. The Personal Gateway is really an evolution of the original DMG and uses the same underlying code base. The two are incompatible and cannot be installed on the same machine. An attempt to do so will result in the following error:

If I have the SSAS Connector, do I still need the Personal Gateway to refresh data?

Yes, The SSAS Connector is a service that is installed on-premises to allow the Power BI service to perform live queries on SSAS servers. In order to keep data in a Power BI model up to date from an on-premises data source, the Personal Gateway is necessary. However, it is not currently possible to install both the Personal Gateway and the SSAS Connector on the same machine. In fact, if you attempt to do so, you will receive precisely the same error as above. The SSAS Connector is another variant of the original DMG.

Do I need to use Power BI Designer to create a refreshable model in Power BI “V2″?

No. While Power BI designer is one tool for doing this, it is not the only one. Models refreshable from on-premises data can also be created by using the Power BI user interface and connecting to Excel workbooks.

Will any data model created in Excel or Power BI Designer work with the Personal Gateway?

(note – this answer has been updated from it’s original to correct some inaccuracies. Thanks to Derek Rickard for pointing this out)

No. In order for a model to be refreshable by the Personal Gateway, it must have been created from a refreshable data source. This is a similar to the DMG which could also refresh some direct on premises data sources, but the difference is that Power Query was required to refresh anything but SQL Server or Oracle data sources. In Excel, a model can be created using PowerPivot, Power Query, or by the selection of appropriate options when importing data.

The following data sources are currently supported.

  • SQL Server
  • Oracle
  • Teradata
  • IBM DB2
  • PostgreSQL
  • Sybase
  • MySQL
  • SharePoint List
  • File (CSV, XML, Text, Excel, Access)
  • SQL Server Analysis Services Tabular models
  • Folder
  • Custom SQL/native SQL

Do I need the Personal Gateway to refresh data sources from the cloud?

No. As with Power BI “V1″, cloud based data sources can be refreshed directly from the service, with no need for a gateway. However, if your model contains data sources from both on premises and the cloud, a gateway will obviously be required. Also, as mentioned above, Power Query must have been used to acquire the data. Supported cloud data sources are:

  • Azure SQL Database
  • Azure Blob Storage
  • Azure Table Storage
  • Azure HDInsight
  • Azure Marketplace
  • Dynamics CRM Online
  • Facebook
  • Google Analytics
  • Salesforce Objects/Reports
  • OData feeds
  • Web (HTML & Web APIs)

Do I need to be an Administrator to run the Personal Gateway?

No. This is a major departure from the DMG. The DMG installed as a service, which requires administrator level permissions to do. In addition, Configuration of data sources at the service level required special permissions. The DMG was designed to be run by administrators. The new personal BI “V2″ is designed to meet the needs of both individual users, and enterprises, and correspondingly, the Personal Gateway can be run by anyone. I suppose that the word “personal” in the name should be a bit of a hint.

At install time, the system is interrogated to determine the current user’s permissions. If the permissions are sufficient, the Personal Gateway installs itself as a service, allowing full unattended operation. If permissions are insufficient, the gateway installs itself as an application. When installed in this manner, the application must be running in order for any refreshes to occur. Obviously, the user must also be logged in.

 

I’ll add more Q&A to this post as needed over the coming weeks. The coming release of the new Power BI service promises to be exciting. For more details, check out the Personal Gateway release announcement.

Analysis Services Power Pivot Mode in SQL Server 2016
Wednesday, June 24, 2015

It is fairly well known that ever since SQL Server 2012, users have been able to install SQL Server Analysis Services (SSAS) in one of two modes – either “Multidimensional or Data Mining Mode” or “Tabular Mode”. Multidimensional is the traditional SSAS mode that supports OLAP cubes, and Tabular is the new engine that supports in memory xVelocity (or PowerPivot) models. Any given instance can only run in one of the two modes, but multiple instances/modes can exist on the same server. SQL Server 2016 (available as CTP2 as of this writing) is adding a third mode to SSAS – PowerPivot Mode.

What’s PowerPivot mode all about? Well, to start with, it’s actually not new – it’s actually older than Tabular mode. It has been available since SQL Server 2008 R2, but was never installed the same way as the other two modes, and had a couple of other different names. To my mind, this has led to a great deal of confusion that this change aims to fix. What it truly is, is a special instance of Tabular mode that exists solely to support PowerPivot for SharePoint. Up until now, this instance was installed during the installation of SQL Server Power Pivot for SharePoint, as can be seen in the screen below.

From the initial release of SQL Server 2008 R2 until (but not including) SQL Server 2012 SP1, this component needed to be installed on a SharePoint server, which is to say a server that had the basic SharePoint bits installed and had been joined to the farm. This requirement led to more than a few instances of SharePoint being installed on SQL Servers, but I digress. The correct way place to install this was on a SharePoint server that was already part of the farm. When this option was selected, what actually got installed was two things. First, a special instance of Analysis Services (it didn’t really have a name at the time), and a SharePoint service application that handled automatic refresh among other things.

The problem with this is that it forced the SharePoint server in question to also perform all of the Analysis Services functions. It wasn’t possible to scale out the analysis capabilities separately from the SharePoint server(s) themselves. This changed with the combination of SharePoint 2013 and SQL Server 2012 SP1. SharePoint Server 2013 allows for Excel Services to utilize one or more instances of what was now known as Analysis Services SharePoint Mode. This can be configure through Excel Services in the Data Model configuration options.

One can add any number of SSAS servers to the service allowing you to scale out in a limitless fashion. These SSAS servers must be running in “SharePoint mode”. The problem is that it’s not immediately obvious as to how to run an SSAS server in SharePoint Mode. The way that this is done is by installing “PowerPivot for SharePoint” on a server. With SQL Server 2012 SP1, this installation no longer requires a SharePoint server. However, if it is installed on a SharePoint server its behaviour is different. When installed on a server without SharePoint, a standalone instance of Analysis Services SharePoint mode will be installed. You can then connect to it using the Excel Services configuration shown above. However, when installed on a server with SharePoint, both the SSAS SP mode instance and the service application will be installed (as with prior versions).

While this behaviour makes sense, it’s certainly not intuitively obvious as to what’s going on. The Data Model Settings in SSAS only refer to registering “SQL Server Analysis Services”, and makes no mention of SharePoint mode. Regular SSAS servers will not work for this capability. On the SQL Side, it’s also not obvious that “PowerPivot for SharePoint” is the installation option for SSAS SharePoint mode, or that there are different behaviours when installed on farm joined servers or not. Finally, the name SSAS SharePoint mode isn’t particularly descriptive – the server in question is there exclusive to support PowerPivot for SharePoint.

Given all of this, the new installation option in SQL Server 2016 becomes clear, and should go a long way to help clear up confusion. PowerPivot mode is that same specific instance of SSAS Tabular mode that was previously referred to as “Analysis Services SharePoint Mode”. To complete the picture, the language in the PowerPivot for SharePoint configuration tool has also been updated for clarity. It may not be completely consistent, but it’s easier to understand.

While none of this represents any major shifts in functionality or capability, it does help to understand the various components of the overall solution. Hopefully the language in Excel Services in SharePoint 2016 will also be updated accordingly.