Skip to Content
Product Information
Author's profile photo Ashok Rajashekar

New String Functions in SAC BI Calculations

Great news! We have enhanced the SAC BI Calculations by adding the new string functions in the Calculated dimensions.

Calculations or formulas created at the SAC Modeler level might not be enough for the story designers to do the ad-hoc analysis on the data.

Hence its very important for the story creators to have a flexibility to manipulate the strings and can create calculated dimensions at the story level instead of planning to do the changes at the data source level.

With the latest releases of 2020.11, 2020.12, 2020.13 for the SAC fast track customers (BI weekly release model) and for Quarterly release cycle with 2020 Q3 QRC, SAP Analytics cloud is supporting the BI calculation maturity by adding following new string functions in Calculated Dimensions.

Note: All these string functions are supported for models based on acquired or live HANA connections only.

TRIM Function:

In the Analytics world data comes from different sources and when it comes to SAC modeling the data might not be properly aligned and it might have some leading and trailing empty spaces.

When the SAC story creators consume this data and wanted to do some string manipulations, it will result into unformatted values in the charts and tables.

Hence SAC story creators can use TRIM function to remove the unwanted leading and trailing spaces.

Syntax:  TRIM ( “String”)

Ex: TRIM (“     SAP Analytics Cloud  “ ) –> “SAP Analytics Cloud”

 

 

In the below table if you want to display a short form for the Cabin_Class_Desc  ie., for Economy “E”, Business “B” and First “F” then you can use substring function to get this.

CALC_CABIN_CLASS_untrimmed =

SUBSTRING([d/”Best_Run_Travel”:Cabin_Class_Desc].[p/ID] ,0 ,1 )

But Cabin_Class_Desc column shows the result with (No Value) as it has some unwanted leading spaces.

Now you can use TRIM formula to remove the unwanted spaces by combining the Substring function.

TRIM Calc =

SUBSTRING(TRIM([d/”Best_Run_Travel”:Cabin_Class_Desc].[p/ID] ,0 ,1 ))

Note:

If you are using Live Hana, then it requires EPMMDS versions 2020.11 or above.

SPLIT Function:

SPLIT function which returns a substring from a specified string with a delimiter character to divide the string into a sequence of tokens.

Syntax: SPLIT (“Dimension”, “Delimiter”, Index)

Ex: SPLIT (“SAP-Analytics-Cloud”, “- “, 2) -> “Analytics”

 

Below example has Best Run Travel data with all different travel carriers, City pair names.

Now you can use SPLIT function to separate Source CITY and Destination CITY from the city pair names.

CAL Source City= SPLIT([d/”Best_Run_Travel”:City_Pair_Names].[p/ID] ,”-” ,1 )

Note:

If you are using Live Hana, then it requires EPMMDS versions 2020.11 or above.

CONCAT Function:

You can now use CONCAT function to combine two strings into one calculated dimension.

CONCAT (“String1”, “String2)

Ex: CONCAT (“SAP”, “ Analytics Cloud”) -> SAP Analytics Cloud

In the below example, all Travel_ Desc contains all traveler names with Last Name and last name separated by “,”.

You can user SPLIT and CONCAT function to list the Traveler names into First Name Last Name.

CAL traveler Name=

CONCAT (

SPLIT ([d/”Best_Run_Travel”:Traveler_Desc_].[p/ID], “,”,2) +” “,

SPLIT ([d/”Best_Run_Travel”:Traveler_Desc_].[p/ID], “,” ,1)

)

Note:

If you are using Live Hana, then it requires 2.00.024.0500 (EPMMDS 1.00.201815.00) or above

UPPER and LOWER Functions:

You can now use LOWERCASE and UPPERCASE functions to convert a text string to all LOWER and UPPER cases.

Ex: UPPER (Dimension)

UPPER (“sap analytics cloud”) -> SAP ANLAYTICS CLOUD

LOWER (“SAP ANALYTICS CLOUD”) –> sap analytics cloud

In the above CONCAT Function example, we were able to display the Traveler names with Last Name and First  Name. Using the same example and by adding UPPERCASE/ LOWERCASE formula, will result you the traveler names with upper case/ Lower case.

Note:

If you are using Live Hana, then it requires 2.00.037.0000  (EPMMDS 1.00.201906) or above.

REPLACE Function:

REPLACE function can be used to Replace the characters specified by location in the given text string with another text string.

Syntax:

REPLACE (String, Target, Replacement)

REPLACE (“SAP Analytics Cloud”, “Cloud”, “Platform”) –> SAP Analytics Platform

In the below example has Employee HR data with different employees, Annual salary, Manager Name and phone numbers.

In this data, all employees phone numbers are not displayed in proper format. Using REPLACE function we can update the right format (312-747-5281).

CAL Updated Phone Num =

REPLACE(REPLACE([d/”HR_Employee_Salary_orig”:Phone_Number].[p/ID],”(“,””),”) “,”-“)

Note:

If you are using Live Hana, then it requires EPMMDS versions 2020.11 or above.

FINDINDEX Function:

FINDINDEX function can be used to search for a sub string and returns its 0 based index. Start index is optional with 0 as default.

Syntax: FINDINDEX (Source:String, target:String , start:int)

Ex: FINDINDEX (“SAP Analytics Cloud”, “Cloud”, 0) –> 14

This function can be used with other string functions to find the index and can use the same to manipulate the strings.

Example usecase for FINDINDEX  is explained along with the RIGHT function below.

Note:

If you are using Live Hana, then it requires 2.00.037.0000  (EPMMDS 1.00.201906) or above.

LEFT and RIGHT Functions:

LEFT function returns the specified number of characters from the start of the string.

Syntax: LEFT (string, number)

Ex: LEFT (“SAP Analytics Cloud”, 1) –> SAP

RIGHT function returns the specified number of characters from the end of the given string.

Syntax: RIGHT (string, number)

Ex: RIGHT (“SAP Analytics Cloud”, 2) –> Analytics

 

In the below chart has different bike product Customer names and its order value.

Now you have a requirement to display the last and middle names only and excluding the first names. You can use Split Function with “ “ as delimiter but customer names has multiple spaces and will not return the complete last and middle names.

To achieve this, We can use RIGHT function with the combination of Length and Find Index.

Here, LENGTH function is to calculate the total length of the customer name

FINDINDEX is used to find the position of “ “ in the customer name.

CAL Updated Custlist =

RIGHT([d/”Final_sac1_Sales_Analysis”:Customer].[p/ID],

LENGTH([d/”Final_sac1_Sales_Analysis”:Customer].[p/ID]) –                                                                    FINDINDEX([d/”Final_sac1_Sales_Analysis”:Customer].[p/ID],” “,0))

Note:

If you are using Live Hana, then it requires 2.00.037.0000  (EPMMDS 1.00.201906) or above.

ENDSWITH Function:

ENDSWITH function returns “True” if the given string ends with the user’s mentioned substring. This function does not ignore trailing white spaces.

Syntax: ENDSWITH (String, String)

Ex:

ENDSWITH (“SAP Analytics Cloud”, “Cloud”) –> True

In the below table has different bike product Customer names and its order value.

Now the requirement is to replace the customer names ends with cycle to BIKES.

REPLACE function alone cannot be used as it will replace all customer names with cycle.

Using ENDSWITH and REPLACE function, you can replace the values ENDSWITH with Cycle with BIKES.

Note:

If you are using Live Hana, then it requires 2.00.037.0000  (EPMMDS 1.00.201906) or above.

