How to have aggregates results and pass ODATA parameters from Azure OpenAI service to Azure Cognitive Service

Padmavathii Vijay 5 Reputation points
2023-07-31T14:29:00.5033333+00:00

I've set of JSON files that contain tabular style data.

These JSON files are indexed by Azure Cognitive Search service.

Azure Cognitive Search is used as a source in Azure Open AI Chat Playground (through Web Studio - deployed on Turbo GPT Model).

Now, my expectation is to use Open AI to get meaningful aggregations and long results from Cognitive Search. How can this be achieved?

Few sample queries:

  1. Sow me average bookings per Salesperson.
  2. How many opportunities were LOST due to insufficient funding in the current quarter / year?
  3. Show me top 10 records where Salesperson is John Doe.

One more observation - although Cognitive Search has more records, Open AI never returns the same records as of Cognitive Search. I was able to find this by enabling Diagnostic Setting and seeing incoming requests on Cognitive Search.

Also - how to pass ODATA common parameters such as $count to Cognitive Search from Open AI?

Please refer to incoming request that I saw in Azure Cognitive Search Service from Azure Open AI. It defaults to top 5 automatically, but doesn't show even 5 records in Azure Open AI Chat Playground

User's image

Azure AI Search
Azure AI Search
An Azure search service with built-in artificial intelligence capabilities that enrich information to help identify and explore relevant content at scale.
1,185 questions
Azure OpenAI Service
Azure OpenAI Service
An Azure service that provides access to OpenAI’s GPT-3 models with enterprise capabilities.
3,658 questions
0 comments No comments
{count} vote

1 answer

Sort by: Most helpful
  1. brtrach-MSFT 17,391 Reputation points Microsoft Employee
    2023-07-31T23:04:18.9066667+00:00

    @Padmavathii Vijay Hello! It seems like you have a few questions about using Azure OpenAI with Azure Cognitive Search. Let me try to help you out.

    To get meaningful aggregations and long results from Cognitive Search using Open AI, you can use the REST API of Cognitive Search to query the data and then use the Open AI API to analyze the results. You can use the aggregation feature of Cognitive Search to get the average bookings per salesperson and the number of opportunities lost due to insufficient funding in the current quarter/year. For example, to get the average bookings per salesperson, you can use the following query:

    https://[service name].search.windows.net/indexes/[index name]/docs?search=*&$count=true&$select=Salesperson,Bookings&$groupby=Salesperson&$top=1000&$orderby=Bookings desc&api-version=2020-06-30
    
    

    This query will return the top 1000 salespeople by bookings, along with the total number of records. You can then use the Open AI API to analyze the results and get the average bookings per salesperson.

    Similarly, to get the number of opportunities lost due to insufficient funding in the current quarter/year, you can use the following query:

    https://[service name].search.windows.net/indexes/[index name]/docs?search=InsufficientFunding&$count=true&$filter=LostOpportunity eq true and (Quarter eq 'Q1' or Year eq '2022')&api-version=2020-06-30
    
    
    

    This query will return the total number of opportunities lost due to insufficient funding in the current quarter/year.

    Regarding your observation that Open AI never returns the same records as Cognitive Search, it's possible that the queries being used are different. Can you please provide more details on the queries being used in Open AI and Cognitive Search?

    To pass ODATA common parameters such as $count to Cognitive Search from Open AI, you can include them in the query string of the REST API call. For example, to get the top 10 records where Salesperson is John Doe, you can use the following query:

    https://[service name].search.windows.net/indexes/[index name]/docs?search=Salesperson:John Doe&$count=true&$top=10&api-version=2020-06-30
    

    This query will return the top 10 records where Salesperson is John Doe, along with the total number of records. You can then use the Open AI API to analyze the results and get the information you need.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.