4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection
4.6 DirectQuery in Practice
With DirectQuery, Power BI stores fakt the model structure (tables, relationships, measures). Each time a visual loads or a slicer changes, it sends SQL to the database.
Steps in Power BI
- Get data › SQL Server › Data Connectivity mode: DirectQuery › OK.
- Select tables › Transform Data (only transformations that fold are allowed. Otherwise you see This step results in a query that is not supported in DirectQuery mode).
- In Model view, select a table › Properties › Advanced › Storage mode to see DirectQuery. For small dimension tables you can choose Dual (composite model).
- In Report view, use Optimize › Optimization presets or File › Options and settings › Options › Current File › Query reduction to reduce the number of queries sent (for example add Apply buttons to slicers and filters).
- Use Optimize › Performance analyzer to see the DirectQuery time of each visual.
Ravindra Bagale's Tip
Friends, many students choose DirectQuery for a small dataset and then write heavy row-by-row DAX that turns into slow SQL. Use DirectQuery only when you really need it, keep measures simple, and talk to the DBA first, because every click in the report sends queries to the production database. Clear?
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students छोट्या dataset साठी DirectQuery निवडतात आणि मग जड row-by-row DAX लिहितात, जो slow SQL मध्ये बदलतो. DirectQuery खरंच गरज असेल तेव्हाच वापरा, measures सोपे ठेवा, आणि आधी DBA शी बोला, कारण report मधला प्रत्येक click production database ला queries पाठवतो. समजलं का?
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students छोटे dataset के लिए DirectQuery चुनते हैं और फिर भारी row-by-row DAX लिखते हैं जो slow SQL बन जाता है. DirectQuery तभी इस्तेमाल करो जब सच में ज़रूरत हो, measures आसान रखो, और पहले DBA से बात करो, क्योंकि report का हर click production database को queries भेजता है. समझ आया?