Building a Power BI Chatbot with OpenAI: Enhancing Business Intelligence through Conversational AI

Introduction

Power BI is a widely-used data visualization and business intelligence tool that enables users to analyze and gain insights from their data. By integrating a chatbot powered by OpenAI’s conversational AI capabilities, users can interact with Power BI more intuitively, asking questions, and receiving insights through a conversational interface. In this blog post, we’ll guide you through the process of creating a Power BI chatbot using OpenAI’s API, from setting up the necessary tools to deploying the chatbot for use.

Note – Please be aware that this solution involves interacting with OpenAI’s API. I encourage users to familiarize themselves with OpenAI’s data usage policy (https://platform.openai.com/docs/data-usage-policy) and take necessary precautions to ensure the privacy and security of their data.

1. Prerequisites and Setup

To start building your Power BI chatbot, you’ll need the following:

• Access to the Power BI API: To interact with Power BI data, you’ll need to register your application with Azure Active Directory (AAD) and obtain the necessary API keys and permissions. Follow the official documentation to get started: https://docs.microsoft.com/en-us/power-bi/developer/embedded/register-app
• OpenAI API key: Sign up for an OpenAI API key if you haven’t already. You’ll use this to access OpenAI’s conversational AI capabilities. Visit https://beta.openai.com/signup/ to sign up.
• A development environment: You can use any programming language and environment that supports HTTP requests. For this tutorial, we’ll use Python and the requests library.
• You can also refer to my earlier blogposts on OpenAI integration
  • https://christopherfinlan.com/2023/04/13/integrating-power-bi-with-openai-a-comprehensive-guide/
  • https://christopherfinlan.com/2023/04/14/enhancing-power-bi-data-analysis-with-openai-api-a-follow-up-guide/

2. Accessing Power BI Data

With your Power BI API access set up, you can now interact with your data. To simplify the process, create a Python function to query the Power BI API:

import requests

def query_power_bi_data(api_url, headers, query):
    response = requests.post(api_url, headers=headers, json=query)
    response.raise_for_status()
    return response.json()

Replace api_url with the URL of your Power BI report or dataset, and headers with the authentication headers containing your API key.

3. Building the Chatbot with OpenAI

To create a chatbot using OpenAI’s conversational AI, you’ll use the OpenAI API. First, install the OpenAI Python library:

pip install openai

Next, create a Python function to send user input to the OpenAI API:

import openai

openai.api_key = "your_openai_api_key"

def chat_with_openai(prompt, model="text-davinci-002"):
    response = openai.Completion.create(
        engine=model,
        prompt=prompt,
        max_tokens=100,
        n=1,
        stop=None,
        temperature=0.5,
    )

    message = response.choices[0].text.strip()
    return message

Replace your_openai_api_key with your OpenAI API key.

4. Processing User Input and Generating Responses

Now that you have functions for interacting with both Power BI and OpenAI, you can create a function to process user input, generate responses, and return data from Power BI:

def process_user_input(user_input):
    openai_prompt = f"Create a Power BI query for the following user question: {user_input}"
    power_bi_query = chat_with_openai(openai_prompt)

    power_bi_data = query_power_bi_data(api_url, headers, power_bi_query)

    openai_prompt = f"Generate a response to the user's question based on the following Power BI data: {power_bi_data}"
    chatbot_response = chat_with_openai(openai_prompt)

    return chatbot_response

5. Deploying the Chatbot

With the core functionality in place, you can now deploy your chatbot using a platform like Flask or Django for Python, or

using Node.js with Express, depending on your preferred environment. We’ll use Flask for this example.

First, install Flask:

pip install Flask

Create a simple Flask application to handle user input and return chatbot responses:

from flask import Flask, request, jsonify

app = Flask(__name__)

@app.route('/chat', methods=['POST'])
def chat():
    user_input = request.json.get('user_input', '')
    response = process_user_input(user_input)
    return jsonify({'response': response})

if __name__ == '__main__':
    app.run(debug=True)

Now, run your Flask application:

python app.py

Your Power BI chatbot is now accessible through a RESTful API at the /chat endpoint. You can send POST requests containing user input as JSON, and the chatbot will respond with insights based on your Power BI data.

6. Integrating the Chatbot with a User Interface

To create a more interactive experience for your users, you can integrate the chatbot with a user interface, such as a web application or a messaging platform like Microsoft Teams or Slack.

For a web application, you can use HTML, CSS, and JavaScript to create a simple chat interface that sends user input to your Flask chatbot endpoint and displays the chatbot’s responses.

Web Application Chat Interface

To create a chat interface for a web application, you’ll need to use HTML, CSS, and JavaScript. Here’s a simple example:

1. Create an HTML file (e.g., index.html) with the following structure:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Power BI Chatbot</title>
    <link rel="stylesheet" href="styles.css">
</head>
<body>
    <div class="chat-container">
        <div id="chat-output"></div>
        <div class="input-container">
            <input type="text" id="user-input" placeholder="Type your question...">
            <button id="send-button">Send</button>
        </div>
    </div>
    http://script.js
</body>
</html>

2. Create a CSS file (e.g., styles.css) to style the chat interface:

body {
    font-family: Arial, sans-serif;
    display: flex;
    justify-content: center;
    align-items: center;
    height: 100vh;
    margin: 0;
    padding: 0;
    background-color: #f5f5f5;
}

.chat-container {
    width: 500px;
    height: 600px;
    border: 1px solid #ccc;
    background-color: white;
    display: flex;
    flex-direction: column;
}

#chat-output {
    flex: 1;
    padding: 1rem;
    overflow-y: auto;
}

.input-container {
    display: flex;
    padding: 1rem;
    border-top: 1px solid #ccc;
}

#user-input {
    flex: 1;
    border: 1px solid #ccc;
    border-radius: 5px;
    padding: 0.5rem;
    outline: none;
}

#send-button {
    margin-left: 1rem;
    padding: 0.5rem 1rem;
    background-color: #007bff;
    color: white;
    border: none;
    border-radius: 5px;
    cursor: pointer;
}

3. Create a JavaScript file (e.g., script.js) to handle user input and communicate with your Flask chatbot API:

const chatOutput = document.getElementById('chat-output');
const userInput = document.getElementById('user-input');
const sendButton = document.getElementById('send-button');

sendButton.addEventListener('click', async () => {
    const userText = userInput.value.trim();

    if (userText.length === 0) {
        return;
    }

    chatOutput.innerHTML += `<p><strong>You:</strong> ${userText}</p>`;
    userInput.value = '';

    const response = await fetch('http://localhost:5000/chat', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json',
        },
        body: JSON.stringify({ user_input: userText }),
    });

    const responseData = await response.json();
    chatOutput.innerHTML += `<p><strong>Chatbot:</strong> ${responseData.response}</p>`;
    chatOutput.scrollTop = chatOutput.scrollHeight;
});

userInput.addEventListener('keyup', (event) => {
    if (event.key === 'Enter') {
        sendButton.click();
    }
});

There are several options for deploying your web application to, including Microsoft Azure or Heroku. Be sure to check the documentation for the vendor of your choice on how to deploy a web application to their platform.

Integrating the Chatbot with Microsoft Teams

To integrate the chatbot with Microsoft Teams, you can create a custom Microsoft Teams bot using the Microsoft Bot Framework. Here’s a high-level overview of the steps involved:

1. Register a new bot with Microsoft Bot Framework:
   • Go to the Azure Portal (https://portal.azure.com/) and sign in with your account.
   • Click “Create a resource” and search for “Bot Channels Registration.”
   • Complete the form, providing the necessary information such as bot name, subscription, and resource group. For the “Messaging endpoint,” enter a placeholder URL (e.g., https://your-bot.example.com/api/messages)—you’ll update this later.
2. Set up a development environment:
   • Install the Bot Framework Emulator (https://github.com/microsoft/BotFramework-Emulator/releases) to test your bot locally.
   • Install Node.js (https://nodejs.org/) and the Bot Framework SDK for Node.js by running npm install -g botbuilder.
3. Create a simple Microsoft Teams bot:
   • Create a new folder for your bot, navigate to it in your terminal, and run npm init to create a package.json file.
   • Install the necessary dependencies by running npm install botbuilder botbuilder-teams.
   • Create a new file (e.g., index.js) and add the following code to set up a basic bot:

const { BotFrameworkAdapter, MemoryStorage, ConversationState } = require('botbuilder');
const { TeamsActivityHandler } = require('botbuilder-teams');
const restify = require('restify');

const adapter = new BotFrameworkAdapter({
    appId: process.env.MicrosoftAppId,
    appPassword: process.env.MicrosoftAppPassword,
});

const storage = new MemoryStorage();
const conversationState = new ConversationState(storage);

class TeamsBot extends TeamsActivityHandler {
    constructor(conversationState) {
        super();
        this.conversationState = conversationState;
    }

    async onMessage(context) {
        // Process user input and communicate with your Flask chatbot API here.
    }
}

const bot = new TeamsBot(conversationState);

const server = restify.createServer();
server.listen(process.env.port || process.env.PORT || 3978, () => {
    console.log(`\n${server.name} listening to ${server.url}`);
    console.log('\nGet Bot Framework Emulator: https://aka.ms/botframework-emulator');
    console.log('\nTo talk to your bot, open the emulator select "Open Bot"');
});

server.post('/api/messages', (req, res) => {
    adapter.processActivity(req, res, async (context) => {
        await bot.run(context);
    });
});

4. Update the messaging endpoint in the Azure Portal:
   • Deploy your bot to a hosting provider of your choice (e.g., Azure, Heroku, etc.), and obtain the public URL.
   • Update the “Messaging endpoint” in your bot’s “Bot Channels Registration” in the Azure Portal to point to the /api/messages endpoint on your bot’s public URL.
5. Add the Microsoft Teams channel:
   • In your bot’s “Bot Channels Registration” in the Azure Portal, navigate to the “Channels” tab.
   • Click “Add a featured channel” and select “Microsoft Teams.”
   • Configure the settings as needed, and save the changes.

Your custom Microsoft Teams bot is now connected to your Flask chatbot API. Users can interact with the bot in Microsoft Teams, and the bot will communicate with your Flask API to provide insights based on your Power BI data.

Conclusion

By integrating OpenAI’s conversational AI capabilities with Power BI, you can create a powerful chatbot that allows users to explore their data and gain insights through a conversational interface. This blog post has demonstrated the steps necessary to build such a chatbot using Python, Flask, and OpenAI’s API. With your chatbot in place, you can enhance your organization’s business intelligence efforts and empower your users to interact with data more intuitively.

This blogpost was created with a LOT of help from ChatGPT Pro.