Get data/Import Data into Power BI

Understanding the steps to Import Data

The very first step to start with Power BI development is getting data into Power BI to analyze and develop reports. Lets see basic steps to get data.

    Power BI has a data section on the ribbon as in the image below. It has many popular data options like Excel, SQL Server, Recent Sources etc. You can choose any data source from it or select any other data source from Get Data option.

Lets pull a simple excel file and understand the process.

• Click on Get Data drop down and select Excel Workbook.
• or click on Excel Workbook option next to it. If you are starting with blank workbook, you will get option to get data on canvas.
• Browse the excel workbook from your computer and select the tab/table you want to pull.
• Once you select a tab/table, you will get a data preview in the next window.
• You will 2 options to Load the data as it is or Transform Data if some cleaning is required.

The Get Data dialog box organizes data types in the following categories:

• All
• File
• Database
• Power Platform
• Azure
• Online Services
• Other

Depending on the data source the data import methods will change.

Dataset Modes

Depending on how you get data into Power BI there are 3 dataset modes in Power BI.

1. Import Mode
2. DirectQuery Mode
3. Composite Mode

Import Mode

Import mode is the most common mode. As the name suggest Import, data will be imported into Power BI Engine. There are some benefits of Import Mode.

1. As data is imported, the performance is fast thanks to in-memory querying.
2. For Developers and Modelers it is easier to work on the data and create Models and Charts. It gives them Design flexibility due to availability of data.
3. It is a default mode. It is recommended to use this mode when data size is small and performance is key requirement.

Import mode can integrate data from different data sources. While there are advantages of Import Mode there are disadvantages too.

1. One of the biggest advantage can be a down side of it too. As data is stored in memory, the whole model have to be loaded to memory before Power BI can query the Model. This can slow down the performance if the data size is large.
2. Data present in the model will be as latest as the last refresh so import model has to be refreshed periodically depending on the frequency which data is refreshed. It is recommended to use this mode when latest data is not required and data source is refreshed periodically.
3. A full refresh will remove all the data from model and replace it with new. This can cost the resources and performance.

When to use Import Mode:

• When data size is small and performance is priority.
• When latest data is not required and data source is refreshed after specific intervals.
• You have sufficient resources to load and process the data.

DirectQuery Mode

Unlike Import Mode DirectQuery Mode will not import data. It will only import the metadata of the actual data. The data will reside in the data source itself. When model is queried, queries are sent to data source and data is retrieved. 

When to use DirectQuery Mode:

• When data volume is large and continuously growing. And it is difficult to load the data and refresh the model.
• When data needs to be in real time and beyond the scheduled refresh frequency limit. Scheduled refresh can be done 8 times a day for shared capacity and 48 times a day for premium users. 

There are some advantages for using DirectQuery Mode:

• There is no limitations on size of the data. 
• The data is nearly real time.
• Dashboard tiles, when based on DirectQuery models, can update automatically as frequently as every 15 minutes

There are some limitations of DirectQuery method.

• DirectQuery sends all requests to the source database, so the required refresh time for visuals depends on how long the underlying source takes to return results. The end to end refresh time depends on multiple factors like resources available, if there is no issue in the network etc.
• Quick insights feature is not available.

Composite Mode

Composite Mode is mix of both import and direct query. Models developed in Composite mode support configuring the storage mode for each model table. The storage mode can be configured as Import, DirectQuery or Dual.

    Data modelers who develop Composite models are likely to configure dimension-type tables in Import or Dual storage mode, and fact-type tables in DirectQuery mode. 

     Consider a data model where Product dimension table have dual mode which can be quickly pulled from in memory. Dimensions don’t change often so can be imported in memory. Where as Sales a fact table with DirectQuery Mode can be queried from data source for the related Products to get the real time data. Then these 2 data sources can be joined using native query. 

Query Modes can be changed as below.