Define the Analytic Model

Contents

• Add Fact Model
• Set Measures and Dimensions
• Define Calculated Measure
• Add Restricted Measure Variable
• Create Restricted Measures
• Define Count Distinct Measure
• Define Exception Aggregation Measure
• Add Filter Variable
• Deploy Analytic Model

In this lesson, we will create a Sales Analytic Model and prepare it for analytic consumption. We specify the measures and dimension attributes of the Sales Fact, and associated Business Partner and Product dimensions.

Based on this initial specification, we define different types of measures, like calculated or restricted business measures. Additionally, an input variable and a filter variable are created. The input values can be changed dynamically in the analytics use case.

At the conclusion of this lesson your Analytic Model will look like:

Review the Help Portal for more information at Creating an Analytic Model.

Create an Analytic Model

Add Fact Model

1. There are two options to create a new Analytic Model. Either inside the Fact view editor using the Create Analytic Model link in the properties side panel. Or from scratch, which we will perform in the following steps.

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

3. Select New Analytic Model, opening the editor.

   

4. In the Repository section, search for your Sales Fact.

5. Drag Sales Fact and drop it on the canvas.

   Note: If you did not create the fact view Sales Fact in the previous unit, choose the predefined, shared object 4OV_Sales Fact instead.

   

6. In the pop-up window, all measures, attributes and associated dimensions are marked by default. To add these to the Analytic Model, choose the Select button.

   

7. [Optional] Press the Show or hide source tree icon to hide the soure tree explorer, and then the Reset button to align the model to the enlarged canvas area.

   

8. The Analytic Model, along with its fact source and linked dimension nodes, is displayed on the canvas.

   In the properties side panel, the measures and dimensions are listed, that are exposed for analytic consumption. Here we see:

   • Fact Source Measures
   • Fact Source Dimensions
   • Dimension attributes (expand the Dimensions folder)
   • Dimension hierarchies are indicated by hierarchy symbol
   • Text Associations are indicated by ‘T’
   

9. In the properties side panel, enter Sales Analytic Model as the Analytic Model name.

   

Set Measures and Dimensions

By selecting the fact and dimension nodes on the canvas, their properties are displayed in the side panel. You can add or remove their measures, attributes, as well as dimension associations anytime. This way, modelers can carefully design what parts of the data model to expose for a given analytic use case.

In the next steps, we remove several not required measures and attributes of the fact and the dimension entities, to streamline our analytic model.

10. Select Sales Fact from the Fact Sources node on the canvas.

11. In the properties side panel,

• Unselect the measures:

  • NETAMOUNT
  • TAXAMOUNT

• Unselect the attributes:

  • CHANGEBY
  • CHANGEDAT
  • FISCVARIANT
  • FISCALYEARPERIOD
  • DELIVERYSTATUS
  • DELIVERYDATE

12. Select the PRODUCTID (Product Dimension) node on the canvas.

13. In the properties side panel, unselect the attributes:

    • TYPECODE
    • SUPPLIER_PARTNERID
    • WIDTH
    • DEPTH
    • HEIGHT
    • DIMENSIONUNIT
    • PRODUCTPICURL

14. Select the PARTNERID (Business Partner Dimension) node on the canvas.

15. In the properties side panel, unselect the attributes:

    • EMAILADDRESS
    • PHONENUMBER
    • FAXNUMBER
    • WEBADDRESS

16. In the properties side panel, select the right chevron symbol of ADDRESSID to edit the attribute.

17. In the ADDRESSID properties, select the checkbox to use the associated dimension. As result, the ADDRESSID (Address Dimension) node is inserted on the canvas.

18. Select the ADDRESSID (Address Dimension) node on the canvas and apply following settings in the properties side panel:

    • Select the dimension attribute COUNTRY to add it to the model. On the canvas, the linked dimension node is shown.
    • Select the attribute BPLocation for the geo-map functionaltiy of SAP Analytics Cloud (SAC).

19. Select the CEATEDATE (Time Dimension - Day) node on the canvas.

20. In the properties panel, only rename the Business Name CREATEDAT to OrderCreationDate.

21. Choose Fact Sources main node on the canvas.

The diagram and the properties of the Analytic Model should look like below. The dimensions belonging to the first, second and third level on the canvas are in the properties overview arranged on the same level, aligning to SAP Analytics Cloud standard.

Let’s save the analytic model before proceeding with the creation of new measures and variables.

22. Select the Save icon in the header menu and confirm the name Sales Analytic Model in the dialog popup.

    

Create Measures and Variables

Much more complex measures can be defined via Analytic Model functionalities. The following types of measures can be added:

• Calculated Measures
• Restricted Measures
• Count Distinct Measures
• Currency Conversion Measures
• Non-Cumulative Measures

Define Calculated Measure

A calculated measure references other measures (which are calculated and aggregated at first) and allows the combination of measures. Let’s add a measure to calculate the average price, based on the gross amount and quantity values.

23. In the measures section, select the Add Measure ‘+’ button and choose Calculated Measure.

24. For the new measure, please fill in the following values:

• Business Name: Avg Price
• Expression box: GROSSAMOUNT / QUANTITY

25. Validate the expression.

    

26. Select the Sales Analytic Model link to return to the main panel overview.

    

Add Restricted Measure Variable

A restricted measure variable is used in the filter condition of a restricted measure. Let’s add a variable to ask for the domestic country. The variable is assigned to the restricted measure calculations afterwards.

27. Go to Variables section in the properties panel.

28. Select the Add Variable ‘+’ button and choose Restriction Variable.

    

29. For the new variable, fill in the following values:

• Business Name: Which Domestic Country

• Dimension: COUNTRY (ADDRESSID)

• Filter Type: Single Value

• Default Value: DE

• Mandatory: [Set Flag]

  

30. Select the Sales Analytic Model link, to return to the main panel overview.

The variable shows an error as it is not assigned to a restricted measure yet, we will do that next.

Create Restricted Measures

Let’s now create restricted measures. Measures that build on existing measures but restrict them along a filter. This is typically used for comparing values by status, like comparing the value of all orders with the value of all open orders. Here we’ll define a Gross Sales measure for the choosen domestic country, to get a better insight. Optionally, define a second restricte measure, representing the international gross sales.

31. In the measures section, select the Add Measure ‘+’ button and choose Restricted Measure.

32. For the new restricted measure, enter the following properties.

• Business Name: Domestic Gross Sales

• Source Measure: GROSSAMOUNT

• Expression: COUNTRY_ADDRESSID = :WHICH_DOMESTIC_COUNTRY

  Note: The restricted measure will use the restricted measure variable WHICH_COUNTRY in the expression.

33. Validate the expression:

    Expression: COUNTRY_ADDRESSID = :WHICH_DOMESTIC_COUNTRY

    Note: If the message Dimension cannot be found appears, verify the correct dimension entry in the Dimensions (34) attribute list.

    

34. Select the Sales Analytic Model link, to return to the main overview.

35. Optionally, repeat the last step and create a second restricted measure, calculating the value of the international gross sales. Enter the following values:

• Business Name: International Gross Sales
• Source Measure: GROSSAMOUNT
• Expression: COUNTRY_ADDRESSID != :WHICH_DOMESTIC_COUNTRY

Define Count Distinct Measure

A count distinct measure counts unique occurrences of attributes. We are interested in the average spend per customer (business partner). For this purpose, we count individual partners in the current drill-down and use that to calculate their average spend amount. The count distinct measure serves as auxiliary measure for the other measure and therefore not exposed for analytic purpose.

In the measures section, select the Add Measure ‘+’ button and choose Count Distinct Measure.

36. For the new restricted measure, enter the following properties:

• Business Name: Customer Count
• Dimensions: PARTNERID (Sales Fact)
• Set checkbox for Is Auxiliary

37. Select the Sales Analytic Model link to return to the main panel overview.

38. In the measures section, select the Add Measure ‘+’ button and choose Calculated Measure.

39. For the new measure, enter the following properties:

• Business Name: Avg Spend per Business Partner
• Expression: GROSSAMOUNT / Customer_Count

40. Validate the expression.

41. Select the Sales Analytic Model link to return to the main panel overview.

Define Exception Aggregation Measure

The Analytic Model enables the creation of Exception Aggregation, expressing complex sub- selection and aggregation behaviours, known from SAP Business Warehouse.

Exception Aggregation are the exception from the rule, which is the default aggregation behaviour defined for the measure. It is even applied when the dimension is not selected in the drill-down.

Let us count the products whose gross amount is exceeding the value of 10 million, to assess how many “high value” products are part of an analytics query, like for a sales order drill-down.

42. In the measures section, select the Add Measure ‘+’ button and choose Calculated Measure.

43. For the new measure, enter following the values:

• Business Name: Products with >1 million
• Expression box: GROSSAMOUNT > 1000000

44. Validate the expression.

    

45. Collapse the expression section or scroll down to Exception Aggregation.

46. Select the following exception aggregation values:

• Type: SUM

• Dimension: PRODUCTID (Sales Fact)

  

47. Select the Sales Analytic Model link to return to the main overview.

Add Filter Variable

Finally, we create a filter variable, which specifies a year interval for the sales order records. This helps to sort out and adapt the data set for the analytics use case. By definition in the ‘Sales Fact’ view, the sales orders covers the years from 2020 to 2023 (Jan-May).

48. Select the Add Variable ‘+’ button in the Variables section.

49. Choose Filter Variable.

50. For the new variable, specify the following properties:

• Dimension: Year (OrderCreationDate)

• Filter Type: Interval

• From: 2021

• To: 2023

  

51. Select the Sales Analytic Model link to return to the main panel overview.

52. Select the Fact Sources (1) node on the canvas.

In the analytic model properties overview in the side panel, the measures, dimensions, and variables are displayed. These properties are exposed for consumption in SAP Analytics Cloud. The dimensions belonging to the first, second and third level on the canvas are in the properties overview arranged on the same level, aligning to SAP Analytics Cloud standard.

Deploy Analytic Model

Let’s save and deploy the analytic model before previewing it from an analytics perspective.

53. Select the Deploy icon in the header menu.

    

54. Confirm the name Sales Analytic Model, if asked.

55. Wait until the notification window appears that the deployment was successful.

Congratulations! You have successfully explored the modeling perspective of the Analytic Model tool, specified the measures and dimensions for the model, and created business measures, input variables, and filter variables. The draft model can be previewed to prepare it for analytic consumption in SAP Analytics Cloud (SAC).