Qubole-Power BI Integration Guide (Azure)

Introduction

Microsoft Power BI is a widely used business analytics solution for data visualizations and reporting. It facilitates the analysis of data through Import and DirectQuery modes.

  • Import mode requires that data be imported into Power BI, which can present latency limitations when working with big data sets greater than 1 GB. Additionally, dashboards can get stale and need to be refreshed to reflect changes in underlying datasets.
  • DirectQuery mode queries the data directly from the source without the need for importing the data into Power BI. This ensures that your visualization is up-to-date with every selection made in your Power BI report.

The Qubole big data platform processes petabytes of data efficiently, making it ideal for processing data in DirectQuery mode. Qubole offers Presto as a service on Azure for ad hoc queries, exploratory analytics, reporting, and dashboards. Presto is based on an MPP (Massively Parallel Processing) design, and hence widely used for processing ad hoc queries at tremendous speed and scale. One of the differentiators of Presto is its ability to process data across multiple sources without needing to import data into a central data lake. This makes Presto an ideal candidate for use with Power BI DirectQuery mode.

Solution description

Power BI DirectQuery mode allows processing of data in place without the need to import data into the Power BI client. However, DirectQuery mode is limited to querying a single source at a time. Qubole’s Presto connector removes this limitation from Power BI in DirectQuery mode by allowing users to run queries from multiple data sources, whether stored in an Azure data lake or on compatible databases.

Qubole’s Presto connector for Power BI allows end users to authenticate securely via an API token to a Presto cluster running in the customer’s Azure tenant. Qubole’s Presto engine provides visibility to all available data catalogs that have been configured. This can include data in a data lake, relational databases such as SQL server, NoSQL databases such as Cosmos DB, and real-time data streaming applications such as Kafka. Using Qubole’s connector, Power BI users can now select tables across any of these catalogs, and create and execute DAX queries across these different data sources for reporting, data exploration, and ad hoc analytics. Reports and dashboards can be published to the app.powerbi.com cloud service and shared with a broader audience within your organization for downstream consumption and collaboration.

../../_images/powerbi_workflow.png

This guide describes the steps to configure Power BI for use with the Qubole Data Service in the following topics: