4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection
Friends, in a company most data lives in a database – SQL Server or MySQL. In this module we learn how to connect to a database, how to write native SQL, what query folding (handing the work to the source database) is, and the clear difference between Import, DirectQuery (data stays in the source and every click sends a live query) and Live connection. Interviewers ask this a lot, so read carefully.
मित्रांनो, company मध्ये बहुतेक data database मध्ये असतो – SQL Server किंवा MySQL. या module मध्ये database ला connect कसं करायचं, native SQL कसं लिहायचं, query folding (काम source database कडे सोपवणं) काय आहे, आणि Import, DirectQuery (data source मध्येच राहतो, प्रत्येक click ला थेट query जाते) आणि Live connection मधला फरक अगदी clear करू. Interview मध्ये हे खूप विचारलं जातं, म्हणून लक्ष देऊन वाचा.
दोस्तों, company में ज़्यादातर data database में होता है – SQL Server या MySQL. इस module में database से connect कैसे करें, native SQL कैसे लिखें, query folding (काम source database को सौंपना) क्या है, और Import, DirectQuery (data source में ही रहता है, हर click पर सीधी query जाती है) और Live connection का फ़र्क बिल्कुल clear करेंगे. Interview में यह बहुत पूछा जाता है, इसलिए ध्यान से पढ़िए.
What you will learn in this module
- Database words every analyst must know: server, database, schema, table, view
- Connecting to Microsoft SQL Server step by step: authentication, Navigator, native SQL queries
- Query folding with databases and View Native Query
- Connecting to MySQL, including the driver install and common errors
- Import vs DirectQuery vs Live connection, explained clearly
- The on-premises data gateway, data source settings and privacy levels
Live story: Orders live in SQL Server
Suppose we are on an Infosys client project for a grocery chain. Orders are not in Excel any more. They sit in a SQL Server database with 50 lakh rows. The client asks: "Import or DirectQuery? Which is faster and which is safer?" This chapter connects to SQL Server and MySQL and explains Import, DirectQuery and Live connection in simple words.
All numbers in this story are fictional practice data.
Live story: Orders SQL Server मध्ये आहेत
समजा आपण एका grocery chain च्या Infosys client project वर आहोत. Orders आता Excel मध्ये नाहीत. ते 50 लाख rows असलेल्या SQL Server database मध्ये आहेत. Client विचारतो: "Import की DirectQuery? कोणतं fast आणि कोणतं safe?" हा chapter SQL Server आणि MySQL ला connect करतो आणि Import, DirectQuery आणि Live connection सोप्या शब्दांत समजावतो.
या story मधले सगळे आकडे काल्पनिक practice data आहेत.
Live story: Orders SQL Server में हैं
मान लीजिए हम एक grocery chain के Infosys client project पर हैं. Orders अब Excel में नहीं हैं. वे 50 लाख rows वाले SQL Server database में हैं. Client पूछता है: "Import या DirectQuery? कौन-सा fast है और कौन-सा safe?" यह chapter SQL Server और MySQL से connect करता है और Import, DirectQuery और Live connection आसान शब्दों में समझाता है.
इस story के सारे आँकड़े काल्पनिक practice data हैं.
In real companies, quick-commerce orders are stored in databases, not Excel files. The store app writes each order into tables, and analysts connect Power BI to those tables. This module uses a fictional practice database called QuickCommerceDB, with the same tables as our sample data (Module 1.7).
Concepts in this chapter
- 4.1Database Terms in Simple Words
- 4.2Connecting to Microsoft SQL Server
- 4.3Advanced Options and Native SQL Queries
- 4.4Query Folding with Databases and View Native Query
- 4.5Connecting to MySQL
- 4.6DirectQuery in Practice
- 4.7Import vs DirectQuery vs Live Connection – Clearly Explained
- 4.8The On-premises Data Gateway
- 4.9Data Source Settings, Changing Sources and Privacy Levels
- 4.10Connecting to PostgreSQL
- 4.11Azure Sources
- 4.12SQL for Power BI: Views, Joins, CTEs and Where to Transform
The chapter recap is at the end of the last concept page.
Ravi Sir's Cards: 17 revision, interview and comparison cards for this chapter · मराठी cards