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
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 अच्छे से सीखो.