How to use Azure Open AI to Enhance Your Data Analysis in Power BI

Data is the new currency of the modern world, and to succeed in this competitive field, you need to master data concepts and tools.
This blog will show you how artificial intelligence (AI), especially Azure Open AI, can transform your data decision-making process within Power BI. Azure Open AI is a cloud-based AI service that uses pre-trained models to generate new content. You can pay for the service with tokens and benefit from features like cloud security and identity management.
Azure Open AI has many applications, including content generation, sentiment analysis, machine translations, and automation. Whether you are a business analyst, a researcher, or a data enthusiast, Azure Open AI can help you achieve your data goals. Join us as we dive into these fantastic possibilities in the next section.

 

1. Architecture 

 

 

2. Azure Open AI creation

 

Creating Azure OpenAI involves a series of steps that build on the principles of Cloud Native architecture. Here's a simplified guide on how to create Azure OpenAI:

1. Set up Azure Cloud: Start creating an Azure account if you haven't already. Sign in to the Azure portal using your Azure account credentials.
2. Create a Resource: To deploy an Azure service, you must create a resource. A resource represents a service or component in Azure, such as a virtual machine, a storage account, or a database. Click on the "Create a resource" button in the Azure portal.
3. Select Azure OpenAI Service: You'll see a list of available Azure services in the resource creation wizard. Choose Azure OpenAI Service by browsing the categories or searching the search bar.
4. Configure the Resource: Once you've selected Azure OpenAI Service, you'll be taken to a configuration page where you can specify the settings for the resource. Fill in the required information, such as resource name, region, pricing tier, and other relevant settings.
5. Review and Create: After configuring the resource, review the settings to ensure they are correct. Once satisfied, click the "Review + Create" button.
6. Validation and Deployment: Azure will validate the configuration settings and check for potential issues. If everything is in order, click the "Create" button to initiate the deployment process.
7. Monitor the Deployment: Azure will start provisioning the resources based on your configuration. You can monitor the deployment progress in the Azure portal. The deployment may take a few minutes to complete, depending on the service.
8. Access and Manage the Deployed Service: Once the deployment is finished, you can access and manage the deployed service through the Azure portal. You can view its properties, make changes to its configuration, monitor its performance, and perform other administrative tasks as needed.

3. Azure OpenAI Service Building Blocks

 

The Azure OpenAI Service Building Blocks are the core components and tools for creating and managing generative AI solutions. They include:

• Visual Interfaces: Azure OpenAI Studio and Playground offer user-friendly interfaces for testing, customizing, and deploying AI models without coding.
• Development Interfaces: APIs and SDKs enable the integration of Azure OpenAI's advanced capabilities into any application.
• Management Features: Deployments, Content Filters, Models, Data Files, and Quotas, which provide users with control over various aspects of their AI implementations.

In this blog, we will focus on using the Visual Interfaces of the Azure OpenAI Service, namely Azure OpenAI Studio and Playground, which offer user-friendly interfaces for testing, customizing, and deploying AI models without coding.

 

3.1. Azure OpenAI Studio and Playground

 

Azure OpenAI Playgrounds are applications within the service that let users interact and customize various AI models, including chat-based models like ChatGPT, non-chat language models, and image generation models. These interfaces simplify generative AI models, especially for non-technical users.

 

 

You can access each Playground (and their related management features) from the left panel of the Studio or visit them directly by following URLs linked in the following sections.

 

3.2. Chat Playground

 

The Chat Playground is a feature within the Azure OpenAI Studio. It provides an interactive interface for users to test and configure the behavior of the chat-based AI models.

The main components of the Chat Playground include:

1. Assistant Setup: This area allows users to configure the chatbot's behavior. Users can choose from templates or create their custom system messages.
2. Chat Session: The main interaction point between the user and the chatbot. Users can type their queries here, and the chatbot responds accordingly.
3. Deploy to: This option allows users to deploy their chatbot to a specific platform or environment. Azure OpenAI Studio enables direct deployments of both Azure Web Apps
   and Power Virtual Agents.
4. Configuration: This area provides options to access deployment and session settings. Users can also clear the chat history and manage parameters related to the chatbot's deployment.
   
   This Playground is handy for developing and refining chatbot applications, as it allows users to test and adjust the chatbot's performance and responses iteratively.

 

3.3. Deployments

