Configuring OpenAI and Azure OpenAI in Power BI

Dive deeper into the world of AI innovation and stay ahead of the AI curve! Subscribe to our AI_Distilled newsletter for the latest insights. Don't miss out – sign up today!

This article is an excerpt from the book, Power BI Machine Learning and OpenAI, by Greg Beaumont. Master core data architecture design concepts and Azure Data & AI services to gain a cloud data and AI architect’s perspective to developing end-to-end solutions 

Introduction

In this article, we delve into the exciting world of Power BI integration with OpenAI and Azure OpenAI. Data-driven decision-making is at the core of modern business, and harnessing the capabilities of AI models for generating text adds an invaluable dimension to your insights. Whether you're new to OpenAI or exploring the power of Azure OpenAI, we'll guide you through the technical requirements, API key setup, resource management, and dataflow optimization to seamlessly infuse AI-generated content into your Power BI projects. Let's embark on a journey to supercharge your data analytics capabilities and stay ahead in the ever-evolving world of data science.

Technical requirements

For this article, you’ll need the following:

• An account with the original open source OpenAI: https://openai.com/. • Optional – Azure OpenAI as part of your Azure subscription: https://azure.microsoft. com/en-us/products/cognitive-services/openai-service. The book is written so this is optional since it is not available to everyone at the time of publication.
• FAA Wildlife Strike data files from either the FAA website or the Packt GitHub site.

• A Power BI Pro license.

• One of the following Power BI licensing options for access to Power BI dataflows:

• Power BI Premium
• Power BI Premium Per User

Configuring OpenAI and Azure OpenAI for use in your Power BI solution

Prior to proceeding with the configuration of OpenAI and Azure OpenAI, it is important to note that OpenAI is still a nascent technology at the time of writing this book. In the future, the integration of OpenAI with Power BI may become less technical, as advancements in the technology continue to be made. However, the use cases that will be demonstrated in this chapter will remain applicable.

As such, the instructions provided in this chapter will showcase how this integration can be used to enhance your data analytics capabilities in the context of Power BI.

Configuring OpenAI

You can create an account in OpenAI (if you do not have one already) from this link: https:// chat.openai.com/auth/login. At the time of writing, new accounts are granted trial credits to begin using OpenAI. If you run out of trial credits, or if the trial is no longer offered after this book has been written, you may need to pay for the use of OpenAI. Pricing details can be found at this link: https://openai.com/pricing.

Once you have an OpenAI account, you will need to create an API key that will be used to authenticate your API calls. An API key can be easily created at this link: https://platform.openai.com/ account/api-keys. Clicking on Create new secret key will allow you to create a new key for API calls that you make later in this chapter. This book will use abc123xyz as an example key for the sample code. Be sure to use the actual Key from OpenAI, and not the Key Name.

Once you have an account and an API key, you are ready to go with OpenAI for this book!

Configuring Microsoft Azure OpenAI

OpenAI is also available as a service in Microsoft Azure. By using the Microsoft Azure OpenAI Service, users can leverage large-scale AI models with the benefits of Azure, such as role-based access security, private networks, and comprehensive security tools that integrate with other Microsoft tools in Azure. Billing and governance can be centralized for large organizations to help ensure the responsible use of AI.

For the purposes of this book, Azure OpenAI is optional as an alternative to the original OpenAI. Azure OpenAI may not be available to everyone since it is a new technology with high demand. All of the content for the workshop can be done with either OpenAI or Azure OpenAI.

Instructions for setting up Azure OpenAI can be found at this link: https://learn.microsoft. com/en-us/azure/cognitive-services/openai/how-to/create-resource/.

Once you’ve created a resource, you can also deploy a model per the instructions at that link. As noted in Chapter 12, you will be using the text-davinci-003 model for the workshop associated with this chapter. OpenAI is evolving rapidly, and you may be able to choose different models at the time you are reading this book. Take note of the following values when walking through these steps; they will be needed later in this chapter:

• Resource name: Note the name of your Azure OpenAI resource in your subscription. This book will use PBI_OpenAI_project for the examples in this chapter.
• Deployment name: This is the name of the resource for the text-davinci-003 model deployment. This book will use davinci-PBIML for names of deployments in examples of code.

