Introduction to SAP HANA Modeling for SAP Business One: Modeling

Introduction to SAP HANA Modeling for SAP Business One: Modeling Master Data Dimensions INTE RNA L

Objectives At the end of this topic, you will be able to: Describe how to model master data for SAP Business One in the SAP HANA Studio 2014 SAP AG. All rights reserved. 2

Business Scenario The sales manager at OEC Computers wants to analyze sales totals in an interactive way. He wants to be able to see invoice amounts summarized by customer information and date. Transactions and Dimensions Needed: Transaction: A/R invoice Measure(s): document total Dimensions: Customer Group, Customer, Posting Period Potential additional measures and dimensions: tax amount, status, territory, sales employee, item, item group, etc. 2014 SAP AG. All rights reserved. 3

Copy and Extend vs. Create New In this example, we create new views for you to learn the steps. However SAP recommends you copy and extend existing views. Why? SAP provides a robust semantic layer with pre-built views designed for SAP Business One analytics Reuse views exist for dimensions and query views are ready to use. Main reason to create views is to add custom columns or to delete unused columns, however, it is easy to copy an existing view to add or delete columns. 2014 SAP AG. All rights reserved. 4

Model for our Business Example To provide the analysis our sales manager needs, we will create: Data Foundation Dimensions for business partners and dates Cube for invoices joined with master data dimensions Dimension for Dates Year Cube for Invoices Quarter Month Date Time Stamp Data Foundation Invoice Date Dimension for Business Partners Invoice Number Customer Business Partner Invoice Total BP Name Tax Amount BP Group 2014 SAP AG. All rights reserved. BP Group BP Group Name Country Country Country Name 5

Business Partner Dimension DocEntry Date 1 20120301 C001 100.00 8.00 2 20120302 C002 150.00 12.00 2014 SAP AG. All rights reserved. BPCode DocTotal Tax 6

Building a dimension Here is the process flow for creating a dimension: 2014 SAP AG. All rights reserved. 7

First step: setting parameters Name and Label Calculation View Type Always use calculation views for speed Copy from Choose a view as the basis for your view Can modify copy Changes in the original do not affect the copy. Subtype Defaults to graphical Option to use Scripted Data Category Set to Dimension 2014 SAP AG. All rights reserved. 8

Nodes in a Dimension 2014 SAP AG. All rights reserved. A New Dimension contains: Projection Node Semantics node Other types of nodes include: Join Union Aggregation You can add other nodes as needed. Click on the node type in the toolbar on the left. Then click in the Graphical area to add the node. In our Business Partner Dimension we need to add a Join before we choose tables. 9

Select tables Drag tables from Catalog to the node 2014 SAP AG. All rights reserved. or Use a search window to find tables: Choose Add Objects icon, or Open the context menu from the node and choose Add Objects 10

Add Filters Add a filter on CardType column Right click on the CardType column Select AddFilter Choose operator and Value 2014 SAP AG. All rights reserved. 11

Define join conditions Define the join type Inner join Left Outer Join 2014 SAP AG. All rights reserved. Set the cardinality View the join properties in the Properties box 12

SAP HANA Join Types Inner Join: The INNER JOIN selects the set of records that match in both the Tables. Left Outer Join: The Left Outer Join selects the complete set of records from first table, with the matching records (where available) in the second table. If there is no match, the right side will contain null. Right Outer Join: The Right Outer Join selects the complete set of records from second table, with the matching records (where available) in the first table. If there is no match, the left side will contain null. Full Outer Join: The FULL OUTER JOIN selects the set of records that match in both the Tables. 2014 SAP AG. All rights reserved. 13

Left Outer Join C CODE C NAME NATION 1 Smith AU 2 Jones AU 3 Martinez MX 4 Rosario MX Returns all rows from left table Even if no matches in right table 2014 SAP AG. All rights reserved. NATION NNAME AU AUSTRALIA KO KOREA C CODE C NAME NATION NNAME 1 Smith AU AUSTRALIA 2 Jones AU AUSTRALIA 3 Martinez MX ? 4 Rosario MX ? 14

Inner Join C CODE C NAME NATION 1 Smith AU 2 Jones AU 3 Martinez MX 4 Rosario MX NATION NNAME AU AUSTRALIA KO KOREA C CODE C NAME NATION NNAME 1 AU AUSTRALIA 2 Smith Jones 2014 SAP AG. All rights reserved. AU AUSTRALIA Only returns rows where there is a match on both sides of join Example returns only first two rows because no value for MX in table 15

Referential Integrity Referential integrity requires that every value of fields where tables are joined (join partners) exist in both tables. These tables lack referential integrity because the Nation table is missing a value in the Customer table. C CODE C NAME NATION NATION NNAME 1 Smith AU AU AUSTRALIA 2 Jones AU 3 Martinez MX KO KOREA 4 Rosario MX 2014 SAP AG. All rights reserved. 16