Deployments provide endpoints to the Azure OpenAI base models, or your fine-tuned models, configured with settings to meet your needs, including the content moderation model, version handling, and deployment size. You can view your deployments from this page, edit them, and create new ones.

 

4. Retrieve key and endpoint.

 

You need an endpoint and a key to make a call successfully against Azure OpenAI.

 

 

 5. Python script

We can copy the Python code from the Playground to use our model API in our Python code (JSON and CURL are also available). This code will import the required library and export our parameter settings.
We will also create a Power BI report and use Azure Open AI to generate insights from the data. Before we begin, we need to complete some steps:

1. Install Python and enable Python in Power BI:
1. First, install Python if you have not done so already. Please visit the official website (https://www.python.org/downloads/) to download it. We recommend versions 3.9 and 3.10.
2. Once Python has been installed, enable Python scripting in Power BI. To do so, open Power BI Desktop. Then, click File > Options and go to Settings > Options > Python scripting. Select the checkbox and click OK.
3. Next, set the Python path in Power BI. Go to File > Options and Settings > Options > Python scripting. Here, click Detect. This selects the Python installation path automatically. You can do this manually by clicking the ellipsis (…) and selecting the Python executable file.
4. Restart Power BI Desktop for the changes you made to take effect.
5. Install the SDK with a package manager such as pip:

                     Pip install openai

2. Now, we need to create a dataset to analyze. Follow these steps:

1. Import your data and choose Transform Data

 

2. You can check the column quality to see if there is something wrong with data quality or empty cells, which can lead to false insight

 

            3. Add Run Python script to the Applied Steps section.

     4. Now, we can run the code in the next section directly into Power BI.

1. To start, import the necessary Python libraries

                # Import Libraries
                import os
                import openai
            

                # Set OpenAI API for Azure
                openai.api_type = "azure"
                openai.api_base = "https://xxxxxxxxxx.openai.azure.com/"
                openai.api_version = "2023-07-01-preview"
                openai.api_key = "xxxxxxxxxxxxxxxxxxxxxx"
            

                # Loop through each row in Renamed Columns & concatenate the data into a single string. Pass resulting string to the API
                for index, row in Renamed_Columns.iterrows():
                    messages = [
                        {
                            "role": "system",
                            "content": "For each company, I will give you the followwing information: Company, Price to Earnings, Price to Book, Return to Equity%, Debt to Equity, Current Ratio, Gross Margin%. Then, I will analyze the ratios and write a brief summary using the company name "
                        },
                        {
                            "role": "user",
                            "content": ''.join([str(col) for col in row])
                        }
                    ]
                    # Make a chat completion Request for the API
                    chat = openai.ChatCompletion.create(
                        engine = "GPT-35-turbo",
                        messages = messages,
                        temperature = 0.7,
                        max_tokens = 800,
                        top_p = 0.95,
                        frequency_penalty = 0,
                        presence_penalty = 0
                    )
                    # Process the response from API
                    reply = chat.choices[0].message.content
                    # Write the response Back to the Report
                    Renamed_Columns.at[index, "reslt"] = reply
            

 

 

This script runs a Python code that iterates over the Power BI table rows and builds a prompt for ChatGPT using the report data. The prompt is sent to ChatGPT, and the API response is returned to the Power BI DataFrame and table for each row (company).

2. Expanding the table means turning a column with tables inside it into multiple columns and rows. This can help you flatten your data and make it easier to analyze.

 

 

6. Build the report with Power BI

In the Power BI desktop, you can use the slicer visual to select the company and the table visual to view the generative insight of the data according to the field you choose.

 

 

 

Congratulations on completing this tutorial blog on How to use Azure Open AI to Enhance Your Data Analysis in Power BI; you have learned how to use Azure Open AI to enrich your data analysis in Power BI. You have discovered how Azure Open AI can create new content based on the dataset using a Python script. Azure Open AI is a versatile tool that can help you leverage your data and make smarter decisions. Whether you are a business analyst, a researcher, or a data enthusiast, Azure Open AI can help you gain data skills and insights. We hope you found this blog helpful and informative.

 

7. Resources 

 

• Azure OpenAI Service fine-tuning gpt-3.5-turbo - Azure OpenAI | Microsoft Learn
• Run Python scripts in Power BI Desktop - Power BI | Microsoft Learn
• Visualization types in Power BI - Power BI | Microsoft Learn