Hi, I've imported data from Azure SQL database and created an embedding on a combined text column CombinedText (concatenation of all textual columns) I used the AI skillset below to create embeddings on the CombinedText column #Microsoft.Skills.Text.AzureOpenAIEmbeddingSkill I've tested the Index with REST API queries to sort numerical fields like 'MaxUnits' and retrieve textual fields like 'Location'. The results are satisfactory and matched the SQL results. I them used vector search on the index in Azure AI studio, with a custom field mapping which includes all textual fields in the Index. But the answers do not retrieve the numerical fields with a response ' The retrieved documents do not provide specific information on the maximum units '. Alternatively, I tried including the numerical columns in the concatenation to get the CombinedText column and used it to create embeddings. While numercial data seems to be retrieved, the sorting is done based on ASCII and the wrong MaxUnits are used in the response. This is understandable as the Content data uses 'CombinedText' which is a string. Can you help out with numerical retrieval of data? Thanks a lot.

@Viswavaageesh Chandramouli I'm glad to see you were able to resolve your issue. Thanks for posting your solution so that others experiencing the same thing can easily reference this. Since the Microsoft Q&A community has a policy that the question author cannot accept their own answer, they can only accept answers by others , I'll repost your solution in case you'd like to Accept the answer. . Issue: You're using Azure AI Studio with Azure SQL DB. You would like to retrieve the numerical fields, but getting the response The retrieved documents do not provide specific information on the maximum units . You need help with numerical retrieval of data. . Resolution: Make the rewriteIntent node convert user's query to Odata string Use a python code to send an REST API request for the Odata query Concat numerical fields in the output to the CombinedText field Use it to in the generateReply LLM node to retrieve the answer. ** Please do not forget to " Accept the answer ” and “ up-vote ” so that this beneficial to other community members.

Azure AI Studio on SQL data base - Problem retrieving Numerical fields

Viswavaageesh Chandramouli 20

Hi,

I've imported data from Azure SQL database and created an embedding on a combined text column CombinedText (concatenation of all textual columns)
User's image

I used the AI skillset below to create embeddings on the CombinedText column

#Microsoft.Skills.Text.AzureOpenAIEmbeddingSkill

I've tested the Index with REST API queries to sort numerical fields like 'MaxUnits' and retrieve textual fields like 'Location'. The results are satisfactory and matched the SQL results.
User's image

I them used vector search on the index in Azure AI studio, with a custom field mapping which includes all textual fields in the Index. User's image

But the answers do not retrieve the numerical fields with a response 'The retrieved documents do not provide specific information on the maximum units'.

Alternatively, I tried including the numerical columns in the concatenation to get the CombinedText column and used it to create embeddings. While numercial data seems to be retrieved, the sorting is done based on ASCII and the wrong MaxUnits are used in the response. This is understandable as the Content data uses 'CombinedText' which is a string.

Can you help out with numerical retrieval of data?

Thanks a lot.

Sina Salam 10,176 Reputation points

2024-09-03T14:22:16.5566667+00:00
Hello Viswavaageesh Chandramouli,

Welcome to the Microsoft Q&A and thank you for lucid explanatory and the question you posted here.

Regarding your challenges to retrieves numerical fields in Azure AI Studio on SQL database.

Kindly follow the bellow best practices for best results and use the links for broader few on how to implement them:

Instead of concatenating numerical data into the CombinedText column, keep numerical fields separate. This allows you to handle them appropriately without converting them to strings https://learn.microsoft.com/en-us/azure/search/search-how-to-index-data

For your search method, combine vector search for textual data with a traditional filter or range query for numerical data https://learn.microsoft.com/en-us/azure/search/search-what-is-azure-search

To extract and process numerical fields, ensuring they are correctly interpreted and retrieved, you will consider also to create a custom skill in your AI skillset to handle numerical data separately https://learn.microsoft.com/en-us/azure/search/cognitive-search-custom-skill-interface

To make the search index to understand and retrieve data correctly, you will need to ensure that your field mapping in Azure AI Studio includes both textual and numerical fields https://learn.microsoft.com/en-us/azure/search/search-create-index

To ensure that the numerical fields are correctly interpreted and used in the final output you can apply post-processing to sort and filter numerical data as needed after retrieving the results https://www.ibm.com/docs/en/curam-social-program-management/7.0.8?topic=practices-best-data

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam
Viswavaageesh Chandramouli 20 Reputation points

2024-09-03T14:33:25.8933333+00:00

Hi Sina,

Thanks for your answer. A couple of the links you'd shared do not exist now.

The problem mainly stems from the fact that the Content field (also seen in Add custom field mapping section) only allows String to be used.

