Backup

Default location of HANA DB backup.

SAP HANA Modeling complete Notes

in this post, i will cover:

1. Approaching SAP HANA modeling.
2. creating attribute views.
3. creating analytic views.
4. creating calculation views.
5. preparing documents - Auto Documentation.
6. Modeling with information composer.

once we create schemas and tables and load data to SAP HANA,we can start creating different types of views; that is,attribute,analytic,and calculation views according to our requirements.

1.Approaching SAP HANA modeling:

Getting ready: for this, we need SAP HANA studio and SAP HANA client (drivers for SAP HANA) downloaded and installed.these software are available both in 32 bit and 64 bit versions.

there are different types of models,such as attribute views,analytic views,calculation views and so on.

• we create attribute views an top tables.
• in analytic views,we join fact tables to the attribute views.
• Database tables or attribute views, analytic views or calculation views can be included in creating calculation views.
• Apart from these,we can also create store procedures.These procedures can also be used in SQLscript based calculation views.

Basically, data in SAP HANA is organised in the form of tables, in these tables,the respective fields are classified either as an attribute or a measure.

• An attribute in a data -warehouse scenario is called a dimension, and it qualifies something in the data; for example,country code,customer Name,and country.
• A measure is an entity that qualifies something; for example,order quantity,Cost,and sales Amount.

2.creating attribute views:

Attribute Views are similar to dimensions,but the only difference is that attribute views are reusable components as they are object -oriented throughout the information modeling.in simple terms,these are views on one or multiple tables that can be reused for multiple purposes.

For example, the attribute view customers can show Customer code,customer Name,Customer city and other customer master data.This attribute view can be used in both analytic and calculation views based on the relevance.Generally, attribute views represent the dimension data (master data).But technically,there are no limitations or restrictions as it's definitely possible to create attribute views on transactional data.


Authorization Required to create attribute views:

• MODELER role and SELECT authorization on the schema from which we are accessing the tables.
• in addition to this,the _SYS_REPO user must have the EXECUTE authorization with the GRANT option on the schema.

Note: in order to create attribute views, we must have a database table readily available.

We need a package. Packages are folder-like objects into which information views are organized structured.

Data provisioning 2: Uploading data from flat files( for long length table)

if we are loading data in to an existing table in a schema, the data types and length must match in the files and tables, otherwise, we end up with errors while loading data. whereas ,when a table is created while loading data,data types and length will be given by the system according to the data in the file.


if a table is not available,the system will create a table in the required schema.

Step 1: selecting the source file and the target table in SAP HANA.

From Quick Launch, select Import, then select Data from Local File and click on Next, as shown in the following screenshot.

                                         

Click on Browse and select the file.According to the data in the flat file, there are several options available to load data from the flat file to SAP HANA. They are discussed as follows:

1. Selecting Worksheet: if there are many Worksheets in Excel,we can select which worksheet's data to load by selecting it from the drop-down menu.
2. Header rows: if the file has header rows,we can eliminate them by selecting the checkbox. there may be more header rows in the file. Hence, the number of header rows field will be enabled,allowing us to enter the number of header rows to be ignored.
3. Importing all data:  This provides an option to either load all the data or selectively load data.By unchecking the import all data checkbox, two more fields will be enabled to enter Start Line and End Line. only the data in the range of these lines will be loaded.
4. Ignore leading and trailing white-space(s) in the file: This option eliminates all the leading/trailing whitespaces in the data, thereby kind of cleansing the data before loading.

These options are illustrated in the following screen shot:

                                                     

in the bottom portion of the File Import Wizard screen, select NEW, and then select the appropriate schema and table name.


Step2 : in the next fields, schema has to be selected and a new table name has to be given as Table Name. This will create a table in the schema selected, as shown in the following screenshot:


                                                                           

the system will propose new fields in the table, with the same names as that of the file, and the same data type as well. The next step remains the same, mapping and completing the data load,as shown in the following screenshot:

Step 3: field mapping and loading data

After performing the step2, the screen prompts to mapping area.Here we need to map the fields of  file and the table fields.we can manually map by selecting each field and dragging it to the other side. From the system side, we will have 2 options: one -to-one and Map by name. Mapping can be done according to the fields, in this case, we go with one-on-one as the source and target fields are in the same sequence, as shown in the following screenshot.

in the mapping screen, we can see sample data from the file. after the fields are mapped, click on next to preview the data from both the file and the existing table.if data preview is not necessary and data has to be loaded on the go,select Finish.The confirmation is shown in the following screen shot:

                                                                             

 A job runs in the background to import the flat file and completes when the data is successfully imported. A data preview of the table will confirm the data is loaded,as shown in the following screenshot:

1. right click on the data and select data preview.
2. data preview on the table, with Reults.
   
   Thus,data is successfully loaded into the SAP HANA system from a flat file. 

Note: By default, a column table is created; we can see this in the top-right corner of the screen,if a row table is specifically required, Store Type should be changed to ROW store.on doing this the system will generate row table instead of column table.

solman AS JAVA installation issue with SecureStore file

2670783 - SecureStore files must be available on Unix. Check if the global directory is mounted

Where SQL query in HDB

1.login to Hana studio.

2.select the correct and require schema. 

3.open HDBSQL session then. 

4.select * from "<DB_SCHEMA>." <TABLE IN THE SCHEMA> where cityname = 'london' 

 Note: Here where query will be used as filter.

How to automate routine jobs of HANA DB and output of the automated job to Excel sheet

Schedule HANA Query using HDBSQL and HDBUSERSTORE.

We can connect to SAP HANA System using  HANA client HDBSQL.

HDBSQL is a command line tool for executing SQL commands on SAP HANA databases. 

We can use HDBSQL to automate mundane tasks and write scripts to run them at certain frequency. 

Requirement: We want to schedule a script at HANA Client/server to run a SQL weekly and send the output via email in excel file format.

Prerequisite: We need SAP HANA client installed on the source system. We also need an SAP HANA database user to connect and HANA system connection information.
We need OS level access from where we want to schedule the script .
Scenario : In our case, we want to schedule HANA SQL using HDBSQL and send the email to receiverEmailAddress@yourcompanyDomain.com

Target System : saphanadev.yourcompany.domain.com
Target Instance Number  : 00
Target HANA user : SYSTEM 
Password : In123
Source system(HANA client)  : saphanaqas(can be any Linux system with HANA client)

Steps:

• Create a secure store entry and use that ID/User to connect to HANA System.

• Create the shell script files that we will schedule using crontab, this script will use the HDB Secure store connection to execute our SQL file.

• Schedule the script using crontab.

We will create a HANA DB Secure store in HANA client and then will use that secure store to make HANA SQL query.

1. Execute following commands in order to create a Secure store entry :

hdbuserstore SET DEV "saphanadev.yourcompany.domain.com:30015" SYSTEM In123

This will create a secure store entry with unique name DEV.

2.Create a SQL file that will have the SQL that you want to schedule. In our case the file name is "HANA_SELECT_BACKUP_STATUS.sql"

3.Write a shell script file with following content.

hdbsql -U DEV -I /fullPath/HANA_SELECT_BACKUP_STATUS.sql -o /fullPath/HANA_SELECT_BACKUP_STATUS_OUTPUT.csv;

echo "Output of HANA_SELECT_BACKUP_STATUS.sql from HANA"|mailx -a /fullPath/HANA_SELECT_BACKUP_STATUS_OUTPUT.csv -r senderEmailAddress@yourcompanyDomain -s "HANA Backup Script Output from  $HOSTNAME" receiverEmailAddress@yourcompanyDomain.com; 

3. Schedule the shell script using the crontab functionality.

0 0 * * 7 /fullPath/YourScriptName.sh

Please note : You need to give correct permission to the owner for all the files so that it can call the script and make require changes.

Restart and stop SYBASE DB

/usr/sap/hostctrl/exe/saphostexec -restart;sleep 10;/usr/sap/hostctrl/exe/saphostctrl -function StopDatabase -dbname YT4 -dbtype SYB -service

How to login to HDB using hdbsql

hostname:HDB:pl0adm /usr/sap/PL0/HDB02 4> hdbsql -n localhost:3<02>13 -i <02>

Welcome to the SAP HANA Database interactive terminal.

Type:  \h for help with commands
       \q to quit

hdbsql=> select * from "SYS"."M_DATABASES"
Username: system
Password:





































DATABASE_NAME,DESCRIPTION,ACTIVE_STATUS,ACTIVE_STATUS_DETAILS,OS_USER,OS_GROUP,RESTART_MODE,FALLBACK_SNAPSHOT_CREATE_TIME
"SYSTEMDB","SystemDB-PL0-02","YES","","","","DEFAULT",?

HANA SLT Brief

https://www.hanatutorials.com/p/sap-hana-data-provisioning-using-slt.html

To check the list of users logged in to the host using putty

$ w

how to start daemon in linux

stanadard syntax:

hostname:/etc/init.d#./<service_name> status
hostname:/etc/init.d#./<service_name> start
hostname:/etc/init.d#./<service_name> stop



hec44v004423:/etc/init.d # ./scc_daemon status
Checking for scc_Daemon: scc_Daemon is stopped.
hec44v004423:/etc/init.d # ./scc_demon start
-bash: ./scc_demon: No such file or directory
hec44v004423:/etc/init.d # ./scc_daemon  start
Starting scc_Daemon

scc_Daemon started.

performance monitoring in SAP BASIS 2

Performance monitoring in SAP BASIS 1

how to broadcast a message to all the putty session in linux

Syntax

$wall ' your message'


ex: $wall 'system will be restart for maintenance in 10 minutes'