Query On-Prem Datasets from the Power BI Cloud

Query-On-Prem-Datasets-from-Power-BI-Cloud

Here are some notes for connecting Power BI to Analysis Services. Thanks to Microsoft for the helpful presentation on 3/4/2015.

Cloud-based BI allows businesses to start doing BI quicker with little to no overhead. One obstacle has been negotiating the desire for always-available rich data with the need to push large (possibly outdated) datasets to the cloud. Microsoft solves this issue with the Power BI Analysis Services Connector.

  • Without using the Analysis Services Connector, Power BI requires you to push refreshed data to the cloud every time you want new data.
  • With the Analysis Services Connector, there is no need to move entire on-prem datasets to the Power BI cloud.

Motivation
Many customers already have hardware and software housing their data on-prem. This needs to be respected. Furthermore, If businesses have a terrabyte of data, it is not currently practical to move that data to the Power BI cloud daily. The question then is, how to move them forward to the cloud and to mobile? This connector is the answer to this question.

Data Refresh means that we pull entire data from relational source and lands in a compressed database running in Power BI. With the AS Connector, the query results are the only data that goes to the cloud. The key thing with the Analysis Services Connector is that data is not moved to the Power BI cloud before being queried.  When building a regular Power BI report, the data lives in the Power BI cloud service. In the case of the Analysis services Connector, the reports connect directly to the on-prem tabular models.

Setup

  1. Install the AS Connector on the SSAS server. It is like a broker between the Power BI service and the on-prem tabular model. It acts as an admin. Only one connector can be attached to each tabular model machine.
  2. When installing the connector, Login to Power BI with Admin account as connector.
  3. Sync Azure Active Directory Services with on-prem active directory.
  4. Then, from Power BI, Get Data – Analysis Services, then pick your database. This imports the schema – not the data, creating a stub database in the cloud that mimics the behavior of the on-prem.

Added Value of the Analysis Services Connector

  • Depending on who connects to the on-prem models, they see their own data (row-level security!). This is available with the SSAS tabular models. The admin user impersonates a user coming from Power BI. This concept is called Effective UserID. Any query through the connector uses the particular login.
  • Cross filtering in a report sends queries live to the on-prem server and gets data back. Microsoft is still trying to improve performance but it currently refreshes the interactive reports reasonably fast.

Speed
Complex queries on complex models are slow due to the model complexity but not due to latency. There is very little overhead when uploading data to the cloud (Internet speed depending). This query pipeline is optimized for latency.

FAQ

  • Do you need to open a particular port through the firewall or any DMZ requirements?
    • No. The Azure Service Bus does not need it. So, the cloud does not reach into on-prem to get data out. Rather, the Connector Admin gets notified that there is a query. Then the connector gets the data and pushes it back to the cloud.
  • How do on-prem identities match Power BI users?
    • The Azure Active Directory Identity gets synced with the on-prem active directory.
  • Should the connector be installed on the same server as SSAS?
    • Yes, this reduces hops and thus latency. However, if the number of users will be large, it could be good to offload the connector to a nearby machine on the same subnet.
  • Is the data encrypted while in transport?
    • Yes, via SSL.
  • Will this work without On-Prem Active Directory?
    • No.

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>