Direct Connect Options in Power BI for Live Querying of a Data Source
In a recent Power BI post, I wrote about two ways to utilize Power BI: one being the "Report Only" and the other including the "Query, Model and Report" alternative. Since then I've done a Power BI POC for a new customer and seen a couple of twitter comments that inspired me to write about this in a bit more detail.
**Updates to this post since it was originally published mid Sept 2015:
- 1/9/16 due to release of Enterprise Gateway
Direct Connect - aka Live Query - 'Pull' Options in Power BI
The direct connect option aligns with the "Report Only" use I talked about in my other post. Why is this a big deal? We've all heard of the 'one version of the truth' aspiration. And while the flexibility and empowerment of self-service BI is indeed incredibly important, so is utilization of a centralized data source that users trust. It may be that there's been a significant investment in a data store that we want to take advantage of in Power BI without bringing the data into an intermediary layer. Or the data volumes are too large for an embedded model. Or we want to take advantage of row-level security. Or we have security restrictions on the ability to replicate data.
As of Jan 2016, there are the following direct connect options in Power BI that involve a 'pull' approach:
- Analysis Services Tabular Model (Live Connection via Enterprise Gateway)
- Analysis Services Multidimensional Model (Live Connection via Enterprise Gateway)
- SQL Server (DirectQuery via Enterprise Gateway)
- SAP HANA (DirectQuery via Enterprise Gateway)
- Azure SQL Database
- Azure SQL Data Warehouse
- Spark (on HDInsight or on-premises)
The Enterprise Gateway is the successor to the SSAS Connector. Via the Enterprise Gateway, there are two modes for live queries: DirectQuery and Live Connection. Conceptually they do the same thing, but apparently there is actually a distinction therefore different lingo.
A live connection to an SSAS Tabular or an SSAS Multidmensional model is the only option which supports row-level security using roles associated to Windows authentication (for now anyway). Although Azure SQL Database has new row-level security capabilities now, its functionality is associated to database users rather than Windows authentication so it's not currently supported for this purpose in Power BI.
When in Live Connection mode, note how it looks a little different in the Power BI Desktop: there is no Data pane, no Relationships pane, and the Edit Queries dialog box doesn't show anything except the connection.
How the SSAS Tabular direct connection looks in Power BI Desktop
SSAS Choices in Power BI Desktop
Near Real-Time 'Push' Options in Power BI
In addition to the 4 'pull' methods listed above, there are 2 'push' methods currently supported: streaming data and APIs, both which can be used for near-real-time analysis. These options are not exactly direct connect since they send data to an embedded model in the Power BI service, but it's close enough that I thought it warranted mentioning here.
Process for exposing streaming data to Power BI
The above chart displays the high level process for exposing streaming data to Power BI. The Azure Event Hub communicates with the data source. Azure Stream Analytics takes this input from the Event Hub and outputs it to Power BI. In addition to the temporary streaming data, you can route some or all of the data from Stream Analytics to a persisted data location as well (which could potentially give Power BI another option for reporting on the history of this data in addition to the live stream).
More info on the APIs can be found in the Power BI Developer Center.
What Is Different with the Direct Connect (Report Only) Live Query Method?
During a recent proof of concept project, the customer asked me if all of the functionality under 'Edit Queries' is there if you are working in the direct connect mode. The answer is no.
This next screen shot shows what the flow looks like when you utilize the embedded data model (the "Query, Model, and Report" alternative).
The full Query>Model>Report approach
Conversely, all of the options to manage the data are eliminated when in direct connect mode (the "Report Only" alternative). Usage of Power BI Desktop becomes quite optional actually, as report authoring could easily occur right in the web browser.
The Report Only approach (direct query / direct connect)
So, my main takeaway is that the fundamentals of both approaches are very different, but there are use cases for using both alternatives.