Demystifying “DirectQuery” and “Connect Live”

The terms “DirectQuery” and “Connect Live” are somehow confusing. I saw lots of people are using both terminologies as alternatives. But, the context of “DirectQuery” and “Connect Live” are very different indeed. Therefore, if use a a terminology when we’re talking about a different context then the whole situation might get quite confusing. in this post I try to explain the differences and make it more clear to prevent using a wrong terminology and make sure everyone is on the same page when we’re referring to “DirectQuery” or “Connect Live”.

When we use the “DirectQuery” terminology we are actually talking about connecting from Power BI Desktop instance to an RDBMS type of data source like SQL Server DB or Oracle DB.

There are two types of data connections when we’re connecting to RDBMS like SQL Server or Oracle DB from Power BI Desktop:

  • Import Data: which literally loads data into the underlying model to make it available in memory
  • DirectQuery: which doesn’t load data into model. Instead, it runs multiple concurrent queries on the RDBMS side (data source side) and gets the results. This is good to support real-time data processing.

Note: The same principal applies to SSAS Tabular.

DirectQuery/Data Import Mode in Power BI Desktop

On the other hand, when talk about “Connect Live”, we are referring to the data connection type from a reporting tool like Power BI Desktop OR Excel to an instance of SSAS, either SSAS Multidimensional or SSAS Tabular.

Continue reading “Demystifying “DirectQuery” and “Connect Live””

Azure Data Lake, Azure Analysis Services, Power BI and How They Relate

Loading Data From On-prem SQL Server to Azure Data Lake Store and Data Visualisation in Power BI

I recently spoke in Tampa Dev Azure Meeting in a webinar form on 31st Jan 2018. We received interesting questions during the presentation. My aim was to introduce basic concepts of Big Data, Azure Data Lake, Azure Data Lake Store (ADLS), Azure Data Factory (ADF)  and Power BI.

I would like to thank Tampa Dev organisers and all attendees for giving the opportunity to speak in this session.

In this session you learn basic concepts of:

  • Big Data
  • Azure Data Lake
  • Azure Data Lake Store (ADLS)
  • Azure Data Factory (ADF)
  • Azure Analysis Services (AAS)
  • Power BI
  • And how they relate

Session recording:

Continue reading “Azure Data Lake, Azure Analysis Services, Power BI and How They Relate”

Visualise SQLite Data in Power BI

SQLite + Power BI

As the name implies, SQLite is a light weight transactional SQL database engine. It is open-source and free for use either for personal commercial. SQLite is widely used in mobile apps and many other desktop applications that need an internal light weight free SQL database. In this post I explain how to visualise SQLite data in Power BI.

Requirements

To be able to go through the process you need to meet the following requirements:

  • Latest Version of Power BI Desktop (Current version: 2.52.4921.682 64-bit (November/2017))
  • Install SQLite ODBC driver: Make sure you install 64 bit version if your Power BI Desktop is 64 bit version like mine!

Note: You may install both x32 and x64 bit versions of the driver if your other applications are in x32 bit.

  • An existing SQLite database

Note: In case you just want to try this and you don’t currently have a SQLite database you can download a SQLite version of “Internet Sales” of AdventrueWorksDW2016 that I made available for you.

How it works

Like any other Power BI cases, it’s really easy to load data from an existing SQLite database to a Power BI Desktop model. You just need to use ODBC data connection and connect to a predefined “ODBC DNS” OR use a “Connection String”. I’ll explain both methods. After you load data to your Power BI Desktop, you create the relationships then you’re good to go and create flashy reports. Continue reading if you’re interested to an step-by-step guide to visualise SQLite data In Power BI.

Importing SQLite Data to Power BI Using ODBC DSN

  • Open the correct version (x32, x64) of ODBC after you downloaded and installed SQLite ODBC Driver
  • Click “Drivers” tab and make sure SQLite ODBC Driver(s) successfully  installed

ODBC Drivers Continue reading “Visualise SQLite Data in Power BI”