SAP HANA Information Composer V 1.0 - External Data Upload

Introduction.
This document will walk you through the process of creation of tables and uploading data into HANA database without writing single SQL statement. This tool helps us to create physical table in HANA database in accordance to the sample data set we provide. It removes all the technicalities of modeling and provides an easy to use simple modeling environment with limited functionalities. Hence it bridges the gap between backend system and business users.
It allows Business users to gather their own data, upload it, and combine/compare it with corporate sources. They need not to understand various modeling technicalities, the tool itself is intelligent enough to suggest various joins/Unions etc once it analyses the data.
Hence SAP HANA (SP3) delivers the first version of “Self service data warehousing” solution.
What is Information Composer?
Information Composer V 1.0 is a self-service BI data warehousing tool, it is a modeling environment for non technical people like end users. User needs not to be a computer expert to analyze the data set.
You can import external data in workbook format (.xls, .xlsx or.csv) into the SAP HANA database and use it to create Information Views for analysis.
It provides a centralize secure environment to the highly trusted corporate BI data. Separate roles are assigned for Information composer users to maintain highly secure access.

Note: It is different from the HANA modeler. Both of them are targeting two different sets of users. HANA modeler is a more powerful tool targeting users with sound technical knowledge and hence offers extensive functionalities. Whereas Information Composer is intended for Business users with less technical knowledge. It provides simple set of functionalities along with user friendly interface.

Scenario example
Uploading of local data is needed in situations where business users needed to combine data from local system and already loaded BI data. Requirements like Profitability reports where we need to take data from local system from time to time for analysis of when the business user needed to upload day today customer comments to the backend system.

Functionality.

1. Data extraction: It helps to extract data, cleanse, preview data and automate the process of creation of physical table in the HANA database.
2. Manipulating data: It helps us to combine two HANA objects (Physical tables, Analytical View, attribute view and calculation views), allows us to add calculation views (select them as attribute or measure) and create information view that can be consumed by SAP BusinessObjects Tools like SAP Businessobjects Analysis, SAP BusinessObjects Explorer and commonly used tools like MS Excel.
3. Provide a centralize IT service in the form of URL, that can be accessed from anywhere.

Note: In HANA terms “Information Views” can be of type Attribute View, Analytic View, and Calculation View. The Information View that SAP HANA Information Composer creates are of type Calculation View.

System landscape

Showing where the Information Composer is integrated in the HANA landscape (new inclusion)  - Source is SAP.

Uploading data

Large amount of data (up to 5 millions of cell) can uploaded using Information Composer.

Below is the address for accessing information Composer:

http://<server>:<port>/IC  

• http://<server):8080/IC (in case of HANA Sandbox On cloud it is http://localhost:8080/IC)
• In case of secured access it is https://<server>:8443/IC

First step is to logon to the Information Composer system.

Note: If one is using the HANA sandbox Personal Image hosted on cloud, one must need to let that process “Start Information Composer” runs for 2-3 minutes (it needs to start and initialize all the Java processes).After 2-3 minutes, you can click on the IE Shortcut for “Information Composer” on the desktop, and it will open up your IC login page (as shown above).

1. One can perform external data load or data manipulations with this tool (as shown below, for this article we will only consider the UPLOAD)

2. One can load data from xls, .xlsx .csv or data from clipboard (that is copied earlier).

- > One can even select sheet number from a workbook.

- > Data can be loaded along with the header

 Note:  As soon as the data is uploaded or the source is selected the data can be saved as a draft. The same can be viewed under table folder in IC_TABLES schema with : “ic/<userid> /saved (or temp )/<alphanumeric caracters>”. Saved will only come if it is saved during the course of uploading the external data. Once the data is published the additional entries will disappear with “ic/<name of the table>” Table related metadata will be stored under “SAP_IC” schema, where tables like IC_MODELS, IC_SPREADSHEETS.

One can find details of tables created using IC under these tables against created by column (in our case there is an entry for S0008812557 and the status is appearing as SAVED)

