Using Direct Query – A Tale From the Trenches

Lately I’ve been working a lot with Power BI using Direct Query. Historically, if my client was considering Direct Query mode, my automatic response was simply “Nope, bad idea”. My lack of enthusiasm was based on predictably poor query performance and limited DAX functionality in Power BI. While we are still far from Nirvana, my opinion about Direct Query is changing, becoming more positive.

Database technologies continue to evolve in ways that dramatically improve database query performance. For example, SQL Server 2016 in-memory capabilities such as updatable column store indexes are game changing. New security enhancements such as Row Level Security (RLS) give us more options for how to control access. These, and other improvements make Direct Query more feasible. Additionally, we are seeing expanding support in Power BI support for Direct Query which is not a coincidence. Clearly, Microsoft recognizes that Direct Query is essential for certain use cases and we can predict that Direct Query implementations will become much more common in the not too distant future.

What are some typical use cases for Direct Query?

1. You need near real time access to your operational data – classic Relational Online Analytical Processing (ROLAP) scenario.
2. You want data security and access control to be managed within the backend database environment (e.g. Row Level Security).
3. You have an existing on premises data warehouse. You want the data to remain on premises and you don’t want to replicate the data in other platforms (e.g. SSAS).
4. The volume of your on premises data sources might exceed the practical limits of SSAS.
5. You are developing a solution using the Power BI Embedding Service and you need the data to be current at all times.

In a recent client engagement, Direct Query was the client’s preferred approach. In this scenario we needed to connect Power BI with an existing Oracle Data Warehouse. OK, no problem. There was a catch, however, the requirements for data security.

In our client’s world data security is defined, managed and enforced within the Oracle database environment. Their security scheme is based on Oracle’s Virtual Private Database technology. All reporting tools currently used in the environment depend on the Oracle data security framework to ensure that user access is appropriately constrained. This method of security is working well for our client and they really didn’t want to maintain a second data security scheme. Given this constraint, Direct Query was most obvious way to connect Power BI.

What challenges would we need to overcome?

1. Oracle security requires knowledge of the current domain user. Power BI Service doesn’t have this knowledge of the local domain user information – it only knows the Power BI user name (work or school email address). Therefore, the Power BI user name would need to be translated to a domain user name, and then somehow, that domain user name would need to be passed in to Oracle.
2. A positive user experience from Power BI requires that query performance is very responsive and does not leave users waiting for visuals to refresh.
3. When using Direct Query mode, there are limits on what is allowed in DAX calculations, but it is not easy to know precisely what those limitations will be until you get fully into the development and testing.

What was the solution?

We chose SQL Server Analysis Services (Tabular/Direct Query) as the hub of the solution architecture. It would function as an intermediary agent in the communication between Power BI Service and Oracle. Why was this advantageous?

The user account name used by Power BI is the work or school email account (e.g. fred@contoso.com). When Power BI Service connects with SSAS, SSAS translates the Power BI user account name to the corresponding domain user account name. This is standard functionality in SSAS and is fundamental to how security roles in SSAS are typically implemented. Read more about how the On Premises Gateway works with SSAS here.

Our requirement, however, was to fully delegate security to Oracle. To do so, SSAS must pass the domain user account name through to Oracle as a property of the database connection, whenever queries are issued via Direct Query. The Oracle database connection from SSAS uses a generic service account because Oracle is not configured to allow Windows user login sessions.

A depiction of solution architecture is as follows:

The other important job that SSAS performs is to translate DAX queries (issued from the Power BI Service) in to SQL statements to be executed in Oracle.

The critical linchpin of the architecture is the On Premises Gateway. All communication from the Power BI Service to SSAS (and by extension, Oracle) flows through the gateway. Note also that solutions developed in Desktop use the Live Connection option for connecting to SSAS. Live Connect to SSAS from Desktop (vs. Import) is our only option when we are delegating security to Oracle. Replicating the data in Desktop and in the Power BI Service, would defeat our purpose.

So what about that pesky domain user name requirement?

Configuring the environment so that the domain user name is actually passed through to Oracle required digging into how SSAS facilitates impersonation. I’ll limit the depth to which I describe SSAS impersonation behaviors in this blog and focus in on what is actually required to make it work with Oracle. For extensive details on Impersonation you can download the Microsoft BI Authentication Overview Document.

Analysis Services in multidimensional or tabular mode typically does not delegate user identities to the backend data sources. The exception is Direct Query tabular mode. To make this work, one must configure and enable Kerberos Constrained delegation. Kerberos can be tricky to set up, and troubleshooting problems requires some good technical talent.

Fortunately for our scenario, Kerberos was not required (whew!) This is because we actually didn’t need SSAS to fully delegate the user identity. Our requirement was to have SSAS connect to Oracle using a generic account. But we would still need to have the actual domain user name to show up in the connection context.

After quite a few hours of research and a lot of trial and error, we found the magic incantation.

A summary of the configuration is as follows:

• SSAS database connection uses an Oracle generic user account (dw_svc_user)
• On Premises Gateway data source configured to connect to SSAS using dedicated domain account
• SSAS impersonation mode property set to ImpersonateCurrentUser

With this configuration, SSAS connects to Oracle via the dw_svc_user generic account. SSAS translates the Power BI user name (EffectiveUserName property) to the domain user name and this gets passed in to Oracle in the connection context as the OSUserName property. The Oracle security framework then reads this property and applies security dynamically.

Initially, we struggled to get this configuration to work in the way we intended. The connection from SSAS was failing, but not for reasons that were obvious. It turns out that there was the problem with SSAS not finding the Oracle TNS Names file. This file (TNSNAMES.ORA) is a SQL*Net configuration file that defines databases addresses for establishing connections to them. To get around this problem, we modified the database connection string to include the Server IP address (see below).

Provider=OraOLEDB.Oracle.1;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XXX.X.XXX)(PORT=1541)))(CONNECT_DATA=(SID=dw)));Persist Security Info=True;User ID=dw_svc_user

While our workaround got us up and running, it is a temporary fix. The error we experienced is likely due to a file permissions problem will require further debugging.

So how did Direct Query Perform with Power BI?

Well, no surprise, there is plenty of Oracle performance tuning to be done in order to satisfy user expectations. That said, from our preliminary testing, the system performance was not completely horrible. It was critically important to demonstrate that we could indeed satisfy the requirement of passing the actual domain user name to Oracle for security delegation. Mission accomplished on this front. Going forward, our client can refine and optimize Direct Query implementations.

In addition to the Direct Query approach, we also had the client test drive SSAS using import mode. As expected, this option demonstrated great performance. Our client saw the value of this approach. They have embraced the idea of implementing SSAS using Direct Query mode for certain use cases, and SSAS using import mode for others. It is great to have options and flexibility and have the best of both worlds.

For your additional reading pleasure:

Direct Query Mode – SSAS Tabular

Connection String Properties for SSAS

Increasing the Row Limits in SSAS for Direct Query