Data visualisation tool + In memory Database in 10 minutes / Docker + Spark + Zeppelin

Hi,

Have you ever wanted to have an ultra fast in-memory database and a real time visualisation tool where you can create charts from your SQL query results?

Now you can! It can all be achieved using open source software and the effort of setting it up is minimal.

In a nutshell:

1. Install Docker (https://www.docker.com)
2. Download a Docker image that contains Spark + Zeppelin
3. Execute the Zeppelin example

Install Docker

If you are in the IT world and you do not know what docker is; I recommend you to find out here.

The docker installation process is very straight forward. If you are using a MAC or Windows 10, I recommend you to install the beta version, which is much more efficient. The beta version requires Windows 10.

Download the Docker image

Once you have docker installed, download an image from: https://github.com/dylanmei/docker-zeppelin

As mentioned on the web page, you only need these two commands:

docker pull dylanmei/zeppelin
docker run --rm --name zeppelin -p 8080:8080 dylanmei/zeppelin

The first command can take from three to ?? minutes depending on your internet connection speed. In my case, it took four minutes.

Execute the Zeppelin example

After executing the second docker command which runs the image, you will see something like this:

This means that Zeppelin  (the visualisation tool) and Spark (the in-memory database) are ready.

Open your browser to this address: http://127.0.0.1:8080. Use Chrome or Firefox.

And you should see something like this:

Click on the Zeppelin Tutorial note. 

Click save; this will cause the notebook to have the needed dependencies.

Then click the "play" icon for the first part or paragraph as it is called in Zeppelin.

As you can see, this will create a table called "bank" (1) from a text file located in (2).

Now you can start to explore the data:

Feel free to execute any of the charts, or alter the SQL statements to start exploring the data you just loaded.

In my next post I will write about using more advanced features.

From IOT trough the cloud into analytics

I still remember the example from several years back telling us what the IOT is going to be about: "Your fridge will be connected to the internet and will order milk and eggs when you are running out". Sounded kind of appealing but, I thing IOT is much more interesting than that.

One nice example of what IOT can achieve is described in this article of Wired magazine.

I myself wanted to experience the topics IOT, Cloud, and analytics combined together, with a budget of 50€, during 5 days, at a rate of 1.5 hours per day.

The result of my experiment is this:

I've got an Onion Omega microcomputer (30 USD) that runs Linux and can be connected to any type of sensor, and connects to a Wi-Fi network. I originally wanted to use a Raspberry PI Zero (5 to 10 USD) but they were in Back Order.

In this case I decided to work with a temperature sensor (5 USD). So, inside the Onion I put a Python program (thanks Maria!) that reads the temperature from the sensor and send it to Microsoft Azure Cloud.

Then I used the newest Microsoft analytics services, called PowerBI to create some charts with the resulting data.

In a Nutshell, any small and cheap micro computer connected to a equally small and cheap sensor, and to a WI-FI network can send its reading to a server in the cloud. Then with any device (PC, Smart phone, tablet) connected to the Internet you can analyse and visualise this information. Great, isn't it?!

Right now I'm in the process of creating a platform which enables anybody to collect and analyse information from sensors (whatever sensor you can imagine) in a cheap and fast way. So, if you have an idea where you can use this, please contact me.

Some pictures:

1. The Onion with an USB stick (A) and the temperature sensor (B). The Onion is on top a pack of cigarettes so you can guess its size.

2. A simple dashboard (running on my iPad) that shows the temperature of the last 30 mins, the current temperature, and the average of the last 5 days. I created a couple of artificial temperature variations by putting some ice near the sensor.

The new world of analytics and BI. From a text file to the iPad in a couple of hours.

The world  has changed in many ways in the last 5 years. It has changed in  analytics and business intelligence too.

A friend of mine is running a web proxy, a program that monitors which web pages are visited and by whom from inside his company. This proxy generates a log file in text format which contains the username, the page visited, the amount of bytes, etc. The file looks like this:

192.168.15.63 user2 [15/Sep/2015:09:10:09 -0400] "GET http://api.new.livestream.com/accounts/8665913/events/4253600/broadcasts/99302610/availability HTTP/1.1" 304 342 TCP_MISS HIER_DIRECT
192.168.15.150 user1 [15/Sep/2015:09:10:16 -0400] "CONNECT ci3.googleusercontent.com:443 HTTP/1.1" 200 5917 TCP_MISS HIER_DIRECT
192.168.15.150 user1 [15/Sep/2015:09:10:16 -0400] "CONNECT ci5.googleusercontent.com:443 HTTP/1.1" 200 11657 TCP_MISS HIER_DIRECT
192.168.15.150 user1 [15/Sep/2015:09:10:16 -0400] "CONNECT ci6.googleusercontent.com:443 HTTP/1.1" 200 17674 TCP_MISS HIER_DIRECT
192.168.15.63 user2 [15/Sep/2015:09:10:25 -0400] "POST http://livestream.com/analytics/api/track HTTP/1.1" 204 484 TCP_MISS HIER_DIRECT

Based on the contents of the log file, my friend wants to know which are the top 10 internet users, the top 10 visited sites, or the peak hours of daily internet use. And he asks me to process the file and get this information for him. There are many ways to achieve it, but the one I tested is the following:

I loaded the log file to a Hadoop-based platform (Microsoft Azure HDinsight) and gave it a queryable structure. For achieving this I had to write only two lines of  code.

Now I have the file loaded and I want to give my friend a familiar tool to query the logged data. It could be Excel (there are ODBC drivers for Excel to connect to HDinsight), but why not something more mobile? For example Microsoft PowerBI, it has has the ability to connect to HDInsight and you can run PowerBI in your iPad.

Now, I have a log file from a web proxy loaded into a Hadoop-based tool, and my friend can query its contents from his iPad, amazing isn't it? 

I used technologies like cloud and big data for a trivial task like this, and I did it without installing any tools locally. My point here is: The way we process and make information available has changed a lot in the last 5 years, and we (IT professionals) have to adapt to those changes, and more importantly, we must take advantage of them!

After four decades SAP ERP (now S/4 HANA) is reborn! Literally!

A lot of the biggest companies in the world have been running SAP for decades, maybe not with incredible performance but always with an "acceptable" performance.

What was the secret behind this "acceptable" performance? It was redundancy; basically you bend database normalisation rules and store some aggregated data for the sake of usability and performance.

Imagine if SAP did not use the redundant data (totals tables). When checking the balance of a ledger account you would need to wait (for minutes) because the system would need to read all related transactions first; thanks to the totals tables it only needs to read one record. But the drawback is that every time you save a new transaction, the system needs to update multiple totals tables.

The point here is: If you need multiple views of your data like by account, by vendor, by country, etc. you can create more and more of those totals tables, but then, every time you save a transaction the system will update all the totals tables you've created leading to slower transactions.

To further address the performance issue, satellite systems were born. Like BW (Business warehouse) that stores an aggregated copy of each transaction that happens in the main system. This way you can make ad-hoc reports on the duplicated data with an "acceptable" response time. Other satellite systems are: CRM, APO, etc.

Both, the use of totals tables and the introduction of satellite systems, successfully ensure performance by replicating main system transactions in multiple locations. This inevitably creates databases that grow huge and become hard to maintain in sync.

However, with the economic globalisation and phenomenas like the internet of things, too many transactions are being generated and systems need to keep up the pace. It seems that saving and updating the same data in multiple locations is not feasible anymore, or at least not optimal.

Eight years ago at the HPI, one of the SAP board members started a revolution, he said "Let's redesign enterprise systems with the following premise: A database that has 0 seconds response time". A new vision and SAP HANA were born.

On 3.Feb.2015 SAP S/4 HANA was launched as the result of that vision from eight years ago.  This new version of the SAP ERP and it's satellite systems don't need any of the old tricks for performance, no more totals tables, no more saving the same transactions in multiple locations. Everything is SIMPLE, that's what the S in the name stands for, and the 4 is because it's a 4th generation system.

This was an incredible task to achieve, since SAP had to rewrite almost all of its 400 millions of lines of code. As a result,  for example a system that has  593 GB database  can now fit in only 8GB, yes the storage space you probably have in your mobile device.

Are you ready for a new world of enterprise software?

If you are interested in more details watch the launch event video here.

Or read this https://blogs.saphana.com/2015/01/14/simple-finance-removes-redundancy-case-materialized-aggregates/.

Hot start for BI tools in 2015! Microsoft PowerBI and SAP Lumira Edge are here!

Just when the first month of 2015 was about to end, two big BI news appeared on the Internet:

Microsoft PowerBI and SAP Lumira EDGE are now available; let me explain why this is big news for me.

I've been working with BI (for the last 11+ years) specifically with SAP BW / BO. I think that for corporations SAP BW / BO is a spectacular tool, but what about small companies, or small units inside corporations?

In my opinion there was no All-in-one (ETL, Presentation, authorisations, scheduling, etc.) BI tool for small companies. This is no longer the case after the release of PowerBI and the use of Office 365. Basically you can use it to:

Extract data from:
- Sources in the cloud (SalesForce, Access Apps, etc.).
- Sources in your office (SQL server databases, Excel/Text files, etc.)

Create nice static printer friendly reports or stunning Dashboards and publish them on your Office 365 Portal. Also you can schedule them to be refreshed automatically. Then anyone with access can use these reports from a PC or from a tablet.

In less than 4 hours I managed to do the following: Install a piece of software that allowed PowerBI to connect to an SQL database in my PC enabling an automatic refresh of the BI model from my local data. Publish two reports to the Office 365 portal that can be accessed from a PC or a tablet: Wow!

Easy and powerful BI is available now for small companies. Off course there are still some rough edges since it is so new, for example the error messages that apear when using other languages than English are totally cryptical, or the screenshots from the help do not match the actual screens.

Inside corporations there are highly specialized teams that could really benefit from an In-Memoy BI solution like Lumira EDGE, which is easy to install a deploy. I've not tested it yet, but it looks promising.

Now January 2015 is gone, but we have two new interesting options for BI.

Data warehouse, this is Big Data: Hauska tutustua!

In case you are wondering "Hauska tutustua" means nice to meet you. Although I've been living in Finland for 3 years, I have to admit that my Finnish language skills are almost 0, but since day 3 here I knew the expression.

Going back to the topic:

One of my favourite definitions of a data warehouse is the one from Bill Inmon:
"A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process."

One great definition for Big Data is this one from IBM:
"Every day, we create 2.5 quintillion bytes of data — so much that 90% of the data in the world today has been created in the last two years alone. This data comes from everywhere: sensors used to gather climate information, posts to social media sites, digital pictures and videos, purchase transaction records, and cell phone GPS signals to name a few. This data is big data."

I can imagine some uses of pure Big Data like:

Security: Intelligent algorithms crawling over millions of logs from the devices in our networks (routers, firewall, etc.) trying to detect anomalies (possible hacking attempts), and alerting the digital security officers.

On line patterns: Analyse every aspect of the customers, where they click, how much time they spend watching a specific product before they click buy it, etc. 

And many others... 

But what about a relation between Big Data and the data warehouse: Should it exist? or should big data replace the data ware house?

My answers are yes (for the relation) and no (for replacing it).

The yes comes from personal ideas like this:
Big data can preprocess tons of data, and at the end provide simple KPIs that can be loaded into the subject-oriented data warehouse.
Imagine a sales warehouse where we have data like: what have been sold, to whom, for which amount, etc. We can easily add to the data warehouse a new KPI, like number of positive and negative reviews in the social media for those products.

In this table we have the yellow coloured KPIs coming from our transactional sales system loaded into our data warehouse, and the green coloured ones were first processed by our big data solution, and then the results were also loaded into the data ware house.

Lets put some numbers, from the transactional system we loaded 100.000 transactions for the yellow columns, and for the green column big data processed 10.000.000 posts from Facebook and Twitter about the products in different parts of the world, and provided us with 4 records that are then loaded into the data warehouse.

Since companies have invested a lot of time in building and connecting their data warehouses to all their transactional systems, replacing them with new systems powered by big data is not a trivial task; at least for some years, I think both technologies will co-exist and need to be integrated.

Have a great Sunday !

Watch out SAP HANA! IBM BLU is here; now with support for DSOs PSAs, and Characteristics!

Update 22-Nov-2014: The post was removed from SDN by the moderators.

Update 7-Oct-2014: Follow the discussion on SDN:
Watch out SAP HANA! IBM BLU is here; now with support for DSOs PSAs, and Characteristics!


On December 2013, IBM and SAP announced that you can use IBM BLU acceleration for SAP BW Infocubes. This was trough the following SAP note:  1889656 - DB6: Mandatory SAP NW BW corrections for BLU Acceleration dated 04.12.2013.

On September 2014 they announced that you can use IBM BLU with SAP BW: Characteristics (Master Data), DSOs and PSAs thanks to the DB2 Cancún release. This was announced via SAP note: 1997314 - DB6: Enablement of BLU Acceleration for PSA, DSOs, and Characteristics InfoObjects date 29.09.2014.

I know that SAP HANA and IBM BLU are not directly comparable, but they share some interesting things like in-memory and columnar storage.

I´ve experimented with BLU for SAP BW only for Infocubes and you can notice the difference straight away.

You can find advantages and disadvantages in both approaches (HANA and BLU). In these times were economy is not on its best, I think IBM BLU is a really interesting option, specially since you can go live gradually, meaning one cube at the time and maybe using the same hardware or just upgrading it a little.

What are your thoughts?


Interested in BLU ? Reading this material from IBM is a great start: http://www.redbooks.ibm.com/redbooks/pdfs/sg248212.pdf (chapter 6)