Download Report

How To... Realize Cross System Reporting Using
SAP HANA Live Content
Database & Technology
SAP HANA Appliance
Applicable Releases:
SAP HANA 1.0 SPS 05
Version 1.1
June 2013
How To... Realize Cross System Reporting Using SAP HANA Live Content
© Copyright 2013 SAP AG. All rights reserved.
All other product and service names mentioned are the trademarks of
No part of this publication may be reproduced or transmitted in any form
their respective companies. Data contained in this document serves
or for any purpose without the express permission of SAP AG. The
informational purposes only. National product specifications may vary.
information contained herein may be changed without prior notice.
The information in this document is proprietary to SAP. No part of this
Some software products marketed by SAP AG and its distributors
document may be reproduced, copied, or transmitted in any form or for
contain proprietary software components of other software vendors.
any purpose without the express prior written permission of SAP AG.
Microsoft, Windows, Excel, Outlook, and PowerPoint are registered
This document is a preliminary version and not subject to your license
trademarks of Microsoft Corporation.
agreement or any other agreement with SAP. This document contains
IBM, DB2, DB2 Universal Database, System i, System i5, System p,
System p5, System x, System z, System z10, System z9, z10, z9, iSeries,
pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390,
OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power
Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER,
only intended strategies, developments, and functionalities of the SAP®
product and is not intended to be binding upon SAP to any particular
course of business, product strategy, and/or development. Please note
that this document is subject to change and may be changed by SAP at
any time without notice.
OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS,
SAP assumes no responsibility for errors or omissions in this document.
HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex,
SAP does not warrant the accuracy or completeness of the information,
MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and
text, graphics, links, or other items contained within this material. This
Informix are trademarks or registered trademarks of IBM Corporation.
document is provided without a warranty of any kind, either express or
Linux is the registered trademark of Linus Torvalds in the U.S. and other
countries.
Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either
trademarks or registered trademarks of Adobe Systems Incorporated in
the United States and/or other countries.
Oracle is a registered trademark of Oracle Corporation.
UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open
Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame,
and MultiWin are trademarks or registered trademarks of Citrix Systems,
Inc.
HTML, XML, XHTML and W3C are trademarks or registered trademarks
of W3C®, World Wide Web Consortium, Massachusetts Institute of
Technology.
implied, including but not limited to the implied warranties of
merchantability, fitness for a particular purpose, or non-infringement.
SAP shall have no liability for damages of any kind including without
limitation direct, special, indirect, or consequential damages that may
result from the use of these materials. This limitation shall not apply in
cases of intent or gross negligence.
The statutory liability for personal injury and defective products is not
affected. SAP has no control over the information that you may access
through the use of hot links contained in these materials and does not
endorse your use of third-party Web pages nor provide any warranty
whatsoever relating to third-party Web pages.
SAP “How-to” Guides are intended to simplify the product implementtation. While specific product features and procedures typically are
explained in a practical business context, it is not implied that those
features and procedures are the only approach in solving a specific
Java is a registered trademark of Sun Microsystems, Inc.
business problem using SAP NetWeaver. Should you wish to receive
JavaScript is a registered trademark of Sun Microsystems, Inc., used
additional information, clarification or support, please refer to SAP
under license for technology invented and implemented by Netscape.
Consulting.
SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP
Any software coding and/or code lines / strings (“Code”) included in this
BusinessObjects Explorer, StreamWork, and other SAP products and
documentation are only examples and are not intended to be used in a
services mentioned herein as well as their respective logos are
productive system environment. The Code is only intended better explain
trademarks or registered trademarks of SAP AG in Germany and other
and visualize the syntax and phrasing rules of certain coding. SAP does
countries.
not warrant the correctness and completeness of the Code given herein,
Business Objects and the Business Objects logo, BusinessObjects,
and SAP shall not be liable for errors or damages caused by the usage of
Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other
the Code, except if such damages were caused by SAP intentionally or
Business Objects products and services mentioned herein as well as their
grossly negligent.
respective logos are trademarks or registered trademarks of Business
Disclaimer
Objects Software Ltd. Business Objects is an SAP company.
Some components of this product are based on Java™. Any code change
Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere,
in these components may cause unpredictable and severe malfunctions
and other Sybase products and services mentioned herein as well as their
and is therefore expressively prohibited, as is any decompilation of these
respective logos are trademarks or registered trademarks of Sybase, Inc.
components.
Sybase is an SAP company.
Any Java™ Source Code delivered with this product is only to be used by
SAP’s Support Services and may not be modified or altered in any way.
2
How To... Realize Cross System Reporting Using SAP HANA Live Content
Document History
Document Version
Description
1.10
1.00
First official release of this guide
Terminology
In working with SAP HANA Live for SAP Business Suite you will often hear reference to the Virtual Data
Model, which is not an official product name. Rather it is a conceptual term used to describe the delivered
calculation views that are imported into SAP HANA upon installation of the SAP HANA Live content package
and which contains the business logic needed to realize real-time operational reporting of SAP transactional
data.
SAP HANA Live has been known at SAP by several internal product names which are mentioned here to
provide as cross-reference to prior presentations or documentation:

SAP HANA Analytics Framework (SHAF)

HANA Based Analytics (HBA)

New Analytics
3
How To... Realize Cross System Reporting Using SAP HANA Live Content
Typographic Conventions
Type Style
Description
Example Text
Words or characters quoted
from the screen. These
include field names, screen
titles, pushbuttons labels,
menu names, menu paths,
and menu options.
Icons
Icon
Description
Caution
Note or Important
Example
Recommendation or Tip
Cross-references to other
documentation
Example text
Emphasized words or
phrases in body text, graphic
titles, and table titles
Example text
File and directory names and
their paths, messages,
names of variables and
parameters, source text, and
names of installation,
upgrade and database tools.
Example text
User entry texts. These are
words or characters that you
enter in the system exactly
as they appear in the
documentation.
<Example
text>
Variable user entry. Angle
brackets indicate that you
replace these words and
characters with appropriate
entries to make entries in the
system.
EXAMPLE TEXT
Keys on the keyboard, for
example, F2 or ENTER.
4
TABLE OF CONTENTS
1.
BUSINESS SCENARIO ................................................................. 2
2.
BACKGROUND INFORMATION .................................................. 2
3.
PREREQUISITES .......................................................................... 2
4.
4.1
4.2
STEP-BY-STEP PROCEDURE – SAP HANA LIVE ..................... 3
Display Calculation View CostCenter ............................. 3
Schema Mapping............................................................... 5
5.
5.1
5.2
5.3
5.4
STEP-BY-STEP PROCEDURE – USE CASE 1 ........................... 7
Initial Setup ........................................................................ 7
Create Package ................................................................. 8
Create Calculation View ................................................. 10
Create Cross-System Calculation View ........................ 17
6.
6.1
6.2
6.3
STEP-BY-STEP PROCEDURE – USE CASE 2 .........................32
Initial Setup ...................................................................... 32
Display Calculation View OpportunityHeader .............. 32
Create Cross-System Calculation View ........................ 34
1. BUSINESS SCENARIO
The virtual data models delivered in the SAP HANA Live content packages are structured representation of
operational data organized by SAP Business Suite application. By using SAP HANA Live business users
can now perform real-time operational reporting and analytics on top of large volumes of data in ERP, CRM
and other SAP Business Suite applications.
Using the graphical modeling environment available within the SAP HANA Studio application, you can
enhance the virtual data models delivered by SAP or create your own models by adding, removing or
renaming fields, creating joins, or adjusting the views to select only relevant fields needed for your analysis.
This guide will demonstrate how to leverage SAP HANA Live content to amalgamate SAP data coming from
different SAP Business Suite source systems to provide a central view for use in reporting and analytics.
Two basic cross-system use cases will be presented -- from which further extensions could be realized.
1. A union of similar data arising from two or more SAP Business Suite systems
2. A combination of dissimilar data from different SAP Business suite systems, but which shares a
common field to make a join
2. BACKGROUND INFORMATION
SAP HANA Live was first released in December 2012 initially offering more than 900 views for many
component areas of ERP, CRM, SCM, GTS and GRC. In May 2013 Support Package Stack 02 was
released which added additional content to further extend the operational reporting coverage and industry
solution capabilities.
The SAP provided virtual data models are developed with all necessary joins and transformations to turn the
data in your database tables into meaningful information for use in operational reporting. However their
foundational intent is to enable real-time reporting on data from one single SAP Business Suite application.
Customers with complex system landscapes involving multiple SAP transactional systems have begun to
inquire how SAP HANA Live can be utilized in their environment. This guide seeks to answer that question.
Note
This How-to Guide does not deal with heterogenic data scenarios that have to be harmonized by
preprocessing systems, such as SAP BusinessObjects Data Services, prior to import into SAP HANA.
SAP customers are cautioned not to overset their expectations for SAP HANA Live and should perform a
feature and benefits comparison with the SAP Business Warehouse powered by SAP HANA. BW on HANA
offers a tightly governed analytical data warehousing environment with delivered content by SAP. It gives
customers the ability to integrate data across systems and to query historical data that may have been
archived out of the transactional systems. Using the data in the warehouse and additional software
components like SEM or BPC, customers have the option to perform financial planning and consolidations,
and with SAP BW they can achieve analytical scenarios where complex logic, sophisticated KPIs, and/or
benchmarking may be needed.
Due to the complementary strengths of each solution, many SAP customers can benefit from utilizing both
BW on HANA and SAP HANA Live in tandem.
3. PREREQUISITES
To install the SAP HANA Live content, a SAP HANA database installation fulfilling the following prerequisites
is necessary:

SAP HANA Appliance Software Support Package Stack 05, highest revision available

SAP HANA Studio Software Support Package Stack 05, highest revision available

Use the Software Update Manager (SUM) for SAP HANA for the most recent update if you already have
the SAP HANA Appliance Software installed

In addition, the SAP Business Suite product for which the SAP HANA Live content is installed needs to
fulfill some prerequisites. They are specified in the section Installation Information for Virtual Data
Models of the Administrator’s Guide for SAP HANA Live.
Software

You can download the SAP HANA Live packages from SAP Solution Manager or directly from SAP
Service Marketplace at http://service.sap.com/swdc
Hardware

Consult the SAP Product Availability Matrix at http://service.sap.com/pam
Relevant SAP Notes are shown below.
SAP Notes

Note 1778607 – SAP HANA Live for SAP Business Suite

Note 1782065 – Tables for SAP HANA Analytics for SAP Business Suite

Note 1782066 – Tables for SAP HANA Analytics for CRM 1.0

Note 1781992 – Tables for SAP HANA Analytics for ERP 1.0

Note 1782068 – Tables for SAP HANA Analytics for GRC 1.0

Note 1780655 – Tables for SAP HANA Analytics for GTS 1.0

Note 1782067 – Tables for SAP HANA Analytics for SCM 1.0

Note 1799254 – Schema mapping for HANA based Analytics

Note 1796718 – Analytics Authorization Assistant

Note 1800631 – [Obsolete] SAP HANA: Limitations for HANA-based Analytics

Note 1800515 – Tables for SAP HANA Analytics for ERP 1.0 in the row store


Additional background/starting documentation

SAP Help Portal landing page for SAP HANA Live for SAP Business Suite 1.0

Release Notes on SAP Help Portal

Administrator’s Guide on SAP Service Marketplace

List of Views in Q1 (attachment to SAP Note 1793376)
Required/recommended expertise or prior knowledge

Knowledge of SAP HANA modeling and security
4. STEP-BY-STEP PROCEDURE – SAP HANA LIVE
4.1
Display Calculation View CostCenter
...
In this step you will get started with using the delivered content of SAP HANA Live. Specifically you
will work with the reuse view named CostCenter.
1. From the SAP HANA Studio, select the Modeler perspective.
2. From the Quick Launch tab, click on the Select System button
HANA system from the list of available systems.
and choose a SAP
3. From the navigation pane, below the selected SAP HANA System, expand the Content folder to
display sap.hba.ecc. Expand the Calculation Views folder and scroll down to locate the calculation
view named CostCenter. Optionally, you can right-click on the ecc package and use the context
menu option to Find the calculation view called CostCenter.
4. Open the View Definition by clicking on the CostCenter view.
5. Notice the surprising complexity in the virtual data model that is needed for simply retrieving the list
of cost center master data and its associated texts. Such a data model faces two main design
challenges. First cost center master data is time-dependent, which means the attributes related to
the key fields (controlling area and cost center) can vary depending on a selected date range.
Second, each master record can be translated into multiple languages, for example to name a cost
center differently in English than in German. The virtual data model solves these challenges using
several joins, projections and aggregations within the calculation view to output the proper list.
6. [Optional] Click on the various objects to see the details and output. Right-click on the Output (or
any intermediate object) to select Data Preview.
7. In the graphical modeling panel, click on the CSKS table and notice in the Properties view menu the
Data Source Type is “DataBase Table” in Schema IDM_SAF. (Your schema will be different
depending on your SLT and/or ERP on HANA setup.)
8. Repeat the prior step for table CSKT and observe the same results.
9. Close the view definition by clicking on the X
the tab strip.
on the right side of
10. At this point you might have some questions like: “Has SAP delivered these views to point to tables
in a fixed, predefined schema, such as IDM_SAF? And what if your data is stored in a different
schema?”
11. Of course, the answer to the above question is “no”. For flexibility in data modeling to
accommodate differences between where the models are authored and which schema the physical
tables reside, SAP HANA Studio offers the Schema Mapping function.
4.2
Schema Mapping
...
In this step you will take a look at the Schema Mapping feature of the SAP HANA Studio.
1. From the Quick Launch tab, select Schema Mapping. Here is an example where the SAP HANA Live
content is defined in authoring schema SAP_ECC, but the physical database tables are stored in
schema IDM_SAF.
2. This How-to Guide demonstrates how to realize cross system reporting using SAP HANA Live
content. So the next question to ask is the following: “ How would the schema mapping be used if
there were 2 or more source ERP systems?”
3. Let’s try to point the virtual data models of SAP HANA Live at a second physical schema.
4. Click the Add
button and input Authoring Schema SAP_ECC again.
5. Notice the error message: Authoring schema SAP_ECC is already existing as physical schema.
6. Click Cancel from the Schema Mapping.
Caution
7. From the above error message you should conclude that it is not possible to install the SAP HANA
Live content package twice within the same SAP HANA instance for two different physical
schemas.
8. This means that to accommodate such scenarios will require customer extensions of the SAP
HANA Live content.
9. In the next section, you will learn how to hand copy a single, delivered SAP HANA Live calculation
view and to adjust it to reference other source systems.
10. But in practice, if you needed to access and enhance many of the SAP HANA Live calculation views,
you should mass export and import them into a new package.
11. End of section.
STEP-BY-STEP PROCEDURE – USE CASE 1
5.
Use Case 1
A union of similar data arising from two or more SAP Business Suite systems
Example
View a cost center master list across 2 ERP systems
5.1
Initial Setup
...
In this step you will examine your HANA instance to know that you have SAP ERP data in two
separate database schemas. If you do not have this situation and you have only one schema of ERP
tables, then you can simulate such a cross system environment using SQL commands to copy
needed tables into a second schema.
1. From the SAP HANA Studio, select the Administration Console perspective.
2. This section of the HTG starts with the position that all SAP HANA Live relevant tables from 2 or
more ERPs systems exist in a central HANA system under unique schemas, such as shown here as
SAP_ECC and SAP_ECC2. Note that your schema names may differ from these examples and will
probably correspond to how your SLT connection was defined in a side-by-side HANA landscape, or
how the ERP on HANA system was installed in an integrated environment.
3. However, if you have only one source ERP system in your HANA instance, but you would like to
practice with this How-to Guide to simulate such a scenario then adapt and execute the following
commands from the SQL editor. The result will be the creation of a second schema called
SAP_ECC2. You will grant the select privilege for this schema to user _SYS_REPO and optionally to
other user(s). Finally you will create copies of tables T000, CSKS and CSKT with data from schema
SAP_ECC into SAP_ECC2.
SQL Console
CREATE SCHEMA SAP_ECC2;
GRANT SELECT ON SCHEMA SAP_ECC2 TO _SYS_REPO WITH GRANT OPTION;
GRANT SELECT ON SCHEMA SAP_ECC2 TO <USER>;
CREATE COLUMN TABLE "SAP_ECC2"."T000" LIKE "SAP_ECC"."T000" WITH DATA;
CREATE COLUMN TABLE "SAP_ECC2"."CSKS" LIKE "SAP_ECC"."CSKS" WITH DATA;
CREATE COLUMN TABLE "SAP_ECC2"."CSKT" LIKE "SAP_ECC"."CSKT" WITH DATA;
4. Press F8 or click the
icon to execute the above SQL commands.
5. Click the X on the SQL Console tab to close the SQL console.
6. You will now see the newly created tables in the SAP_ECC2 schema.
5.2
Create Package
...
In this step you will create two new data modeling packages. One will be used to extend the SAP
HANA Live content against a second ERP source schema. The other package will be used to create
new virtual data models that cross systems.
1. From the SAP HANA Studio, select the Modeler perspective.
2. From the Quick Launch tab, click on the Select System button
HANA system from the list of available systems.
3. From the Quick Launch tab, click Package, then click on the Create…
and choose a SAP
button.
4. Define the package properties as follows. Click OK when input is complete.
Field
Value
Name
sap.hba.ecc2
Description
sap.hba.ecc2
Delivery Unit
As applicable
Structural
No
Original Language
As applicable
Person Responsible
As applicable
Logon Locale
As applicable
5. Create another package with the following properties. Click OK when input is complete.
Field
Value
Name
sap.hba.cross
5.3
Description
sap.hba.cross
Delivery Unit
As applicable
Structural
No
Original Language
As applicable
Person Responsible
As applicable
Logon Locale
As applicable
Create Calculation View
...
In this step you create a new calculation view by copying a view delivered within the SAP HANA Live
content. In the new view, you will have to adjust the source tables to read from different schema
than those original ERP tables which are referenced in the Schema Mapping and used during the
installation of the SAP HANA Live content package.
1. From the navigation pane, below the selected SAP HANA System, expand the Content folder to
display sap.hba.ecc2. Right click on ecc2 and select New  Calculation View...
2. Define the calculation view properties as follows. Click Finish when input is complete.
Field
Value
Name
CostCenter
Description
CostCenter
Package
sap.hba.ecc2
Create New
< not selected >
Copy From
< selected >  browse  sap.hba.ecc.CostCenter
3. The new CostCenter calculation view is displayed in the graphical editor.
4. Click on the tables CSKS or CSKT. Notice in the Properties panel the data is sourced from schema
SAP_ECC. We want to adjust this new view to read those tables from schema SAP_ECC2. We will
do that by deleting these two tables and adding them again using the other schema.
Recommendation or Tip
Before deleting elements from a model, it is a good practice to review the objects above the
element(s) to be deleted to understand how any joins are defined and what fields are included
in the output. It is recommended that you take screen prints for reference because these will
have to be setup again.
5. Select Join_1.
6. Capture the Details of Join_1 and note the Join Type in the Properties menu.
7. Capture the Output of Join_1.
8. Click on table CSKS. Click on the Delete
icon.
9. Click Yes to confirm the deletion.
10. Click on table CSKT. Click on the Delete
icon.
11. Click Yes to confirm the deletion.
12. From the navigation pane, below the selected SAP HANA System, expand the Catalog folder to
display schema SAP_ECC2. Expand the Tables folder and locate tables CSKS and CSKT. Then
select each table and drag them into the graphical modeling canvas below Join_1.
13. The result should appear as follows:
14. Click on the tables CSKS or CSKT. Notice in the Properties panel the data is sourced from schema
SAP_ECC2.
15. Connect tables CSKS and CSKT to Join_1.
16. Define a text join using language column SPRAS and the following join fields:
CSKS
CSKT
MANDT
MANDT
KOKRS
KOKRS
KOSTL
KOSTL
DATBI
DATBI
17. Select the following fields and then right-click and choose Add To Output.
CSKS
CSKT
DATAB
KTEXT
BUKRS
LTEXT
GSBER
VERAK
VRAK_USER
PRCTR
WAERS
ABTEI
18. Click the Save and Validate
icon.
19. Check the Status of the Job Log. It should state “Completed with warnings”.
20. Click the Save and Activate
icon.
21. Check the Status of the Job Log. It should state “Completed with warnings”.
22. Click the Data Preview
icon.
23. Review the Raw Data tab of the Data Preview.
24. Click the X on the CostCenter tab to close the Data Preview.
25. Click the X on the sap.hba.ecc2.CostCenter tab to close the Calculation View Definition.
5.4
Create Cross-System Calculation View
...
In this step you create a new calculation view that combines the CostCenter views from each ERP
source. You will also join these views with table T000 from each source to include the logical
system name within the output. It is anticipated that such an attribute might be used for security
purposes to define analytical privileges because some users who consume the cross-system view
should only able to display data from a specified source. However we will not cover how to setup
the security aspects of such a requirement in this guide.
1. From the navigation pane, below the selected SAP HANA System, expand the Content folder to
display sap.hba.cross. Right click on cross and select New  Calculation View...
2. Define the calculation view properties as follows. Click Next when input is complete.
Field
Value
Name
ECC_CostCenter__ECC2_CostCenter
Description
ECC_CostCenter__ECC2_CostCenter
Package
sap.hba.cross
Create New
< selected >
Copy From
< not selected >
View Type
Graphical
< selected >
SQL Script
< not selected >
3. From the Select Objects screen, expand the Catalog folder and locate the two T000 tables from the
SAP_ECC and SAP_ECC2 schemas. Hint: you can input T000 and click the search
button.
4. Select these tables and Add them to the right side panel.
5. From the Select Objects screen, expand the Content folder and locate the two CostCenter views
from the sap.hba.ecc and sap.hba.ecc2 packages. Hint: you can input CostCenter and click the
search
button.
6. Select these views and Add them to the right side panel. Then click Finish.
7. The new calculation view is displayed in the graphical editor.
8. Drag the elements to arrange them in the following order from left to right:
9. Use the Tools Palette to add two join elements.
10. Drag these join elements over the objects as shown below.
11. Connect the objects to set the joins.
12. Select Join_1. Define a Left Outer Join using the client fields and cardinality n..1:
CostCenter_1
T000
SAPClient
MANDT
13. Select the following fields and then right-click and choose Add To Output.
CostCenter_1
T000
< all fields >
LOGSYS
14. The Output of Join_1 should be as follows:
15. Select Join_2. Define an Left Outer Join using the client fields and cardinality n..1:
CostCenter
T000_1
SAPClient
MANDT
16. Select the following fields and then right-click and choose Add To Output.
CostCenter
T000_1
< all fields >
LOGSYS
17. The Output of Join_2 should be as follows:
18. Use the Tools Palette to add two projection elements.
19. Drag these Projection elements over the join objects as shown below.
20. Connect the Joins to the Projections.
21. Select the left Projection. Select all the fields and then right-click and choose Add To Output.
22. [Optional] Select the left Projection. Under the Output of Projection_X, select Calculated Column.
Right-click and select New.
23. [Optional] Input the values of the Calculated Column as follows, and then click Add. The intent here
is to create a runtime column which represents the source schema of the data. This step is only
needed if you are simulating the environment of two or more ERP systems, otherwise the Logical
System field from table T000 will be unique for each data source.
24. Select the right Projection. Select all the fields and then right-click and choose Add To Output.
25. [Optional] Select the right Projection. Under the Output of Projection_X, select Calculated Column.
Right-click and select New.
26. [Optional] Input the values of the Calculated Column as follows, and then click Add.
27. Use the Tools Palette to add a Union element.
28. Drag the Union element over the Projection objects as shown below.
29. Connect the Projections to the Union.
30. Select the Union element. In the Details of the Union pane, click the Auto Map by Name
31. The Details of Union_5 is automatically updated.
32. Connect the Union to the Output.
icon.
33. Select the Output element. In the Details of ECC_CostCenter__ECC2_CostCenter pane, select all
the fields and then right-click and choose Add as Attribute.
34. Select the attribute LOGSYS. In the Properties pane, overwrite the name to change it from LOGSYS
to LogicalSystem.
35. Select the Output element. Check the Properties panel to see that the property “Enforce SQL
Execution” is set to ‘True’ and the property “Allow Relational Optimization” is set to ‘False’. You will
learn more about these properties in a later section of this guide.
36. Click the Save and Validate
icon.
37. Check the Status of the Job Log. It should state “Completed with warnings”.
38. Click the Save and Activate
icon.
39. Check the Status of the Job Log. It should state “Completed with warnings”.
40. Click the Data Preview
icon.
41. Review the Raw Data tab of the Data Preview. Notice the LogicalSystem and SourceSchema fields
are included in the output.
42. Click the X on the ECC_CostCenter__ECC2_CostCenter tab to close the Data Preview.
43. Click the X on the sap.hba.cross.ECC_CostCenter__ECC2_CostCenter tab to close the View
Definition.
44. Epilogue.
Note or Important
In the preceding steps you were instructed to add table T000 directly into the new view. To
be consistent with SAP HANA Live modeling guidelines for reusability, you may want to
create a view called “LogicalSystem” on top of table T000. Then use this LogicalSystem
view to join to other views, instead of using table T000 directly.
Also if you want to expose such views to reporting clients, then SAP HANA Live uses the
convention of creating a so-called “Query” view on top of the reuse views. In the query view
variables or input parameters are created for important fields such as SAPClient. You are
recommended to follow the same best practice for your models.
45. End of section.
STEP-BY-STEP PROCEDURE – USE CASE 2
6.
Use Case 2
1) A combination of dissimilar data from different SAP Business suite systems, but which
shares a common field to make a join
Example
View a CRM Opportunities report that is extended with Customer Basic Data that is sourced
from ERP
6.1
Initial Setup
...
In this step you will examine your HANA instance to know that you have both SAP CRM and ERP
data in two separate database schemas.
1. From the SAP HANA Studio, select the Administration Console perspective.
2. This section of the HTG starts with the position that all SAP HANA Live relevant tables needed for
SAP CRM and ERP exist in a central HANA system under unique schemas, such as shown here as
SAP_CRM and SAP_ECC. Note that your schema names may differ from these examples and will
probably correspond to how your SLT connection was defined in a side-by-side HANA landscape, or
how the CRM or ERP on HANA system were installed in an integrated environment.
6.2
Display Calculation View OpportunityHeader
...
In this step you will get started with using the delivered content of SAP HANA Live. Specifically you
will work with the reuse view named OpportunityHeader.
1. From the SAP HANA Studio, select the Modeler perspective.
2. From the Quick Launch tab, click on the Select System button
HANA system from the list of available systems.
and choose a SAP
3. From the navigation pane, below the selected SAP HANA System, expand the Content folder to
display sap.hba.crm. Expand the Calculation Views folder and scroll down to locate the calculation
view named OpportunityHeader. Optionally, you can right-click on the crm package and use the
context menu option to Find the calculation view called OpportunityHeader.
4. Open the View Definition by clicking on the OpportunityHeader view.
5. Notice the surprising complexity in the virtual data model that is needed for querying the
Opportunity Header data. This view is built on the foundation of view CRMD_DHR_HEADOPP and
joins 33 additional views of CRM attribute data. The delivered virtual data model of SAP HANA Live
removes the difficulty of working directly with CRM tables. Since CRM tables often use a technical
UUID field as the table key. This UUID field has to be joined to another table to allow inclusion of
attributes or master data that is understandable to users.
6. Notice how the following Properties are set when dealing with SAP HANA Live content such as the
OpportunityHeader calculation view.
Note or Important
Multidimensional reporting
If this property is disabled you can create a calculation view without any measure, and the view is not
available for reporting purposes.
Enforce SQL Execution
Due to early bugs with this property, use ‘True’ only with Revision 53 weekstone 16 and higher.
For SAP HANA Live the default value is ‘True’. If this property is set to ‘True’ then optimization and
execution of the query occurs in the SQL engine. If this property is set to ‘False’ then optimization and
execution of the query occurs in the calculation engine. If the property value is set differently among
stacked calculation views, the top-level view in the query determines behavior at runtime.
Allow Relational Optimization
When this property is set to ‘True’ it allows the engine to do critical relational optimizations such as to
simplify nested SQL statements, for example to turn:
"SELECT A, SUM(X) FROM (SELECT * FROM AV/CV) GROUP BY A”
Into 
"SELECT A, SUM(X) FROM AV/CV GROUP BY A"
However there can be side effects where counters and SELECT COUNT might deliver wrong results, so
the recommendation is to use the default value of ‘False’.
7. Click the X on the sap.hba.crm.OpportunityHeader tab to close the View Definition.
6.3
Create Cross-System Calculation View
...
In this step you create a new calculation view by combining two delivered views available from the
SAP HANA Live content. In the new view, you will use opportunity header data from CRM joined to
customer basic data from ERP. Next you will create a calculated column called
“CustomerAgeGroup” to segment the customers based their master record creation date in the
ERP system. Finally, you will you this new column in an analysis with the number of opportunities
available from CRM.
1. From the navigation pane, below the selected SAP HANA System, expand the Content folder to
display sap.hba.cross. Right click on cross and select New  Calculation View...
2. Define the calculation view properties as follows. Click Next when input is complete.
Field
Value
Name
CRM_OpportunityHeader__ECC_CustomerBasic
Description
CRM_OpportunityHeader__ECC_CustomerBasic
Package
sap.hba.cross
Create New
< selected >
Copy From
< not selected >
View Type
Graphical
< selected >
SQL Script
< not selected >
3. From the Select Objects screen, expand the Content folder and locate the OpportunityHeader view
from the sap.hba.crm package. Hint: you can input OpportunityHeader and click the search
button.
4. Select this view and Add it to the right side panel.
5. From the Select Objects screen, expand the Content folder and locate the CustomerBasicData view
from the sap.hba.ecc package. Hint: you can input CustomerBasic and click the search
button.
6. Select this view and Add it to the right side panel. Then click Finish.
7. The new calculation view is displayed in the graphical editor.
8. Use the Tools Palette to add two projection elements.
9. Drag these Projection elements over the view objects as shown below.
10. Connect the Views to the Projections.
11. Select the left Projection. Select all the fields and then right-click and choose Add To Output.
12. Select the right Projection. Select only the SAPClient, Customer, and CreationDate fields and then
right-click and choose Add To Output.
13. Select the right Projection. Under the Output of Projection_X, select Calculated Column. Rightclick and select New.
14. Input the values of the Calculated Column as follows, and then click Add. The intent here is to
create a runtime column which represents the customer age group in the format YYYYMM. You
will use an expression against the Creation Date of the customer master record. The expression
uses the INT() function to convert the date to an integer, as well as the left string function to return
only the first 6 digits of the date corresponding to the year and month.
Expression
int(leftstr("CreationDate",6))
15. Use the Tools Palette to add a join element.
16. Drag the join element over the objects as shown below.
17. Connect the Projections to the Join.
18. Select Join element. Define a Left Outer Join using the following fields and cardinality n..1:
Projection_1
Projection_2
SAPClient
SAPClient
Prospect
Customer
Recommendation or Tip
Be certain that the SAP client is the same when joining two or more cross-systems like CRM
and ERP.
19. Select the following fields and then right-click and choose Add To Output.
Projection_1
Projection_2
< all fields >
CreationDate
CustomerAgeGroup
20. The Output of Join_3 should be all fields:
21. Connect the Join to the Output.
22. Select the Output element. Select the following fields and then right-click and choose either Add as
Attribute or Add as Measure.
Attributes
Measures
< all fields except the measures >
ExpectedNetAmount
NmbrOfOpptyWithSuccssrSlsOrder
NumberOfOpportunities
23. The Output should include all fields:
24. Select the Output element. Check the Properties panel to see that the property “Enforce SQL
Execution” is set to ‘True’ and the property “Allow Relational Optimization” is set to ‘False’.
25. Click the Save and Validate
icon.
26. Check the Status of the Job Log. It should state “Completed with warnings”.
27. Click the Save and Activate
icon.
28. Check the Status of the Job Log. It should state “Completed with warnings”.
29. Click the Data Preview
icon.
30. Review the Raw Data tab of the Data Preview. Notice the CreationDate and CustomerAgeGroup
fields from the customer master in ERP are included in the output with the opportunity header data
from CRM.
31. Click on the Analysis tab. Drag the CustomerAgeGroup measure under the Labels axis. Drag the
NumberOfOpportunties measure under the Values axis.
32. End of section.
www.sap.com