README.md

Section 8 - Calculations and Blending Deep Dive

ℹ️Objective: Use the latest calculations available in SAP Analytics Cloud to expose additional information that was not available in your original data model. Understand the potential and workflow of blending models and creating calculations in our story.

Estimated Time: 25 mins

Exercise Description: You have access to shipping information and financial sales data that with some common dimensions, including Order Number and Store Name. You need to create various calculated dimensions, statistical aggregations, and blended visualizations to understand the effects of shipping times and impact of delivery delays on sales revenue.

Key Features:

• Create calculated dimensions with string functions to enhance your data
• Understand how to blend data models by linking on common dimensions
• Employ statistical calculations in aggregations of your data
• Integrate calculation input controls to simulate values in your visualizations

⚠️Disclaimer When completing exercises, some data values in the screenshots may not match what you see on your screen. This is because the dynamic time filters that were applied at the time the screenshots were taken is different from the current system date.

---

⚠️Does your Dashboard look like this?

1. Click the Main Menu icon

2. Click Files

3. Click Public

4. Click TechEd 2021

5. Select Section 8.0 - Calculations and Blending (Start)

6. Click Copy icon

🚩Save the file in MyFiles

7. Save the file as "ANA260_Section 8_Your Initials"

8. Click OK

9. Click MyFiles

10. Click ANA260_Section 8_Your Initials

11. Click Edit

12. Click Operations Overview

⚠️Exercise check! Does your dashboard look like this screenshot in Edit mode?

🚩When working with your data in SAP Analytics Cloud, you may find that you want to look at additional components to your data that are currently not captured by the existing measures and dimensions in the model. Luckily, story designers can extend the data models with additional calculated measures and dimensions. This enables the story creator to delve into further insights from their data. Let us start by creating a calculated dimension that divides the Store dimension in our data into Studios and Non-Studios.

13. Click Gross Margin % for Actual Chart

14. Click Designer

15. Click + Add Dimension

16. Scroll and Click + Create Calcluated Dimension...

ℹ️Welcome to Calculated Dimensions!

Calculated Dimensions are useful when you want to enrich your dimension if your model does not have the needed format.

You can choose to combine existing dimensions to create your own dimensions. There are two calculated dimensions that can be created:

Calculated Dimension: Use formulas to create new dimension members

Measure Based Dimension: Use ranges within an existing measure to determine how to group dimension members together

🚩As we want to identify the stores that are studios, we will use the regular calculated dimension.

17. Click Calculated DImension

🚩The formula field for Calculated Dimensions uses conditional logic and function formulas to create the Dimension rules. This offers the business analyst great flexibility in defining new calculated dimensions.

18. Press Ctrl + Space on the Keyboard

19. Click IF

🚩Our IF statement has three fields. The first field is used as a condition that evaluates to true or false. The second field is the Dimension value if True and the third field is the Dimension value if False.

20. Click the Condition (First Field) in the IF Formula

21. Press Ctrl + Space on the Keyboard

💡Using Ctrl + Space is a great way to learn how to use the Calculation Editor. Using this hotkey combination will always bring up all possible functions and measures/dimensions that can be typed into the according field for the ease of the user.

ℹ️Welcome to String Functions in the Calculation Editor! There are a variety of different String Functions that can be used to transform the Dimension values to a specific use case.

FINDINDEX: Search for a substring and return its 0 based index ENDSWITH: Returns True if given string ends with user given substring TRIM: Removes unwanted leading and trailing spaces REPLACE: Replace characters in a string with a specified replacement SPLIT: Returns substring from a string using a specified divider UPPERCASE/LOWERCASE: Convert a string to all Lower or all Upper case CONCAT: Combine two strings together LEFT/RIGHT: Returns the specified number of characters from the beginning or end of a string

Let us use RIGHT function to categorize Stores by if they are a Studio.

22. Scroll till RIGHT is Visible

23. Click RIGHT

🚩Here we are specifying the Dimension we are reading our string from

24. Type St in the First Input Field for RIGHT

25. Click Store

ℹ️Since Dimensions often have an ID and Description, it is important to clarify that we are looking to parse the Store name from description here.

26. Type in a Period "."

27. Press Ctrl + Space on the keyboard

28. Click Description

🚩We know we are trying to divide our Store dimension into Studios and Non-Studio. Since we are looking for "Studio" at the end of store name, we know we should filter on 6 characters using our RIGHT string function.

29. Type "6" in the Second Input Field for the RIGHT Function

🚩Let us compare the last 6 letters of our store name with Studio to group them into two Store Groups.

30. Type in ="Studio" following the RIGHT formula

31. Type in "Studio" in the TRUE Field for the IF Function

32. Type in "Non-Studio" in the FALSE Field for the IF Function

⚠️Quality Check! Does the end of your formula look like this?

🚩Format will parse your Formula and identify if there are any problems with the input parameters.

33. Click Format to Validate the Formula

🚩Great! Our formula is valid and good for use in defining a new Calculated Dimension. Let us name this Dimension and use it in our charts.

34. Name the Calculated Dimension as Store Group

35. Click OK

⚠️Quality check! Does your chart look like this after including your Calculated Dimension?

🚩By creating a Calculated Dimension, we are able extract further insights from our data. We can now see that Gross Margin % is higher for Studios than other stores. This could be of interest to us for further financial analysis and investments.

🚩Let us use our new Calculated Dimension to find out the split of Average Sales Revenue by Store Group.

36. Click Avg Sales Revenue for Actual Chart

37. Click + Add Dimension

38. Scroll and Click Store Group

39. Click Inside the Builder Panel to Collapse the Dimension Selection Drop Down Menu

⚠️Quality check! Do your charts look like this screenshot?

🚩We can now see that Avg Sales Revenue is also higher for Studio Stores. It could be a good business decision to change our contract structure with our Studio Stores!

🚩Up until this point, we have been working with visualizations built from measures and dimensions spanning a single model. Now, we can improve the value of our analysis through Blending to combine data sources. We can accomplish this by linking dimensions across models so we can compare and analyze the relationship between measures and dimensions across multiple models

ℹ️If you are missing the icon for Link Dimensions in your toolbar, click on ... under More to surface this option.

40. Click Link Dimensions

ℹ️Welcome to Link Dimensions!

Link Dimensions allow you to create blended visualizations that display data from multiple models. It also allows you to create filters that simultaneously update all visualization that include linked data regardless of the model.

By default, the ID attribute is used to match members between the linked dimensions. However, for non-hierarchy dimensions it is possible to change the description that is used for linking.

We can utilize Linked Dimensions in our data by linking across the Finance and Shipping models by connecting dimensions that are identical across our two models.

🚩Let us link the Order Number and Store dimensions between our two models. Both these dimensions are identical across our models and have independent members that we can gather insights from. By linking on Store and Order Number we can look at analyses that target Store entities or individual orders.

41. Scroll and Click Order Number

42. Scroll and Click Order Number

43. Scroll and Click Store

44. Scroll and Click Store

🚩It is important to specify the Dimension Attribute we are linking across. Order Number in our models is common on the Dimension ID whereas Store is common on the Dimension Description (the store name). Let us make sure that Order Number is linked by IDs between the models and Store is linked by Description or Store Names between the models.

45. Click Link Attribute

46. Click ID

47. Click Link Attribute

48. Click ID

49. Click Link Attribute

50. Click Description

51. Click Link Attribute

52. Click Description

53. Click Set

🚩SAP Analytics Cloud will display the model links you have created to link dimensions. We could choose to edit these links or add more linked dimensions.

54. Click Done

🚩Now that we have Linked Dimensions, we can create blended visualizations to look at correlations in our data. As a business analyst, we may be concerned about shipping delays and how it affects our company revenue. Let us look at a blended table to display information that would help this analysis

55. Scroll to the Right of the Dashboard

56. Click ANA260_Shipping_Info Table

57. Click + Add Linked Models

58. Click ANA260_ORDER_FINANCE

ℹ️We are now able to see ANA260_ORDER_FINANCE as a linkable model to this table because we have linked it to our Shipping model on Order Number and Store dimensions.

We can choose to display measures and calculations across our columns from both models to look at the relationships in the data.

In this table, we have specified Reasons for Delay as our Row dimension. We can look at how our Finance data is affected in each of these delay members.

🚩Let us create a calculation using Finance data to see how much of our product revenue falls under each of the Reasons for Delay categories.

59. Click the More Action Icon for the Account Dimension

60. Click Add Calculation

61. Click ANA260_ORDER_FINANCE

62. Rename the Calculation to % Contribution to Revenue

63. Type in "%" in Edit Formula

64. Select %GrandTotal

65. Type in "Re" in %GrandTotal Field

66. Click ["ANA260_ORDER_FINANCE":Sales_Revenue]

67. Click OK

🚩Our table has updated with this calculation, but it has also included many other measures from the Finance model. First, we need to filter what columns are included in the table.

68. Click Filter

69. Click ANA260_ORDER_FINANCE

70. Scroll and Click % Contribution to Revenue

71. Click OK

⚠️Quality Check! Does your table look like this screenshot?

🚩We can see how useful blended visualizations can be with multiple data sets. From this table, we can see that around 42% of our revenue ships on time. However, weather conditions and out of stock also have relatively high % contributions to revenue. We should probably consider inventory management improvements as over 12% of our revenue in shipments are delayed due to being Out of Stock.

🚩We can create calculations with measures from both models for visualization purposes. Let us look at the relationship between average order size from our stores and the delivery time

72. Scroll down

73. Click the Store Order Size per Store for Actual Chart

🚩Once again, our visualization has a linked model for Finance. Let us add a new measure for Store Order Size.

74. Click Add Measure

75. Click ANA260_SHIPPING_INFO

76. Click Store Order Size

77. Click Inside the Builder Panel to collapse the Measure Selection drop - down menu

⚠️Quality check! Does your chart appear like this screenshot?

🚩Note: You might have a different color chart

ℹ️Let us look at how this calculation was created. We can easily examine the formula by going into the options

78. Click More Action Icon for Store Order Size

79. Click Edit Calculation

🚩Here is the formula for Store Order Size. It is a calculation using Sales Revenue from the Finance model and a Count Aggregation from the Shipping Model for the Number of Delivered Orders. Since we are blending on Store name, our chart will understand this context for our Shipping aggregation.

80. Click OK

🚩Let us use a Top N ranking to sort our chart data since we are interested in analyzing our top performing stores rather than all our stores.

81. Click More Actions

82. Click Rank

83. Click Top N Options

84. Set Value to 10

85. Click Apply

⚠️Quality check! Does your chart look like this screenshot?

🚩We want to use this chart to drive our analysis to be able to filter on a top performing store. Let us use Linked Analysis from this chart to filter other visualizations.

86. Click More Actions

87. Click Linked Analysis

🚩We want to select which visualizations are impacted by filtering when we select a top performing store. Let us choose only selected widgets for Linked Analysis.

88. Click Only Selected Widgets

🚩Let us link our blended chart to a chart that only uses the Shipping model. Any selections on our original chart will filter to the secondary chart via Store name.

89. Scroll and Click Avg Delivery Time per Store for Actual

90. Click Apply

91. Click the First Three Entries in the Store Order Size per Store Chart

92. Click Filter

⚠️Quality check! Do your two charts look like this screenshot?

Note: You might have a different color for your first chart.

🚩It looks as if there is a relationship between larger average order size and a faster average delivery. We have filtered using Linked Analysis from our Finance model with Store Order Size to our Shipping model with Avg Delivery Time based on the linking of dimensions we have created previously.

Let us now look a scatterplot to see if Average Delivery Time is affecting our Gross Margin.

93. Scroll to the right of the dashboard

94. Click Avg Delivery Time. Gross Margin per Store for Actual Chart

🚩Scatterplots are a great way to look at the correlation between two measures in our blended data. We will be comparing the average delivery time and gross margin for each store. Let us move to the Builder panel.

95. Click Add Measure

96. Click ANA260_SHIPPING_INFO

97. Click Avg Delivery Time

98. Click Add Measure

99. Click ANA260_ORDER_FINANCE

100. Click Gross Margin

⚠️Quality check! Does your scatterplot look like this screenshot? We can see that stores with higher avg delivery time seem to also have lower gross margin

🚩Let us now test out Calculation Input Controls and simulating values. Here we have a basic financial statement of gross margin, sales revenue, and cost of goods sold calculated by the difference between sales revenue and gross margin. We want to simulate what our gross margin % on each product would be given a simulated sales revenue increase

101. Scroll to the bottom of the Dashboard

102. Click the ANA260_ORDER_FINANCE Table

🚩Let us add a calculation for simulated sales revenue.

103. Click More Action Icon for the Account Dimension

104. Click Add Calculation

105. Click Calculated Measure

106. Rename the Calculation to Simulated Sales Revenue

107. Type "Sa"

108. Click ["ANA260_ORDER_FINANCE":Sales_Revenue]

109. Type in "*" (Multiplication Sign)

🚩We are using a calculation input control in this calculation. Calculation input controls allow the viewer of the story to adjust the value in the formula through a widget on the page. This interactivity is key to our simulated financial statement

110. Click + Create New

🚩Calculation input controls can be created using values in Existing Dimensions or through values defined in a Static List. We want to create a static list of Revenue Multiplier values

111. Click Revenue Multiplier

112. Click Static List

113. Expand Values

🚩Let us define a range of possible values to multiply our simulated sales revenue by.

114. Click Select by Range

115. Input 1 as the Min Value

116. Input 2.5 as the Max Value

117. Input 0.1 as the Increment Value

118. Click OK

119. Click OK

120. Click Revenue Multiplier

121. Click OK

122. Drag our new Input Control to the left of the table (if not already on the left)

🚩Now we can test to see how our Revenue Multiplier input control can help simulate values in our table.

123. Resize the Revenue Multiplier Calculation Input Control

⚠️Quality check! Does your revenue multiplier and table look like this screenshot? Let us try simulating some values.

124. Drag the Revenue Multiplier to 1.5

⚠️Quality check! Do the values on your table look like this after changing the calculation input control? Using the calculation input control for a simulation improves the interactivity of our dashboard for the use of the story viewer.

⚠️
Quality check! Does your dashboard look like this screenshot?

🚩Please save your story by pressing Ctrl + S on your keyboard!

ℹ️You have now completed the Calculations and Blending Jump Off. In this section we have covered how to create a calculated dimension with string functions, how to link dimensions and use blending for visualizations, how to create calculations with measures across models, how to apply linked analysis between charts with different models, how to use median and quartile aggregations, and how to use calculation input controls to create simulated calculations.

Summary

You have completed the entire Time Analysis section!

You are now able to:

• Create calculated dimensions with string functions to enhance your data
• Understand how to blend data models by linking on common dimensions
• Employ statistical calculations in aggregations of your data
• Integrate calculation input controls to simulate values in your visualizations