In order to prepare data for analysis, we want to tame them, so to speak. Data management capabilities are here key to derive the desired output. This is why, in the first release of SAP Data Warehouse Cloud, one of the main highlights of the graphical modeling environment is the Calculated Columns functionality. This SQL-based feature allows you to change an existing or model an additional column with the means of more than 150 functions already. In this article, I'll go over some of the most common functionalities.
As a background: in the past, the Line of Business user was forced to do this on the analytics layer which caused severe performance issues and prevented organizations from reusing data assets through redundant activities across the organization.
That is why SAP Data Warehouse Cloud enables you to model your desired data objects on the data management layer and share objects – heavily increasing governance and your return on data investments.
Here, we will showcase 24 of the Functionalities of the Calculated Columns to inspire you what is already possible today and how you can solve your data management challenges. In 2020, we will enhance the modeling environment with an SQL Scripting functionality which will take the possibilities to the next level. Even more important, to understand what is already possible now – door by door.
DAYS_BETWEEN – Calculating the days between two columns or a certain date and a specific column. Always helpful to know how much time you have left until the end of the quarter. e.g. DAYS_BETWEEN(Date, '2019-12-31')
RTRIM – Eliminating specified characters from the right side of a specific cell. For data cleansing and preparation, especially for external data, it is always required to reduce unwanted characters. In this example, we have nutritional data about advent calendars where the quantity column entails characters like '?' or 'e'. e.g. RTRIM(quantity, '?e')
RAND – Even though it sounds random, sometimes you need a random input for your analytics, e.g. to have some non-alphabetical ordering or to build some demo data. The RAND() function provides you with those capabilities by giving you numbers between 0 and 1 and with the following expression you can derive random numbers from it: TO_INTEGER(RAND() * 100)
ABAP_ALPHANUM – A necessary tool (not only) for success in the world of SAP and ABAP-based systems -– is the ability to add leading zeros to fields in order to perform harmonization afterward as well as smooth ordering as well (09,10,11, …). With the second element, you specify the harmonized length of the target column. e.g. ALPHANUM(code,13)
DAYNAME – Especially for holiday planning, it is always important to know on which weekdays the holidays fall every year. For analytical purposes in general, this is really helpful, e.g. to showcase the distribution of sales order entries in a week for personnel planning or to understand the promotion effectiveness between weekdays and weekends, . e.g. DAYNAME(Date)
ADD_DAYS – In order to compare data across weeks, months or years, it is quite helpful to be able to transform date fields. In this example, we add 365 days to our current date to understand on which weekday the Dec 6th (a holiday in Germany) is in 2020. e.g. DAYNAME(ADD_DAYS,365)
LOWER – Data from 3rd Party Systems and Data Lakes like S3, GCP, etc. often contain strings in different formats. Consequently, harmonization of the strings is key in order to combine data from different systems. Bringing all letters to small characters is one way to reach the next step of comparability. In this example, we have submissions of mail addresses that we can harmonize with e.g. LOWER(Recipient).
CASE / LIKE – Often you need to check cells for certain values to derive an additional information, such as a flag or ID. In this case we search for the Eenglish string "gluten" to check if the product an advent calendar is gluten-free.
WHEN allergens_tags LIKE '%en:gluten%' THEN 'NO'
ABS – Being able to derive absolute values is key for arithmetic operations. In this case it allows us to calculate Santa's the average derivation from the average annual stock value to optimize his inventory planning next year. E.g. ABS(Difference)
WIDTH_BUCKET – This function allows you to easily understand whether certain values lie within or outside of pre-defined data range(s). In this example, it helps to easily understand which item fits into which bucket to leverage this information within the story, e.g. WIDTH_BUCKET(Wish_List_Amount, 20, 200, 1)
QUARTER – For many reporting purposes, especially for Year-over-Year comparisons, having the quarter as an attribute in string form can be extremely helpful, e.g. QUARTER(Date)
REPLACE – Structural editing in data management is key, especially when working with data from federated systems where you do not have the chance to change source data values. Replacing certain values to ensure consistent reporting on mass data, e.g. REPLACE(pnns_groups_1,'unknown', 'Other')
CONCAT – Fragmented information assets are a core problem for consolidated reporting. In order to bring data from different columns together, concatenation is one vehicle to make that happen. e.g. CASE WHEN brands =" THEN CONCAT(product_name, CONCAT('_','OTHER')) ELSE CONCAT(product_name, CONCAT('_',brands)) END
LAST_DAY – Often, it is required for reports to understand where you are in the month and how long you have until the end of the month. That is why this function helps you to dynamically derive this date from your desired input in order to for example calculate the remaining days to close deals for your analytical view, e.g. LAST_DAY(Date)
NOW – Equally important to the end of the month is the information of the current timestamp. That's why this calculated function helps you to directly have this information ready for consumption in your view or further calculations, such as a too-late flag, e.g. CASE WHEN Date < NOW() THEN 'YES' ELSE 'NO' END.
LOCATE – Everyone who has ever written a wish list, knows the situation when they do not want to state the product specifically but provide only a brand prioritization. The locate function of SAP Data Warehouse Cloud helps Santa Claus to understand with which prioritization 'Adidas' comes to execute a promotion that he has agreed on with the brand, e.g. Locate(Brands_Prio,'Adidas').
UPPER – Earlier, we introduced the LOWER function, so obviously the contrary function is required, as well: transforming a string into upper characters only to harmonize columns, e.g. UPPER(HEADER).
UMINUS – Especially for reporting / visualization purposes, the ability to invert values is key and with this function is easily achievable, e.g. UMINUS(Units_Wished).
LEFT – Sometimes less is more. With this function, you are able to reduce a string by a predefined number (or calculation that leads to this number) for example to derive the product ID and remove the unneessary 'bike' string, e.g. LEFT(Product,4).
LENGTH – Even though sometimes less is more, it is crucial to understand how much or long something is. That is why this function helps you to derive the length of the string in a column – for example to understand the length of a list (knowing that especially here quality is superior to quantity), e.g. LENGTH(Text).
ISOWEEK – Becoming better week by week is not only a perfect idiom for a motivational calendar but it is also a common reporting use case to showcase a measure a longside the weeks of a year, e.g. ISOWEEK(Date).
GREATEST – For quantitative comparisons, the ability to retrieve the maximum value of multiple columns is key. In this example, it helps to act upon the different text lengths of each year, e.g.
WHEN Greatest(LENGTH_2017,LENGTH_2018,Length_2019) = Length_2019
NEXT_DAY – Especially for daily comparisons, the ability to calculate the date for the next day for a subsequent join is important., e.g. NEXT_DAY(Date).
SOUNDEX – With this function, you can harmonize columns by their sound – which also helps to harmonize fields from different voice-to-text processing applications, e.g. SOUNDEX(Voice_Input_1).
Want to know more? Check out our SAP Data Warehouse Cloud Onboarding Guide to learn more!