Assigned Tags

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Abhishek Bansal
      Abhishek Bansal

      Very well written blog with practical use cases of string functions.

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Great enhancements and very clear blog.

      One question left: are these functions executed within the browser (depending on frontend power of user's laptop/mobile) or effectively pushed down to the backend (Hana) for live data source and/or acquired SAC model?

      Author's profile photo Ashok Rajashekar
      Ashok Rajashekar
      Blog Post Author

      Hi Jef,

      Thanks for your comments.

      All these functions are pushed to backend (Hana) and executed for both Acquired and Live connections. Hence for Live connections there is a dependency on the Hana Backend Version.

      Regards,

      Ashok R

      Author's profile photo shruti srivastava
      shruti srivastava

      Hi Ashok ,

      Its an informative blog. Just a query , how can I test which query is being executed when calculation are pushed to HANA ?

       

      e.g. I create a LIVE story on HANA View without any prompts . At story level , I create some calculated dimensions restricting data on certain attributes. I would like to know what is the query which gets fired everytime the report is refreshed.

      Author's profile photo Ashok Rajashekar
      Ashok Rajashekar
      Blog Post Author

      Hi Shruthi,

      Thanks for your Query. BI Calculation Queries are not exposed externally and you will not be able to test these queries. Client will send the queries in the form of Information Access (InA) Queries to the Hana.

      Regards,

      Ashok R

       

      Author's profile photo shruti srivastava
      shruti srivastava

      Thanks Ashok !

       

      So just to confirm again , In case of Live connections , on each refresh all the calculations and selections ( page filter / story filter / widget filter) are pushed down to HANA ?

      Author's profile photo Ashok Rajashekar
      Ashok Rajashekar
      Blog Post Author

      Yes Shruti. All requests are pushed to Hana and will get the response from it.

      Regards,

      Ashok R

      Author's profile photo Rene Malmberg
      Rene Malmberg

      Hi

      nice feature but this is something you would expect to be there during the start. This is a very basic feature

      When you do this on a story level – you have to do this everything for each story – every time. And there is not a copy paste function – so from the start every time!

      and you cannot use calculated dimensions in a blended scenario

      so this is nice for one off analysis – but one off analysis is not done in SAC – as the functions are too limited. So you would do this in excel or powerbi (which is free BTW)

      the power of SAC lies in the models and reuse one dataset across many stories. This road you have chosen leads you directly into PowerBI. And here you are light years behind. So remain and develop the enterprise solution with models. And stay clear of powerbi

      Author's profile photo Ashok Rajashekar
      Ashok Rajashekar
      Blog Post Author

      Hi Rene,

      Thanks for your comments and valuable suggestions. we have started working on the SAC Calculation maturity by adding all basic use cases to complex use cases which are frequently used ones.

      Copy Calculations and other important formulas are there in our road map and you can expect many more calculations coming in the future releases.

      Regards,

      Ashok R

      Author's profile photo Jef Baeyens
      Jef Baeyens

      I also hope to see that calculations can be defined centrally = only once (not within each story) and option to calculate persistent or not, on-the-fly or asynchronously auto-calculated in backend.

      If enhancements are indeed planned, then please update the roadmap so customers know what to expect in future QRC releases.

      Author's profile photo Ashok Rajashekar
      Ashok Rajashekar
      Blog Post Author

      Hi Jef,

      Thanks for your valuable suggestion. Yes indeed we are updating the Roadmap with all the features planned in the upcoming releases.

       

      Regards,

      Ashok R

      Author's profile photo Pooja Elangovan
      Pooja Elangovan

      Wow, It's a great blog.

      Author's profile photo Ashok Rajashekar
      Ashok Rajashekar
      Blog Post Author

      Thanks Pooja. you can also refer my new blog on Measure based calculated dimension to support Page/story filters.

      https://blogs.sap.com/2021/09/20/sac-bi-story-calculation-improvements-measure-based-calculated-dimension-to-support-page-story-filters

      Author's profile photo Logeshwaran Mariyappan
      Logeshwaran Mariyappan

      Hi ,

       

      if i get the dimension value in variable from the user , how to change the variable to measure  in model level calculation?

       

      eg: Fiscal year is dimension, it has the values (2021,2022,2023,2024,2025) as string, i create a variable for Fiscal year .

       

      how to change a dimension value to number ?

      thanks and regards.