Today, both the source and volume of data collected have exploded. It is now easy to analyze the structured data (such as your sales numbers), but more difficult to listen to your customer. SAP HANA allows you to convert unstructured customer feedback in multiple languages from multiple sources into actionable insight. The insight helps to create and modify campaigns, understand current sentiments and trends, launch new products, and correct the pricing of products.

The insight even helps to create targeted contents to increase sales, change customer sentiments for critical problems, and gauge customer feelings. One example of text analysis is the use of sentiment analysis in elections to understand voters’ feelings.

Text analysis involves pattern recognition; tagging or annotation; information extraction; data mining techniques, including link and association analysis; and visualization. The goal is to turn text into data for analysis.

SAP HANA offers the flexibility to do text analysis and analytics on one platform. The massive data in SAP HANA is stored in a single database that saves time and cost and increases performance. In my example, I fetch twitter tweets and perform text analysis on them to understand customer sentiments. I also create visualizations of the data.

The steps to apply SAP HANA text analysis are as follows:

• Upload data to SAP HANA
• Perform text analysis configuration and create a custom dictionary in case the existing one does not suffice
• Create a full text index and model
• Create visualizations

Step 1. Upload Data to SAP HANA

In my example I have extracted Twitter data to an Excel file on my desktop using the Search API and uploaded the file into SAP HANA using Python Open Database Connectivity (PYODBC). It is the easiest way of connecting to SAP HANA via Open Database Connectivity (ODBC).

The SAP HANA Database Open Database Connectivity (HDBODBC) driver is a prerequisite for uploading data in SAP HANA using Python. Users can upload data by various data provisioning techniques such as SAP Landscape Transformation Replication Server (SLT) and SAP Data Services. Other techniques are already covered in the following articles:

• How to Move Your Data into SAP HANA with SDA and BODS
• How to Select the Appropriate SAP HANA Data Provisioning Tool
• How to Perform Data Provisioning in SAP HANA Studio

The driver is installed with the SAP HANA client. You can check if the driver is present in your laptop ODBC data source administrator (Figure 1) by following menu path Start > Control Panel > System & Security > Administrative Tools > ODBC > System DSN.

Figure 1

HDBODBC driver

Prior to the execution of the Python script, the column table “TEXT_ANALYSIS”, “PRODUCT” that holds the unstructured data should be present in SAP HANA. Execute the query (Figure 2) in the SQL console of the SAP HANA system to create the column table. The table is present in the schema TEXT_ANALYSIS table folder under Catalog in Systems (Figure 3).

Figure 2

Create a table query

Figure 3

Product table

I have created the following Python script in Figure 4 that reads the local desktop file that has Twitter tweets. The script loads file data into the SAP HANA column table “TEXT_ANALYSIS”, “PRODUCT”.

Figure 5

Uploaded data

The strategy I followed to get correct insight is to remove retweets from the Excel file and only upload the relevant tweets in SAP HANA. I did it to eliminate the sentiment bias of a single opinion.  

Step 2. Text Analysis Configuration and Dictionary

The TEXT_ANALYSIS configuration file that is present in the package named sap (Figure 6) uses analyzer, dictionaries, and extraction rule sets to extract text analysis information.

Figure 6

Text analysis configuration

The file is stored in the SAP HANA repository in an XML format. It specifies the text analysis steps to be performed. The following analyzers are used in different types of text analysis configuration:

• The Format Conversion Analyzer specifies the option of format detection for uploaded unstructured data. The format changes according to the MIME type. For example, it checks if it is a tweet rather than a blog.
• The Structure Analyzer specifies automatic language detection. It automatically detects the language of the unstructured text and then extracts text analysis information in the TA_TYPE column. In my scenario the unstructured text is in English.
• The Linguistic Analyzer specifies the options of tokenization, stemming, and tagging:

a. Tokenization: The separation of input text into its elements. For example, the sentence “Iphone is a product of Apple” would be broken into six tokens.

b. Stemming: The identification of word stems or dictionary forms. For example, the words connection, connected, and connective are reduced to the stem connect.

c. Tagging: The labeling of the word’s part of speech. For example, paper is a noun.

• The Extraction Analyzer specifies the option for entity and relationship extraction. You specify the extraction rules and dictionary in the analyzer for sentiment analysis. This analyzer is only used in Extraction_* configurations.

a. Dictionary: You use this user-defined repository to create more user-defined entity types and names. You can also use it to store name variations. A developer can combine several dictionaries together in one configuration file. For example, if you want to replace the real-world entities such as United Kingdom, U.K., and UK by a single entity EuropeanCountry , then use the dictionary.