Tips on Joins for Dimensions The Many table must be the left / inner table. Check all joins have cardinality 1 to the right/outer table and 1 or N to the left/inner table. Drag joins from the center table to outer table, then change cardinality as needed. Recommendations: Use Left Outer Joins when facts with missing attributes are allowed For more info: http://saphanatutorial.com/sap-hana-join-types/ 2014 SAP AG. All rights reserved. 17

Select attributes Define the output structure for view Add table columns to output Specify any filters 2014 SAP AG. All rights reserved. 18

Connect All Nodes in Model All nodes need to be connected The model ends at the projection and semantic nodes A view can have multiple nodes Draw a line from one node to another to connect nodes 2014 SAP AG. All rights reserved. 19

Semantics Node Set the final output for the dimension in the Semantics Node In this node you can: Mark attributes and measures Rename or change labels for columns Create filters Hide columns Create hierarchies Set variables or parameters Define key attributes 2014 SAP AG. All rights reserved. 20

Save and activate Saving the view: the view is not visible to reporting tools until activated. Save and validate Activating the view: makes the view visible to reporting tools Remember, if you change the view after activation, save and reactivate the view. 2014 SAP AG. All rights reserved. Save and activate 21

Preview Data Preview data to confirm the model works. Three ways to preview data: 2014 SAP AG. All rights reserved. Raw Data Distinct Values Analysis 22

Case Study – Create a Business Partner Dimension Physical tables: OCRD, OCRG, OCRY Output: BP Code BP Name BP Type BP Group Name BP Country Name 2014 SAP AG. All rights reserved. 23

Summary Key points about dimensions: Dimensions give us the ability to join related tables of master data. Dimensions are used in transactional cubes to give additional context to the transactional data. The best practice is to copy existing views and modify them. To create a reuse view for a dimension, use a calculation view with Dimension as the subtype. A newly created dimension contains a projection node and a semantics node. When you join tables, you need to define the join conditions, such as the join type and cardinality. Use Left Outer Joins when facts with missing attributes are allowed. Set the cardinality N:1. Select the attributes you want in your view. Set final output in Semantics node. You need to choose at least one key attribute. Save and activate then preview data for accuracy. 2014 SAP AG. All rights reserved. 24

Thank you This concludes the topic on modeling master data dimensions. Thank you for your time. 2014 SAP AG. All rights reserved. 25

2015 SAP AG. All rights reserved. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Excel, Outlook, PowerPoint, Silverlight, and Visual Studio are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, z10, z/VM, z/OS, OS/390, zEnterprise, PowerVM, Power Architecture, Power Systems, POWER7, POWER6 , POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storwize, XIV, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Informix, and Smarter Planet are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torvalds in the United States and other countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or registered trademarks of Adobe Systems Incorporated in the United States and other countries. Oracle and Java are registered trademarks of Oracle and its affiliates. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Google App Engine, Google Apps, Google Checkout, Google Data API, Google Maps, Google Mobile Ads, Google Mobile Updater, Google Mobile, Google Store, Google Sync, Google Updater, Google Voice, Google Mail, Gmail, YouTube, Dalvik and Android are trademarks or registered trademarks of Google Inc. INTERMEC is a registered trademark of Intermec Technologies Corporation. Wi-Fi is a registered trademark of Wi-Fi Alliance. Bluetooth is a registered trademark of Bluetooth SIG Inc. Motorola is a registered trademark of Motorola Trademark Holdings LLC. Computop is a registered trademark of Computop Wirtschaftsinformatik GmbH. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork, SAP HANA, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects Software Ltd. Business Objects is an SAP company. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems Inc. Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere, and other Sybase products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Sybase Inc. Sybase is an SAP company. HTML, XML, XHTML, and W3C are trademarks or registered trademarks of W3C , World Wide Web Consortium, Massachusetts Institute of Technology. Crossgate, m@gic EDDY, B2B 360 , and B2B 360 Services are registered trademarks of Crossgate AG in Germany and other countries. Crossgate is an SAP company. Apple, App Store, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch, Objective-C, Retina, Safari, Siri, and Xcode are trademarks or registered trademarks of Apple Inc. All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary. IOS is a registered trademark of Cisco Systems Inc. RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl, BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook, and BlackBerry App World are trademarks or registered trademarks of Research in Motion Limited. 2015 SAP AG. All rights reserved. The information in this document is proprietary to SAP. No part of this document may be reproduced, copied, or transmitted in any form or for any purpose without the express prior written permission of SAP AG. 26