Tag: SSAS

Power BI Enterprise Gateway and SSAS – What Account Should You Use?

Published February 23, 2016

With the release of the Power BI Gateway, it is now possible to publish specific data sources for the entire organization. The gateway works in both live connect (data stored on premises) and refresh (cached data stored in the Power BI service) scenarios. A variety of data sources are supported by the gateway including SQL Server Anlalysis Services (SSAS).

SSAS was previously supported by the SSAS connector which has now been deprecated in favour of the Enterprise Gateway. This certainly helps with understanding and complexity, but it does raise one particular concern around data security which you should be aware of.

When a data source is published, credentials are provided that the gateway will use to connect to the data source. All users that use the published data source will connect to it with those credentials. This proxy account will be used for all users and should therefore be a least privilege account – it should have no more access to data than needed. However, there is one data source that notably does not work this way – SSAS.

SSAS employs the EffectiveUserName feature to provide fine grained permissions to data in the model. With EffectiveUserName, the proxy account is used only to establish the initial connection to the SSAS server, and all queries are executed with the permissions of the consuming user, allowing the data to be security trimmed.

However, in order to use EffectiveUserName, the proxy account needs to have the highest permission level within Analysis Services – Administrator. This is the exact opposite of the account criteria for all other data sources. What is important is to understand these criteria in order to not unwittingly open up data to the wrong audience. If an admin level account were used as proxy for a SQL Server data source, potetially sensitive data could be exposed to the wrong users.

In a nutshell, the thing to remember is simply to always use a least privilege account for all data sources except for SSAS, which needs an admin level account.

3 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.

Analysis Services Power Pivot Mode in SQL Server 2016

Published 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.

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

Something Went Wrong When Connecting Power BI to SSAS

Published February 2, 2015

One of the more powerful features in the new Power BI preview is the ability to connect your Power BI dashboard to an on-premises instance of SQL Server Analysis Services. This is done by installing a connector on-premises that connects to bot the SSAS server(s) and to the Power BI service. Installation is straightforward, but quite often the first attempt to connect to the data results in the “oh-so-helpful” error message, “something went wrong”.

The message isn’t very helpful, unless you’re speaking with a support representative.

I have found that the problem in most cases is that the user connecting to the data does not have sufficient rights on the SSAS server, or the server does not understand who the user is. This may very well be the same user that was used to connect from the SSAS Connector back into the service, but that doesn’t matter. The problem is that SSAS does not know about that identity.

To explain, first, we need to consider how the connector is registered.

When the connector is registered, two sets of credentials are provided. One credential is used to connect the connector to the SSAS server. This is an Windows credential (typically in the form of DOMAIN\username, and it must be an Administrator on the SSAS server. The reason for this requirement is that it will be used to funnel all Connector queries to the SSAS server, and it uses the EFFECTIVEUSERNAME feature in SSAS. EFFECTIVEUSERNAME requires admin level access.

The second credential is used to connect the connector to the Power BI service. This one is used to register the connector with the service so that it can be used by dashboard authors, and isn’t extensively used afterward. This credential will be an Organizational Account (i.e. an Office 365 identity/Azure Active Directory) and needs to have enough rights to register a data source with Power BI.

Once registered, it works as follows.

When a dashboard user interacts with the dashboard, or accesses the data source, a request is sent to the connector with the credentials of the user making the request. The connector then establishes a connection with the SSAS server, using the admin credentials registered with the connector, and issues the query using the EFFECTIVEUSERNAME parameter, which basically means “run this query using the provided user’s credentials”. The user provided is the one making the request. This allows for per-user level security for Power BI, but unfortunately, it is what typically causes the error above.

The issue is that SSAS only understands Windows (NTLM and Kerberos) credentials. Without doing anything else, it has no idea what an Organizational ID is. So how can it work at all? There are two ways.

The first, is that your domain can be federated with Azure Active directory, specifically with the Azure Active directory that your Power BI (Office 365) tenant is using. Once federation is complete, your AD domain (domainxxx.com) will be registered and trusted with your internal NTLM/Kerberos domain and your users will be mapped to their Azure ID identities. SSAS will then understand who they are, and if granted permission, they will be able to access SSAS data via the Power BI dashboard. This is the only supported method, and is what should be used in a production environment. There is however another way.

If AD federation is not an option in the short term, or you simply need to get a development or demonstration environment spun up, it is possible to “hack” your active directory to allow SSAS to understand the organizational IDs. First, the AD domain is registered directly with Active directory, then the Active Directory users can be set to use that directory. The key part is that the user name in the internal AD (ie DOMAIN\xxx) must match the user portion of the Azure AD account (ie xxx@domainxxx.com). The entire procedure is outline very well by Greg Galloway in this article, and I won’t repeat it here.

Going back to the original error, it would be nice if it could be a little more descriptive. I’d be happy with “Access Denied”. These are early days, and the product is still in preview – I expect this will change. Security also may not be the only cause of this error, but it’s the only one that I’ve seen thus far.

2 Comments