22 September, 2020
To monitor the performance of views, we can use two tools
1) Explain plan
2) To administration control.
It is a table to maintain details of views that are used to evaluate the execution plan that the SAP HANA DB follows to excursive the SQL statements. The result of the evaluation is stored into the EXPLAIN – PLAN - table view for later user examination
The SQL statement must be a data manipulation statement. Thus a schema definition language stunt con not be used with the EXPLAIN PLAN command
We can obtain a SQL plan from EXPLAIN – PLAN – TABLE view. All users shy the view. Here is an example of reading an SQL plan from the view.
Inclined to build a profession as SAP HANA Developer? Then here is the blog post on, explore "SAP HANA Training"
DELETE FROM Explain – plan – table WHERE statement name = ‘TPc – H Q10 Explain Plan set statement – NAME = ‘TPC – H Q10’ for SELECT TOP 20 C- Cust keys C- name Sum (1 – enfolded price * (1-1___ discount) AS revenues C- acct bal, N – Name, C – address, C – phone, C – connect FROM Customer, Orders, Line item, Nation WHERE C_Cust key = O_custkey AND 0_ORDINATE <> = ‘1993 – 10 – 101’ AND 0_ordinate < - ADD _Months (‘1993-10-01’3) GROUP by C _ cust key: C_name, C_ acetbol, C_Phone N_name C_address, C-connect ORDER By We venue DESC; SELECT operator _name, operator – details, table – name FROM EXPLAON – plan – table WHERE statement – name = ‘TPc – H QSO’; This table contains Statement _ name Operator _name Operator _ details Schema – Name Table _name Table – Type Table – Size Out put –Size Subtee – cost operator _ ID Parent – operator – ID Level position Host Port Timestamp connection _ ID Execution – Engine
To run this console. Go to the HANA install directory and execute SAP admin. SH
It is used to trace fro loading/analysis performance & provides landscapes, tables, views, …….. etc
Procedure click on plan execution (tab) – click on start (to tray)
Loading / analytic performer
And for every trace system will create.TPT in HANA DB working directory
It gives overviews of all plans.
SQL syntax: CREATE SCHEMA <Schema – name > [owned by <User-name>]
Description: The Create schema statement creates a schema in the current database
Example: CREATE SCHENA my – schema owned by the system;
SQL schema syntax: SET SCHEMA <SCHEMA- NAME>
Parameters: <schema_name> :: = string literal which specie files schema name
Description: We can change the current schema of the session. The current schema is used when database objects names such as table names are not prefixed with the schema name.
CREATE Table: CREATE[<table-tyupe>] TABLE <table-name> <table-contents – socrce.>[ <table_create_option_list>] [WITH PARAMETERS ( < parameter – key – value – list)]
Example: Create column table abc. (schema name) company (table) (int primary key, chp – Name varchar 2 (20))
This is for creating column table
EX:- To create row table abc. Company ((MP number int primary key, CMP – Name varchar (20))
Import:- To import from specified directory ;
SQL Syntax: IMPORT <TABLE – NAME – LIST> as < import _format> format <path> [with <options – name-list] [threads <number – of – threads]
<Table_name_list> :: = ,table – name>, …. | ALL
<import – format> :: = BINARY | csv
<option – name – list> :: = <option – name>,….
<option – name> :: = REPLACE | CATALOG ONLY
<path> :: = ‘full –path
<number-of – threads> :: > integrator to indicate the no. of threads
Export <table – name-list> AS <Export – format> INTO <path>
[with <option- name- list>] Threads <number-of-thereads>]
<table – name-list>:: = <table – name>,……. | All
<Export – format> :: = BINARY | CSV
<OPTION – NAME- LIST> :: = OPTION – NAME>,…….
<OPTION – NAME> :: = REPLACE | CATALOG ONLY | SCRAHBLE [BY <PASSWORD>]
<Path> :: = A string literal to specify the path
<number-of-threads> :: = Integer to specify the number the threads
The SAP HANA column store engine has been optimized for the most frequent pattern of OLAP greenies in the form of single block SPJG (select, project, join & group by). This selection lists high – cost features that are best avoided when formulating SQL – queries to bobbin the best performance from the column engine
Slower query Select * From T
WHERE TO _DATA (Date_string, “yyyymmdd’) = CURRENI_DATE)
Faster Query Select * from T
Where to – DATE (date-string) = current date;
Implicit type casting:
SAP HANA could perform type casts implicitly even if the user did not explicitly specify a type cast operation.
For Eg: if there is a comparison between a VARCHAR value and a DATE value, the database system performs an implicit type cast operation to convert the
VARCHAR Value into a DATE value. Implicit type casting is done from lower – precedence types to higher procedure types.
We can find type precedence rules in the SAP HANA SQL reference.
Slower Query SELECT * FROM
WHERE date – string <current – Date:
Faster Query Select* from
Where date – string <to_char(current – date, ‘yyyymmdd’);
In other words, the column (not natively support join) engine supports only engines natively
Select H year, M. month, SUM (T.ship-amount) From T JOIN M No T.Ship – Date BETWEEN H. first – date and M last – date Group By M.Year, M months
Select M.Yerar, M.Month, SUM (T. ship – amount) From T Join M ON Extract (Year from t-ship _date)= M.year and Extract (a month from T.Ship – date)= M.month group by M.year, m. month
Filter predicate inside outer Join predicate
This is used for inside outer join predicates and they cannot be sifted below the Join operation
Slower Query Select * from
Left joins on T.a = s.a and T.b=1;
Faster Query Select - * from Left joins on T.a = S.a and t.b = s.one;
DDL for faster query Alter tables
Add (one integer Generated always As 1);
It is a reporting tool to generate different flowers
After modeling views & tables we need to provide them as sconces to BO,
So, that we need to integrate SAP BO, & SAP HANA with JDBC & DDBC connection
In BO flow
SAP (HANA) DB (Views & tables)
In BD we need to connect semantic layers first designed a structure based on reports (a structure is nothing but the structure of the table we need to join them
After joining the semantic layers, we need to create reports
NOTE:- For HANA we can create only crystal reports web, explorer, dashboards only
For in-depth knowledge on SAP HANA, click on below
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .