HANA Fuzzy search demystified!

You want to search for all GL postings with relate to ‘insurance’. Because insurances are exempted from tax, there should be no tax amount included in these postings. But how do you look for these postings? Using HANA Fuzzy search this can be done.

In the HANA full-text search options within SQL, fuzzy search is one of the options. SAP describes fuzzy search as follows:

Fuzzy Search is a fast and fault-tolerant search feature for SAP HANA. A fuzzy search returns records even if the search term contains additional or missing characters or other types of spelling errors.

Source: SAP HANA Search Developer Guide

What this means is that when someone makes a typo, you most likely still will get a hit within your search parameters. The result of the fuzzy search will give you a hit score known as fuzzy score. This rates from 1 to 0, 1 being a full hit. It will tell you how good the hit is with your search query, and possibly you can filter out low ranking results.

Setup

Basically there are 3 steps

1. Set the proper index on your table that needs to be searched
2. Write a HANA scripted calculated view that includes the fuzzy search
3. Use the output of the scripted calculated view for further analysis

1. To be able to search word by word through string fields, an additional index is needed. If this is index is not set, the result will look at the string as a whole and this will have impact on the fuzzy score. To set the index look up the table in the SAPABAP1 schema, in my case the GL postings and set a new ‘full-Text index’ on the particular field (column) you wish to use in the fuzzy search. Make sure to check the ‘Fuzzy Search’ box.

Once saved, the index is available and ready for use.

• Now the index is set, we can write the SQL code for the scripted calculation view. Here we have to adhere to the fuzzy search syntax in the CONTAINS-statement. Below is the code from the example. First there are certain field I need, but as first field the fuzzy score. Using the code below you will get the fuzzy score in the output.

Then in the WHERE the fuzzy search is included as a filter to only get the records that contain my keywords. Being SQL I can also add an extra filter to exclude historical data at the end.

In the fuzzy search statement several things are happening: there are multiple search words used who are all given in 1 string separated by the OR-statement and then I only wish to include hits with a fuzzy score of 0.8 or higher.

The result is a set of records that can be used in another calculation view for example.

• The result of the scripted calculated view is just like any other view: you can use this as a source for another calculation view, use in a composite provider, etc. In my example I have used the result of the fuzzy search to look for certain GL postings in another graphical calculation view. These GL postings are in turn used for filtering and tax information is added to the postings.

This is an example on the ease of use. For my scenario this worked well and I guess for 80% of the scenarios this works well. However there is a whole set op parameters that can be added to tweak the fuzzy search. You can find these in the SAP HANA Search Developer Guide.

Thanks for your interest!

Gerard.