r.konijnenburg

7 Posts

I got a very interesting comment on one of my Blogs: “can you send me the files you use? I also want to load 700 million records in my HANA sandbox”. This led me to believe I need to do some explanation on how you get many test records without actually loading huge files!

 

Now I have to be honest, I did not come up with this trick myself. Hans Bouwers, one of the HANA specialist in our team guided me towards this awesome trick. It will generate your much needed “Big Data” in a flash.

 

So “show me the records Ronald”!

 

Step 1 - Create some temporary tables and populate them with data

 

Let’s say my main table “SALES” has the following structure:

 

MONTH

SALESREP

CUSTOMER

SALES

TARGET

January

B. Gates

Apple

120000

122222

....

....

.....

.....

.....

 

If I would want to populate the above with 120 million records I do not have to load enormous files to do so, but I would just create some temporary tables and a get little help from my friend SQL.

 

Create 3 extra temporary tables (they mirror your original table, if you need some help on creating tables and loading data, just follow one of my SAP HANA Developer Access Beta program - get the best out of your test-drive!):

 

Table “SALES1” (total 12 records):

 

MONTH

January

February

.....

 

 

Table “SALES2” (total 1000 records):

 

SALESREP

B. Gates

S. Ballmer

.....

 

Table “SALES3” (total 10.000 records):

 

CUSTOMER

SALES

TARGET

Apple

120000

122222

Pixar

122111

122221

....

....

....

 

 

12 x 1000 x 10.000 = 120.000.000 records. The above thee small tables can give us 120 million records with a little help from SQL!

 

 SQL your way into “Big Data”

 

Go into the HANA studio, open up a SQL window and execute the following command:

 

insert into SALES

select 

  SALES1.MONTH,

  SALES2.SALESREP,

  SALES3.CUSTOMER,

  SALES3.SALES,

  SALES3.TARGET,

  from SALES1, SALES2, SALES3

 

Wait for a couple of minutes and be amazed. For every unique combination of your fields a record is generated. How is that for “Big data”!

Be nice

Now I can imagine that we do not want to stress the above to the limit. No need to bring our fantastic HANA experts Blag and Juergen into trouble by blowing up the database ;-).

 

Thanks for reading this blog and stay in touch!

 

Ronald.

Ronald Konijnenburg

Siri meets HANA

Posted by Ronald Konijnenburg Jan 22, 2012

So a cold day in December you are surfing the net and you get triggered by the famous SAP HANA Kinect demo: “wouldn’t it be great to use voice instead of gestures and use Siri on the iPhone to ask Business Intelligence like questions?” Imagine a scenario where a user would ask a simple question like  “Give me total sales for Customer BMW” and Siri would give the answer.  Imagine that...

So what happened next? I decided not to snuggle up to the cat, poor myself a glass of egg nog or finish my Christmas shopping. I decided to make it happen and so a journey full of challenges started.


Siri Proxy
I came across this fantastic piece of software called Siri Proxy. According to the authors:

Siri Proxy is a proxy server for Apple's Siri "assistant." The idea is to allow for the creation of custom handlers for different actions. This can allow developers to easily add functionality to Siri.

I installed Siri Proxy on a virtual Ubuntu machine and within a couple of hours I had Siri Proxy running and it was taking commands other than the ones Apple put in. I installed some community written Plugins and got really enthusiastic about the technique. I picked up a Ruby book, deep dived into the basics and started writing my own Plugin.

 

SQL Plugin
I decided my Plugin should be able to execute SQL statements and query any database. This would make it possible to connect to virtually any database and one would be able to use it in a broad and agnostic way.

 

The first test
As a Proof of concept I installed a random database (MySQL) on an Ubuntu environment. I created a simple table containing the key figure “Sales” and objects “Sales rep, Customer and Date” and loaded some random data to my table. Within two week (and some sleepless nights) the Plugin worked beautifully. It took my commands and fired off the SQL with lighting speed.

 

What if we can do this on HANA?

“Can I do this on HANA?”. Like my father always told me: “impossible is not a fact, it’s an opinion”. So I took up the challenge again. I contacted my colleague Rob Beeren and we drew up the architecture. Execution and the headaches started.

 

So what challenges can one find connecting Siri to HANA?

If you look at a solution after you build it, it always looks easy. But honestly: connecting an iPhone on 3G to a Cloud server with closed ports is not easy. Thank goodness the guys over at SAP Labs persuaded Cloudshare to open some ports. After this, we managed to VPN into the Cloud and thus have an integrated scenario. Next HANA ODBC drivers on Linux were added, the required  ruby Gems for ODBC were implemented and finally we pressed the Home button on the iPhone. What happened next, you can find below:

 

Siri meets HANA

 

See you at the Innojam! And ofcourse I will release full code and instructions after the event!

 

Stay in touch,

 

Ronald. 


As long as I've been in BI I heard two things:

 

  1. Spreadsheets are bad
  2. The holy grail of reporting is self-service BI

 

So why is it that these two topics always come up in discussions and how are they related?

Spreadsheets are bad

Well this one is easy right? We need to have a single version of the truth in our company and having different spreadsheets with different truths doesn't help. Now at lower management one might still get away with having some figures which don't totally match your neighbors figures at the meeting table, but it becomes a different ballgame when we need 100% correct figures for decisionmaking. Think of that customer we should or should not let go based on the margin information in our data warehouse. Now the misperception often is that these companies do not have a sophisticated data warehouse, but just some massive collection of files, scatttered all over the place. The truth is often different. These companies do have a sophisticated data warehouse and they do have a single version of the truth. What they often lack however is the power of flexibility (call it agility if you want) and the power to fulfill the ever growing demand of new information needs. Put in that people still believe that reporting should always look spot on and we have a challenge of keeping up with demand. IT with all best intentions simply cannot cope with the ever changing request for new insights. Those companies that come to the conclusion that it does not always matters how the information physically looks, but that the content and speed is more important, have a case to change for the better. Here self-service BI enters the equation.

Self-service BI

So you have a multi million Euro data warehouse, a mature IT department and all the change request procedures in place to keep the stability you invested in, but you lack speed. Speed of execution that is, speed of data is handled by SAP HANA Developer Access Beta program - get the best out of your test-drive! nowadays so one less concern. Speed of execution is what is needed to keep up with increased demands on time to information (TTI). I don't have to explain why it is important to have this high on the agenda in these troublesome times. So how do we cut back on TTI? We give control to our end-users. BI less controlled by IT, et voila, we have gained speed. Well, on paper that is. The reality is different. Not only do companies get trapped in new layers of complexity by self-service reports which do not match the raw figures, they often also get hundreds of new reports which cannot be supported. Not by IT and not by the end-users. So is there a way out of this? There sure is. The 7 virtues of self-service BI.

The 7 virtues of self-service BI

  1. Its number 1 for a reason: one size does not fit all. Only give full control to users who fully understand the figures, who are tech savvy enough and really require full control. Getting rights to filter and create your own calculated measures is not for everyone in the company. Going from spreadsheet misery to the self-service equivalent is a very small step. People who do not need full control can still create there own ad-hoc reports, but are restricted to only use predefined characteristics and key figures.
  2. Self-service BI does not mean there is no standardized and harmonized reporting. Reports which are required in regular weekly/monthly meetings and have the same data scope should be fixed. If decisions are based on them they should be made out of stone.
  3. Control your flood of reports. Measure how often reports are used. If not run often, archive them. Full stop, no discussion.
  4. Check the type of analysis done. If a certain data slice or report is often requested and no standard report is available, it’s time to create one.
  5. Promote self-service BI reports to standard report when often used and shared. Statistics help to determine this.
  6. Use great, easy to use, fun tools, but don't promise they'll solve all problems and fulfill all requirements. BusinessObjects Web Intelligence for sure is a great tool, but be clear on where the power of it is. Easy to use and the ability to create Mobile content in a flash is an absolute winner. That said, Mobile BI - quickly create some eye-candy is often a great way to increase usage of reports. Reporting on an iPad remains magical.
  7. Self-service BI does not mean we can cut out the IT department. Supporting the end-user will still take a fair amount of time and let’s not forget where the focus of IT should be: creating that trustful data set and to keep it available at all times.

Thanks for reading this blog, live by your virtues and stay in touch!

Ronald. 

Summary

So you receive an invitation to the SAP HANA Developer Access Beta program, but then what? Information on how to use HANA is floating around the net, but as always, you need to know where to look. I did all the research for you and made some concrete examples on how to fully use your opportunity to test-drive HANA. The result is written in the following document: 7 steps towards your first HANA report:

SAP HANA Developer Access Beta program - 7 steps towards your first HANA report

 

For those of you not aware what the SAP HANA Developer Access Beta program is, there is an excellent FAQ on SDN which you can find here.

FAQ SAP HANA Developer Access (BETA) - HANA Sandbox System

 

Have fun and stay in touch!

A couple of days ago I was Lucky enough to receive an invitation to the SAP HANA Developer Access Beta program. For those of you not aware what that is, there is an excellent FAQ on SDN which you can find here:

http://wiki.sdn.sap.com/wiki/pages/viewpage.action?bc=true&pageId=257820641

The following paragraphs will describe 7 steps towards your first HANA reporting by using SAP HANA Sandbox.

 

Step 1 – Cloudshare

In your invitation mail there is a link to cloudshare.com. Create a new account, log on to the desktop and follow the following YouTube video on how to set up your own HANA system:

http://www.youtube.com/watch?v=9NKSYgt5eX4

A great tip is given on how to use remote desktop instead of using the web version which comes with Cloudshare. This is highly recommended as navigation is much easier in the remote desktop.

Logon details for your RDP session are given on the Cloudshare page each time you activate the environment:

 

Step 2 – Getting big data

So you managed to set up a system and created a RDP session. Good job!  Now to find some “big data”. In this article I’m using data which I downloaded from the guys over at Infochimps. I’ve used the following data sources which in total will give me about 20 million records.

  • AMEX Exchange  Daily 1970-2010 Open, Close, High, Low  and Volume
  • NASDAQ  Exchange  Daily 1970-2010 Open, Close, High, Low  and Volume
  • NYSE  Exchange  Daily 1970-2010 Open, Close, High, Low  and Volume

The files contain stock exchange data. Great recipe for finding something interesting.

You will be getting a bunch of separate csv files. Use the daily prices ones. For simplicity sake I have merged all files into three separate csv files. Good old DOS can help you with that by using the following command:

copy *.csv importfile.csv

 Make sure to execute the command in the same directory your files are placed. Replace importfile.csv with something recognisable (like AMEX.csv, NASDAQ.csv, NYSE.csv).

 

Step 3 – Create your table in HANA

And now the fun begins! You need to create a table which holds all your data records. Remember those files I downloaded from Infochimps? The files have the following structure:

  

That means I need to replicate that structure into my HANA table. You can create your table in the HANA studio using the modeler or by using SQL.

 

Modeler:

 

Please note that you should create the table in your own schema and use Column Store (to witness awesome speed later on).

 

SQL:

I prefer SQL because it’s faster. The following command will create your table:

 

create column table "S0001432066"."NASDAQ"(

      "EXCHANGE" VARCHAR (10) not null,

      "STOCK" VARCHAR (10) not null,

      "DATE" DATE not null,

      "PRICEOPEN" DECIMAL (15,2),

      "PRICEHIGH" DECIMAL (15,2),

      "PRICELOW" DECIMAL (15,2),

      "PRICECLOSED" DECIMAL (15,2),

      "STOCKVOLUME" DECIMAL (15),

      "PRICECLOSEDADJ" DECIMAL (15,2),

primary key ("EXCHANGE","STOCK","DATE")) 


Step 4 – FTP and import your files into the HANA system

The guys over at SAP will make you do a little treasure hunt in order to find the user id and password for the FTP server. Go into you HANA system and execute the following SQL statement: 

Select * from SYSTEM.FTP_SERVER

 

Et voila, a username and password (masqued for obvious reasons):

Take note of what is mentioned on where to store the files. More specifically you should create a folder on the server equal to your SCN number (in my case S0001432066).

Fire off your favourite FTP client (mine is FileZilla):

 

Create a directory and store your files:

 

Take note that next to my files containing the data there is a so called “ctl” file. These files are required in order to be able to load data in your created (NASAQ) table. The files have the following content:

 

Import data

into table S0001432066."NASDAQ"

from 'AMEX.csv'

record delimited by ' '

fields delimited by ','

optionally enclosed by '"'

error log 'Text_Tables.err

 

NASADAQ is the name of my created table, AMEX.csv the file I will load.

 

If required, additional information can be found in this post:

How to load CSV files into HANA

 

Time to import your 20 million something records into HANA! Execute the following SQL statement:

 

IMPORT FROM '/dropbox/S0001432066/AMEX.ctl'


Note the name of the folder I created in step 4 (folder S0001432066), /dropbox/ is a prefix. After a while you will get the following result back:

 

Statement 'IMPORT FROM '/dropbox/S0001432066/AMEX.ctl'' successfully executed in 7:22.046 minutes  - Rows Affected: 0


Hang on I hear you thinking. 0 Rows? No it’s not actually. You can check by firing off the following SQL statement: 


select count(*) from NASDAQ

 

 

That looks promising! Let’s check some more:

We have data! Look at the log file:

Fetched 30 row(s) in 15 ms

Wow!

 

Step 5 – Create an Analytic view for reporting

First step create a package which will hold your view:

  

Create a so-called Analytic View:

Give it a name:

Select your table:

Drag your objects into the attributes and measures sections:

Validate, save and activate your view. Well done! We can use this view in Explorer and Excel. Note that you can preview your data and even auto document if by using these buttons:

Important! In case preview fails it is likely you have to “grant” your schema by executing this statement:

 

grant select on schema s0001432066 to _SYS_REPO with grant option

Replace s0001432066 with your own namespace ofcourse.

 

As an extra step your could create an extra table which holds the stock names. If you follow the same procedure as for creating the table with the stock records, you can join your two tables and have texts together with the stock names. Sequence for this would be:

  1. Create and load your text table
  2. Create an attribute view
  3. Link your analytic view together with your attribute view

Result would be:

 

Preview:

Step 6 – Using Explorer to report on your data

On your desktop a shortcut to Explorer can be found:

Fire it off and be sure to enter the correct server which can be found in your invitation mail:

Go to manage spaces :

Select your view and press “New”:

Give it a name:

Put in your objects:

And press ok!  Don't forget to index your data.

Refresh your page and you have a new information space:

Press it and start analysing! This one is for you Steve. Apple stock prices in the year he had to leave Apple:

Be sure to select enough data to get a nice trend.

 

Step 7 – Using Excel to report on your data

There is also a possibility to use Excel pivot tables for your reporting needs.  Fire off Excel and connect to your view:

Choose Other/advanced:

Select the MDX connection:

Enter server credentials (check your invitation mail if not known):

Select your view:

You now have your data in a pivot table format. Set some filters and analyse your data at great speed!

Note that data retrieval is at great speed, but Excel makes it a bit sluggish in a RDP session.

 

Many thanks for bearing with me till the end and good luck with this great opportunity to test drive HANA!

I must say that I had different plans for this blog. I wanted to show you how the new Mobile BI OnDemand solution would work, but unfortunately I did not notice the following after uploading my Web Intelligence document to the web:

 

You can use your Web Intelligence XI 3.1 Rich Client

  

Now I have a tendency of being a little stubborn, so I tested with a Web Intelligence document created in 4.0:

Ok, clear. BOBJ 4.0 is not supported yet.

 

So how do I help my my customers by creating some mobile content for them in an Agile way? I use what I have available:

  1. A BOBJ 4.0 system
  2. A windows 7 laptop with a Lan connection

Set up the BOBJ environment

In order to use Mobile BI I have some work to do on the server, but this is relatively simple compared to previous versions of BOBJ mobile. Just follow these steps:

 

  1. Stop Tomcat Web application server.
  2. Copy the file, mobilebiservice.war from the SAP BusinessObject Enterprise to [Tomcat home directory]\webapps: For SAP BusinessObjects Enterprise 4.0 SP02: [Install directory]\Mobile 14\Client12.0\java\applications
  3. Start Tomcat.

 

After this create a Web Intelligence document and be sure to assign it to category "Mobile". This is a default category, create it if not available. After this download the Mobile BI app from the Appstore on your iPad and install it. Nice, we are almost there!

Connect to the iPad

So how do I connect the iPad to BOBJ without having the full infrastructure in place including Reverses proxies and what not, simple, create a virtual connection as supported in Windows 7. A great way of doing this is using software like "Connectify" or "Virtual Router". Google it and install. This will create a hotspot out of your Lan connection so that you can connect the iPad to it.

 

After installing and running Connectify, make the connection on your iPad. As an example my setup:

 

And the result some great looking content in a flash (without flash that is ;-).

 

Thanks for reading this blog and stay in touch!

Ronald.

Introduction

 

 

So what do
you when your client asks you to activate the customer hierarchy in reporting?
Simple your say, you deep dive into ECC, look for the 0CUSTOMER hierarchy DataSource
and activate. You do a quick RSA3 check and receive....no data.

 

Your day just
became a little more interesting. After some
investigation you find out your client did not implement “Classification” and therefore has no hierarchy on 0CUSTOMER. They do however have a hierarchy on the logistical view of the customer. As 0CUSTOMER is
part of your data model you decide not to introduce 0CUST_SALES, but instead
load it to your 0CUSTOMER object.



 

So how do you
load a hierarchy from the 0CUST_SALES to your 0CUSTOMER object? SDN supplies
the answer:



The new SAP NetWeaver BW 7.30 hierarchy framework



 

Well party.
I have to load from an object with multiple compounds (the logistical view
ones) to an object without compounds. That means I have to do some more steps
to make this happen.

h2. Migrating
your DataSource


In order
use the new hierarchy framework you need to migrate your DataSource to a 7.x
version. Before migrating them, you must first implement note 1313068 in you
ECC system as this will enable PSA usage.

 

Compounded

objects

 

 

Looking at
your hierarchy on 0CUST_SALES you can see the compounded objects in the nodes.

!http://dl.dropbox.com/u/11677676/1.png|alt=|src=http://dl.dropbox.com/u/11677676/1.png!

 

The node “000010002000000120” is the compounded key.
Representing Division “00”, Distribution Channel “00”, Sales Organization
“1000” and Customer “2000000027”. As our
0CUSTOMER object does not have all these compounds, you will have to remove
them in your transformation.* *

 

Transformation

 

 

IF <source_fields_3>-h_nodeid EQ 1.



 

<source_fields_3-h_iobjnm = ‘0H_HIERNODE’.



 

ELSE.



 

<source_fields_3-h_iobjnm = ‘0CUSTOMER’.



 

ENDIF.



 

ENDLOOP.

 

<br />





Note that
NodeID “1” is always the top node and must be set to 0H_HIERNODE.

 





Make
following mapping in the hierarchy header:



OBJECTID to
OBJECTID



HIENM to 0H_HIENM



 



And in the header texts do a simple 1:1 mapping
from source to target fields.

 

In the hierarchy nodes there is a little more
logic required:



!http://dl.dropbox.com/u/11677676/2.png|alt=|src=http://dl.dropbox.com/u/11677676/2.png!





As you can see in the above we need to create a
small routine to get rid of the compounded objects. We can do that in this
example by taking the last 10 positions:

 





 

+RESULT

= SOURCE_FIELDS_3-nodename8 (10).

 

<br />

Finally the node texts again is a simple straight
forward 1:1 mapping.

 

 

 

Result

!http://dl.dropbox.com/u/11677676/3.png|height=161|alt=|width=497|src=http://dl.dropbox.com/u/11677676/3.png!

And success! We have successfully loaded the
0CUST_SALES hierarchy to our 0CUSTOMER object.

 

 

 

Some

caution

 

 

 

 

Ofcourse compounded objects are there for a reason.
In my example my hierarchy was unique per sales organisation and therefore I
did not run into problems with different mappings per sales organisation. When
this is different be sure to filter on only those objects which hold a unique
mapping.

 





The examples above can ofcourse be used with
different objects. Be sure however to keep the limitations in the back of your
head.

 

Actions