Monitoring the SSAS Connector in the Power BI Preview

Welcome to part 3 of a 3-part series on using the SSAS Connector with the new Power BI Preview. This information is as of early March 2015 and is subject to change as the Power BI Preview evolves.

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview

Part III: Monitoring the SSAS Connector in the Power BI Preview (you are here)

What's Going On When the SSAS Connector Is Running?

As we discussed in Part 1, the SSAS Connector allows Power BI to display data that is stored in an Analysis Services Tabular model.  What's happening:

  • Reports:  Refreshed on-demand from user interaction
  • Dashboards:  Refreshed automatically by the system

A Power BI report issues queries back to SSAS Tabular model when a user interacts with the report, field list, or filter.

A Power BI dashboard issues queries back to SSAS Tabular model every 10 minutes to keep the dashboard current. It does this without a refresh schedule being enabled (which isn't necessary in Power BI for an SSAS Tabular model anyway). 

 

If you have a lot of users and a lot of dashboards, that's a lot of queries happening automatically. At this time, the 10-minute refresh interval is not configurable -- that's one of my wish list items. Most commonly I see an SSAS tabular model being refreshed daily by a job which makes Power BI refreshes every 10 minutes a slight bit of overkill. So the ability to scale this back would be very helpful to manage the SSAS server load.

Monitoring SSAS Queries Being Sent to Power BI with SQL Server Profiler

First, a quick word about Profiler. It is deprecated for the SQL Server Engine, but not officially for Analysis Services yet according to this MSDN documentation. However, many database professionals are starting to rely more on Extended Events instead of Profiler for both the DB Engine and SSAS because Extended Events are less taxing on the system and have far more events that can be captured. Since Profiler is familiar let's start with that though. In the next section we'll show Extended Events. For purposes of this first example, we'll keep it simple and use the Profiler GUI for our trace (with a Production server, you obviously want to do that cautiously so as not to significantly impact performance).

If you fire up Profiler and start a trace on the Analysis Services instance, look for three things: the NTUserName, the EffectiveUserName associated to the Query Begin event, and the ApplicationName which is Power BI.

In the above screen shot:

  • NTUserName:  This is the account with SSAS administrator permissions used when the SSAS Connector was configured. Preferably this is a domain service account, not associated to a user, with a password that doesn't expire.
  • EffectiveUserName: When the active user is also the original author, this is the active user. When the active user is viewing a dashboard that's been shared with them, the EffectiveUserName is still the original author -- not the active user.
 

I talked about the issue with EffectiveUserName and shared dashboards in Part I of this series. Am holding out hope that this behavior changes very soon so security is honored in all cases.

Monitoring SSAS Queries Being Sent to Power BI with Extended Events

Since there's not yet a GUI for setting up Extended Events in SSAS, the starting place is an XMLA query window in Management Studio. Below is an example of a small trace I let run on the SSAS server for a couple of minutes.  I did borrow Bill Anton's script as a starting point for this example. Both Mark Vallincourt and Bill Anton have much more thorough discussions about this topic if you want to read more.

The only thing I cared about this time was tracing was the QueryBegin event.  

<Create
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<ObjectDefinition>
 <Trace>
<ID>9</ID>
<Name>QueryBeginTracing</Name>
<ddl300_300:XEvent>
 <event_session 
  name="SSAS_9_10"
dispatchLatency="1"
maxEventSize="4"
maxMemory="4"
memoryPartitionMode="none"
eventRetentionMode="allowSingleEventLoss"
trackCausality="true">
<event package="AS" name="QueryBegin" />
<target package="Package0" name="event_file">
<parameter name="filename" value="SSASTrace_QueryBegin.xel" />
</target>
 </event_session>
</ddl300_300:XEvent>
 </Trace>
</ObjectDefinition>
</Create>

After the trace starts, it creates an .xel file in your SSAS log folder.

If you double-click the .xel file, it'll open for viewing in Management Studio.  Here's what it looks like when you first open it:

You can start right-clicking detail items to display as columns in the table in the top half of the screen.

SSASExtendedEvent4.png

If you expand RequestProperties, you'll see the EffectiveUserName there. So here in the Extended Events trace we have all the same information we saw in Profiler, plus more, just in a different format. You can also trace more events if you'd like of course. Info on SSAS trace events can be found here.

Oh, and don't forget to stop your traces from running.

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<TraceID>9</TraceID>
</Object>
</Delete>

If you need to double check what's running against your SSAS Tabular instance, check the Discover_Traces DMV. In the following screen shot, you can see I've got two traces running: one is my Profiler session, and the other is my Extended Events session. The Flight Recorder will be there too if you have it enabled in your SSAS server properties.

SELECT * FROM $system.discover_traces

Monitoring SSAS Server Resources Associated with the SSAS Connector

Here's where you use the same tools and techniques you use to monitor your server health, whether it be PerfMon, Activity Monitor, a third party tool, and so forth. A few things I've observed from the default installation of the SSAS Connector:

Windows Events

Events related to the SSAS Connector are classified as the "Data Management Gateway" log.

Reminder: The SSAS Connector cannot currently co-exist on the same machine as a Data Management Gateway (from Power BI for O365).  So even though the log naming of services and events are the Data Management Gateway, it's really the SSAS Connector. I guess this means the SSAS Connector is a special type of Data Management Gateway, but different than the one associated with Power BI for O365.

Windows Service

The Windows Service associated with the SSAS Connector is run by: NT SERVICE\DIAHostService.

 

Local Group

When the SSAS Connector is installed, a local group called "Data Management Gateway Users" is created. The person who installed and configured the SSAS Connector is who is initially added to the group. So in my case, I configured the SSAS Connector so I'm the person initially added to the group (even though I specified the PowerBISvcAccount credentials as the administrator). 

SSASConnector_c.png

Firewall Ports

The SSAS Connector installation sets up the following inbound rules associated to DIAHost.exe and DIAWP.exe:

Managing the Password for the SSAS Connector

Lots of organizations have security policies which dictate user passwords change on a regular interval. If the SSAS Connector were set up with a user's credentials, and the password expires, then the SSAS Connector stops functioning. That's because the ID and PW are stored by the SSAS Connector. 

 

Therefore, unless your policy dictates otherwise you may want to set up the SSAS Connector with a domain service account with a password that doesn't expire frequently. 

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview

Part III: Monitoring the SSAS Connector in the Power BI Preview (you are here)

How to Deploy Master Data Services Models Between Environments

If you plan to promote a SQL Server MDS (Master Data Services) model from Dev and/or QA to Production, there's a few particulars you need to know to get it right. In this entry I'm focusing on using the "MDSModelDeploy" utility since it offers the most flexibility. Below I will discuss a two-tier MDS environment, Development and Production, just to keep things simple.

Three Options for MDSModelDeploy

DeployClone: Appropriate for the first time you promote to a new environment, such as from Dev to Prod. You can use DeployClone to deploy structure only, or structure + data. Keeps the unique identifier (MUID) consistent between environments, which is necessary if you want to keep models in sync between Dev and Prod.

DeployUpdate: Appropriate for when you want to push an update to a new environment, such as a new column in an entity. This can deploy structure changes only (ex: a new column), or structure + data (ex: refreshing Dev from Prod occasionally). Will only work when the unique identifier (MUID) is the same between source and destination.

DeployNew: Not used that frequently; only appropriate for creating a brand new model using another model as a starting point. Creates a new unique identifier (MUID) for the new model. This is *not* the choice you want to use for promoting to Production from Dev.

Checking the Service Name

Before you do any deployments, you need to know the Service Name(s), especially if you have more than one MDS instance running on the same server. In most cases this is MDS1 if you only have one MDS web app & one MDS database running on the SQL Server instance.

MDSModelDeploy listservices

In my local environment, I use several MDS databases for testing.  From "listservices" we see that my MDS_Development is referred to as MDS3, and MDS_Production is MDS4.

Basic Steps for Using MDSModelDeploy

These steps will be standard for each of the examples in each section below. If MDS is installed locally (i.e., if the MDSModelDeploy utility exists on your local development box), you can omit some of the RDP steps. Also, if any shares to the MDS Configuration paths exist, you can simplify and omit RDP steps that way too. I went ahead and listed the most basic way to get it done, knowing you can simplify once you're familiar with where things are located.

1. Remote Desktop into source server where MDS is installed (ex: Dev). 

2. Open an elevated command prompt (CMD > Run as Administrator).

3. In the command prompt, change the directory to the path where the MDSModelDeploy utility resides.  

CD C:\Program Files\Microsoft SQL Server\120\Master Data Services\Configuration

4. Run MDSModelDeploy to generate a package from the source MDS server. Locate the deployment package on a file share (this saves you from copying and pasting it from one server's configuration folder to the other). See examples below for generating a package.

5. Remote Desktop into target server where MDS is installed (ex: Prod).

6. Open an elevated command prompt (CMD > Run as Administrator).

7. In the command prompt, change the directory to the path where the MDSModelDeploy utility resides.  

CD C:\Program Files\Microsoft SQL Server\120\Master Data Services\Configuration

8. Run MDSModelDeploy to deploy the package on the destination MDS server. This will point to the share used in step 4. See examples below for deploying a package.

9. Run validation for the model just deployed.

DECLARE @ModelName NVARCHAR(50) = 'ModelName'
DECLARE @Model_Id INT
DECLARE @UserName NVARCHAR(50) = 'Domain\User'
DECLARE @User_Id INT
DECLARE @Version_ID INT

SET @User_Id = (SELECT ID FROM mdm.tbluser u WHERE u.UserName = @UserName)
SET @Model_Id = (SELECT TOP 1 model_id FROM mdm.viw_system_schema_version WHERE Model_Name = @modelname)
SET @Version_ID = (SELECT MAX(ID) FROM mdm.viw_system_schema_version WHERE Model_ID = @Model_Id)

EXEC mdm.udpValidateModel @user_id, @Model_ID, @Version_ID, 1

10. Reinstate security for users (if a clone was performed) in the Users and Group Permissions area.

11. Double check all subscription view names are correct in the Integration Management area. I've seen the subscription views come through after a DeployClone, but with new prefixes with the model name in front which needs the prefix removed so that ETL processes can continue to use the subscription views.

Example: Initial Deployment of MDS Model

This technique uses DeployClone to ensure that Dev and Prod can be kept in sync with the same unique identifier (MUID) for the model.

Source MDS server (structure only):

MDSModelDeploy createpackage -model ModelName -service MDS1 -package "X:\Path\FileName"

Source MDS server (including data):

MDSModelDeploy createpackage -model ModelName -service MDS1 -version VERSION_1 -package "X:\Path\FileName" -includedata

Target MDS server:

MDSModelDeploy deployclone -service MDS1 -package "X:\Path\FileName.pkg"

Following the DeployClone, you'll also need to validate the model. Run the validation script from SSMS (see script in step 9 above).

You also need to reinstate permissions for users to see the model. This is done from the User and Group Permissions area in Master Data Manager.

I've noticed sometimes it takes a couple of minutes for everything to show up in the Master Data Manager web interface after the deployment.

Note that after this initial DeployClone, you will always do a DeployUpdate from this point forward. If you try to do another DeployClone you'll get an error that says "A clone of the model cannot be created. There is already a model with the same name or MUID." So in this case, do a DeployUpdate instead.

Example: Deploying MDS Model Changes

This technique uses DeployUpdate which requires the same unique identifier (MUID) for each model. This is often used for promoting a structural model change from Dev to Prod some time after an initial DeployClone was done. Or, the "includedata" option can be used for refreshing Dev from Prod occasionally (i.e., assuming data stewards have been keeping the data in Prod up to date).

Source MDS server (structure only):

MDSModelDeploy createpackage -model ModelName -service MDS1 -package "X:\Path\FileName"

Source MDS server (including data):

MDSModelDeploy createpackage -model ModelName -service MDS1 -version VERSION_1 -package "X:\Path\FileName" -includedata

Target MDS server:

MDSModelDeploy deployupdate -service MDS1 -package "X:\Path\FileName.pkg"

Following the DeployUpdate, you'll also need to validate the model. Run the validation script from SSMS (see script in step 9 above).

Note that if you did a DeployNew initially, then try to do a DeployUpdate, you'll get an error "The model cannot be updated. There is no match for a model with the same name and MUID." If this happens, you'll need to do a DeployClone from the best environment, delete the model from the other environments, deploy the clone, the re-implement any changes and retry the DeployUpdate. 

Why It's Important to Validate After MDS Deployment

After deployment with data, the members in the model are deployed but not yet validated. This can be seen in the Master Data Manager web interface:

 

After you run a validation on the model, then the members will show validated in the interface:

 

Validation like this should also occur after any staging ETL processes are run. Here's the script to run the MDS stored procedure to validate a model. Note that the two variables at the top need a value input.

DECLARE @ModelName NVARCHAR(50) = 'Product'
DECLARE @Model_Id INT
DECLARE @UserName NVARCHAR(50) = 'COATES_HPENVY\Melissa'
DECLARE @User_Id INT
DECLARE @Version_ID INT

SET @User_Id = (SELECT ID FROM mdm.tbluser u WHERE u.UserName = @UserName)
SET @Model_Id = (SELECT TOP 1 model_id FROM mdm.viw_system_schema_version WHERE Model_Name = @modelname)
SET @Version_ID = (SELECT MAX(ID) FROM mdm.viw_system_schema_version WHERE Model_ID = @Model_Id)

EXEC mdm.udpValidateModel @user_id, @Model_ID, @Version_ID, 1

Deploying Only a Subset of a Model

The MDSModelDeploy utility deploys an entire model. What if you really only want to push one entity and be extra cautious not to touch anything else? That's where the MDSPackageEditor.exe comes in handy.

You launch the MDSPackageEditor after you have created a package from the source MDS server. Then you de-select the individual elements you want to exclude, and it saves the package as a different file name. Then you'd continue on with the deployment to the destination server like normal. Using the MDSPackageEditor.exe is much easier than editing the .pkg XML file manually.

Logging MDS Deployments

By default, trace file logging of MDS deployments is not enabled. Instructions for how to enable it can be found here: Enabling Logging to Troubleshoot MDSModelDeploy.exe. (Don't forget to back up the original config file before editing it, just to be safe.)

Note that in SQL Server 2014, Cumulative Update 6 is required in order to use the trace file. Prior to CU6, a trace file doesn't get generated even if it's been enabled in the config file. This is documented here:  No MDSTrace.log file is generated after you enable tracing in the MDS 2014 web.config file. Installing CU6 did indeed solve the problem for me.

Brief example of enabling logging in the MDSModelDeploy.exe.config file:

Where to locate the MDSModelDeployTrace.log file:

What You Need to Set Up the SSAS Connector in the Power BI Preview

Welcome to part 2 of a 3-part series on using the SSAS Connector with the new Power BI Preview. This information is as of early March 2015 and is subject to change as the Power BI Preview evolves.

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview (you are here)

Part III: Monitoring the SSAS Connector in the Power BI Preview

Prerequisites for Using the SSAS Connector with Power BI

1. An SSAS Tabular Model

In order to use the SSAS Connector for Power BI, you need a SQL Server Analysis Services database defined, deployed, and processed on a SQL Server instance configured for Analysis Services in Tabular mode. Power BI is not yet able to support a Multidimensional (OLAP) model - at this time SSAS needs to be configured as a Tabular instance. SQL Server 2012 or 2014 is supported with the SSAS Connector (since Tabular mode was introduced in SQL Server 2012).

More information: Tabular Model Projects

2. Roles Defined in the SSAS Tabular Model

Within your SSAS Tabular model, at least one security role must be created to grant Read permissions to the appropriate Windows users and/or groups. Within each role, you may restrict data elements as necessary (using row filters) to enforce row-level security.

More information:  Roles (SSAS Tabular)

3. Power BI Pro License

With the overhauled Power BI platform, there's two types of licenses: Free and Power BI Pro. As you can imagine, accessing Analysis Services data is a "corporate" type of feature and therefore requires a Power BI Pro license.

More information: Power BI Pricing

4. SSAS Connector Installed on a Domain Machine

In order for your Power BI cloud service tenant to communicate with your SSAS server(s), an SSAS Connector must be installed. The SSAS Connector acts as a proxy between the Power BI service and your Analysis Services server. Following are the pertinent details about the SSAS Connector:

  • The person installing the SSAS Connector must have possession of an ID and password with administrator privileges on the SSAS server. It's highly recommended that you create a specific Power BI service account, with administrator privileges on the appropriate SSAS DBs, rather than use an individual person's account. This account does not need to be a Power BI administrator (this is one area where the new Power BI Preview is very different than Power BI for Office 365). Things will be easiest if the password for the Power BI service account does *not* expire at frequent intervals; if the password does expire, in this initial release you will need to go back into the SSAS Connector and update the password in there since it stores those credentials.
  • The SSAS Connector needs to be installed on a domain-joined machine, and not be part of a workgroup.
  • The SSAS Connector doesn't have to be installed directly on the SSAS server though it can be if you prefer (generally if the user base is low, the same server is ok). The SSAS Connector should be installed on a machine in the same domain as the SSAS server, or in another domain that's trusted, and in the same vicinity as the SSAS server to minimize network latency. It should not be your personal laptop or desktop.
  • The SSAS Connector should be installed on a machine remains on, accessible at all times, with Internet access. If the SSAS Connector is unavailable, live queries from Power BI will fail with an "oops, something went wrong" message.
  • If you have more than one SSAS server you wish for Power BI to be aware of, then one connector needs to be set up for each server. 
  • .NET Framework 4.5 is a prerequisite on the computer where the SSAS Connector will be installed.
  • Configuring the server name and domain\user name for the SSAS Connector are case-sensitive.

All SSAS connectors that have been registered by any user show up after you do Get Data > SSAS. This could be a long list if you're in a big company. If a user tries to connect to an SSAS database that has been registered and doesn't have at least Read permissions, Power BI will then show a "No Databases Found" message.

Current limitations of the SSAS Connector:

  • At the moment a single SSAS Connector cannot register multiple SSAS servers.
  • The SSAS Connector cannot currently function properly on the same machine as where another Data Management Gateway has been installed.
  • The SSAS Connector doesn't currently have any scale-out functionality like the Data Management Gateway does. 
  • ID and Password for the Administrator are stored in the SSAS Connector; if the password expires without being manually changed in the SSAS Connector, connectivity will be down.
  • EffectiveUserName passed on a shared dashboard retains the user who shared the dashboard rather than the user who is running it. More information on that in Part 1 of this series.
  • The SSAS Connector issues queries back to the SSAS Connector every 10 minutes to keep dashboards up to date. This frequency cannot currently be configured.

More information: Configure a Power BI Analysis Services Connector

5. Work ID Integrated with Active Directory

A requirement for all of this to work is that the ID/PW you use for Power BI is the same one used to access SSAS data (SSAS only supports Windows authentication). For most organizations that have Active Directory implemented, this won't be an obstacle. 

If you have signed up for Power BI with a personal account that is not integrated with an organizational account, the ability to connect to SSAS data is a bit more difficult to pull off. Greg Galloway has posted a really useful workaround for this in his blog: Setting Up a Demo of the Power BI Analysis Services Connector. I used Greg's approach of defining an alternate UPN for my personal/playground SharePoint Farm in Azure which has a virtual network of 3 machines: a domain controller, a SQL server, and a SharePoint server.

More information: Active Directory Domain Services

6. Only for users who sign into Power BI with an ".onmicrosoft.com" address: Directory Sync (AAD DirSync) with Azure Active Directory

If you log onto a cloud service such as Power BI with an ".onmicrosoft.com" account, and this is different than your regular work ID (i.e., it's not single sign-on), you can still facilitate usage of the SSAS Connector but it takes additional configuration.  Use of the SSAS Connector with two different logins requires the installation of Directory Sync (DirSync) on your domain controller to handle syncing up your Active Directory (AD) with Azure Active Directory (AAD). The DirSync process permits a cloud service (like Power BI) to communicate with Active Directory and thus reach Analysis Services on your on-premises server. Of course, this needs to be set up for the same domain that the SSAS database resides in.

Why do you want to bother syncing? It's convenient for users to minimize the # of IDs and PWs for them, definitely true, but it's also far more secure. If an employee leaves the company, the ID only needs to be disabled in one place then it'll propagate. That minimizes the risk that a former employee may still have access after they're no longer employed.

More information:  Why You Might Need DirSync to Connect to an On-Premises Analysis Services Server 

             and Power BI Analysis Services Connector Deep Dive

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview (you are here)

Part III: Monitoring the SSAS Connector in the Power BI Preview

Overview of Using Analysis Services Data in the new Power BI Preview

Welcome to part 1 of a 3-part series on using the SSAS Connector with the new Power BI Preview. This information is as of Feb 2015 and is subject to change as the Power BI Preview evolves.

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview (you are here)

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview

Part III: Monitoring the SSAS Connector in the Power BI Preview

Overview of How the SSAS Connector Works

The functionality to access SSAS data directly via the Power BI Preview works by using EffectiveUserName in the connection string. The method of using EffectiveUserName in an expression-based connection string has been around a long time as one alternative to configuring Kerberos delegation for implementing security in a BI environment.

Below is a representation of what's happening: the SSAS Connector sits in the middle as a proxy. The user query to the SSAS tabular database is issued as the Administrator (as configured by the SSAS Connector), but with an EffectiveUserName of the person who is running the report (i.e., issuing the query).

Note: In the diagrams, I show two machines in the domain; this could be one if you prefer to install the SSAS Connector directly on the SSAS server. More on that in the next part of this series.

What Happens When a Dashboard is Shared?

With EffectiveUserName we see that role-based security settings are honored based on the user running the report - that's great, and as expected, for the user who created the report in the first place (i.e., the report or dashboard owner). How about if User A shares a dashboard with User B and these two users do *not* have the same security settings specified in the SSAS roles?

 

Unfortunately, at this point in the Power BI Preview, it will be User A's security settings (i.e., the owner of the dashboard that was shared) that will be inherited by User B. Put another way, anyone User A shares with will see User A's data. This is obviously not ideal if the users are not typically permitted to see the exact same thing.

At some point in the (hopefully near) future we need for User B (the recipient of the share) to be who is passed in the EffectiveUserName. Until a recipient of a shared dashboard becomes the EffectiveUserName, the recommendation has to be not to share dashboards that use SSAS under the covers unless you know row-level security isn't a factor (i.e., if you're using SSAS due to higher data volumes or more frequent refreshes or some other reason besides role-based security).

SSASConnector2.jpg

Advantages of Using the SSAS Connector

  • No need to store redundant set of data inside of a Power Pivot workbook. This keeps the data more secure & reduces risk of error.
  • Because there's no need to store redundant data, there's also no need to set up a separate data refresh schedule in Power BI. The data is as fresh as the scheduling for the underlying SSAS Tabular model.
  • The reports and the data are now truly separated. This is huge!  It enables delivery of multiple reports, to multiple users, using the same source data. This wasn't previously possible without multiple Power Pivot models (which introduces more data redundancy and risk of misalignment between the multiple Power Pivot models).  **Note this won't really be possible until the issue with the EffectiveUserName for shared dashboards gets resolved.**
  • Honors row-level security specified inside of SSAS.  **For the original report author.
  • There are currently no documented restrictions on whether it's operating in the in-memory (stored in SSAS) mode or direct query mode. This means you should be able to use a tabular model to access a SQL Server relational database or even APS in direct query mode, if desired. (Though you do want to aggregate and summarize the data whenever possible so as not to try to pull back a ton of data volume.)

Disadvantages of Using the SSAS Connector

  • As discussed above, the EffectiveUserName issue for shared dashboards.
  • Currently the SSAS Connector only supports Tabular Models, but Microsoft has publicly stated that support for Multidimensional is coming.
  • Q&A functionality is not yet available in Power BI when SSAS is the data source.
  • If Power Query was used in an Excel workbook that's being upgraded to an SSAS Tabular model, the components written in Power Query must be done a different way (such as SSIS for instance). Currently there's not a migration path for Power Query to SSIS, nor does Power Query integrate with SSAS. A Power Pivot workbook with its data going straight into Power Pivot (without Power Query) is eligible for upgrade.
  • Currently there's no scale-out method documented for the SSAS Connector like there is with the Data Management Gateway.

High Level Architecture for the SSAS Connector

Following are two diagrams presented by Microsoft at its 3/4/2015 webinar on the Power BI Connector:

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview (you are here)

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview

Part III: Monitoring the SSAS Connector in the Power BI Preview

Finding More Information

Power BI Support Documentation - SQL Server Analysis Services Tabular Data

MSDN - Connection String Properties (Analysis Services)