In this article by Vinay Singh, author of the book Real Time Analytics with SAP HANA, this article covers Full Text Search and hierarchies in SAP HANA, and how to create and use them in our data models.
After completing this article, you should be able to:
(For more resources related to this topic, see here.)
Before we proceed with the creation and use of Full Text Search, let’s quickly go through the basic terms associated with it. They are as follows:
Some of the applied applications of fuzzy search could be:
Let’s see what are the use cases for text search:
The results of text analysis are stored in a table and therefore, can be leveraged in all the HANA- supported scenarios:
The capabilities of HANA Full Text Search and text analysis are as follows:
The benefits of full text search:
The following are the supported data types by fuzzy search:
Before we can use the search option in any attribute or analytical view, we will need to enable this functionality in the SAP HANA Studio Preferences as shown in the following screenshot:
We are well prepared to move ahead with the creation and use of Full Text search. Let’s do this step by step as follows:
Create Schema <DEMO>; // I am creating , it would be already present from our
previous exercises.
SET SCHEMA DEMO; // Set the schema name
Create a Column Table including FUZZY SEARCH indexed columns.
DROP TABLE DEMO.searchtbl_FUZZY;
CREATE COLUMN TABLE DEMO.searchtbl_FUZZY (
CUST_NAME TEXT FUZZY SEARCH INDEX ON,
CUST_COUNTY TEXT FUZZY SEARCH INDEX ON,
CUST_DEPT TEXT FUZZY SEARCH INDEX ON,
);
Search for customers in the countries that contain the ‘MAIN’ word:
SELECT score() AS score, *
FROM searchtbl_FUZZY
WHERE CONTAINS(cust_county, 'MAIN');
Search for customers in the countries that contain the ‘MAIN’ word but with Fuzzy parameter 0.4
SELECT score() AS score, *
FROM searchtbl_FUZZY
WHERE CONTAINS(cust_county, 'West', FUZZY(0.3));
Perform a fuzzy search for a customer working in a department that includes the department word :
SELECT highlighted(cust_dept), score() AS score, *
FROM searchtbl_FUZZY
WHERE CONTAINS(cust_dept, 'Department', FUZZY(0.5));
Fuzzy search for all the columns by looking for the customer word:
SELECT score() AS score, *
FROM searchtbl_FUZZY
WHERE CONTAINS(*, 'Customer', FUZZY(0.5));
Hierarchies are created to maintain data in a structured format, such as maintaining customer or employee data based on their roles and splitting the data based on geographies. Hierarchical data is very useful for organizational purposes during decision making.
Two types of hierarchies can be created in SAP HANA:
The hierarchies are initially created in the attribute view and later can be combined in the analytic view or calculation view for consumption in a report as per business requirements. Let’s create both types of hierarchies in attribute views.
Each level represents a position in the hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels. Each level above the base level contains aggregate values for the levels below it.
Add a client and node key again as output to your attribute view that you just created, that is AT_LEVEL_HIERARCY_DEMO, as we will use these two fields in
Add the attribute view created in the preceding step and the SNWD_SO_I table to the data foundation:
The parent-child hierarchy is a simple, two-level hierarchy where the child element has an attribute containing the parent element. These two columns define the hierarchical relationships among the members of the dimension. The first column, called the member key column, identifies each dimension member. The other column, called the parent column, identifies the parent of each dimension member. The parent attribute determines the name of each level in the parent-child hierarchy and determines whether the data for parent members should be displayed
Let’s create a parent-child hierarchy using the following steps:
The following is the sample code and the insert statement:
CREATE COLUMN TABLE "DEMO"."CCTR_HIE"(
"CC_CHILD" NVARCHAR(4),
"CC_PARENT" NVARCHAR(4));
insert into "DEMO"."CCTR_HIE" values('','')
insert into "DEMO"."CCTR_HIE" values('C11','c1');
insert into "DEMO"."CCTR_HIE" values('C12','c1');
insert into "DEMO"."CCTR_HIE" values('C13','c1');
insert into "DEMO"."CCTR_HIE" values('C14','c2');
insert into "DEMO"."CCTR_HIE" values('C21','c2');
insert into "DEMO"."CCTR_HIE" values('C22','c2');
insert into "DEMO"."CCTR_HIE" values('C31','c3');
insert into "DEMO"."CCTR_HIE" values('C1','c');
insert into "DEMO"."CCTR_HIE" values('C2','c');
insert into "DEMO"."CCTR_HIE" values('C3','c');
As per the business need, we can use one of the two hierarchies along with attribute view or analytical view.
In this article, we took a deep dive into Full Text Search, fuzzy logic, and hierarchies concepts. We learned how to create and use text search and fuzzy logic. The parent-child and level hierarchies were discussed in detail with a hands-on approach on both.
Further resources on this subject:
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…