Power BI Import vs. Direct Query: Understand Differences

Choosing to click either the Import or Direct Query box is one of the first decisions you’ll have to make as a Power BI developer. The decision is dependent on your report, but you should also have a strategy in place on how to use both options.

This guide gives more insight into the Power BI import vs direct query debate. It shows you what the Power BI import is, what direct query in Power BI is, the key differences between the Power BI direct query vs import, and its advantages and disadvantages.

Table of Contents:

1. What is Power BI Import?
2. What is DirectQuery in Power BI?
3. What are the Key Differences between Power BI Directquery vs. Import?
4. Which Method is Better: Power BI Import vs. Direct Query?
5. How to Manually Import Data to Power BI?
6. How to Connect Using DirectQuery?
7. How to Visualize Your Data in Power BI?
8. What are the Advantages & Disadvantages of Power BI Direct Query vs Import?
9. Wrap Up

First…

What is Power BI Import?

With the Import method, data imported into the Power BI is stored in a compressed in-memory column store. This method comes in handy when dealing with smaller data sets or when the data is updated infrequently.

Users who use the Import method can create complex Power BI data models, create visualizations and reports based on the imported data, and perform advanced calculations using the Data Analysis Expressions (DAX) language.

What is DirectQuery in Power BI?

When using the DirectQuery method, data stays in the source system and it’s queried directly by Power BI. This method comes in handy when you’re handling large Power BI datasets or when the data is frequently updated.

Users who use DirectQuery can create data models that span multiple tables or even multiple data sources and perform real-time data analysis of the source data. Since the data is not imported into Power BI, you’ll have no need for manual data refresh. After all, the data is always up-to-date.

What are the Key Differences between Power BI Directquery vs. Import?

Data Storage:

• Import: Here, data from the external data sources is imported into Power BI’s internal data model. The data is stored within the Power BI file itself, and that makes it independent of the original data source once imported. This will, in turn, lead to faster report rendering and better performance for smaller datasets.
• DirectQuery: Here, Power BI connects directly to the external data source in real-time without importing the data into Power BI. This way, data resides entirely in the original data source, and queries are executed against that source whenever a data visualization is rendered.

Performance:

• Import: Importing data into Power BI usually leads to faster report performance.
• DirectQuery: It offers real-time access to large datasets without the need to import them into Power BI. Performance in DirectQuery varies depending on the complexity of the queries and the responsiveness of the data source.

Data Freshness:

• Import: Since data is imported into Power BI, it’s static until the dataset is refreshed.
• DirectQuery: Data is queried in real-time from the source system. This way, the data shown in the report is always up-to-date and reflects the current state of the source system.

Data Volume:

• Import: Import is best fitted for smaller to medium-sized datasets. It should be used when historical data needs to be stored within Power BI, or when data doesn’t change frequently.
• DirectQuery: It’s best fit for large datasets, or when real-time data access is needed. It’s used with big data platforms, cloud-base data warehouses, or other systems that can handle large volumes of data.

Which Method is Better: Power BI Import vs. Direct Query?

• Data Volume: If you’ll be dealing with large datasets that are frequently updated, and require real-time access, then DirectQuery is your best choice. It helps users avoid data duplication. However, if you are handling smaller to medium-sized datasets or working in conditions where offline accessibility is of the essence, then you should opt for Import.
• Data Freshness: DirectQuery offers real-time access to data, and that ascertains that the latest information is available. Import requires periodic refreshing of data, and that could lead to outdated insights, especially in cases where real-time data is needed.
• Performance: Since data is stored locally within Power BI during Import, it offers faster performance. DirectQuery may come with latency as a data source is queried in real-time, especially when the user is handling large datasets and complex queries.
• Data Transformation: Import gives room for extensive data transformation and modeling within Power BI, and that translates to flexibility for complex transformations. DirectQuery limits data modeling capabilities as transformations have to be performed in the source system (or through Power Query).

How to Manually Import Data to Power BI?

Here are steps to help you manually import Analytics data through Power BI.

• In the lower left screen of the Power BI, click “Get Data.”
• Under “Import or Connect to Data,” click “Files.” After that, click “Get.”

Click “Local File.”

Choose the file to upload and click “Open.” Next, click “Upload” under “Upload your Excel file to Power BI.”

You’ll see the “Your file has been uploaded” message.

How to Connect Using DirectQuery?

First, navigate to the “Home” section of the Power BI Desktop ribbon, and select “Get data.” After that, select a data source that DirectQuery supports like “SQL Server.”

In the dialog box, under the “Data connectivity mode,” select “DirectQuery.”

How to Visualize Your Data in Power BI?

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Quarters	Vendors	Market Share
Q1	Samsung	27.69
Q1	Apple	28.45
Q1	Xiaomi	11.8
Q1	Huawei	6.53
Q1	Oppo	5.3
Q1	Vivo	4.19
Q2	Samsung	28.14
Q2	Apple	27.58
Q2	Xiaomi	12.62
Q2	Huawei	6.17
Q2	Oppo	5.5
Q2	Vivo	4.21
Q3	Samsung	28.45
Q3	Apple	27.71
Q3	Xiaomi	12.9
Q3	Huawei	6
Q3	Oppo	5.29
Q3	Vivo	4.17
Q4	Samsung	27.97
Q4	Apple	27.62
Q4	Xiaomi	12.68
Q4	Huawei	5.17
Q4	Oppo	6.07
Q4	Vivo	4.66

• Paste the data table above into the “Power Query” window, and name it “Market Share Analysis.” Next, select the “Create a dataset only” option.

• Navigate to the left-side menu, and click on the “Data Hub’ option. Power BI will populate the data set list. If no data set has been created, you’ll get an error message.

• Click on “Create report.”

• Click on “Market Share Analysis.”

• Click on “Expand All.”

• To see your chart metrics, click on “Expand All.”

• Click on “Get more visuals.”

• Search for “ChartExpo” and select “Comparison Bar Chart.”

• Click on “Add.”

• You’ll see the Comparison Bar Chart in the visuals list.

• You can expand the chart space.

• Select the metrics of your data.

• Click on the second icon of Format visuals. After that, click on visual.

• In Visual, you can click on License Settings and add the key. After adding the key, you’ll see the comparison bar chart.

• To add the header text, click on the General tab. Next, add the header text in the Title.

• Here’s the final look at the Comparison Bar Chart in Power BI.

Insights

• Samsung and Apple are the top two vendors in terms of market share, with Samsung taking the lead.
• Xiaomi has been gaining market share, while Huawei has been losing market share.
• Oppo and Vivo have been relatively stable in terms of market share.

What are the Advantages & Disadvantages of Power BI Direct Query vs Import?

Import:

Advantages:

• Offline Accessibility: Reports can be accessed without a live connection to the data source.
• Faster Performance: Data is locally stored, and that results in quicker report rendering.
• Extensive Data Modeling: It gives room for complex data transformations and relationships within Power BI.

Disadvantages:

• Limited Real-time Analysis: It does not offer real-time access to data. Users will have to depend on scheduled refreshes for updates.
• Static Data: Data is refreshed periodically, and that could lead to outdated insights.
• Storage Constraints: Larger datasets may exceed file size limits, or cause performance issues.

DirectQuery:

Advantages:

• Reduced Data Duplication: It eliminates the need for local data storage, and that reduces redundancy.
• Scalability: Since data is queried in real-time, you can use it when handling large datasets.
• Real-time Data Access: It provides up-to-date insights directly from the data source.

Disadvantages:

• Limited Data Modeling: It offers less flexibility for complex data transformations within Power BI.
• Slower Performance: Querying data in real-time leads to slower report rendering.
• Dependency on Source System: Performance and availability are contingent on the responsiveness of the data source.

FAQs

Can you change from direct query to Import in Power BI?

Yes, you can change from DirectQuery to Import in Power BI by modifying the data source settings within the report’s data model. It involves data importation into Power BI’s internal storage.

What are two benefits of using DirectQuery instead of Import?

Two benefits of using DirectQuery over Import in Power BI are real-time data access that ensures up-to-date insights, and avoiding data duplication. Avoiding data duplication helps reduce storage requirements and ascertain data integrity.

Can we create measures in a direct query?

Yes, measures can be created in DirectQuery mode in Power BI. It’s important to know that complex calculations may impact performance due to real-time querying.

Wrap Up

When it comes to the Power BI Import vs Direct Query debate, the major difference is in where the data resides. Import imports data into Power BI, while DirectQuery connects to the source in real-time. The data location affects the performance, scalability, and data freshness. It also impacts decision-making and analysis.

Power BI Import gives room for extensive data transformation and modeling capabilities when data is imported. Users can apply various transformations, define relationships between tables, and create calculated columns. However, data transformations are limited in DirectQuery mode since the data resides in the source system. There are cases where transformations have to be performed directly in the source system (or through Power Query in Power BI).

By following the steps in this guide, you’ll become a proficient Power BI user and will be able to create more engaging and interactive visualizations.