Search for notes by fellow students, in your own course and all over the country.
Browse our notes for titles which look like what you need, you can preview any of the notes via a sample of the contents. After you're happy these are the notes you're after simply pop them into your shopping cart.
Document Preview
Extracts from the notes are below, to see the PDF you'll receive please use the links above
SAP HANA
About the Tutorial
SAP HANA is an in-memory data platform that is deployable as an on-premise appliance, or
in the cloud
...
At the core of this real-time
data platform is the SAP HANA database, which is fundamentally different from any other
database engine in the market today
...
The tutorial is divided into sections such
as SAP HANA Basics, SAP HANA-Modeling, Reporting, and SAP HANA-SQL
...
Audience
This tutorial has been prepared for anyone who has a basic knowledge of SQL
...
Prerequisites
Before you start proceeding with this tutorial, we assume that you are well-versed with
basic database concepts
...
If you are not aware of these concepts, then we recommend that you
first go through our short tutorials on SQL
...
Ltd
...
Ltd
...
We strive to update the contents of our website and tutorials as timely and as precisely as
possible, however, the contents may contain inaccuracies or errors
...
Ltd
...
If you discover any errors on our website or
in this tutorial, please notify us at contact@tutorialspoint
...
AUDIENCE
...
I
COPYRIGHT & DISCLAIMER
...
ERROR! BOOKMARK NOT DEFINED
...
1
1
...
3
...
5
...
7
...
2
SAP HANA – IN-MEMORY COMPUTING ENGINE
...
6
SAP HANA STUDIO –ADMINISTRATION VIEW
...
11
SAP HANA – INFORMATION MODELER
...
16
PART 2: SAP HANA – MODELING
...
9
...
11
...
13
...
15
...
17
...
22
SCHEMA IN DATA WAREHOUSE
...
27
SAP HANA – PACKAGES
...
31
SAP HANA – ANALYTIC VIEW
...
38
SAP HANA – ANALYTIC PRIVILEGES
...
47
SAP HANA – EXPORT AND IMPORT
...
59
18
...
20
...
SAP HANA – REPORTING VIEW
...
0 CONNECTIVITY TO HANA VIEWS
...
63
SAP HANA – EXCEL INTEGRATION
...
72
22
...
24
...
26
...
SAP HANA—SECURITY OVERVIEW
...
76
SAP HANA – AUTHENTICATIONS
...
90
SAP HANA – LICENSE MANAGEMENT
...
94
PART 5: SAP HANA – DATA REPLICATION
...
29
...
31
...
33
...
97
SAP HANA – ETL BASED REPLICATION
...
107
SAP HANA – DXC METHOD
...
112
SAP HANA – MDX PROVIDER
...
115
34
...
116
ii
SAP HANA
35
...
37
...
SAP HANA – PERSISTENT LAYER
...
120
SAP HANA – HIGH AVAILABILITY
...
126
PART 7: SAP HANA – SQL
...
40
...
42
...
44
...
46
...
48
...
50
...
129
SAP HANA – DATA TYPES
...
134
SAP HANA – SQL FUNCTIONS
...
141
SAP HANA – SQL STORED PROCEDURES
...
144
SAP HANA – SQL TRIGGERS
...
148
SAP HANA – SQL EXPLAIN PLANS
...
151
SAP HANA – SQL SCRIPT
...
SAP HANA – OVERVIEW
SAP HANA
SAP HANA is a combination of HANA Database, Data Modeling, HANA Administration and
Data Provisioning in one single suite
...
According to former SAP executive, Dr
...
HANA developed interest by mid-2011 and various fortune 500 companies
started considering it as an option to maintain Business Warehouse needs after that
...
Based on multi core architecture in distributed system environment
...
Used extensively in Memory Computing Engine (IMCE) to process and analyze
massive amount of real time data
...
It is written in C++, supports and runs on only one Operating System Suse Linux
Enterprise Server 11 SP1/2
...
A key to this is the effective and efficient use of data and information by
analyst and managers
...
It involves high maintenance cost for IT companies to store and maintain large data
volumes
...
SAP HANA Vendors
SAP has partnered with leading IT hardware vendors like IBM, Dell, Cisco etc
...
2
SAP HANA
There are, total, 11 vendors that manufacture HANA Appliances and provide onsite support
for installation and configuration of HANA system
...
SAP HANA Installation
HANA Hardware vendors provide preconfigured appliances for hardware, Operating System
and SAP software product
...
This onsite visit includes deployment of HANA system in Data Center, Connectivity to
Organization Network, SAP system ID adaption, updates from Solution Manager, SAP Router
Connectivity, SSL Enablement and other system configuration
...
HANA Studio
Installation is completed on local system and HANA system is added to perform Data
modeling and administration
...
SAP HANA – IN-MEMORY COMPUTING ENGINE
SAP HANA
An In-Memory database means all the data from source system is stored in a RAM memory
...
SAP HANA In-Memory
Database wastes no time in loading the data from hard disk to RAM
...
Features of In-Memory Database
The main features of SAP HANA in-memory database are:
SAP HANA is Hybrid In-memory database
...
It uses parallel processing with multicore CPU Architecture
...
SAP HANA In-Memory
database reads data in 5 nanoseconds
...
Figure: SAP HANA In-Memory Database
Analysts want to see current data immediately in real time and do not want to wait for data
until it is loaded to SAP BW system
...
4
SAP HANA
Advantages of In-Memory Database
HANA database takes advantage of in-memory processing to deliver the fastest dataretrieval speeds, which is enticing to companies struggling with high-scale online
transactions or timely forecasting and planning
...
In-Memory Column-based storage provides data compression up to 11 times, thus,
reducing the storage space of huge data
...
5
3
...
SAP HANA studio is both, the central development
environment and the main administration tool for HANA system
...
It provides an environment for HANA Administration, HANA Information Modeling
and Data Provisioning in HANA database
...
At the time of
Studio installation, specify the features you want to install as per the role
...
SAP HANA Studio Perspectives / Features
SAP HANA Studio provides perspectives to work on the following HANA features
...
General troubleshooting tools like tracing, the catalog browser and SQL Console
are also included
...
It addresses, in particular, the DataMarts and
ABAP on SAP HANA scenarios, which do not include SAP HANA native application
development (XS)
...
It provides Toolset for developing SAP HANA native applications like application code written
in Java and HTML
...
7
4
...
monitoring
features,
SAP
HANA
Administrator Editor can be accessed in several ways:
From System View Toolbar: Choose Open Administration default button
In System View: Double Click on HANA System or Open Perspective
HANA Studio: Administrator Editor
In Administration View: HANA studio provides multiple tabs to check configuration and
health of the HANA system
...
Adding a HANA System to Studio
Single or multiple systems can be added to HANA studio for administration and information
modeling purpose
...
1
...
3
...
Port
Port
Port
SSh
3615 should be open to connect to Database
31015 Instance No 10
30015 Instance No 00
port should also be opened
Adding a System to Hana Studio
To add a system to HANA studio, follow the given steps
...
Enter HANA system details, i
...
Host name & Instance number and click next
...
Click on Next
and then Finish
...
Each HANA system has two main sub-nodes, Catalog and Content
...
e
...
Content
The Content tab contains design time repository, which holds all information of data models
created with the HANA Modeler
...
The content node
provides different views on same physical data
...
SAP HANA – SYSTEM MONITOR
SAP HANA
System Monitor in HANA studio provides an overview of all your HANA system at a glance
...
It tells about Data Disk, Log disk, Trace Disk, Alerts on resource usage
with priority
...
SAP HANA – INFORMATION MODELER
SAP HANA
SAP HANA Information Modeler; also known as HANA Data Modeler is heart of HANA System
...
Features of Information Modeler
Provides multiple views of transactional data stored in physical tables of HANA
database for analysis and business logic purpose
...
Information Modeling Views are consumed by Java or HTML based applications or
SAP tools like SAP Lumira or Analysis Office for reporting purpose
...
SAP HANA Modeling Views exploit real power of SAP HANA
...
Storing
data in Column tables is not a new thing
...
With evolution of SAP HANA, HANA used column based data storage in Information views
and presented the real benefits of columnar tables over Row based tables
...
So, similar data types come together as
shown in the example above
...
In a conventional database, data is stored in Row based structure i
...
horizontally
...
This provides Performance
optimization, flexibility and data compression in HANA database
...
In Dictionary Compressed, cells are stored in form of numbers in tables and numeral cells
are always performance optimized as compared to characters
...
Column Store: Types of Column based storage
Functional Difference: Row vs Column Store
It is always advisable to use Column based storage, if SQL statement has to perform
aggregate functions and calculations
...
Row based storage is preferred when output has to return complete row
...
13
SAP HANA
Row vs Column Store: Functional
In the above example, while running an Aggregate function (Sum) in sales column with
Where clause, it will only use Date and Sales column while running SQL query so if it is
column based storage table then it will be performance optimized, faster as data is required
only from two columns
While running a simple Select query, full row has to be printed in output so it is advisable
to store table as Row based in this scenario
...
They represent master data
and similar to characteristics of BW
...
Important features are:
Attribute views are used in Analytic and Calculation views
...
Used to filter size of dimension tables in Analytic and Calculation View
...
It has at least one fact table that has measures and primary keys
of dimension tables and surrounded by dimension tables contain master data
...
Analytic views contain at least one fact table and multiple dimension tables with
master data and perform calculations and aggregations
They are similar to Info Cubes and Info objects in SAP BW
...
Calculation Views
Calculation Views are used on top of Analytic and Attribute views to perform complex
calculations, which are not possible with Analytic Views
...
Important features are:
Calculation Views are defined either graphical using HANA Modeling feature or
scripted in the SQL
...
One or more Attribute views and Analytic views are consumed with help of inbuilt
functions like Projects, Union, Join, Rank in a Calculation View
...
SAP HANA – CORE ARCHITECTURE
SAP HANA
SAP HANA was initially, developed in Java and C++ and designed to run only Operating
System Suse Linux Enterprise Server 11
...
Most important component of SAP HANA system is Index Server, which contains
SQL/MDX processor to handle query statements for database
...
SAP HANA: Core Architecture
Index Server
Index Server is heart of SAP HANA database system
...
When SQL or MDX is fired for SAP HANA system, an Index Server
takes care of all these requests and processes them
...
Index Server contains Data engines to handle all SQL/MDX statements that come to HANA
database system
...
Index Server also has Session and Transaction Manager, which manage transactions and
keep track of all running and closed transactions
...
It segments all query requests and direct them to correct engine for the
performance Optimization
...
It contains several engines and processors for
query execution:
MDX (Multi Dimension Expression) is query language for OLAP systems like SQL is
used for Relational database
...
Planning Engine is responsible to run planning operations within SAP HANA database
...
17
SAP HANA
Stored Procedure processor executes procedure calls for optimized processing; it
converts OLAP cubes to HANA optimized cubes
...
When a transaction is executed or failed, Transaction manager notifies relevant data engine
to take necessary actions
...
Persistence Layer
It is responsible for durability and atomicity of transactions in HANA system
...
It ensures database is restored to most recent state and ensures that all the transactions
are completed or undone in case of a system failure or restart
...
Backups are stored as save points in the
Data Volumes via a Save Point coordinator, which is normally set to take back every 5-10
minutes
...
Index Server uses preprocessor server for analyzing text data and extracting the
information from text data when text search capabilities are used
...
In distributed
environment, there are multiple nodes with each node has multiple CPU’s, Name server
holds topology of HANA system and has information about all the running components and
information is spread on all the components
...
It decreases the time in re-indexing as it holds which data is on which server in
distributed environment
...
Statistical
Server is responsible for collecting the data related to system resources, their allocation and
consumption of the resources and overall performance of HANA system
...
18
SAP HANA
XS Engine
XS engine helps external Java and HTML based applications to access HANA system with
help of XS client
...
SAP HANA: XS Engine
XS Engine transforms the persistence model stored in database into consumption model for
clients exposed via HTTP/HTTPS
...
SAP Host agent is used by Software Update Manager SUM for installing
automatic updates to all components of HANA system in distributed environment
...
This information is used by Software Update Manager to install automatic updates on HANA
system components
...
This agent provides all the information about HANA database, which include
database current state and general information
...
SAP HANA Studio Repository
19
SAP HANA
SAP HANA studio repository helps HANA developers to update current version of HANA
studio to latest versions
...
Software Update Manager for SAP HANA
SAP Market Place is used to install updates for SAP systems
...
It is used for software downloads, customer messages, SAP Notes and requesting license
keys for HANA system
...
20
SAP HANA
PART 2: SAP HANA – MODELING
21
8
...
These views are consumed by JAVA/HTML based applications or
SAP Applications like SAP Lumira, Office Analysis or third party software like MS Excel for
reporting purpose to meet business logic and to perform analysis and extract information
...
You can create new Package under Content tab in HANA studio using right click on Content
and New
...
Each View has different structure for Dimension and Fact tables
...
Fact and Dimension Table
Fact Table contains Primary Keys for Dimension table and measures
...
Example of Measures: Number of unit sold, Total Price, Average Delay time, etc
...
Dimension tables are used to create schemas with fact tables and can
be normalized
...
Suppose a company sells products to customers
...
22
SAP HANA
Figure: Fact and Dimension Table
For example, row 3 in the fact table records the fact that customer 1 (Brian) bought one
item on day 4
...
The fact table lists events that happen in our company (or at least the events that we want
to analyze- No of Unit Sold, Margin, and Sales Revenue)
...
23
9
...
Schemas are created by
joining multiple fact and Dimension tables to meet some business logic
...
However, Data Warehouse use Schemas that
join dimensions and fact tables to meet business logic
...
Each Dimension is
represented by only one dimension and is not further normalized
...
Example: In example given below, we have a Fact table FactSales that has Primary keys
for all the Dim tables and measures units_sold and dollars_ sold to do analysis
...
Facts/Measures in Fact Table are used for analysis purpose along with attribute in Dimension
tables
...
Normalization is used to organize attributes and tables
of database to minimize the data redundancy
...
Figure: Snowflakes Schema: Dim, Normalized Dim and Fact Table
In the above example, DimItem and DimLocation Dimension tables are normalized without
losing any information
...
Galaxy Schema
In Galaxy Schema, there are multiple Fact tables and Dimension tables
...
Figure: Galaxy Schema: Multiple Dim and Fact Tables
25
SAP HANA
In the above example, there are two Fact tables FactSales, FactShipping and multiple
Dimension tables joined to Fact tables
...
26
10
...
New tables can be created using the two methods given below:
Using SQL editor
Using GUI option
SQL Editor in HANA Studio
SQL Console can be opened by selecting Schema name, in which, new table has to be
created using System View SQL Editor option or by Right click on Schema name as shown
below:
HANA Studio: Creating a New Table using SQL Editor
Once SQL Editor is opened, Schema name can be confirmed from the name written on the
top of SQL Editor
...
Once you write Create table SQL query, click on Execute option on top of SQL editor right
side
...
Once
statement is successfully executed, right click on Table tab under Schema name in System
View and refresh
...
Insert statement is used to enter the data in the Table using SQL editor
...
You can right click on Table name and use Open Data Definition to see data type of the
table
...
Creating Table using GUI Option
Another way to create a table in HANA database is by using GUI option in HANA Studio
...
Once you click on New Table-> It will open a window to enter the Table name, Choose
Schema name from drop down, Define Table type from drop down list: Column Store or
Row Store
...
Columns can be added by clicking on + sign, Primary Key
can be chosen by clicking on cell under Primary key in front of Column name, Not Null will
be active by default
...
28
SAP HANA
HANA Studio: Creating Table
Once you Execute (F8), Right Click on Table Tab-> Refresh
...
Below Insert Option can be used to insert data in
table
...
Inserting Data in a table using GUI in HANA studio
You can right click on Table name and use Open Data Definition to see data type of the
table
...
To use tables from one Schema to create views we should provide access on the Schema to
the default user who runs all the Views in HANA Modeling
...
SAP HANA – PACKAGES
SAP HANA
SAP HANA Packages are shown under Content tab in HANA studio
...
You can create a new Package by Right Click on Content Tab-> New-> Package
SAP HANA: Package
You can also create a Sub Package under a Package by right clicking on the Package name
...
You can also create Decision Table, Define Analytic Privilege and create Procedures in a
Package
...
You have to enter Package Name, Description while creating a Package
...
SAP HANA – ATTRIBUTE VIEW
SAP HANA
Attribute Views in SAP HANA Modeling are created on the top of Dimension tables
...
You can also copy a new Attribute
View from already existing Attribute Views inside other Packages but that doesn’t let you
change the View Attributes
...
Attribute Views are used in Analytical and Calculation Views for analysis to pass
master data
...
Attribute Views are used for performance optimization in large size Dimension tables,
you can limit the number of attributes in an Attribute View which are further used
for Reporting and analysis purpose
...
How to Create an Attribute View?
Choose the Package name under which you want to create an Attribute View
...
Enter Attribute View name and
description
...
In sub type, there
are three types of Attribute views: Standard, Time, and Derived
...
When you enter the Attribute name, Type and Subtype and click on
Finish, it will open three work panes:
Scenario pane that has Data Foundation and Semantic Layer
...
Output pane where we can add attributes from Detail pane to filter in the report
...
You can add multiple Dimension tables and Attribute Views in the Scenario Pane
and join them using a Primary Key
...
Once Tables or Attribute
Views are added to Data Foundation, they can be joined using a Primary Key in Details Pane
as shown below
...
All columns will be added to Output pane
...
Now you can right click on the Attribute View and go for Data Preview
...
However, once you activate
it, that diamond disappears that confirms that View has been activated successfully
...
These Objects can be added to Labels and Value axis by right click and adding or by dragging
the objects as shown below:
33
SAP HANA
Attribute View: Adding Objects to Axis
34
13
...
Analytic views use real power of SAP HANA to perform complex
calculations and aggregate functions by joining tables in form of star schema and by
executing Star schema queries
...
Analytic Views are designed to run Start schema queries
...
Fact
table contains primary key for each Dim table and measures
...
How to Create an Analytic View?
Choose the Package name under which you want to create an Analytic View
...
When you click on an Analytic View, New Window
will open
...
SAP HANA: Creating an Analytic View
35
SAP HANA
When you click Finish, you can see an Analytic View with Data Foundation and Star Join
option
...
Click on Star Join to add
Attribute Views
...
In the example given below, 3
dim tables have been added: DIM_CUSTOMER, DIM_PRODUCT, DIM_REGION and 1 Fact
table FCT_SALES to Details Pane
...
SAP HANA: Adding tables in Analytic View to perform Star Schema
Select Attributes from Dim and Fact table to add to Output pane as shown in snapshot
shown above
...
Click on Semantic layer, choose facts and click on measures sign as shown below to change
datatype to measures and Activate the View
...
Add Attributes to Labels Axis and Measure to Value axis
for analysis purpose
...
Choosing graphs
37
14
...
These are used to perform complex calculations, which are not
possible with other type of Views
...
They are used to perform complex calculations, which are not possible with other
Views
...
Built-in Union, Join, Projection & Aggregation nodes
...
Right Click
on Package-> Go to New-> Calculation View
...
SAP HANA: Creating a Calculation View
38
SAP HANA
Enter View name, Description and choose view type as Calculation View, Subtype Standard
or Time (this is special kind of View which adds time dimension)
...
Graphical Calculation Views
It has default nodes like aggregation, Projection, Join and Union
...
SQL Script based Calculation Views
It is written in SQL scripts that are built on SQL commands or HANA defined functions
...
You can choose Star join with Cube dimension
...
Calculation View with Star Join
It does not allow base column tables, Attribute Views or Analytic views to add at data
foundation
...
All Fact tables can be added and can use default nodes in Calculation View
...
You have to find list of all
employees with their Joining date, Emp Name, empId, Salary and Bonus
...
Dim Tables: Empdim and Empdate
Create column table Empdim (empId nvarchar(3),Empname nvarchar(100));
Insert into Empdim values('AA1','John');
Insert into Empdim values('BB1','Anand');
Insert into Empdim values('CC1','Jason');
Create column table Empdate (caldate
nvarchar(4));
date, CALMONTH nvarchar(4) ,CALYEAR
Insert into Empdate values('20100101','04','2010');
Insert into Empdate values('20110101','05','2011');
Insert into Empdate values('20120101','06','2012');
Fact Tables: Empfact1, Empfact2
Create column table Empfact1 (empId nvarchar(3), Empdate date, Sal integer );
Insert into
Empfact1 values('AA1','20100101',5000);
Insert into
Empfact1 values('BB1','20110101',10000);
Insert into
Empfact1 values('CC1','20120101',12000);
Create column table Empfact2 (empId nvarchar(3), deptName nvarchar(20), Bonus
integer );
Insert into Empfact2 values ('AA1','SAP', 2000);
Insert into Empfact2 values ('BB1','Oracle', 2500);
Insert into Empfact2 values ('CC1','JAVA', 1500);
Now we have to implement Calculation View with Star Join
...
Create a Calculation View with Star Join
...
Add both fact tables to both Projections and add attributes of these Projections to
Output pane
...
Add parameters of Fact Join to
output pane
...
Choose parameters in Output pane and active the View
...
Add
attributes and measures to values and labels axis and do the analysis
...
You need not to create Analytical views and Attribute Views
and directly Fact tables can be used as Projections
...
Calculation View without Star Join
Create 2 Attribute Views on 2 Dim tables-Add output and activate both the views
...
Now Create a Calculation View-> Dimension (Projection)
...
Add attributes of this Join to output pane
...
Activate the view successful and go to Data preview for analysis
...
SAP HANA – ANALYTIC PRIVILEGES
SAP HANA
Analytic Privileges are used to limit access on HANA Information views
...
Sometimes, it is required that data in the same view should not be accessible to other users
who do not have any relevant requirement for that data
...
Now if you do
not want your Report developer to see Salary details or Emp logon details of all employees,
you can hide this by using Analytic privileges option
...
We cannot
add measures to restrict access in Analytic privileges
...
So we can restrict data by Empname, EmpId, Emp logon or by Emp Dept and not by
numerical values like salary, bonus
...
Enter name and Description of Analytic Privilege-> Finish
...
44
SAP HANA
You can click on Next button and add Modeling view in this window before you click on
finish
...
Once you click on Add button, it will show you all the views under Content tab
...
Selected View
will be added under reference models
...
Add objects you want to add to Analytic privileges from select object option and click on
OK
...
You can add Object value that will not reflect in Data Preview of Modeling
View
...
Status
message – completed successfully confirms activation successfully under job log and we can
use this view now by adding to a role
...
Adding Analytic Privileges to User roles
Search Analytic Privilege you want to apply with the name and click on OK
...
To delete Analytic Privileges from specific user, select view under tab and use Red delete
option
...
46
16
...
It allows you to import data from workbook format (
...
csv) into HANA
database and to create Modeling views for analysis
...
Technically sound people who have strong experience in data
modeling use HANA Modeler
...
It provides simple functionalities with easy to use interface
...
Manipulating data: It helps us to combine two objects (Physical tables, Analytical
View, attribute view and calculation views) and create information view that can be
consumed by SAP BO Tools like SAP Business Objects Analysis, SAP Business Objects
Explorer and other tools like MS Excel
...
How to upload data using Information Composer?
It allows us to upload large amount of data (up to 5 million cells)
...
You can perform data loading or manipulation
using this tool
...
xls,
...
It allows data to be loaded along with header
...
In this case, you
have to delete the table from Schema in HANA database
...
One can find
details of tables created using IC under these tables
...
Copy the data to clipboard and
upload it with help of Information Composer
...
It has inbuilt
capability of data cleansing that is used to remove any inconsistency in data
...
IC has inbuilt
feature to check the data type of uploaded data
...
Provide a technical
name and description of table and this will be loaded inside IC_Tables Schema
...
IC_MODELER is for creating physical tables, uploading data and creating information
views
...
This role
does not allow the user to upload or create any information views using IC
...
Java 6 (64-bit) must be installed on the server
...
Client Requirements:
Internet Explorer with Silverlight 4 installed
...
SAP HANA – EXPORT AND IMPORT
SAP HANA
HANA Export and Import option allows tables, Information models, Landscapes to move to
a different or existing system
...
This option can be accessed from File menu at the top or by right clicking on any table or
Information model in HANA studio
...
Users can use this option to export all the packages that make a delivery unit and the
relevant objects contained in it to a HANA Server or to local Client location
...
This can be done through HANA Modeler->Delivery Unit->Select System and Next->Create> Fill the details like Name, Version, etc
...
You can see list of all packages assigned to Delivery unit
...
The user can restrict the export through “Filter by time” which means Information views,
which are updated within the mentioned time interval will only be exported
...
This will export
the selected Delivery Unit to the specified location
...
User
can select single Information view or group of Views and Packages and select the local Client
location for export and Finish
...
52
SAP HANA
Support Mode
This can be used to export the objects along with the data for SAP support purposes
...
Example: User creates an Information View, which throws an error and he is not able to
resolve
...
53
SAP HANA
Export Options under SAP HANA Studio:
Landscape: To export the landscape from one system to other
...
Import Option under SAP HANA Content
Go to File->Import, You will see all the options as shown below under Import
...
xls or
...
Click on Next-> Choose Target System-> Define Import Properties
Select Source file by browsing local system
...
It also gives an option to create a new table under existing Schema or if you
want to import data from a file to an existing table
...
55
SAP HANA
When you click on finish, that table will be populated under list of tables in mentioned
Schema
...
xls file
...
You can choose from a server
or local client
...
If the user selects “Activate objects”, then after the import, all
the imported objects will be activated by default
...
Click Finish and once completed successfully, it will be populated to target system
...
56
SAP HANA
Mass Import of Metadata
Go to File->Import->Mass Import of Metadata ->Next and select the source and target
system
...
Selective Import of Metadata
It allows you to choose tables and target schema to import Meta data from SAP Applications
...
Remember that the Data Store should
have been created already of type SAP Applications->Click Next
57
SAP HANA
Select tables you want to import and validate data if required
...
58
SAP HANA
PART 3: SAP HANA – REPORTING
59
18
...
These Views
can be consumed by different reporting tools like SAP Business Object, SAP Lumira, Design
Studio, Office Analysis and even third party tool like MS Excel
...
This generates the need for consuming HANA Modeling views by different reporting tools
and to generate reports and dashboards, which are easy to understand for end users
...
There is wide variety of BI tools like – Web Intelligence, Crystal Reports,
Dashboard, Explorer, Office Analysis and many more
...
SAP HANA – BI 4
...
WebI uses a semantic layer called Universe to connect to data source and these Universes
are used for reporting in tool
...
IDT supports multisource enabled data source
...
Main tools that are used for designing interactive dashboards- Design Studio and Dashboard
Designer
...
Dashboard design (xcelsius) uses IDT to consume
schemas in HANA database with a Relational or OLAP connection
...
HANA views can be directly consumed in Lumira for visualization and creating
stories
...
This OLAP
connection can be created in CMC or IDT
...
It also depicts tools,
which need a relational connection using IDT to connect to HANA are shown with dotted
lines
...
A Relational connection can only be created in IDT/UDT
...
Another thing to note is that a relational connection always produces a SQL statement to
be fired from report while an OLAP connection normally creates a MDX statement
Information Design Tool
In Information design tool (IDT), you can create a relational connection to an SAP HANA
view or table using JDBC or ODBC drivers and build a Universe using this connection to
provide access to client tools like Dashboards and Web Intelligence as shown in above
picture
...
62
20
...
You can also connect to SAP HANA using an OLAP connection created using information
design tool or CMC
...
Dashboards
Dashboards can connect to SAP HANA only through a relational Universe
...
Web Intelligence
Web Intelligence can connect to SAP HANA only through a Relational Universe
...
It can also connect
to SAP HANA through SAP BI Platform using a relational Universe
...
Explorer
You can create an information space based on an SAP HANA view using JDBC drivers
...
Relational
connection through IDT is used to connect Web Intelligence and Dashboards to HANA
database
...
Login to CMC with the user name and password
...
It will also show already
created connections in CMC
...
Enter the name of an OLAP connection and description
...
Provider: SAP HANA
Server: Enter HANA Server name
Instance: Instance number
It also gives an option to connect to a single Cube (You can also choose to connect to single
Analytic or Calculation view) or to the full HANA system
...
Authentication Types: Three types of Authentication are possible while creating an OLAP
connection in CMC
...
64
SAP HANA
Prompt: Every time it will ask user name and password
SSO: User specific
Enter user: user name and password for HANA system and save and new connection
will be added to existing list of connections
...
By default, it will show you the Information
View if you have specified it while creating this connection otherwise click on Next and go
to folders-> Choose Views (Analytic or Calculation Views)
...
Enter HANA server name and Instance number
...
65
SAP HANA
It will show all views
...
It will show
all measures and dimensions
...
There are four tabs inside SAP Lumira:
Prepare: You can see the data and do any custom calculation
...
Click on X axis and Y axis + sign to add
attributes
...
Drag first Visualization then add page then add second visualization
...
Otherwise you can also publish story from SAP Lumira to SAP Community Network
SCN or BI Platform
...
Click on New-> Project
Enter Project Name-> Finish
...
You can also test this connection by clicking on Test Connection option
...
Next step is to publish this connection to Repository to make
it available for use
...
It will create a new relational connection with
...
67
SAP HANA
...
...
If you use this connection while creating and
publishing a Universe, it will not allow you to publish that to repository
...
cns connection type->Right Click on this->click on New Data foundation->Enter
Name of Data foundation->Next->Single source/multi source->click on Next->Finish
...
Import all tables from HANA database to master pane to create a Universe
...
Double Click on the Joins and detect Cardinality->Detect-> OK->Save All at the top
...
Right Click on
...
It will show all
the objects automatically, under master pane->
...
68
SAP HANA
Right-click on
...
Now open WebI Report from BI Launchpad or Webi rich client from BI Platform client tools
-> New -> select Universe->TEST_SAP_HANA->OK
...
You can choose attributes and measures from left
pane and add them to Result Objects
...
69
21
...
Business Managers and Analysts can connect it to HANA database to draw
Pivot tables and charts for analysis
...
Choose SAP HANA MDX Provider from this list to connect to any MDX data source-> Enter
HANA system details (server name, instance, user name and password)-> click on Test
Connection-> Connection succeeded-> OK
...
You can choose an Information view -> click Next-> Select Pivot table/others-> OK
...
You can choose different
attributes and measures to report as shown and you can choose different charts like pie
charts and bar charts from design option at the top
...
SAP HANA—SECURITY OVERVIEW
SAP HANA
Security means protecting company’s critical data from unauthorized access and use, and
to ensure that Compliance and standards are met as per the company policy
...
SAP HANA supports multiple databases in a single HANA system and this is known as
multitenant database containers
...
A multiple container system always has exactly one system database
and any number of multitenant database containers
...
Database containers in HANA
system are identified by a SID and database name
...
SAP HANA provides all security related features such as Authentication, Authorization,
Encryption and Auditing, and some add on features, which are not supported in other
multitenant databases
...
SAP HANA User and Role Management
SAP HANA user and role management configuration depends on the architecture of your
HANA system
...
If the end-user directly connects to the SAP HANA database, then user and role in
database layer of HANA system is required for both end users and administrators
...
User accessing HANA system can either be a technical user or an end user
depending on the access requirement
...
Executing that operation depends on privileges
that user has been granted
...
HANA Studio is one of powerful tool to manage user and roles for HANA database system
...
User type can be a technical database user or end user needs access on HANA
system for reporting purpose or for data manipulation
...
Read access is provided by PUBLIC role which is assigned to
every standard users
...
When these users are created, they do not
have any access initially
...
They do not have access to view any data in database as they don’t have generic
Public role added to profile like standard users
...
75
23
...
SAP HANA User Administration Activities
Depending on business needs and configuration of HANA system, there are different user
activities that can be performed using user administration tool like HANA studio
...
To create users and roles in HANA studio, go to HANA Administrator
Console
...
To create a new user right
click on User and go to New User
...
Enter User name (mandate) and in Authentication field enter password
...
You can also choose to create a restricted user
...
The
password rules include a minimal password length and a definition of which character types
(lower, upper, digit, special characters) have to be part of the password
...
Users in the database can be authenticated by varying mechanisms:
Internal authentication mechanism using a password
...
509
...
However, only one
password and one principal name for Kerberos can be valid at any one time
...
It also gives an option to define validity of user, you can mention validity interval by
selecting the dates
...
Some users that are, by default, delivered with the SAP HANA database are: SYS, SYSTEM,
_SYS_REPO, _SYS_STATISTICS
...
There are different
types of privileges that can be added to a user profile
...
HANA roles to user profile or to add custom roles created
under Roles tab
...
This removes need to remember and add objects
to a user profile every time for different access types
...
This role
contains read only access to system views and execute privileges for some procedures
...
Modeling
It contains all privileges required for using the information modeler in the SAP HANA studio
...
To add a
system privileges to a user profile, click on + sign
...
Content Admin
It contains the similar privileges as that in MODELING role, but with the addition that this
role is allowed to grant these privileges to other users
...
78
SAP HANA
System Privileges for Users and User Roles
Data Admin
This is a type of privilege, required for adding Data from objects to user profile
...
Additionally, the
DEBUG privilege for the corresponding procedure is needed
...
Also allows access to AUDIT_LOG system view
...
Also allows access to AUDIT_LOG system view
...
Backup Operator
It authorizes the BACKUP command to initiate a backup process
...
Normally, the
content of these views is filtered based on the privileges of the accessing user
...
By
default, each user owns one schema, with this privilege the user is allowed to create
additional schemas
...
Only the owner of
an Analytical Privilege can further grant or revoke that privilege to other users or roles
...
Data Admin
It authorizes reading all data in the system views
...
Database Admin
It authorizes all commands related to databases in a multi-database, such as CREATE,
DROP, ALTER, RENAME, BACKUP, RECOVERY
Export
It authorizes export activity in the database via the EXPORT TABLE command
...
Import
It authorizes the import activity in the database using the IMPORT commands
...
Inifile Admin
It authorizes changing of system settings
...
Log Admin
It authorizes the ALTER SYSTEM LOGGING [ON|OFF] commands to enable or disable the
log flush mechanism
...
Optimizer Admin
It authorizes the ALTER SYSTEM commands concerning SQL PLAN CACHE and ALTER
SYSTEM UPDATE STATISTICS commands, which influence the behavior of the query
optimizer
...
For example, ALTER
SYSTEM RECLAIM DATAVOLUME and ALTER SYSTEM RESET MONITORING VIEW
...
Role Admin
This privilege authorizes the creation and deletion of roles using the CREATE ROLE and DROP
ROLE commands
...
Activated roles, meaning roles whose creator is the pre-defined user _SYS_REPO, can
neither be granted to other roles or users nor dropped directly
...
Please check documentation concerning activated
objects
...
Components of the SAP HANA database can create new system privileges
...
81
SAP HANA
Object/SQL Privileges
Object privileges are also known as SQL privileges
...
Object Privileges for Users and User Roles
Given below are possible types of Object Privileges:
Object privilege on database objects that exist only in runtime
Object privilege on activated objects created in the repository, like calculation views
Object privilege on schema containing activated objects created in the repository,
Object/SQL Privileges are collection of all DDL and DML privileges on database
objects
...
82
SAP HANA
Object Privileges and their applicability on database objects:
Analytic Privileges
Sometimes, it is required that data in the same view should not be accessible to other users
who does not have any relevant requirement for that data
...
We can apply row and column level security in Analytic Privileges
...
Allocation of row and column level security for modeling views
...
Package privileges are used to allow access to data models- Analytic or Calculation
views or on to Repository objects
...
You can also mention if assigned user authorizations
can be passed to other users
...
Use Ctrl key to select multiple packages
...
Select one or more repository packages that you want to authorize access to, the
selected packages appear in the Package Privileges tab
...
READ: Read access to the selected package and design-time objects (both
native and imported)
REPO
...
Grantable to Others - If you choose ‘Yes’ for this, this allows assigned user
authorization to pass to the other users
...
This can be assigned to an individual user or to the group of users
...
Application Privileges for Users and User Roles
To define Application specific privileges in a user profile or to add group of users, below
privileges should be used:
Application-privileges file (
...
xsaccess)
Role-definition file (
...
SAP HANA – AUTHENTICATIONS
SAP HANA
All SAP HANA users that have access on HANA database are verified with different
Authentications method
...
Below is the list of authentication methods supported by SAP HANA:
User name/Password
Kerberos
SAML 2
...
509
User Name/Password
This method requires a HANA user to enter user name and password to login to database
...
Password should be as per password policy i
...
Password length, complexity, lower and
upper case letters, etc
...
Please
note that password policy cannot be deactivated
...
It is required to map external login to internal database
user
...
It also allows HTTP access in HANA Extended Service using HANA XS engine
...
SAML
SAML stands for Security Assertion Markup Language and can be used to authenticate users
accessing HANA system directly from ODBC/JDBC clients
...
SAML is used only for authentication purpose and not for authorization
...
These
tickets are issued to users when they login into SAP system, which is configured to issue
such tickets like SAP Portal, etc
...
X
...
509 certificates can also be used to login to HANA system via HTTP access request from
HANA XS engine
...
User in trusted certificate should exist in HANA system as there is no support for user
mapping
...
User logins at client applications using
different authentication methods and SSO allows user to access HANA system directly
...
509 client certificates for HTTP access from HANA XS engine
SAP Logon/Assertion tickets
89
25
...
When a user connects to HANA database using client tools via
JDBC/ODBC or Via HTTP to perform some operations on database objects, corresponding
action is determined by the access that is granted to the user
...
Authorization is a combination of both accesses
...
When all required privileges are found, system stops this check and grants the
requested access
...
They are used for administrative tasks such as creating Schemas, data backups,
creating users and roles and so on
...
Object Privileges
They are applicable to database operations and apply to database objects like tables,
Schemas, etc
...
Different actions like Select, Execute, Alter, Drop, Delete can be defined based on database
objects
...
Analytic Privileges
They are applicable to data inside all the packages that are created in HANA repository
...
They apply row and column level security to
attributes that are defined in modeling views in HANA packages
...
Package contains different Modeling views like Attribute,
Analytic and Calculation views and also Analytic Privileges defined in HANA repository
database
...
They are used to control access on applications created with HANA XS engine
...
Repository Authorization in SAP HANA Database
_SYS_REPO is the user owns all the objects in HANA repository
...
_SYS_REPO is owner of all objects so it can only be used to grant access on these
objects, no other user can login as _SYS_REPO user
...
SAP HANA – LICENSE MANAGEMENT
SAP HANA
SAP HANA License management and keys are required to use HANA database
...
Types of License keys
SAP HANA system supports two types of License keys:
Temporary License Key – Temporary License keys are automatically installed
when you install the HANA database
...
Permanent License Key – Permanent License keys are valid only till the predefine
expiration date
...
They can installed from SAP Market place under Keys and Requests tab
...
During this period, you have to install a permanent License
key again
...
Enforced: If Enforced license key is installed and consumption of HANA system
exceeds the license amount of memory, HANA system gets locked
...
There is different License scenarios that can be used in HANA system depending on the
landscape of the system (Standalone, HANA Cloud, BW on HANA, etc
...
92
SAP HANA
How to Check License Properties of HANA
Right Click on HANA system-> Properties->License
It tells about License type, Start Date and Expiration Date, Memory Allocation and the
information (Hardware Key, System Id) that is required to request a new license through
SAP Market Place
...
All Licenses tab under License tells about Product name, description, Hardware key, First
installation time, etc
...
SAP HANA – AUDITING
SAP HANA
SAP HANA audit policy tells the actions to be audited and also the condition under which the
action must be performed to be relevant for auditing
...
SAP HANA database auditing feature allows monitoring action performed in HANA system
...
When an action is
performed, the policy triggers an audit event to write to audit trail
...
In a distributed environment, where you have multiple database, Audit policy can be enabled
on each individual system
...
ini
file and for tenant database, it is defined in global
...
Activating an Audit Policy:
To define Audit policy in HANA system, you should have system privilege: Audit Admin
...
You can also choose Audit trail targets
...
Database Table: Internal database table, user who has Audit admin or Audit
operator system privilege he can only run select operation on this table
...
94
SAP HANA
You can also create a new Audit policy in the Audit Policies area->choose Create New Policy
...
Save the new policy using the Deploy button
...
You can disable a
policy by changing status to disable or you can also delete the policy
...
SAP HANA – DATA REPLICATION OVERVIEW
SAP HANA
SAP HANA Replication allows migration of data from source systems to SAP HANA database
...
System replication can be set up on the console via command line or by using HANA studio
...
We have three
types of data replication methods in HANA system:
SAP LT Replication method
ETL tool SAP Business Object Data Service BODS) method
Direct Extractor connection method (DXC)
SAP LT Replication Method
SAP Landscape Transformation Replication is a trigger based data replication method in
HANA system
...
It has SAP LT Replication server, which takes
care of all trigger requests
...
02 or above
...
Advantages of SLT Replication
SLT Replication method allows data replication from multiple source systems to one
HANA system and also from one source system to multiple HANA systems
...
It has no measureable performance impact in
source system
...
It allows real-time data replication, replicating only relevant data into HANA from
SAP and non-SAP source systems
...
Creating a Trusted RFC Connection in ECC system
On your source SAP system AA1 you want to setup a trusted RFC towards target system
BB1
...
Using RFC trusted/trusting relationship between two SAP systems, RFC from a trusted
system to a trusting system, password is no required for logging on to the trusting system
...
Enter transaction number sm59 -> this is
transaction number to create a new Trusted RFC connection ->Click on 3rd icon to open a
new connection wizard-> click on Create and new window will open
...
98
SAP HANA
Go to Logon & Security tab, Enter Language, Client, ECC system user name and password
...
Click on Test Connection and it will successfully test the connection
...
Click on New-> New Window will open-> Enter configuration name ->Click Next -> Enter
RFC Destination (connection name created earlier), Use search option, choose name and
click next
...
Enter No of Data transfer jobs like 007(it cannot be 000)
-> Next-> Create Configuration
...
Select tables you want to move to HANA database->
ADD->Finish
...
101
29
...
BODS system is an ETL tool used to extract,
transform and load data from source system to target system
...
You need to define data flows in
Data Services, scheduling a replication job and defining source and target system in data
store in Data Services designer
...
and apply
...
Right click on Table ->Import by name ->Enter ECC table to import from ECC system (MARA
is default table in ECC system)-> Import -> Now expand Table -> MARA -> Right Click View
Data
...
Now, to choose target system as HANA database, create a new data store
...
x
...
103
SAP HANA
This data store will be added to Local Object Library
...
Note that target table
should be of similar datatype as source table
...
From right side tab, choose work flow -> Enter work flow name -> Double click to add it
under batch job -> Enter data flow -> Enter data flow name -> Double click to add it under
batch job in Project area Save all option at top
...
Select it and right click-> Add
new-> Template table to create new table with similar data types in HANA DB-> Enter table
name, Data store ECC_HANA_TEST2-> Owner name (schema name) -> OK
Drag table to front and connect both the table-> save all
...
Go to HANA studio-> Expand Schema -> Tables -> Verify data
...
Scheduling of Batch Job
You can also schedule a batch job by going to Data Services Management console
...
Choose the repository from left side -> Navigate to 'Batch Job Configuration' tab, where you
will see the list of jobs -> Against the job you want to schedule -> click on add schedule > Enter the 'schedule name' and set the parameters like (time, date, reoccurring etc
...
105
SAP HANA
106
30
...
The main components of this
replication method are the Sybase Replication Agent, which is part of the SAP source
application system, Replication agent and the Sybase Replication Server that is to be
implemented in SAP HANA system
...
It informs R3 Load to transfer initial load to HANA database
...
R3 load on target system imports data
into SAP HANA database
...
The Sybase Replication agent detects any data changes
at time of initial load and ensures every single change is completed
...
This table
log moves data from source system to HANA database
...
All further changes in source system are captured and
replicated from source system to HANA database using above-mentioned method
...
Note: This method only supports SAP ERP system as data source and DB2 as database
...
SAP HANA – DXC METHOD
SAP HANA
Direct Extractor Connection data replication reuses existing extraction, transformation, and
load mechanism built into SAP Business Suite systems via a simple HTTP(S) connection to
SAP HANA
...
It is considered as method for
extraction, transformation, and load with limited capabilities for data extraction
...
You can set an interval when batch job executes example: every 20 minutes
and in most of cases it is sufficient to extract data using these batch jobs at certain time
intervals
...
DXC method reduces complexity of data modeling in SAP HANA as data sends to
HANA after applying all business extractor logics in Source System
...
Limitations of DXC Data Replication
Data Source must have a predefined mechanism for extraction, transformation and
load and if not we need to define one
...
0 or higher with at least
below SP: Release 700 SAPKW70021 (SP stack 19, from Nov 2008)
...
Go to Configuration -> xsengine
...
108
SAP HANA
Enabling ICM Web Dispatcher service in HANA Studio: Go to Configuration ->
webdispatcher
...
It enables ICM Web Dispatcher service in HANA system
...
Setup SAP HANA Direct Extractor Connection: Download the DXC delivery unit into SAP
HANA
...
Import the unit using Import Dialog in SAP HANA Content Node -> Configure XS Application
server to utilize the DXC -> Change the application_container value to libxsdxc
Creating a HTTP connection in SAP BW: Now we need to create http connection in SAP
BW using transaction code SM59
...
Parameters List:
PSA_TO_HDB_DESTINATION: we need to mention where we need to move the
Incoming data (Connection Name created using SM 59)
PSA_TO_HDB_SCHEMA: To which Schema the replicated data need to assign
PSA_TO_HDB: GLOBAL To Replicate All data source to HANA
...
DATASOURCE – Only Specified Data Source are used for
PSA_TO_HDB_DATASOURCETABLE: Need to Give the Table name having the List
of data sources which are used for DXC
...
Replicate the metadata using specified application component (data source version Need to
7
...
5 version data source we need to migrate that
...
Once data source is activated in SAP BW it will create the following Table in Defined
schema:
/BIC/A00 – IMDSO Active Table
/BIC/A40 –IMDSO Activation Queue
/BIC/A70 – Record Mode Handling Table
110
SAP HANA
/BIC/A80 – Request and Packet ID information Table
/BIC/AA0 – Request Timestamp Table
RSODSO_IMOLOG - IMDSO related table
...
Now data is successfully loaded into Table /BIC/A0FI_AA_2000 once it is activated
...
SAP HANA – CTL METHOD
SAP HANA
Open SAP HANA Studio -> Create Schema under Catalog tab
...
Now create file with “ctl” extension with following
syntax:
--------------------------------------import data into table Schema
...
csv'
records delimited by '\n'
fields delimited by ','
Optionally enclosed by '"'
error log 'table
...
ctl’
Check data in table by going to HANA Studio -> Catalog -> Schema -> Tables -> View
Content
112
33
...
It provides driver
to connect HANA system to Excel and is further, used for data modelling
...
SAP HANA supports both query languages: SQL and MDX
...
Excel Pivot tables use MDX
as query language to read data from SAP HANA system
...
MDX supports multidimensional data model and support reporting and Analysis
requirement
...
Existing physical tables and schemas presents the data
foundation for Information models
...
113
SAP HANA
Once the connection is successful, you can choose Package name -> HANA Modeling views
to generate Pivot tables
...
Connection and Session management of
HANA database handles statements that are executed by HANA
...
This calculation model creates an execution plan that generates
standard results for MDX
...
To make MDX connection to HANA database, HANA client tools are required
...
Once installation of HANA client is done,
you will see the option of SAP HANA MDX provider in the list of data source in MS Excel
...
SAP HANA – MONITORING AND ALERTING
SAP HANA
SAP HANA alert monitoring is used to monitor the status of system resources and services
that are running in the HANA system
...
The monitoring component of HANA system
continuously collects information about health, usage and performance of all the
components of HANA database
...
The priority of alert raised in HANA system tells the criticality of problem and it depends on
the check that is performed on the component
...
However, if it reaches 96%, system will raise a high priority alert
...
System monitor is used to check all
key component and services of a HANA system
...
It tells
about Data Disk, Log disk, Trace Disk, alerts on resource usage with priority
...
It also tells about the time when an alert is raised, description of the alert, priority of the
alert, etc
...
Memory and CPU usage
Data backups
117
35
...
It ensures that database can be restored to the most recent committed state after a restart
or after a system crash and transactions are executed completely or completely undone
...
There
are services in HANA system that has their own persistence
...
Why does SAP HANA database need a Persistent Layer?
Main memory is volatile therefore data is lost during a restart or power outage
...
Backup & Restore is available
...
Data and Transaction Log Volumes
Database can always be restored to its most recent state, to ensure these changes to data
in the database are regularly copied to disk
...
Data and logs of a system are stored in
Log volumes
...
This information is stored in data pages, which are called Blocks
...
Log volumes store the information about data changes
...
They are saved to log buffer
when transaction is committed
...
These
regular intervals are called savepoints and by default they are set to occur every five
minutes
...
During this operation changed data is written to disk and redo logs are also saved
to disk as well
...
Redo log entries are written to the
log volumes for all changes to persistent data
...
118
SAP HANA
Frequency of savepoint can be configured by global
...
Savepoints can be initiated by
other operations like database shut down or system restart
...
During the HANA system installation, following default directories are created as the storage
location for data and log volumes:
/usr/sap/
/usr/sap/
These directories are defined in global
...
Note that Savepoints do not affect the performance of transactions executed in HANA
system
...
With HANA
system running on proper hardware, impact of savepoints on the performance of system is
negligible
...
SAP HANA – BACKUP & RECOVERY
SAP HANA
SAP HANA backup and recovery is used to perform HANA system backups and recovery of
system in case of any database failure
...
Backup now option can be used to run data backup wizard
...
Backup Interval Settings
Backint settings give an option to use third party tool for data and log back up with
configuration of backing agent
...
120
SAP HANA
File and Log Based Data Backup Settings
File based data backup setting tells the folder where you want to save the data backup on
HANA system
...
You can also limit the size of data backup files
...
Log backup settings tell the destination folder where you want to save log backup on
external server
...
You can choose backup interval from drop down
...
Backup Interval: It can be in seconds, minutes
or hours
...
If you disable
this log area will continue to fill and that can result database to hang
...
Backup wizard is used to specify backup settings
...
121
SAP HANA
When you click on next -> Review Backup settings -> Finish
It runs the system backups and tells the time to complete backup for the each server
...
Hence, during
recovery, end users or SAP applications cannot access the database
...
122
SAP HANA
As a consequence of a logical error, the database needs to be reset to its state at a
particular point in time
...
How to recover a HANA system?
Choose HANA system -> Right click -> Back and Recovery -> Recover System
Types of recovery in HANA system
Most Recent State: Used for recovering the database to the time as close as possible to
the current time
...
Point in Time: Used for recovering the database to the specific point in time
...
Specific data backup is required for the above type of recovery option
...
Note: To run recovery wizard you should have administrator privileges on HANA system
...
SAP HANA – HIGH AVAILABILITY
SAP HANA
SAP HANA provides mechanism for business continuity and disaster recovery for system
faults and software errors
...
or any hardware failures
...
The following illustration shows the phases of high availability in HANA system:
First phase is being prepared for the fault
...
Data is backed up and stand by systems take over the operations
...
To achieve high availability in HANA system, key is the inclusion of extra components, which
are not necessary to function and use in case of failure of other components
...
SAP HANA
provides several levels of hardware and software redundancies as below:
HANA System Hardware Redundancy
SAP HANA appliance vendors offer multiple layers of redundant hardware, software and
network components, such as redundant power supplies and fans, error-correcting
memories, fully redundant network switches and routers, and uninterrupted power supply
(UPS)
...
SAP HANA Software Redundancy
SAP HANA is based on SUSE Linux Enterprise 11 for SAP and includes security preconfigurations
...
SAP HANA Persistence Redundancy
SAP HANA provides persistence of transaction logs, savepoints and snapshots to support
system restart and recovery from failures, with minimal delay and without loss of data
...
This improves the availability of HANA system by reducing
the recovery time from an outage
...
SAP HANA – LOG CONFIGURATION
SAP HANA
The SAP HANA system logs all the transactions that change application data or the database
catalog in log entries and stores them in log area
...
The log files are available in HANA system and can be
accessed via HANA studio on Diagnosis files page under Administrator editor
...
After a system failure, you may need to redo log entries from log backups to restore the
database to the desired state
...
Configuring Log backup Timeout
The log backup timeout determines the interval at which the log segments are backed up if
a commit has taken place in this interval
...
ini configuration file
...
Automatic log backup
only works if at least one complete data backup has been performed
...
SAP HANA studio can be used to enable/disable the automatic log backup function
...
A full log area can result a database freeze in HANA system
...
ini configuration file
...
SAP HANA – SQL OVERVIEW
SAP HANA
SQL stands for Structured Query Language
...
SQL is used to retrieve
the data, store or manipulate the data in the database
...
Data Manipulation Language (DML)
DML statements are used for managing data within schema objects
...
Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
Data Control Language (DCL)
Some examples of DCL statements are:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
Why do We Need SQL?
When we create Information Views in SAP HANA Modeler, we are creating it on top of some
OLTP applications
...
Database understands only this
language
...
HANA Calculation views can be created in two ways - Graphical or using SQL script
...
How to open SQL console in HANA Studio?
Select the HANA system and click on SQL console option in system view
...
SAP HANA can act both as Relational as well as OLAP database
...
However, when we directly access HANA views using OLAP connection,
then it will act as OLAP database and MDX will be generated
...
SAP HANA – DATA TYPES
SAP HANA
You can create row or Column store tables in SAP HANA using create table option
...
When you create a table, you also need to define attributes inside it
...
The Dimension field tells the length of value and the Key option to define it as primary key
...
Numeric
Character/ String
131
SAP HANA
Boolean
Date Time
Binary
Large Objects
Multi-Valued
The following table gives the list of data types in each category:
Date Time
These data types are used to store date and time in a table in HANA database
...
Default format for a Date data type is YYYY-MM-DD
...
Default format for Time data type is HH: MI: SS
...
Default format for SECONDDATE data type is YYYY-MMDD HH:MM:SS
...
Default format for TIMESTAMP data type is YYYY-MM-DD HH:MM:SS:FFn,
where FFn represents fraction of second
...
Min value: 0 and max value: 255
SMALLINT: stores 16 bit signed integer
...
Min value: -2,147,483,648 and max value:
2,147,483,648
BIGINT: stores 64 bit signed integer
...
40E + 38 and max value: 3
...
Min
value:
1
...
7976931348623157E308
-
Boolean
Boolean data types stores Boolean value, which are TRUE, FALSE
Character
Varchar: maximum of 8000 characters
...
ALPHANUM: stores alphanumeric characters
...
SHORTTEXT: stores variable length character string which supports text search
features and string search features
...
VARBINARY: stores binary data in bytes
...
Large Objects
LARGEOBJECTS are used to store a large amount of data such as text documents and
images
...
BLOB: stores large amount of Binary data
...
TEXT: it enables text search features
...
BINTEXT: supports text search features but it is possible to insert binary data
...
Array
Arrays store collections of value with the same data type
...
133
41
...
They are used to pass
conditions in a SQL query
...
134
SAP HANA
Comparison Operators
Comparison operators are used to compare the values in SQL statement
...
Set Operators
Set operators are used to combine results of two queries into a single result
...
UNION: It combines the results of two or more Select statements
...
INTERSECT: Intersect operation is used to combine the two SELECT statements,
and it returns the records, which are common from both SELECT statements
...
MINUS: Minus operation combines result of two SELECT statements and return only
those results, which belong to first set of result and eliminate the rows in second
statement from the output of first
...
SAP HANA – SQL FUNCTIONS
SAP HANA
There are various SQL functions provided by SAP HANA database:
Numeric Functions
String Functions
Fulltext Functions
Datetime Functions
Aggregate Functions
Data Type Conversion Functions
Window Functions
Series Data Functions
Miscellaneous Functions
Numeric Functions
These are inbuilt numeric functions in SQL and use in scripting
...
ABS: It returns the absolute value of a numeric argument
...
BITCOUNT: It performs the count of number of set bits in an argument
...
BITOR: It perform an OR operation on bits of passed argument
...
BITUNSET: It is used to set bits to 0 in
...
CEIL: It returns the first integer that is greater or equal to the passed value
...
138
SAP HANA
FLOOR: It returns the largest integer not greater than the numeric argument
...
LN: It returns the natural logarithm of the argument
LOG: It returns the algorithm value of a passed positive value
...
Various other numeric functions can also be used: MOD, POWER, RAND, ROUND, SIGN, SIN,
SINH, SQRT, TAN, TANH, UMINUS
String Functions
Various SQL string functions can be used in HANA with SQL scripting
...
CHAR: It returns the character associated with passed ASCII value
CONCAT: It is Concatenation operator and returns the combined passed strings
...
LEFT: It returns the first characters of a passed string as per mentioned value
...
LOCATE: It returns the position of substring within passed string
...
NCHAR: It returns the Unicode character with passed integer value
...
RIGHT: It returns the rightmost passed value characters of mentioned string
...
UCASE: It is identical to UPPER function
...
Other string functions that can be used are: LPAD, LTRIM, RTRIM, STRTOBIN,
SUBSTR_AFTER, SUBSTR_BEFORE, SUBSTRING, TRIM, UNICODE, RPAD
BINTOSTR
Date Time functions
There are various Date Time functions that can be used in HANA in SQL scripts
...
CURRENT_TIME: It returns the current local system time
...
CURRENT_UTCDATE: It returns current UTC (Greenwich Mean date) date
...
CURRENT_UTCTIMESTAMP
DAYOFMONTH: It returns the integer value of day in passed date in argument
...
YEAR: It returns the year value of passed date
...
Most common data type conversion functions used in HANA in SQL scripts:
CAST: It returns the value of an expression converted to a supplied data type
...
TO_TIME: It converts a passed time string to the TIME data type
...
Other similar Data Type conversion functions are: TO_BIGINT, TO_BINARY, TO_BLOB,
TO_DATE, TO_DATS, TO_DECIMAL, TO_DOUBLE, TO_FIXEDCHAR, TO_INT, TO_INTEGER,
TO_NCLOB,
TO_NVARCHAR,
TO_TIMESTAMP,
TO_TINYINT,
TO_VARCHAR,
TO_SECONDDATE, TO_SMALLDECIMAL, TO_SMALLINT
There are also various Windows and other miscellaneous functions that can be used in HANA
SQL scripts
...
Session_User: It returns the user name of current session
140
43
...
There are different SQL
expressions that can be used in HANA:
Case Expressions
Function Expressions
Aggregate Expressions
Subqueries in Expressions
Case Expression
This is used to pass multiple conditions in a SQL expression
...
Example
SELECT COUNT( CASE WHEN sal < 2000 THEN 1 ELSE NULL END ) count1,
COUNT( CASE WHEN sal BETWEEN 2001 AND 4000 THEN 1 ELSE NULL END ) count2,
COUNT( CASE WHEN sal > 4000 THEN 1 ELSE NULL END ) count3 FROM emp;
This statement will return count1, count2, count3 with integer value as per passed condition
...
Aggregate Expressions
Aggregate functions are used to perform complex calculations like Sum, Percentage, Min,
Max, Count, Mode, Median, etc
...
Aggregate Functions: Sum, Count, Minimum, Maximum
...
Common aggregate functions include:
Average ()
Count ()
Maximum ()
Median ()
Minimum ()
Mode ()
Sum ()
141
SAP HANA
Subqueries in Expressions
A subquery as an expression is a Select statement
...
A subquery is used to return data that will be used in the main query as a condition to
further restrict the data to be retrieved
...
There are a few rules that subqueries must follow:
Subqueries must be enclosed within parentheses
...
An ORDER BY cannot be used in a subquery, although the main query can use an
ORDER BY
...
Subqueries that return more than one row can only be used with multiple value
operators, such as the IN operator
...
A subquery cannot be immediately enclosed in a set function
...
Subqueries with the SELECT Statement
Subqueries are most frequently used with the SELECT statement
...
00 |
| 5 | Hardik
| 27 | Bhopal | 8500
...
00 |
+----+----------+-----+---------+----------+
142
44
...
Stored Procedures
are used to achieve certain result across applications
...
These
stored procedures are executed by applications to perform that task
...
It can also result in set of Select statements, which are used by other
Stored Procedures
...
Stored procedures prevent users to see the complexity and details of tables in a
database
...
No need to keep reissuing the individual statements but can refer to the database procedure
...
"ARTICLE_LOOKUP")
as
begin
opt = select * from "EFASION"
...
SAP HANA – SQL SEQUENCES
SAP HANA
A sequence is a set of integers 1, 2, 3, that are generated in order on demand
...
Using AUTO_INCREMENT column
The simplest way in MySQL to use sequences is to define a column as AUTO_INCREMENT
and leave rest of the things to MySQL to take care
...
This will create table and after that it will insert few rows in
this table where it is not required to give record ID because it is auto-incremented by
MySQL
...
02 sec)
mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
-> (NULL,'housefly','2001-09-10','kitchen'),
-> (NULL,'millipede','2001-09-10','driveway'),
-> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0
...
00 sec)
Obtain AUTO_INCREMENT Values
LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that
understands how to issue SQL statements
...
PERL Example
Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a
query
...
The following example references it through the
database handle:
$dbh->do ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};
PHP Example
After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by
calling mysql_insert_id( ):
mysql_query ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);
Renumbering an Existing Sequence
There may be a case when you have deleted many records from a table and you want to
re-sequence all the records
...
If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way
to do it is to drop the column from the table, then add it again
...
Following is the example where MySQL will start sequence
from 100
...
146
46
...
Triggers are, in fact, written to be executed in response to any of the following
events:
A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
...
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)
...
Benefits of Triggers
Triggers can be written for the following purposes:
Generating some derived column values automatically
Enforcing referential integrity
Event logging and storing information on table access
Auditing
Synchronous replication of tables
Imposing security authorizations
Preventing invalid transactions
147
47
...
They are used to
protect client applications from the changes made to name or location of an object
...
Create Synonym statement is used create a Synonym for a table, view, package, procedure,
objects, etc
...
To access this from Server2, a
client application would have to use name as Server1
...
Customer
...
To address these we can create a synonym of Customer table Cust_Table on Server2 for
the table on Server1
...
Now, if the location of this table changes, you will have to modify
the synonym to point to the new location of the table
...
Public Synonyms
Public Synonyms are owned by PUBLIC schema in a database
...
They are created by the application owner for the
tables and other objects such as procedures and packages so the users of the application
can see the objects
...
Customer;
To create a PUBLIC Synonym, you have to use keyword PUBLIC as shown
...
Private synonyms can be referenced only by the schema that owns the table or object
...
Customer;
148
SAP HANA
Drop a Synonym
Synonyms can be dropped using DROP Synonym command
...
Syntax
DROP PUBLIC Synonym Cust_table;
DROP Synonym Cust_table;
149
48
...
They are used
to evaluate execution plan that SAP HANA database follows to execute the SQL statements
...
To use
Explain Plan, passed SQL query must be a data manipulation language (DML)
...
EXPLAIN PLAN TABLE in database
EXPLAIN PLAN_TABLE in database consists of multiple columns
...
COLUMN SEARCH value tells the starting position of column engine operators
...
To create an EXPLAIN PLAN STATEMENT for a SQL query
EXPLAIN PLAN SET STATEMENT_NAME = ‘statement_name’ FOR
To see values in EXPLAIN PLAN TABLE
SELECT Operator_Name, Operator_ID
FROM explain_plan_table
WHERE statement_name = 'statement_name';
To delete a statement in EXPLAIN PLAN TABLE
DELETE FROM explain_plan_table WHERE statement_name = 'TPC-H Q10';
150
49
...
It is used to remove incorrect, incomplete data and prevent data quality problems before
they are loaded in Data warehouse
...
It helps in understanding the source data better
...
It is used with Extraction, Transformation and Loading task
...
You can use the Data Profiling task inside an Integration Services package to profile data
that is stored in SQL Server and to identify potential problems with data quality
...
Access Requirement
To run a package contains Data Profiling task, user account must have read/write
permissions with CREATE TABLE permissions on the tempdb database
...
The Data Profile Viewer also
supports drilldown capability to help you understand data quality issues that are identified
in the profile output
...
Data Profiling Task Setup and Reviewing
Setting up the Data Profiling Task
It involves execution of a package that contains Data Profiling task to compute the profiles
...
Reviewing the Profiles
To view the data profiles, send the output to a file and then use the Data Profile Viewer
...
151
SAP HANA
Data Profiling: Configuration Options
The Data Profiling task has these convenient configuration options:
Wildcard columns
While configuring a profile request, the task accepts ‘*’ wildcard in place of a column name
...
When the task runs, the task profiles every column that has an appropriate
data type
...
A Quick Profile profiles a table or
view by using all the default profiles and settings
...
Five of these profiles can
check individual columns and the remaining three analyze- multiple columns or relationships
between columns
...
xsd schema
...
152
50
...
SQL Script is known as collections of SQL extensions
...
SQL Script supports stored Functions and Procedures and that allows pushing complex parts
of Application logic to database
...
Using SQL Scripts in place of single query enables Functions to return
multiple values
...
SQL Script provides control logic that is not available in single SQL statement
...
Calculations are executed at database layer to get benefits of HANA database like
column operations, parallel processing of queries, etc
...
Output parameters must be of table types
...
SQL Scripts with Calculation Views
SQL script are used to create script based Calculation views
...
Define output structure, activation of view creates table
type as per structure
...
Expand the content node -> Select a package where you want
to create the new Calculation view
...
Select calculation view type -> from Type dropdown list, select SQL Script -> Set
Parameter Case Sensitive to True or False based on how you require the naming convention
for the output parameters of the calculation view -> Choose Finish
...
Choose SQL Script node in the Semantics node -> Define the output structure
...
Add the required output parameters and specify its
length and type
...
155
SAP HANA
In the Source pane, choose the columns that you want to add to the output -> To add
selective columns to the output, then select those columns and choose Add
...
Activate the script-based calculation view: In the SAP HANA Modeler perspective: Save
and Activate - to activate the current view and redeploy the affected objects if an active
version of the affected object exists
...
Save and activate all - to activate the current view along with the required and affected
objects
...
In the context menu, select Start of the navigation path Team Next
navigation step Activate End of the navigation path
...
156