by Dibyendu Datta | February 16, 2023

Extending Real-Time Connectivity in Power BI

Power BI is an interactive data visualization product developed by Microsoft that can connect with a wide variety of data sources through different connectivity modes. The availability of each data connectivity mode depends on which data source is chosen in Power BI.

For most data sources available in Power BI, the only available connection method is the “Import” option. For some databases and services, there are two modes of connection, namely “Import” and “DirectQuery.” For your strategic business decisions, you need to choose which connection method you would want to use beforehand, and each method (Import or DirectQuery) has its own advantages and disadvantages.

In this article, we explore the Import and DirectQuery connectivity modes and describe how CData Power BI Connectors expand real-time connectivity in Power BI by enabling DirectQuery mode for more than 250 SaaS, Big Data, and NoSQL data sources. Additionally, we showcase a collection of use cases where CData customers are leveraging DirectQuery in Power BI.

Connection Modes

Import:

• Data is cached, creating a point-in-time snapshot of the data
• Available for every data source in Power BI
• Every interaction and filter applied to the data is done to the compressed cache instead of the actual data source itself
• Fastest option for exploring data since the dataset is cache in memory
• Import supports datasets up to 1GB

Direct Query:

• Data sources are queried directly, providing real-time connectivity
• Available for a limited set of data sources in Power BI
• Every filter and interaction with the report generates a new query to the data source
• Best option for evaluating live data or considering incremental changes in the data source
• DirectQuery is necessary for large datasets (Import only supports up to 1GB of data)

DirectQuery with CData Power BI Connectors

CData seized a unique opportunity to develop third-party Power BI connectors for more than 250+ data sources, including ERP and CRM systems, NoSQL and Big Data stores, and more. These data sources can be connected by installing CData connectors alongside the Power BI application, extending Power BI’s data reach beyond its built-in native connections and helping users to connect with more data sources.

Before you start to visualize the data, you must load the data. The Power BI Connectors developed by CData ensure uniform connectivity to these sources and helps provide access to invaluable enterprise data for data load, data visualization, and data manipulation.

Mid- to high-level enterprises deal with huge volumes of data on a regular basis. As we already know, the Import mode is unreliable when it comes to analyzing large datasets. CData developed third-party connectors to solve this challenge, allowing our customers to utilize DirectQuery connections within Power BI instead – which are quite effective with large volume datasets.

CData provides Power BI connectors for over 250+ data sources and applications to run real-time queries and work on virtually live data. Since the dataset refreshes automatically every 15 minutes, organizations know the data being visualized and analyzed is current and up to date. This not only effectively reduces the effort for the enterprise to analyze these large datasets but also gives them the flexibility to switch between the connection modes, from DirectQuery to Import, if need be.

CData Power BI Customer Stories

Let’s look at some of the popular customer use cases preferring CData Power BI Connectors over Power BI’s natively used data sources

• A multinational information technology company uses Presto as the interface for its underlying enterprise databases and data stores, but the native import functionality available in Power BI was unable to handle the size of the datasets needed for reporting. As such, the company implemented the CData Power BI Connector for Presto to retrieve data from its databases and can generate reports in real-time using the DirectQuery connection mode.
• A well-known marketplace, logistics, and payment service company needed DirectQuery in Power BI for their Hive data warehouse and prefers the CData Power BI Hive connector over the native Hive LLAP connector.
• A professional training services company uses our Dynamics 365 and Sage Intacct Power BI connectors for real-time visualisations and to meet their active business requirements.
• A top US fast-moving consumer goods (FMCG) company uses DirectQuery on a non-relational database MongoDB through the CData Power BI Connector, catering to their dynamic reporting needs.
• One of the top business consulting firms started using the CData Power BI Connector for Couchbase for their day-to-day reporting use cases.

Free Trial & More Information

Thanks to the CData Power BI Connectors, users can connect Power BI to live data from SaaS, Big Data and NoSQL sources to perform analytics and build visualizations to drive business.

Get started with a free 30-day trial of any of the Power BI Connectors. For more videos, head over to our YouTube channel. As always, let us know if you have any questions during your evaluation. Our world-class CData Support Team is always available to help.