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.
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 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.
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
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 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 =
Note:
If you are using Live Hana, then it requires EPMMDS versions 2020.11 or above.
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 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 returns “True” if the given string ends with the user’s mentioned substring. This function does not ignore trailing white spaces.
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.
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 =
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.
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.
- SAP Managed Tags:
14 Comments
