How to Connect Stored Procedure in Power BI

Using a stored procedure to feed data into a Power BI report is an excellent way to speed up refresh times, enhance security, and keep your data logic centralized. It allows your database to handle the heavy lifting of data processing before the data reaches Power BI. This article will guide you through connecting to a stored procedure, managing parameters, and following best practices.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Use a Stored Procedure with Power BI?

While you can connect Power BI directly to tables and views, using a stored procedure offers several advantages, especially for complex reports.

• Better Performance: Stored procedures handle filtering and calculations directly on the database server, sending only the final, necessary data to Power BI. This can significantly reduce report refresh times.
• Enhanced Security: By granting EXECUTE privileges on a stored procedure, you can limit user access to the data, providing fine-grained control over what data can be viewed.
• Simplified Maintenance: A stored procedure centralizes data logic. Update it once, and every report using it will automatically reflect the changes.
• Manages Complexity: Encapsulating complex SQL queries in a stored procedure abstracts the complexity for Power BI developers.

Prerequisites: What You’ll Need

Before you begin, ensure you have the following:

• Database Access: Access to a database like Microsoft SQL Server or MySQL. While specific screens may vary, the principles in this guide apply broadly. We focus on SQL Server for examples.
• A Stored Procedure: Ensure a stored procedure is created and saved in your database.
• Power BI Desktop: Install the latest version of Power BI Desktop.
• Crucial Credentials: You’ll need the server name, database name, and user credentials with permission to execute the specific stored procedure.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Connect Power BI to a Stored Procedure: Step-by-Step

Follow these steps to connect to a stored procedure using Power BI:

Step 1: Start the Connection from Power BI

In Power BI Desktop, select the Home ribbon tab, click Get Data, and select SQL Server. If needed, click "More..." to find SQL Server.

Step 2: Enter Your Server and Database Information

Provide your connection details:

1. Enter your database server's name in the Server field.
2. Enter your database's name in the Database field.
3. Select Import for Data Connectivity mode.

Step 3: Use the Advanced Options to Execute the Procedure

In Advanced options, enter the T-SQL command in the SQL statement box:

EXEC YourSchemaName.YourStoredProcedureName

For example:

EXEC dbo.sp_GetMonthlySales

Click OK after entering the command.

Note: Leaving the "SQL statement" box blank will display a list of tables and views, which is not desired here.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 4: Authenticate and Load Your Data

Enter your credentials when prompted by Power BI:

• Windows: Use if logging in with Windows credentials.
• Database: Enter a specific SQL Server username and password.
• Microsoft account: Use for authentication with Azure Active Directory.

After authenticating, preview and load the stored procedure's data into your model or use Transform Data for additional adjustments in the Power Query Editor.

Working with Stored Procedures That Use Parameters

For dynamic stored procedures accepting parameters, use Power Query's M language to build the call.

Step 1: Create Parameters in Power BI

In the Power Query Editor, create parameters holding the values to pass to your procedure:

• Name: StartDate
• Name: EndDate

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Modify Your Source Connection's M Code

Modify the M code for your query to construct the EXEC statement with parameters:

let Source = Sql.Database("your_server_name", "your_database_name", [Query= "EXEC dbo.sp_GetSalesByDateRange @StartDate = '" & Date.ToText(StartDate, "yyyy-MM-dd") & "', @EndDate = '" & Date.ToText(EndDate, "yyyy-MM-dd") & "'" ]) in Source

This query dynamically updates parameter values and calls the stored procedure. Click Done to execute the query with parameters.

Final Thoughts

Learning to connect Power BI to a stored procedure enhances report performance and security. Using your database server's capabilities and centralized logic streamlines reporting from back-end to front-end.

Mastering Power Query's M language and SQL requires effort, but tools like Graphed simplify the process. Connect your data sources effortlessly, create real-time reports, and leverage plain English to generate insights swiftly, empowering your whole team.