4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection
4.7 Import vs DirectQuery vs Live Connection – Clearly Explained
Quick answer: the short, self-contained version of this lesson is the guide Composite Models in Power BI.
Start with a problem
In an interview Ruhi says DirectQuery and Live connection are the same thing. The panel frowns.
Explain simply
DirectQuery connects to a database and you build the model. Live connection connects to an existing semantic model; the model already exists. Import copies data into your file.
Show the data
| Report | Source | Who builds the model |
|---|---|---|
| Training report on 8 orders | CSV | Ravindra |
| Ops monitor | SQL Server (live data) | Ops team |
| City managers' reports | QC Sales model published by Rani's team | Rani's team |
About this data: This table uses columns: Report, Source, Who builds the model. This table uses columns: Storage. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
-
Training report → Get data › Text/CSV › Import.
Your result should look like this:
Storage Import; data inside the file -
Ops monitor → Get data › SQL Server › DirectQuery.
Your result should look like this:
Storage DirectQuery; you still create relationships and measures -
City managers → Home › Power BI semantic models › QC Sales Model › Connect.
Your result should look like this:
Status bar Connected live to the Power BI semantic model -
In the live report, add a report measure: Modeling › New measure.
- To add your own Excel targets, click Make changes to this model (creates a composite model).
Show the result
Your result should look like this (fictional sample):
| Question | Import | DirectQuery | Live connection |
|---|---|---|---|
| Where is the data? | In the model | In the database | In the remote model |
| Who builds the model? | You | You | Someone else |
| Power Query? | Full | Folding steps only | No |
Explain what happened
The deciding question is "what am I connecting to?" A database (DirectQuery) or an existing model (Live connection).
Explore further
- A composite model can mix a live semantic model with your own Import table.
Detailed reference for this lesson:
Many students confuse DirectQuery and Live connection. Both leave the data outside your .pbix file, but they connect to different things:
- DirectQuery connects to a data source (a database such as SQL Server). You build the model (relationships, measures) in your file.
- Live connection connects to an existing semantic model, either a published Power BI semantic model or an Analysis Services model. The model already exists. Your file only contains the report.
| Question | Import | DirectQuery | Live connection |
|---|---|---|---|
| Connects to | Any source | A relational source (SQL Server, Azure SQL, Snowflake …) | A Power BI semantic model or Analysis Services |
| Where is the data at report time? | Copied into the model (VertiPaq) | Stays in the source database | Inside the remote model |
| Who builds the model? | You | You | Someone else (central BI team) |
| Power Query available? | Full | Only steps that fold | No |
| Calculated columns/tables | Yes | Yes (limited) | No (report-level measures only) |
| Speed | Fastest | Depends on the database | Depends on the remote model |
| Freshness | As of last refresh | Near real-time | As fresh as the remote model |
| Refresh needed? | Yes (scheduled) | No data refresh (queries run live) | Refresh happens on the remote model |
| How to create | Get data › choose source › Import | Get data › SQL Server › DirectQuery | Home › Power BI semantic models (OneLake catalog) or Get data › Analysis Services › Connect live |
| Blinkit example | 12 months of CSV + SQL data for training reports | Live order monitoring on the ops database | City managers build reports on the central "QC Sales" model published by Rani's team |
Steps in Power BI – create a Live connection
- Home › Power BI semantic models (in newer versions via the OneLake catalog).
- Pick the published model, e.g. QC Sales Model in the QC – Maharashtra workspace › Connect.
- The status bar at the bottom shows Connected live to the Power BI semantic model …. The Table and Model views show the model, but you cannot edit Power Query.
- You can still add report measures (Modeling › New measure).
- To add your own table (for example a targets Excel file), click Make changes to this model (status bar or Modeling tab). This converts the file to a composite model using DirectQuery for Power BI semantic models.
Practice task
Explain in two sentences to Ruhi (a new analyst) why the city managers' reports use a Live connection while the operations team's real-time monitor uses DirectQuery.
This table is very important, friends. Read it once more before your interview.
हा table खूप महत्त्वाचा आहे, मित्रांनो. Interview च्या आधी पुन्हा एकदा नक्की वाचा.
यह table बहुत ज़रूरी है, दोस्तों. Interview से पहले एक बार फिर ज़रूर पढ़िए.
Ravindra Bagale's Tip
Remember one thing: a very common interview mistake is saying Live connection and DirectQuery are the same. With DirectQuery you build the model on top of a database. With Live connection you connect to an existing semantic model or Analysis Services model and cannot change relationships or add Power Query steps. Learn the comparison table in this section.
Ravindra Bagale's Tip – मराठी
एक गोष्ट लक्षात ठेवा: Live connection आणि DirectQuery एकच आहेत असं म्हणणं ही interview मधली खूप common चूक आहे. DirectQuery मध्ये तुम्ही database वर model बनवता. Live connection मध्ये तुम्ही आधीपासून असलेल्या semantic model किंवा Analysis Services model ला connect करता आणि relationships बदलू किंवा Power Query steps जोडू शकत नाही. या section मधला comparison table नीट शिका.
Ravindra Bagale's Tip – हिंदी
एक बात याद रखो: Live connection और DirectQuery को एक ही बताना interview की बहुत common गलती है. DirectQuery में तुम database के ऊपर model बनाते हो. Live connection में तुम पहले से मौजूद semantic model या Analysis Services model से connect करते हो और relationships बदल या Power Query steps जोड़ नहीं सकते. इस section की comparison table अच्छे से सीखो.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| "They are the same" | Both keep data outside the file | Database vs existing semantic model |
| Looking for Power Query in a live report | Live connection has none | Use report measures or a composite model |
| Duplicate city models across teams | Everyone imports their own copy | Publish one central model; use Live connection |
Practise independently
Practice task
Explain in two sentences to Ruhi why city managers use a Live connection and ops uses DirectQuery.
Hints
- Who owns the model? How fresh must the data be?
Solution
City managers reuse Rani's governed central model, so a Live connection gives them one version of the truth without building a model. Ops needs near-real-time data from the database and builds its own small model, so DirectQuery fits.
Think like a professional
Interviewers test this distinction often; answer with "what it connects to" and "who builds the model".
Revision card