4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection
4.7 Import vs DirectQuery vs Live Connection – Clearly Explained
Many students confuse DirectQuery and Live connection. Both leave the data outside your .pbix file, pan 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
Aata he bagha: 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.
Ha table khup important aahe, mitrano. Interview chya aadhi punha ekda nakki vacha.
Ravindra Bagale's Tip
Ek goshta lakshat theva: A khup 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.