[Genie] Format assistance & entity match doesn't work for me

[Jeff_Zhang]

I enable format assistance & entity match for some table fields in my genie space. But it looks like it doesn't work. 
e.g. the following field is categorical and I enable format assistance & entity match for it. The following is the generated sql:
 

move_type = 'GATE_OUT'

But what I expect is move_type = 'GATE-OUT'

To me, it looks like genie don't know the real value in move_type.
Anyone can help to fix this problem? Thanks

[pavannaidu]

@Jeff_Zhang 

It could be for a couple of reasons

1. Check if the number of distinct values in this column exceeds the current limit of 1,024 distinct values. Reference: https://docs.databricks.com/aws/en/genie/knowledge-store#-prompt-matching-components

2. Check if there are duplicate entries in the column, where both GATE_OUT & GATE-OUT exist in th column.

3. More importantly, it could be that the entity matching state is stale. The state is created when the Genie space is created. However, if the data is refreshed, you will need to manually refresh the state.

• In UI, toggling entity matching on and off will refresh the state that is used for matching values. You will see a spinning wheel in the UI, for example, see below 

• If you want to update the Genie space, you can also do it programmatically: https://docs.databricks.com/api/azure/workspace/genie/updatespace

I hope this helps! 

[Jeff_Zhang]

Thanks @pavannaidu I am sure this column doesn't exceeds the current limit of 1,024 distinct values. And there's no duplicate GATE_OUT & GATE-OUT. And I have refreshed the entity matching several times, but doesn't work for me. 

[pavannaidu]

Thanks for checking, @Jeff_Zhang ! Just want to clarify regarding refreshing the state: you will need to toggle off & save, then toggle on again & save. Otherwise, format assistance/entity matching won't refresh. You will see a spinning wheel in the UI when it's refreshing. 

Additionally, do you see values such as GATE-OUT, GATE-IN, etc., in the format assistance? 

Last resort: try recreating the Genie space and see if it works. You can clone a Genie space; this will recreate the space: https://docs.databricks.com/aws/en/genie/set-up#clone-a-genie-space

[Jeff_Zhang]

@pavannaidu I have tried all the approaches, but none of them works for me. Genie is a blackbox for me which means it is very hard for me to do the trouble shooting. 

[mckohnen]

@pavannaidu I would like to reopen this issue, as I have faced similar problems (and yes I made sure to enable it properly, toggling off and saving, toggling on and saving again, etc.). For Genie Agent in Chat Mode, when going into the thinking process and checking the intermediate queries, it does not seem to be able to leverage the entity matching. According to the documentation:

Review the following example:

"Show me car sales in Florida for Q1."

If the data uses state abbreviations (such as FL), and Genie cannot access the values for that column, Genie might generate SQL that includes ILIKE '%Florida%', which returns no results.

Enabling entity matching on the state column allows Genie to access representative values. With this context, Genie can recognize that FL corresponds to "Florida" and generate more accurate SQL.

Without entity matchingWith entity matching

WHERE state ILIKE '%Florida%'

WHERE state = 'FL'

Now in my case I have a table with region, business_unit, country (3 columns) no more than 30 rows. Enable format assistance and entity matching on all of them. If I ask any question that needs BU (business_unit) filtering, e.g. as simple as "Show me the countries belonging to the Greek BU" I get the following thinking pattern in Genie Chat Mode: 

And the first SQLs in the thinking process look like this: 
Inspection:

SELECT DISTINCT
  `country`
FROM
  `dc_adb-landing-zone-002`.`idor`.`tsc2_ucm_dim_geo_entities`
WHERE
  `business_unit` ILIKE '%Greece%'
  AND `business_unit` IS NOT NULL
  AND `country` IS NOT NULL

Checking:

SELECT DISTINCT
  `business_unit`
FROM
  `dc_adb-landing-zone-002`.`idor`.`tsc2_ucm_dim_geo_entities`
WHERE
  `business_unit` ILIKE '%Greece%'

 Verification (this is the only time the actual BU name is filtered as I would expect from the beginning given the entity matching documentation example):

SELECT
  `business_unit`,
  `country`,
  `region`
FROM
  `dc_adb-landing-zone-002`.`idor`.`tsc2_ucm_dim_geo_entities`
WHERE
  `business_unit` = 'BU Greece & Cyprus'

 And the final SQL used for the answer then though is the first SQL (from the inspection, so with ILIKE filter).

Clearly something is not working as documented. Please let me know if this is a Genie Agent bug (if so please forward it to the team to fix it, as this would decrease the thinking time which can pile up for complex questions) or if there is something else we are all missing, to really leverage entity matching.