Search icon CANCEL
Subscription
0
Cart icon
Cart
Close icon
You have no products in your basket yet
Save more on your purchases!
Savings automatically calculated. No voucher code required
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletters
Free Learning
Arrow right icon
Real Time Analytics with SAP Hana
Real Time Analytics with SAP Hana

Real Time Analytics with SAP Hana: Enhance your SAP HANA skills using this step-by-step guide to creating and reporting data models for real-time analytics

By Vinay Singh
$15.99 per month
Book Oct 2015 226 pages 1st Edition
eBook
$35.99 $24.99
Print
$43.99
Subscription
$15.99 Monthly
eBook
$35.99 $24.99
Print
$43.99
Subscription
$15.99 Monthly

What do you get with a Packt Subscription?

Free for first 7 days. $15.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Real Time Analytics with SAP Hana

Chapter 1. Kickoff – Before We Start

This chapter intends to provide context and background to set the base with which we can manipulate the datasets to be used for data modeling. This section tries to act as a refresher that should help you understand and pick up modeling topics faster in upcoming chapters.

We start the chapter with Structured Query Language (SQL)—how we can use it for controlling and manipulating the SAP HANA database objects and data. Then we move on to create SQLscript and learn how to use it effectively. We will also discuss creation and call of procedure step by step in this chapter, which is a good tool for the upcoming topics. We will end the chapter with a detailed discussion on JOINS and how it can be used for connecting tables in SAP HANA.

After completing this chapter you will be able to:

  • Understand and use SAP HANA SQL statements

  • Create SQLscript and use it

  • Create and call a procedure

  • Connect tables using SAP HANA specific JOINS

Introducing SAP HANA SQL


As stated, you will not learn SQL as a whole new concept, but will just revise the traditional SQL concepts at a glance and focus on a few new topics that are of importance from SAP HANA perspective. Our key focus here will be on the SAP HANA SQL script, creating procedures, and learning to create SAP HANA specific JOINS.

Classical SQL

SQL is used to retrieve, store, and manipulate data in the database. SQL can be studied under three subheads:

These subheads are explained as follows:

  • DDL: These statement that are used to define the data: create, alter, drop tables

  • DML: These statements are used to manipulate the data, select, deselect, insert, and update

  • DCL: These statements that are used to control the table, grant, and revoke

The followings are the elements of SQL:

  • Identifiers: These are used to represent names in SQL statements including table/view name, column name, username, role name and so on. There are two types of Identifiers: ordinary and delimited.

  • Data types: These define the characteristics of the data and its value. Data types in SQL are as follows:

    Categories

    Data type

    Numeric

    float, real, integer, decimal, double, tinyint,

    small int, and small decimal

    Large

    blob, clob, nclob, and text

    Binary

    varbinary

    Character string

    varchar, nvarchar, alphanum, and shorttext.

    Date

    time, date, secondtime, and timestamp

  • Expressions: These are clause evaluated to return values. We have different types of expressions in SQL. For example, if…then…..else (case expression) or nested queries (Select (Select ……)).

  • Functions: These are used in expressions for retrieving information from the database. We have a number of functions and data type conversion functions. The number functions take numeric values or alphanumeric/strings with numeric character values and return numeric values, whereas, data type conversion functions are used to convert arguments from one data type to another. For example, to_alphanum, concat, current_date, and so on.

  • Operators: These are used for value comparison, assigning values, or can also be used for calculation. We have different types of operators like Unary, Binary, arithmetic, and string operators to name a few. For example, +, =, subtraction, and or.

  • Predicates: A predicate is specified by combining one or more expressions or logical operators and returning one of the following logical or truth values: true, false, or unknown. Examples are null, in, and like.

In the upcoming chapters, we will learn how to work with SAP HANA studio and open SQL editor, so as to complete the concepts. I will show you how we work with the preceding SQL concepts. For our examples and exercises, we will use the following tables. We will create more tables in further chapters as we progress.

The following table shows you the sales_facts:

PRODUCT_KEY

REGION_KEY

AMOUNT_SOLD

QUANTITY_SOLD

01

100

50000

500

02

200

60000

600

03

300

20000

200

The following table shows you the CUSTOMERS data:

CUSTOMER_KEY

CUST_LAST_NAME

CUST_FIRST_NAME

C1

Mehta

Yatin

C2

Aguirre

Tomas

C3

Huber

Ralf

The following is a REGION table:

REGION_ID

REGION_NAME

SUB_AREA

100

Europe

Germany

200

Asia

Japan

300

US

Northfields

The following table shows you details of the PRODUCT table:

PRODUCT_KEY

PRODUCT_NAME

01

GasKit

02

RubberWasher

Let's see how we can create the preceding tables in SAP HANA:

  1. In SAP HANA studio, right-clicking on your schema (here, HANA_DEMO) will display Open SQL Console; click on it.

  2. We will cover some of the following SQL queries to create the tables:

    Create a schema first, if it hasn't already been created for you—HANA_DEMO; you can choose any name.

    A database schema is the skeleton structure that represents the logical view of the entire database (objects such as tables, views, and stored procedures). It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data, whereas Table is one of the objects contained in schema. It is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows:

    CREATE SCHEMA "HANA_DEMO";
    GRANT SELECT ON SCHEMA HANA_DEMO TO _SYS_REPO WITH GRANT OPTION; if you do not run Grant , later when you will activate your views it will give you erros.
    

    The following command creates the SALES_FACTS table:

    CREATE  COLUMN TABLE "HANA_DEMO"."SALES_FACTS"(
    "PRODUCT_KEY" INTEGER NOT NULL,
    "REGION_KEY" INTEGER NOT NULL,
    "AMOUNT_SOLD" DECIMAL NOT NULL,
    "QUANTITY_SOLD" INTEGER NOT NULL,
    PRIMARY KEY ("PRODUCT_KEY","REGION_KEY") );
    

    The following command creates the CUSTOMER table:

    CREATE  COLUMN TABLE "HANA_DEMO"."CUSTOMER"(
    "CUSTOMER_KEY" VARCHAR(8) NOT NULL,
    "CUST_LAST_NAME" VARCHAR(100) NULL,
    "CUST_FIRST_NAME" VARCHAR(30) NULL,
    PRIMARY KEY ("CUSTOMER_KEY ") );
    

    The following command creates the PRODUCTS table:

    CREATE  COLUMN TABLE "HANA_DEMO"."PRODUCTS" (
    "PRODUCT_KEY" INTEGER NOT NULL,
    "PRODUCT_NAME" VARCHAR(50) NULL,
    PRIMARY KEY ("PRODUCT_KEY") );
    

    The following command creates the REGION table:

    CREATE  COLUMN TABLE "HANA_DEMO"."REGION"(
    "REGION_ID" INTEGER NOT NULL,
    "REGION_NAME" VARCHAR(100) NULL,
    "SUB_AREA" VARCHAR(30) NULL,
    PRIMARY KEY ("REGION_ID") );
    

    The following are sample insert queries:

    insert into "<YOUR SCHEMA>"."TABLE NAME" values(columns1,Columns2,..,); 
    insert into "HANA_DEMO"."SALES_FACTS" values(01,100,50000,500); 
    insert into "HANA_DEMO"."PRODUCTS" values(01,'GasKit');
    insert into "HANA_DEMO"."REGION" values(01,'Europe','Germany'); 
    

    Tip

    I am inserting single values, but you can insert or re-run the query with different values or download the Excel file from our website for demo data.

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

    In the following screenshot, you can see the tables we just created under the HANA_DEMO schema:

Tip

We need to Grant schema SELECT rights to _SYS_REPO user.

In SQL, the editor of our schema needs to execute the following command line:

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

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

The SAP HANA SQLscript


In the following section, we will learn about the SAP HANA SQLscript and see the additional capabilities it brings along with it.

Why SQLscript?

SQLscript is a collection of extensions in Structured Query Language (SQL). The main motivation for SQLscript is to push data intensive application logic into the database, which was not being done in the classical approach where the application logic is mostly executed in an application layer.

We have the following extensions for SQLscript:

Extension

Usage

Datatype extension (create/drop type)

This allows definition of table type without corresponding tables

Procedural extension (create procedure)

This is an imperative construct to push data intensive logic into the database

Functional extension (create function)

This creates side-effect free scalar or table functions, which can be used to express and encapsulate complex data flows

How different is an SQLscript in SAP HANA from classical SQL queries?

Let's do a comparative study between an SQLscript in SAP HANA and classical SQL queries to find out what the point of differences are, as shown in the following table:

SQLscript in SAP HANA

Classical SQL

Multiple result sets can be returned

Query returns only single result set

More database intensive, codes are executed at DB layer, gives better performance

Limited executions at DB layer resulting in multiple access to and from database, relatively slow performance

Control logics such as if/else and business logics like currency conversion can be easily expressed

SQL queries do not have such features

Gives more flexibility to developer to use imperative and declarative logics together

No such flexibility with SQL queries

Supports local variables for intermediate result sets with implicit types

Globally visible views need to be defined even for intermediate result sets or steps

Parameterization of views is possible

Parameterization of views is not possible

The following figure shows you a graphical comparison of the classical approach and the SAP HANA approach:

When should we use SQLscript?


SQLscript should be used in cases where other modeling constructs of SAP HANA, for example, analytic views or attribute views are not sufficient.

Procedures


Procedures are reusable processing blocks that are implemented using the SQLscript, which describes a sequence of operations on data passed as input and database tables. It can be created as read-only (without side-effects) or read-write (with side-effects).

Procedures can have multiple input parameters and output parameters (can be scalar or table types).

There are three different ways to create a procedure in HANA:

  • Using the SQL editor (in SAP HANA Studio)

  • Using the Modeler wizard in the modeler perspective (in SAP HANA Studio)

  • Using the SAP HANA XS project in the SAP HANA Development perspective (in SAP HANA Studio), which isn't discussed in this chapter

Creating with the SQL editor (in SAP HANA Studio)

The following syntax is used to create procedure via the SQL editor:

CREATE PROCEDURE {schema.}name 
            {({IN|OUT|INOUT} 
                        param_name data_type {,...})} 
            {LANGUAGE <LANG>} {SQL SECURITY <MODE>} 
            {READS SQL DATA {WITH RESULT VIEW <view_name>}} AS 
BEGIN 
... 
END

Tip

Downloading the example code

You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

The parameters are for:

  • Reads SQL Data: This defines a procedure as read-only.

  • Language: This specifies the implementation. SQLscript is the default language.

  • With result view: This is used to create a column view for the output parameter of the type table.

Let's create a procedure where we will pass discount as the input parameter and get the sales report as the output parameter. We use the same tables that we created previously:

CREATE PROCEDURE HANA_DEMO."PROC_EU_SALES_REPORT"(
            IN DISCOUNT INTEGER,
            OUT OUTPUT_TABLE HANA_DEMO."EU_SALES" )
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
Pvar1 = SELECT T1.REGION_NAME, T1.SUB_AREA, T2.PRODUCT_KEY, T2.AMOUNT_SOLD
            FROM HANA_DEMO.REGION AS T1
            INNER JOIN
            HANA_DEMO.SALES_FACT AS T2
            ON T1.REGION_KEY = T2.REGION_KEY;

Pvar2 = SELECT T1.REGION_NAME, T1.SUB_AREA, T1.PRODUCT_KEY, T1.AMOUNT_SOLD, T2.PRODUCT_NAME
            FROM :Pvar1 AS T1
            INNER JOIN
            HANA_DEMO.PRODUCT AS T2
            ON T1.PRODUCT_KEY = T2.PRODUCT_KEY;

OUTPUT_TABLE = SELECT SUM(AMOUNT_SOLD) AS AMOUNT_SOLD, SUM(AMOUNT_SOLD - (AMOUNT_SOLD * :DISCOUNT/ 100)) AS NET_AMOUNT,
            PRODUCT_NAME, REGION_NAME, SUB_AREA
            FROM :Pvar2 
            GROUP BY PRODUCT_NAME, REGION_NAME, SUB_AREA;
END;

We can call the previously created procedure with the following CALL statement:

CALL HANA_DEMO."PROC_SALES_REPORT" (8, null);

You can see the created procedure below our schema under the Procedure... folder.

Procedure creation using the wizard

Choose the package in which you want to create the procedure and right-click on it.

A new screen will pop up; fill in the details and click on Confirm:

The SQL console opens with default syntax; we need to put our logic in between BEGIN and END.

The following is a sample logic with which I am creating the Procedure:

On the left-hand side of the screen, you can see the output pane:

Click on it and select New…:

Define the columns which we used in the preceding procedure:

Similarly, perform the same steps for input parameters as well:

Now the procedure is ready to be called via the CALL statement.

Once we build our concept about different views, then one question that will definitely come to our mind is, should we use calculation views (not yet discussed) or procedures. We will discuss this once we have discussed the calculation view in Chapter 5, Creating SAP HANA Artifacts – Analytical Privileges and Calculation Views.

JOINS in SAP HANA


To address some specific business cases and have improved execution, SAP HANA introduces some additional JOINS on top of existing SQL JOINS. These SAP HANA specific JOINS are as follows:

  • Referential JOIN

  • Text JOIN

  • Temporal JOIN

  • Star JOIN

  • Spatial JOIN

Let's see the scenarios when we should consider using these SAP HANA specific JOINS :

Type

Scenario / use case

Remarks

Referential JOIN

Facts with matching dimensions only where referential integrity is ensured.

It's the default join type in SAP HANA.

Facts returned are dependent on queried attributes.

Text JOIN

Multi language table.

Needs a language column.

Behaves as the left outer join.

Temporal JOIN

A key date within a validity period.

Acts as a referential join.

Star JOIN

Star schema scenarios.

Needs data organized in a star schema.

All attributes and hierarchies are included.

Spatial join

Geospatial data.

Only available in calculation views.

Unions versus JOINS


Unions are used to combine the result set of two or more SELECT statements. It's always tempting to JOIN two analytic views when measures from more than one table are required. This should, however, be avoided for performance reasons. It is more beneficial to use a Union in a calculation view. Technically, a Union is not a JOIN type.

Points to remember:

  • Union is not supported in the attribute or analytical view but can only be used in calculation views.

  • Union with constant values are supported within graphical calculation views and the Union operator can accept 1..N input sources.

  • Script-based calculation views can only accept two input sources at a given time.

  • Do not JOIN analytical views (to be discussed later), as you might have performance issues. Instead, use Union with constant values when working with multiple fact tables.

Self-study questions


  1. What are the other JOINS used in classic SQL that are not mentioned in the preceding discussion, and how are they different?

  2. Can you think of use cases where you should use procedure?

Summary


With this chapter, we set the base for the book. It was expected that you already knew these topics and the chapter refreshed them for you. We started with the basics of SQL and how to use SAP HANA SQL statements. We progressed to create SQLscript and procedure. Towards the closure of the chapter, you learned about additional JOINS that SAP HANA has to improve business scenarios, and we closed the chapter with a discussion on Union and JOINS.

In the next chapter, we will cover the approach to SAP HANA data modeling and the dos and don'ts while creating data models. You will also learn which kind of view should be created for different types of information.

Left arrow icon Right arrow icon

Key benefits

  • This book will help you to process analytical and transactional data in real time with the help of SAP HANA.
  • Walk through the steps of the data modeling process and build various data models and artifacts in SAP HANA Studio.
  • Packed with rich examples and use cases that are closely focused on developing real-time applications.

Description

SAP HANA is an in-memory database created by SAP. SAP HANA breaks traditional database barriers to simplify IT landscapes, eliminating data preparation, pre-aggregation, and tuning. SAP HANA and in-memory computing allow you to instantly access huge volumes of structured and unstructured data, including text data, from different sources. Starting with data modeling, this fast-paced guide shows you how to add a system to SAP HANA Studio, create a schema, packages, and delivery unit. Moving on, you’ll get an understanding of real-time replication via SLT and learn how to use SAP HANA Studio to perform this. We’ll also have a quick look at SAP Business Object DATA service and SAP Direct Extractor for Data Load. After that, you will learn to create HANA artifacts—Analytical Privileges and Calculation View. At the end of the book, we will explore the SMART DATA access option and AFL library, and finally deliver pre-packaged functionality that can be used to build information models faster and easier.

What you will learn

Get to grips with the basic building blocks of Analytics/Data models in the SAP HANA environment. Discover various schemas, modeling principles, Joins, and the architecture of the SAP HANA engine. Build data models and artifacts in Sap HANA Studio. Design decision tables and understand the concept of transport management in the SAP HANA landscape. Work with the different views in SAP HANA Studio. Explore full-text search and fuzzy search in SAP HANA. Create your own scenarios and use cases using sample data and code.

Product Details

Country selected

Publication date : Oct 30, 2015
Length 226 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781782174110
Category :
Concepts :

What do you get with a Packt Subscription?

Free for first 7 days. $15.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details


Publication date : Oct 30, 2015
Length 226 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781782174110
Category :
Concepts :

Table of Contents

16 Chapters
Real Time Analytics with SAP HANA Chevron down icon Chevron up icon
Credits Chevron down icon Chevron up icon
About the Author Chevron down icon Chevron up icon
About the Reviewers Chevron down icon Chevron up icon
www.PacktPub.com Chevron down icon Chevron up icon
Preface Chevron down icon Chevron up icon
1. Kickoff – Before We Start Chevron down icon Chevron up icon
2. SAP HANA Data Modeling Approach Chevron down icon Chevron up icon
3. Different Ways of SAP HANA Data Load Chevron down icon Chevron up icon
4. Creating SAP HANA Artifacts Attribute Views and Analytical Views Chevron down icon Chevron up icon
5. Creating SAP HANA Artifacts – Analytical Privileges and Calculation Views Chevron down icon Chevron up icon
6. Understanding Text Search and Hierarchies in SAP HANA Chevron down icon Chevron up icon
7. Using Decision Tables and Transporting SAP HANA Content Chevron down icon Chevron up icon
8. Consuming SAP HANA Data Models Chevron down icon Chevron up icon
9. An Introduction to Application Function Library Chevron down icon Chevron up icon
Index Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Empty star icon Empty star icon Empty star icon Empty star icon Empty star icon 0
(0 Ratings)
5 star 0%
4 star 0%
3 star 0%
2 star 0%
1 star 0%
Top Reviews
No reviews found
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.