b. Extraction rule: You use this rule to identify a more complex entity type than the dictionary. You can create extraction rules to identify entities in an industry-specific language and establish an entity-to-entity relationship.

SAP has provided following text analysis configuration for extracting sentiment and text analysis information from unstructured data:

• Linganalysis_basic provides tokenization capability.
• Linganalysis_stems provides tokenization and stemming capability.
• Linganalysis_full provides tokenization, stemming, and tagging capability. For example, the sentence “I hate you” is stored in the full text index table (Figure 7). The TA_RULE is LXP for linganalysis_*.

Figure 7

Linganalysis_full

• Extraction_core extracts entities from unstructured text, such as people or organizations, including linganalysis_full capabilities.

• Extraction_core_voiceofcustomer provides sentiments and emotions, including extraction core capabilities. You can use this configuration to retrieve information on customer needs and perceptions. The TA_TOKEN is extracted according to configuration. The column TA_TYPE provides information on user sentiments (Figure 8). The TA_RULE for Extraction_* is Entity Extraction.

Figure 8

Extraction_core_voiceofcustomer

The text analysis configuration and dictionary are used for creating an index on which analytics can be performed. I have used Extraction_core_voiceofcustomer for my scenario as it provides sentiment analysis capabilities and also gives information on customer views.

Step 3. Create a Full Text Index and Model

You have to create a full text index on the column containing the unstructured data to use text analysis functions. The following data types are valid for text analysis: Text, Bintext, Nvarchar, Varchar, Nclob, Clob, and Blob. You can create an index two ways:

• Index during table creation
• Manual index on an existing table

You have to write the following parameters in SQL query while creating a full text index for text analysis:

TEXT ANALYSIS ON

CONFIGURATION '<NAME OF TEXT ANALYSIS CONFIGURATION>'

Index During Table Creation

The benefit of creating an index when you create a table is that it is created automatically with the table and you don’t have to execute a SQL query afterward to create a full text index. Execute the query in the SQL console shown in Figure 9 to create the index.

Figure 9

Index creation with a table

The option ASYNC automatically performs text analysis in the background and stores the results in an index table after an insert or update operation in the database table completes. If you do not specify a language in the index creation, then English is used by default.

Manual Index on an Existing Table

I have created a manual index for my scenario. Execute the query (Figure 10) in the SQL console of the SAP HANA system to create the index. The manual index approach is useful when tables already exist and the requirement is to use the table for text analysis.

Figure 10

Manual index

The index table is created with the name $TA_<index_name> in the same schema that contains the original table. When the index is created, all the pre-existing content in the table is processed and subsequent updates to the original table update the index table. The index is also dropped when you drop the original table on which the index is created.

The full text index is created after the execution of the query in Figures 9 or 10. The important columns in the table are:

• TA_TOKEN contains the elements of the input text.
• TA_RULE is required to distinguish between the linguistic analysis output and output from the entity extraction.
• TA_TYPE specifies the token on the basis of the configuration. For example, if the configuration is Extraction_* then it specifies SAP as the company. Otherwise, if configuration is Linganalysis_* then Noun is mentioned for SAP. This column also provides information on sentiments and emotions from unstructured data.
• TA_COUNTER counts all tokens across the document. For example, the sentence “I hate you” has a token for each word (Figure 11). The token generated varies with configuration.

Figure 11

TA_Counter

Figure 12 shows the full text index with the TA_TOKEN column that contains the elements of the input text.

Figure 12

Text analysis index

Model

You can use the full text index table to do analysis and get insight. Post index creation I have created analytic and attribute views to analyze the Twitter feeds and create visualizations. One of the views is shown in Figure 13.

Figure 13

Analytic model

Step 4. Create Visualizations

The first step in creating a visualization is to analyze the type of the tweets users post on Twitter. I have created a tag cloud graph (Figure 14) based on the TA_TOKEN column in the full text index table to show that the tweets are mainly about user sentiments and views on different topics.

Figure 14

User sentiments

Later, I filtered the product description to Kindle and then analyzed the tweets on the column TA_TOKEN via the tag cloud graph (Figure 15).

Figure 15

Analysis of tweets

You can do predictive analysis and geospatial analysis on the data to analyze the sentiments by region and to obtain insights.

Akash Kumar

Akash Kumar is an SAP technical consultant specializing in ABAP and HANA. He has 5+ years of experience in design and development of products with corporate organizations such as TCS, RBS, and SAP Labs. He is an active speaker and has organized multiple BarCamps in Delhi, India. He holds a bachelor of technology degree in computer science. He holds a certification in SAP HANA.

 

You may contact the author at akashkumar1987@gmail.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.