Creating Calculation Views

Performing Practical Exercises

Objective

After completing this lesson, you will be able to performing practical exercises

Create a simple DIMENSION Calculation View

Exercise Objectives

After completing this exercise, you will be able to:

  • Create a simple DIMENSION calculation view.
  • Deploy and Preview a calculation view.

Business Example

You are working as a modeler at a customer site where the Enterprise Procurement Model (EPM) is used.

You often need to report on products contained in the SNWD_PD table. You decide to create a CVD_PD calculation view that you will be able to use whenever products are needed.

Overview of Exercise Tasks

  • Create a DIMENSION calculation view.
  • Deploy the calculation view and preview the data.

Prerequisites

Prerequisites

Your SAP Business Application Studio development environment should have been set up and the HC300 project imported and deployed.

Task 1: Create a DIMENSION Calculation View

Steps

  1. Create a new DIMENSION calculation view named CVD_PD.

    1. Select View→Command Palette.

    2. Enter arti in the command line field.

    3. Choose the Create SAP HANA Database Artifact command.

    4. Choose the Browse for folder icon and select the /home/user/projects/HC300_A####/db/src/models/exercises/ folder. Choose OK.

    5. Complete the creation form with the information provided in the following table :

      Create SAP HANA Database Artifact

      PropertyValue
      Artifact typeCalculation View
      Artifact nameCVD_PD
      Data CategoryDIMENSION

    6. Choose Create.

  2. Add the SNWD_PD table in the projection node.

    1. In the open calculation view editor, select the Projection node and choose the Add Data Source icon.

    2. Enter snwd_pd in the Search field.

    3. Select the SNWD_PD table with synonym HC::SNWD_PD.

    4. Choose Finish.

  3. Select the following columns as output :

    • CLIENT
    • NODE_KEY
    • PRODUCT_ID
    • CATEGORY
    • SUPPLIER_GUID

    1. Click on the Expand Details Panel icon on the top right.

    2. If not already displayed, select the Mapping tab.

    3. Drag and drop each column listed above to the Output columns pane.

    4. Close the calculation view tab.

Task 2: Deploy the Calculation View and Preview Data

Steps

  1. Deploy the new calculation view.

    1. In the SAP HANA PROJECTS sub-pane, select the CVD_PD calculation view.

    2. Click on the Deploy icon.

  2. Open SAP HANA Database Explorer and preview the data.

    1. Go to SAP HANA Database Explorer.

    2. Select Columns Views.

    3. Right click on the HC::CVD_PD view and choose Open Data.

    4. Close the SQL tab without saving.

Create a simple CUBE Calculation View

Exercise Objectives

After completing this exercise, you will be able to:

  • Create a calculation view of type CUBE

Business Example

You are working as a modeler at a customer site where the Enterprise Procurement Model (EPM) is used.

You have been asked to model a calculation view to retrieve the gross and net amounts of sales orders by currency and sales order ID, and ordered by gross amounts (descending).

You will then create a calculation view of type CUBE that answers the design requirement.

Overview of Exercise Tasks

  • Create a Simple CUBE Calculation View

Prerequisites

Prerequisites

Your SAP Business Application Studio development environment should have been set up and the HC300 project imported and deployed.

Steps

  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:

    FieldValue
    NameCVC_SALES_SIMPLE
    LabelSales
    Data CategoryCUBE
    With star join[Deselected]

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

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

    3. Enter the calculation view label and other properties as specified in the table.

    4. Choose Create.

      Result

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

  3. In the Aggregation node, add the SNWD_SO table from EPM_MODEL as data source:

    1. Select the Aggregation node.

    2. Choose +, Add Data Source.

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

    4. In the search results, select the SNWD_SO table with synonym HC::SNWD_SO.

    5. Choose Finish.

  4. Expand the Details panel.

    Note

    From SAP HANA 2.0 SPS03 onwards, the Details panel is hidden when you create a new calculation view or open an existing one.

    1. Choose the Expand Details Panel icon.

    2. Alternatively, you can double-click the corresponding node in the Scenario pane.

  5. If necessary, maximize the size of the calculation view editor.

    1. Choose the Maximize Detail Panel icon.

  6. On the Mapping tab of the Aggregation node, add the following columns to the output.

    • CURRENCY_CODE

    • SO_ID (sales order ID)

    • GROSS_AMOUNT

    • NET_AMOUNT

    1. On the Mapping tab, select the columns as per the table above.

    2. Choose the Add To Output button.

  8. Modify the order of the columns so that CURRENCY_CODE comes before SO_ID.

    1. On the Columns tab of the Aggregation node, select the CURRENCY_CODE column and choose Move Up (the up arrow).

      Alternatively, you can use the cut/paste buttons. This is especially useful when you need to move several columns at a time and/or move columns further up or down, to a non-adjacent location.

  9. Order the result set as follows:

    • CURRENCY_CODE (ascending)

    • GROSS_AMOUNT (descending)

    Hint

    This is done in the Semantics node. In case the Sort Result Set icon is not visible, use the Toolbar Overflow button.

    1. On the Columns tab of the Semantics node, choose Sort Result Set.

    2. Choose + Add, and define the sort criteria for the CURRENCY_CODE column.

    3. Repeat the previous step for the GROSS_AMOUNT column. Make sure you choose the sort direction Descending.

    4. Choose OK.

  10. Check that the relevant type (Attribute or Measure) is assigned to each column. Gross and Net amounts should be flagged as measures, and other columns as attributes.

    1. On the Columns tab of the Semantics node, check the Type property.

    2. If needed, to change the Type property for a column, select it and use the Mark as Attribute and Mark as Measure buttons.

  11. Deploy the CVC_SALES_SIMPLE calculation view.

    Check the deployment status.

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

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

  12. Preview the data of the CVC_SALES_SIMPLE calculation view.

    1. Open SAP HANA Database Explorer.

    2. Select Columns Views, or refresh the content of the catalog browser by choosing the Refresh icon on the right.

    3. right-click the HC::CVC_SALES_SIMPLE view and choose Open Data.

    4. Close the SQL tab.

Define a Time Dimension Calculation View

Exercise Objectives

After completing this exercise, you will be able to:

  • Manage reference date/time data inside the HDB module of your project
  • Create a DIMENSION calculation view of type TIME

Business Example

You need to analyze sales data for your company, with the possibility to easily expand/collapse data aggregates based on different level of date granularity. For this purpose, you want to explore the capabilities of TIME dimension calculation views.

Overview of Exercise Tasks

  • Task 1: Generate Time Data in your HDI Container

  • Task 2: Create a Dimension Calculation View of type TIME

Prerequisites

Prerequisites

Your SAP Business Application Studio development environment should have been set up and the HC300 project imported and deployed.

Task 1: Generate Time Data in your HDI Container

Steps

  1. Generate data for an existing time table with the following settings:

    SettingValue
    Calendar TypeGregorian
    Period UnitTime - Day
    Generate Data After CreationChecked
    From Year2018
    To Year2019
    First Day of WeekSunday

    1. In your HC300_A#### project, right-click on the folder db and choose Maintain Time Tables.

    2. In the Generate Time Data window, use the settings provided above and choose Next then Finish.

    Don't forget you first need to select the check-box Time before you can choose the radio button Day. Also, you must select the check-box Generate Data After Creation.

  2. Examine the contents of the generated source file that represents the time table.

    1. In your HC300_A#### project, expand the generated folder time_tables and double-click on the file M_TIME_DIMENSION.hdbtable.

  3. Check the table contents using the Database Explorer.

    1. Open the Database Explorer and expand your container HC300_A####_HDI_CONTAINER and select Tables.

    2. Right-click on the table M_TIME_DIMENSION and choose Open Data and you should see records covering the years 2018 and 2019. In particular, notice the granularity of the record is at the level of date and also notice the related attributes that are generated for each date, such as month, quarter, year.

Task 2: Create a Dimension Calculation View of type TIME

Steps

  1. In your exercises folder, create a calculation view of the type time dimension using the values provided below.

    SettingValue
    NameCVD_DATE_ATTRIBUTES
    LabelDate attributes
    Data CategoryDIMENSION
    TypeTIME
    CalendarGregorian
    GranularityDate
    TableHC::M_TIME_DIMENSION
    Auto Createselected

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

    2. Name the created file CVD_DATE_ATTRIBUTES.hdbcalculationview and press Enter.

    3. Use the table above to complete the fields and press Create.

  2. Review the generated definition of the calculation view focusing on mapping, filter expression and the hierarchy definition.

    1. Select the Projection_1 node.

    2. In the Projection_1 node, select the Filter Expression tab and review the filters.

    3. Select the Semantics node.

    4. In the Hierarchies tab, double-click the Gregorian_Hierarchy item.

  3. Generate a root node for the hierarchy.

    1. Expand the Properties pane.

    2. In the Root node Visibility dropdown list, choose Add Root node.

  4. Deploy the calculation view.

    1. In the SAP HANA PROJECTS sub-pane, select the CVD_DATE_ATTRIBUTES calculation view.

    2. Click on the Deploy icon.

  5. Open SAP HANA Database Explorer and preview the data.

    1. Go to SAP HANA Database Explorer.

    2. Select Columns Views.

    3. Right click on the HC::CVD_DATE_ATTRIBUTES view and choose Open Data.

    4. Close the SQL tab without saving.

  6. Close all remaining tabs.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn