Skip to Content
Author's profile photo Former Member

Text Analysis of IPL Match using Twitter Data (Part 1)

Hi All ,

IPL is going on in INDIA these days and I thought of doing TEXT ANALYSIS of Match between Chennai Super Kings and Royals Challenger Bangalore.

1. Data which will be analyzed – Twitter

2. Source – Excel

Note – We can download twitter data and few blogs guide on how to do it. For this blog I have the Twitter Data ready over excel which I will be using for analysis.


ℹ M S Dhoni – Captain of Chennai Super Kings & ℹ Virat Kohli – Captain of Royals Challenger Bangalore

Snap 1 - Players Screen Shot.JPG

First of all we need to Create Schema.

Please use SQL highlighted below to create Schema. I am using “IPL” as Schema name for this blog.

Snap 2 - Create Schema.JPG

Once the SQL in above Screen Shot is executed, Schema with “IPL” name is created.

Refer to below screen shot which indicates Schema is created.

Snap 3 - Create Schema.JPG

In this Blog we are uploading Twitter Data using Excel.

Snap 4 - Importing Tweet Data using Excel.JPG

Excel reside in my Computer and I will be uploading the same for this Blog.

Snap 5 - Picking Excel from folder.JPG

Fields from the Excel which has ‘tweetId’, ‘memberId’ ‘tweetDate’ ‘tweetContent’.

Refer to below screen shot

Snap 6 - Picking Excel from folder.JPG

Below screen shot reflects that Table is created and Data which is imported from Excel

Snap 7 - Table created and data imported into Schema.JPG

We need to create Index on table which we created

Table Name – IPL_Match_Twitter Data

Below SQL need to be executed which will created Index name ‘ipl’ in Schema ‘IPL’

Configuration Used – ‘EXTRACTION_CORE_VOICEOFCUSTOMER’

Use the SQL as it is , just change the Index name as per choice and Schema/Table Name followed by Column name on which Index need to be created.

Snap 8 - Creating Index.JPG

$TA_IPL in below screen shot indicates , Index table is created

Snap 9 - Index created.JPG

If we check the Table Contents , Screen like below appears

Snap 10 - Fetching Data from Index Table.JPG

If we want to Generate SQL of already created table then Right Click on Index Table – Generate – Select Statement

Benefit – We can avoid writing SQL statement for fetch on table and remove the column name which is not needed for analysis.

Snap 11 - Generating SQL.JPG

I have removed few Column name and kept only below, If I execute the SQL table content will be displayed only for selected column.

Snap 12 - Generating SQL with columns which are needed.JPG

Screen if we execute the above SQL

Snap 13 - Output of Index table with column needed.JPG

🙂 Lets play with SQL now 🙂

Below Query on Index Table will fetch Count of TA_TOKEN in Descending Order.

Benefits – It will display maximum discussed word

Snap 14 - SQL commands_1.JPG

ℹ TA_TYPE column carries details related to Sentiment.

    If we see below Screen Shot we can see few Sentiments like

    1. WeakPositiveSentiment

    2. StrongPostiveSentiment

    3. WeakNegativeSentiment

    4. StrongNegativeSentiment ℹ

Snap 15 - SQL commands_2.JPG

If we use below query on TA_TYPE equal to Person , we notice Virat Kohli and MS Dhoni have been used differently like

1. Kohli

2. Virat Kohli

3. Virat

Snap 16 - SQL commands_3.JPG

➕ Note – How above issue can be solved will be explained in next blog which cover ‘Custom dictionary’ and ‘Custom Configuration’

          We will be using TA_NORMALIZED column for this and writing more SQL ➕

In the next blogs I will cover

1. Custom Dictionary ➕

2. Custom Configuration ➕

3. Creating Analytical View

4. Analyzing data using SAP Lumira

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Good one for learners..

      Author's profile photo Former Member
      Former Member

      Thanks Rahul! very nice demonstration of entity extraction and the capabilty of SAP HANA in terms of sentiment analysis.

      Author's profile photo Former Member
      Former Member

      good job

      Author's profile photo Former Member
      Former Member

      Hey Rahul, Nice concept. Good way to connect the understanding of HANA Text Analysis with what we Indians can relate to very quickly i.e. Cricket.

      Author's profile photo Pooja Goel
      Pooja Goel

      Perfect Example...

      Author's profile photo Former Member
      Former Member

      Nice Article

      Author's profile photo Vivek Tripathi
      Vivek Tripathi

      best example to understand HANA text analysis part....  😉

      Author's profile photo Former Member
      Former Member

      Good show !!

      Author's profile photo Hadeed Hashim
      Hadeed Hashim

      For the upcoming PSL 2020 Schedule, Twitter will erupt right about PSL Tickets, Match Live Scorecard, Match Highlights, Tweets Analysis etc. Stay Connected to PSL Final for more details... The similar stuff we have like IPL.