What is a DirectQuery, you ask? A method of retrieving data from the source at time of query execution. Goes straight to the data source for every request. In contrast to import mode, where you upload the data. You can pull right from SQL Server or whatever. When interacting with a tabular model, that consists of a formal engine and storage engine: 1. Accepts the request and creates a query plan. 2. Generates the query to target respective data source (based on choice between import and DQ) - with DQ: DAX is converted to SQL and sent directly to the data source. Will only need to hold the meta data from the tables When to use DirectQuery? Depends on three (3) scenarios: - Large data set. Size of data can't be accommodated in maximum PBX file size - 1 GB for Pro, 10GB for Premium - If you need real-time or near-real-time data, i.e., Stream Processing - Access control. Security policies on the data-source side you need to uphold. For consideration prior to using DQ - User experience with DQ will depend almost exclusively on the quality of the underlying data-source. - Is your source database optimized for the analytic workload? - Are the indexes missing/inappropriate? Is data missing? - Is the data model in place for targeting multiple queries? - Number of users interacting with the data-source will have an impact on performance - E.g., each visual will generate at least one query per data source - You won't be able to control network latency, Best Practices - Create proper indexes to support your most exhaustive queries - Consider creating column store indexes for large analytical workloads -- Ask your DBA for help! - Data integrity in place - Dims contain proper keys and relate smoothly to your fact tables - Persistent data objects in source database - Materialize all aggregations, transformations, and calculations in a special table or indexed view - PQ will be able to retrieve from a single place instead of performing complex operations every time a query is being executed Try to Avoid - Complex PQ transformations - Try to push calculated columns to the source database, and keep them consistent - Avoid complex DAX measures - Processing the queries can produce expensive SQL queries - Avoid relationships on GUID columns -- Globally Unique Identifier columns - PQ does not fluidly accept this data type, requires internal data conversion upon execution - Limit parallelism and apply query reduction - Can limit the amount of connections DQ can open at the same time In conclusion: Avoid DirectQuery if possible!!!
What is a DirectQuery, you ask?
A method of retrieving data from the source at time of query execution. Goes straight to the data source for every request.
In contrast to import mode, where you upload the data. You can pull right from SQL Server or whatever.
When interacting with a tabular model, that consists of a formal engine and storage engine:
1. Accepts the request and creates a query plan.
2. Generates the query to target respective data source (based on choice between import and DQ)
- with DQ: DAX is converted to SQL and sent directly to the data source. Will only need to hold the meta data from the tables
When to use DirectQuery?
Depends on three (3) scenarios:
- Large data set. Size of data can't be accommodated in maximum PBX file size
- 1 GB for Pro, 10GB for Premium
- If you need real-time or near-real-time data, i.e., Stream Processing
- Access control. Security policies on the data-source side you need to uphold.
For consideration prior to using DQ
- User experience with DQ will depend almost exclusively on the quality of the underlying data-source.
- Is your source database optimized for the analytic workload?
- Are the indexes missing/inappropriate? Is data missing?
- Is the data model in place for targeting multiple queries?
- Number of users interacting with the data-source will have an impact on performance
- E.g., each visual will generate at least one query per data source
- You won't be able to control network latency,
Best Practices
- Create proper indexes to support your most exhaustive queries
- Consider creating column store indexes for large analytical workloads -- Ask your DBA for help!
- Data integrity in place
- Dims contain proper keys and relate smoothly to your fact tables
- Persistent data objects in source database
- Materialize all aggregations, transformations, and calculations in a special table or indexed view
- PQ will be able to retrieve from a single place instead of performing complex operations every time a query is being executed
Try to Avoid
- Complex PQ transformations
- Try to push calculated columns to the source database, and keep them consistent
- Avoid complex DAX measures
- Processing the queries can produce expensive SQL queries
- Avoid relationships on GUID columns -- Globally Unique Identifier columns
- PQ does not fluidly accept this data type, requires internal data conversion upon execution
- Limit parallelism and apply query reduction
- Can limit the amount of connections DQ can open at the same time
In conclusion: Avoid DirectQuery if possible!!!
wow🤩 what a dedication! great thanks 🥰
Thank you. This is a great summary
Thank you for your videos! Wish you made a Udemy course D: There's no great ones out there right now. Just a thought ;)
Great suggestion!