Performing Practical Exercises

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

2. In your exercises folder, create a new calculation view with the following properties:

   Field	Value
   Name	CVD_CONTROL_PARALLELIZATION
   Label	CVD_CONTROL_PARALLELIZATION
   Data Category	DIMENSION

   1. In the Explorer view, right-click the folder HC300_A####→db→src→exercises folder and choose New File.

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

   3. Enter the calculation view label and Data Category as specified in the table.

   4. Choose Create.

3. Add a new Projection node and rename the node label as Start_Parallel.

   1. Select and put a Projection node from the palette into the area at the very bottom of the data flow canvas.

   2. Right-click the node and choose Rename and enter the name Start_Parallel and press Enter.

4. In the new Projection node, add the following data source:

   Table HC::Empl_Salary_Current

   1. Select the Start_Parallel node that you just added.

   2. Choose +Add Data Source.

   3. In the Find Data Sources window, click the search field and enter empl.

   4. In the search results, select the table HC::Empl_Salary_Current.

   5. Choose Finish.

5. Expand the Details panel.

   1. Choose the Expand Details Panel icon in the top right corner of the screen.

6. On the Mapping tab of the new Start_Parallel node, add all columns to the output.

   1. Select the new Start_Parallel node and on the Mapping tab, right-click the data source HC::Empl_Salary_Current and choose Add to Output so that all three columns are added to the output pane.

7. Enable the Setting Partition Local Execution to start the parallelization and choose Year for the partition column.

   1. Expand PROPERTIES section of the mapping tab (the lower part of the mapping tab)

   2. If you don't see the setting Partition Local Execution, re-select the data source HC::Empl_Salary_Current so that the setting Partition Local Execution appears under PROPERTIES.

   3. Select the setting Partition Local Execution so a tick appears in the box.

   4. Using the Partition Column drop-down selector, choose the column Year.

8. Add a new Projection node above the one created earlier and rename the node label as Processing_In_Parallel.

   1. Select and put a Projection node from the palette into the canvas area between the two existing projection nodes.

   2. Right-click the new node and choose Rename and enter the name Processing_In_Parallel so it has a clear meaning.

9. Connect the projection node Start_Parallel to the projection node Processing_In_Parallel and map all columns to the output.

   1. Drag a line from Start_Parallel projection node to the Processing_In_Parallel projection node to connect them.

   2. On the Mapping tab of the Processing_In_Parallel projection node right-click the data source Start_Parallel and choose Add to Output so that all three columns are added to the output pane.

10. In the projection node Processing_In_Parallel, create a new calculated column using the information in the table below:

    Setting	Value
    Name	MonthlySalary
    Data Type	DECIMAL
    Length	10
    Scale	2
    Expression	"TotalSalary" / 12

    Hint

    Make sure to use double quotes in the SQL expression so that the case of the TotalSalary column name is passed properly to the expression as TotalSalary, not TOTALSALARY.

    1. Select the projection node Processing_In_Parallel then select the tab Calculated Columns and choose the Add button.

    2. Select the template calculated column CC_1 and enter the details from the table.

11. Add a new Union node above the projection node Processing_In_Parallel and rename the union node label as Stop_Parallel.

    1. Select and drop a Union node from the palette to the area directly above the Processing_In_Parallel projection node.

    2. Right-click the union node and choose Rename and enter the name Stop_Parallel so it has a clear meaning.

12. Connect the projection node Processing_In_Parallel to the union node Stop_Parallel and map all columns to the output.

    1. Drag a line from Processing_In_Parallel projection node to the Stop_Parallel union node to connect them.

    2. On the Mapping tab of the Stop_Parallel union node right-click the data source Processing_In_Parallel and choose Add to Output so that all four columns are added to the output pane.

13. Terminate the parallel processing in the union node.

    1. In the union node, re-select the data source Processing_In_Parallel so that the setting Partition Local Execution appears under PROPERTIES (clicking the header of the data source acts like a toggle between settings under properties).

    2. Select the setting Partition Local Execution so a tick appears in the box.

14. Connect the union node Stop_Parallel to the default projection node and map all columns to the output.

    1. Drag a line from Stop_Parallel union node to the default projection node to connect them.

    2. Select the default projection node and on the Mapping tab right-click the data source Stop_Parallel and choose Add to Output so that all columns are added to the output pane.

15. Use the auto layout feature to properly display the calculation view scenario.

    1. Choose the Auto Layout icon to rearrange the scenario symbols.

16. Deploy the CVD_CONTROL_PARALLELIZATION calculation view.

    1. Choose the Deploy icon for your CVD_CONTROL_PARALLELIZATION calculation view..

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

1. Open SAP HANA Database Explorer tool and generate the SELECT statement.

   1. Open the SAP HANA Database Explorer view.

   2. Select your HDI container, expand its content and select Columns Views.

   3. In the Catalog Browser sub-pane, set the filter to control.

   4. Right-click on the HC::CVD_CONTROL_PARALLELIZATION view and choose Generate SELECT statement.

2. Generate the SQL Analyzer Plan file. Use SQLA as prefix.

   1. Top-right of the SQL console tab, choose ...→Executed Plan.

   2. Enter SQLA as File Name Prefix.

   3. Choose Save.

   Result

   The HANA SQL Analyzer view opens, showing the query execution plan.

3. Check the parallel execution of the query.

   1. Select the PLAN GRAPH tab.

   2. Scroll down the execution plan to find the first operations.

   3. You should see two parallel table scans. One with a filter on year 2022 and another one on the year 2023.

4. Close all the open tabs in Business Application Studio and go back to the Explorer view.

   1. Right-click any open tab and choose Close All. Don't save anything.

   2. Select the Explorer view.

First, you want to see how the Employee Salary calculation view is designed and identify its data sources.

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

2. Open the Calculation View db→src→resources→CVC_EMPL_SALARY.

   1. In the Explorer view, navigate to the folder db→src→resources.

   2. Select the calculation view CVC_EMPL_SALARY.

   3. Choose Expand Details Panel.

3. Observe the design of the Union_1 node.

   1. In the Scenario pane, select the Union_1 node.

   2. Display the Mapping tab.

4. Preview the data of both data sources.

   Hint

   You can preview the data sources directly from the calculation view design, in the Scenario pane or in the Mapping tab.

   1. In the Mapping tab of the Union_1 node, select the data source HC::Empl_Salary_Current then right-click and choose Data Preview.

   2. Repeat the previous step for the other data source, HC::Empl_Salary_Historical.

      Note

      Make sure to first select the other data source, and then right-click. If you do not do that, you might query the same data source again.

You want to query the total wages paid in 2023 to each employee.

1. Execute the default generated SELECT statement.

   1. In the SAP HANA Database Explorer view, expand the database list.

   2. Expand your HDI container HC300_A####_HDI_CONTAINER and select Columns Views.

   3. In the Catalog Browser sub-pane, add a filter on salary.

   4. , right-click on the HC::CVC_EMPL_SALARY calculation view and choose Generate SELECT statement.

   5. Choose Run.

   6. Note that the data from both sources is combined together in the result set (salaries for the years 2020 to 2023).

2. Adapt the default generated SELECT statement to return only the data for 2023, and execute it again.

   Hint

   Because the SQL session is connected to your container schema, the schema name is not mandatory in the SQL statement and can be removed. You can also simplify the query by removing the TOP 1000 clause because the source tables only contain a few records.

   After your modifications, the code should look like the following example:

   Code Snippet

   Copy codeSwitch to dark mode

   12345
   SELECT "Year", "EmployeeID", SUM("TotalSalary")
   FROM "HC::CVC_EMPL_SALARY"
   WHERE "Year" = 2023
   GROUP BY "Year", "EmployeeID";
   

   1. Remove the TOP 1000 clause.

   2. Add the following WHERE clause to the SQL query, between the FROM and the GROUP BY lines.

      Code Snippet

      Copy codeSwitch to dark mode

      1
      WHERE "Year" = 2023

   3. Optionally, remove the container schema name "HC300_A####_HDI_DB_1"., including the . (dot) separator.

   4. To execute the query, choose Run.

   5. Check that the query result is now filtered, showing only the data for 2023.

3. Analyze the SQL query. Enter SQLA as prefix.

   1. Select the Analyze drop down list and choose Generate SQL Analyzer Plan File.

   2. Enter SQLA as File Name Prefix and choose Save.

      Result

      The SQL plan file opens in the HANA SQL Analyzer view.

   3. Look into the DATA USAGE block for the Accessed Tables number.

You want to optimize this calculation view so that, whenever possible, the non-relevant data sources of the union node are pruned. For example, when querying data for 2022 or 2023, the historical data source should not be scanned at all because by design, it does not contain any valid data for these years.

1. To make your screen cleaner, close all open tabs in Business Application Studio.

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

2. Copy the original Calculation View CVC_EMPL_SALARY from the resources folder to your exercises folder. Rename the copy CVC_EMPL_SALARY_PRUNING.

   Hint

   Check that the runtime name of the calculation view is also updated by opening the Semantics→View Properties→General window and checking the Name property.

   1. Come back to the Explorer view, select the CVC_EMPL_SALARY.hdbcalculationview from your resources folder and press Ctrl+C(Copy).

   2. Select the exercises folder and press Ctrl+V(Paste).

   3. Right-click on the copied calculation view and choose Rename.

   4. Enter _PRUNING after CVC_EMPL_SALARY and press Enter.

3. In the new (copied) calculation view, add a pruning configuration table with the name Empl_Salary_Pruning.

   Caution

   Do NOT define the namespace (the namespace, HC, will be added automatically to your table identifier upon deployment).

   Define the pruning conditions as follows:

   Pruning Configuration Entries

   Input	Column	Operators	Low Value
   HC::Empl_Salary_Current	Year	>=	2022
   HC::Empl_Salary_Historical	Year	<	2022

   Caution

   The pruning configuration entries define which value (or range of values) of a specified attribute CAN be found in each of the data sources.

   1. If needed, select the calculation view CVC_EMPL_SALARY_PRUNING.

   2. Choose Expand Details Panel.

   3. In the Semantics node, select the View Properties tab.

   4. In the Advanced tab, next to the Pruning Configuration Table field, choose +Create Table.

   5. In the Table Name field, enter Empl_Salary_Pruning.

   6. In the Pruning Configuration Entries table, choose +Add and define the condition for the Current data source, as per the table provided.

   7. Repeat the previous step for the Historical data source.

   8. Choose Create and Build.

      Result

      Three design-time files are created in your exercises folder with the name Empl_Salary_Pruning and different extensions.

4. Deploy the Calculation View.

   Check the build status.

   1. Choose the Deploy icon for your CVC_EMPL_SALARY_PRUNING calculation view..

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

5. From the default SELECT statement generated for the new calculation view, adapt the SQL query to return only the data for 2023, as you did in Task 2, and analyze the query.

   After your modifications, the code should look as follows (note that it now refers to the new calculation view):

   Code Snippet

   Copy codeSwitch to dark mode

   12345
   SELECT "Year", "EmployeeID", SUM("TotalSalary")
   FROM "HC::CVC_EMPL_SALARY_PRUNING"
   WHERE "Year" = 2023
   GROUP BY "Year", "EmployeeID";
   

   1. Go to SAP HANA DATABASE EXPLORER.

   2. Select your HDI container and select Columns Views.

   3. Right click on the HC::CVC_EMPL_SALARY_PRUNING view and choose Generate SELECT statement.

   4. Remove the TOP 1000 clause.

   5. Add the following WHERE clause to the SQL query, between the FROM and the GROUP BY lines:

      Code Snippet

      Copy codeSwitch to dark mode

      1
      WHERE "Year" = 2023

   6. Optionally, remove the container schema name "HC300_A####_HDI_DB_1"., including the . (dot) separator.

   7. Select Run to execute the Query. Only data for 2023 should display.

   8. Select the Analyze drop down list and choose Generate SQL Analyzer Plan File.

   9. Enter SQLA as File Name Prefix and choose Save.

   10. Look into the DATA USAGE block for the Accessed Tables number.

6. Close all the open tabs in Business Application Studio and go back to the Explorer view.

   1. Right-click any open tab and choose Close All. Don't save anything.

   2. Select the Explorer view.