Performing Practical Exercises

1. From SAP Business Application Studio, launch SAP HANA Database Explorer.

   1. Click the (Business Application Studio Menu) icon top left and choose View→Command Palette.

      Alternatively, you can press Ctrl+Shift+P or insert the character > inside the Search Bar.

   2. In the Command Palette, enter Database Expl.

   3. Choose SAP HANA: Open Database Explorer.

   4. If a browser tab shows up to choose an identity provider, choose origin: cis4hcpractice-platform.

      You must either specify the name of the identity provider (variant 1), that is, cis4hcpractice-platform, or choose in the list of identities the one that is associated with it (variant 2). If several identity providers are listed but none is cis4hcpractice-platform (variant 3), choose Sign in to another account and proceed with the variant 1 workflow.

      

   Result

   SAP HANA Database Explorer opens in a new browser tab.

   Note

   Your SAP HANA Database Explorer might show no connections at all, except if you have already connected other SAP HANA instances in Database Explorer with the same identity and in the same SAP BTP region (eu10).

2. Create a connection to the SAP HANA Cloud database instance. The database instance details are provided in your SAP Learning System Access setup guide. You will use single sign-on (SSO) authentication.

   1. In SAP HANA Database Explorer, choose +(Add an instance to the Database Explorer).

   2. In the Instance Type dropdown list, choose SAP HANA Database.

   3. Enter the Host and Port in the respective fields.

      You can find this information in your SAP Learning System Access setup guide (section "Logon Information to Access SAP Training Systems" >> "SAP HANA Cloud Database Instance" ).

      Caution

      Make sure you select the radio button in front of the Port Number field.

   4. For the Authentication Mode, choose Single sign-On.

   5. Select the Connect to the database securely using TLS/SSL check-box.

   6. If it is selected, DEselect the Verify the server's certificate using the trusted certificate below check-box.

   7. In the Display name, enter HC300_DB (A####) (SSO enabled) where A#### is your assigned database user name.

   8. Choose OK.

   Result

   The connection to the SAP HANA Cloud database instance is created.

3. Confirm your assigned SAP HANA Cloud database user A####.

   This can be done by executing the SQL statement:SELECT SESSION_USER FROM DUMMY;

   1. Select the new connection HC300_DB (A####) (SS0 enabled).

   2. Top-left of the instances list, choose Open SQL Console.

   3. Enter the above SQL statement.

   4. Choose (Run) or press F8.

   5. Check that your assigned user number A#### is consistent with the one you got from the tile of your SAP Learning System Access instance (on the SAP Learning System Access portal).

1. Create a new folder HC300 to store all the course files on your computer. Choose a location that you can easily access and/or create a shortcut to this folder.

   Later on, any course file you use can be found in this folder, always referred to as HC300 or the HC300 folder.

2. Download the course files from GitHub.

   The repository URL is: https://github.com/SAP-samples/hana-cloud-modeling-learning-journey. It contains both an existing modeling project (in a .zip archive) and several files organized into different folders that you will need for some activities. The target folder for the download is the new HC300 folder you created in the previous step.

   1. In your web browser, go to the GitHub repository https://github.com/SAP-samples/hana-cloud-modeling-learning-journey

   2. In the branch selector, make sure that the branch main is currently selected .

   3. Select the HC300_2410_Course_Files.zip item.

   4. Click the top-right ... ellipsis button and choose Download.

   5. Save the downloaded .zip file in your HC300 folder.

      If you are not prompted for a target folder, it will generally default to a folder such as Downloads, easily accessible from your file explorer or from the browser itself. Then, you just need to move the downloaded archive to your HC300 folder.

      Caution

      This archive is NOT a ready-to-import project for SAP Business Application Studio, but it contains one, as well as a number of files such as SQL code, templates, and so on. So you must first unzip/extract the archive to see its content and work with it.

3. On your computer, in the HC300 folder, unzip the HC300_2410_Course_Files.zip archive.

   Note

   There is NO need for an extra subfolder such as HC300_2410_Course_Files. So extracting the archive directly into your HC300 folder is the simplest approach.

   1. Right-click the .zip file and choose Extract here.

   2. If the extracted files are not located as expected, move them together to the right location.

   3. After the HC300_2410_Course_Files.zip archive as been extracted, you can delete this archive.

   Note

   Depending on your operating system and tools, in particular your zip/unzip tool, the steps to extract the archive can vary. In the end, the objective is to get a folder structure such as C:\HANA\HC300\ with all the content extracted from the .zip archive.

   

1. Import the project you will use for most of your activities into SAP Business Application Studio.

   The project is provided as an HC300.zip file. It will be imported into the /home/user/projects folder.

   1. Go back to Business Application Studio.

   2. Select the Explorer icon in the Activity bar (left side bar).

   3. Choose Open Folder.

      Note

      If you have already worked with the Explorer view in this development space, click the (Business Application Studio Menu) icon top left and choose File→Open Folder instead.

   4. Select the /home/user/projects folder.

   5. Choose OK.

      If a message asking if you want to leave the page appears, choose Leave.

   6. In the opened Get Started tab, choose Import.

      Hint

      If you do not see this Get Started tab, you can open it by choosing the top left menu icon, then choose Help→Get Started.

   7. In your course files, find the previously extracted HC300.zip file and choose Open.

   8. When the project import is completed, you should see a message on the bottom right corner telling you that the project has been imported.

      Note

      The project is imported automatically in a new workspace.

2. Rename the project folder, adding _ and your student number A####.

   Hint

   To rename a folder, it must be displayed in the Explorer view, but NOT as a primary folder in a workspace. So you need to open the parent folder projects first.

   1. Choose File→Open Folder.

   2. Select the folder projects and choose OK.

   3. In the Explorer view, right-click the HC300 folder and choose Rename.

      Alternatively, you can press F2.

   4. Adjust the folder name to HC300_A#### and press Enter.

3. Open the renamed folder in the Explorer view.

   This is important to make sure you won't touch any file located in the other child folders of projects.

   1. Click the (Menu) icon and choose File→Open Folder.

   2. Select the projects folder.

   3. Select the HC300_A#### folder and choose OK.

      Result

      The HC300_A#### folder is now exposed as the root folder in the file explorer. It contains a number of files and sub-folders corresponding to an SAP HANA Cloud modeling project.

   4. If needed, close the Get Started and Welcome tabs.

   Alternatively, you could also open the project in a workspace. This can be done from the Get Started window, by clicking directly the project folder in the Projects list. To keep it simple, a workspace is a list of folders that can be shown together in the SAP Business Application StudioExplorer pane, and share a number of settings.

4. Check that the course resources are present.

   The project is made up of one SAP HANA Database (HDB) module, stored in the db folder, where you will store all modeling objects you create. It already contains the design-time source files of a number of tables, calculation views and other objects that you will use during the activities.

   Within the db/src/ folder, the design-time objects will be organized in different sub-folders, as follows:

   • data

     Contains the definition of tables (.hdbtable files), the data (.csv files) and the corresponding mapping definition (.hdbtabledata files).

   • external_data

     Contains references to external objects and data that are not stored in your project itself.

   • models

     Contains ready-made calculation views that you will use as sources (resources folder), and will be used to store all the modeling artifacts that you will create during the training (exercises folder) and .

     This folder also contains a solutions folder, which includes the final (and also, when relevant, intermediate) stage of modeling exercises. You can consult these solution objects, for example, to compare them with the ones you are creating in the exercises folder.

     Note

     To distinguish solution objects from the ones you will develop, all the solution objects have _00 in their name. For example, CVC_SO_00 is the solution object that corresponds to the CVC_SO calculation view that you will build during the course.

In order to deploy the project into SAP HANA, you need to first connect to Cloud Foundry, and then bind the HDB module of your project to an HDI container. You will create a new HDI container.

1. Log on to Cloud Foundry.

   You will authenticate using Single-Sign-On (SSO).

   1. Click the Menu icon and choose View→Command Palette

      Alternatively, you can use the top search bar and type > before your entry.

   2. Enter CF in the command line. A list of Cloud Foundry commands should display.

   3. Choose Login to Cloud Foundry.

   4. Select the authentication method SSO Passcode and click the link Open a new browser page... just below.

   5. If you get a warning message related to opening an external website, choose Open.

   6. If needed, confirm your SAP BTP Sign-in identity. It must be consistent with the identity you used to access the SAP Learning System Access portal, and must use the identity provider cis4hcpractice-platform.

      Consult your SAP Learning System Access - System Setup Guide for more details, if needed.

   7. On the new browser tab, to copy the temporary password, choose the copy icon. Then close the tab.

   8. Back to SAP Business Application Studio, paste the temporary password and choose Sign in.

      Result

      You have been signed in to Cloud Foundry is displayed in a notification box bottom-right.

2. Choose the relevant Cloud Foundry target to deploy your modeling content in.

   This target is defined by the two following elements, in the same tab, Login to Cloud Foundry:

   Cloud Foundry Organization	SAP Practice Systems - HANA Cloud_hanacloud-sharedlearning-a000001-cf-eu10-sd
   Cloud Foundry Space	DEV (default space in your Cloud Foundry Organization)

   

   1. Select the Cloud Foundry organization as specified

   2. Select the DEV Cloud Foundry space

   3. Choose Apply.

   Caution

   At some point during your modeling activities, especially overnight, your connection to Cloud Foundry might time out. In that case, when resuming your exercises, log back in to Cloud Foundry and choose the target Organization and Space, as specified in the two last steps.

3. Bind the HANA database module of your project to a new HDI Container with the following name : HC300_A####-hdidb-ws-xxxxx (where A#### is your assigned student number).

   Use the default service instance name proposed by the SAP Business Application Studio wizard.

   1. In the Explorer view, expand the SAP HANA Projects sub-pane. It is located by default at the very bottom of the Explorer view.

   2. Under HC300_A####/db→Database Connections, select hdi_db.

      If needed, to refresh the list of HDB modules, click the Synchronize icon next to the HC300_A####/db entry

   3. Click on the green Bind icon on the right of hdi_db.

   4. In the Select a binding option drop down, choose Bind to an HDI Container.

   5. In the Select an SAP HANA Service drop down, Choose Bind to the default service instance: HC300_A####-hdidb-ws-xxxxx.

      Result

      A message displays at the bottom right of the screen saying that instance creation has started.

   6. Wait until the creation and binding of of the HDI container instance are complete.

      Result

      A message saying the HC300_A####-hdidb-ws-xxxxx service has been bound should be displayed in the bottom right of the screen. In the Explorer view, in the SAP HANA Projects sub-pane, the HC300_A####/db→Database Connections→hdi_db (…) icon is showing a green plug and the name of the bound service in parentheses.

   7. In the automatic undeployment related idalog box, answer Enable and do not ask again.

4. Bind the external service cross_container_service_2 with the EXT_SCHEMA_SERVICE service.

   1. In the Explorer view, expand the SAP HANA Projects sub-pane

   2. Expand the Database Connections folder.

      Result

      You should see two entries. One for the hdi_db connection and one for cross_container_service_2.

   3. Click the green Bind icon on the right of the entry cross-container-service-2.

   4. In the drop-drown list, select Bind to a user provided service.

   5. Select the EXT_SCHEMA_SERVICE service.

      Notice that, because the service name mentioned in the mta.yaml file is in sync with the actual name of an existing user-provided service in the DEV space, another option offers to execute the binding to the EXT_SCHEMA_SERVICE in a single click. You can choose either option.

      Result

      The cross container service is now bound. (EXT_SCHEMA_SERVICE) should now appear after the (logical) name of the service cross-container-service-2.

5. Deploy the project.

   1. In the Explorer view, expand the SAP HANA Projects sub-pane

   2. On the right of the HC300_A####/db item, which is the root of your HDB module, click on the (Deploy) icon.

      Result

      The Terminal displays the deployment trace.

   3. Check that the deployment was successful.

      Result

      The HDB module of your project is now deployed inside the HDI container, meaning that all the database artifacts have been created. The mention Pending Deployment that was showing up next to each database object is gone.

6. Close the Terminal window.

   1. Click the X(Close Panel) icon at the right of the terminal header.

To simplify the use of SAP Business Application Studio and improve your productivity, you will arrange menus, hide unused options and icons, and set up some basic options.

1. Make the menu bar always visible, on top.

   1. Click the (Business Application Studio Menu) top left.

   2. Select View→Appearance→Menu Bar

   3. The menu bar should now be displayed on top.

      Note

      If the menu items do not show up immediately, try to refresh the current browser tab (SAP Business Application Studio).

2. Hide a number of activity bar icons that you will not need for this course. Only keep the following icons : Explorer, Search , Source Control, HANA SQL Analyzer, Guide Center and SAP HANA Database Explorer.

   1. To remove the Run and Debug icon, right-click on it on the Activity bar on the left, and choose the first entry in the context menu: Hide 'Run and Debug'.

      Hint

      Alternatively, you can right-click anywhere on the Activity bar on the left and deselect Run and Debug from the views list.

   2. Repeat this sub-step for all other views to hide.

3. In the Explorer view, hide the OUTLINE and TIMELINE sub-panes.

   1. Make sure the Explorer view is opened. If not, select the Explorer icon in the Activity bar.

   2. Right-click on the OUTLINE sub-pane and choose Hide 'Outline'.

   3. Right-click on the TIMELINE sub-pane and choose Hide 'Timeline'.

4. Enable word wrapping in the text editor and check automatic save is enabled with a delay of 1000 milliseconds.

   Caution

   With the Auto Save setting, you never lose work you have done on a file. However, make sure NOT to do any action such as testing the behavior of an editor on a "precious" object. Use dummy objects/copies instead.

   1. Select the Manage icon (cogwheel) on the bottom left and choose Settings.

   2. In the Search settings field, enter word wrap.

   3. Set the Editor : Word Wrap option to on.

   4. In the Search settings field, enter auto save.

   5. Check that Files : Auto Save is set on afterDelay and that Files: Auto Save Delay is set to 1000 milliseconds.

   6. Close the Settings tab.

You will now connect your SAP HANA Database Explorer to the HDI Container created as part of the binding and deployment of your HC300_A#### project. This must be done first in the external Database Explorer so that the connection can be viewed from within SAP Business Application Studio. You will also give a friendly name to this connection.

1. Add the HDI container of your deployed modeling project HC300_A#### to the SAP HANA Database Explorer.

   The HDI container can be added automatically by launching the SAP HANA Database Explorer from SAP Business Application Studio (SAP HANA Projects sub-pane).

   1. In the Explorer view of SAP Business Application Studio, expand the SAP HANA Projects sub-pane.

   2. On the right of the HC300_A####/db element, choose the Open HDI Container icon.

   3. If a dialog box about opening an external website shows up, choose Open.

      Note

      You will not do this very often as part of this learning journey, so just choosing Open is fine. Otherwise, you can add hana-cockpit (wherein SAP HANA Database Explorer is exposed) as a trusted domain.

2. Modify the display name of your HDI Container connection to make it simpler.

   Use the following pattern: HC300_A####_HDI_CONTAINER, where A#### is your assigned user number

   1. In the database list, locate your HDI Container connection, which has the pattern SharedDevKey@HC300_A####-hdidb-ws-xxxxx. It should be the active/open one.

      Note that is starts with your project name, and includes the development space id (ws-xxxxx).

   2. Right-click the connection name and choose Properties.

   3. Replace the Display Name with HC300_A####_HDI_CONTAINER (A#### is your assigned user number) and choose OK.

3. Check that your SAP HANA Cloud instance database connection and the renamed HDI container connection are now visible in SAP Business Application Studio.

   1. Back to SAP Business Application Studio, in the Activity bar, choose Database Explorer.

   2. If needed, in the Database List sub-pane, choose Refresh Database List.

   3. Check that your two HC300... connections in the database list are present with the expected names:

      • HC300_DB (A####) (SSO enabled): this is the connection to the entire database
      • HC300_A####_HDI_CONTAINER: this is the restricted connection to a single HDI container
   Note

   In the Database Explorer, all databases and HDI container instances you have access to might be listed if they have already been added to Database Explorer (entries are not filtered by SAP BTP account or subaccount). You can filter the entries if needed.

You will now familiarize yourself with the user interface, first in the Explorer view, then in the Search and SAP HANA Database Explorer views.

1. Navigate through the HC300_A#### project to look at data and models folders. Open the two files Employees.hdbtable and CVD_ADRESSES.hdbcalculationview.

   1. Expand your HC300_A#### project in the Explorer view down to HC300_A####/db/src/data/TRAINING.

   2. Choose the file Employees.hdbtable to open it in the text editor.

   3. Expand your HC300_A#### project in the Explorer view down to HC300_A####/db/src/models/resources.

   4. Choose the file CVD_ADRESSES.hdbcalculationview to open it in the corresponding visual editor.

   5. Close the calculation view tab.

2. Search for all files containing the string Employees. Then narrow down the result set to show only files containing Employees with matching case.

   1. In the Activity bar, click the Search icon.

   2. In the search field, enter Employees. You should see all files containing employees, regardless of the case.

   3. Click the Match Case icon at the right of the search field.

      Result

      All the previous matches containing EMPLOYEES (in upper case) are filtered out.

3. In the SAP HANA Database Explorer view, open the connection to your HDI container and display the data of the HC::EMPLOYEES table and the HC::CVD_ADDRESSES Column View.

   1. In the SAP HANA Database Explorer view, expand the connection to your HDI container HC300_A####_HDI_CONTAINER.

      Hint

      In general, if there are several or a lot of items in the database list, for example because you have some from other projects or trainings, you can filter the list. Use the Select Databases (funnel) icon, enter HC300 and check the databases and HDI connections you want to display.

   2. Select Tables.

      Result

      A list of all the tables should appear in the Catalog Browser sub-pane.

   3. Right-click the HC::Employees table and choose Open Data.

   4. The query should be run automatically and show data in a result tab. If not, in the open SQL tab, choose the top-left Run icon.

   5. Close the SQL tab. In the dialog box, choose Don't Save.

   6. In the Database List sub-pane, select Column Views.

   7. Right-click the HC::CVD_ADRESSES column view and choose Open Data.

      Hint

      Similarly to the Database List, entries in the Catalog Browser sub-pane can be filtered, using the Apply Filter (funnel) icon.

   8. Close the SQL tab. In the dialog box, choose Don't Save.

Most of the modeling exercises in this learning journey will rely on the connection to the HDI Container, for example when previewing the data. However, a few exercises explore how a standard database user can query the data of an HDI Container.

To make this possible, your user A#### must have the privilege to query data from the container. A generic role for enabling data access to your container’s content has been created when you deployed the HC300_A#### project for the first time.

The SQL statements to grant this role can be found in the file SQL for External HDI Container Access.txt. These statements must be executed in your SAP HANA Cloud database, Catalog View (NOT the HDI container), connected with user A####.

1. In the file explorer of your computer, open the script from your course files and copy its content to an SQL console connected to the HC300_DB (A####) (SSO enabled) connection.

   Code Snippet

   Copy codeSwitch to dark mode

   1234
   
   SELECT * FROM ROLES WHERE ROLE_NAME LIKE '%HC300_A####%access_role';
   
   CALL TRAIN_ADMIN.P_HC300_MANAGE_ROLE('GRANT','','HC300_A####_HDI_DB_1::access_role', ?);

   1. In your course files, open the folder SQL for External HDI Container Access.txt.

   2. Select the entire content of the file (press Ctrl+A) and to copy the script, press Ctrl+C.

   3. Hover your mouse over the HC300_DB (A####) (SSO enabled) connection and, on the right, choose Open SAP HANA SQL Console.

   4. To paste the script, choose Ctrl+V.

2. Adapt the script so that it uses your assigned user number A####.

   1. To replace the string, press Ctrl+H.

   2. In the fields Search for and Replace with, enter respectively A#### and your assigned user number.

   3. Next to the button Replace, choose All.

3. Execute the SQL statements to grant your course participant role some privileges on your container.

   Note

   You have two options to execute a single SQL statement in the SQL console:

   • Select the statement an choose Run (F8).
   • Place the cursor anywhere within the statement and choose Run Statement (F9), which is one of the possible options in the drop-down list next to the Run button.

   1. Check the actual name of your HC300_A#### container access role (SQL statement in section #4.3.a).

      Code Snippet

      Copy codeSwitch to dark mode

      1
      SELECT * FROM ROLES WHERE ROLE_NAME LIKE '%HC300_A####%access_role';

   2. Check that the role name in the following SQL statement is consistent with the result of the previous query.

   3. Execute the SQL statements in section #4.3.b.

      Code Snippet

      Copy codeSwitch to dark mode

      12
      CALL TRAIN_ADMIN.P_HC300_MANAGE_ROLE('GRANT','','HC300_A####_HDI_DB_1::access_role', ?);
      

4. Check that the statement was executed successfully. The result should show a message saying Operation successful! together with the executed statement.