Skip to Content

Create a Dimension Graphical Calculation View (XS Advanced)

Create a graphical calculation view with a dimension data type.
You will learn
  • How to create a calculation view with a Dimension data type
  • How to perform the basic modelling operations, such as projections and joins
  • This tutorial is also available as a video
jung-thomasThomas JungJanuary 5, 2021
Created by
ccmehil
September 5, 2016
Contributors
jung-thomas
ccmehil

Prerequisites

  • This tutorial is designed for SAP HANA on premise and SAP HANA, express edition. It is not designed for SAP HANA Cloud.
  • Tutorials: Import SHINE tables and data
  • Step 1

    Create a new file called models/.hdinamespace under /db/src.

    Create namespace

    Although there is no need to use namespaces in general, calculation views are consumed from external reporting tools best with namespaces

    Use the following code in the file

    JSON
    Copy
    {
        "name":    "db.models",
        "subfolder": "ignore"
    }
    
    

    Save and close the file.

    If you do not see the file, go to View-> Show hidden files.

  • Step 2

    Right-click on the models folder and create a new calculation view

    New CV

    Call it PRODUCTS and choose type DIMENSION and click Create

    Call it Products
  • Step 3

    Click the Create join button and drop the node at the bottom of the design window

    New Join node

    Right-click on the node and call it Product_BP

    New Join node

    Press enter and select the node again, choose the + sign to add data sources

    Call it Products

    Add MD.Products and MD.BusinessPartners to the node. Use the + sign to search for the entities.

    Add data sources

    Double-click on the name of the node to open the join definition. Drag and drop SUPPLIER.PARTNERID to PARTNERID to connect the two tables.

    Add data sources

    Click on the mapping tab and select PRODUCTID, TYPECODE, CATEGORY, NAMEID, CURRENCY, PRICE, DESCID, PARTNERID, COMPANYNAME and ADDRESES.ADDRESSID and then choose Add To Output.

    Call it Products

    Change the name of the column Category to ProductCategory

    Call it Products
  • Step 4

    Create a new join node, and drag the existing node as input

    Add data sources

    Use the + sign to add MD.Addresses to this join node.

    Add addresses

    Join the two tables by ADDRESSES_ADDRESSID and ADDRESSID.

    Add addresses

    Go to the mapping tab to add columns to the output. Select all columns from the Product_BP node except ADDRESSES_ADDRESSID. From MD.Addresses table select CITY, POSTALCODE, STREET, BUILDING, COUNTRY and REGION.

    You can double-click on Product_BP to add all of the columns to the output and then right-click on ADDRESSES_ADDRESSID to remove that output column

    Add addresses

    Rename the current join node to Address

  • Step 5

    Add a new join node and use the Address node as input. Add Util.Texts as a data source.

    Add texts

    Join NAMEID to TEXTID. Set the join to Text join and the language column to LANGUAGE.

    Add texts

    In the mapping tab, select all columns from the Address node except NAMEID and add them to the output. From the Util.Texts table select TEXT.
    Change the name of the TEXT column in the output to ProductName.

    Add texts

    Rename the join node to ProductName.

  • Step 6

    Add a new join node and use ProductName as an input. Add Util.Texts again as a source.

    Add texts

    Use a text join between DESCID and TEXTID.

    Add texts

    Repeat the process of adding columns to the output via the mapping tab. Select all columns from the ProductName node except DESCID. From the Util.Texts table select TEXT but change the name of to ProductDesc.

    Add texts

    Rename the join node to ProductDesc.

  • Step 7

    Connect the node ProductDesc to the projection node. Use the AutoLayout feature to align the nodes and the Auto Map by name button to create the output. Save the Calculation View.

    Final view

    Build the view then right-click on it and choose Data Preview.

    Final view

    The output should be similar to the following. Copy the SQL statement produced by the SQL button into the validation below to complete it

    Final view

Back to top