Model Context Protocol (MCP) finally gives AI models a way to access the business data needed to make them really useful at work. CData MCP Servers have the depth and performance to make sure AI has access to all of the answers.
Try them now for free →
Top CData Connectors for Microsoft Power BI
DirectQuery is one of the standout features of Microsoft Power BI, perfect for analyzing large volumes of data from sources like Elasticsearch, MongoDB, MySQL, Snowflake, and SAP BusinessObjects. Typically, Power BI imports data from databases (DB) or data warehouses into Power BI Desktop for analysis, known as Import Mode. However, when dealing with large data volumes, importing all the data into Power BI is neither efficient nor realistic. That's where DirectQuery Mode comes in, allowing you to query and analyze data directly from the source without importing it into Power BI. CData Power BI Connectors fully support this powerful feature. This article will explain Power BI DirectQuery and introduce popular CData Connectors for DirectQuery purposes!
What is Power BI DirectQuery?
When analyzing data in Power BI Desktop, you typically import the data first. This method, called Import Mode, leverages Power BI's high-performance query engine for data analysis. However, when the data is too large to import, you can analyze it directly from the DB or data warehouses without importing it into Power BI. This is known as DirectQuery Mode.
To use DirectQuery in Power BI, select DirectQuery during the Get Data operation. This mode returns only the aggregation query results, which are much smaller than the full dataset, making analysis more efficient.
Please see the detail article how to use DirectQuery with Google BigQuery in Power BI.
When to Use DirectQuery (Benefits)
- Large Data Volumes: DirectQuery is ideal for aggregating large datasets. For example, when storing data in Elasticsearch, Google BigQuery, or Snowflake, importing all the data into Power BI is impractical. DirectQuery allows Power BI to handle these large volumes efficiently.
- Near Real-Time Data: If you need to analyze near real-time data, DirectQuery is the way to go. Power BI has a refresh limit for imported data (8 times a day for Power BI Pro). DirectQuery ensures the latest data is always displayed when you open or refresh a report or dashboard, with dashboard tiles updating as frequently as every 15 minutes.
- Dataset Size Limitations: Power BI Desktop limits dataset sizes to 1 GB for the free version and 10 GB for Power BI Pro. DirectQuery helps you work around these limitations by querying data directly from the source.
Points to Note When Using DirectQuery
- Performance: Since data is updated directly from the source each time you open the dashboard, data loading may take time. Ensure your data source performs well to avoid issues.
- 1 Million Row Limit: DirectQuery has a limit of 1 million rows per query. Use aggregation and filters to manage this limit.
- ODBC Connections: DirectQuery cannot be used with generic ODBC connections. Some sources supported natively by Power BI do not support DirectQuery. This is why CData provides a separate Power BI Connector series.
Popular CData Power BI Connectors for DirectQuery Purpose
From Power BI documentation, here are some sources that do not support DirectQuery (as of March 12, 2025). We selected data sources with large volumes.
| Data Source | Native DirectQuery Support | Note |
|---|---|---|
| MysQL | No | Supported by CData MySQL Connector. |
| MongoDB | Yes | Only DBaaS Atlas is supported. CData supports on-premises MongoDB too. |
| Salesforce | No | Supported by CData Salesforce Connector. |
| DB2 | Yes | Only LUW. AS400, IBM i versions are supported by CData Connector. |
| Sybase | No | Supported by CData Sybase Connector. |
| Zendesk | No | Supported by CData Zendesk Connector. |
| FHIR | No | Supported by CData FHIR Connector. |
Check Out the Popular CData Power BI Connector Sources!
The CData Power BI Connector series, which enables you to connect to over 270 types of DB, DWH, and SaaS sources, supports DirectQuery. Here is a list of CData Power BI Connectors that are popular for DirectQuery functionality:
- Elasticsearch: Enterprise search with a large amount of data. Power BI does not provide a connector for this source. CData Power BI Connector supports Elasticsearch v2.2 and later REST APIs. If the Elasticsearch plan allows API usage, the connector can be used.
- MySQL: A major RDB known to all data users. Power BI's connector does not support DirectQuery. CData Power BI Connector, of course, supports DirectQuery. It can connect to MySQL not only locally but also in cloud environments like Amazon RDS and Google Cloud SQL.
- MongoDB: MongoDB has both an installed version and a cloud service version called MongoDB Atlas. Power BI natively supports only the MongoDB Atlas version. CData Power BI Connector is popular among users hosting MongoDB. MongoDB has large data volumes and a schema-less structure. CData Power BI Connector for MongoDB scans MongoDB objects, automatically detects table schemas, and makes it easy to analyze in Power BI.
- DB2: IBM's DB2, often used in core systems. CData supports Power BI Connector for AS400 and IBM i DB2 as well. It is popular for analyzing DB2 data directly from Power BI without importing data into a DWH.
- Google BigQuery: Many users choose CData connectors for performance and functionality. Performance is crucial for DirectQuery. Check out the performance comparison of CData drivers here.
- Snowflake: Many users prefer CData connectors for performance and functionality.
- Salesforce: The pioneer of enterprise CRM. CData Power BI Connector for Salesforce is popular among users who want DirectQuery functionality not supported by Power BI.
Other data sources where DirectQuery is commonly used include:
Free Trial & More Information
We provide 30-day trial of CData Power BI Connectors with no feature or row limitation. Please click and downloadCData Power BI Connectors and use DirectQuery with the source of your choice.
