Define Product Dimension

Objective

After completing this lesson, you will be able to create a graphical view with Dimension as the sematic type. Combine the product related information from the data layer foundation. Filter your data or add other operators. Enrich the outcome with dimension specific semantics, before persisting the data of the Product dimension.

Contents

  • Create Product Dimension
  • Join Product Text
  • Join Sentiment Data
  • Add Filter
  • Add Projection
  • Adapt Attribute Properties (Semantics)
  • Preview Product Dimension Data
  • Save and Deploy
  • Persist Product Dimension View


In this lesson, we create a Product Dimension view. We join Sentiment Data and Product information from the previous unit, filter data with an SQL expression and add a projection to exclude columns. For this task, you use the Data Builder to build a Graphical View. Finally, the data of the view gets persisted in SAP Datasphere.

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


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


Create Product Dimension

  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 the 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. Go to the Repository tab in the Source Tree Panel.

  2. Drag the remote table Products onto the canvas.

    Note

    : If you did not create the remote table

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

  1. When you select the Products node on the canvas, the available operators are displayed. The options shown depend on the selected node type.

    Different operators (top to bottom):

    • Add a Filter node to filter your data with an SQL expression.
    • Add a Projection node to rename, reorder, or exclude columns.
    • Add a Calculated Columns node to create new columns and define calculations in them.
    • Add an Aggregation node to perform SUM, COUNT, MIN, and MAX calculations.
    • List Join Suggestion, refers to the join definitions of the entity relationship models.
    • Preview the data output by the selected diagram node in the Data Preview panel.
    • Open the Impact and Lineage Analysis diagram.
    • Open the object in its own editor in a new tab.

Update the View Properties.

  1. Select the output View 1 node and go to the properties panel on the right side. If the properties panel is not open, click on Details button at the top right.

  1. Enter Product Dimension as the new Business Name.

  2. 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.

    Click here for more information.

  3. Change the Semantic Usage type 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. Click here for more information.

  1. Set Expose for Consumption to ON.

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


Join Product Text

Join the remote table Products with the other remote table ProductTexts to enhance the product data by product name description. Both tables were imported to the SAP Datasphere repository in the previous Data Acquisition unit.

  1. Select the remote table ProductTexts in the source tree.

  2. Drag ProductTexts onto the canvas and drop it onto the Products object to join the two tables.

    Note

    : If you did not create the remote table

    Ensure to select the Join option in the popup, after you drop the table on the Products object.

  1. Note the auto-join on Product ID. No action is needed.


Join Sentiment Data

Join the product information with the product sentiment data, ingested from Google Cloud Storage in the previous Data Acquisition unit.

  1. Select the local table LoadSentiments.

  2. Drag LoadSentiments onto the canvas and drop it onto the Inner Join node in your canvas.

    Note

    : If you did not create the table

    Ensure to select the Join option in the popup after you drop the table on the Inner Join node.

  3. Note the auto-join on Product ID.

  1. [Optional] Choose Hide source tree and Zoom to fit to rearrange the modeling nodes on the canvas, as we have joined all the tables for the view.


Add Filter

Filter the product data to display only English text.


  1. Select the JOIN node to the left of the Output View.

  1. Choose the Filter operator in the popup to add it to the diagram.

  2. Go to the Columns tab in the Filter Properties panel.

  3. Scroll down the list of fields.

  4. Select the LANGUAGE field to use it in the Expression.

  5. Type ='EN' including the single quotes (make sure these are single quotes and not double quotes as these are interpreted quite differently in SQL).

    The completed expression should show:

    LANGUAGE='EN'

  6. Press the Validate button to check your entry.

  1. The validation should clear any errors and return this icon to green status.

  2. Select the output view node Product Dimension (last node to the right).

Since we already filter on English, there is no need to keep the Language attribute as a key.

  1. Scroll down the Attributes list to find the attribute LANGUAGE.

  2. Select the More (…) button, go to Key, and choose Remove as Key.


Add Projection

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

  1. For the definition of the output column attributes, select the Projection node.

  2. Go to the properties panel, select the More (…) button for CREATEBY, and choose Exclude Column.

  1. Repeat the last step for the following columns:
  • MEDIUM_DESCR
  • CREATEDAT
  • LONG_DESC
  • CHANGEBY
  • CHANGEDAT

  1. For column renaming, select the More (…) button for SHORT_DESCR, and choose Change Name.

  2. Change Business and Technical Name to DESCRIPTION and click the Rename button.

Adapt Attribute Properties (Semantics)

Maintain the attribute properties by selecting the appropriate semantic type to understand the value in the column. Click here for more information about attributes and their semantic types.

  1. Select the output node Product Dimension in the canvas, and press the Edit Columns icon in the Properties panel.

  2. Apply the following changes and close the attributes editor:

  • Set Semantic Type of attribute LANGUAGE to Language.
  • Set Semantic Type of attribute DESCRIPTION (SHORT_DESC) to Text.
  • Set Text/Association of attribute PRODUCTID to DESCRIPTION.

The semantics will enhance the data model, which we build in the next lessons. For example, instead of the value of PRODUCTID, the associated text of DESCRIPTION is displayed in the analysis screen.

Preview Product Dimension Data

  1. To preview your final dimension model, select the output node Product Dimension and choose the Data Viewer operator in the popup menu.


Save and Deploy

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

  1. Select the Deploy icon in the header menu.

  2. Confirm the Business Name as Product 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 Product Dimension View

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

  1. After the successful deployment, 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.



Congratulations! You have sucessfully created a Dimension Graphical View, which joins both remote product tables with the local sentiment data table. Applied a filter and a projection to the model, before specifying the attribute semantic types. Finally, you persisted the data of the Product Dimension View.


Contents

  • Create Product Dimension
  • Join Product Text
  • Join Sentiment Data
  • Add Filter
  • Add Projection
  • Adapt Attribute Properties (Semantics)
  • Preview Product Dimension Data
  • Save and Deploy
  • Persist Product Dimension View


In this lesson, we create a Product Dimension view. We join Sentiment Data and Product information from the previous unit, filter data with an SQL expression and add a projection to exclude columns. For this task, you use the Data Builder to build a Graphical View. Finally, the data of the view gets persisted in SAP Datasphere.

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


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


Create Product Dimension

  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 the 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. Go to the Repository tab in the Source Tree Panel.

  2. Drag the remote table Products onto the canvas.

    Note

    : If you did not create the remote table

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

  1. When you select the Products node on the canvas, the available operators are displayed. The options shown depend on the selected node type.

    Different operators (top to bottom):

    • Add a Filter node to filter your data with an SQL expression.
    • Add a Projection node to rename, reorder, or exclude columns.
    • Add a Calculated Columns node to create new columns and define calculations in them.
    • Add an Aggregation node to perform SUM, COUNT, MIN, and MAX calculations.
    • List Join Suggestion, refers to the join definitions of the entity relationship models.
    • Preview the data output by the selected diagram node in the Data Preview panel.
    • Open the Impact and Lineage Analysis diagram.
    • Open the object in its own editor in a new tab.

Update the View Properties.

  1. Select the output View 1 node and go to the properties panel on the right side. If the properties panel is not open, click on Details button at the top right.

  1. Enter Product Dimension as the new Business Name.

  2. 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.

    Click here for more information.

  3. Change the Semantic Usage type 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. Click here for more information.

  1. Set Expose for Consumption to ON.

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


Join Product Text

Join the remote table Products with the other remote table ProductTexts to enhance the product data by product name description. Both tables were imported to the SAP Datasphere repository in the previous Data Acquisition unit.

  1. Select the remote table ProductTexts in the source tree.

  2. Drag ProductTexts onto the canvas and drop it onto the Products object to join the two tables.

    Note

    : If you did not create the remote table

    Ensure to select the Join option in the popup, after you drop the table on the Products object.

  1. Note the auto-join on Product ID. No action is needed.


Join Sentiment Data

Join the product information with the product sentiment data, ingested from Google Cloud Storage in the previous Data Acquisition unit.

  1. Select the local table LoadSentiments.

  2. Drag LoadSentiments onto the canvas and drop it onto the Inner Join node in your canvas.

    Note

    : If you did not create the table

    Ensure to select the Join option in the popup after you drop the table on the Inner Join node.

  3. Note the auto-join on Product ID.

  1. [Optional] Choose Hide source tree and Zoom to fit to rearrange the modeling nodes on the canvas, as we have joined all the tables for the view.


Add Filter

Filter the product data to display only English text.


  1. Select the JOIN node to the left of the Output View.

  1. Choose the Filter operator in the popup to add it to the diagram.

  2. Go to the Columns tab in the Filter Properties panel.

  3. Scroll down the list of fields.

  4. Select the LANGUAGE field to use it in the Expression.

  5. Type ='EN' including the single quotes (make sure these are single quotes and not double quotes as these are interpreted quite differently in SQL).

    The completed expression should show:

    LANGUAGE='EN'

  6. Press the Validate button to check your entry.

  1. The validation should clear any errors and return this icon to green status.

  2. Select the output view node Product Dimension (last node to the right).

Since we already filter on English, there is no need to keep the Language attribute as a key.

  1. Scroll down the Attributes list to find the attribute LANGUAGE.

  2. Select the More (…) button, go to Key, and choose Remove as Key.


Add Projection

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

  1. For the definition of the output column attributes, select the Projection node.

  2. Go to the properties panel, select the More (…) button for CREATEBY, and choose Exclude Column.

  1. Repeat the last step for the following columns:
  • MEDIUM_DESCR
  • CREATEDAT
  • LONG_DESC
  • CHANGEBY
  • CHANGEDAT

  1. For column renaming, select the More (…) button for SHORT_DESCR, and choose Change Name.

  2. Change Business and Technical Name to DESCRIPTION and click the Rename button.

Adapt Attribute Properties (Semantics)

Maintain the attribute properties by selecting the appropriate semantic type to understand the value in the column. Click here for more information about attributes and their semantic types.

  1. Select the output node Product Dimension in the canvas, and press the Edit Columns icon in the Properties panel.

  2. Apply the following changes and close the attributes editor:

  • Set Semantic Type of attribute LANGUAGE to Language.
  • Set Semantic Type of attribute DESCRIPTION (SHORT_DESC) to Text.
  • Set Text/Association of attribute PRODUCTID to DESCRIPTION.

The semantics will enhance the data model, which we build in the next lessons. For example, instead of the value of PRODUCTID, the associated text of DESCRIPTION is displayed in the analysis screen.

Preview Product Dimension Data

  1. To preview your final dimension model, select the output node Product Dimension and choose the Data Viewer operator in the popup menu.


Save and Deploy

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

  1. Select the Deploy icon in the header menu.

  2. Confirm the Business Name as Product 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 Product Dimension View

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

  1. After the successful deployment, 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.



Congratulations! You have sucessfully created a Dimension Graphical View, which joins both remote product tables with the local sentiment data table. Applied a filter and a projection to the model, before specifying the attribute semantic types. Finally, you persisted the data of the Product Dimension View.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn