SAP HANA Tutorial, Material and Certification Guide

In this session, we will see how to build a simple attribute view using data stored on SAP HANA.

By the end of this, you will have created tables and attribute views in SAP HANA.

Prerequisite:

You have SAP HANA Studio installed on your machine (Section 3.2)
You have added an SAP HANA system in HANA Studio (Section 3.3)

Create new tables in SAP HANA and fill them with data:

Open HANA Studio and expand the SAP HANA system.
Go to your schema. Right-click on your schema and select SQL editor.

Note: In this example schema name is “SAP_HANA_TUTORIAL”. In case you want to create a new schema use below query.

create schema <schema_name>;

Copy and paste the below script in SQL editor and execute.

—-REPLACE <YOUR SCHEMA> WITH YOUR SCHEMA NAME

—- Create Product table

create column table “<YOUR SCHEMA>”.”PRODUCT”(

“PRODUCT_ID” INTEGER,

“PRODUCT_NAME” VARCHAR (100),

primary key (“PRODUCT_ID”)

);

insert into “<YOUR SCHEMA>”.”PRODUCT” values(1,’Shirts’);

insert into “<YOUR SCHEMA>”.”PRODUCT” values(2,’Jackets’);

insert into “<YOUR SCHEMA>”.”PRODUCT” values(3,’Trousers’);

insert into “<YOUR SCHEMA>”.”PRODUCT” values(4,’Coats’);

insert into “<YOUR SCHEMA>”.”PRODUCT” values(5,’Purse’);

– Create Region table

create column table “<YOUR SCHEMA>”.”REGION”(

“REGION_ID” INTEGER,

“REGION_NAME” VARCHAR (100),

“SUB_REGION_NAME” VARCHAR (100),

PRIMARY KEY (“REGION_ID”)

);

insert into “<YOUR SCHEMA>”.”REGION” values(1,’Americas’,’North-America’);

insert into “<YOUR SCHEMA>”.”REGION” values(2,’Americas’,’South-America’);

insert into “<YOUR SCHEMA>”.”REGION” values(3,’Asia’,’India’);

insert into “<YOUR SCHEMA>”.”REGION” values(4,’Asia’,’Japan’);

insert into “<YOUR SCHEMA>”.”REGION” values(5,’Europe’,’Germany’);

– Create Sales table

create column table “<YOUR SCHEMA>”.”SALES”(

“REGION_ID” INTEGER ,

“PRODUCT_ID” INTEGER ,

“SALES_AMOUNT” DOUBLE,

PRIMARY KEY (“REGION_ID”, “PRODUCT_ID”) );

insert into “<YOUR SCHEMA>”.”SALES” values(1,1,100);

insert into “<YOUR SCHEMA>”.”SALES” values(1,2,90);

insert into “<YOUR SCHEMA>”.”SALES” values(1,5,85);

insert into “<YOUR SCHEMA>”.”SALES” values(2,2,80);

insert into “<YOUR SCHEMA>”.”SALES” values(2,1,75);

insert into “<YOUR SCHEMA>”.”SALES” values(3,3,85);

insert into “<YOUR SCHEMA>”.”SALES” values(4,4,75);

insert into “<YOUR SCHEMA>”.”SALES” values(5,1,65);

insert into “<YOUR SCHEMA>”.”SALES” values(5,2,65);

After executing the scripts you should have 3 tables created. If there are no tables, try right-clicking on your schema and refreshing.

Grant schema SELECT rights to _SYS_REPO user:

Open the SQL editor of your schema and execute the following command line:

GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;

If you miss this step, an error will occur when you activate your views later.

Click here to know more about this

Create an attribute view:

1. Open HANA Studio and make sure you are in Modeler perspective

2. Create a new package under the content folder.

Right-click on the content folder and choose “New” -> “Package.” Fill the fields “Name” and “Description” and click “OK.”

If you dont see the new package after this, try right-clicking on the content folder and refreshing.

3. Right click on the package and choose “New Attribute View.” Enter a name and a description and click “Finish”

4. The attribute view will be opened in the HANA studio editor. Select “+” sign on “Data Foundation”

5. Search for table “REGION” and select it.

6. Now add the columns from the REGION table to output. Simply right click on the column and select “Add to Output”. Add all 3 columns REGION_ID, REGION_NAME, SUB_REGION_NAME to the output.

Once it is done, you will see the selected column in right side pane.