TZHANA for Application Consultants
Material Number: 50104428
Version: 96
© SAP AG 2011
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.
SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver, Duet, Business ByDesign, ByDesign, PartnerEdge 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 in several other countries all over the world. All other product and service names mentioned and associated logos displayed are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary.
The information in this document is proprietary to SAP. This document is a preliminary version and not subject to your license agreement or any other agreement with SAP. This document contains 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. SAP assumes no responsibility for errors or omissions in this document. SAP does not warrant the accuracy or completeness of the information, text, graphics, links, or other items contained within this material. This document is provided without a warranty of any kind, either express or 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 Weitergabe und Vervielfältigung dieser Publikation oder von Teilen daraus sind, zu welchem Zweck und in welcher Form auch immer, ohne die ausdrückliche schriftliche Genehmigung durch SAP AG nicht gestattet. In dieser Publikation enthaltene Informationen können ohne vorherige Ankündigung geändert werden.
Einige von der SAP AG und deren Vertriebspartnern vertriebene Softwareprodukte können Softwarekomponenten umfassen, die Eigentum anderer Softwarehersteller sind.
SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver, Duet, Business ByDesign, ByDesign, PartnerEdge und andere in diesem Dokument erwähnte SAP-Produkte und Services sowie die dazugehörigen Logos sind Marken oder eingetragene Marken der SAP AG in Deutschland und in mehreren anderen Ländern weltweit. Alle anderen in diesem Dokument erwähnten Namen von Produkten und Services sowie die damit verbundenen Firmenlogos sind Marken der jeweiligen Unternehmen. Die Angaben im Text sind unverbindlich und dienen lediglich zu Informationszwecken. Produkte können länderspezifische Unterschiede aufweisen.
Die in diesem Dokument enthaltenen Informationen sind Eigentum von SAP. Dieses Dokument ist eine Vorabversion und unterliegt nicht Ihrer Lizenzvereinbarung oder einer anderen Vereinbarung mit SAP. Dieses Dokument enthält nur vorgesehene Strategien, Entwicklungen und Funktionen des SAP®-Produkts und ist für SAP nicht bindend, einen bestimmten Geschäftsweg, eine Produktstrategie bzw. -entwicklung einzuschlagen. SAP übernimmt keine Verantwortung für Fehler oder Auslassungen in diesen Materialien. SAP garantiert nicht die Richtigkeit oder Vollständigkeit der Informationen, Texte, Grafiken, Links oder anderer in diesen Materialien enthaltenen Elemente. Diese Publikation wird ohne jegliche Gewähr, weder ausdrücklich noch stillschweigend, bereitgestellt. Dies gilt u. a., aber nicht ausschließlich, hinsichtlich der Gewährleistung der Marktgängigkeit und der Eignung für einen bestimmten Zweck sowie für die Gewährleistung der Nichtverletzung geltenden Rechts.
SAP übernimmt keine Haftung für Schäden jeglicher Art, einschließlich und ohne Einschränkung für direkte, spezielle, indirekte oder Folgeschäden im Zusammenhang mit der Verwendung dieser Unterlagen. Diese Einschränkung gilt nicht bei Vorsatz oder grober Fahrlässigkeit.
Die gesetzliche Haftung bei Personenschäden oder die Produkthaftung bleibt unberührt. Die Informationen, auf die Sie möglicherweise über die in diesem Material enthaltenen Hotlinks zugreifen, unterliegen nicht dem Einfluss von SAP, und SAP unterstützt nicht die Nutzung von Internetseiten Dritter durch Sie und gibt keinerlei Gewährleistungen oder Zusagen über Internetseiten Dritter ab.
Student Exercise Manual
Material Number: 50104428
Name
HANA 1.0 GA
2
Table of Contents
Table of Contents...2
Lesson 2: Overview ...3
2.1 Exercise: Look and Feel...3
Lesson 3: Architecture Overview ...15
3.1 Exercise: Architecture...15
Lesson 4: Data Provisioning ...19
4.1 Import SAP ERP Metadata into the SAP HANA Database ...19
4.2 Upload Data into HANA Using Data Services...25
Lesson 5: Modelling Overview...39
Scenario CO-PA...39
5.1 Create the Attribute Views...40
5.2 Create the Analytical View for Actuals ...51
5.3 Create the Analytical View for Plan ...58
5.4 Create the Calculation View ...65
5.5 (Optional) Calculation View: SQL Script...73
5.6 Export a Model ...78
5.7 Import a Model ...81
Lesson 6: Reporting ...84
6.1 Exercise: Excel Pivot Tables (ODBO/MDX access)...84
6.2 Exercise: SAP BusinessObjects Explorer...91
6.3 Exercise: Analysis for Office using a local ODBC connection...97
Lesson 7 User Management and Security...104
7.1 Create New User and Assign Basic Privileges ...104
7.2 Create and assign Analytic Privileges...115
Appendix...125
SQL Script Example (UNION)...125
3
Lesson 2: Overview
This section includes an exercise on look and feel of HANA. In this exercise you will log on to the WTS server, and get familiar with navigating in HANA. You will complete the exercise by creating your own package.
2.1 Exercise: Look and Feel
1. To Logon to the WTS landscape go to: 2. Start-Menu
Choose Common-Training
3. The In-Memory Computing Studio On has not yet been installed on the WTS desktop.
For this reason, you have to go via a remote desktop connection to another desktop.
Start the Remote Desktop Connection as shown in the screenshot on the right side. Use the path: Start Menu -> All
4
4. In the next screen you have to choose one of the available remote desktop servers.
Note: Choose the server RA-HANA10-XXX . (XXX will be specified by the instructor)
Click > Connect.
5. In the Remote Desktop Connection dialog box enter the following user name and password.
User: train-xx (where xx represent your assigned number.)
Pwd: will be provided by the instructor
Click > OK.
6. Now you are on the right desktop and can start the In-Memory Computing Studio by going to Start -> Programs -> SAP in-memory computing ->
5
7. Open Administration Console
HINT: Hit to close the overview screen.
8. Register a new System. Right Click > Add System... within the Navigator view.
Enter the Hostname, Instance Number and Description. Hostname : lt00XX.wdf.sap.corp (XX will be specified by the instructor)
Instance Number : YY (will be provided by the instructor) (see screenshot for details)
6
9. Enter your assigned User and Password credentials.
Username: STUDENTxx where xx represents your assigned number Password: will be provided by the
instructor Click > Finish.
10. The first customization you should do is now to adjust the default setting of the default client in the studio preferences.
Therefore go in the top menu and choose Window -> Preferences...
7
11. Next, expand the Information Modeler node in the tree and click on Default Model Parameters .
Now ensure that the client is 800. Default Language: dynamic
12. The whole perspective setup can be maintained via the Window menu on top.
You have access via Window -> Open Perspective -> Other to all pre-delivered and saved perspectives.
8
13. To switch easily between different perspectives you can click on the toolbar on top on the corresponding button.
Each open perspective will be shown here.
14. Customize a perspective choose the Window menu -> Customize Perspective.
15. In the perspective customization the user can change Tool Bar, Menus, Command Group Availabilities and Shortcuts.
If an element is greyed out it means this element is not available. Please keep in mind that the studio
is running in the Eclipse Framework and not all components of Eclipse are required for HANA.
9
16. To open a new view just go to the window menu again and drop down menu Show View -> Other...
17. In the Show View dialog box you can choose which view you want to add to your perspective.
Next, double click on the view or click on OK after selecting the view to add it in your perspective. Then, you can drag the view to a
sidebar; put the view as footer or on top of the perspective.
10
18. For the SAP HANA Studio the central point of Access is the Navigator View, which is usually placed on the left side of the screen.
In the Navigator tree you can incorporate several SAP HANA instances directly connected with the appropriate user.
Note that there are two main sections in the navigation tree.
The Catalog node navigates to the physical tables, views, etc. The Content node
navigates to Attribute Views, Analytic Views, Calculation Views, Functions and Analytical Privileges
19. The Catalog contains all Database schemas which will be automatically created when a user is created.
Each Schema contains a folder for Column Views, Functions, Tables and Views.
Also notice the Catalog has a folder for Authorizations and another folder where Public Synonyms will be maintained.
11
20. The Folder Content contains the Information Model packages.
Each package contains a folder for Attribute Views, Analytic Views, Calculation Views, Analytic Privileges and Procedures.
21. The Navigation tree can be customized via a small dropdown icon.
22. In the Navigation tree customizing you can select which items should show up in the tree as a node and which should not show up.
So you can show/hide the Information models in the
Administration console, show/hide the Administration node, etc.
12
23. After all customizing and you brought a lot of trouble in your screen there is an easy way to RESET the perspective!
Reset the Perspective now in preparation for the step 29.
24. The Administration console reflects the pre-delivered Administration
Perspective.
To get to this screen, choose the Navigate tab followed by a click on the administrative icon (looks like tools).
According to your user rights you have in this perspective options to administrate the SAP HANA Instances.
25. Create a delivery unit that will contain all your models. Delivery units can be transported.
First Open the Information Modeller perspective.
From the Help menu > Select Quick Launch.
13
26. Select Delivery Units from the Setup section.
27. Click on Create.
28. Enter STUDENTXX for the Name and Responsible person, enter 1.0 for the Version. Remember to replace XX with your assigned student number. HINT: In order to create delivery units ensure that your user ID has the following System Privileges assigned: REPO.MAINTAIN_DELIVERY_UNITS or the following Role assigned: REPO_ADMIN_ROLE
14
30. Create a new package. Select the Content folder > Right Click > New Package.
31. Enter studentXX for the package name & description. Ensure to select your Delivery Unit (STUDENTXX). Remember to replace XX with your assigned student number.
32. As a result you will see the following folder structure created automatically for you under the newly created package.
33. Select your package and verify that the Delivery unit is assigned correctly. The delivery unit assignment can be changed here as need be.
15
Lesson 3: Architecture Overview
This section includes an exercise on Architecture. The goal is to find out architecture-related information about some tables.
In this section you will learn:
How to see whether a table is in row store or in column store How to see what indexes are defined for a table.
We do this example for table MARA in the SYSTEM schema; and for table P_SCHEMAS_ from the SYS schema.
3.1 Exercise: Architecture
1. Log on to the SAP HANA Studio with your user.
For this exercise it does not matter whether you are using the
Administration perspective or the Information Modeler perspective. So let s start looking for table
MARA .
2. In the Navigator Tree, expand the node Catalog
3. Expand the node for the TRAINING -Schema. You may try what happens when you expand the Tables folder within that schema.
16
4. This shows the tables in that schema. We don t want to search this way. So we collapse the Tables folder again.
5. Right-click the Tables folder and select Filter from the context menu. This opens a dialog box.
6. In that dialog box, you can enter a table name.
Note: you are opening the list of tables within schema TRAINING , so we are searching for tables within the TRAINING schema.
Therefore, do not specify the Schema name in the search field. Enter MARA in the input field and
confirm with OK
17
7. After a short while, this will filter the list of tables by all tables whose names starts with MARA .
8. Expand the list of tables. That list should
now be filtered.
9. The context menu for a table offers several options. We are interested in the table metadata which we can reach by
Opening the table Definition (or by double-clicking the table ).
Other options which are not used here include:
Open Data Preview: Explore the table contents
Open Content: simple display of the table contents.
18
10. Once you double-click the table, the table definition is displayed.
This screen shows the table structure, i.e. all fields of the table with their types etc.
Whether the table is in column store or in row store is shown in the top-right corner of this screen.
MARA obviously is a column-table. 11. The key fields of the table are marked in
column Key .
For MARA, these are the client and the material number, aka MANDT and MATNR .
12. Let s now look at the table
P_QUERYPLANS from the SYS schema.
You should be able to verify that: The table is a row-store table
There are one indexes defined: IDX_P_QUERY_PLANS which is a
unique index with indexed column NAME sorted in ascending order;
19
Lesson 4: Data Provisioning
This section includes an exercise on data services.
4.1 Import SAP ERP Metadata into the SAP HANA Database
1. Before importing metadata into the SAP HANA Database, you must verify the connection settings to your SAP BusinessObjects Data Services instance. Please note that the Import Server settings are specific to a single HANA server instance.
Please navigate to Information Modeler Perspective and Open the Quick-Launch view
Click on the Manage Connections... button and connect to the HANA instance in which you wish to import metadata (Select the STUDENTXX instance - Remember to replace XX with your assigned student number)
Click on Configure Import Server
In case the configuration is empty, use the following connection details and hit the apply button.
Server Address: wdflbmt2287.wdf.sap.corp Repository Name: DSREPO
20
ODBC Data Source: <empty>
2. Select Quick Launch -> Content -> Import
In the import screen you can search an import source by entering the name of the source in the text field or,
Click on Information Modeler folder -> Source Objects --> Next.
3. Select the target system (your HANA-system) and click Next.
21
4. You can display connections of DataStore type SAP Applications that are imported from the specified
DataServices repository. This datastore is already created in Data Services Repository.
In our exercise we want to import metadata from ERP-Table TCURR, therefore you don´t have to change the default settings.
Hint: If no DataSource Connection is displayed, close and reopen the Studio.
22
5. Select the depending objects for Import by two ways:
Use the ERP application hierarchy to drill down to your required table. Hint: In case of many hierarchy
levels you should use second two and enter table name directly.
Search for TCURR using the search field.
Select the TCURR in the object list and click on add to put it to target list.
NOTE: This screenshot and the next have a description of
Exchange Rate . You will not have that this screenshot is incorrect.
23
6. Select your Schema (studentXX) from the default catalog for the metadata import.
24
Click on Validate.
Make sure the status column has a green checkbox.
Click on Next.
Click on Finish
A success message in the Job log will confirm the metadata table import. Right click on the STUDENTXX Schema
in the Navigator pane and click on Refresh.
You should now see the TCURR table under the Tables folder.
25
4.2 Upload Data into HANA Using Data Services
Import SAP ERP Metadata into SAP BusinessObjects Data Services
1. Start to SAP BusinessObjects Dataservices Designer
Use the path: Start Menu -> Programs -> SAP BusinessObjects Data Services 4.0 SP1 -> Data Services Designer.
2. Enter your assigned User and Password credentials.
Username: studentXX where XX represents your assigned number. Password: will be provided by the
instructor. Click > Log On.
26
3. If the log on process is successful, the repository DSREPO will appear, then click on OK
4. Go back to the SAP BusinessObjects Data Services Designer and go to the Datastore tab.
5. Select the DS_ECC Datastore you created previously and make sure you can see the Tables child node.
6. Right-click on the Tables node and click on Import By Name...
27
7. Enter TCURR as the name of the table you want to import.
28
You can now use the TCURR table from the DS_ECC Datastore as either a data source or a data target. For this exercise, it will be your data source.
29
Create a new DataStore for HANA in SAP BusinessObjects Data Services
1. In the Local Object Library, create a new Datastore.
Right-click on the white background. Choose New
2. In the Local Object Library, create a new Datastore.
Datastore name: HANA_STUDENTXX Datastore type: Database Database type: HANA Database version: HANA 1.x Data source: HANA_HDB User name: STUDENTXX Password: as provided by the
30
Import SAP HANA Database Metadata into SAP BusinessObjects Data Services
1. Right-click on the Tables node of your new Datastore HANA_STUDENTXX and click on Import By Name...
2. Enter the following... Name - TCURR Owner - STUDENTXX
31
3. You can now use the TCURR table from the HANA_STUDENTXX Datastore as either a data source or a data target. For this exercise, it will be your data target.
32
Create a batch job in SAP BusinessObjects Data Services to extract data from SAP ERP and
load it into SAP HANA Database
1. Click on the Create Project link of the Getting Started section.
2. Enter STUDENTXX as the project name 3. Click on Create.
4. In the Project Area located on the left side, right-click on your new project and click on New Batch Job.
5. Enter JOB_STUDENTXX as the name of the new batch job
33
6. Click on the Dataflow icon from the toolbar located on the right side of the main SAP BusinessObjects Data Services Designer window.
7. Click on the canvas to the left of the toolbar to add a new Dataflow to your new batch job.
8. Enter DF_STUDENTXX as the Dataflow name.
9. Double-click on the new Dataflow
10. Click on the TCURR table from the DS_ECC Datastore, drag onto the canvas and drop it.
34
11. Click on the TCURR table from the HANA_STUDENTXX Datastore, drag onto the canvas, drop it and click on Make Target.
12. Click on the Query transform icon and click on the canvas.
13. Once you have all three items on the canvas placed like this, click on the small square on the right-side of the source TCURR table and drag it to the small square on the left side of the Query.
14. Then click on the small square on the right-side of the Query and drag it to the small square on the left side of the target TCURR table.
35
15. Once you ve connected all three items, double-click on the Query transform in the middle.
16. From the Schema In located on the left side, select all the fields under the TCURR schema and right-click and click on Map to Output. This will map all fields from the inbound schema to fields of the output schema that have the same name.
17. Close the Query window and go back to the Dataflow.
18. Validate the dataflow by clicking on the Validate icon from the toolbar located above and a little bit to the left of the canvas where you created the Dataflow
36
The result of the validation should show that there are no errors in your Dataflow. If there are, please notify the instructor.
19. Go to the project area in the upper left-hand corner and right-click on your newly created job and click on Execute.
20. If you are prompted to save changes, click on yes.
21. Click OK on the Execution Properties window to accept all defaults.
37
A log is written during the execution of the batch job. If the batch job is successful, you will see the following text at the bottom of the log.
22. Go to the SAP HANA Studio, find your Schema (STUDENTXX), open the Tables folder and right-click on the Exchange Rates (TCURR) table and click on Open Data Preview.
38
Here are the results of your SAP
BusinessObjects Data Services batch job. You ve successfully extracted data from an SAP ERP system and loaded it into SAP HANA Database. Congratulations!
39
Lesson 5: Modelling Overview
Lesson Objectives
The SAP HANA 1.0 hosts an application tool called SAP HANA Studio which incorporates the data modeling
function within Information Modeler perspective.
After completing this lesson, you will be able to
Use the Information Modeller
Create Attribute, Analytic, and Calculation views: Add or remove text tables
Create basic JOIN operations Activate and deploy views Export and import models
Scenario CO-PA
You are at a customer site and have been asked to build an Information Model for HANA for the purpose of displaying CO-PA data. You have been asked to produce the following 3 reports. This section takes you through the process to create the models to be used later in the reporting exercise.
40
5.1 Create the Attribute Views
1. Please navigate in the Content to your own package.
NOTE: Please make sure you select your own package (STUDENTXX where XX represents your assigned number).
2. Create a new Attribute View: Select the Attribute View of your
package and using the context menu (right click), New > Attribute View to create new Attribute View.
Enter the name of the Attribute View: LOCATION_XX and description, Customer Location XX.
Press next to Attribute View to the select objects Screen.
42
3. Choose TRAINING and select the table KNA1 to build the Attribute View.
Enter KNA1 then hit the add button.
Hint: Using Search help for selecting the right tables OR use the solution icon on upper right.
4. Repeat the above steps to add T005U.
Click Finish to get into the Attribute View Editor Hint: If the dialog box
disappears and you do not have an Add button experiment with the Add Tables icon on upper right.
Tip: In case the dialog below "New Attribute View" disappears, you can hit the "Add table" button and continue the table selection.
43
5. The Attribute View editor gets populated with the selected tables.
This is where you will define the relationships of the attributes.
6. User will now select the fields for use in the attribute view
Select the key field KUNNR and right mouse click on field. Choose Add as Key Attribute. Result: The
KUNNR:KNA1.KUNNR shows in the Output frame.
44
7. Display the properties of KUNNR. Select KUNNR from the Output frame.
On the Property Tab for field KUNNR, select Description Mapping and choose the field TRAINING.KNA1.NAME1 from the drop down list.
8. Join field LAND1 from KNA1 to LAND1 of T005U as a text table join.
Click on the join and change the join type in the property view. Text Table (Join Type) 1..1 (Cardinality) . Select SPRAS for the Language Column.
9. Join field REGIO from KNA1 to T005U field BLAND.
As before set the Join type to Text Table, 1..1 (Cardinality) . Select as Language Column
-> SPRAS Save.
45
10. Add a second text table to the attribute view.
You can do it by selecting the "Add table" button and continue the table selection. Enter table T005T and select
the TRAINING table from the list.
Click OK.
Result: Table T005T is added to the Data Foundation tab.
46
11. Define a text join between field LAND1 from KNA1 to field LAND1 from T005T
Right click on join and change the join type in the property view.
At the join Property frame select as Language Column -> SPRAS
HINT: Don t forget to make the join type = textTable.
12. Add KNA1.LAND1 as a attribute with a right click on LAND1 -> Add as Attribute.
Select LAND1 from the Output frame
On the Property Tab for field LAND1, select Description Mapping and choose the field TRAINING.T005T.LANDX. HINT: Only select or highlight LAND1 to get to the property tab.
13. Select REGIO from the Output. On the Property Tab for field
REGIO, select Description Mapping and choose the field TRAINING.T005U.BEZEI. Save.
47
14. Add ORT01 as an attribute. This is a description field for
CITY so in this case there is no need to map to a text table. Save.
15. On the Attribute View
LOCATION_XX, right click and select Activate.
Result: The Deployment Log will show successful or give an error message.
HOT TIP: If presented with the error message attribute view needs at least one key attribute or key measure you likely saved at a point in time when no key attribute was selected. To solve this problem, close the studio and open the studio again. Activate the attribute view without opening it.
48
16. Choose Attribute View, LOCATION_XX. Right click to choose Data Preview.
Note that KUNNR, LAND1 and REGIO have a description displayed in the Data Preview.
17. Navigate to the Catalog . Select the folder _SYS_BIC, expand Column Views and locate the prefix
studentxx/LOCATION_XX.
18. You may also display the definition of the Attribute View, by right clicking on the column view,
studentxx/LOCATION_XX and choose Definition.
49
which will be needed for reference when creating an Attribute View for use on Calculation Views. (We ll do this in a later exercise.)
19. Next create a new Attribute View for product.
Name it PRODUCT_XX with description PRODUCT_XX.
Join table MARA and table MAKT
Define the join as a text join on the properties tab.
Define the language column as SPRAS on the Property Tab. Add MATNR as key attribute to
the Output Frame
Define the description mapping for the key attribute
MAKT.MAKTX.
51
5.2 Create the Analytical View for Actuals
1. Close all open views prior to creating a new analytical view.
Create an analytical view CEA1_XX (XX being your student number allocated), with the description Contribution Margin for Actuals. Click Next
2. Find a table, CE1IDEA and click Add. Remember you have to find the
table by clicking on the arrow next to the input field
Important Do not click finish or you will have to start over.
52
3. Navigate to your package, expand the folder and select the attribute views for PRODUCT_XX and LOCATION_XX.
Click on Finish.
4. Choose the Data Foundation Tab. Result: You will see table CE1IDEA
in your data foundation tab.
5. Navigate to the Logical View Tab to see the two attribute views.
Note that the Data Foundation is empty.
It will be filled once you select your fields (attributes and measures)
53
6. Navigate to the Data Foundation Tab. Select the two fields KNDNR and
ARTNR and add them as attributes (right click).
7. Navigate to the Logical Tab.
Notice that the Data Foundation now has two fields.
Join DataFoundation.ARTNR to PRODUCT_xx.MATNR. Join DataFoundation.KNDNR to
LOCATION_xx_KUNNR.
8. Navigate to Data Foundation Tab, where we will define further selections.
Apply filters on the fields PALEDGER and VRGAR (record type).
Select PALEDGER > right click > Apply Filter.
Choose Operator Equal from the Apply Filter dialog box
Specify the value 01. For VRGAR, choose Operator Equal
and specify the value F. Save.
54
9. Notice there is now a filter icon next to the PALEDGER and VRGAR.
10. Next select the attributes to be included in the analytical view.
Choose from CE1IDEA the following fields:
PERIO, VKORG, PLIKZ.
HINT: To find the fields quickly enter the name of the filed in the Find Column field.
11. Next select the measures to be included in the analytical view.
Choose from CE1IDEA the following fields:
VV010 VV070 VV290 VV960
55
12. Rename the measures in the Name field of the Property Tab for each measure.
Use copy and paste from the table to ensure that use the exact descriptions. Change From Change To VV010 GrossRevenue VV070 SalesDeduction VV290 ProductionVariance VV960
OtherExpenses
Save.13. Next define the calculated measures to be included in the analytical view.
Right click on Calculated Measures and choose New.
56
14. Create the first Calculated Measure, NETREVENUE.
Select Decimal data type with length 15,0
Double click on the desired measure for it to appear in the expression editor. Either type in the minus sign or double click on the Operator.
Continue to create all other needed Calculated Measures.
Name Description Data Type Length Formula
NETREVENUE NetRevenue DECIMAL 15 "GrossRevenue"-"SalesDeduction" CM1 Contribution Margin1 DECIMAL 15 "NetRevenue"-"ProductionVariance" CM2 Contribution Margin2 DECIMAL 15 "CM1"-"OtherExpenses"
57
15. Display the data in your Analytical View. Right click on Analytical View and
58
5.3 Create the Analytical View for Plan
16. Close all open views prior to creating a new Analytical view.
Create an analytical view CEP1_XX with the description Contribution Margin for Planning.
17. Find a table, CE2IDEA and click Add. Remember you can find the
table by clicking on the arrow next to the input field
Click Next.
Important Do not click finish or you will have to start over.
18. Navigate to your package, expand it and select attribute views for PRODUCT_XX and LOCATION_XX.
59
Click on Finish.
19. Choose the Data Foundation Tab. You will see CE2IDEA in the data
foundation tab.
20. Choose the Logical View Tab to see the two attribute views created previously.
Note that the Data Foundation is empty.
60
21. Navigate to the Data Foundation Tab again.
Add KNDNR and ARTNR as attributes (right click).
22. Navigate to the Logical Tab again. Notice the Data Foundation now has
two fields.
Join fields ARTNR and MATNR. Join KNDNR and KUNNR.
23. Navigate to Data Foundation Tab, where we will define further selections.
Apply filters on the fields
PALEDGER and VRGAR (record type).
Select PALEDGER > right click > Apply Filter.
Choose Operator Equal from the Apply Filter dialog box
Specify the value 01.
For VRGAR, choose Operator Equal and specify the value F.
61
24. Notice there is now a filter icon next to the PALEDGER and VRGAR.
25. Next select the attributes to be included in the analytical view.
Choose from CE2IDEA the following fields:
PERBL, VKORG.
26. Next select the measures to be included in the analytical view.
Choose from CE2IDEA the following fields:
VV010001, VV070001 VV290001 VV960001.
62
27. Rename the measures in the Name field of the Property Tab for each measure.
Use copy and paste to ensure the exact names. Change From Change To VV010001GrossRevenue VV070001SalesDeduction VV290001ProductionVariance VV960001OtherExpenses Save.
28. Next define the calculated measures to be included in the analytical view.
Right click on Calculated Measures and choose New.
63
29. Create the first Calculated Measure, NETREVENUE.
Select Decimal data type with length 15, 0
Double click on the desired measure for it to appear in the expression editor. Either type in the minus sign or double click on the Operator.
Continue to create all other needed Calculated Measures.
Name Description Data Type Length Formula
NETREVENUE NetRevenue DECIMAL 15 "GrossRevenue"-"SalesDeduction" CM1 Contribution Margin1 DECIMAL 15 "NetRevenue"-"ProductionVariance" CM2 Contribution Margin2 DECIMAL 15 "CM1"-"OtherExpenses"
30. You should have the following Calculated Measures.
64
31. Save and activate your Analytical view. Preview the Analytical view as well to see if you get data.
65
5.4 Create the Calculation View
20. Navigate to the Calculation View under your student package. Right click to create a new Calculation View.
Name your calculation view CE_PLAN_ACTUAL_XX
Description: Plan Actual Comparison for CO-PA
Specify Graphical for the type of the Calculation View.
66
21. Since we will be reading from existing Analytical Views you do not have to select any tables.
Click Next.
22. Select the previously created 2 Analytical Views within the studentXX package. CEA1_XX
CEP1_XX
Replace XX with your student number.
67
23. As a result the Graphical Calculation View editor will open.
24. From the Tools Palette select 2 Projection graphical nodes one for each Analytical View. This is where you will set the Actual versus Planned data indicator.
25. Double Click on each Projection node and rename each to Projection_A and Projection_P
68
26. Add a Union Graphical node and Rename the node to Union.
27. With the mouse pointer hover over each graphical node and draw a connection line between the nodes as shown on the right representing a data flow graph.
69
28. Add a new Calculated Column called KPLIKZ.
In the diagram select the Projection_A node. Within the Output right click on Calculated Columns > New.
29. Enter KPLIKZ for the name of the field. Select INTEGER and enter 0 in the Expression Editor as the Planned Indicator value. Click OK.
30. Ensure the Calculated Column is visible in the Output of Projection_A.
70
31. Add all the fields of CEA1_XX to the Output of the Projection_A node. Select the Projection A node, within the details view select all the fields > Right Click > Add to Output. HINT: Use the CTRL key to select multiple fields except !!!
** DO NOT ADD FIELD PLIKZ ***
32. Proceed to work on the Projection_P node. As before add the field KPLIKZ but this time set the Expression value to 1 as an indication for planned data.
71
33. Since we will eventually use a Union to combine the results from both Analytical views we have to ensure that the field names are identical in both views. After you have added all of the CEP1_XX fields to the Output select the field PERBL (within Output of Projection_P) and then within the properties view rename the PERBL field to PERIO Hint: If renaming is not allowed, close and reopen the Studio.
34. Proceed to select and work with the Union node. Within the details of the Union CTRL select all the fields from the Projection_A node > Right Click > Add to Target.
72
36. Scroll down and select all the fields on the Projection_P node. Since we want to combine the two analytical views we need to map the fields from both views to each other. Right Click > Map to Target.
You can also select each individual field on the left and drag & drop it over the corresponding field on the right hand side.
HINT : You can also use the automatic mapping by name
37. The completed Calculation View mapping should look as follows. Save your work.
73
38. Click on the Output Node in the main diagram. Within the Details view pane select each field > Right Click > Add the field as Attribute or as Measure according to the following.
Activate and Preview HINT :
Make sure that the Participant Element Check is not selected Quick Launch tab -> Setup -> Manage Preferences -> Validation Rules
Uncheck the Participant Element Check under the Calculation View section
74
1. Create a Script based Calculation View. Right click on Calculation views > New > Calculation View...
2. Enter CE_PLAN_ACTUAL_XX2 for the name of the Calculation view.
Select SQL Script for the type and be sure to select the _SYS_BIC schema. Click Finish.
75
4. In the left side of the diagram double click on the Script Node. Before entering code into the middle section click on the icon Define Output Parameter within the Output section in the right side of the screen.
5. Enter all the output fields according including the corresponding Data Type and Length.
WARNING: While defining the Output for the Data type VARCHAR, DO NOT click on the Scale fields.
HINT: List of fields and data types for
the output table. Name Data type Length Scale MATNR VARCHAR 40 0 KUNNR VARCHAR 35 0 REGIO VARCHAR 20 0 LANDX VARCHAR 15 0 ORT01 VARCHAR 35 0 PERIO VARCHAR 7 0 VKORG VARCHAR 4 0 KPLIKZ VARCHAR 1 0 GROSSREV DECIMAL 15 0 SALESDEC DECIMAL 15 0 PRODVAR DECIMAL 15 0 OTHEREXP DECIMAL 15 0 NETREV DECIMAL 15 0 CM1 DECIMAL 15 0 CM2 DECIMAL 15 0
76
6. Refer to the Appendix (SQL Script Example 1) and copy all the SQL code statements and past it between the BEGIN & END section replacing the
var_out = ... code.
You find an electronic version of the SQL Script template in
Start -> My Documents -> My
Documents -> 01 Calc View Template Union.txt
7. The completed SQL Script. Remember to replace studentxx with your assigned number.
Save your work.
8. Click on the Output Node in the Scenario on the left hand side. Within the Script View details select all the Attributes > Right Click > Add as Attribute to move the fields into the outputs over to the right hand side of the screen. Proceed to do the same for the measures.
77
78
5.6 Export a Model
1. Based on what you ve learned thus far, create a new attribute view under your package.
For the purposes of this exercise make the view simple, one table, no joins, etc. Alternatively, you can copy your attribute view student XX.LOCATION_XX.
Name your view DUMMYxx, where xx represents your assigned number Save and activate
2. Export your attribute view. Select File Export.
Select > Information Modeler > Information Models,
Click > Next OR
From the Quick Launch screen, under Content, Select EXPORT
79
3. Select your system, Click > Next.
4. Highlight the Attribute View, DUMMYxx, click > Add
Enter the target location for the Export. HINT: Use the path to the desktop. The process will create a folder on the desktop. The name of the folder should be the same name as the system you are logged into. Within the folder you will find your new exported file.
Click > Finish
The status of the export in the job log should say Success .
If you double click on the log entry you can see the details of the job
80
5. Navigate to the location the file was placed.
Open the file to ensure the XML is within the file.
Congratulations, you ve exported your first file!
81
5.7 Import a Model
6. Now that the export was successful, import the xml file that was created during the export to the RKT system under your own package.
Select File IMPORT OR
From the Quick Launch view, under CONTENT, Select IMPORT
82
7. Select Information Models, Click > Next
8. Select the target system for the import and Click > Next
83
9. Navigate to the file location
In the window Models for Import locate the XML document corresponding to your DUMMYxx attribute view and add it.
Click > Finish
10. Look at the Job Log status of the import. It appears the import of the attribute
view failed.
The reason for the failure is because the view already existed.
Delete the view from your package and re-import.
Confirm the status in the Job log is Success
84
11. In the Navigator window, refresh the folder attribute views in your package studentXX
The DUMMYxx attribute view reappeared
Activate Preview the data
Lesson 6: Reporting
This section includes exercises on various BI client tools: Microsoft Excel, SAP Business Objects Explorer, and SAP BusinessObjects Analysis
After completing this lesson, you will be able to
Understand reporting via SAP Business Intelligence 4.0 Understand connectivity options of the SAP BI Platform 4.0 Understand reporting options via ODBO, ODBC and JDBC
Execute reports via select reporting applications (Analysis for Office, BO Explorer) via these interfaces
6.1 Exercise: Excel Pivot Tables (ODBO/MDX access)
This exercise focuses on the basics of Excel Pivot Table access to HANA data. Excel Pivot Table access is provided via a native ODBO (OLE DB for OLAP). Any ODBO-enabled client can use this driver so this is just one example from Excel 2007:
85
1. Initiate the Excel Data Connection Wizard
Open Excel 2007 Click on the DATA menu Select From Other Sources Select From Data Connection
Wizard
Result: Data Connection Wizard launches
2. Select Other/Advanced drivers then click NEXT
86
3. Select SAP HANA MDX Provider (Naming as of course development time)
Click Next (do not click OK and do not double-click SAP NewDB MDX Provider
87
4. Enter system logon info provided by your instructor.
Host : lt00XX.wdf.sap.corp Instance number : YY User : STUDENTXX Password : provided by the
instructor
Test the connection in order to verify the credentials
5. Select a cube (analytic or calculation view) from the database selection.
Select studentxx from the dropdown Select CE_PLAN_ACTUAL_XX Click NEXT
88
6. Accept the proposed file name for storing the data connection.
Enable Save password in file (Saving password is a
recommended setting for this RKT exercise)
Carefully read the confirmation dialog : click Yes in the confirmation dialog
Click Finish in the Data Connection Wizard
7. To open the connection, simply accept the proposed Pivot Table report properties. If necessary, re-enter your Password.
89
8. An empty Pivot Table should appear, and the list of fields available from the right panel PivotTable Field List
9. Select fields from the right panel and drag and drop them onto the row, column and data items areas of the PivotTable
91
6.2 Exercise: SAP BusinessObjects Explorer
In Internet Explorer, connect to SAP Business Objects Explorer on the BI 4 system. Use the connection details provided by the instructor, the URL should look like
Fehler! Hyperlink-Referenz ungültig.
Here: http://wdflbmt2268.wdf.sap.corp:8080/explorer
Log on using the credentials provided by the instructor: Language: English (United States)
System: <hostname>:<port number> User: STUDENTXX
Password: provided by the instructor Authentication: Enterprise
93
2. Expand SAP High-Performance Analytic Appliance (SAP HANA) 1.0 Expand the Connection that is available
Select the Calculation View studentXX/CE_PLAN_ACTUAL_XX
3. Click on New to create a new Information Space on this calculation view.
4. From the Properties tab, enter a name STUDENTXX_INFOSPACE , check the option Show on Home page , and select the Folder Favorites
94
Go to the next tab Objects , add dimensions and measures to the Information Space by double clicking (or drag and drop) on the objects listed in the left pane. Click OK when your selection is made.
95
5. Index your newly created information space:
96
6. From the top menu bar, go back to the Home tab
7. And refresh the list of information spaces available from the Home page : 8. Open the information space you have just created:
97
6.3 Exercise: Analysis for Office using a local ODBC connection
A pre-beta-version of Analysis for Office (AAO) has been installed in the training system. In this exercise you will use AAO to connect to SAP HANA through a locally defined ODBC connection. The ODBC connection has already been created by the administrator.
1. Start AAO from the Start Menu: a. Start Menu
b. Programs
c. SAP BusinessObjects d. Analysis for Microsoft Excel
98
2. Start the Select Data Source wizard:
3. In order to use a local ODBC connection (instead of authenticating against BOE and using a relational DB connection published to CMC), click skip in the wizard:
99
4. Select the ODBC connection named HANA HDB , click Next
100
101
6. Find and expand your package (studentXX), select one of the views you have created, click OK
102
8. Drag and drop dimensions and measures in the rows and columns to navigate in that view and build your report
104
Lesson 7 User Management and Security
Goal:
This lesson will demonstrate which minimum set of privileges a user needs in order to read data from one
particular data model in SAP HANA.
You will create a reporting user named TESTXX (XX being your group number) which you will supply
with sufficient privileges to read data from the data model you have created earlier.
In this Exercise you will do the following tasks:
- Create a new user TESTXX
- Add Package Privileges to user TESTXX
these will allow browsing the list of packages and seeing the views inside of the packages
it is only needed for preview from inside of SAP HANA Studio. The privilege is not required for
using other reporting tools.
for simplicity, we collected all require package privileges into one role.
- Add SQL privileges to user TESTXX
- Create an Analytic Privilege for an Analytic View, based on one of the Analytic Views you created
- Create an Analytic Privilege for an Analytic View and a Calculation View, based on one of the
Attribute Views you created
- Assign these Analytic Privileges to user TESTXX
- Verify that the security concept works as described.
7.1 Create New User and Assign Basic Privileges
Add user TESTXX to your SAP HANA Studio:
1. In the navigator tree, go to Catalog -> Authorization -> Users
Right-click and create a new User HINT: Make sure to have the role
SAP_USER_ADMIN as a Granted Role.
105
2. Enter user name TESTXX where xx represents your assigned number.
Password: Init1234 Session Client: 800
In our ERP tables, the client is 800. Then SAVE the user.
3. Add a new system with user TESTXX Right click in the Navigator Tree Provide the same server hostname given by the instructor.
106
4. Finish and confirm the new password Suggestion: Abcd1234
Click OK
107
5. Right-click the new entry for System HDB and user TESTXX in the Navigator tree
Choose Refresh from the context menu.
This will update the system status in your Navigator tree.
108
6. Open the user editor for user TESTXX Work as user STUDENTXX
Navigate to the following path: HDB (STUDENTXX)
Default Catalog Authorization Users
Either: Right-click user TESTXX and choose Open
Or: double-click user TESTXX
7. Check all tabs for the different types of privileges (Granted Roles, SQL Privileges, Analytic Privileges, System Privileges and Package Privileges Verify that the only granted privilege is role PUBLIC
8. In the user editor for user TESTXX, set the Session Client field to 800 In our ERP tables, the client is 800. If you have the default client for your data models set to dynamic , the Session Client from your user profile will be substituted at query run time. 9. Save the user profile:
Either click the Save icon Or hit Ctrl+S
We will continue using this user editor for user TESTXX, so do not close it.
109
Verify that the user cannot view your Analytic Views or the Calculation View you
have created:
10. Work as user TESTXX
Try navigating to the following path HDB (TESTXX)
Content studentxx Analytic Views
On trying to expand folder Content you should receive an error message as shown on the right.
This is because access to the content tree (design time versions of Information Models) is restricted by Package Privileges. User TESTXX does not have any Package Privileges assigned.
Add Package Privileges to user TESTXX
11. Switch to the user editor for user TESTXX
Verify that the user who opened the dialog is your user STUDENTXX Within the editor, switch to tab Granted Roles
User who opened the editor
110
12. There is a predefined role that contains all privileges needed to browse the Content Tree and allow you to attempt a preview.
Click the green -icon to add a new role to user TESTXX.
In the search dialog, start typing REPO_ADMIN_ROLE .
Once the Search-as-you-type finds the desired role, highlight role
REPO_ADMIN_ROLE in the list of Matching items , then click OK
13. Save the user profile: Either click the Save icon Or hit Ctrl+s
We will continue using this user editor for user TESTXX, so do not close it.
Verify that the user cannot view your analytic views nor the calculation view you
have created:
14. Work as user TESTXX Right-click the Content-folder
Select Refresh from the context menu. This should now show the list of all packages in the system.
111
15. Work as user TESTXX Navigate to the following path HDB (TESTXX)
Content studentxx Analytic Views
Right-click the Analytic View CEA1_XX Select Data Preview from the context menu.
(You may do the same for the other Analytic Views or for the Calculation view)
16. Data preview should give you an error message as in the screenshot to the right.
The reason for this message is that the user is missing SQL privileges to access the runtime object of the Analytic View. The runtime object of the Analytic View is the Consumption Column View created in Schema _SYS_BIC.
Add SQL Privileges to the user
There are two ways to grant the SQL Privilege for accessing run-time objects of Information Models. The first way is to use the user editor; the second one is to explicitly run a stored procedure for this purpose.
In this part of the exercise we grant the privilege via the user editor. The stored procedure will be demonstrated later.
112
17. Again make sure the user editor for user TESTXX is opened by user
STUDENTXX.
Switch to tab SQL Privileges . Click the -icon
18. In the search window, start typing studentXX/CEA1_XX (where both occurences of XX must be replaced by your group number).
Hint: including the package name in the search will greatly help you find the required view (compared to only trying to search for CEA1_XX
Select the appropriate cube from the list of Matching items .
Click OK .
19. Now you have selected the object for which you want to grant SQL privileges, you also have to choose what privilege to grant.
For reading from an object, we need to grant the SELECT-privilege.
Highlight the SQL Object studentXX/CEA1_XX Activate the check box for the
SELECT -Privilege.
User who opened the editor
113
20. Repeat the same for Analytic View CEP1_XX.
You may try adding the Calculation View; however, the search dialog typically does not find it
In the next step, we will use an SQL command to add this privilege to user TESTXX.
21. Save the user profile: Either click the Save icon Or hit Ctrl+S
We will continue using this user editor for user TESTXX, so do not close it.
Add the SELECT privilege for the Calculation View
22. Work as user STUDENTXX Highlight the system entry for
HDB (STUDENTXX) In the Navigator tree.
Click the Icon for the SQL editor 23. In the SQL editor, type the following
command (replacing XX by your team number):
Take care to set the quotes in exactly the correct way:
Both parameters (the view name and the user name) have to be enclosed in single quotes.
Within the view name, the schema name ) must not be enclosed in double quote, whereas the name of the object inside of the schema (here:
) has to be enclosed in double quotes. Execute the statement by clicking the green arrow or hitting the F8 -key.
114
24. Verify that user TESTXX has SQL SELECT PRIVILEGES for all three views.
You have to re-open the user editor for user TESTXX in order to see the most recent state of the user profile.
Verify that the user still cannot read from the views
25. Work as user TESTXX Navigate to the following path HDB (TESTXX)
Content studentxx Analytic Views
Right-click the Analytic View CEA1_XX Select Data Preview from the context menu.
26. Data preview should give you a different error message now, see the screenshot to the right.
The reason for this message is the user has SQL access to the run time object, but is still missing an Analytic Privilege. You may test this for the other views as well.
115
7.2 Create and assign Analytic Privileges
Create an Analytic Privilege for the Analytic View CEA1_XX, based on Analytic
View CEA1_XX
Please be very careful when working on this part. Analytic Privileges can presently only be activated once. If you need to change the Analytic Privilege after it has been successfully activated, you will have to delete it and re-create it from scratch.
In the following we are going to grant Analytic Privileges to user TESTXX which will finally allow reporting off the Analytic and Calculation Views. We are restricting access to only Material Number P-103 .
In this first step, we will define the restriction directly the MATNR-field of view CEA1_XX. 1. Work as user STUDENTXX
Navigate to the following path HDB (STUDENTXX)
Content studentxx
Analytic Privileges
Right-click the folder name Analytic Privileges
Select New Analytic Privilege from the context menu.
2. In the creation wizard, type the name: AP_CEA1_P103_XX
(replacing XX appropriately)
Enter a description : Analytic Privilege for view CEA1_XX - restricting to MATNR - P-103
116
3. In the second step of the wizard, select your view studentXX/CEA1_XX Highlight that view in the Content tree on the left.
Click Add this will add the view to the right-hand part of the display. Click Finish .
4. Implementing the privilege is now done in three steps:
1) Select further views for which this privilege should be valid (optional) We will not add further views in this step.
2) Select attributes on which a restriction shall be defined (All fields of all views selected in 1) will be offered)
3) Define value restrictions for the attributes selected in 2)
5. Add the field MATNR to the list of Associated Attributes Restrictions . Click the corresponding Add button Select field MATNR from the presented field list
Click OK
3
2
117
6. Define the value restriction for field MATNR
Highlight the MATNR field under Associated Attributes Restrictions Click the Add button for Assign Restrictions (this increases the counter for the number of restrictions for MATNR)
Click into the Value field in Assign Restrictions . Click the -icon. In the search window, search for Material P-103
Select the material from the search list Click OK
(Note: you could also have typed in the value directly, without opening the search window).
7. Save the Analytic Privilege: Either click the Save icon or hit
Ctrl+S .
Then activate the Analytic Privilege
118
Assign Analytic Privilege AP_CEA1_P103_XX to user TESTXX
8. For granting Analytic Privileges, there are two possibilities: via the User Editor in SAP HANA Studio; or via SQL statements.
We first show how to use the User Editor:
Work as user STUDENTXX
Open the User Editor for user TESTXX In that Editor, switch to tab Analytic Privileges
9. Click the green -icon In the search dialog, start typing
studentXX/AP_CEA1_P103_XX (replacing XX appropriately)
From the list of Matching items , select privilege
studentXX/AP_CEA1_P103_XX Click OK
10. Save the user profile: Either click the Save icon Or hit Ctrl+S
User who opened the editor
119
Verify that the user can read from Analytic View CEA1_XX
11. Work as user TESTXX Navigate to the following path HDB (TESTXX)
Content studentxx Analytic Views
Right-click the Analytic View CEA1_XX Select Data Preview from the context menu.
12. Data Preview should now return a list of 474 output values.
Verify that the list contains only records with MATNR = P-103.
You may verify that preview still does not work for Analytic View CEP1_XX nor for the Calculation View.
120
Create an Analytic Privilege for the Analytic View CEP1_XX and the Calculation
View CE_PLAN_ACTUAL_XX, based on Attribute View PRODUCT_XX
Please be very careful when working on this part. Analytic Privileges can presently only be activated once. If you need to change the Analytic Privilege after it has been successfully activated, you will have to delete it and re-create it from scratch.
13. Finally, we define an Analytic Privilege that will allow reading from the second Analytic View and from the Calculation View.
Work as user STUDENTXX
Create a new Analytic Privilege inside of package studentXX
Name this privilege AP_CEP1_P103_XX
Description : Analytic Privilege for view CEP1_XX - restricting to MATNR - P-103
14. In the second step of the creation wizard, select the Plan Data Analytic View studentXX CEP1_XX Click Finish
