Create a Sales Fact Data Model

Objective

After completing this lesson, you will be able to create a graphical view with Fact as the sematic type. Combine the sales order (item) information from the data layer foundation. Apply a filter to sort out older data records. Define fact measures and attributes, and their semantics. Associate the Sales Fact model with the previous created dimensions, as well as a time dimension. Get familiar with the Impact and Lineage Analysis tool, and view the object relationships.

Contents

  • Join Sales Tables
  • Add Filter
  • Add Projection
  • Define Sales Fact
  • Adapt Measure and Attributes Properties (Semantic)
  • Add Associations
  • Save and Deploy
  • Preview Sales Data
  • Persist Sales Fact View
  • Impact and Lineage Analysis


In this lesson, we will create a Sales Fact View. We join Sales Order entities and apply operators, like a filter to restrict the quantity of transactional records, and a projection to exclude columns. With the Graphical View Builder, we create the view and specify the measures, attributes, and dimension associations. The measure and attribute semantics include value and quantity information.

Finally, we explore the dependencies of the Fact View and related dimensions and tables, using the Impact and Lineage Analysis tool.

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 .


[Optional] Modular Approach

If you did not complete the previous dimension view lessons, you can instead use the equivalent ‘4OV_*’ objects, shared to your user space .

Join Sales Tables

  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 SalesOrders table and drag it onto the canvas (A).

    Note

    : If you did not create the table

  1. On the canvas, the SalesOrders node is linked with the output node View 1 automatically.

    The output node represents the final dataset and properties of the model.

  2. Drag SalesOrderItems from the repository onto the canvas and drop it onto the SalesOrders node to join the two tables (B).

    Note

    : If you did not create the table

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

  1. Select the new JOIN node.

    In the associated properties side panel:

    • Leave the Join Type to Inner

    • Set the Cardinality to One (0,1) for SalesOrders and Many (*) for SalesOrderItems

      Each sales order consists of one or multiple sales items

    • Note the auto-join on the column SALESORDERID from both tables. No action is needed

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



Add Filter

Filter the sales data, as we are only interested in the sales transactions of the year 2020 and later.

  1. Select the INNER JOIN node in the canvas (A).

  2. Select the Filter operator in the popup menu, adding it to the diagram.

  1. In the associated Filter Properties side panel:

    • Go to the Columns section (C)

    • Scroll down and select the CREATEDAT field, to use it in the Expression editor (D)

    • In the Expression editor append the date larger operator: >='01.01.2020'

    • The completed expression should be: CREATEDAT>='01.01.2020' (E)

    • Click the Validate button to check your entry (F)



Add Projection

Exclude columns of the joined tables that are not required in the output node for future analysis.

  1. Select the Projection node in the canvas, and view the output column attributes.

  2. In the properties panel, select the More (…) button for NOTEID, and choose Exclude Column.

  3. Repeat the last step for the following columns:

  • ITEMATPSTATUS
  • OPITEMPOS
  • LIFECYCLESTATUS
  • BILLINGSTATUS


Both the SalesOrders table and the SalesOrderItems table contain several attributes with the same name. The properties panel of the Projection node lists these attributes as either active or excluded columns. For the target model, it is necessary to include the attributes from the SalesOrderItems table, as we aim to analyze data at the order item level in our reports.

  1. Select the active GROSSAMOUNT entry in the projection properties panel, to display the lineage and the source entity.

  2. If the SalesOrders table is shown as the source table, continue with steps 16 through 20.

Rename and exclude the the GROSSAMOUNT attribute of the SalesOrders table.

  1. Select the More (…) button for GROSSAMOUNT, and choose Change Name.

  2. Change Business and Technical Name to GROSSAMOUNT_SO and press the Rename button.

  3. Select the More (…) button of the renamed GROSSAMOUNT_SO, and choose Exclude Column.

Restore the GROSSAMOUNT attribute of the SalesOrderItems table to the model.

  1. Select the excluded GROSSAMOUNT entry, which refers to the SalesOrderItems table.

  2. Select the More (…) button for GROSSAMOUNT, and choose Restore Column.

  3. Repeat the last steps - source table lineage, rename, exclude, and restore - for the attributes:

    • NETAMOUNT
    • TAXAMOUNT

    In the Projection properties panel the GROSSAMOUNT, NETAMOUNT, and TAXAMOUNT attributes should look like the following:


Define Sales Fact

  1. Select the output node View 1 on the canvas, and go to the properties side panel.

  2. Enter Sales Fact 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.

    Click here for more information.

  4. Change the Semantic Usage to Fact.

    The Semantic Usage determines how the entity will be used. Fact indicates that your entity contains measures and attributes. A fact typically has associations pointing to dimensions and is consumed by analytic models.

  1. Set Expose for Consumption to ON.

A red error appears, informing that at least one visible measure is needed.

Change amount and quantity attributes to measures.

  1. Scroll down the Attributes list until the attribute GROSSAMOUNT is visible.

  2. Select the More (…) button for GROSSAMOUNT, and select Change to Measure.

  1. Repeat the last step and change the following attributes to Measures:

    • NETAMOUNT
    • TAXAMOUNT
    • QUANTITY

  2. Go to the Measures section,

    • Change the order of the measures according to below representation, if necessary. Select an entry and drag it to the new position.

    • Keep SUM as the default aggregation type for the measures of the SalesOrderItems table. For the excluded measures of the SalesOrder table, the MIN aggregation would be the appropriated type. Other aggregation options are AVG, COUNT, MAX and NONE.