Next, you’ll need to create a key for your Azure OpenAI API calls. From your Azure OpenAI resource, named PBI_OpenAI_project for this book, go to Resource management | Keys and endpoint, and your keys will be on that page. This book will use abc123xyz as an example key for the sample code.

Once you have either OpenAI or Azure OpenAI set up and ready to go, you can add some new generative text capabilities to your project using FAA Wildlife Strike data!

Preparing a Power BI dataflow for OpenAI and Azure OpenAI

In Chapter 12, you decided to use OpenAI for two use cases with your FAA Wildlife Strike database project:

• Generating descriptions of airplane models and the operator of the aircraft, for each incident
• Summarizing the free text remarks provided in the report for each incident

Since OpenAI is still new at the time of writing this book, Power BI does not yet have connectors built into the product. But you can still call OpenAI and Azure OpenAI APIs from both Power Query and Power BI dataflows using custom M scripts. Let’s get started!

First, you will create a new dataflow for use with OpenAI and Cognitive Services in Power BI:

1. From your Power BI workspace, on the ribbon, select New | Dataflow.

2. Select Define new tables | Link tables from other dataflows.

3. Sign in and click Next.

4. Expand your workspace.

5. Expand the Strike Reports dataflow and check Strike Reports Curated New.

6. Click Transform Data.

7. Create a group named Sources and move Strike Reports Curated New into that group.

8. Right-click Strike Reports Curated New and unselect Enable load.

Next, you will create a version of the query that will be used with OpenAI and Cognitive Services:

1. Right-click on Strike Reports Curated New and select Reference.

2. Rename the new query Strike Reports Curated New OpenAI.

3. Create a group named OpenAI and move Strike Reports Curated New OpenAI into the group.

In Chapter 12, you decided to use the FAA Wildlife Strike Operator, Aircraft, Species, and Remarks database columns as part of your OpenAI prompts. Filtering out blank and unknown values from Strike Reports Curated New OpenAI will help produce better results for your testing. Note that you may need to select Load more... if the values all come up empty or UNKNOWN:

1. For the Operator column, filter out the UNKNOWN, UNKNOWN COMMERCIAL, BUSINESS, and PRIVATELY OWNED values.

2. For the Aircraft column, filter out UNKNOWN.

3. For the Species column, filter out Unknown bird, Unknown bird – large, Unknown bird – medium, Unknown bird – small, and Unknown bird or bat.

For the Remarks column, filter out (blank).

Finally – this step is optional – you can filter the number of rows for testing purposes. Both OpenAI and Azure OpenAI can run up a bill, so limiting the number of calls for this workshop makes sense. For the example in this book, the Strike Reports Curated New OpenAI table will be filtered to events happening in or after December 2022, which can be filtered using the Incident Date column.

Now you are ready to add OpenAI and Cognitive Services content to your data!

Conclusion

In conclusion, configuring OpenAI and Azure OpenAI for integration with Power BI offers valuable enhancements to your data analytics capabilities. While OpenAI is still an evolving technology, the instructions provided in this article remain relevant and applicable. Whether you choose OpenAI or Azure OpenAI, both options empower you to leverage AI models effectively within Power BI.

Setting up these services involves creating API keys, resources, and deployments, as outlined in the article. Additionally, preparing your Power BI dataflow for OpenAI and Azure OpenAI is a crucial step. You can filter and optimize your data to improve the quality of AI-generated content.

As AI continues to advance, the potential for enhancing data analytics with OpenAI grows, and these configurations provide a strong foundation for leveraging generative text capabilities in your projects.

Author Bio

Greg Beaumont is a Data Architect at Microsoft; Greg is an expert in solving complex problems and creating value for customers. With a focus on the healthcare industry, Greg works closely with customers to plan enterprise analytics strategies, evaluate new tools and products, conduct training sessions and hackathons, and architect solutions that improve the quality of care and reduce costs. With years of experience in data architecture and a passion for innovation, Greg has a unique ability to identify and solve complex challenges. He is a trusted advisor to his customers and is always seeking new ways to drive progress and help organizations thrive. For more than 15 years, Greg has worked with healthcare customers who strive to improve patient outcomes and find opportunities for efficiencies. He is a veteran of the Microsoft data speaker network and has worked with hundreds of customers on their data management and analytics strategies.