Joining Data Sources in Calculation Views

Performing Practical Exercises

Objective

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

Create a DIMENSION Calculation View with joins

Exercise Objectives

After completing this exercise, you will be able to:

  • Define a join into a DIMENSION calculation view.
  • Join several tables to get all needed columns.

Business Example

You are at a customer site where the Enterprise Procurement Model (EPM) is used. Information about business partners, products, sales orders, and purchase orders is located in several tables, and you need to structure this data in a report.

You already created a DIMENSION calculation view for the products and now need to create a DIMENSION calculation views for the Business Partner dimension. This view will be used later on to create the star join calculation views for purchase orders.

The view will join several master data tables related to the business partners, including the contact persons and their e-mails.

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
    NameCVD_BP_JOIN
    LabelBusiness Partners
    Data CategoryDIMENSION
    TypeSTANDARD

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

    2. Enter CVD_BP_JOIN.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. Add a Join node Join_1 to the Scenario pane, and add the following tables from EPM_MODEL schema as data sources to the new node:

    • Table SNWD_BP: Business Partners master data

    • Table SNWD_CONTACT: Contact person details

    1. In the Scenario pane, add a Join node below the top nodes by selecting the Create Join node from the palette () and clicking in the scenario pane below the projection node.

    2. Select the new Join_1 node and click the + sign on the right of the node.

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

    4. In the search results, select the two tables SNWD_BP and SNWD_CONTACT.

    5. Choose Finish.

  4. If needed, expand the Details panel.

    1. Choose the Expand Details Panel icon.

  5. In the Join Definition tab for Join_1, join the data sources as follows:

    The table SNWD_BP is considered as the left table, and the table SNWD_CONTACT as the right table. If needed, move the two tables to make sure you have SNWD_BP on the left and do not define the join incorrectly.

    • Join the field CLIENT of the left table to the field CLIENT of the right table.

    • Join the field NODE_KEY of the left table to the field PARTNER_GUID of the right table.

    Use a Referential join type and a 1..1 cardinality:

    Hint

    To check the cardinality, in the Properties pane, you can choose Propose Cardinality.

    Screen capture of the join definition: CLIENT identified with CLIENT, NODE_KEY identified with PARTNER_GUID, cardinality 1:1.

    1. In the Join Definition tab, if needed, drag and drop the table SNWD_BP so that it appears at the left of the SNWD_CONTACTS table.

    2. Drag the SNWD_BP.CLIENT field to the SNWD_CONTACT.CLIENT field, and drag the SNWD_BP.NODE_KEY field to the SNWD_CONTACT.PARTNER_GUID field.

      Caution

      For the second connector, make sure that you actually join the PARTNER_GUID of the right table (NOT the NODE_KEY).

    3. To edit the join properties, select the new connector.

    4. In the PROPERTIES pane, In the Join Type dropdown list, choose Referential.

    5. In the Cardinality dropdown list, choose 1..1.

    6. Optionally, click the Propose Cardinality button.

      Note

      The Join Type and Cardinality properties are defined for each join, in this case, the pair of connections between the tables.

  6. In the Mapping tab for the node Join_1, add the following columns to the output:

    Source tableColumns

    SNWD_BP

    • CLIENT

    • NODE_KEY

    • BP_ID

    • BP_COMPANY_NAME

    SNWD_CONTACT

    • FIRST_NAME

    • LAST_NAME

    • LANGUAGE

    Hint

    You can add the columns from the two data sources to the output in a single operation, by selecting them all before clicking the Add to Output button. Ensure that you do not collapse a data source in the tree because it removes the selection. Moreover, do not select an entire data source from the tree, because in this case all the columns will be added to the output.

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

    2. Choose the Add To Output button.

      Screen capture of the Add columns to output icon: FIRST_NAME, LANGUAGE, LAST_NAME are selected.

  7. Add another Join node Join_2 to the Scenario pane, between the nodes Join_1 and Projection, and add the two following data sources to this new node:

    • Node Join_1

    • Table SNWD_BP_EM (e-mail address) from EPM_MODEL

    Hint

    To link a node to another one, select the source node and drag the arrow icon onto the target node. When the target node is reached, it gets a bold border, you can drop the arrow icon.

    1. In the Scenario pane, add a new Join node Join_2 below the top nodes by selecting the Create Join icon from the palette and clicking in the scenario pane between the Join_1 and Projection nodes..

    2. Select the Join_1 node.

    3. Drag the arrow icon to the Join_2 node.

    4. Select the Join_2 node.

    5. Choose the + icon on the right of the Join_2 node.

    6. In the search field, enter SNWD_.

    7. Select the SNWD_BP_EM table.

    8. Choose Finish.

  8. In the Join Definition tab, join the data sources of node Join_2 as follows:

    The source Join_1 is considered as the left table, and the table SNWD_BP_EM as the right table.

    • Join the field CLIENT of the left table to the field CLIENT of the right table.

    • Join the field NODE_KEY of the left table to the field PARTNER_GUID of the right table.

    Use a Referential join type and a 1..1 cardinality:

    Note

    The join is defined on the same fields and with the same properties as the previous one.

    1. On the Join Definition tab, drag the Join_1.CLIENT field to the SNWD_BP_EM.CLIENT field, and drag the Join1.NODE_KEY field to the SNWD_BP_EM.PARTNER_GUID field.

      Caution

      For the second join, ensure that you actually join the PARTNER_GUID of the right table (NOT the NODE_KEY).

    2. To edit the join properties, select the new connector.

    3. In the PROPERTIES pane, in the Join Type dropdown list, choose Referential.

    4. In the Cardinality dropdown list, choose 1..1.

    5. Choose OK.

  9. On the Mapping tab for node Join_2, add the following columns to the output:

    Source TableColumns

    Join_1

    [All the columns]

    SNWD_BP_EM

    EMAIL_ADDRESS

    Hint

    To add all the columns from Join_1 in a single step, select the Join_1 item from the Data Sources tree.

    1. In the Mapping tab for Join_2, select the Join_1 item and choose Add to Output.

      You do not need to select any specific column from this data source, you need all columns.

    2. Select the SNWD_BP_EM→EMAIL_ADDRESS field and choose Add To Output.

  10. Define the Join_2 node as the data source for the Projection node, and add all the columns of the Projection node to the output.

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

    2. Drag the arrow icon to the Projection node.

    3. In the Mapping tab of the Projection node, select the Join_2 data source and choose Add to Output.

  11. In the Semantics node, define the BP_ID column as a key column.

    Hint

    If the KEY column is not displayed by default, click the (Customize Column Display) button.

    1. In the Scenario pane, choose the Semantics node.

    2. In the Columns tab, choose (Customize Column Display), select the Key field and choose OK.

    3. Select the Key checkbox of the BP_ID column.

  12. Deploy the CVD_BP_JOIN calculation view.

    Check the deployment status.

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

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

  13. Preview the data of the CVD_BP_JOIN calculation view.

    1. Open SAP HANA Database Explorer.

    2. Select Column Views , right-click the HC::CVD_BP_JOIN view and choose Open Data.

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

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

