Performing Practical Exercises

Create a calculation view that will process a top 5 extraction on a provided sales order item view.

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

2. Preview the data of the calculation view CVCS_SO that will be used as a data source for the rank node. It is located in your resources folder.

   1. In the explorer, expand your HC300_A#### project to display the folder db→src→resources.

   2. Select then right-click the calculation view CVCS_SO and choose Data Preview..

   3. Select the arrow button on the right of the SO_ID column. Then choose Sort ascending.

   This calculation view returns information on sales order details (each sales order has several line items; one for each product ID that is part of the order). The dimension Calculation views are used to retrieve, among other things, the Customer Name (BP_COMPANY_NAME) and its country (COUNTRY), as well as the Product ID (PRODUCT_ID) and product category (CATEGORY).

3. Create a new calculation view of the type Cube, with the following properties:

   Field	Value
   Name	CVC_SO_RANK
   Description	Sales Orders Rank
   Data Category	CUBE
   With Star Join	[Deselected]

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

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

   3. Enter the calculation view properties as per the table above.

   4. Choose Create.

      Result

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

4. Add a new Rank node Rank_1 to the calculation view scenario, and define it as the data source for the Aggregation node.

   1. In the Scenario pane, select the Rank node from the palette and drop it to the area below the Aggregation node.

      Result

      The new node is automatically named Rank_1.

   2. Select the Rank_1 node and drag the arrow icon to the Aggregation node.

5. Define the calculation view resources→CVCS_SO as the data source for the Rank_1 node, and add the following columns to the output:

   • COUNTRY

   • PRODUCT_ID

   • GROSS_AMOUNT

   1. Select the new Rank_1 node and choose + on the right of the node.

   2. In the Find Data Sources window, select the Search field and enter CVCS_SO.

   3. In the search results, select the view HC::CVCS_SO and choose Finish.

   4. In the Mapping tab, select the columns as per the table.

   5. Choose Add To Output.

6. Define the settings of the Rank node so that it extracts, for each country, the five products that total the biggest sales amount (column: GROSS_AMOUNT).

   A rank column, named RANK, should be generated.

   Property	Value
   Aggregation	Row
   Result Set Direction	Top
   Result Set Type	Absolute
   Target Value	Fixed Value: 5
   Offset	Fixed Value: [Leave Blank]
   Generate Rank Column	[Selected]
   Generated rank column name	RANK (do not keep the default column name)
   Partition column	COUNTRY
   Sort Column	GROSS_AMOUNT (direction: Descending)

   1. Select the Rank_1 node.

   2. In the Definition tab, define the rank properties using the values from the table.

      To add a column to the Logical Partition, choose +. Do the same to define the Sort Column.

7. In the Aggregation node, add all columns to the output.

   1. Select the Aggregation node.

   2. In the Mapping tab, select the Rank_1 item and choose Add To Output.

      Alternatively, you can drag the Rank_1 item from the Data Sources area to the Output Columns area.

8. Deploy the CVC_SO_RANK calculation view.

   Check the deployment status after activation.

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

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

9. Preview the data of the CVC_SO_RANK calculation view.

   1. Right-click the CVC_SO_RANK calculation view and choose Data Preview.

   2. Check the data displayed in the Raw Data tab.

   Caution

   Because the Rank_1 node is using a CUBE calculation view as a data source, that CUBE calculation view feeds the rank node with a data set on which it applies the defined aggregation. This aggregation is based on the list of attribute columns requested by the query executed on top of the rank node. In the current scenario, the CSCS_SO view has executed an aggregation by COUNTRY and PRODUCT_ID.

10. Filter the data preview to US as the COUNTRY and check that you receive the expected result, which is shown in the following table:

    COUNTRY	PRODUCT_ID	RANK	GROSS_AMOUNT
    US	HT-1037	1	51 352 888.96
    US	HT-1021	2	8 026 133.60
    US	HT-1116	3	4 848 000.00
    US	HT-1106	4	2 441 955.76
    US	HT-1502	5	1 243 077.12

    ,

    1. Choose the arrow at the right of the COUNTRY column header

    2. In the Filter field, enter US and press Enter.

       Note

       This way of filtering does not re-execute the query against the calculation view, but just filters the current result set. It is valid in the current scenario because the complete result set has 80 rows, which is less than the maximum number of rows (1,000) defined by default in the settings of SAP Business Application Studio (SAP HANA Database Explorer section).

       In other scenarios, you might need to apply a filter to the data preview query itself, by choosing Add Filter. The query will then be re-executed against the calculation view.

    Result

    Note

    The following intermediate solution object is available at this stage:

    solutions→CVC_SO_RANK_00_STAGE1

11. Close the preview tab.

You will now modify the properties of the Rank node and output columns of the calculation view CVC_SO_RANK.

1. Add the column BP_COMPANY_NAME to the definition of the Rank_1 node and up to the Semantics node.

   1. On the Mapping tab of the Rank_1 node, select the BP_COMPANY_NAME column and choose Add To Output.

      Alternatively, you can drag the column from the Data Sources pane to the Output Columns pane.

   2. In the Output Columns pane, right-click the column BP_COMPANY_NAME and choose Propagate to Semantics.

   3. In the confirmation dialog box, choose OK.

