Overview of Using Analysis Services Data in the new Power BI Preview (Part 1)
***Note this post applies to the SSAS Connector which has been deprecated as of mid-2016. Refer instead to the Power BI Enterprise Gateway.***
Welcome to part 1 of a 4-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
Part IV: Connecting to Analysis Services From Power BI Designer and Excel
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?
Update 7/24/2015 - This issue has been resolved with the GA release for Power BI. Row-level security is now honored.
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).
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. (As of 7/24/2015)
- 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
- 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
Part IV: Connecting to Analysis Services From Power BI Designer and Excel
Finding More Information
Power BI Support Documentation - SQL Server Analysis Services Tabular Data