Join Three Tables in a Single Join Node

Exercise Objectives

After completing this exercise, you will be able to:

  • Combine more than two data sources in a single Join node
  • Adjust Join node properties so that the joins are executed in the correct order to suit your scenario

Business Example

You are working as a modeler and would like to explore the possibility to define joins with more than two tables. However, you want to ensure that you can properly control the order in which several joins defined in the same node are executed. For this purpose, you will work on a sample data set and test different options.

Note

When more than one two tables need to be joined at a given stage of the calculation scenario, it is of course still possible to use join nodes with only two joined tables and to stack these nodes. It might be relevant in some cases, especially when you want full control over the join order and/or a better legibility of the calculation scenario.

Overview of Exercise Tasks

  • Task 1: Review the Data Sources Used in your Scenario

  • Task 2: Import a Calculation View and Observe its Behavior

  • Task 3: Modify the Multi-Join Order and Check the Impact on the Calculation View Output

Prerequisites

Prerequisites

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

Task 1: Review the Data Sources used in your Scenario

For the sake of simplicity, the sample data set is made up of three tables for Sales, Customers, and Countries. The three tables have names starting with HC::MJ_ (for multi-join) and are already defined and populated with data (their design-time files are part of the HDB module that you built during exercise 1).

Steps

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

  2. In the Database Explorer, open the three following tables to review their structure and content:

    • HC::MJ_SALES

    • HC::MJ_CUSTOMERS

    • HC::MJ_COUNTRIES

    1. Open SAP HANA Database Explorer.

    2. Select Tables.

    3. Select the Apply Filter icon and enter MJ in the search field then press Enter.

    4. In the search results, right-click each table and choose Open Data.

Task 2: Import a Calculation View and Observe its Behavior

You will now import a calculation view including a single Join node, combining the three tables (Multi-Join).