This is visible in the prompt flow where despite searching the index corretly, only the 'content' is used for answer generation.

The question now is, how do I add 'Max Units' to the content field?

Thanks,
Viswa
Sina Salam 10,176 Reputation points

2024-09-04T21:25:04.93+00:00
Hi,

Thank you for your feedback.

Sorry for the links that broken.

For Number 1: https://learn.microsoft.com/en-us/azure/search/search-create-service-portal and https://learn.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/search-options

For number 4: https://learn.microsoft.com/en-us/azure/search/search-how-to-create-search-index?tabs=portal

For number 5: The contents have been moved to github.io

Now about the current issue:

The question now is, how do I add 'Max Units' to the content field?

If I get you right, you mean is the issue of integrating numerical fields, like 'Max Units,' into the content field of your Azure AI Studio prompt flow.

Review this documentation for best solution: https://learn.microsoft.com/en-us/azure/search/cognitive-search-custom-skill-interface and https://learn.microsoft.com/en-us/azure/search/search-create-index

However, since the Content field in Azure AI Studio only accepts string values, and you want to incorporate the 'Max Units' (a numerical value) into the content used for answer generation, this is an example of what you can do:

While maintaining numerical fields separately, you can create a combined string field that concatenates relevant textual and numerical data. This combined string field can then be indexed and used in the Content field.

Create a custom skill that concatenates the 'Max Units' field with other textual content into a new field specifically designed for the Content field in your index.

If you have control over query logic, you can programmatically concatenate or format the numerical data with textual content when generating answers or presenting search results like this using JSON:

{ "content": "Product ABC with Max Units 50 available.", "maxUnits": 50 }

So, this example, content combined string that includes both textual and numerical information, which you can use for search and answer generation purposes.

Success.
navba-MSFT 23,975 Reputation points Microsoft Employee

2024-09-05T05:28:45.08+00:00
@Viswavaageesh Chandramouli Welcome to Microsoft Q&A Forum, Thank you for posting your query here!

.

Please note that embeddings primarily capture the semantic meaning of text, representing objects like words, documents, or entities as vectors in a multidimensional space. This allows for similarities and relationships between the objects to be represented based on their context or meaning, rather than focusing on their numerical values. The key idea is that embeddings place similar items closer together in this space based on their semantic content, not their raw numerical characteristics.

.

To address the issue of retrieving numerical fields in Azure AI Studio while performing vector search, you need to approach the problem differently because embeddings primarily capture semantic meaning from text, not numerical values.

.

Here are some suggestions:

Separate Numerical and Textual Data: Instead of concatenating numerical data into the CombinedText column, keep numerical fields separate. Use the CombinedText column for text embeddings and handle numerical fields directly in your queries.

Hybrid Search Approach: Use a hybrid search approach that combines vector search for textual data and traditional SQL queries for numerical data. This way, you can leverage the strengths of both methods. For example, use vector search to find relevant documents and then filter or sort these results based on numerical fields using SQL queries.

Custom Skill for Numerical Data: Create a custom skill in Azure Cognitive Search that processes numerical fields separately. This skill can extract and store numerical data in a way that allows for accurate retrieval and sorting.

Post-Processing: After retrieving documents using vector search, perform post-processing to sort or filter based on numerical fields. This can be done in your application logic after receiving the search results.

Azure AI Search: Utilize Azure AI Search capabilities to handle both textual and numerical data efficiently. Azure AI Search supports hybrid search scenarios where you can combine vector search with traditional search techniques.

Hope this helps.

Viswavaageesh Chandramouli 20

Appreciate your guidance. Can you please tell me how an Custom skill can help with Numerical sorting? Irrespective of the way data is stored in the index, doesn't the 'querySearchResource' node in prompt flow carry the responsibility of searching and sorting the index to retrieve the top matching values?

Also, can you please also review my recent changes in line with your comments that led to retrieve numerical values, but struggles with answers requiring sorting fields like MaxUnits.

Current Solution:

I have separate numerical and Text data fields in my Index. The CombinedText column in the SQL view only uses textual data as it is used to create vector embeddings in the index.

In the default prompt flow, I've modified the 'ChunkDocuments' node to concatenate the numerical fields to the content field which can be used to generate a reply.

Prompt flow Graph:
User's image

chunkDocument python code:

from promptflow import tool
from typing import List, Dict, Any
from flowUtils import transform_retrieval_response
from ragcore.chunkDocuments import chunk_documents_core
from ragcore.utils import query_type_map, data_source_map

# Query Types:
# - Keyword
# - Semantic
# - Vector
# - Hybrid (vector + keyword)
# - Hybrid + semantic

# Data Source Types:
# - Azure AI Search
# - Cosmos

