Power BI is a powerful business intelligence tool for analyzing your data and presenting it visually.
Power BI is best known for the impressive data visualizations and dashboard capabilities it has. However, before you can begin building reports, you first need to connect to the necessary data sources. When establishing a connection to a data source, you may be presented with one of two different options on how your data should be treated: Import or DirectQuery. This blog will help you determine when to use Import Mode or DirectQueryMode for a project.
Power BI offers two types of connections to build a report.
• Import Mode
• DirectQuery Mode
To decide which connection to use do a preliminary check by asking yourself the following questions, before loading the data.
1. Is your requirement to load the data daily or weekly?
2. If your requirement to load data is daily, how frequently should it be refreshed?
3. What is the size of your dataset?
4. Do you always need fresh data?
This section enumerates a few thumb rules for easy decision making to choose between Import Mode Vs DirectQuery.
• Import Mode is most suitable and recommended if the requirement to load data is weekly or daily.
• If however, the requirement to load data is many times in a day – like daily with a 3-hour frequency – then Import Mode rather than DirectQuery Mode is recommended.
• If the objective is to always have fresh data, then DirectQuery Mode is required.
• If your dataset is less than 1 GB, then the Import mode is suitable. For larger datasets, it is better to connect with DirectQuery mode.
This section lists the initial steps while using DirectQuery and also highlights some key facts.
Following are the steps while using DirectQuery:
• Whenever the user interacts with any Power BI visualization, a query is sent to the actual data source.
• The performance can be checked using SQL Profiler if there is a connected SQL Server database.
• If the page has 10 visualizations, then a database query is called for whenever a visual is clicked.
• An independent query to the original data source is created for each and every visualization.