Define Address Dimension

Objective

After completing this lesson, you will be able to create a graphical view with Dimension as the sematic type. Add Geo-Code data and a hierarchy to the view. Enrich the outcome with dimension specific semantics and associate it with another dimension, before persisting the data of the Address dimension.

Contents

  • Create Address Dimension
  • Add Geo-Coordinates Column
  • Duplicate Country Column
  • Add Projection
  • Add a Level-Based Hierarchy
  • Add Dimension Association
  • Deploy and Persist View


In this lesson, we create an Address Dimension view. The view is extended by geo-coordinates to suppport the Geo-Map capability of SAP Analytics Cloud. We define a location hierarchy, grouping the address data into region, country and city levels. Finally, we link the address view with the Countries dimension table, containing the country text description.

At the conclusion of this lesson your Graphical View will look like the following:

The Impact and Lineage Analysis perspective shows the target object association(s):


Review the Help Portal for more information at Creating a Graphical View .


Create Address Dimension

We create first the Address Dimension view which becomes linked with the Countries Dimension table. For the Address Dimension, the remote table with the address master data is our base.

  1. Select Data Builder in the side navigation area. Choose your space if necessary.

  2. Select the New Graphical View tile, opening the view editor.

In source tree panel on the left side of the canvas, local and shared objects are listed in the Repository tab. Alternatively objects of remote sources are available for selection in the Sources tab.

  1. In the Repository tab, select the Addresses table and drag it onto the canvas.

    Note

    : If you did not create the

    On the canvas, the Addresses node is linked with the output node View 1 automatically. The output node represents the final dataset and properties of the model.

Update the View Properties.

  1. Select the output node View 1, then go to the properties side panel on the right.

  2. Enter Address Dimension as the new Business Name.

  3. Don’t change the Release State, as your user doesn’t have sufficient privileges to revert back to the previous state.

    Release State is an optional feature. It provides stable objects consumers can use with confidence.

    Select here for more information.

  4. Change the Semantic Usage to Dimension.

    The Semantic Usage determines how the entity will be used. Dimension indicates that your entity contains attributes that can be used to analyse and categorize measures defined in other entities.

  1. Set Expose for Consumption to ON.

    This makes the object available for consumption in SAP Analytics Cloud and other analytical clients.

  1. [Optional] Choose Hide source tree and Zoom to Fit to rearrange the modeling nodes on the canvas, as we don’t add further objects from the source tree panel to the canvas.

Add Geo-Coordinates Column

Next, we will create a geo-coordinates column to combine latitude and longitude values that can be used in an SAP Analytics Cloud Geo Map. You can only create geo-coordinates in a graphical view with a semantic usage of Dimension.

Review the Help Portal for more information at Creating a Geo-Coordinates Column.


  1. Select the Addresses table node on the canvas.

  1. Choose the Calculated Columns operator in the popup menu.

  2. Select the new Calculated Columns node that was added to the diagram.

  3. Click the Add a New Calculated Column ‘+’ icon in the properties panel.

  1. Choose Geo-Coordinates Column in the dropdown.

  2. Change the Business Name to BPLocation.

  1. Verify that the LATITUDE and LONGITUDE columns are set correctly.

  2. Select the Columns link, to return to the main properties of the Calculated Column node.

  1. The new column BPLocation is added at the bottom of the column list.

In addition to the new geo-coordinate column, create a duplicate of the existing COUNTRY column. It will be used later for a hierarchy definition.

  1. Select the new Calculated Columns node that was added to the diagram.

  2. Click the Add a New Calculated Column ‘+’ icon in the properties panel.

  1. Choose Calculated Column in the dropdown.

  1. For the new column, fill in the following values:

    • Business Name: COUNTRYID
    • Expression box: COUNTRY

  2. Validate the expression.

  3. Select the Columns link, to return to the main properties of the Calculated Column node.

  1. The new column COUNTRYID is added at the bottom of the column list.

Add Projection

Exclude and rename the columns that are not required in the output node for future analysis.

  1. Select the Calculated Columns node on the canvas.

  2. Choose the Projection operator in the popup menu, to add it to the diagram.

  1. Select the Projection node that was added to the diagram.

  2. Go to the properties panel, select the More (…) button for BW Delta Process: Update Mode, and choose Exclude Column.

  1. Repeat the last step for the following columns:
  • POSTALCODE
  • STREET
  • BUILDING
  • Byte
  • VALIDITY_STARTDATE
  • VALIDITY_ENDDATE


Add a Level-Based Hierarchy

Add a Level-Based Hierarchy on the Dimension view, to support drill-down and drill-up.

Review the Help Portal for more information at Add a Hierarchy to a Dimension.


  1. Select the output node Address Dimension.

  1. Press the Hierarchy button in the View Properties panel, which will open the hierarchy dialog.

  2. Select the Add ‘+’ button, to add a hierarchy.

  1. Choose Level Based Hierarchy.

  2. Change the names:

    • Business Name: LocationHierarchy
    • Technical Name: LocationHi

  1. Select the Add Level ‘+’ button in the Levels section.

    Start building the levels of the hierarchy.

  2. Use the dropdown to select the REGION column.

  3. Repeat this action. Select the Add Level ‘+’ button, and select COUNTRYID in the new row of the dialog box.

  1. Repeat it for CITY.

The hierarchy has a fixed number of levels and will be structured top-down in the order of the fields you've selected (top of the list to the bottom of the list).

  1. Press the Close button.

  2. Note the indicator for the existence of a hierarchy on the Dimension. Only a table or a view with the semantic usage Dimensions can have hierarchies.



Add Dimension Association

We will now link the dimension table Countries to our Address Dimension view to enhance the country codes by their country names.

  1. In the properties panel of the Address Dimension node, scroll down to Associations section.

  1. Select the Create Association ‘+’ button, and choose Association.

  2. In the Select Association Target dialog, choose Countries (Technical Name) and press the Select button. (If you choose the Business Name link, the object editor opens in a new tab).

    Note

    : If you did not create the table dimension

  1. Review the Mappings section. An automatic mapping is done for matching column names.

    • Ensure that the COUNTRY column of the Address Dimension is joined to the COUNTRYCODE column of Countries.

    • To define the mapping, select COUNTRY. Drag the column and drop it on COUNTRYCODE.

  1. Select the More downward chevron to the left of the slash in the header area, and select the Address Dimension entry.

    This should return you to the Associations section in the properties panel.

  2. The view diagram and dimension properties will look like the following:

Before saving the view, let us look on the attribute values of the dimension view. The attributes of associated dimension are not displayed in the data preview.

  1. Select the output node Address Dimension in the canvas, and choose the Data Viewer operator in the popup menu.

    The values are displayed in the preview window below.

Save and Deploy

When you save an object, it is stored in the SAP Datasphere repository. Doing a deployment, the object is created as a run-time version for use in the SAP Datasphere database.

  1. Press the Deploy icon in the header menu.

  2. Confirm the Business Name as Address Dimension in the dialog popup, and Save it.

  3. Notification messages appear and informs you on the deployment progress.

  4. Ensure the view is deployed before you continue.

Persist Dimension View

Optionally, persist the Address Dimension view. The data of the view represents master data, which changes less frequently than transactional data.

  1. Go to the Data Persistence section in the Properties panel.

  2. Select the Data Persistence icon, and choose Start Data Persistence. If asked, confirm the action in the dialog popup.

  3. Select the Refresh icon, to track the persistence progress.

  4. [Optional] Open the Views Monitor link. There, you can monitor the run details inside the Data Integration Monitor.

    In the Data Integration Monitor, select the Back ‘<’ symbol in the shell bar (top bar), or choose the Open in Data Builder icon, to return to the view editor.

  5. Close the graphical view editor and return to the Data Builder entry site.



Congratulations! You have successfully created a Dimension View representing the Address master data. In addition, you applied a geo map calculated column for use with SAP Analytics Cloud, defined a location hierarchy and the semantics. Finally, you associated the Address Dimension with the Countries Dimension table, and persisted the data of the Dimension View.


Contents

  • Create Address Dimension
  • Add Geo-Coordinates Column
  • Duplicate Country Column
  • Add Projection
  • Add a Level-Based Hierarchy
  • Add Dimension Association
  • Deploy and Persist View


In this lesson, we create an Address Dimension view. The view is extended by geo-coordinates to suppport the Geo-Map capability of SAP Analytics Cloud. We define a location hierarchy, grouping the address data into region, country and city levels. Finally, we link the address view with the Countries dimension table, containing the country text description.

At the conclusion of this lesson your Graphical View will look like the following:

The Impact and Lineage Analysis perspective shows the target object association(s):


Review the Help Portal for more information at Creating a Graphical View .


Create Address Dimension

We create first the Address Dimension view which becomes linked with the Countries Dimension table. For the Address Dimension, the remote table with the address master data is our base.

  1. Select Data Builder in the side navigation area. Choose your space if necessary.

  2. Select the New Graphical View tile, opening the view editor.

In source tree panel on the left side of the canvas, local and shared objects are listed in the Repository tab. Alternatively objects of remote sources are available for selection in the Sources tab.

  1. In the Repository tab, select the Addresses table and drag it onto the canvas.

    Note

    : If you did not create the

    On the canvas, the Addresses node is linked with the output node View 1 automatically. The output node represents the final dataset and properties of the model.

Update the View Properties.

  1. Select the output node View 1, then go to the properties side panel on the right.

  2. Enter Address Dimension as the new Business Name.

  3. Don’t change the Release State, as your user doesn’t have sufficient privileges to revert back to the previous state.

    Release State is an optional feature. It provides stable objects consumers can use with confidence.

    Select here for more information.

  4. Change the Semantic Usage to Dimension.

    The Semantic Usage determines how the entity will be used. Dimension indicates that your entity contains attributes that can be used to analyse and categorize measures defined in other entities.

  1. Set Expose for Consumption to ON.

    This makes the object available for consumption in SAP Analytics Cloud and other analytical clients.

  1. [Optional] Choose Hide source tree and Zoom to Fit to rearrange the modeling nodes on the canvas, as we don’t add further objects from the source tree panel to the canvas.

Add Geo-Coordinates Column

Next, we will create a geo-coordinates column to combine latitude and longitude values that can be used in an SAP Analytics Cloud Geo Map. You can only create geo-coordinates in a graphical view with a semantic usage of Dimension.

Review the Help Portal for more information at Creating a Geo-Coordinates Column.


  1. Select the Addresses table node on the canvas.

  1. Choose the Calculated Columns operator in the popup menu.

  2. Select the new Calculated Columns node that was added to the diagram.

  3. Click the Add a New Calculated Column ‘+’ icon in the properties panel.

  1. Choose Geo-Coordinates Column in the dropdown.

  2. Change the Business Name to BPLocation.

  1. Verify that the LATITUDE and LONGITUDE columns are set correctly.

  2. Select the Columns link, to return to the main properties of the Calculated Column node.

  1. The new column BPLocation is added at the bottom of the column list.

In addition to the new geo-coordinate column, create a duplicate of the existing COUNTRY column. It will be used later for a hierarchy definition.

  1. Select the new Calculated Columns node that was added to the diagram.

  2. Click the Add a New Calculated Column ‘+’ icon in the properties panel.

  1. Choose Calculated Column in the dropdown.

  1. For the new column, fill in the following values:

    • Business Name: COUNTRYID
    • Expression box: COUNTRY

  2. Validate the expression.

  3. Select the Columns link, to return to the main properties of the Calculated Column node.

  1. The new column COUNTRYID is added at the bottom of the column list.

Add Projection

Exclude and rename the columns that are not required in the output node for future analysis.

  1. Select the Calculated Columns node on the canvas.

  2. Choose the Projection operator in the popup menu, to add it to the diagram.

  1. Select the Projection node that was added to the diagram.

  2. Go to the properties panel, select the More (…) button for BW Delta Process: Update Mode, and choose Exclude Column.

  1. Repeat the last step for the following columns:
  • POSTALCODE
  • STREET
  • BUILDING
  • Byte
  • VALIDITY_STARTDATE
  • VALIDITY_ENDDATE


Add a Level-Based Hierarchy

Add a Level-Based Hierarchy on the Dimension view, to support drill-down and drill-up.

Review the Help Portal for more information at Add a Hierarchy to a Dimension.


  1. Select the output node Address Dimension.

  1. Press the Hierarchy button in the View Properties panel, which will open the hierarchy dialog.

  2. Select the Add ‘+’ button, to add a hierarchy.

  1. Choose Level Based Hierarchy.

  2. Change the names:

    • Business Name: LocationHierarchy
    • Technical Name: LocationHi

  1. Select the Add Level ‘+’ button in the Levels section.

    Start building the levels of the hierarchy.

  2. Use the dropdown to select the REGION column.

  3. Repeat this action. Select the Add Level ‘+’ button, and select COUNTRYID in the new row of the dialog box.

  1. Repeat it for CITY.

The hierarchy has a fixed number of levels and will be structured top-down in the order of the fields you've selected (top of the list to the bottom of the list).

  1. Press the Close button.

  2. Note the indicator for the existence of a hierarchy on the Dimension. Only a table or a view with the semantic usage Dimensions can have hierarchies.



Add Dimension Association

We will now link the dimension table Countries to our Address Dimension view to enhance the country codes by their country names.

  1. In the properties panel of the Address Dimension node, scroll down to Associations section.

  1. Select the Create Association ‘+’ button, and choose Association.

  2. In the Select Association Target dialog, choose Countries (Technical Name) and press the Select button. (If you choose the Business Name link, the object editor opens in a new tab).

    Note

    : If you did not create the table dimension

  1. Review the Mappings section. An automatic mapping is done for matching column names.

    • Ensure that the COUNTRY column of the Address Dimension is joined to the COUNTRYCODE column of Countries.

    • To define the mapping, select COUNTRY. Drag the column and drop it on COUNTRYCODE.

  1. Select the More downward chevron to the left of the slash in the header area, and select the Address Dimension entry.

    This should return you to the Associations section in the properties panel.

  2. The view diagram and dimension properties will look like the following:

Before saving the view, let us look on the attribute values of the dimension view. The attributes of associated dimension are not displayed in the data preview.

  1. Select the output node Address Dimension in the canvas, and choose the Data Viewer operator in the popup menu.

    The values are displayed in the preview window below.

Save and Deploy

When you save an object, it is stored in the SAP Datasphere repository. Doing a deployment, the object is created as a run-time version for use in the SAP Datasphere database.

  1. Press the Deploy icon in the header menu.

  2. Confirm the Business Name as Address Dimension in the dialog popup, and Save it.

  3. Notification messages appear and informs you on the deployment progress.

  4. Ensure the view is deployed before you continue.

Persist Dimension View

Optionally, persist the Address Dimension view. The data of the view represents master data, which changes less frequently than transactional data.

  1. Go to the Data Persistence section in the Properties panel.

  2. Select the Data Persistence icon, and choose Start Data Persistence. If asked, confirm the action in the dialog popup.

  3. Select the Refresh icon, to track the persistence progress.

  4. [Optional] Open the Views Monitor link. There, you can monitor the run details inside the Data Integration Monitor.

    In the Data Integration Monitor, select the Back ‘<’ symbol in the shell bar (top bar), or choose the Open in Data Builder icon, to return to the view editor.

  5. Close the graphical view editor and return to the Data Builder entry site.



Congratulations! You have successfully created a Dimension View representing the Address master data. In addition, you applied a geo map calculated column for use with SAP Analytics Cloud, defined a location hierarchy and the semantics. Finally, you associated the Address Dimension with the Countries Dimension table, and persisted the data of the Dimension View.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn