As discussed in the first blog entry, Twitter Search API has been accessed, with the word ‘cityrail’ as the search term. To those not in the know, Cityrail is the train network of Sydney (Australia) metropolitan area. It was a very obvious target: with relatively big customer base it was guaranteed to get enough unstructured data. Over time (2-3 months) Data Services collected a few thousands tweets supposedly related to Cityrail.

It is worth to elaborate on the data collection process. In one request-response session, Twitter Search API returns up to 100 most recent tweets. Provided that within every 15 minutes the number of tweets about Cityrail does not exceed 100 (that assumption has been confirmed), Data Services Batch Job running every 15 minutes can collect all such tweets almost in real-time.

I would also like to mention a conversation I had with a colleague recently. He wondered if JSONAdapter may help to obtain a large amount of tweets for analysis instantly, and if Twitter Streaming API might help with that. The answer to the last question seems to be negative: once you open a Stream, Twitter feeds entries there in real time, but those are current entries; they can be grouped into time slots or used in another ETL process by Data Services. Streaming API should be used, for example, when for some topic the number of tweets exceeds, say, 100 per minute, hence Batch Job described above may not be able to cope, even if executed every minute.

Otherwise, the only difference between Stream and Search APIs becomes that the Stream API would provide raw data, while Search would apply some extra filtering/ranking by relevance to the search term. In fact, it is possible to build a Data Services job to get historical results, executing consecutive search requests to Search API deeper and deeper into the past (by restricting the TweetID field in a request) — the process would not be instant, though, but probably running for 1-2 hours (consider it an Initial Load), and it is hard to tell how far into the past it can go.

The bottom line is: if there is an immediate need to analyze the historical data, you may have to contact the Twitter’s partner data providers. Otherwise, JSONAdapter may help to start collecting the data and implement (near) real-time analysis.

The further discussion will be around the following points:

• text parsing using using Data Services,
• ‘noise’ reduction,
• Topic-Sentiment links rebuilding,
• the sarcasm problem (no pun intended!).

Text parsing itself is simpler than one might expect. A special Transform in Data Services v.4.0, called Entity Extraction, parses the input unstructured text and extracts entities and facts about them. Its output is a number or ID’ed records containing one entity/fact each, accompanied with location attributes (paragraph number, sentence number and offset) and categorized accordingly to the rules specified in the Transform options.

Provided out of the box are dictionary of categorized entities and a few rulesets for facts extraction – they are located in the folder TextAnalysis of a standard Data Services installation (availability for use is subject to the license: either full DI+DQ or DI Premium). One of those rulesets, Voice Of Customer (VOC), is used for this work. SAP allows customization of rulesets (at your own risk, of course) and implementation of user-defined dictionaries and rules. SAP has also published several blueprints, which could be used to start new text analyses developments. For this blog, a blueprint for sentiment analysis has been used, it does the following:

1. parse the incoming unstructured text into Topic and Sentiment entities using Voice Of Customer ruleset: for example, a phrase “I like apples” would be parsed into Topic=apples and Sentiment=like (accompanied with Sentiment Type ‘StrongPositive’),
2. process Topics data and put Topics into groups, to enable some measures, like number of Sentiments per group, and ensure topics like ‘apple’ and ‘apples’ would fall into the same group,
3. build a SAP BusinessObjects Universe on top of resulting tables, to enable WebI reporting with slice and dice capabilities.

A few important changes have been made to the blueprint design to deal with Twitter data, the first one covering the issue of noise elimination. For starters, the blueprint assumed the original text to be in plain English; in reality, tweets constitute quite a lingo, full of abbreviations, expletives of all kinds, and with incomplete grammar. In the upcoming SP release for Data Services, SAP makes an attempt to keep in touch with social media and adds new entities for trend/hashtag and for handle/mention. That did not seem enough, and a custom ruleset has been implemented and added to the Entity Extraction transform to detect and mark words that should be excluded from further processing. The picture below demonstrates options of the transform, including two out of the box rulesets followed by the custom one:

This way, if an entity is extracted as both e.g. Topic and a (custom-defined) Blather type of entity, it will be detected in a simple join:

The following screenshot displays a sample output from the transform ‘Blather’, the second column contains the entity extracted from the original tweet and categorized as an expletive:

All such entities would be filtered, thus clearing the output from most of the ‘yucks’, ‘hells’, and ‘lols’. There is one more use of those, which will be discussed a couple paragraphs below.

Noise may also occur on macro-level. Tweets analysis is different from the blueprint in one more way: while the blueprint assumed the source text is completely relevant (for example, customer feedbacks on the imaginary Swiftmobile, collected in a separate folder), tweets don’t have to be. Filtering tweets by a word X returns not just a customer’s view on X, but all aspects of people lives that somehow involve X and that people care to write about. The amount of such macro-noise in ‘cityrail’ selection is, actually, small, but in a selection for, say, ‘westfield’ (a major chain of shopping malls in Australia) it becomes much bigger, for obvious reasons. A possible way to further filter the results would be by having a predefined list of topics specific to the bigger theme.

By default, the output of Entity Extraction transform looks like what might be called a ‘spaghetti’ type of data, i.e. it doesn’t care about relationships between Topics and Sentiments. While it may be considered sufficient, a need to relate Topics and Sentiments may be considered. Assuming that in a sentence related topic and sentiment should be closely located, it’s possible to derive Topic-Sentiment pairs from ParentID and Offset fields of Entity Extraction transform output:

This design obviously ignores topics not accompanied by sentiments and vice versa, and those could be added to the reporting data model.

‘Raw’ tweets preview in the database revealed that tweets mostly expressed negative feedback on Cityrail: people tend to complain more often than praise, and – by the way – I wrote the first draft of this blog on the day and hour when some Cityrail’s equipment failure caused major suspension of service and delays. Therefore, it was surprising to see significant ‘StrongPositiveSentiment’-related numbers in the reporting. The reason was that many tweets were sarcastic and should not have been taken literally, but, rather, their sentiment would be opposite to their literal meaning. So, if a tweet is deemed sarcastic, its positive Sentiment should be reverted; while negative Sentiment still counts.

Apparently, sarcasm detection in user feedbacks is a much bigger problem without a general solution. Even a human cannot detect sarcasm perfectly (73% accuracy has been reported from one research), as familiarity with the context is often required. Given the Data Services’ ability to process Python scripts in User Defined Transforms, one might attempt to build a sarcasm detection functionality in Data Services based on Bayesian classification and using not only words, but markers of emotions: emoticons, ‘blather’-words discussed above, words highlighting using ‘*’ (like in “I *love* when trains go slowly in rainy weather”) or enclosing into quotation marks, and, of course, the hashtag #sarcasm. Coincidence of negative and positive (rather, strong positive, in terms of VOC’s ruleset) sentiments or, rather, emotions in one tweet is also a potential sarcasm marker. The last one, actually, can be implemented with regular Data Services ETL:

The results below could have been slightly better if VOC knew that ‘con’ is a short form for ‘conditioner’, not a negative sentiment expression. Some extra customization of the dictionary may be required.

Implementation of the full outlined above sarcasm detection functionality scope, however, seems to be a project by its own and beyond this blog.

Setting up reports on the analysed data was not a primary goal of this work, as the SAP blueprint’s approach of BusinessObjects Universe was adopted. The original plan was to use SAP BW BEx reporting, but as storage of texts longer than 60 characters in BW InfoProviders is not trivial, the idea had been discarded.

Consumer sentiment is quantified here by counting the number of feedbacks, restricted measures have been created for each feedback type. The screenshot below demonstrates how Data Quality grouped topics into larger groups using fuzzy matching logic:

A drilldown into a group is then possible, like below:

An extra characteristic, time, has been added to reporting as an obvious choice: there is clear correlation between number of ‘cityrail’ tweets and morning/afternoon transport peak hours. One might think of implementing a “rolling total negative sentiment” of 30 minute scope and raise an alert if that value exceeds some threshold.

Lastly, beyond consumer sentiment analysis, another obvious idea would be to geocode Tweets using either geolocation information (GPS coordinates) from tweets metadata, or geographical names from tweets themselves (post-processing is required for the latter, of course, to eliminate noise). The geocoded data could be made available for visualization in Business Objects or provided to a GIS product like ArcGIS for use in spatial analyses.

- Roman Bukarev

Facebook data is based on so called social Graph using Open Graph Protocol. Every object in that Graph — a person, a feed, a post etc — is a node in that Graph, having an ID and a few properties. These properties can be accessed using Graph API, which, by no surprise, uses JSON: an inquisitive developer can play with Graph Explorer to get the idea. There are a couple of implications following from that observation: a) every type of Graph node has its own ‘schema’ when described in JSON, b) nodes represent either objects (say, a person) or feeds (wall posts, events etc).

From Data Services extraction point, constantly updated feeds is a better candidate for extraction, as they may contain unstructured data worth analysing. I targeted a feed with wall posts of Commonwealth Bank of Australia (CBA), expecting to see various feedbacks left by CBA’s consumers, which then might be interesting to analyse for consumer sentiment.

Here’s what I would see in Graph Explorer for a typical contents of the Feed node (I edited the image to change names and mask IDs) :

So my first step in Data Services was to import that metadata into JSONAdapter using the URL from Explorer:

I used the option to generate a sample DTD from the JSON data. As I mentioned in the previous blog, it may not be perfect, but is a good start.

A problem that I found with the Facebook Feed object is related to the liquid nature of JSON, maybe somehow utilised by Graph: some nodes of the JSON data have changing names, as below:

Imagine what mess that would make in the data structure definition. These changing numbers, however, do not seem to have much sense, as tag objects here have their IDs anyway. In order to skip them in data definition I had to introduce the setting “Skip level under Elements” and re-import the metadata from the generated DTD file with instruction to JSONAdapter to skip one level under “message_tags” element. Ah, I had to manually adjust the DTD to make it less relaxed, based on my analysis of the data, basically replacing “(element1 | element2 )*”-like DTD constructions with ” (element1?, element2? )”.

As the result of that import, this is the partial screenshot of NRDM schema corresponding to Facebook Feed:

This is enough to build a Data Services dataflow, similar to the one from the previous blog:

In this dataflow, the table T_FB_FEEDS collects messages on the CBA’s wall, while another table, T_FB_TIMER, keeps the change pointer (latest feed entry’s “created_time” attribute) and thus allows to load changed data only.

So, this is how Facebook Feed is finally stored in a database, ready for further analysis:

Now let us discuss RSS (RDF Site Summary) documents in the context of data acquisition via JSON format. RSS is a list of some documents, where each entry in the list contains some summary of the original document (or just data snippet) and some metadata, like posting date or the author. RSS is published as an XML document and is meant to be updated as new documents appear in the original collection, frequency of updates varying from days to minutes. An RSS document may be then accessed by various reader applications or by some aggregator services like Google Reader — or by Data Services.

Keep in mind that RSS is an XML document, and it needs to be converted to JSON, so it could be accessed by the JSONAdapter.

Such conversion looks redundant as JSONAdapter internally converts JSON data to XML again, to pass it to Data Services. However, there is no RSS-reading Adapter for Data Services that I would know of (and I wouldn’t care writing an extra Adapter just for that), so the exercise is legit.

As for what tool may be used to convert RSS to JSON, preferably on the fly, there are a few online services around, easily googlable. My first candidate was Google (as I’m long time user of their Reader application, even that it degraded recently), which worked just fine – however, a few points from their Terms of Service made me not so willing to try and use the Google Feed API . So I picked a free and not so encumbered with usage terms RSS to JSON service on blastcasta.com. Mind you, different RSS-JSON converters deliver different JSON schemas.

After all this research, the actual ETL setup less than an hour or so, here’s the partial screenshot of the RSS schema I had got:

As I had mentioned in the first blog entry, element names are built from JSON elements using a ‘snowball’-approach, and some names may become too long to serve as column headers, after the NRDM-data gets unnested and routed into a database table. A Query transform should be used to a) pick only the required columns, b) map the element names to column names satisfying the staging database’s requirements.

That said, my resulting staging table looks like this:

The ETL process likely needs some delta-capability, e.g. based on some hashcode generated from the RSS-item’s URL or the title; that part being trivial, though.

Technically, RSS format allows storing the original document’s whole contents, and I personally would prefer to see it that way, as it saves the reader’s time and clicks – a few RSS publishers do so, and I love them for that. However, many content owners prefer to release only a small text snippet into RSS, as (I guess) they still want to get their revenue from advertisement etc. That leaves two options: a) only analyse those ~50-words snippets, b) get Data Services to crawl to the obtained links and extract the text from there, ignoring all the markup/navigational/advertisement additions. The second option is obviously more attractive; however, that functionality is missing in the standard Data Services build (as of the time of writing this) and may be a good candidate for a nice little Adapter.

In the next blog entry I will try to cover data analysis opportunities for the data acquired by Data Services.

-Roman Bukarev

I would like to start this series of blogs with discussion on how unstructured data from social media can be loaded to a data warehouse.

In this context appears the name JSON — an open source text-based data interchange format. The acronym stands for JavaScript Object Notation – the name points to the roots of the format, but, actually, the standard is used outside of Javascript, with implementations of JSON for various platforms referenced at its homepage, http://json.org.

Have a glance at JSON code:

}

As you might guess, it describes a green plant of 50 cm height.

Just so you might compare – in XML, a well-known standard for data interchange, one possible way to express the same would be:

</Plant>

On large data volumes the JSON representation is more lightweight than XML, mainly because of missing closing tags. JSON became widely used in web development (“Ajax programming” is your keywords for further reading), which brings us to the topic of those famous social web-based applications, including Twitter, Facebook, Friendfeed, or RSS feeds collected by Google Reader.

So this is why JSON looks like a good candidate to be the medium for unstructured data extraction into a data warehouse: it is supported by source web-applications, and it can be converted relatively easy into XML, understood by major ETL tools – in our case, SAP BusinessObjects Data Services.

Even out of the box, Data Services can integrate with wide range of databases and data formats from various vendors, and that range can be extended by using plugins called Adapters. SAP provides Java Software Development Kit to create such Adapters. A very helpful introduction into Data Services Adapter SDK can be found at SDN website; sample code is also provided as a part of the product installation.

With that SDK I have developed JSONAdapter that obtains data via HTTP in JSON-format, converts it to XML and passes to Data Services – so I will describe the steps to configure such interface with Twitter, assuming the JSONAdapter has been installed, and its Datastore in the Local Repository has been created.

Twitter provides a search interface, where search parameters are included into a URL.  For example, a URL “http://search.twitter.com/search.json?q=intelligence&rpp=50” would render Twitter to return a JSON-formatted result with 50 most recent tweets containing the word “intelligence”.

The first step in ETL setup is to describe the data structure (metadata) in XML DTD form, one that Data Services understands. Unfortunately, it is not always possible to obtain JSON Schema, used to describe the structure of a JSON document – nor it is actually standardized yet. That is why the Adapter makes an attempt to derive metadata from a sample data. While the sample may be incomprehensive, and there may be several ways to describe the same XML data by DTD, it is still a good start.

So, let’s use “Import By Name” JSONAdapter’s functionality for metadata import.

, which leads to..

While actually creating a Function Call that may be used straight away, the import process also (optionally) generates a DTD file.  That file may be reviewed and adjusted, for example, to denote some XML elements as optional rather than required. “Import By Name” functionality should be then used again, but this time to import metadata from the adjusted DTD file, not a sample URL.

With that second import or without, the final result would be a Function returning data in Nested Relational Data Model (NRDM), the Data Services’ internal representation of XML data (screenshot below is partial):

In the new Function, the Input parameters are always URL and EXTRA_PARAM, which both form the Url submitted to the web-application, but at development time such split provides more clarity regarding constant and variable parts of the request.

The Output is a nested schema that may be processed using standard Data Services tools. Let’s have a closer look at Data Flow design for that.

Function cannot be placed into a Data Flow by itself; it rather should be inserted into a Query transform, which, in turn, should be preceded by a Row_Generation transform, to provide an input. Hence, the Row_Generation transform generates exactly 1 row, and Query transform Twitter_Search calls the JSONAdapter-based Function:

Greater flexibility may be added to the Function call’s input parameters using variables:

These variables may be globals or the data flow’s input parameters, so that is the way to parameterise Function calls from outside of Data Services.

The NRDM data received by Data Services may then be unnested and stored in table format, voi la!

From here, the social media data can be used and reused for analytical processing as part of the data warehouse.

In the next blogs I will outline how to configure JSONAdapter to load data from Facebook and RSS Feeds, and, using collected tweets as a model, discuss text data processing options in Data Services.

• Enhanced scalability & performance

• Flexible and tighter integration with SAP BusinesssObjects products

• Reduced TCO and higher development efficiency

• Simplified configuration and operations

Innogence was one of the first SAP partners embarking on BW 7.3 ramp-up at the end of last year in ANZ region. With the experiences gathered through ramp-up phase, Innogence helped the client upgrade to BW 7.3 in a speedy manner before a major user community go live. The upgrade went very smooth and finished within the planned down-time window. Congratulations to the team!

“Innogence has also reinforced its commitment to SAP to deliver next-generation solutions on the SAP HANA platform based on SAP in-memory computing technology.  This carries on from the success Innogence delivered to the APJ market around BIA/BWA (Business Warehouse Accelerator – in-memory computing technology) over the last 3 years. Innogence deployed around 17 BIA/BWA environments, so it is a natural progression for the Innogence in-memory team” said Ian Markram Director of Innogence. On the initial findings and global ramp up success Ian believes HANA will be another extremely successful SAP product that is a must have for clients.

At the Innogence Innovation centre, clients in the ANZ region will be able to experience how HANA can help them run business and simplify existing IT landscapes. This will help clients recognise the benefits of in-memory computing for their organizations.

If you would like to enquire about the HANA Test Drive offered by Innogence, please E-Mail: enquiries@innogence.com or speak to your account manager.

“The appointment of Phil in this role is arguably the best strategic move we’ve ever made” says Hernus Carelsen. Phil Cameron is qualified as a Chartered Accountant and holds a Masters in Project Management and Human Resource Management from UNSW. He has sold and delivered over $120 million revenue over the last 12 years. During this time he has performed the roles of Sales and Marketing Manager for Deloitte Outsourcing in NZ, SAP Client Partner responsible for Consulting in SAP Queensland, Managing Director and Founder of Zer01 (a SAP GRC Consultancy in Australia) and Global Sales Director for Spendvision (a Global SaaS Expense Management Product).

A NSW based client also attended Innokey’s offices in Hyderabad to work with the existing offshore team; and to get ready for the support phase for their project.

A link to Innogence’s offshore partner can be found at http://innokeysoft.com/

http://www.sap.com/australia/partners/partner_table.epx

For a company that specialises just in Business Intelligence, this cements Innogence’s position as the undisputed BI leader in Australia.