Performing Practical Exercises

Review the calculation view of type dimension, which exposes master data attributes of employees and uses a parent-child hierarchy to organize the employee data. You will access this hierarchy using SQL.

1. If needed, launch SAP Business Application Studio and start your HCMOD development space.

2. In your resources folder, open the Calculation View CVD_EMPLOYEE_HIER and examine the definition. Pay attention to the hierarchy that is defined in the calculation view.

   1. If not already done, switch to the Explorer view of your HC300 project.

   2. In your resources sub-folder, select the calculation view CVD_EMPLOYEE_HIER.

   3. Review the data source HC::EMPLOYEE_HIERARCHY by double-clicking the Projection node, so that you see on which columns the hierarchy is built.

   4. Review the hierarchy by selecting the semantics node and then selecting the Hierarchies tab and then select the hierarchy H_EMP and expand the nodes section. In particular, note how the hierarchy is a parent-child type (observe the hierarchy icon) and is based on the CHILD_DNUMBER and PARENT_DNUMBER attributes.

Review the calculation view CVCS_EMPLOYEE_DATA. This calculation view joins employee data with the hierarchy dimension, but most importantly, also enables SQL access to the hierarchy.

1. In your resources folder, open the Calculation View CVCS_EMPLOYEE_DATA and examine the definition. Pay particular attention to the settings that enable SQL access to the shared hierarchy.

   1. In your resources folder, select the Calculation View CVCS_EMPLOYEE_DATA.

   2. Review the star join node. Notice how the employee data and dimension are combined by selecting the Join Definition tab.

   3. Double-click the Semantics node and choose the tab View Properties. In the General sub-tab, you will see that Enable Hierarchies for SQL Access is already selected.

   4. Review the settings for the shared hierarchy by selecting the Hierarchies tab. Then, under the Shared Hierarchies sub–tab, select the hierarchy H_EMP and expand the section SQL ACCESS. Notice the name H_EMPNode that is generated from the hierarchy name + the fixed word ‘node’. This is the name you need to refer to when you want to reference the hierarchy nodes in an SQL expression using SELECT, WHERE, GROUP BY, and so on. You can change the name here if you prefer to something more meaningful.

2. Preview the CVCS_EMPLOYEE_DATA calculation view with a hierarchical display.

   1. In the Explorer view, right-click the calculation view CVCS_EMPLOYEE_DATA and choose Data Preview.

   2. Display the Hierarchy tab and drag the H_EMP to the Selected Hierarchy pane and the REMAINDERDAYS measure to the Selected Measures pane.

   3. Explore the hierarchy. Observe that the total number of remainder days for a given parent node is composed of the parent's own value (not displayed specifically in this view) plus the total of all its children.

You will now discover how to write the SQL that is used to query the calculation view, and in particular filter the results using the hierarchy.

1. Open a SQL Console for your HDI container.

   1. Switch to the SAP HANA Database Explorer view.

   2. Highlight your database container and then choose Open SAP HANA SQL Console in the toolbar (last icon on the right).

2. On your device, from your HC300 exercise files, open the file Prepared Code→SQL for reading hierarchy structure.txt, and copy and paste its entire contents to the SQL console.

   Code Snippet

   Copy codeSwitch to dark mode

   12345678910111213141516171819202122232425262728293031
   
   -- First Part : Select data for all employees, independently of hierarchy
   
   SELECT 
   "DNUMBER",
   "REMAINDERDAYS" 
   FROM "HC::CVCS_EMPLOYEE_DATA";
   
   -- Second Part : Filter data on a parent node
   
   SELECT 
   "DNUMBER",
   "REMAINDERDAYS" 
   FROM "HC::CVCS_EMPLOYEE_DATA"
   WHERE "PARENT_DNUMBER" = 'D100001';
   
   -- Third Part : Filter data on a hierarchy node
   
   SELECT 
   "DNUMBER",
   "REMAINDERDAYS" 
   FROM "HC::CVCS_EMPLOYEE_DATA"
   WHERE "H_EMPnode" = 'D100001';
   
   --Fourth Part : Select the hierarchy information
   
   SELECT 
   "H_EMPnode",
   SUM("REMAINDERDAYS") 
   FROM "HC::CVCS_EMPLOYEE_DATA"
   GROUP BY "H_EMPnode";

   1. Double-click the file HC300→Prepared Code→SQL for reading hierarchy structure.txt.

   2. Select all the content of the file (press Ctrl+A) and copy it to the clipboard (press Ctrl+C).

   3. To paste the content in the SQL Console, press Ctrl+V or right-click anywhere inside the SQL Console and choose Paste.

3. Review the SQL code to study how the hierarchy is read.

   1. The key element in the SQL code is the use of the column name H_EMPnode, which is the generated column that represents the nodes at all levels of the hierarchy.

4. Execute the SQL statements and review the results.

   1. To execute the script, press F8 or choose Run on the toolbar.

      Hint

      To execute a single statement, you can either select it and press F8, or put your cursor inside and press F9(Run Statement).

5. Close all open tabs in Business Application Studio and come back to the Explorer view.

   1. Right-click any open tab and choose Close All.

   2. Choose the Explorer icon.

You want to find the name of an employee, even when you type the surname of the employee incorrectly. You decide to achieve this by defining a table function.

1. If needed, launch SAP Business Application Studio and start your HCMOD development space.

2. In the Explorer view of SAP Business Application Studio, create a new function TF_FUZZY_EMPLOYEES under the node exercises.

   1. Choose View→Command Palette

   2. Enter Artifact and choose the SAP HANA: Create SAP HANA Database Artifact option.

   3. Choose the Browse button and choose the /home/user/projects/HC300_A####/db/src/models/exercises/ folder and choose OK.

   4. For the artifact type, choose Function (hdbfunction).

   5. Enter TF_FUZZY_EMPLOYEES as function name and choose Create.

3. Copy and paste the function code from the prepared text file that you can find it in your HC300 folder. The file you need is Prepared Code→SQL for Table Function.txt.

   Code Snippet

   Copy codeSwitch to dark mode

   12345678910111213141516171819202122232425
   FUNCTION "HC::TF_FUZZY_EMPLOYEES" (LastNameFilter NVARCHAR(40))
   	RETURNS table
           ( 
          "FIRST_NAME" NVARCHAR(40),
          "LAST_NAME" NVARCHAR(40),
          "SEX" NVARCHAR(1),
          "LANGUAGE" NVARCHAR(1),
          "EMAIL_ADDRESS" NVARCHAR(255)
           )
   	LANGUAGE SQLSCRIPT
   	SQL SECURITY INVOKER AS
   BEGIN
   
   RETURN
   
   select
   "FIRST_NAME",
   "LAST_NAME",
   "SEX",
   "LANGUAGE",
   "EMAIL_ADDRESS"
    from "HC::SNWD_EMPLOYEES"
    where contains("LAST_NAME", :LastNameFilter, FUZZY(0.5));
   
   END

   Note

   This code has CONTAINS and FUZZY statements. This feature enables you to find the names of employees even if they are misspelled. You can learn more about these features in the course SAP HANA Advanced Modeling.

   1. First, completely remove the code that was generated in the function, so the file is completely empty.

   2. Open the prepared text file which contains the SQL and copy and paste the entire contents to the new function.

4. Deploy your table function.

   1. Choose the Deploy icon for the TF_FUZZY_EMPLOYEES function.

   2. In the Console pane, check that the deployment completes successfully.

1. Create a calculation view of type DIMENSION in your exercises folder with the name and label CVD_FIND_EMPLOYEES.

   1. Right-click your exercises folder and choose New File.

   2. Enter CVD_FIND_EMPLOYEES.hdbcalculationview as file name and press Enter.

   3. Enter the calculation view label and change the Data Category to DIMENSION.

   4. Choose Create.

2. Add a new Table Function node and assign the table function TF_FUZZY_EMPLOYEES to it.

   1. From the toolbar, select the icon Create Table Function and then click in the empty space at the bottom of the flow, directly underneath the Projection node.

   2. In the Table Function node, choose Add Table Function (looks like a grid with a +). Be careful not to choose the standard 'add' icon that also uses a + symbol)

   3. In the Search field, enter Fuzzy and you will see only one entry appears.

   4. Select the table function HC::TF_FUZZY_EMPLOYEES and choose Finish.

      Be careful not to choose the _00 version of the function, because this is the solution version and not the one you created.

3. Map the input parameter LASTNAMEFILTER of the table function to a constant value SMIHT, which represents a misspelled employee name.

   1. Double-click the Table Function node and choose the Input Mapping tab.

   2. In the toolbar, choose Create Constant (the last icon on the right)

   3. In the Value field, enter SMIHT and choose OK.

   4. Drag a line from the constant value on the left side to the LASTNAMEFILTER input parameter of the table function which is found on the right side so they are now mapped.

4. Connect the Table Function node to the Projection node.

   1. Drag a line from the arrow connector icon in the Table Function node to the bottom of the Projection node.

5. Map all incoming columns to the output columns of the Projection node.

   1. Select the Projection node and make sure that the Mapping tab is selected.

   2. Under Data Sources, right-click the header TableFunction_1 and choose Add To Output, so that all columns appear under Output Columns.

6. Deploy your calculation view.

   1. Choose the Deploy icon for the CVD_FIND_EMPLOYEES calculation view.

   2. In the Console pane, check that the deployment completes successfully.

7. Test the table function by using the Data Preview function of the calculation view.

   1. Right-click your calculation view in the folder structure under exercises and select the Data Preview option. You should see results which include records where the last name closely matches the constant value.

   Note

   An intermediate solution object is available at this stage:

   solutions→CVD_FIND_EMPLOYEES_00_STAGE1.hdbcalculationview.

1. From the calculation view CVD_FIND_EMPLOYEES remove the mapping between the constant and the function input parameter.

   1. Select the Table Function node and select the Input Mapping tab.

   2. Select the line that maps the constant to the function parameter and right-click to select Remove Mapping

   3. Highlight the constant value and choose Remove in the toolbar.

      You might find that the constant does not disappear. If this is the case, simply save, then close and re-open the calculation view to refresh the screen. The mapping should then disappear.

2. Create a new input parameter with the label and name Enter_Name, a data type NVARCHAR, and length 40.

   1. With the Table Function node still selected click the Parameters tab.

   2. Choose +, and from the drop-down list select Input parameter..

   3. Select the new input parameter and enter the name and label Enter_Name.

   4. Expand the section Parameter Type — Direct and from the drop-down list Data Type, choose NVARCHAR..

   5. In the Length field, enter 40.

   6. Use the < Back button to return to the main screen.

3. Map the new input parameter Enter_Name to the input parameter LASTNAMEFILTER of the table function.

   1. Still in the Table Function node, select the Input Mapping tab.

   2. If the parameter is still named IP_1, close and open the calculation view again.

   3. Drag a line from the input parameter Enter_Name on the left side to the LASTNAMEFILTER on the right side in order to map them.

4. Deploy your calculation view.

   1. Choose the Deploy icon for the CVD_FIND_EMPLOYEES calculation view.

   2. In the Console pane, check that the deployment completes successfully.

5. Test the table function by using the Data Preview function of the calculation view. At the prompt, enter the deliberately misspelled name SMIHT.

   1. Right-click your calculation view and select the Data Preview option. After a few moments you will see a prompt for the input parameter Enter_Name, where you will enter the misspelled last name.

   2. In the From column, type SMIHT.

      Note

      Do not try to display the Value Help dialog box. You would get a warning, because the input parameter is not designed to generate a list of possible values.

   3. Choose Open Content to execute the data preview using your input value.

      Result

      You can see that SAP HANA still managed to find two employees with the surname Smith, despite you typing the surname incorrectly.

6. Close all the open tabs in Business Application Studio.

   1. Right-click any open tab and choose Close All.

1. If needed, launch SAP Business Application Studio and start your HCMOD development space.

2. In the EXPLORER view, create a new scalar function SF_GET_BEST_CUSTOMER in the folder exercises.

   1. Navigate to HC300_A####→db→src→exercises..

   2. Choose View→Command Palette

   3. Enter Artifact and choose the SAP HANA: Create SAP HANA Database Artifact option.

   4. Choose the Browse button and choose the /home/user/projects/HC300_A####/db/src/models/exercises/ folder and choose OK.

   5. For the artifact type, choose Function (hdbfunction).

   6. Enter SF_GET_BEST_CUSTOMER as function name and choose Create.

3. Create the SQLScript of your scalar function using the following code:

   Code Snippet

   Copy codeSwitch to dark mode

   12345678910
   FUNCTION "HC::SF_GET_BEST_CUSTOMER"( )
          RETURNS best_cust nvarchar(10) 
          LANGUAGE SQLSCRIPT 
          SQL SECURITY INVOKER AS 
   BEGIN 
      
   SELECT customer_id into best_cust from "HC::SALES_DATA"
   where quantity = (select max(quantity) from "HC::SALES_DATA");
      
   END;

   Note

   The SQLScript code can be copied from a text file in your HC300→Prepared Code folder: SQL for Scalar Function.txt.

   1. From Windows Explorer, in your HC300→Prepared Code folder, double-click the file SQL for Scalar Function.txt.

   2. Select all the code (Ctrl+A) and copy it to the clipboard (Ctrl+C).

   3. In the Function source file, remove all existing content and then paste the entire code that you just copied (Ctrl+V).

4. Deploy the scalar function and check the log to ensure that it was successful.

   1. Choose the Deploy icon for the SF_GET_BEST_CUSTOMER function.

   2. In the Console pane, check that the deployment completes successfully.

You will now use the function to derive the value of an input parameter and use the parameter value to filter your data.

1. Create a calculation view type CUBE in your exercises folder with the name and label CVC_SALES_IP_USING_FUNC.

   1. Right-click the exercises folder and choose New File.

   2. Enter CVC_SALES_IP_USING_FUNC.hdbcalculationview as file name and press Enter.

   3. Update the label.

   4. Choose Create.

      Result

      The calculation view graphical editor opens on the right of your screen.

2. Assign the table SALES_DATA as a data source to the aggregation node.

   1. To the right of the Aggregation node, choose Add Data Source.

   2. In the Search field, enter sales_data.

   3. Select the table SALES_DATA and choose Finish.

3. Add the columns CUSTOMER_ID — QUANTITY — QTY_UNIT — SQL_DATE — PRODUCT_ID — AMOUNT — CURRENCY to the output of the aggregation node.

   1. Double-click on the aggregation node to open the settings area and ensure that the Mapping tab is selected.

   2. Drag the columns CUSTOMER_ID — QUANTITY — QTY_UNIT — SQL_DATE — PRODUCT_ID — AMOUNT — CURRENCY from the data source to the output columns.

4. Create an input parameter IP_1 with label Highest Quantity Customer that filters the CUSTOMER_ID to the value returned by the scalar function.

   1. Select the Parameters tab.

   2. Choose + and select Input parameter.

   3. Select the new parameter IP_1 so that the properties appear.

   4. In the General section, locate the Label field and enter Highest Quantity Customer.

   5. Select, as value for Parameter Type, Derived From Procedure/Scalar Function.

   6. Expand the Parameter Type — Derived From Procedure/Scalar Function section and choose HC::SF_GET_BEST_CUSTOMER for the Procedure/Scalar Function. Do not select Input Enabled..

   7. Choose the Back button to return to the top level screen of the Parameters tab.

5. Define a filter expression that uses the scalar function returned value to select only the best customer.

   1. Select the Filter Expression tab of the Aggregation node.

   2. Expand Components→Elements→Columns and select CUSTOMER_ID so it is added to the expression.

   3. Add = after the column CUSTOMER_ID so the expression looks like this : "CUSTOMER_ID" =.

   4. Expand Components→Elements→Input Parameters and select IP_1.

      The complete expression now looks like this: "CUSTOMER_ID" = '$$IP_1$$'

   5. Choose Validate Syntax to ensure that you have no errors in the expression.

6. Define the sorting sequence of the column QUANTITY as descending so that the highest sales quantity is shown at the top of the display.

   1. Select the semantics node and then select the Columns tab.

   2. Select the toolbar iconSort Result Set (look for the opposing arrows icon)

   3. Select the + button to add a column.

   4. From the drop-down list, select the column QUANTITY.

   5. Choose Descending for the sort direction.

   6. Press OK.

7. Deploy your calculation view.

   1. Choose the Deploy icon for the CVC_SALES_IP_USING_FUNC calculation view.

   2. In the Console pane, check that the deployment completes successfully.

8. Test the calculation view correctly returns the sales orders of the customer who made the biggest quantity purchase.

   1. Right-click your calculation view and select the Data Preview option.

   2. Switch to the Raw Data tab and you should see only customer 3000 who made the highest purchase of 16 items.

9. Close all the open tabs in Business Application Studio.

   1. Right-click any open tab and choose Close All.