Steps

  1. Switch back to the Explorer.

  2. In the workspace, import the CVC_MJ_SALES.hdbcalculationview file from your course files folder HC300→Calculation Views Templates into the folder HC300_A####→db→src→exercises.

    1. In the workspace, right-click your exercises folder and choose upload.

    2. Browse for the file HC300→Calculation Views Templates→CVC_MJ_SALES.hdbcalculationview and choose Open.

    3. Make sure that the calculation view ends up in the .../exercises folder.

      Result

      A new calculation view CVC_MJ_SALES is created in your project and opened in a new tab.

  3. Expand the Details panel and review the design of the Join_1 node.

    Hint

    To improve the screen layout, you can hide the workspace by clicking on the Explorer icon, and also use the Maximize Details Panel icon.

    1. Choose the Expand Details Panel icon.

      Alternatively, you can double-click the Join_1 node.

    2. In the Join_1 node, review the Join Definition and Mapping tabs.

    3. To check a join definition, select the join connector in the Join Definition tab.

  4. Deploy the CVC_MJ_SALES calculation view.

    Check the deployment status.

    1. If needed, open the Explorer pane.

    2. Choose the Deploy icon for your CVC_MJ_SALES calculation view.

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

  5. Preview the calculation view data in the Raw Data tab.

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

    2. Display the Raw Data tab.

Task 3: Modify the Multi-Join Order and Check the Impact on the Calculation View Output

Now, you want to modify the Multi-Join Order and check whether it affects the calculation view results.

Steps

  1. Set the Multi Join Order property of the Join_1 node to Inside Out.

    1. Display the Join Definition tab of the Join_1 node and click anywhere in the white space.

    2. In the PROPERTIES pane, in the Multi Join Order drop-down list, select Inside Out.

  2. Deploy the CVC_MJ_SALES calculation view again.

    Check the deployment status.

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

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

  3. Preview the calculation view data on the Raw Data tab.

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

    2. Display the Raw Data tab.

  4. Close all open tabs in Business Application Studio.

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

Create a CUBE with Star Join Calculation View

Exercise Objectives

After completing this exercise, you will be able to:

  • Create a CUBE with Star Join calculation view.
  • Reuse existing dimension calculation views.

Business Example

You need to report on purchase order data. To meet the business requirements, you have decided to create a CUBE with Star Join calculation view.

Note

The fact table will be based on two sources from the SAP Enterprise Performance Management (EPM) model: the purchase order headers, and the purchase order Items.

Prerequisites

Prerequisites

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

