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

What is Power BI?

Power BI is an self-service business intelligence offering from Microsoft. It is primarily aimed at business users to access corporate information (and even information outside of the organization) without (or with the least) involvement of IT. Users can build their own business intelligence solutions, share and collaborate with team members and finally publish this information.

From the mid 2000s, with the launch of SQL Server 2005, Microsoft had been striving to bring business intelligence to the masses. Business Intelligence at that time was mostly used by large enterprises and was quite a costly solution to put into place. So, when SQL Server 2005 came into being with a new and improved Analysis Services, sporting Unified Dimensional Model, all of a sudden even small organizations could implement BI.

Fast forward to the present, we have self-service BI and analytics running the show, and Microsoft is in the middle of the game once again with Power BI. If you would like to learn more about Power BI and what it offers, I have a curation on Curah! dedicated especially for this. The content of the curation will change as Power BI evolves, so go take a look: https://curah.microsoft.com/348325/what-is-power-bi.

Getting Started with Business Intelligence on the Microsoft Platform

I was invited to the monthly Microsoft Student Partner meet-up last Friday, and I was excited to be speaking to an all-student audience after a long time. I had checked with the organizers about the audience’s know-how of business intelligence, and was told that it was something new to them. So, I decided to talk about Business Intelligence and Data Analytics on the Microsoft platform.

Now, 5 years ago, this subject would have been much easier to talk about. However, in recent times, the space of business intelligence and data analytics has changed much. Even within the Microsoft space of BI there is a lot to look at, and if you are new in the field, getting started is going to be quite a tough little challenge. With this in mind I built a little slide-deck, and a simple demo that I hoped would wow, or at least give them a moment’s awe, and waltzed into the auditorium.

Continue reading Getting Started with Business Intelligence on the Microsoft Platform

Power BI: Another Player in the Game

First up, Power BI for Office 365

Power BI for Office 365

An year ago till a couple of months ago, when we spoke of Power BI, it meant just one thing: Power BI for Office 365. Power BI for Office 365 is portrayed as a self-service business intelligence (or data analytics) tool on the cloud. The product is basically;

  • Excel 2013 with a bunch of add-ons for report authoring,
  • and special sites called Power BI sites on Office 365 for sharing and collaboration of the reports created using Excel, plus a few more goodies

Going further, Excel 2013 is the essential component of Power BI for Office 365. It is where all the data extraction, clean-up, consolidation and modeling happens (Power Query and Power Pivot). It is also where the reports and analytics is done (Power View and Power Map). The best thing about this is that all these add-on that I have mentioned are free, and can be used even without purchasing Power BI for Office 365 (PowerMap requires that you have an Office 365 ProPlus subscription). Then, if you need to share it with others, you to put the workbook on a Power BI site. This enables others in your team to see the data and collaborate on it as well. Apart from this, Power BI for Office 365 allows

  • analysis using natural language questions (in English) with Q&A,
  • creating and managing a repository of queries and data sources (including on-premise), on the cloud,
  • and access data from the Power BI Windows Store App so that information is available on the go on Windows tables

The pricing for Power BI for Office 365 currently looks like this, and has more than what I have described here. For more information I recommend reading this, the Power BI for Office 365 service description and FAQs.

And now, there is a new player in the game. And it’s from Microsoft itself…

Continue reading Power BI: Another Player in the Game

Slaughter Analytics – Part 2

In the first part of Slaughter Analytics, I showcased how I performed self-service ETL to pull in and model data from a livestock slaughter statistics webpage. In this post I shall focus on performing some simple analytics through visualizations.

Excel 2013 gives us two more add-ins for visualization, interactive analytics and storytelling: Power View and Power Map. The video below showcases the demo that I used to perform the analysis of the extracted and modeled data.

Slaughter Analytics – Visualization

 

Continue reading Slaughter Analytics – Part 2