Having done some work with the unstructured text engine within the SAP HANA Platform I wanted to capture and share how to do this.  For this example I have used Twitter data looking at Formula One hashtags and F1 accounts.

The linguistic engine is just one of the engines in the HANA Platform but is not often talked about but it is very easy to use to extract structured information from unstructured text. This text could be held in a simple character field or it could be within a binary document, we support many binary formats including TXT, RTF, HTML, PDF, DOC, DOCX, XLS, XLSX, PPT, PPTX and MSG. The official Text Analysis, Text Search and Text Mining documentation can be found here

For this example I have used the Text Analysis (TA) engine straight out of the box and yes it works, the results were OK, but as you would expect with any Industry, Line of Business or sport F1 has its own terms, the drivers and teams(constructors) being prime example of these so I wanted to create a custom dictionary to improve the understanding of these.

There’s a good blog that shows the old way (HANA SP7) of doing this SAP HANA Custom Dictionary


With SP9, this is even easier, there’s only really 3 steps.

  1. Create the XML dictionary
  2. Reference the dictionary in a TA configuration file
  3. Call the Text Analysis Configuration with SQL

1.1 HANA Web IDE

Go to the HANA Web IDE, for me this is at

http://ukhana.mo.sap.corp:8001/sap/hana/ide/editor/

For others it would be

http(s)://<HANA HOSTNAME>:80<HANA INSTANCE>/sap/hana/ide/editor/


WebIDE-1.png

1.2 Create Dictionary File

Create a New “File” for the dictionary, I used the path sap.hana.ta.config

The file needs to end in .hdbtextdict

WebIDE-New FIle.png

1.3 Create the dictionary

Here’s a snippet of mine, I have attached the full XML file below.  Check your XML file opens in a web browser, also be care of the double quotes ” – sometimes you may find the “smart quotes” like “ and ” which are not smart for XML files! 


<?xml version="1.0" encoding="UTF-8"?>
<dictionary xmlns="http://www.sap.com/ta/4.0">
   <entity_category name="F1 Driver">
      <entity_name standard_form="Lewis Hamilton">
            <variant name ="Lewis"/>
            <variant name ="Hamilton"/>
            <variant name ="HAM"/>
            <variant name ="@LewisHamilton"/>
            <variant name ="#TeamLH"/>
            <variant name ="LewisHamilton"/>
      </entity_name>
      <entity_name standard_form="Jenson Button">
            <variant name ="Jenson"/>
            <variant name ="Button"/>
            <variant name ="#JB22"/>
            <variant name ="@JensonButton"/>
            <variant name ="JensonButton"/>
      </entity_name>
      <entity_name standard_form="Kimi Raikkonen">
            <variant name ="Kimi"/>
            <variant name ="Raikkonen"/>
            <variant name ="Kimi Räikkönen"/>
            <variant name ="Räikkönen"/>
            <variant name ="Ferrari Kimi Raikkonen"/>
      </entity_name>
     </entity_category>
</dictionary>











Below you can see the full Dictionary XML file inside the WebIDE

Once you click Save you should see in the black console as above that is gets saved and activated (compiled automagically) immediately.

F1.hdbtextdict.png


2.1 Create configuration file 

The easiest way is to chose one if the other .hdbtextconfig file that you see.  Whichever one is the most appropriate.

This can be done easily – Right click copy and paste. I chose the VOICEOFCUSTOMER one as I was initially using some Twitter data for the unstructured analysis. Give the new file a sensible name, remember to keep the .hdbtextconfig extension.


2.2  Edit Configuration file

Open your newly copied file and scroll to the bottom.

Add, an entry that references your Dictionary file you created above for me I added


<string-list-value>sap.hana.ta.config::F1.hdbtextdict</string-list-value>











2.3 Save configuration file

You should see it also activates at the same time, which will check for any errors too.

F1.hdbtextconfig.png

3.1 Database Table

You can now use your new configuration. I loaded some Twitter data using the HANA Data Provisioning Agent that’s also part of HANA SP9.  I created a simple table F1-TWEETS with 3 columns, It must have a primary key and also a text field in either an NVARCHAR, VARCHAR, BLOB or CLOB

F1-TWEETS.png


INSERT INTO "F1-TWEETS" (
SELECT "Id", "ScreenName", "Tweet"  FROM "F1"."F1HANA-Twitter_Status");










3.2 Create FullText index with the new configuration


CREATE FULLTEXT INDEX "F1-TWEETS-FTI" ON "F1"."F1-TWEETS"("Tweet")
CONFIGURATION 'F1'
FAST PREPROCESS OFF
TEXT ANALYSIS ON;









This creates a new table in my case $TA_F1-TWEETS-FTI which contains the structured version of the unstructured data.

When you use a dictionary the TA_NORMALIZED column is populated enhanced definitions that you have defined in the custom dictionary.


3.3 Visualisation of the Restults

To illustrate the difference that the dictionary makes compare the 2 visualisations that I created with Lumira using a calculation view against the $TA_F1-TWEETS-FTI table.


Without the Dictionary – TA_TOKEN

Without-Dictionary.png

Without-Dictionary 2.png



With the Dictionary – TA_NORMALIZED

With-Dictionary.png

With-Dictionary 2.png

For me it is clear that there enormous benefit to using the Text Analysis to turn unstructured data into meaning information and when you combine that with the custom dictionaries you have a very powerful tool.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply