What’s the difference between Live Connection and DirectQuery in Power BI, and where do they fit?

Hot on the heels of the question I was asked a few days ago, comes another closely related one: “What’s the difference between Connect live to a data source and DirectQuery a data source in Power BI?”

We had already established that there are two methods in which we could interact with data using Power BI: loading data into Power BI and accessing the data source directly.

Connecting live and DirectQuery both fall into the latter method, but there is a difference.

In DirectQuery mode, you access the data source, such as a relational database or data mart for data, but then you would create calculated columns or measures on top of it in Power BI generating a data model layer, something similar to a database view, if you may. The data still exists at the data source; but is pulled through the data model on Power BI onto the the visuals. The end users and report creators will see and interact with the data model on Power BI.

In the case of Connect live, the data model itself is at the source, you interact with it directly and no data model layer is created on Power BI. All measures, calculated columns and KPIs are provided by the data model at the source, along with the data. End users and report authors will see and interact with this data model through Power BI.

If you would compare these two methods on a conceptual level; DirectQuery mode is used in cases of self-service where you have data marts or a data warehouse on a relational database, and business users build their own data models off this for their business needs. The data marts or data warehouse will integrate data from various systems, and provide base measures with related dimensions.  Business user may create custom measures and calculated columns on top of this to suit their reporting and analytic requirements, and then explore data and build visual reports. Think of this as the data discovery phase of the self-service exercise.

Live connections would probably be used in scenarios where the analytic needs are better understood, and/or the type of analytics that were described above have matured and has become a mainstream in the organization. Here data models are built off the data warehouse using Analysis Services (multidimensional or tabular), with measures, calculations and KPIs that were earlier part of the self-service (and the data discovery exercise) incorporated in it. Business users now have established reports and dashboards that showcase organizational performance powered by established data models. Think of this phase where things have evolved into corporate BI that gives real value.

[SUBJECT TO CHANGE] Out of the whole bunch of supported data sources, Power BI currently supports the following in DirectQuery mode:

  • SQL Server
  • Azure SQL Database
  • Azure SQL Data Warehouse
  • SAP HANA
  • Oracle Database
  • Teradata Database
  • Amazon Redshift (Preview)
  • Impala (Preview)
  • Snowflake (Preview)

and the following using a Live connection:

  • Analysis Services Tabular
  • Analysis Services Multidimensional
  • Azure Analysis Services (Preview)

Advertisements

Consuming Live Data Sources vs On-Premise Data via gateways

So I got this question today, on what seemed to be a little confusing for the questioner, about two features of Power BI Pro.

Power BI Pro - Two Features

The question: “Why does it say ‘Consume live data sources with full interactivity’ as one feature while the other feature says ‘Access on-premise data using the Data Connectivity Gateways’, while it is obvious that if you need to connect to an on-premise data source to consume live data it has to be through a gateway?”

Okay, this is how I would explain this:

‘Consume live data sources with full interactivity’ means that you can directly access live data sources, such as relational databases, or data models built atop Analysis Services without having to load the data into Power BI first. Power BI has two ways of providing data to the user to build reports: “Import” and “Direct Query/Connect live”. The former allows you to connect to the data source, pull in the required data into Power BI and build a model off that, and then let the consumer build reports off this data model. Here the user hits Power BI for the data. The latter allows you to directly connect to the data source via Power BI and build reports off the data structure that already exists at the source, either by creating a model layer or using the model at the source. It is this latter method does this feature describe.

The other feature, ‘Access on-premises data using the Data Connectivity Gateways’ just mean that in order for you to get data from an on-premise source you need to use a Data Connectivity Gateway. The gateway is but a security mechanism that allows Power BI (which is a cloud service) to access a client’s secure environment (which is on-premise) to access data, regardless of it using the “Import” or the “Direct Query/Connect live” modes.

Of course, if you were accessing a cloud based data source such as an Azure SQL Database, an Azure SQL Data Warehouse or Excel files on a SharePoint Online folder, you would not need a gateway; and you can access them using “Import” or consume live data with full interactivity using the “Direct Query/Connect live” mode.

Theming in Power BI

Finally, we have theming in Power BI. A much requested and required feature, especially for organizations where using their corporate color themes in everything they do, is a way of life. And even when showcasing the capabilities of Power BI to potential clients, the questions sometimes boils down to something simple things like the customization of the color theme. This question can now be attempted with a confident ‘yes’, rather than the thoughtful ‘yes’ that we blurt out while mentally going through the steps of applying a colors from widget to widget.

The March 2017 update of Power BI Desktop comes with a preview of Themes. Right now it is in its simplest of forms: You manually create a JSON file that has a very few attributes that can set basic color themes to your reports. So all you have to do is create file that looks like this:

{
“name”: “rainbow”,
“dataColors”: [ “#FF0000”, “#FF7F00”, “#FFFF00”, “#00FF00”, “#0000FF”, “#4B0082”, “#9400D3” ],
“background”:”#FFFFFF”,
“foreground”: “#9400D3”,
“tableAccent”: “#FFFF00”
}

And then do this in Power BI Desktop; here:

Theme Import

And lo and behold my rainbow theme is applied:

To revert, you just re-select the Default Theme.

Yes, it is old-school, but this is preview, and only a few attributes are designed to get affected by the theme settings. However, it works, it gives us an idea as to what’s coming, and also let’s us pour in our suggestions as well.

What I really like about this is that you can have any number of colors listed out, usually it is around 8, with Power BI adding the default white and black. And what I really like about it is the list of accent colors based on the main colors:

Theme Colors

All in all these are exciting times. Things on the aesthetic customization aspect can only get better. To read more, check out the Power BI blog.

The Analytics Pane in Power BI

The August 2016 edition on Power BI Desktop introduces a new pane named “Analytics” right next to the “Fields” and “Format” panes. Now, this title probably gave you, just as it did for me, a racing heart and goosebumps. However when you actually go to the pane, you just blurt out a disappointed “oh…”. Well, not that it is bad, you have options such as adding a percentile line, median line, trend line and a few more. But one would expect to see a little bit more with respect to analytics.

One thing that you do find is a forecast line, which for now works on a single-measure line chart. It’s pretty neat, but as advanced analytics go requires enough data points in order give you a good forecast. Of course this is just the start. We’re sure to see more analytic capabilities in the future.

Cortana Intelligence Suite – Not just an intelligent personal assistant

Everyone knows Siri. Well, at least those who own an iPhone do. Then again that amounts to almost everyone. So I guess everyone does know Siri. A couple years ago however, we were introduced to Cortana (named after Microsoft’s Halo game character) on Windows Phone. She soon found her way onto Windows 10 devices, and is now even made available for iOS and Android devices. Think of her as something of a primitive version of Scarlett Johansson’s character in Her, if I may. She is Microsoft’s own intelligent personal assistant, and I use her almost everyday.

But what I am going to introduce you to in this post is something bigger. Much much bigger. It’s called Cortana Intelligence Suite; Microsoft’s Azure-based analytics and intelligence platform. Don’t panic just yet! D-Day hasn’t begun, nor have the machines risen, but I bet you can well imagine what Microsoft must be aspiring when they named it that.

Cortana Intelligence Suite (CIS) is a platform and process for building end-to-end advanced analytical solutions. The platform is made up of various tools and services, mainly running on top of Azure. It is not necessary that all the tools that make up CIS be used for your solution, but it is important that you follow a certain process to get it done. This way you know what you are going to do in a methodical manner, and it would be easy to choose the right tools and services from the suite to get things done.

The process that CIS solutions follow is known as Cross Industry Standard Process for Data Mining (CRISP-DM), which is a long-used time-tested means of performing data mining (which is very closely related to analytics, and is often employed to perform analytics).

CRISP-DM
CRISP-DM

For a complete overview of CIS and how it can be used, this easy-to-understand video by Buck Woody would be the ideal choice. It also details the tools and services that are part of the CIS platform. Once you are done with the video, you would probably get the gist of how Cortana fits into the business intelligence and analytics vision of Microsoft. Enjoy!

[VIDEO: Cortana Intelligence Suite – Overview by Buck Woody]