This is one of the excellent features started by SAP to jot down the ideas and in turn use them for innovation..
Normally Ideas get rejected due to one of the followoing :

1.Missing business needs from real customers

2.Lack of realistic data

3.Scope of idea

The SAP HANA Idea Incubator is a crowdsourcing program that connects SAP customers to SAP HANA innovators to build new prototypes for customer edge ideas on SAP HANA.  Iterative prototyping process can help our customers to build an application more efficiently when they get the resources.  This program  has expanded rapidly from North America to customers in APJ.  Several SAP HANA customers and innovators have already used the SAP HANA Idea Incubator to connect and build new prototypes to explore new use cases of SAP HANA and further advance their knowledge of how SAP HANA can be used in the marketplace.


 

The latest version of SAP River was recently released as part of SAP HANA SPS 08. I wanted to point out a set of documentation located at: help.sap.com/river

 

The documentation set includes:

  • SAP River Developer Guide: Tells you how to set up a user and set up SAP HANA studio in order to create HelloWorld in SAP River.
  • SAP River Language Reference: Describes the SAP River language, as well as the OData calls you can make against your SAP River backend.
  • SAP River Tutorials: 5 tutorials that explain different features of SAP River; comes with full source code and step-by-step instructions,
  • SAP River XS JavaScript Reference: JSDocs for the APIs for accessing SAP River objects from XS JavaScript.
  • SAP River Python Examples: Examples of making OData calls from Python code; includes a small sample app and all the Python code you need.

 

Also check out the new SAP River videos from HANA Academy for version SPS 08. Towards the end of the playlist, there are new videos related to SPS 08, which you should probably watch first.

Introduction

 

Recently, we have created a number of new tutorial videos for the SAP HANA Academy to show new features and functionality introduced with SAP HANA Support Package Stack (SPS) 08. You can find them collected on the YouTube playlist: SPS 08 - What's New.

 

To highlight some, Philip added 19 new videos to the playlist on the Predictive Analysis Library, 5 new videos to the SAP River playlist, 12 new videos on SAP River RDE, while Bob recorded a nice introduction to SAP HANA Answers and provided an update to the playlist on SAP HANA smart data access.

 

In this blog, my focus will be on what's new for SAP HANA SPS 08 on the topic of installation and update.

 

If you want a complete overview of what’s new in SAP HANA SPS 08, I recommend Michael Eacrett's Blog: What's New in SAP HANA SPS 08 | SAP HANA on the SAP HANA community site.

 

SAP HANA Master Guide

 

Most of you will be familiar with the SAP HANA Master Guide, the central starting point for the technical implementation of SAP HANA. In this guide you will find information about the architecture, software components and deployment scenarios.

 

In the past, we have seen only minor updates to the Master Guide from release to release, but for SPS 08, the guide has been significantly modified on a number of chapters. The introduction has been improved and sample use cases, architecture configurations, and deployment options are now included. The curious section about how to configure the Excel client ODBO driver has finally been removed and related information, SAP Notes, etc. has been moved to the Appendix, where it belongs.

 

In this SAP HANA Academy video, I provide a brief overview about the new Master Guide, which also serves as an introduction for those not yet familiar with HANA.

 

 

SAP HANA Server Installation

 

With SPS 08, the information from the Update and Configuration Guide and the Installation Guide has been merged into a single Installation and Update Guide. The update information now includes updating a SAP HANA system and components from both the SAP HANA lifecycle management tool hdblcm(gui) and the SAP HANA lifecycle manager.

 

Initially, SAP HANA was available only as an appliance with preconfigured hardware and preinstalled software, provided by a SAP hardware partner. With the introduction of Tailored Data Center Integration (TDI FAQ) more flexibility, openness and freedom of choice was given for the SAP HANA customer. See also: SAP Certified Appliance Hardware for SAP HANA

 

As of SPS 08, RedHat Enterprise Linux (RHEL) is now a supported operating system and a specific Configuration Guide about how to prepare your RHEL system for HANA has been added. The guide is attached to SAP Note 2009879 - SAP HANA Guidelines for Red Hat Enterprise Linux (RHEL) Operating System.

 

In this HANA Academy tutorial video on HANA server installation, I discuss general preparation topics ...

 

 

... and in the next video get hands-on with preparing a RHEL system for HANA using the guide.

 

 

Some new features have been introduced for the server installation as well with SPS 08:

  • Specify the maximum memory allocation for a new system during installation
  • Specify the usage type of the system to be installed

 

We can see this in action in this HANA Academy tutorial video on performing an interactive installation using Lifecycle Manager (hdblcmgui).

 

 

Scripted installations using a parameter file and a password file is discussed in the video on automating installations:

 

 

System Usage is discussed in this tutorial video (SPS 08 - What's New playlist as the topic is not restricted to the installation)

 

 

 

 

SAP HANA Client Installation

 

As of SAP HANA SPS 08, Windows 2012 R2 is included as a supported operating system for the SAP HANA client, and as client interface Microsoft ADO.NET has been added to complement ODBC, JDBC and ODBO (Excel OLEDB for OLAP driver).

 

ADO.NET sample code is included in the SAP HANA Developer Guide and in the SAP HANA Data Provider for Microsoft ADO.NET Reference.

 

In this tutorial video, the SAP HANA Client installation on Windows is demonstrated ..

 

 

... and on Linux

 

 

 

SAP HANA Studio Installation

 

It is now possible to configure role-based SAP HANA studio features. The role-based feature sets are administration, database development, and application development. This allows for a more focused SAP HANA studio environment with targeted menu's.

 

Feature-based SAP HANA studio is demonstrated in this tutorial video.

 

 

SAP HANA Answers

 

New with SPS 08 is SAP HANA Answers, a knowledge hub for SAP HANA that can be integrated with SAP HANA studio. When installed it provides a new view in SAP HANA studio allowing you to find answers to questions based on your context or ask questions to the community.

 

SAP HANA Answers is explained in this playlist by Bob. Below the first video of that list.

 

 

Uninstall SAP HANA

 

The SAP HANA Lifecycle Management tool (HLM) nicely bundles common post-installation configuration tasks, including uninstall. In the video below we see the tool in action in both graphical mode [hdblcmgui] and command line mode [hdblcm] for scripted automation.

 

 

 

The last videos shows common post-installation tasks like making an initial backup, creating a user store key, and starting, stopping and getting process information using the command line.

 

 

Thank you for watching

 

You can view more free online videos and hands-on use cases to help you answer the What, How and Why questions about SAP HANA and Analytics on the SAP HANA Academy at youtube.com/saphanaacademy or follow us on Twitter @saphanaacademy.

Workshop.gif

Are you looking to gain an in-depth understanding of the SAP HANA Platform?  Do you plan to build, integrate, and certify an application with SAP HANA?

 

SAP-Partners.gif

The SAP Integration and Certification Center (SAP ICC) will be offering partners and ISVs an SAP HANA Enablement Workshop.  This introductory 4-day workshop will take place on Monday, July 28, 2014 to Thursday, July 31, 2014 at SAP Labs in Palo Alto, California will facilitate a general understanding of SAP HANA.  After each training module, partners and ISVs will reinforce their skills via a series of hands-on exercises to demonstrate their knowledge of the various components for the SAP HANA Platform.  This enablement workshop will outline the underlying knowledge needed to allow for the development, integration, and certification of an application with SAP HANA.

 

By attending this enablement workshop, you'll be able to:

  • Understand the end-to-end native application development for SAP HANA
  • Reinforce knowledge with skill-builder modules via hands-on exercises
  • Understand the certification roadmap and process for application certification
  • Leverage a 30% discount for application certification to enable Go-to-Market
  • Engage with product experts and certification team via Q&A sessions

 

 

Registrations Fees and Deadlines:

 

Due to the popularity of this enablement workshop, seating will be limited and registration will be on a first-come, first-served basis.  If you would like to make a group booking, please submit separate registrations for each individual of your organization.

 

INDIVIDUAL REGISTRATION
REGISTRATION TYPESDATESFEESREGISTRATION
Early BirdBefore June 27, 2014$2,000.00 USDSign-up here!
RegularBefore July 28, 2014$3,000.00 USDSign-up here!
GROUP REGISTRATION - THIRD OR MORE GET A DISCOUNT
REGISTRATION TYPESDATESFEESREGISTRATION
Early BirdBefore June 27, 2014$1,500.00 USDSign-up here!
RegularBefore July 28, 2014$2,500.00 USDSign-up here!

 

Event Logistics and Agenda:


DATESMonday, July 28, 2014 to Thursday, July 31, 2014
TIME9:00 AM to 5:00 PM (Pacific)
LOCATION

SAP Labs

3410 Hillview Avenue

Palo Alto, CA 94304

Building 2 - Baltic Room

 

The agenda for this enablement workshop will highlight some of the following topics:

  • Introduction to SAP HANA Development Platform
  • SAP HANA Application Development Tools: SAP HANA Studio and Eclipse
  • Introduction to SQL Basics and Debugging
  • Introduction to SAP HANA Native Development
  • Introduction to Data Modeling with SAP HANA
  • Points of Integration with SAP HANA
  • SAP HANA Readiness Assessment
  • How to Certify your Application with SAP HANA
  • Q&A Session: SAP HANA Product Experts
  • Q&A Session: SAP Integration and Certification Center

 

Take advantage of this opportunity to plan, build, and explore the various certification programs for SAP HANA and leverage a 30% discount when submitting an application for certification with the SAP HANA Platform!  For any questions or inquiries relating to this enablement workshop, please contact icc-info@sap.com.

Hi All,


I came across this scenario, thought of sharing so that will help others as well.


In Calculation view, when the name of Projection node is same as Table name added, activation fails with error.


Scenario: Add two projection nodes and add tables VBAK and VBUP to them. Name the projection nodes as same as table names i.e., VBAK and VBUP. Join the projection nodes and activate the view. Activation failed with error

"Floating Node VBAK found in this Calculation View"

"Floating Node VBUP found in this Calculation View"

 

After activation, when the view is closed and opened again, projection nodes are left as orphans in the model and the tables VBAK and VBUP are directly considered into the join.

 

Workaround: Change the projection node names other than VBAK and VBUP. Changing projection names to lowercase also works i.e., vbak and vbup. Or give meaningful names as TAB_VBAK and TAB_VBUP.

 

This has been proposed in SAP idea incubator, here. Hope this feature will be enabled in the next versions.

 

Regards,

Chandra.

     We have a Chinese version(http://scn.sap.com/community/chinese/hana/blog/2014/06/16/sap-hana%E4%B8%ADgrouping-sets-rollup-cube%E7%9A%84%E4%BD%BF%E7%94%A8) of this document.

    In the SELECT query sentences, you can use GROUP BY sentence to group the table using the content of one or more columns and use aggregation function on groups. And here you can use GROUPING SETS to generate results of multiple groups of data in a single statement. The result is equivalent to UNION ALL the result of each group.

     Here will introduce the usage of GROUPING SETS, ROLLUP and CUBE.

Syntax

SELECT <attributes> [, grouping_id(<attributes>) ]   [, text_filter(dimensional_attribute) ]

FROM …

GROUP BY GROUPING SETS [BEST n] [LIMIT n] [OFFSET n]

[WITH SUBTOTAL] [WITH BALANCE] [WITH TOTAL]

[TEXT_FILTER <filterspec> [FILL UP [SORT MATCHES TO TOP]]]

[STRUCTURED RESULT [WITH OVERVIEW] [PREFIX ‘value’]]

( <group>, …, <group> )

HAVING <having-filter>;

 

<group> := <attribute> |( <attribute>, …, <attribute> ) |(( <attribute>, …, <attribute> ) ORDER BY <attribute> [ASC] [DESC])

 

GROUPING SETS

     We can use GROUPING SETS to point out the columns combination used to group, which is called grouping sets.

For example, GROUPING SETS(A, B, (C,D)) represents the UNION ALL of the result  of grouping by A, by B and by C,D respectively.

 

     ROLLUP and CUBE are the special forms of GROUPING SETS.

 

ROLLUP

For example

     ROLLUP(A,B,C) is equivalent to GROUPING SETS((A,B,C),(A,B),A,()).

     That is to say, the number of grouping sets in ROLLUP is n+1

         

CUBE

For example:

     CUBE (A,B,C) is equivalent to GROUPING SETS((A,B,C),(A,B),(A,C),(B,C),A,B,C,()).

     Then, the number of grouping sets in CUBE is 1+C(n,1)+C(n,2)+…+C(n,n-1)+1=2n.

 

Examples

     Here we design a table name SALES.

createcolumntable sales ( id int primarykey, customer varchar(5), year int, product varchar(5), sales int ); 
insert into sales values(1, 'C1', 2009, 'P1', 100); 
insert into sales values(2, 'C1', 2009, 'P2', 200); 
insert into sales values(3, 'C1', 2010, 'P1', 50); 
insert into sales values(4, 'C1', 2010, 'P2', 150); 
insert into sales values(5, 'C2', 2009, 'P1', 200); 
insert into sales values(6, 'C2', 2009, 'P2', 300); 
insert into sales values(7, 'C2', 2010, 'P1', 100); 
insert into sales values(8, 'C2', 2010, 'P2', 150);
select * from sales;

1.png

GROUPING SETS

select customer, year, product, sum(sales) from sales 
groupbyGROUPING SETS ( customer, year,product ); 

is equivalent to

select customer, NULL, NULL, sum(sales) from sales groupby customer 
union all
select NULL, year, NULL, sum(sales) from sales group by year
union all
selectNULL, NULL, product, sum(sales) from sales groupby product;

2.png

 

3.png

 

ROLLUP

select customer, year, sum(sales) from sales 
groupby ROLLUP(customer, year); 

is equivalent to

select customer, year, sum(sales) from sales groupbygrouping sets ( (customer, year), (customer) ) 
union all
selectNULL, NULL, sum(sales) from sales;

4.png

 

5.png

 

CUBE

select customer, year, sum(sales) from sales 
groupby CUBE(customer, year);

is equivalent to

select customer, year, sum(sales) from sales 
group by grouping sets ( (customer, year), (customer), (year) ) 
union all 
selectNULL, NULL, sum(sales) from sales;

6.png

 

7.png

 

grouping_id()/grouping()

     grouping_id() returns a integer for each grouping set to distinguish them.

     grouping() also returns a integer, and its parameter must be the name of one column. It presents if this column is used to group, returning 0 or 1.

 

select customer, year, sum(sales),grouping_id(customer, year),grouping(customer),grouping(year) from sales 
groupby ROLLUP(customer, year); 

8.png

select customer, year, sum(sales),grouping_id(customer, year),grouping(customer),grouping(year) from sales 
groupby CUBE(customer, year);

9.png

Notice: in the example of CUBE, there are two columns, CUSTMOER and YEAR in grouping_id(). Then we can assume there is a binary number, the low present the column YEAR and the high present the column CUSTOMER. If in one grouping set this column is used to group, this column is set 1, if not set 0. Then return a integer as the result.

 

BEST

BEST nn can be positive number, negative number or 0. Returns only the top-n grouping sets sorted in descending order of the number of rows aggregated in each grouping set. Negative number means sorting in ascending order and 0 means not using the BEST keyword.

select customer, year, product, sum(sales) 
from sales groupby cube BEST 3 ( customer, year, product );

10.png

LIMIT OFFSET

LIMIT n [OFFSET m]limit the returned grouped records and offset number for each grouping set.

select customer, year, product, sum(sales) 
from sales 
groupbygrouping sets LIMIT 2 OFFSET 1 ( (customer, year), (product) );

11.png

WITH SUBTOTAL/WITH BALANCE/WITH TOTAL

This can be used with the keyword LIMIT OFFSET, adding an additional subtotal in each grouping set. Using WITH SUBTOTAL, a subtotal of the part of LIMIT OFFSET will be returned. And the last will be returned when using WITH BALANCE. WITH TOTAL means to return the aggregated total value for each grouping set.

select customer, year, product, sum(sales) 
from sales groupbygrouping sets LIMIT 2 WITH SUBTOTAL WITH BALANCE WITH TOTAL( (customer, year), (product) );

12.png

TEXT_FILTER

TEXT_FILTER <filterspec> means to filter the content on the grouping columns. Here we can use  wildcard characters, such as *, ?, and logical operators, such as OR, AND, NOT, AND NOT.

select customer, year, product, sum(sales), text_filter(customer), text_filter(product) 
from sales groupbygrouping sets TEXT_FILTER '*2' ( (customer, year), (product) );

13.png

FILL UP

FILL UP can be used with TEXT_FILTER to return all the rows in each grouping set. And we can use text_filter() function to displays matching values.

select customer, year, product, sum(sales), text_filter(customer), text_filter(product) 
from sales groupbygrouping sets TEXT_FILTER '*2' FILL UP ( (customer, year), (product) );

14.png

 

SORT MATCHES TO TOP

This is used with FILL UP to order the matching values to the top of each grouping set.

select customer, year, product, sum(sales), text_filter(customer), text_filter(product) 
from sales groupbygrouping sets TEXT_FILTER '*2' FILL UP SORT MATCHES TO TOP ( (customer, year), (product));

15.png


STRUCTURED RESULT [WITH OVERVIEW] [PREFIX ‘value’]

     Return results as temporary tables. Using WITH OVERVIEW, an overview temporary table will also be returned. Using PREFIX can specify the prefix for the tables. The prefix must start with “#”, which means a temporary table. And the default value is “#GN”. #GN0 is the overview table, and #GNn is the result of each grouping table.

select customer, product, sum(sales) 
from sales group by grouping sets structured result WITH OVERVIEW ( product, customer);  
select * from "#GN0"; 
select * from "#GN1"; 
select * from"#GN2";

16.png

 

17.png

 

18.png

 

19.png

MULTIPLE RESULTSETS

MULTIPLE RESULTSETS can set results to be returned in multiple result sets.

select customer, year, product, sum(sales) 
from sales groupbygrouping sets MULTIPLE RESULTSETS ( (customer, year), (product) );

20.png

 

21.png

 

22.png

     We have a Chinese version(http://scn.sap.com/community/chinese/hana/blog/2014/04/02/sap-hana-sqlscript-procedure%E4%B8%AD%E7%9A%84%E5%BC%82%E5%B8%B8%E5%A4%84%E7%90%86) of this document.

     For SQL procedure using SQLScript, SAP HANA provides corresponding methods of exception handling. There are three tools can be used: EXIT HANDLER, CONDITION, SIGNAL or RESIGANL.

     An example using EXIT HANLEDR shows as follows.

CREATETABLE MYTAB (I INTEGERPRIMARYKEY); 
CREATE PROCEDURE MYPROC1 
AS BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
INSERT INTO MYTAB VALUES (1); 
INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301 
END; 

     When invoking this procedure, an exception of violating unique constraint will be thrown, for inserting the same content on primary key. Then the invocation well be suspended, and the subsequent operations will not be executed. After suspending the procedure, the action operations of EXIT HANDLER will be executed.

 

     Notice: We can use  "::SQL_ERROR_CODE","::SQL_ERROR_MESSAGE" to get the SQL ERROR CODE and related error message of the caught exception.

EXIT HANDLER

When declare an EXIT HANDLER in SQLScript Procedure, the syntax shows as below.

DECLARE EXIT HANDLER FOR <proc_condition_value_list> <proc_stmt>;
<proc_condition_value_list> ::= <condition_value> [ { <comma> <condition_value> }... ]
<condition_value> ::= <sql_error_code> | <condition_name> | SQLEXCEPTION
<sql_error_code> ::= SQL_ERROR_CODE <numeric_literal>

     We can declare an EXIT HANDLER to handle exceptions of the given SQL ERROR CODE or user defined condition in SQL procedures and provide the subsequent operations after the exception is thrown.

Notice

SAP HANA provides common SQL error code

http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a78d3275191014b41bae7c4a46d835/content.htm

or checkSAP HANA SQL and System Views Reference”->1.10  SQL Error Codes

 

CONDITION

     You can declare a CONDITION in SQL procedure to define a condition which contains a SAP HANA error code or used-defined error code and name it. Then you can declare an EXIT HANDLER to handle exception of this condition, or use SIGNAL or RESIGNAL to throw an exception of a condition containing a user-defined error code.

     The syntax of CONDITION declaration

DECLARE <condition name> CONDITION [ FOR <sql_error_code> ];
<sql_error_code> ::= SQL_ERROR_CODE <numeric_literal>

SIGNAL/RESIGNAL

     SIGNAL or RESIGNAL can be used to throw exception in SQL procedure. But here exception only represents used-defined error, whose error code is between 10000 and 19999.

     The difference between SIGNAL and RESIGNAL is that when using SIGNAL, you must point out the SQL ERROR CODE or CONDITION of the exception. But RESIGNAL can be used in the action part of an EXIT HANDLER all alone. That is to say, RESIGNAL can be used to throw exception of the EXIT HANDLER catches to the caller. A user-defined exception can be catch by the corresponding EXIT HANDLER, or caught by the caller, same as an exception contains error code.

The syntax of using SINGAL or RESIGNAL

SIGNAL <signal_value> [<set_signal_information>] ;
RESIGNAL [<signal_value>] [<set_signal_information>];
<signal_value> ::= <condition_name> | <sql _error_code>
<set_signal_information> ::= SET <signal_information_item>
<signal_information_item> ::= MESSAGE_TEXT  <equals operator> <simple_value_specification>
<simple_value_specification> ::= <procedure_expression>

Examples

1.General exception handling

CREATETABLE MYTAB (I INTEGER PRIMARYKEY); 
CREATE PROCEDURE MYPROC 
AS BEGIN 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
       SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    INSERT INTO MYTAB VALUES (1); 
    INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301 
    -- will not be reached 
END; 
CALL MYPROC;

1.png

2.Error code exception handling

CREATETABLE MYTAB (I INTEGERPRIMARYKEY);
CREATE PROCEDURE MYPROC 
AS BEGIN
    DECLARE myVar INT;
    DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299       SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    SELECT I INTO myVar FROM MYTAB; --NO_DATA_FOUND exception
END;
CALL MYPROC;

2.png

The error message of SQL ERROR CODE 1299 is ”No data found”.

3.png

 

3.Conditional Exception Handling

CREATETABLE MYTAB (I INTEGERPRIMARYKEY);
CREATE PROCEDURE MYPROC 
AS BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 301;
    DECLARE EXIT HANDLER FOR MYCOND 
        SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    INSERT INTO MYTAB VALUES (1);
    INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301
    -- will not be reached
END;
CALL MYPROC;

4.png

4.Throw an exception using SIGNAL

 

CREATEPROCEDURE MYPROC 
AS BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE EXIT HANDLER FOR MYCOND 
        SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';
    -- will not be reached
END;
CALL MYPROC;

5.png

5.Throw an exception using RESIGNAL

 

CREATEPROCEDURE MYPROC 
AS BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE EXIT HANDLER FOR MYCOND RESIGNAL;
    SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';
    -- will not be reached
END;
CALL MYPROC;

6.png

6.When using EXIT HANLDER, related tables can be used to store the exception information.

 

CREATETABLE ERROR_LOG
(ERROR_CODE VARCHAR(5),
 ERROR_MSG VARCHAR(200),
 TIME_STAMP TIMESTAMP);
CREATE PROCEDURE MYPROC 
AS BEGIN
DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
DECLARE EXIT HANDLER FOR MYCOND signal mycond set message_text = 'error'; 
 SIGNAL MYCOND;
-- will not be reached
END;
CREATE PROCEDURE MYPROC1 
AS BEGIN
DECLARE EXIT HANDLER FOR sqlexception
INSERT INTO ERROR_LOG VALUES(::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE,CURRENT_TIMESTAMP);
CALL MYPROC;
END;    
CALL MYPROC1;

7.png

     We have a Chinese version(http://scn.sap.com/community/chinese/hana/blog/2014/03/12/sap-hana-%E5%9F%BA%E4%BA%8Eweb%E7%9A%84ide%E7%9A%84%E9%85%8D%E7%BD%AE%E5%8F%8A%E4%BD%BF%E7%94%A8) of this document.

     For SAP HANA developer, SAP HANA Studio is a common client tool to develop or manage on SAP HANA. At the same time, SAP HANA XS engine also provides a web-based development environment. Only using a web browser, you can perform some simple development. When you either cannot install and use SAP HANA studio locally or you need to develop from a remote location, you can use this IDE.

This web-based IDE is called SAP HANA Web-based Development Workbench, which contains four modules:

  • Editoroperate objects in HANA repository.
  • Catalogmanage objects in catalog.
  • Securitymanage users and roles.
  • Tracecheck or download trace files of XS applications.

Notice: In the version of SPS06, SAP HANA XS provides two web-based IDE tools: HANA IDE (Integrated Development Environment) lite to perform simple operations and SAP HANA Web-Based Development Workbench for complex operations. In the version of SPS07, the two tools are merged to the new tool, SAP HANA Web-based Development Workbench.

 

Usage and Privilege      

     After installing SAP HANA server, the SAP HANA Web-based Development Workbench is installed by default. You can use the link http://<WebServerHost>:80<SAPHANAinstance>/sap/hana/xs/ide to access it. The user to logon need to be granted the related roles or privilege in advance. The user granted the system role sap.hana.xs.ide.roles::Developer can access all the modules of SAP HANA Web-based Development Workbench.

1_副本.png

     For each module, here are the links and related roles:

module

link

role

Editor

http://<WebServerHost>:80<SAPHANAinstance>/sap/hana/xs/ide/eidtor

sap.hana.xs.ide.roles::EditorDeveloper

Catalog

http://<WebServerHost>:80<SAPHANAinstance>/sap/hana/xs/ide/catalog

sap.hana.xs.ide.roles::CatalogDeveloper

Security

http://<WebServerHost>:80<SAPHANAinstance>/sap/hana/xs/ide/security

sap.hana.xs.ide.roles:: SecurityAdmin

Trace

http://<WebServerHost>:80<SAPHANAinstance>/sap/hana/xs/ide/trace

sap.hana.xs.ide.roles::TraceViewer

     sap.hana.xs.ide.roles::Developer are the collection of the other four roles, containing all the privileges.

 

Editor

     In Editor, you can maintain and execute all the objects in HANA repository. Here you can create, edit and run all objects, containing packages and applications.

     According to this example, we create a simple SAP UI5 project and introduce the main features of Editor.

     Firstly, you can create a new package:

2.jpg

     In a package, you can use "Multi-File Drop Zone"to add multiple files at the same time.

3_副本.png

     Then, create a new application:

4.jpg

     Fill in the package name and choose a template (here we choose SAP UI5 Hello World).

5_副本.png

     When using this template, .xsaccess, .xsapp and index.html files can be created automatically and you can modify them as your need. Editor doesn’t only provide different templates of SAP HANA XS application, but also provides template for files, such as .hdbtable and .hdbschema files.

6_副本.png

     After modifying, choose “save file” or use Ctrl+S to save the file. And the file is activated in the repository by default. Then choose the html file or xsjs file, and choose “Run on server”or use F8 to run on the browser.

Notice: Editor also supports to save but not activate a file. You can configure in HANA Studio: in Configuration tab, choose xsengine.ini->httpserver, add the parameter developer_mode, and set it ture:

15.jpg

     Then set Editorenable inactive save and enable inactive object execution

16.jpg

     When saving files, you can choose "save file"(Ctrl+S) or"save without activating"(Ctrl+I)

Then to the modified files, you can check the modified version, the corresponding modify time and modifier. And you can compare two versions of the same file.

7_副本.png

     Editor also provides the function of debugging server side javascript files. You can set break point, perform single step, check the value of variables and compute the value of expressions. The user who perform the debug function must have the role of sap.hana.xs.debugger::Debugger.

8_副本.png

Catalog

     Catalog is a tool providing the function of developing and maintaining the runtime objects. The user must own the related privileges of the operated schemas, tables or other objects.

     You can check the definition of tables and execute SQL sentences.

9_副本.png

  10_副本.png

Security

     In Security, you can manage users and role. The user must have the system level privilege of “USER ADMIN”.

In this module, you can create new user or role, check or modify the roles or privileges of a user or a role and change the password of a user.

11_副本.png

 

Trace

     Trace provides the function of checking the trace files of XS applications and setting trace level. The user who sets the trace level must have the system level privilege of “TRACE ADMIN”.

     Add trace sentence in the related files.

12_副本.png

     Set the trace level for the application.

13_副本.png

     You can check and download trace files.

14_副本_副本.png

     We have a Chinese version(http://scn.sap.com/community/chinese/hana/blog/2014/03/03/sap-hana-ce-function%E5%BC%95%E7%94%B3-%E6%80%A7%E8%83%BD%E5%AF%B9%E6%AF%94) of this document.

     In the previous blog, I have introduced the usage of common CE functions simply. And this blog will compare the performance between the CE functions and the corresponding SQL query sentences, using some test scenarios. According to the result of these test scenarios, we can get some tips, which can be chosen to use for SQL tuning in SQL procedures.

 

CE_UNION_ALL

 

     CE_UNION_ALL function is equivalent to the UNION ALL operation of two table variables, which is the combination of two table variables. And then the outcome is bound to another table variable. In the version before SPS6, the operation of UNION ALL on column tables will be transferred to the row engine. But after the version of SPS6, HANA has optimized the UNION ALL operation, and then it can be implemented on the column engine. In addition to pay attention, independent codes can be executed parallelly in SQLScript SQL procedures. So the two parts of UNION ALL operation are generally considered to be executed parallelly.

     For example, the two SQL procedures use CE_UNION_ALL() function and UNION ALL SQL sentence respectively to execute the union operation of two tables. And the data volume of the two tables is 189W and 15W.

dropprocedure union_all_test_1;
create procedure union_all_test_1(out trans transport)
language sqlscript as
begin
 d5300_transport = CE_COLUMN_TABLE("D5300", ["ACT_GI_DATE", "TRANSPORT_ID", "MATERIAL", "BATCH", "SOURCE_PLANT", "SOURCE_STORE", "TARGET_PLANT", "TARGET_STORE", "TOTAL_COST", "WEIGHT"]);
                            
 d5200_transport = CE_COLUMN_TABLE("D5200", ["ACT_GI_DATE", "TRANSPORT_ID", "MATERIAL", "BATCH", "SOURCE_PLANT", "SOURCE_STORE", "DISTR_CHAN", "SALES_OFF", "TOTAL_COST", "WEIGHT"]);
                            
 d5200_transport2 = CE_PROJECTION(:d5200_transport, ["ACT_GI_DATE", "TRANSPORT_ID", "MATERIAL", "BATCH", "SOURCE_PLANT", "SOURCE_STORE","DISTR_CHAN" AS "TARGET_PLANT", "SALES_OFF" AS "TARGET_STORE", "TOTAL_COST", "WEIGHT"]);
                            
 trans = CE_UNION_ALL(:d5300_transport,:d5200_transport2);
end;
drop procedure union_all_test_2;
create procedure union_all_test_2(out trans transport)
language sqlscript as
begin
       
 trans = 
select "ACT_GI_DATE", "TRANSPORT_ID", "MATERIAL", "BATCH", "SOURCE_PLANT", "SOURCE_STORE",  "TARGET_PLANT", "TARGET_STORE", "TOTAL_COST", "WEIGHT" from "D5300"
union all
select "ACT_GI_DATE", "TRANSPORT_ID", "MATERIAL", "BATCH", "SOURCE_PLANT", "SOURCE_STORE", "DISTR_CHAN"  AS "TARGET_PLANT", "SALES_OFF" AS "TARGET_STORE", "TOTAL_COST", "WEIGHT" from "D5200";   
                     
end;

     The processing time of these two SQL procedures shows as follows:

1.jpg

 

2.jpg

     In this test scenario, the processing time of the two methods is similar.

 

CE_AGGREGATION

     The CE_AGGREGATION function groups the input columns and computes aggregation for each group. And it is equivalent to the combination of GROUP BY sentence and aggregation functions.

     For example, the two SQL procedures use CE_ AGGREGATION () function and GROUP BY sentence respectively to execute the group and count operation on a table of which data volume is 1184W.     

dropprocedure groupby_test_1;
create procedure groupby_test_1(out tmp2 groupby)
language sqlscript as
begin
 tmp1 = CE_COLUMN_TABLE("RECORD",["USERID","CAMPID","MEDIAID","TYPE"]);
 tmp2 = CE_AGGREGATION (:TMP1,[COUNT(*) AS CNT], ["USERID","CAMPID","MEDIAID","TYPE"]);
end;
drop procedure groupby_test_2;
create procedure groupby_test_2(out tmp1 groupby)
language sqlscript as
begin
 tmp1 = SELECT COUNT(*) AS CNT,USERID,CAMPID,MEDIAID,TYPE FROM "RECORD" GROUP BY USERID,CAMPID,MEDIAID,TYPE;    
end;

     The processing time of these two SQL procedures shows as follows:

3.jpg

4.jpg

     As the result shows, in this test scenario, the processing time of the two methods is similar.   

CE_JOIN    

     CE_JOIN function implements the INNER JOIN operations of two table variables, using the join condition.

     For example, the two SQL procedures use CE_ JOIN () function and INNER JOIN sentence respectively to execute the inner join operation.  And the data volume of the two parts is 1184W separately.

dropprocedure join_test_1;
create procedure join_test_1(out join_result innerjoin)
language sqlscript as
begin
 tmp1 = CE_COLUMN_TABLE("RECORD",["USERID","CAMPID","MEDIAID","TYPE"]);
 tmp2 = CE_AGGREGATION (:TMP1,[COUNT(*) AS CNT], ["USERID","CAMPID","MEDIAID","TYPE"]);
 tmp3 = CE_PROJECTION(:tmp2,["USERID","MEDIAID" as "MEDIAID1"]);
 tmp4 = CE_PROJECTION(:tmp2,["USERID","MEDIAID" as "MEDIAID2"]);
 join_result = CE_JOIN(:tmp3,:tmp4,["USERID"],["USERID","MEDIAID1","MEDIAID2"]);
                
end;             
drop procedure join_test_2;
create procedure join_test_2(out join_result innerjoin)
language sqlscript as
begin
 tmp1 = SELECT USERID,CAMPID,MEDIAID,TYPE FROM "RECORD" GROUP BY USERID,CAMPID,MEDIAID,TYPE;    
 join_result = select f.userid,f.mediaid as mediaid1,l.mediaid as mediaid2 from :tmp1 as f join :tmp1 as l on f.userid=l.userid;
end;

     The processing time of these two SQL procedures shows as follows:

5.jpg

6.jpg

     We can conclude that, in this scenario, the processing time of CE_JOIN() function is shorter than the processing time of INNER JOIN. Here CE_JOIN() function has the higher performance.

NOTICE

      According to the result of these test scenarios, the CE functions and corresponding SQL sentences have similar performance in most cases. But in particular cases, the CE functions have even higher performance.

     But when using CE functions, something needs to be paid attention. Each CE function need to bind the result to a table variable, that is to say the result need to be materialized. Then for complex operations using CE functions, it may cause performance problem. So we need to balance the performance to choose to user whether CE functions or SQL sentences.

We have a Chinese version(http://scn.sap.com/community/chinese/hana/blog/2014/02/18/sap-hana-ce-function%E7%9A%84%E4%BB%8B%E7%BB%8D%E5%8F%8A%E4%BD%BF%E7%94%A8) of this document.

CE functions are short for Calculation Engine Plan Operators. Like the format of functions, they contain input parameters and output parameters, and encapsulate the operation of data transformation. CE functions are usually used in the definition of procedures with SQLScript. And the operations can be operated in the calculation engine directly. In general, each CE function can be present by a SELECT query sentence. So the operation can be implemented by SQL sentences alternatively.

Here is an example of using CE functions in the definition of a SQLScript procedure.

createprocedure test1(out pro_cat_join pro_cat)
language sqlscript as
begin
 category_tab1 = CE_COLUMN_TABLE("FURNITURE"."CATEGORY");
 category_tab2 = CE_PROJECTION(:category_tab1,["CATEGORYID","NAME" AS "CATEGORYNAME"]);
 product_tab = CE_COLUMN_TABLE("FURNITURE"."PRODUCT");
 pro_cat_join= CE_JOIN(:category_tab2,:product_tab,["CATEGORYID"],["PRODUCTID","CATEGORYID","CATEGORYNAME"]);
end;

The Introduction of  Common CE Functions

Data Source Access Operators: bind the physical tables or column tables to table variables.

1.CE_COLUMN_TABLE

     This function uses a physical column table or a list of column of it as data source to bind it to a table variable. It provides access to a physical table.

     For example: Choose the columns NAME,COLOR,PRICE of table PRODUCT and bind it to the table variable product1.


               product1 = CE_COLUMN_TABLE("PRODUCT",["NAME","COLOR","PRICE"]);

     Equivalent SQL sentence:

 

product1 = select"NAME","COLOR","PRICE"from"PRODUCT"     ;

2.CE_JOIN_VIEW

     This function provides access to an Attribute View by choosing the join view or an optional list of attributes to bind it to a table variable.

     For example: Choose the attributes PRODUCT_NAME,CATEGORY_NAME from Attribute View ATTR_PRODUCT, and bind it to the table variable product2.


               product2 = CE_JOIN_VIEW("ATTR_PRODUCT",["PROCUCT_NAME","CATEGORY_NAME"]);

     Equivalent SQL sentence:

 

              product2 = select"PROCUCT_NAME","CATEGORY_NAME"from"ATTR_PRODUCT";

3.CE_OLAP_VIEW

     This function uses an OLAP view, namely Analytical View, as data source. Then some key figures and dimension are chosen to group the OLAP view and compute the aggregation values. The result will be bound to a table variable.

     For example: use Analytical View as data source, group by CATEGORY_NAME and compute the SUM of AMOUNT. Then bind the result to table variable olap_view.

  

              olap_view = CE_OLAP_VIEW("ANAL_SALE", ["CATEGORY_NAME", SUM("AMOUNT")]);

     Equivalent SQL sentence:

 

olap_view = select CATEGORY_NAME, SUM(AMOUNT) FROM"ANAL_SALE"GROUPBY CATEGORY_NAME;

4.CE_CALC_VIEW

     This function provides access to an Calculation View by choosing the view or an optional list of attributes to bind it to a table variable.

     For example: Choose the attributes QUANTITY,AMOUNT from Calculation View, and bind it to the table variable cal.


               cal = CE_CALC_VIEW("CALC_SALE",["QUANTITY","AMOUNT"]);


     Equivalent SQL sentence:

 

cal = select"QUANTITY","AMOUNT"from"CALC_SALE"     ;

Relational Operators: execute relational operations on calculation engine directly

5.CE_JOIN

     This function is get the inner join result of two tables on a list of join attributes. For the join attributes, only one will contains and they must own the same column name.  if not, you need to rename them in advance. Like the usage of above functions, you can choose a list of columns of the inner join result to be returned.

     For example: inner join table variables :product and :category on the columns ”NAME”, contain column "PRODUCTID","PROJECT_NAME","CATEGORYID","NAME" and  bind the result to table variable product3.


              product3 = CE_JOIN(:project,:category,["CATEGORYID"],["PRODUCTID","PROJECT_NAME","CATEGORYID","NAME"]);

 

     Equivalent SQL sentence:

                                                                       

product3= select P."PRODUCTID",P."PROJECT_NAME",P."CATEGORYID",C."NAME"from :project as P innerjoin :category  as C on P."CATEGORYID"=C."CATEGORYID";

 

6.CE_LEFT_OUTER_JOIN

     This function realizes the function of left outer join. And the syntax is the same as CE_JOIN() function.

 

7.CE_RIGHT_OUTER_JOIN

     This function realizes the function of right outer join. And the syntax is the same as CE_JOIN() function.

 

8.CE_PROJECTION

     This function can restrict the columns of a table variable. And you can also rename columns, calculate expression or apply filters.

     For example: Restrict the columns "PRODUCTID","PRICE","NAME" from table variable :product.Specify a filter that "PRICE" is greater than 50 and rename the column ”NAME” to ”CATEGORYNAME”.

 

              product4 = CE_PROJECTION(:product,["PRODUCTID","PRICE","NAME"AS"CATEGORYNAME"],'"PRICE">50');

     Equivalent SQL sentence:

product4 = select"PRODUCTID","PRICE","NAME"AS"CATEGORYNAME"from :product where"PRICE">50;

9.CE_CALC

     This function is generally used with other CE functions. The usage of it is to compute the value of expressions and bind to a new column. It contains two parameters: the expression and the returned data type. And some functions can be used in the expression, such as conversion functions, string functions, mathematical functions, date functions and so on.

     For example: choose the columns CID,CNAME,OID,SALES of table variable :product, and add the result of  sales multiplies by :vat_rate as a new column with the name “SALES_VAT”.

 

with_tax = CE_PROJECTION(:product, ["CID", "CNAME", "OID", "SALES",CE_CALC('"SALES" * :vat_rate',decimal(10,2)) AS"SALES_VAT"]);

     Equivalent SQL sentence:


with_tax2 = SELECT cid, cname, oid, sales,sales * :vat_rate as sales_vat FROM :product;

10.CE_AGGREGATION

     This function can be used to group the input table variable and compute the aggregation value, such as COUNT SUM MIN MAX.

     For example: Group the table variable :product by the column “YEAR”, and compute the count value of each group.


              product5 = CE_AGGREGATION (:product,[COUNT ("PRODUCT_ID") AS"CNT"], ["YEAR"]);

              Equivalent SQL sentence:

 

product5 = SELECTCOUNT ("PRODUCT_ID") AS cnt, yearFROM :product GROUPBYyear;

11.CE_UNION_ALL

     This function computes the union of two tables which have the same table schema and duplicate rows will be preserved.

     For example:  compute the union of two table variables :product1 and :product2 with duplicate rows containing.


              pro_union_all = CE_UNION_ALL (:product1, :product2);

              Equivalent SQL sentence:

 

pro_union_all = SELECT * FROM :product1 UNIONALLSELECT * FROM :product2;

Special Operators:

12.CE_VERTICAL_UNION

     This function can realize the function of vertical union.  It combines the two table variables, which have the same data volume, to a new table variable. And you can also rename the columns of the result.

     For example: combine the columns "ID", "FIRSTNAME" of table variable :firstname and the column " LASTNAME " of table variable : lastname to a new table variable.

 

out = CE_VERTICAL_UNION(:firstname, ["ID", "FIRSTNAME"AS"GIVENNAME"], :lastname, ["LASTNAME"AS"FAMILYNAME"]);


Particular Usage

  1. Add Serial Number

     When using CE_CALC() function, we can use the rownum() function to return a new column containing the serial number. So use this we can add serial number to a table variable.

     For example:

              product2 = CE_PROJECTION(:product1, ["NAME","COLOR","PRICE",CE_CALC('rownum()',integer) as"ROWNUM"]);

     result:

1.png

 

2.Fetch top n records

     According the previous example, we can use rownum() function to get the serial number and fetch top n records.

     For example: fetch top 5 records of table variable :product1.

 

product3 = CE_PROJECTION(:product1, ["NAME","COLOR","PRICE",CE_CALC('rownum()',integer) as "ROWNUM"],'"ROWNUM"<5');


     result:

2.png

Notices

Because CE functions are executed directly on calculation engine, it may influence the execution of the procedure, in some cases, is more efficient. We should not mix the CE functions with SQL queries to avoid the transformation between different engines, which may influence the performance.

Constriants

By now, some SQL queries can’t be realized by a CE function. For instance, the operation of UNION, there only exist a CE function CE_UNION_ALL() which is equivalent to the operation of UNION ALL. And there isn’t a corresponding CE function of the operation FULL OUTER JOIN. But we can use other CE functions or other methods to implement the operations we want.

 

     About the detailed usage of CE functions, please refer to SAP HANA SQLScript Reference

  http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

Hi Folks,

 

Introduction

 

     I would like to share one idea with you guys . I have seen couple of questions posted in SCN(and outside SCN too) regarding how to create deep entity set , how to update complex structures through OData operations etc. Well, this blog does not talk about handling these scenarios using OData calls but trying to achieving such scenarios using XSJS . Here, I am explaining how we can pass complex JSON structure directly to the XSJS context for processing.

 

 

Designing the UI5

   

     Here we are designing the UI5 application from where the POST call will be triggered to the back end XSJS service. For this I created on simple table control with 6 columns and bind it with an OData service ( for testing it, I am using JSON model ).

table.JPG

 

Triggering the XSJS service with the JSON Data using AJAX Call

 

     In the submit button click, we will get the table binding context and get the corresponding JSON object associated with the table. Which would contain the current values in the table. It will automatically hold any data change that we have made in the table entries.

 

return new sap.ui.commons.layout.MatrixLayout({
  id : "matrix1",
  layoutFixed : false
  }).createRow(oTable).
  createRow(new sap.ui.commons.Button({
  text : "Submit",
  press : function(evt){
  //Getting the JSON data bound to the table object (oTable)
  var JSONData = oTable.getBinding().getModel().oData.modelData;
  //Converting the JSON object to a string variable (formalluy serialization)
  var JSONString = JSON.stringify(JSONData);
  //Making an AJAX POST call
  $.post("proxy/Finance/XSApps/test.xsjs",//this is the target URL
  { JSON_DATA: JSONString } //Data passing with attribute naem JSON_DATA
  ).done(function( data ) {//Success function call
     alert( "Data returned rom XSJS: " + data );
   });
  }

In the above code snippet, all what I am doing is generating the JSON data, serializing it and attaching the same in the POST call made against the target XSJS service.

 

Designing the XSJS back end

    

     Designing the XSJS back end is quite easy . We have sent the JSON data to the target XSJS through a POST call. So the required data will be available in the request object. It is a key value pair, where the key name here is JSON_DATA and the value will be the serialized JSON object. We now need to de serialize the JSON string to JSON object . JSON.parse() method would help us for this task.

 

var JSONString = $.request.parameters.get("JSON_DATA");//Reading the input JSON string
var JSONObj = JSON.parse(JSONString);//serializing the input string
// Now we have the JSON object with us. Do what ever manipulation you want to do on it.
//For the test case i am just returning the number of rows in the JSON object
$.response.setBody(JSONObj.length);

Testing the service :

 

call.JPG

This is the smallest blog I have written.But for me , it was too helpful . When you face such a situation where you need to send complex structured data to the back end , this method would help you.

Share your views regarding performance(or anything).

 

Sreehari V Pillai

"Save Nature For the Future"

Alex Fan

FUZZY SEARCH(二)

Posted by Alex Fan Jun 22, 2014

     FUZZY SEARCH not only can apply on one column, it can also apply on multiple columns. Let’s see an example, firstly we create a table named companies:

    

CREATE COLUMN TABLE companies
(
id INTEGER PRIMARY KEY,
companyname SHORTTEXT(200) FUZZY SEARCH INDEX ON,
contact SHORTTEXT(100) FUZZY SEARCH INDEX ON
);




     Then, insert the following values:

INSERT INTO companies VALUES (1, 'SAP Corp', 'Mister Master');
INSERT INTO companies VALUES (2, 'SAP in Walldorf Corp', 'Master Mister');
INSERT INTO companies VALUES (3, 'ASAP', 'Nister Naster');
INSERT INTO companies VALUES (4, 'ASAP Corp', 'Mixter Maxter');
INSERT INTO companies VALUES (5, 'BSAP orp', 'Imster Marter');
INSERT INTO companies VALUES (6, 'IBM Corp', 'M. Master');




    Next, we use “and” to perform multiple fuzzy search:

SELECT SCORE() AS score, * FROM companies
WHERE CONTAINS(companyname, 'IBM',
FUZZY(0.7,'textSearch=compare,bestMatchingTokenWeight=0.7'))
AND CONTAINS(contact, 'Master',
FUZZY(0.7,'textSearch=compare,bestMatchingTokenWeight=0.7'))
ORDER BY score DESC




1.png

     The score of this method is the means of all searched columns’ scores. We can also use freestyle search on multiple columns:

SELECT SCORE() AS score, * FROM companies
WHERE CONTAINS((companyname,contact), 'IBM Master', FUZZY(0.7))
ORDER BY score DESC;




2.png

    We can see that the scores of the two search methods above are different. The reason is that freestyle search generally use TF/IDF to calculate the score, besides it can’t support 'textSearch=compare', 'bestMatchingTokenWeight=0.7' and other parameters.

similarCalculationMode

Option similarCalculationModecontrols how the similarity of two strings (or, for TEXT attributes, terms) is calculated. Basically, the similarity of two strings is defined by the number of common characters, wrong characters, additional characters in the search string, and additional characters in the reference string. The following calculation modes exist:


Mode

Impact on wrong charac

ters

Impact on additional

characters in search

Impact on additional

characters in table

search

high

high

low

compare

moderate

high

high

symmetricsearch

high

moderate

moderate

substringsearch

high

high

low

 

Examples:

TRUNCATE TABLE "TEST"."SEARCH_TEXT";
INSERT INTO "TEST"."SEARCH_TEXT" VALUES('HANAGEEK');
INSERT INTO "TEST"."SEARCH_TEXT" VALUES('HANAGEEKER');
INSERT INTO "TEST"."SEARCH_TEXT" VALUES('HANAGEK');
INSERT INTO "TEST"."SEARCH_TEXT" VALUES('HANAGAEK');




     Firstly, we use compare mode to perform the search:

SELECT SCORE() AS SCORE,*
FROM "TEST"."SEARCH_TEXT"
WHERE CONTAINS(CONTENT,'HANAGEEK',FUZZY(0.5,'similarCalculationMode=compare'))
ORDER BY SCORE DESC;




3.png

Then, we use search model:

SELECT SCORE() AS SCORE,*
FROM "TEST"."SEARCH_TEXT"
WHERE CONTAINS(CONTENT,'HANAGEEK',FUZZY(0.5,'similarCalculationMode=search'))
ORDER BY SCORE DESC;




4.png

    We can conclude that, compare search model has more effects compared to search model When the original string has additional characters than the search string; search model has more effects than compare model when wrong characters exists. So, when we want to search “SAP” not “SPA”, we should we search model:

TRUNCATE TABLE "TEST"."SEARCH_TEXT";
INSERT INTO "TEST"."SEARCH_TEXT" VALUES('SAP');
INSERT INTO "TEST"."SEARCH_TEXT" VALUES('SAP HANA');
INSERT INTO "TEST"."SEARCH_TEXT" VALUES('SPA');




Perform the search with search model:

SELECT SCORE() AS SCORE,*
FROM "TEST"."SEARCH_TEXT"
WHERE CONTAINS(CONTENT,'SAP',FUZZY(0.5,'similarCalculationMode=search'))
ORDER BY SCORE DESC;




5.png

Perform the search with compare model:

6.png

spellCheckFactor

Option spellCheckFactordefines the score for strings that are not identical but get a fuzzy score of 1.0.

There are two use cases for option spellCheckFactor:

(A) This option allows you to set the score for terms that are not fully equal but that would be a 100% match because of the internal character standardization used by the fuzzy search. For example, the terms 'Café' and 'cafe' give a score of 1.0 although the terms are not equal. For some users it might be necessary to distinguish between the terms. The decision whether two terms are equal is based on the term representation stored in the column dictionary. Option spellCheckFactortherefore works differently on string and text columns, as described in the following sections.

(B) The fuzzy search can return a 100% match for terms that are not identical but cannot be differentiated by the fuzzy-string-compare algorithm. For example, the fuzzy search cannot differentiate between the terms 'abaca' and 'acaba'. In this case, the spellCheckFactorcan be used to avoid a score of 1.0.

If (A) and (B) are not needed by an application, you can set the spellCheckFactor to 1.0 to disable the feature.

spellCheckFactor has different effects on string and text. We create a varchar type table SEARCH_TEXT_CHAR and text type table SEARCH_TEXT separately. Then insert the same words Café and cafe:

TRUNCATE TABLE "TEST"."SEARCH_TEXT";
INSERT INTO "TEST"."SEARCH_TEXT" VALUES('Café');
INSERT INTO "TEST"."SEARCH_TEXT" VALUES('cafe');
INSERT INTO "TEST"."SEARCH_TEXT_CHAR" VALUES('Café');
INSERT INTO "TEST"."SEARCH_TEXT_CHAR" VALUES('cafe');




We search “cafe” separately:

SELECT SCORE() AS SCORE,*
FROM "TEST"."SEARCH_TEXT"
WHERE CONTAINS(CONTENT,'cafe',FUZZY(0.5))
ORDER BY SCORE DESC;







7.png

Then we use spellCheckFactor model to perform the search:

SELECT SCORE() AS SCORE,*
FROM "TEST"."SEARCH_TEXT"
WHERE CONTAINS(CONTENT,'cafe',FUZZY(0.5,'spellCheckFactor=0.8'))
ORDER BY SCORE DESC


 

8.png

 

We can conclude that scenarios “A” only has effects on strings. This is because all characters are replaced by lowercase characters without any diacritics before the fuzzy comparison takes place. This is called standardization. It is therefore possible to get a 100% match when comparing two unequal terms, because the standardization process returned two identical terms. 

interScriptMatching


Option interScriptMatching=on is used to find Latin transliterations of non-Latin search terms, and vice versa.

Often users cannot enter data using the 'original' characters of a foreign language. As a result, a transliteration to the Latin alphabet is used to enter this data. A German user who wants to create a new Chinese business partner for example, types in the city name as 'Shanghai' instead of using the Chinese characters ('上海'). Later, a Chinese user who searches for the business partner in '上海' does not find the data because the search term '上海' and the city name 'Shanghai' stored in the database do not have any characters in common.

To support search requirements as in the example above, search option interScriptMatching can be used. When interScriptMatching=on is used, it is possible to find Latin transliterations of non-Latin search terms, and vice versa. The behavior of fuzzy search changes as follows:

1. Search with Latin characters

(a)Latin alphabet database entries are searched as usual

(b)Non-Latin alphabet database entries are searched using a Latin transliteration of the original data

2. Search with non-Latin characters

(a)Latin alphabet database entries are searched using a Latin transliteration of the search term

(b)Non-Latin alphabet database entries are searched using the original search term

Examples:

CREATE COLUMN TABLE interscript
(
str NVARCHAR(100) PRIMARY KEY
);
INSERT INTO interscript VALUES ('Shanghai');
INSERT INTO interscript VALUES ('上海');
INSERT INTO interscript VALUES ('Beijing');
INSERT INTO interscript VALUES ('北京');
INSERT INTO interscript VALUES ('背景');


Firstly, we perform the search without the interScriptMatching model:

SELECT TO_DECIMAL(SCORE(),3,2), * FROM interscript WHERE CONTAINS(str, 'shanghai', FUZZY(0.7)) ORDER BY SCORE() DESC;

9.png

Then, we use interScriptMatching model to search:

SELECT TO_DECIMAL(SCORE(),3,2), * FROM interscript WHERE CONTAINS(str, 'shanghai',
FUZZY(0.7,'interScriptMatching=on')) ORDER BY SCORE() DESC

10.png

If we search ‘beijing’ the words ‘北京’ and ‘背景’ all will be searched out.

SELECT TO_DECIMAL(SCORE(),3,2), * FROM interscript WHERE CONTAINS(str, 'beijing',
FUZZY(0.7,'interScriptMatching=on')) ORDER BY SCORE() DESC

11.png

Alex Fan

FUZZY SEARCH (一)

Posted by Alex Fan Jun 22, 2014

     We have a Chinese version of this document.

     SAP HANA supports several of text search methods. For example, “like” is well known and supported by all main database also supported by SAP HANA. Besides, SAP HANA also supports fuzzy search function. Fuzzy Search is a fast and fault-tolerant search feature for SAP HANA. A fuzzy search returns records even if the search term contains additional or missing characters or other types of spelling errors.

     For example, we have table SEARCH_TEXT with three words “HANAGEEK”,”HANAGEEKER”,”HANAGEK”:

1.png



     If we use fuzzy search functions to search HANAGEEK, all the three words will be returned no matter it contains additional or missing characters.

2.png

    Fuzzy search is only available for column tables and attribute views and supports the following SQL types and column store types.


3.png

String Types

String types support a basic fuzzy string search. The values of a column are compared with the user input, using the fault-tolerant fuzzy string comparison.

When working with string types, the fuzzy string comparison always compares the full strings. If searching with 'SAP', for example, a record such as 'SAP Deutschland AG & Co. KG' gets a very low score, because only a very small part of the string is equal (3 of 27 characters match).

We insert the value 'SAP Deutschland AG & Co. KG' into table SEARCH_TEXT and then perform the fuzzy search:

INSERT INTO SEARCH_TEXT VALUES('SAP Deutschland AG & Co. KG');


4.png

     We can see that the record contains “SAP” is not be queried out. It means the record “SAP Deutschland AG & Co. KG” has a low score. If we change the column type to TEXT, will the results change? Let’s keep on learning.

Text types

Text types support a more sophisticated kind of fuzzy search. Texts are tokenized (split into terms) and the fuzzy comparison is done term by term.

For example, when searching with 'SAP', a record such as 'SAP Deutschland AG & Co. KG' gets a high score, because the term 'SAP' exists in both texts.

We will take the same example for comparison. Firstly, we create a table SEARCH_TEXT_TMP with a TEXT type column:

CREATE COLUMN TABLE SEARCH_TEXT_TMP(
  CONTENT TEXT
);
INSERT INTO SEARCH_TEXT_TMP VALUES('SAP Deutschland AG & Co. KG');

    Then we perform the fuzzy search, we can see that the record will a relative high score:

SELECT SCORE(),*
FROM SEARCH_TEXT_TMP
WHERE CONTAINS(CONTENT, 'SAP', FUZZY(0.2));

5.png

Date Types

      

A fuzzy search on DATE columns supports two types of error: date-specific typos and dates lying within a user-defined maximum distance.

 

1.     Score Calculation for Typos

Instead of using Levenshtein distance or other string comparison algorithms, the following date-specific typos and errors are defined as similar:

     (1) One wrong digit at any position (for example, 2011-08-15 instead of 2011-08-25). This type of error gets a score of 0.90.

     (2) Two digits interchanged within one component (day, month, or year) (for example, 2001-01-12, 2010-10-12, or 2010-01-21 instead of 2010-01-12). This type of error gets a score of 0.85.

     (3) Month and day interchanged (US versus European date format) (for example, 2010-10-12 instead of 2010-12-10). This type of error gets a score of 0.80.

Only one of these errors is allowed. Dates with more than one error are not considered similar, so the score is 0.0.

        Now, we create a table with a date type column:

CREATE TABLE SEARCH_DATE(
    CONTENT DATE
);

Then, we insert the following dates into the table: 2000-01-11, 2000-01-10, 2000-01-01, 2000-10-01 and 2000-10-02. After the insertion, we search the date 2000-01-10:

SELECT SCORE() AS score,TO_VARCHAR (CONTENT, 'YYYY-MM-DD')
FROM SEARCH_DATE
WHERE CONTAINS(CONTENT, '2000-01-10', FUZZY(0.8))
ORDER BY score DESC;

6.png

As shown in the above results, the date 2000-10-02 has one more errors compared with the search date 2000-01-10. So, its’ score will be 0, other dates will all be searched out. 

Score Calculation for Date Distance

The maximum allowed distance between dates can be defined using the search option 'maxDateDistance', which defines a number of daysThe default for this option is 0, meaning that the feature is disabled. If we set this option to 5, then the date 5 days before or after the search date is validate.

     (1) The identical date gets a score of 1.0.

     (2) The date that is maxDateDistance days away from the search input gets a score that equals the fuzzySimilarity parameter (0.95 in the example above).

     (3) The score of dates between the identical date and maxDateDistance is calculated as a linear function between the two dates defined above. In other words, for each day the score is reduced by ((1-fuzzySimilarity) / maxDateDistance).

     (4) For dates outside the range of maxDateDistance, the score is 0.0.

For example, we insert dates between 2000-01-05 and 2000-01-15 and then perform the following search:

SELECT SCORE() AS score,TO_VARCHAR (CONTENT, 'YYYY-MM-DD')
FROM SEARCH_DATE
WHERE CONTAINS(CONTENT,'2000-01-10',FUZZY(0.95,'maxDateDistance=5'))
ORDER BY score DESC

7.png

     The search returns all date between 2000-01-05 and 2000-01-15.

      If a date meets the conditions of a typo and the conditions of the maxDateDistance parameter, two scores are calculated for the same date. In this case, the score() function returns the highest of both scores. This is shown in the following example:

SELECT SCORE() AS score,TO_VARCHAR (CONTENT, 'YYYY-MM-DD')
FROM SEARCH_DATE
WHERE CONTAINS(CONTENT, '2000-01-10', FUZZY(0.8, 'maxDateDistance=5'))
ORDER BY score DESC;

8.png

Search with FUZZY(0.0)

It is not possible to search for all values that have a fuzzy score greater than or equal to 0. This would return all values of a column and would result in large result sets. A search with FUZZY(0.0) therefore returns all values that have a fuzzy score greater than 0. Let’s see the following example:

TRUNCATE TABLE SEARCH_TEXT;
INSERT INTO SEARCH_TEXT VALUES('HANAGEEK');
INSERT INTO SEARCH_TEXT VALUES('SAP');
SELECT SCORE() AS SCORE,*
FROM SEARCH_TEXT
WHERE CONTAINS(CONTENT,'HANAGEEK',FUZZY(0.0))
ORDER BY SCORE DESC;

9.png

     We can see that, string SAP didn’t return. It means, the score of String SAP is 0 and FUZZY(0.0) only return those strings whose score is greater than 0.

Alex Fan

SAP HANA Custom Dictionary

Posted by Alex Fan Jun 22, 2014

     We have a Chinese version of this document.

     We often need to recognize specific names, products … correctly in some projects based on SAP HANA text analysis. SAP HANA segmentation engine may not be able to correctly recognize new words. Suppose, we need to recognize and extract words “上网卡”,“上海中学”,“乔布斯”correctly.

     Firstly we also need to create table SEGMENTATION_TEST and add full-text index on column content:

CREATE COLUMN TABLE "TEST"."SEGMENTATION_TEST" (
  "URL" VARCHAR(200),
  "CONTENT" NCLOB,
  "LANGU" VARCHAR(10),
  PRIMARY KEY ("URL")
);
CREATE FULLTEXT INDEX FT_INDEX
ON SEGMENTATION_TEST(CONTENT) TEXT ANALYSIS
ON CONFIGURATION 'LINGANALYSIS_FULL'
LANGUAGE COLUMN "LANGU";


     Then we insert the words “上网卡”,“上海中学”,“乔布斯” into table SEGMENTATION_TEST:

INSERT INTO "TEST"."SEGMENTATION_TEST"(URL,CONTENT,LANGU)
VALUES('XXX.XXX.XXX','上网卡','zh');
INSERT INTO "TEST"."SEGMENTATION_TEST"(URL,CONTENT,LANGU)
VALUES('XXX.XXX.XXX2','上海中学','zh');
INSERT INTO "TEST"."SEGMENTATION_TEST"(URL,CONTENT,LANGU)
VALUES('XXX.XXX.XXX3','乔布斯','zh');

    Then we query table $TA_FT_INDEX and results as shown below:

1.png

     We can see that three words all can’t be recognized by SAP HANA segmentation engine correctly.

      To solve the above problem, SAP HANA provides custom dictionary. For those words not in the default dictionary, we can add them in the custom dictionary so as to recognize them correctly.

     Chinese custom dictionary file of SAP HANA is simplified-chinese-std.sample-cd, the file path is \usr\sap\XXX\SYS\global\hdb\custom\config\lexicon\lang\ simplified-chinese-std.sample-cd. XXX is you HANA instance name. There are some examples in the file:   

<?xml encoding="euc-cn" ?>
<!--?Copyright 2013 SAP AG. All rights reserved.
SAP and the SAP logo are registered trademarks of SAP AG in Germany and other countries. Business Objects and the Business Objects logo are registered trademarks of Business Objects S.A., which is an SAP company.
-->
<!-- Sample tagger-lexicon client dictionary -->
<explicit-pair-list>
<!-- Common Nouns -->
<item key = "海缆"       analysis = "海缆[Nn]"></item>         
<item key = "船艏"       analysis = "船艏[Nn]"></item>
<!-- Proper Names -->
<item key="张忠谋"                 analysis = "张忠谋[Nn-Prop]"></item>
<item key="奇摩"             analysis = "奇摩[Nn-Prop]"></item>
</explicit-pair-list>

     Two types of noun are supported by SAP HANA. We can add the identifier [Nn-Prop] to tag the words to names and add [Nn] to tag the words to common noun. The final file after we add the three words as shown below:

<?xml encoding="euc-cn" ?>
<!--?Copyright 2013 SAP AG. All rights reserved.
SAP and the SAP logo are registered trademarks of SAP AG in Germany and other countries. Business Objects and the Business Objects logo are registered trademarks of Business Objects S.A., which is an SAP company.
-->
<!-- Sample tagger-lexicon client dictionary -->
<explicit-pair-list>
<!-- Common Nouns -->
<item key = "海缆"       analysis = "海缆[Nn]"></item>         
<item key = "船艏"       analysis = "船艏[Nn]"></item>
<item key = "上网卡"       analysis = "上网卡[Nn]"></item>
<item key = "上海中学"       analysis = "上海中学[Nn]"></item>
<!-- Proper Names -->
<item key="张忠谋"                 analysis = "张忠谋[Nn-Prop]"></item>
<item key="奇摩"             analysis = "奇摩[Nn-Prop]"></item>
<item key="乔布斯"                 analysis = "乔布斯[Nn-Prop]"></item>
</explicit-pair-list>

     Then we truncate the table SEGMENTATION_TEST and also insert the three words, results shows as below:

2.png

     We can see that “上网卡”, ”上海中学” and “乔布斯” have been recognized correctly.

     We now use configuration EXTRACTION_CORE instead of LINGANALYSIS_FULL. As explained in the previous blog, EXTRACTION_CORE can identify groups, names …etc. We first drop the previous full-text index and then add the full-text index with configuration “EXTRACTION_CORE”:

DROP FULLTEXT INDEX "TEST"."FT_INDEX";
CREATE FULLTEXT INDEX FT_INDEX
ON SEGMENTATION_TEST(CONTENT) TEXT ANALYSIS
ON CONFIGURATION 'EXTRACTION_CORE'
LANGUAGE COLUMN "LANGU";

    Then we insert the following words and query the results:

INSERT INTO "TEST"."SEGMENTATION_TEST"(URL,CONTENT,LANGU)
VALUES('XXX.XXX.XXX3','乔布斯','zh');
INSERT INTO "TEST"."SEGMENTATION_TEST"(URL,CONTENT,LANGU)
VALUES('XXX.XXX.XXX4','海缆','zh');
INSERT INTO "TEST"."SEGMENTATION_TEST"(URL,CONTENT,LANGU)
VALUES('XXX.XXX.XXX5','张忠谋','zh');

3.png

   

     As shown in the above picture, words “乔布斯” and ”张忠谋” are recognized as personal names but word “海缆” is recognized as NOUN_GROUP. The reason we have explained before, we add the identifier [Nn-Prop] to words “乔布斯” and ”张忠谋”, so they will be tagged to personal names, but the words 海缆” with identifier [Nn] will be tagged to NOUN_GROUP.

     We have a Chinese version of this document. 

    In the last blogs we can crawling the html source files and extract html contents using the RLANG. In this blog, we will discuss how to realize Chinese segmentation with SAP HANA since segmentation is the base of text-based scenery.

1.  Introduction of SAP HANA Chinese segmentation

SAP HANA segmentation is a part of SAP text analysis, we can use SAP HANA segmentation engine by creating full-text index on tables. Seven data types are supported by SAP HANA text analysis: TEXT, BINTEXT, NVARCHAR, VARCHAR, NCLOB, CLOB and BLOB

To use SAP HANA Chinese segmentation, we should firstly ensure that

You installed HANA database supports Chinese segmentation. We can use

     The following SQL to check the feature:

SELECT * FROM SYS.M_TEXT_ANALYSIS_LANGUAGES




    As the following picture shows, simplified Chinese is supported.

1.png

2. DEMO

     Firstly, we create a table for testing:

CREATE COLUMN TABLE SEGMENTATION_TEST(
  URL VARCHAR(200) PRIMARY KEY,
  CONTENT NCLOB,
  LANGU VARCHAR(10)
);




     The column CONTENT stores the text for segmenting. The column LANGU specifies the languages we used. Here we specify the language to ZH.

      Next, we create full-text index on the content column of table SEGMENTATION_TEST. We can use the following SQLS:

CREATE FULLTEXT INDEX FT_INDEX
ON SEGMENTATION_TEST(CONTENT) TEXT ANALYSIS
ON CONFIGURATION 'LINGANALYSIS_FULL'
LANGUAGE COLUMN "LANGU";




     We should be aware of that the tables need to create full-text indexes must have primary key, otherwise an error will be occurred.

     2.png

After we create the full-text index, SAP HANA will automatically generator a table named $TA_<index_name>.

Now we insert a record to table SEGMENTATION_TEST:

INSERT INTO SEGMENTATION_TEST(URL,CONTENT,LANGU)
VALUES('http://xxx.xxx.xxx','想获取更多SAP HANA学习资料或有任何疑问,请关注新浪微博@HANAGeek!我们欢迎你的加入!','ZH');



      Then we query the contents of table $TA_FT_INDEX, each row contains the word and some other information:

3.png


As shown above, segmentation table not only contains split words but also have the words’ speech. For example, the speech of word “获取”is verb. The word HANA is an unrecognized word, so the speech is unknown. 

       SAP HANA supports various text analysis configurations:

LINGANALYSIS_BASIC: This configuration provides the following language processing capabilities for linguistic analysis of unstructured data:

  • Segmentation, also known as tokenization - the separation of input text into its elements

LINGANALYSIS_STEMS: This configuration provides the following language processing capabilities for linguistic analysis of unstructured data:

  • Segmentation, also known as tokenization - the separation of input text into its elements
  • Stemming - the identification of word stems or dictionary base forms

LINGANALYSIS_FULL: This configuration provides the following language

processing capabilities for linguistic analysis of unstructured data:

  • Segmentation, also known as tokenization - the separation of input text into its elements
  • Stemming - the identification of word stems or dictionary base forms
  • Tagging - the labeling of words' parts of speech

EXTRACTION_CORE: This configuration extracts entities of interest from unstructured text, such as people, organizations, or places mentioned.

In most use cases, this option is sufficient. EXTRACTION_CORE_VOICEOFCUSTOMER: Voice of the customer content includes a set of entity types and rules that address requirements for extracting customer sentiments and requests. You can use this content to retrieve specific information about your customers' needs and perceptions when processing and analyzing text. The configuration involves complex linguistic analysis and pattern matching that includes processing parts of speech, syntactic patterns, negation, and so on, to identify the patterns to be extracted.

 

To keep track of deletions in the source table, the keys in the table $TA_FT_INDEX need to be aligned to the keys of the source table. To do this, use the following SQL statement:

ALTER TABLE TEST."$TA_FT_INDEX" ADD CONSTRAINT R_KEY FOREIGN
KEY(URL) REFERENCES TEST.SEGMENTATION_TEST(URL) ON DELETE CASCADE;


Actions

Filter Blog

By author:
By date:
By tag: