How to build an Azure Bot Service chatbot using a Power BI dataset as a knowledge base?

[LB_Team]

Hi everyone,

I’m working on creating a chatbot using Azure Bot Service. I’ve gone through a lot of tutorials, but most of them only explain how to build a chatbot using static knowledge bases like PDFs or websites.

My requirement is different:

• I want my chatbot to answer questions based on the Power BI dataset I’ve created.

• This dataset includes tables, measures, and relationships that I use in my Power BI dashboard.

• The goal is for the bot to interpret user questions (like “What are this month’s sales?”) and fetch data from the Power BI dataset dynamically.

I’m looking for guidance on:

1. Where do I start?

2. What’s the best way to make the Power BI dataset accessible to the bot - guide me with steps

3. Are there any sample implementations or documentation that specifically focus on using Power BI datasets as the knowledge base for a chatbot?

Any pointers, resources, or step-by-step guidance would be really helpful!

Thanks in advance.

[v-tsaipranay]

Hi @LB_Team ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[v-tsaipranay]

Hi @LB_Team  ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

To start, set up the Azure Bot Service to host your chatbot, use Azure OpenAI or LUIS to understand user questions, and connect to your Power BI dataset via the XMLA endpoint or REST APIs. Ensure secure access by registering an Azure AD app with appropriate permissions.

To make your Power BI dataset accessible to a chatbot, follow these general steps:

Step 1: Register an Azure AD Application
Begin by registering an application in Azure Active Directory. This application will serve as a secure intermediary between your chatbot and Power BI, allowing authenticated data access. Be sure to assign the necessary permissions for the bot to read Power BI data.

Step 2: Grant Access to the Power BI Workspace
After publishing your dataset to the Power BI Service, place it in a Premium workspace (or Premium per user). Grant your Azure AD application access to this workspace to enable secure, real-time data queries by the bot.

Step 3: Enable XMLA Endpoint for Dataset Access
With Power BI Premium, you can use the XMLA endpoint to allow external tools, such as your bot’s backend, to connect and query datasets using DAX. Confirm that the XMLA endpoint is enabled and set to allow read access in the workspace settings.

Step 4: Build a Query Layer to Translate User Questions
Develop a layer that interprets natural language questions and converts them into queries compatible with your Power BI dataset. AI services like Azure OpenAI or LUIS can help identify user intent and generate appropriate DAX queries.

Step 5: Securely Authenticate Your Bot
Ensure your chatbot uses secure authentication methods, such as OAuth2 with Azure AD, to connect to Power BI. Store credentials securely and obtain tokens before making any data requests.

Step 6: Process and Format the Data Response
Once the bot retrieves data, present the results in a clear and user-friendly format. This may include summarizing values, formatting dates, or grouping information based on the query.

By following these steps, your chatbot can serve as an interactive interface for delivering insights from your live Power BI data.

Here are the documents; please refer to the following:

Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn

Bot Framework Composer documentation | Microsoft Learn

I hope this helps you, if you need any further assistance, feel free to reach out.

Thank you.

[lbendlin]

I know of a similar project where the chatbot gets primed with the data model via prompt engineering, then the chatbot is used to generate the DAX from the business queries and then the chatbot runs the DAX query against the semantic model.

No endorsement.