Table Of ContentSAP PRESS is a joint initiative of SAP and Rheinwerk Publishing. The
know-how offered by SAP specialists combined with the expertise of
Rheinwerk Publishing offers the reader expert books in the field. SAP
PRESS features first-hand information and expert advice, and provides
useful skills for professional decision-making.
SAP PRESS offers a variety of books on technical and business-related
topics for the SAP user. For further information, please visit our website:
www.sap-press.com.
Michael Pytel
Implementing SAP Business Suite on SAP HANA
2016, approx. 625 pp., hardcover
ISBN 978-1-4932-1257-6
Merz, Hügens, Blum
Implementing SAP BW on SAP HANA
2015, 467 pages, hardcover
ISBN 978-1-4932-1003-9
Haun, Hickman, Loden, Wells
Implementing SAP HANA (2nd edition)
2014, 860 pages, hardcover
ISBN 978-1-4932-1176-0
Schneider, Westenberger, Gahm
ABAP Development for SAP HANA
2013, 609 pages, hardcover
ISBN 978-1-59229-859-4
Anil Babu Ankisettipalli, Hansen Chen, Pranav Wankawala
®
SAP HANA Advanced Data Modeling
Bonn (cid:2) Boston
Dear Reader,
Congratulations! By purchasing this book, you’ve taken the first step in advancing
your career as an SAP HANA developer. As you know, beginner’s basics only go so
far in tackling the real world obstacles of SAP HANA data model design. Business
breeds complexity, and it’s your job to keep up. From modeling complex logic to
building predictive models to calculating trends, the world of SAP HANA is bigger
and more intricate than ever.
However, fear not! Having worked closely with Anil Babu Ankisettipalli, Hansen
Chen, and Pranav Wankawala, I can tell you, personally, that you are in good hands.
There’s a difficulty and craft involved in teaching complicated topics in a compre-
hensive and thoughtful way. Anil, Hansen, and Pranav navigate these waters master-
fully, providing in-depth examples and sample code to guide you through advanced
data modeling use cases. So, what are you waiting for? It’s time to take your career
to the next level!
What did you think about SAP HANA Advanced Data Modeling? Your comments
and suggestions are the most useful tools to help us make our books the best they
can be. Please feel free to contact me and share any praise or criticism you may
have.
Thank you for purchasing a book from SAP PRESS!
Sarah Frazier
Editor, SAP PRESS
Rheinwerk Publishing
Boston, MA
[email protected]
www.sap-press.com
Notes on Usage
This e-book is protected by copyright. By purchasing this e-book, you have agreed
to accept and adhere to the copyrights. You are entitled to use this e-book for
personal purposes. You may print and copy it, too, but also only for personal use.
Sharing an electronic or printed copy with others, however, is not permitted, neither
as a whole nor in parts. Of course, making them available on the Internet or in a
company network is illegal as well.
For detailed and legally binding usage conditions, please refer to the section
Legal Notes.
This e-book copy contains a digital watermark, a signature that indicates which
person may use this copy:
Imprint
This e-book is a publication many contributed to, specifically:
Editor Sarah Frazier
Acquisitions Editor Kelly Grace Weaver
Copyeditor Melinda Rankin
Cover Design Graham Geary
Photo Credit Shutterstock.com: 1578873/© Tomislav Forgo
Production E-Book Kelly O’Callaghan
Typesetting E-Book SatzPro, Krefeld (Germany)
We hope that you liked this e-book. Please share your feedback with us and read
the Service Pages to find out how to contact us.
The Library of Congress has cataloged the printed edition as follows:
Ankisettipalli, Anil Babu, author.
SAP HANA advanced data modeling / Anil Babu Ankisettipalli, Hansen Chen, Pranav Wankawala.
-- 1st edition.
pages cm
Includes index.
ISBN 978-1-4932-1236-1 (print : alk. paper) -- ISBN 1-4932-1236-2 (print : alk. paper) -- ISBN
978-1-4932-1238-5 (print
and ebook : alk. paper) -- ISBN 978-1-4932-1237-8 (ebook) 1. Computer simulation. 2. Databases
. 3. SAP HANA (Electronic
resource) I. Chen, Hansen, author. II. Wankawala, Pranav, author. III. Title.
QA76.9.C65A55 2015
003’.3--dc23
2015030306
ISBN 978-1-4932-1236-1 (print)
ISBN 978-1-4932-1237-8 (e-book)
ISBN 978-1-4932-1238-5 (print and e-book)
© 2016 by Rheinwerk Publishing, Inc., Boston (MA)
1st edition 2016
We would like to thank all of our colleagues at the SAP Innovation Center –
Silicon Valley, USA. All of our knowledge and findings have come from several
projects that we have worked on together over the years. This book would not have
been possible without all the knowledge sharing, brainstorming, and problem
solving that we did together. Our attempt with this book is to extend our culture of
collaboration, thought leadership, and innovation to a wider community. We
would like to dedicate this book to this fantastic team with whom we are privileged
to work with.
Contents
1 SAP HANA Data Models ........................................................... 21
1.1 SAP HANA Database Architecture Overview .................................. 21
1.2 SAP HANA Modeling Paradigms .................................................... 22
1.2.1 Client and Data Connection ............................................. 23
1.2.2 Modeled Views ................................................................ 24
1.2.3 Stored Procedures ............................................................ 24
1.2.4 C++ (Application Function Libraries) ................................. 25
1.2.5 L Language ....................................................................... 25
1.2.6 R Language ...................................................................... 25
1.3 Information Views ......................................................................... 26
1.3.1 Attribute Views ................................................................ 26
1.3.2 Analytic Views ................................................................. 37
1.3.3 Calculation Views ............................................................. 52
1.4 Analytic Privileges ......................................................................... 67
1.4.1 Classical XML-Based Analytic Privilege ............................. 67
1.4.2 SQL-Based Analytic Privilege ............................................ 73
1.5 Stored Procedures ......................................................................... 75
1.5.1 SQLScript Procedures ....................................................... 75
1.5.2 L Procedures .................................................................... 81
1.5.3 R Procedures .................................................................... 82
1.6 Application Function Library .......................................................... 86
1.6.1 Business Function Library ................................................. 86
1.6.2 Predictive Analysis Library ................................................ 88
1.7 Summary ....................................................................................... 90
2 Modeling Complex Logic .......................................................... 93
2.1 Achieving Recursive Logic with Hierarchies .................................... 93
2.1.1 Creating Hierarchies with Tables ....................................... 94
2.1.2 Creating a Hierarchy in an Attribute or Calculation View ... 97
2.1.3 Hierarchy View Attributes ................................................ 100
2.2 Transposing Columns and Rows ..................................................... 110
2.2.1 Column-to-Row Transposition .......................................... 110
2.2.2 Row-to-Column Transposition .......................................... 116
2.2.3 Reversing a Matrix ........................................................... 118
9
Contents
2.2.4 Merging Data from Multiple Records ............................... 121
2.2.5 Splitting Strings ................................................................ 122
2.3 Using cube() with Hierarchies ........................................................ 123
2.4 Calculating Running Total .............................................................. 127
2.5 Calculating Cumulative Sum .......................................................... 131
2.6 Filtering Data Based on Ranking .................................................... 134
2.6.1 Using a Subquery ............................................................. 134
2.6.2 Using Window Functions ................................................. 135
2.6.3 Manipulating Concatenated Virtual Columns .................... 136
2.6.4 Using a Rank Node in a Calculation View ......................... 137
2.7 Controlling Join Paths via Filters .................................................... 138
2.8 Full Outer Join in a Calculation View ............................................. 143
2.9 Making Dynamic Queries in a Stored Procedure ............................ 148
2.9.1 Changing Tables Dynamically ........................................... 148
2.9.2 Changing Filters Dynamically ............................................ 150
2.9.3 Changing Output Columns Dynamically ............................ 152
2.10 Showing History Records Side By Side ........................................... 153
2.11 Sample Data .................................................................................. 158
2.11.1 Using RAND() ................................................................... 158
2.11.2 Using $rowid$ .................................................................. 158
2.11.3 Using Identity Columns .................................................... 159
2.11.4 Using LIMIT/OFFSET ........................................................ 160
2.11.5 Using the TABLESAMPLE SYSTEM .................................... 160
2.12 Using a Vertical Union to Join Tables ............................................. 161
2.13 Sorting Records ............................................................................. 163
2.13.1 Sorting IP Addresses ......................................................... 163
2.13.2 Sorting with Exceptions .................................................... 166
2.13.3 Sorting with User-Defined Rules ....................................... 167
2.14 Finding Missing Values .................................................................. 168
2.14.1 Using the NOT IN Clause .................................................. 169
2.14.2 Using a Self-Join ............................................................... 170
2.14.3 Using a Vertical Union ...................................................... 171
2.14.4 Using Window Functions ................................................. 171
2.15 Using Window Functions for Complex Grouping ............................ 172
2.16 Joining Based on a Date Sequence ................................................. 178
2.17 Using a Nested Calculation View ................................................... 185
2.18 Summary ....................................................................................... 191
10