2. Deploy the CVC_SO_RANK calculation view.

   Check the deployment status after activation.

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

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

3. Preview the data of the CVC_SO_RANK calculation view.

   1. Right-click the CVC_SO_RANK calculation view and choose Data Preview.

   2. Display the Raw Data tab.

4. Modify the default SQL query of the data preview to remove the BP_COMPANY_NAME column. You must also remove this column from the GROUP BY clause. Then, execute the modified query and filter the result set on COUNTRY = 'US'.

   The modified SQL query should look as follows:

   Code Snippet

   Copy codeSwitch to dark mode

   1234567
   SELECT TOP 1000
   	"COUNTRY",
   	"PRODUCT_ID",
   	"RANK",
   	SUM("GROSS_AMOUNT") AS "GROSS_AMOUNT"
   FROM ...
   GROUP BY "COUNTRY", "PRODUCT_ID", "RANK";

   1. In the Data Preview tab, choose Edit Sql Statement In Sql Console.

   2. Remove the column "COMPANY_NAME", both from the SELECT section and the GROUP BY section of the statement.

      Do not forget to also remove the commas following the column name.

   3. Choose Run.

   4. Choose the arrow at the right of the COUNTRY column header.

   5. In the Filter field, enter US and press Enter.

   Result

   Note

   The following intermediate solution object is available at this stage:

   solutions→CVC_SO_RANK_00_STAGE2.

5. Close the preview tabs.

6. Modify the CVCS_SO_RANK calculation view, adding the BP_COMPANY_NAME column to the Logical Partition of the Rank node. Then, deploy the calculation view.

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

   2. On the Definition tab, under the Partition Column area, choose + and select the BP_COMPANY_NAME column with the drop-down list.

   3. Choose the Deploy icon for your CVC_SO_RANK calculation view.

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

   Result

   Note

   The following intermediate solution object is available at this stage:

   solutions→CVC_SO_RANK_00_STAGE3.

7. Preview the data of the CVC_SO_RANK calculation view.

   1. Right-click the CVC_SO_RANK calculation view and choose Data Preview.

   2. Display the Raw Data tab.

8. Modify the default SQL query of the data preview to remove the BP_COMPANY_NAME column. You must also remove this column from the GROUP BY clause. Then execute the modified query and filter the result set on COUNTRY = 'US'.

   The modified SQL query should look as follows:

   Code Snippet

   Copy codeSwitch to dark mode

   1234567
   SELECT TOP 1000
   	"COUNTRY",
   	"PRODUCT_ID",
   	"RANK",
   	SUM("GROSS_AMOUNT") AS "GROSS_AMOUNT"
   FROM ...
   GROUP BY "COUNTRY", "PRODUCT_ID", "RANK";

   1. In the Data Preview tab, choose Edit Sql Statement In Sql Console.

   2. Remove the column "COMPANY_NAME", both from the SELECT section and the GROUP BY section of the statement.

      Do not forget to also remove the commas following the column name.

   3. Choose Run.

   4. Choose the arrow at the right of the COUNTRY column header.

   5. In the Filter field, enter US and press Enter.

9. Close the preview tabs.

10. Modify the view CVC_SO_RANK to activate the Dynamic Partition Elements in the properties of the Rank_1 node. Deploy the calculation view.

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

    2. In the Definition tab, select the Dynamic Partition Elements checkbox.

    3. Choose the Deploy icon for your CVC_SO_RANK calculation view.

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

    Result

    Note

    The following intermediate solution object is available at this stage:

    solutions→CVC_SO_RANK_00_STAGE4.

11. Preview the data of the CVC_SO_RANK calculation view. You get the same result, partitioned by COUNTRY and BP_COMPANY_NAME.

    1. Right-click the CVC_SO_RANK calculation view and choose Data Preview.

    2. Display the Raw Data tab.

12. Modify the default SQL query of the data preview to remove the BP_COMPANY_NAME column. You must also remove this column from the GROUP BY clause. Then execute the modified query and filter the result set on COUNTRY = 'US'.

    The modified SQL query should look as follows:

    Code Snippet

    Copy codeSwitch to dark mode

    1234567
    SELECT TOP 1000
    	"COUNTRY",
    	"PRODUCT_ID",
    	"RANK",
    	SUM("GROSS_AMOUNT") AS "GROSS_AMOUNT"
    FROM ...
    GROUP BY "COUNTRY", "PRODUCT_ID", "RANK";

    1. In the Data Preview tab, choose Edit Sql Statement In Sql Console.

    2. Remove the column "COMPANY_NAME",, both from the SELECT section and the GROUP BY section of the statement.

       Do not forget to also remove the commas following the column name.

    3. Choose Run.

    4. Choose the arrow at the right of the COUNTRY column header.

    5. In the Filter field, enter US and press Enter.

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

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

Now, you want to retrieve the top 10% best-selling products by country. You will adjust the calculation view accordingly.

1. Open the CVCS_SO_RANK calculation view.

   1. In the Explorer view, select the src→exercises→CVCS_SO_RANK calculation view.

2. In the Rank node definition, remove the column BP_COMPANY_NAME from the output columns. Confirm that it has automatically been removed from the list of columns used to partition the data set.

   1. Double-click the Rank_1 node and display the Mapping tab.

   2. In the Output Columns area, select the BP_COMPANY_NAME column and choose Remove Output Column.

   3. In the confirmation dialog box, read the warning and choose Yes.

   4. In the Definition tab, confirm that the Logical Partition only contains the COUNTRY column.

3. Adjust the Definition of the Rank node as follows (the table only mentions the properties you need to modify):

   Property	Value
   Result Set Type	Percentage
   Target Value	Fixed Value: 0.1

   1. Select the Rank_1 node.

   2. In the Definition tab, adjust the properties as per the table.

4. Deploy the CVC_SO_RANK calculation view.

   Check the deployment status after activation.

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

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

   Result

   Note

   The following intermediate solution object is available at this stage:

   solutions→CVC_SO_RANK_00_STAGE5.

5. Preview the intermediate result set of the Rank_1 node.

   1. In the Scenario pane, right-click the Rank_1 node and choose Data Preview.

   2. Display the Raw Data tab.

6. Filter the result set on COUNTRY = 'CN'.

   1. Choose the arrow at the right of the COUNTRY column header.

   2. In the Filter field, enter CN and press Enter.

7. Refresh the preview of the CVCS_SO_RANK calculation view.

   1. Choose the Refresh icon in the Raw Data pane.

8. Close the preview tabs.

9. In the definition of the CVCS_SO_RANK calculation view, sort the result set of the calculation view by COUNTRY (asc) and RANK (asc).

   1. In the Semantics node, display the Columns tab.

   2. Choose Sort Result Set.

      Note

      Depending on your screen size and display mode, the button might be hidden. In this case, choose the Toolbar Overflow button.

   3. Choose +Add.

   4. Select the COUNTRY column.

   5. Keep the default sort order Ascending.

   6. Repeat these steps for the RANK column.

10. Deploy the CVC_SO_RANK calculation view.

    Check the build status after activation.

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

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

    Result

    Note

    The following intermediate solution object is available at this stage:

    solutions→CVC_SO_RANK_00_STAGE6.

11. Preview the CVCS_SO_RANK calculation view and confirm that the result set is now logically ordered.

    1. Right-click the CVC_SO_RANK calculation view and choose Data Preview.

    2. Display the Raw Data tab.

    3. Check that the result set is now ordered in a relevant way.

12. Close all open tabs except the Calculation View editor tab for the CSC_SO_RANK calculation view.

    1. Select then right-click the tab CVC_SO_RANK.hdbcalculationview and choose Close Others.

       Note

       You can use the tooltip to check the complete tab name. As an aside, a design-time file always include the file extension —here, .hdbcalculationview– which is not the case for data preview tabs.

Now, you would like to find the products that sell worst in each country, but this time you want the number of products in the result set for each country to be determined as a percentage of the total sales in this country.

More specifically, you want to extract and rank, in each country, the products that generate the lowest revenue and represent 1% of the total sales (GROSS_AMOUNT) in the country.

1. In the CVC_SO_RANK calculation view, adjust the Definition of the Rank node as follows (the table only mentions the properties you need to modify):

   Property	Value
   Aggregation Function	Sum
   Target Value	Fixed Value: 0.01
   Sort Column	GROSS_AMOUNT (direction: Ascending)

   Note

   Keep the Result Set Type setting Percentage.

   1. Select the Rank_1 node.

   2. In the Definition tab, adjust the properties as per the table.

2. Deploy the CVC_SO_RANK calculation view.

   Check the deployment status after activation.

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

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

   Result

   Note

   The following intermediate solution object is available at this stage:

   solutions→CVC_SO_RANK_00_STAGE7

3. Preview the Calculation View. Check that the result subset for COUNTRY = 'CA' is consistent.

   1. Right-click the CVC_SO_RANK calculation view and choose Data Preview.

   2. Display the Raw Data tab.

   3. Choose the arrow at the right of the COUNTRY column header.

   4. In the Filter field, enter CA and press Enter.

   5. Confirm that the cumulative rank does not exceed 0.01 for any of the returned rows (in particular the last one).

4. Close the data preview tab.

5. (Optional) Consider another way to achieve a similar result.

6. (Optional) Adjust the Definition of the Rank node as follows (the table only mentions the properties you need to modify).

   Property	Value
   Aggregation Function	Sum
   Result Set Direction	Down
   Sort Column	GROSS_AMOUNT (direction: Descending)

   1. Select the Rank_1 node.

   2. In the Definition tab, adjust the properties as per the table.

7. (Optional) Deploy the CVC_SO_RANK calculation view.

   Check the deployment status after activation.

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

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

8. (Optional) Preview the Calculation View. Compare the results for COUNTRY = 'CA' with what you got before.

   1. Right-click the CVC_SO_RANK calculation view and choose Data Preview.

   2. Display the Raw Data tab.

   3. Choose the arrow at the right of the COUNTRY column header.

   4. In the Filter field, enter CA and press Enter.