Ravindra BagaleCourses & study guides

4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection

4.6 DirectQuery in Practice

By Last updated:

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

  1. Get data › SQL Server › Data Connectivity mode: DirectQuery › OK.
  2. 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).
  3. In Model view, select a table › Properties › Advanced › Storage mode to see DirectQuery. For small dimension tables you can choose Dual (composite model).
  4. 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).
  5. Use Optimize › Performance analyzer to see the DirectQuery time of each visual.

Ravindra Bagale's Tip

Mitrano, khup 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, karan every click in the report sends queries to the production database. Samjla ka?