TDWI | Training & Research | Business Intelligence, Analytics, Big Data, Data Warehousing

Analysis: Microsoft Extends Power BI Q&A Search to On-Premises Data

Microsoft’s decision to extend Power BI’s natural language search technology to on-premises data sources is good news for Power BI subscribers.

• By Steve Swoyer
• May 17, 2016

Another week, another new update for Power BI, the self-service business intelligence (BI) service Microsoft provides as part of Office 365. This update is an answer to the prayers of many Power BIers.

When Power BI debuted nearly three years ago, it included “Q&A,” a natural language search facility that permits subscribers to search against data in Office 365.

Microsoft last week announced plans to extend Q&A to search on-premises data.  Even though this feature isn’t officially available -- Redmond’s currently offering a public preview -- official support will follow. Soon.

“In the coming weeks we’ll be adding additional data sources and capabilities to the public preview -- and we’re looking forward to getting your feedback to help us refine Power BI’s Q&A support for enterprise gateway connected data sources,” wrote Brian Baumgartner, a principal program manager with Microsoft, in a post on the Power BI blog.

“To use the Q&A experience with your enterprise gateway connected data source, simply enable the ‘turn on Q&A for this dataset’ option in the ‘Q&A and Cortana’ settings for your dataset.”

This isn’t a surprising move on Microsoft’s part. As Baumgartner noted in his blog posting, subscribers have long clamored for Redmond to extend PowerBI’s Q&A tool to support searches against on-premises data.

More to the point, it’s already possible to use Q&A to search against on-premises data, as Microsoft data platform solutions architect James Serra demonstrated on his own blog more than a year ago.

“Now in beta for Power BI is a way to access data from the Power BI site in real-time on on-premise SQL Server. This is accomplished by having Power BI connect to an SSAS tabular model that is on-premise and uses Direct Query mode. That SSAS tabular model is connected to SQL Server, so a Power View report or Q&A on the Power BI site would be hitting SQL Server in real-time,” Serra wrote on his blog. “Power BI is able to ‘find’ the on-premise SSAS via an installation piece that is similar to the DMG, called the Power BI Analysis Services Connector.”

Both Serra’s early proof-of-concept and Microsoft’s proposal to extend Q&A to support on-premises (enterprise gateway-connected) data sources share a similar limitation: you can’t search against pre-built (i.e., live) OLAP cubes. 

Instead, you’re searching against what Microsoft calls SQL Server Analysis Services (SSAS) “tabular” data models -- i.e., in-memory “databases” (similar to OLAP cubes, but using tabular relational structures) that can be populated with data from SQL Server or from third-party sources, such as Oracle.

The upshot is that you can’t currently use Q&A to search against live SSAS OLAP cubes. That said, you can import existing SSAS cubes into Microsoft’s tabular data model format. The SSAS tabular models also support a mode called “DirectQuery” in which SSAS enforces a semantic model and passes queries to SQL Server itself. Melissa Coates, a solutions architect with BlueGranite who writes about SQL Server and other databases on her SQL Chick blog, describes DirectQuery as “conceptually analogous to ROLAP,” the relational OLAP technology first popularized by MicroStrategy Inc.  In other words, you can use DirectQuery in tandem with SSAS tabular models to search against live data in SQL Server."

Several people who commented on Baumgartner’s post asked specifically about support for OLAP cubes. It’s also worth noting that Baumgartner’s post specifies that Q&A works with SQL Server 2016 tabular data models. Redmond revamped its tabular support in SQL Server 2016. Prior versions used metadata inherited from OLAP modeling constructs, such as “cubes,” “dimensions,” and “measures,” but tabular data models in SQL Server 2016 use “tabular metadata object definitions in script and code.”

Will Microsoft eventually support search against SSAS cubes? We’ve asked the Power BI team and we’ll update this article as soon as we know anything for sure. In the short-term, Redmond’s move is a great development for Power BI subscribers.  It marks the extension of a powerful online-only natural language search technology to the on-premises world. It’s the kind of thing people working with Power BI have wished for -- pined for, even -- for almost two years now.