How to Connect Databricks to Power BI

Combining the heavy-duty data processing power of Databricks with the user-friendly visualization capabilities of Power BI creates a formidable analytics stack. This guide will walk you through the exact steps to connect these two platforms, helping you transform massive datasets into clear, interactive reports. We'll cover everything from getting your credentials to choosing the right connectivity mode for your needs.

Why Connect Databricks to Power BI?

Databricks excels at managing and processing enormous volumes of data using Apache Spark. It's the place where data scientists and engineers clean, transform, and model data at scale. Power BI, on the other hand, is built for business intelligence - making data accessible and understandable for business users through interactive dashboards and reports.

By connecting them, you get the best of both worlds:

• Scalability: Leverage Databricks' distributed computing to query petabytes of data without overwhelming Power BI.
• Centralized Data: Analyze your entire data lakehouse from a single source of truth in Databricks without having to move or duplicate data.
• Powerful Visuals: Turn complex Spark SQL query results into intuitive charts, graphs, and dashboards that anyone on your team can understand and act upon.

Before You Begin: What You'll Need

To ensure a smooth connection process, make sure you have the following ready:

• A Databricks Workspace: An active Databricks account with a workspace configured on Azure.
• A Databricks Cluster or SQL Warehouse: You need a running compute resource to handle the queries from Power BI. A Databricks SQL warehouse is highly recommended for BI workloads as it is optimized for performance and efficiency.
• Power BI Desktop: The latest version installed on your machine. This is where you'll build the initial connection and your reports.
• Necessary Permissions: You must have "Can Attach To" or "Can Restart" permissions for your cluster or SQL warehouse in Databricks, as well as access permissions for the data tables you want to query.

Step-by-Step Guide to Connecting Databricks and Power BI

Once your prerequisites are in order, connecting the two platforms is a straightforward process. Let’s walk through it step-by-step.

Step 1: Get Your Databricks Connection Details

First, you need to collect two key pieces of information from your Databricks workspace: the Server Hostname and the HTTP Path. You can find these in the connection details of your SQL warehouse or interactive cluster.

1. Log in to your Databricks workspace.
2. Navigate to the compute resource you want to connect to.
3. Go to the Connection details tab (for SQL Warehouses) or the JDBC/ODBC tab (for clusters).
4. You will see the Server Hostname and HTTP Path fields. Copy these values into a notepad or keep the window open, as you'll need them in a moment.

Step 2: Start the Connection from Power BI

Now, open Power BI Desktop to begin the connection process using its native Databricks connector.

1. Launch Power BI Desktop.
2. On the Home ribbon, click Get Data.
3. In the "Get Data" dialog box, you can either search for "Databricks" or navigate to Azure > Azure Databricks.
4. Select Azure Databricks and click Connect.

Step 3: Enter Your Databricks Connection Details

A new window will appear asking for the connection details you just copied from Databricks.

1. Paste the Server Hostname into the server hostname field.
2. Paste the HTTP Path into the HTTP path field.
3. You can optionally specify a catalog and/or database under the Data Connectivity Mode options, but it's often easier to navigate to these later.

Step 4: Choose a Data Connectivity Mode

This is a critical step that determines how Power BI interacts with your Databricks data. You have two main options: Import and DirectQuery.

Import Mode

What it does: This mode copies, or imports, a snapshot of your data from Databricks and stores it within the Power BI file (.pbix). When you interact with a report, Power BI queries this local copy.

Pros:

• Excellent performance, since all data is cached locally.
• You also get full access to Power BI’s Power Query (M) transformations.

Cons:

• The data is not real-time and must be refreshed manually or on a schedule.
• Limited by the memory of your machine and the 1 GB dataset size limit per file in Power BI Pro (larger with Premium).

Best for: Smaller datasets (under 1 GB) or when ultra-fast report performance is more important than live data.

DirectQuery Mode

What it does: This mode sends queries directly to your Databricks cluster or SQL warehouse every time a user interacts with a report (e.g., clicks a filter or changes a chart). No data is stored inside the Power BI file itself.

Pros:

• The data is always current, reflecting the latest information in Databricks.
• Allows working with massive datasets that couldn't be imported into Power BI.

Cons:

• Report performance depends on the speed of your Databricks compute resource and network latency.
• Power Query transformation capabilities are more limited compared to Import mode.

Best for: Very large datasets, real-time reporting needs, and situations where data security policies require that data remain within its source.

Choose the mode that best fits your project's requirements and then click OK.

Step 5: Authenticate Your Connection

Power BI now needs to authenticate with Databricks to gain permission to access the data. The most common and secure method for individual users is a Personal Access Token (PAT).

Generating a Personal Access Token in Databricks:

1. In your Databricks workspace, click your username in the top-right corner and select User Settings.
2. Go to the Developer tab.
3. Next to Access Tokens, click Manage.
4. Click Generate new token.
5. Add a descriptive comment (e.g., "Power BI Connection") and set an expiration lifetime for the token. For security, don't set it to 'infinite'. 90 days is a good starting point.
6. Click Generate.
7. Important: Copy the generated token immediately and store it securely. You will not be able to see it again after you close the dialog.

Using the Token in Power BI:

1. Back in Power BI, you'll be prompted for credentials.
2. For the user name, type the word token.
3. In the password field, paste your entire Personal Access Token.
4. Click Connect.

Step 6: Navigate and Load Your Data

After a successful connection and authentication, Power BI's Navigator window will open. This is where you select the data you want to use in your report.

• Your Databricks catalogs, databases (schemas), and tables will be displayed in a familiar tree structure.
• Expand the folders to find the table or view you want to analyze.
• Check the box next to one or more tables to see a preview of the data on the right side.
• Once you've selected your desired data, you have two options:

After clicking Load or Transform Data (and then Close & Apply), your data will be available in the Power BI 'Fields' pane, ready for you to start building visualizations.

Best Practices for Optimal Performance

Connecting is just the first step. To get the best experience, follow these tips:

• Use Databricks SQL Warehouses: For any BI-related workload, always prefer a Databricks SQL warehouse over an all-purpose cluster. They are specifically optimized for fetching SQL query results quickly, providing much better performance for Power BI.
• Prepare Your Data in Databricks: Don't make Power BI do the heavy lifting. Perform as much data cleaning, aggregation, and transformation as possible within Databricks using notebooks or Delta Live Tables. Create aggregated views for Power BI to query, which will be much faster than querying raw, multi-billion-row tables.
• Be Specific with DirectQuery: When using DirectQuery, only select the columns you need. Avoid using SELECT *. The fewer columns and rows Power BI asks for, the faster Databricks can respond. Use filters in Power BI to reduce the amount of data being requested with each interaction.
• Secure Your Credentials: Treat your Personal Access Tokens like passwords. Use a password manager to store them, and regularly rotate them by setting expiration dates. Delete any tokens that are no longer in use.

Final Thoughts

This tutorial shows that bridging the gap between Databricks and Power BI is a smooth process, enabling you to build rich, interactive reports on top of your largest datasets. By following these steps and best practices, you can effectively unite big data processing with world-class business intelligence.

While this connection is perfect for deep lakehouse analytics, we know much of your key business data - from marketing campaigns and sales activities - lives scattered across many different SaaS platforms. Staging all of it in a system like Databricks is often overkill for answering daily performance questions. That's why we built Graphed. We connect directly to your Google Analytics, Shopify, Salesforce, HubSpot, and advertising accounts, allowing you to ask questions in plain English and instantly get real-time dashboards without any complex setup or data wrangling.