SAP 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