Exercises to create the CVD_PD calculation view (Create a simple DIMENSION calculation view) and CVD_BP_JOIN calculation view (Create a DIMENSION calculation view using joins) should have been completed.

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
    NameCVCS_PO
    LabelPurchase Orders
    Data CategoryCUBE
    With Star Join[Selected]

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

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

    3. Enter the calculation view name 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. Add a Join node Join_1 to the Scenario pane, and add the following tables from EMP_MODEL as data sources to the new node:

    • Table SNWD_PO: Purchase order document headers

    • Table SNWD_PO_I: Purchase order line items

    1. In the Scenario pane, add a Join node below the top node by selecting the Create Join icon from the palette and adding the node below the star join node.

    2. Select the new Join_1 node and click the + sign on the right of the node.

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

    4. In the search results, select the two tables.

    5. Choose Finish.

  4. In the Join Definition tab, join the data sources of node Join_1 as follows:

    The table SNWD_PO is considered as the left table, and the table SNWD_PO_I as the right table.

    • Join the field CLIENT of the left table to the field CLIENT of the right table.

    • Join the field NODE_KEY of the left table to the field PARENT_KEY of the right table.

    Use a Referential Join type and a 1..n cardinality:

    Hint

    To check the cardinality, in the Properties pane, you can choose Propose Cardinality.

    1. In the Details pane, drag the SNWD_PO.CLIENT field to the SNWD_PO_I.CLIENT field, and then drag the SNWD_PO.NODE_KEY field to the SNWD_PO_I.PARENT_KEY field.

      Caution

      For the second connector, make sure that you actually join the PARENT_KEY of the right table (NOT the NODE_KEY).

    2. To edit the join properties, select one of the new connectors.

    3. In the Join Type dropdown list, choose Referential.

    4. In the Cardinality dropdown list, choose 1..n.

    5. Optionally, click the Propose Cardinality button.

  5. In the Mapping tab for node Join_1, add the following columns to the output:

    Source tableColumns

    SNWD_PO

    • CLIENT

    • PO_ID

    • PARTNER_GUID

    SNWD_PO_I

    • PO_ITEM_POS

    • PRODUCT_GUID

    • CURRENCY_CODE

    • GROSS_AMOUNT

    • TAX_AMOUNT

    • NET_AMOUNT

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

    2. Choose the Add To Output button.

  6. Define the Join_1 node as the data source for the Star Join node.

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

    2. Drag the arrow icon to the Star Join node.

  7. Add the following dimension calculation views to the Star Join node:

    Source FolderDimension Calculation View

    exercises

    CVD_BP_JOIN

    (Business partners)

    exercises

    CVD_PD

    (Products)

    Note

    There are several columns with the same names (CLIENT or NODE_KEY) so alias names are automatically proposed to avoid duplicates. These aliases could be modified in the Semantics node if needed.

    1. In the Scenario pane, choose the + sign on the right of the Star Join node.

    2. In the search field, enter CVD.

    3. Select the two dimension views mentioned in the table above.

    4. Choose Finish.

    5. In the Alias Proposal dialog box, confirm by choosing OK.

  8. In the Star Join node, join the Join_1 table with the two dimension calculation views as shown in the following table:

    Join_1 is considered as the left table, and the dimension calculation views as the right tables.

    Left Table ColumnRight Table Column

    Join_1.CLIENT

    CVD_BP_JOIN.CLIENT

    Join_1.PARTNER_GUID

    CVD_BP_JOIN.NODE_KEY

    Join_1.CLIENT

    CVD_PD.CLIENT

    Join_1.PRODUCT_GUID

    CVD_PD.NODE_KEY

    Use a Referential Join type and a n..1 cardinality.

    1. In the Join Definition tab of the Star Join node, drag the field CLIENT from the Join_1 data source to the field CLIENT of the calculation view CVD_BP_JOIN.

    2. Drag the field PARTNER_GUID from the Join_1 data source to the field NODE_KEY of the calculation view CVD_BP_JOIN.

    3. In the Join Type dropdown list, choose Referential.

    4. In the Cardinality dropdown list, choose n..1.

    5. Drag the field CLIENT from the Join_1 data source to the field CLIENT of the calculation view CVD_PD.

    6. Drag the field PRODUCT_GUID from the Join_1 data source to the field NODE_KEY of the calculation view CVD_PD.

    7. In the Join Type dropdown list, choose Referential.

    8. In the Cardinality dropdown list, choose n..1.

  9. Select the following columns from the fact table to promote them to the semantics node :

    • PO_ID

    • PO_ITEM_POS

    • CURRENCY_CODE

    • GROSS_AMOUNT

    • TAX_AMOUNT

    • NET_AMOUNT

    1. In the Mapping tab, select the columns as per the list above.

    2. Choose the Add To Output button.

  10. In the Semantics node, check that the relevant Type property is assigned to all the PRIVATE columns. The three columns with amounts are measures, all the others are attributes.

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

    2. Check the Type property of each private column.

  11. Hide the CLIENT and NODE_KEY columns (shared attributes) from the dimension calculation views CVD_BP_JOIN and CVD_PD. These columns are not needed for reporting.

    1. Select the Columns tab of the Semantics node.

    2. In the Shared columns list, select the Hidden checkbox of the two CLIENT columns and the two NODE_KEY columns.

  12. Hiding columns does not prevent name conflict during deployment, that's why alias names are still needed. To be more specific in the naming, change the alias names to the CLIENT and NODE_KEY columns (shared columns), as follows:

    Data SourceColumn NameAlias Name
    CVD_BP_JOINCLIENTCLIENT_BP
    CVD_BP_JOINNODE_KEYNODE_KEY_BP
    CVD_PDCLIENTCLIENT_PD
    CVD_PDNODE_KEYNODE_KEY_PD

    1. In the Shared columns list, update the Alias Name property as per the table above.

  13. Deploy the CVCS_PO calculation view.

    Check the deployment status.

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

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

  14. Preview the data of the CVCS_PO calculation view. Display the gross and net amounts by company name and product category.

    1. In the workspace, right-click the CVCS_PO calculation view and choose Data Preview.

    2. In the Analysis tab, drag the GROSS_AMOUNT and NET_AMOUNT columns to the Value Axis area.

    3. Drag the BP_COMPANY_NAME and CATEGORY columns to the Label Axis area.

    4. Change to Table Display to have a clearer view of the result.

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

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

Learning

SAP FacebookSAP YoutubeSAP LinkedIn