Optimize Searches with SQLScript for SAP HANA

Big data has been on the agenda of many companies for some time now. The central question is: How can the analysis and evaluation of ever-increasing data volumes and data flows as well as different data formats be successful? Only those who can draw the right conclusions from the huge flood of data and align their strategies accordingly will remain competitive in the age of the Internet of Things and Big Data. A database system that enables real-time analysis and evaluation of this data comes from SAP and is called HANA.

While the scepticism with the market launch in 2010 was still rather high, HANA is now used by around 7000 companies, and the trend is rising. A major advantage of the HANA database: Thanks to in-memory technology, users can analyze data much faster and more efficiently and evaluate it in real time. HANA also includes an integrated search function to search records using various methods. Although the search function is already on board, it must be set up for each company and its different requirements. If the search function is optimally set up, it increases user satisfaction and acceptance and also leads to greater cost-effectiveness in the long term. Incorrectly set up, the search function shows too high error rates, user acceptance decreases and productivity suffers.

Recommended action for an optimized HANA search

In his bachelor thesis, our colleague Johannes Brandau scientifically investigated the optimization of the HANA search for structured data and derived corresponding recommendations for action from his results. The result is a guide for the search settings in SAP HANA, which can help companies to adapt the search functions to their needs and significantly increase acceptance within the company.

In this blog post we present the most important aspects of the guide. There is certainly a need for action in your company to optimize your search settings and you will find suggestions for improvements in the following sections. 

The search without SAP HANA

Without SAP HANA, the search results often remain inaccurate because error tolerances are not possible. In addition, only conventional SQL queries that are very long and complex can be made. By using the various functions provided by SAP HANA and optimizing the queries using the different search modes, the search result can be significantly improved. Our scientific study shows how this optimization works in detail.

The optimized SAP HANA search

As already mentioned, SAP HANA has integrated search functions available, such as fuzzy search or freestyle. Different search methods such as compare, search, symmetric search or substringsearch can also be used for the HANA search. The aim was to find out whether the optimization of the search methods had the desired positive effect on the HANA search. Our employee's research results relate to structured data, personal data or data relating to goods or logistics are best suited. 

The study tested three areas of the HANA search: the fuzzy search, the freestyle search and several searches linked by UNION. The first two search methods tested the search with and without error scenarios and whether indexing had a positive effect on the search results. The aim of the UNION query was to find out whether the search of several columns connected by UNION delivers more accurate results than other search functions and whether indexing also improves the result here.

The result: Indexing has a positive effect on the search results in SAP HANA. Another finding: The different search methods showed that the results are very different and that there is a strong correlation with the data to be searched. If you are looking for data with a certain error tolerance, the fuzzy search is the best choice.

Increase the performance of your BW with SQLScript

In the following section we will show you an exemplary implementation of the SAP HANA Fuzzy Search using the four different search methods:

// Fuzzy Search with compare  

SELECT  SCORE()  AS  score,  *  FROM  COMPANIES

WHERE  CONTAINS(COMPANY_NAME,'SAP',FUZZY(0.8,'similarCalculationMode=compare'))

ORDER  BY  score  DESC;

// Fuzzy Search with substringsearch

SELECT  SCORE()  AS  score,  *  FROM  COMPANIES

WHERE  CONTAINS(COMPANY_NAME,'SAP',FUZZY(0.8,'similarCalculationMode=substringsearch'))

ORDER  BY  score  DESC;

// Fuzzy Search with search

SELECT  SCORE()  AS  score,  *  FROM  COMPANIES

WHERE  CONTAINS(COMPANY_NAME,'SAP',FUZZY(0.8,'similarCalculationMode=search'))

ORDER  BY  score  DESC;

// Fuzzy Search with symmetricsearch

SELECT  SCORE()  AS  score,  *  FROM  COMPANIES

WHERE  CONTAINS(COMPANY_NAME,'SAP',FUZZY(0.8,'similarCalculationMode=symmetricsearch'))

ORDER  BY  score  DESC;

The potential of an optimized HANA search

The HANA database has the speed advantage over conventional relational databases and is therefore better suited for Big Data analysis. The included search function, on the other hand, is still relatively unexplored territory for many people, but at the same time has a very high potential that needs to be exploited. The scientific research of our colleague shows that an optimization is possible and can make a difference, if one wants to increase the accuracy and thus also the user satisfaction.

As a specialist for SAP BI and system development, we would be pleased to be your professional partner when it comes to the conversion to and optimization of SAP HANA and are always available to advise you with our expertise. Find out more about the optimized HANA search and simply contact us – we are happy to help.