Adapt Measure and Attributes Properties (Semantic)

Specify the properties of the measures and attributes. For this, select the appropriate semantic type of a column. For example, a value, quantity, date (interval), geo or textual information, or another kind of semantic information are options.

Later these semantics are used for data processing, analytics, and data consumption. A user creating reports gets extended information, like measures with the associated unit or currency information.

Review the Help Portal for more information at Semantic Types for Attributes and Measures.

  1. Scroll to the Attributes section in the properties panel.

  2. Click the Edit Attributes pencil icon.

In the attributes edit mode; the key, business name, semantic type and column label can be updated. Extend the attributes by unit and currency semantics information.

  1. For attribute CURRENCY, select semantic type Currency Code.

    The Currency attribute column includes currency values like EURO or Dollar.

  2. For attribute QUANTITYUNIT, select semantic the type Unit of Measure.

    The Unit attribute column includes unit values like piece or kilogram.

  3. Select the Close button.

  1. Scroll up to the Measures section in the properties panel.

  2. Press the Edit Measures pencil icon.

In the measures edit mode; the business name, aggregation, semantic type and unit column can be maintained. Extend the measures by quantity and unit semantic information.

  1. For the measure GROSSAMOUNT, select semantic type Amount with Currency and unit column CURRENCY.

  2. Repeat the step for measures NETGROSSAMOUNT and TAXAMOUNT.

  3. For the measure QUANTITY, select semantic type Quantity with Unit and unit column QUANTITYUNIT.

  4. Close the measures editor.



Add Associations

An association can be created from any table or view to any other table or view at any level of the data layer. This could be either from the E/R model editor, table editor, or graphical view editor.

Define the relationships between the fact view and the previous created dimensions, as well as to a predefined time dimension.

Now link the Business Partner Dimension to the Fact.

  1. In the properties side panel, scroll down to the Associations section.

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

  3. The Select Association Target window opens.

Now link the Business Partner Dimension to the Fact Table.

  1. In the Select Association Target dialog, select BusinessPartnerDimension (Technical Name).

Note

: If you did not create the

  1. Press the Select button.

  2. Review the Mappings section (an automatic mapping is done for matching column names):

    Ensure that the PARTNERID column of Sales Fact view is joined to the PARTNERID column of Business Partner Dimension view.

  3. Return to the Associations section:

    Select the More (…) downward chevron to the left of the slash in the header area and select the Sales Fact entry.

  1. Repeat the steps to create additional associations with Product Dimension and Time Dimension - Day (SAP.TIME.VIEW_DIMENSION_DAY).

    The Time Dimension will help in the future analysis, to order the sales data by year or month for example.

    Association Target: Mapping Column - Fact: Mapping Column - Dimension:
    Product Dimension PRODUCTID PRODUCTID
    Time Dimension - Day CREATEDAT Date

    Note

    : If you did not create the

    For the mapping of Time Dimension - Day, select the CREATEDATE column of Sales Fact. Drag and drop it on the Date column of Business Partner Dimension.

  2. After creating the relationships of the fact with the dimensions, the Associations section will list three entries.



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. Select the Deploy icon in the header menu.

  2. Confirm the Business Name as Sales Fact, and Save it.

  3. A notification window appears informing on the deployment status.

  4. Ensure the view is deployed before you continue.



Preview Sales Data

View the measure and attributes values of the fact view. The attributes of associated dimensions are not displayed in the data preview.

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



Persist Sales Fact View

Optionally, persist the Sales Fact view.

If the view is complex or a large amount of data is processed, this may impact the performance of other views or dashboards built on top of it. You can improve performance by persisting the data of the view and you can schedule regular updates to keep the data fresh.

  1. After the successful deployment, go to the Data Persistence section in the properties side panel.

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

  3. Notification messages appear and informs you on the progress of the persistence run.



Impact and Lineage Analysis

The Impact and Lineage Analysis diagram helps you to understand the lineage of a selected object, or one or more of its columns. Along with its impacts on the objects that depend on it and that will be impacted by any changes that are made to it.

Now at the end of the view creation, look at the dependencies and relationships of your table and view entities.

  1. Select the Impact and Lineage Analysis operator of the output node, or click the lineage icon in the Tools header menu.

  2. In the Data Analysis diagram, we see the tables and view. This lineage is extended in our next unit when you create the Analytic Model object.

  3. Choose the Dependency Analysis tab, to see all related objects of Fact Sales including their associations to the dimension tables and views in the user space.

  1. You can select an entity to get further information, open the lineage, or open the editor for this object in a new browser tab. For example, the Product Dimension details.

  2. Select the Table symbol on the Sales Fact entity, and get an overview of the columns.

  1. Close the Impact and Lineage Analysis diagram again.

  2. Close the Graphical View Builder for the Sales Fact view.

  3. In the Data Builder your fact view, tables, and replication flow objects are listed.



Congratulations! You have successfully created a Fact Graphical View, which joins the sales order entities. Applied a filter to limit the transactional records and a projection to the model. The defined semantic types include values and quantities information. Finally, you have associated the fact with your created dimension views and the time dimension, before exploring the Impact and Lineage Analysis.


Learning

SAP FacebookSAP YoutubeSAP LinkedIn