Metadata from these system tables are taken for “My Data” section (explained later).

Once the data is published, one cannot rename the table. In that case one need to delete the table. This activity cannot be carried out from IC. For deletion on need to login to the HANA Modeler and write a simple deletion code.

Note: don’t forget to select the correct schema.

Snapshot of IC_SPREADSHEETS table under SAP_IC schema.

The other way of uploading the data into HANA system is to copy a set of data into the clipboard and then upload that with the help of IC*.

*IC stands for Information Composer. One can choose header part along with the data set. In that case the “Upload with header” is needed to be checked.

3. Once the data is uploaded (in temporary storage), it will display a preview of the data set uploaded. Spyglass on the right hand side will help to search a particular entry.

Note: By default the data is stored in columnar format under schema IC_TABLES where table name starts with “ic/”

Below is a button to have a summarized view. That gives an idea of the variety of that available, where the blue color gives an idea of repetition of a particular entry.

.

4. Some time the data set may contain inconsistent data (like in our case country may appear as India or india or INDIA) but all of them have the same meaning. So this tool offers an additional step for Cleansing of data (optional step).

The tool intelligent enough to finds out the variation in the data part and finds the probability of that they might be same. It offers manual correction or one can drag and drop the differences in to a single stack as shown.

If we move our cursor on top of any field, it will display the property of that column. One can change the data source any time during the upload process with the help of “Change Source” link.

5. Next step is to classify the data, whether it is a measure or an attribute (measures can be used for mathematical calculations). The tool itself shows all the fields that can be considered as a measure. One needs to select them manually by selecting the respective checkbox.

Note:  A separate index field (which is also the primary key) is created by default for every physical table created with the help of IC

6.  Here comes the most important step: Publication or the final step of creation and loading of physical table into HANA database. Provide a technical name and description (make sure no other table with the same name is there inside the IC_TABLES schema).

Two sets of roles can be assigned to users who will use Information Composer. IC_MODELER is for creating physical tables, uploading data and creating information views. The other one is the IC_PUBLIC, this role allows users to view information views created by other users. This role doesn’t allow user to upload of create any information views using IC. (More details are there at the end of this article)

Once the “Share data with other users” is selected users having IC_PUBLIC role can view the uploaded data set/ physical table.

“Start a new information View based on this data” will automatically jump to the next modeling screen where present data set will act as the first data source.

Once the “Publish and Finish” is clicked a physical table is created at the HANA database. If everything goes well, a success message will be displayed (as below).

A quick access launch bar can be viewed on the left side of the IC. Displaying all the recently created and publicly available tables. It takes information from IC_SPREADSHEETS and IC_MODELS.

 Various symbols are displayed alongside the tables, explained as below:   

Icon helps us to filter out "Draft user data", "Private user data", or "Public user data". 21.JPG

Icon to rename, delete, or share the data (data cannot be renamed once it is published)   

this symbol shows that dataset is already published.

This symbol tells that it is shared with other users (having IC_PUBLIC role at least).

System Requirements - Source is SAP

Server Requirements:

• At least 2GB of available RAM is required
• Java 6 (64-bit) must be installed on the server
• The Information Composer Server must be physically located next to the HANA server

Client Requirements:

• Internet Explorer with Silverlight 4 installed

User Roles - Source is SAP

Two sets of user roles exist for Information Composer. Assign these roles to users who will use the Information Composer:

• IC_MODELER Allows users to work with Information Composer and upload data, create Information Views and so on. If you only have the IC_MODELER role assigned to your user, then you can only create and use private Information Views (that is, they cannot be seen by other users). The IC_MODELER role includes the IC_PUBLIC role. This means that users that are granted with this role already include the IC_PUBLIC role
• IC_PUBLIC Allows users to work with Information Composer and to see workbooks and Information Views that were uploaded or created by other users. For example, if you choose to share your data or Information views with other users, then anyone with the IC_PUBLIC role can see this data.

Source: scn.sap.com