@tool
def chunk_documents(results: List[List[Dict[str, Any]]], max_tokens: int, queries: List, top_k: int, data_source: str, query_type: str) -> List:
  # Check if Query Type is valid
  if query_type not in query_type_map:
    raise Exception(f"Invalid Query Type: {query_type}")
  
  # Check if Data Source Type is valid
  if data_source not in data_source_map:
    raise Exception(f"Invalid Data Source: {data_source}")
  

  #New additions to default Code
  #Min Units
  for result_list in results:
    for result in result_list:
      additional_fields = result.get('additional_fields', {})
      min_unit = additional_fields.get('MinUnits')
      if min_unit is not None:
        result['text'] += f", Minimum Units: {min_unit}"

 # max_units 
  for result_list in results:
    for result in result_list:
      additional_fields = result.get('additional_fields', {})
      max_units = additional_fields.get('MaxUnits')
      if max_units is not None:
        result['text'] += f", Maximum Units: {max_units}"

 # cost_per_unit = []
  for result_list in results:
    for result in result_list:
      additional_fields = result.get('additional_fields', {})
      cost_per_unit = additional_fields.get('CostPerUnit')
      if cost_per_unit is not None:
        result['text'] += f", Cost Per Unit: {cost_per_unit}"
  
  transformed_results = transform_retrieval_response(results, queries)
  
  return chunk_documents_core(transformed_results, max_tokens, top_k, data_source_map[data_source], query_type_map[query_type])

For a question like 'which location has the highest Maximum units?', the answer is wrong.
User's image

The correct answer as retrieved in the index through the REST API is:
User's image

Looking deeper into the 'trace' of the answer, the problem is with 'QuerySearchResource' node -

User's image

Why is the sorting not taking place despite the output query of the extractSearchIntent node being "Which location has the highest maximum units?" ?

Thanks

navba-MSFT 23,975 Reputation points Microsoft Employee

2024-09-05T12:52:04.5233333+00:00

@Viswavaageesh Chandramouli As discussed over the private chat, this issue requires a closer troubleshooting over a screen-sharing session. So we have enabled a one-time free support ticket for your subscription ff921XXX-XXXX-XXX-XXX-XXXcd6f502 for a quick and immediate assistance.

.

Steps to be followed:

Please create a service request to formally track this issue. This helps us track widespread impact in the event other customers are experiencing similar issues.

Details on how to raise a service request below:

•            Go to the Health Advisory section within the Azure Portal: https://aka.ms/healthadvisories

•            Select the Issue Name "You have been enabled for one-time Free Technical Support"

•            Details will populate below in the Summary Tab within the reading pane and you can click on the link "Create a Support Request" to the right of the message

After you create a support ticket the Service Request number should look like 202409XXXXXX.

You will receive a call from Microsoft Support professional after 48 business hours and they will assist you further on this issue. Once you have a resolution, please post the answer on this thread so that it will benefit the other community audience.

Hope this helps.

**

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you.
navba-MSFT 23,975 Reputation points Microsoft Employee

2024-09-06T05:26:19.79+00:00

@Viswavaageesh Chandramouli A quick follow-up to check if you had a chance to look at my above suggestion. Please let me know if the above suggestion helped to open a Support ticket.
Viswavaageesh Chandramouli 20 Reputation points

2024-09-17T10:33:35.32+00:00
Solution:

Make the rewriteIntent node convert user's query to Odata string

Use a python code to send an REST API request for the Odata query

Concat numerical fields in the output to the CombinedText field

Use it to in the generateReply LLM node to retrieve the answer

Accepted answer

navba-MSFT 23,975 Reputation points Microsoft Employee

2024-09-18T02:48:58.43+00:00
@Viswavaageesh Chandramouli I'm glad to see you were able to resolve your issue. Thanks for posting your solution so that others experiencing the same thing can easily reference this. Since the Microsoft Q&A community has a policy that the question author cannot accept their own answer, they can only accept answers by others, I'll repost your solution in case you'd like to Accept the answer.

.

Issue:

You're using Azure AI Studio with Azure SQL DB. You would like to retrieve the numerical fields, but getting the response The retrieved documents do not provide specific information on the maximum units. You need help with numerical retrieval of data.

.

Resolution:

Make the rewriteIntent node convert user's query to Odata string

Use a python code to send an REST API request for the Odata query

Concat numerical fields in the output to the CombinedText field

Use it to in the generateReply LLM node to retrieve the answer.

**

Please do not forget to "Accept the answer” and “up-vote” so that this beneficial to other community members.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure AI Studio on SQL data base - Problem retrieving Numerical fields

0 additional answers

Your answer