Wrangling Data

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Use data wrangling options for cleaning up and formatting data

Data Wrangling

Now that you have a feel for the potential data targets such as dimensions and models, lets head towards the import process. Flat files can be imported to both public dimensions as well as models however the main source of master and transaction data for most customers will be from SAP Systems such as SAP Business Warehouse and SAP S/4HANA. When importing from SAP Systems, the imports can be scheduled. As a matter of fact, dimensions can be imported in a sequence that includes the model import.

As previously discuss in this course, data used in SAC models for stories and analytical applications may need to be modified for reporting and analysis purposes. For example, data from a source system that is imported into an SAC model may have multiple columns for employee names - first name and last name. But for stories and analytic applications, designers may need only one column that combines both first and last names.

Wrangling Functions in a Data Model

When importing data into SAC, the data wrangling options include:

  • Transformations: You can click on a column or cell and create a transformation. The options are: concatenation, split, extract, replace, change, and filter.

  • Custom Expression Editor: You can create formulas. This editor has all of the transformation options and more advanced data transformation.

Transformation Example

The figure Transformation Example is an example of using the classic wrangling interface.

Custom Expression Editor

Sometimes you may need to use a more robust editor to manipulate data. The Custom Expression Editor provides this functionality.

The figure Formula and Expression Editor Example is an example of using the Formula and Expression Editor interface.

An example of the syntax to add a prefix to a dimension is as follows:

Code snippet
CONCAT("EXT-",[COSTCTR])
Copy code

An example of syntax to convert a date field format from 20200315 to 01/15/2020:

Code snippet
CONCAT(SUBSTRING([Posting Date],4,2),CONCAT('/',CONCAT(SUBSTRING([Posting Date] ,6 ,2 ),CONCAT('/',SUBSTRING([Posting Date] ,0 ,4 ))))) 
Copy code

Combine Data from Two Sources

Another option for wrangling data includes combining data from two sources.

The figure Wrangling - Combine Data from Two Sources is an example of this concept.

Let's look at an example of data wrangling that combines data from two sources.

Watch this video to learn how to create a new model based on an Excel file.

Note
Imports from SAP systems can be scheduled from the model in the data management workspace.
Note
Quite often, monthly data is imported every day into SAC models. In cases like this, the designer can use the update options to prevent data duplication. Also, when importing from SAP systems, the import is typically filtered to the current period.

Create an Analytic Model from a File and Wrangle Data

Prior to an upcoming board meeting, the HR department handed you an Excel file to incorporate into the board presentation. You must upload the file to a story and use a series of transformations to clean up and format the data.

In this exercise, you will acquire and wrangle data from an Excel file in SAP Analytics Cloud. You will learn how a series of quick transformations can help clean up data in the application.

Task Flow

In this exercise, you will perform the following tasks:

  • Import data from a file.
  • Explain data manipulation (grid view).
  • Create quick transformations to concatenate and extract the data.
  • Create a level-based hierarchy.

Save progress to your learning plan by logging in or creating an account