Skip to main content

A look at the Analysis Services Connector

Headshot of article author Adam Saxton

NOTE: Information listed here is good as of 2/17/2015 and is subject to change.

The Analysis Services Connector is a new item with the Public Preview of Power BI.  The idea behind this is that it allows you to stream live data to use with your reports and dashboards in the cloud.  This works against a Tabular instance of Analysis Services.

If you are signing into Power BI with an account that doesn’t map to your Active Directory, such as onmicrosoft.com, you need to configure DirSync enabled via Azure Active Directory for your O365 Tenant so that we know what to set the EffectiveUserName to.

Why you might need use DirSync to connect to an on-premises Analysis Services Serverhttps://support.powerbi.com/knowledgebase/articles/505323-why-you-need-dirsync-to-connect-to-on-premises-ana

You can also look at two other posts I made regarding Azure Active Directory and DirSync.

Setting up Azure Active Directory with Office 365https://blogs.technet.com/b/powerbisupport/archive/2015/02/16/setting-up-azure-active-directory-with-office-365.aspx

Do we really need AAD DirCync?https://blogs.technet.com/b/powerbisupport/archive/2015/03/10/do-we-really-need-aad-dirsync.aspx

To get started, we need to download the Analysis Services Connector from the Power BI Site. We can do this by clicking on the cog icon in the upper right and selecting Analysis Services Connector Preview. This will prompt you to download PowerBIASConnector.exe.

SNAGHTML5478e4a

You will want to install that on the machine that is running the Tabular instance of Analysis Services.

NOTE:  If you have installed the Data Management Gateway from the existing service, this must be uninstalled before installing the Analysis Services Connector.  They cannot co-exist.

Run the PowerBIASConnector.exe setup program and go with the defaults.  There isn’t much configuration at this point.  Once it is done installing click Launch to begin the configuration wizard.

SNAGHTML54c873d

You will need to sign into your Power BI Account.  Once that is done click Next. You will then be prompted for information about your Tabular Instance.  You will need to provide the Server Name that we will connect to.  You will also need to supply an account that the Connector will use to connect to the Instance.  This must be an account that as Admin rights on the server.  In my case, I created an account within my domain called PBIConnector.  I did this so that if I need to look at a Profiler Trace, I can spot that account immediately and know what it is from.

SNAGHTML54fc64b

SNAGHTML5510bea

Once that information is entered, click Next. You can then provide a Friendly name for this connector.  This is the name that will show in the list of available connectors which we will look at in a little bit.  You can also provide a description and a friendly error message in the event something goes wrong.  Once that is entered, click Next.

SNAGHTML552f481

It should indicate that the configuration was successful.  We can then click Close.

SNAGHTML5540db1

What can we do with this now?  We obviously want to use the data that is on that Tabular Instance.  We will want to connect to that.  We can do that by going back to the Power BI Site and either clicking on Get Data or the + next to Datasets.

SNAGHTML556143f

We can then select SQL Server Analysis Services and then click Connect.

SNAGHTML5573501

We will then see a list of the different Connectors that have been configured.  You will see the name, description, server name and who published it.  In a larger organization, you may see a lot of items here.  For example, in the Microsoft Tenant, it is a big list.  You can use the text box above to filter the results if you know what you are going after.

SNAGHTML558dbac

This highlights a change from the old service.  In the old service, in order to configure a Gateway, it had to be done by a designated Administrator.  Typically your IT group.  This caused some friction on both the user and the IT group.  And, in a lot of cases, they may not know who to talk to in order to get it setup.  In the new service, if you have admin rights to the Analysis Services Instance, you can configure it yourself.  No need to reach out to someone.  As long as DirSync is enabled.

If you click on the Connector, you will then see a list of the available Models on that server that you have access to. You may see a message indicating No databases found.  Or you may see the custom error message that they supplied if there are connectivity issues.

SNAGHTML568ea35

From here, you can click on the Model you are interested in.  I will just choose the first one – Internet Sales and then click Connect.  You will then see that listed under Datasets.

SNAGHTML573aa19

I can create a new dashboard and then click on the dataset.  This will take us to the Report designer, where we can start to explore with the data.

SNAGHTML57c4808

The data is not stored in the cloud.  It is coming directly from the on premises Analysis Services Tabular Instance.

How this works?

This is where the Azure Active Directory DirSync pieces comes into play.  We will use The synced account when we are going back to the Tabular instance from the cloud.  We do this by way of using EffectiveUserName when connecting to Analysis Services.

We can see this by opening Profiler Trace and seeing the connection come across.  When we interact with the data, from a connection perspective, you will see it connect with the Account that you configured for the Connector.  In my case, this is the PBIConnector account.

SNAGHTML5808a0f

If we look closer at one of the trace events, we can see EffectiveUserName being used on the request.

SNAGHTML58537ff

The value for EffectiveUserName comes from the account information for the user in the Tenant that you are currently signed into.

Diagnostics

We already looked at one aspect of Diagnostics and that is a Profiler Trace.  It is an Analysis Services Instance, so the Profiler Trace can give us some insight into what is happening.

Another diagnostic item that is available to us is the Event Logs.  As with the old Data Management Gateway, the Analysis Services Connector makes use of the Data Management Gateway Event Log.  This is located under Applications and Services Logs.

image

This can help with seeing if there is something wrong with the Connector itself.

In the event that something else goes wrong, and you are presented with just the Activity ID/Correlation ID and a timestamp, it is important to make note of that if you do have to call Support, as we will need that to do anything.

SNAGHTML591cb99

**** UPDATE – 3/12/2015 *****

Check out the Product Team’s blog on how this works as well.  They also have an FAQ regarding certain questions.

Power BI Analysis Services Connector Deep Divehttps://blogs.msdn.com/b/powerbi/archive/2015/03/11/power-bi-analysis-services-connector-deep-dive.aspx

 

Adam W. Saxton | Microsoft Business Intelligence Support – Escalation ServicesTwitter | YouTube | Facebook