Preparing and Transforming Transaction Data

As we have already seen, data used in SAP Analytics Cloud models may need to be modified for reporting and analysis purposes. For example, data from a source system that is imported into a model may have multiple columns for employee names - First Name and Last Name. But for stories and analytic applications, designers may want only one column that combines both First and Last names.

In the Prepare Data step, you can do quality checks, perform simple and complex transformations, and change the job settings.

1. Make simple data changes with smart transformations and quick actions directly from the data table.
2. Use the overview pane to perform a quality check on the data.
3. Perform complex transactions with the custom expressions editor.
4. Change the job settings.

Just as when importing data into a dimension, when importing into a new model, simple transformations are available via the smart transformation and quick actions. If more complex transformations are needed, the custom expression editor is available.

In the Before data table, you can see the source data contains year and week as separate columns. SAP Analytics Cloud requires weeks 1-9 in two-digit format, using 08 instead of 8, as seen below.

The solution is to:

1. use the Custom Expression editor to add a 0 if the week is less than 10.
2. use the Transformation Bar to concatenate the Year and Week values with no separator into the required YYYYWK format.

These results are shown in the After data table.

During the import process, the Job Settings can be maintained:

• Import method: These selections are important when importing data again for the same data region.
• Reverse sign by account type: If the source system stores revenue, equity, and liabilities with positive values, then they can be reversed based on the account type property in the account dimension.
• Update local dimensions with new members: If the model contains a private dimension, then its members can be updated with new members during the model import.
• Conditional validation: This is used to validate members to make sure they are not parent members in a hierarchy.

In the Map to Target step, the system will map fields automatically where possible. If the source and target fields names are different, then the mapping can be completed using drag-and-drop. When importing from an SAP source system, the mapping can be edited if needed.

1. Source fields.
2. Unmapped target fields. You can:
   • Map to source fields.
   • Set to a constant.
   • Set to unassigned (#).
3. Mapped fields.

The Map Properties step can be used to map properties of both public and private dimensions, if there are any. The only way to import into a private dimension is during the model import. Also, when importing into an analytic model, the Map Properties step indicates that all records have been assigned to the public actual version.

Before you complete the import, you must review any issues. In the Review Import step, if there are no issues the Dimension Restrictions area will be blank. If you have any issues, choose the Prepare Dataset workspace, correct the issues, re-map if necessary, and return to the Review Import workspace.

For example, in the following image you can see the issue that requires review. The U00 Stores dimension does not include e-Fashion in the store name, so the imported data does not match the model dimension's data.

After the import process is complete, there are several important options in the Data Management workspace:

• Edit allows you to change the import query.
• Schedule is where you can set up the import query to run periodically (e.g. daily).
• Select import method is used to change the import method (for example, from Update to Clean and Replace).
• Refresh is used to run the import again.

In an account-based model you can combine data from two sources. For example, you may have a data source that contains a dimension for Entity and another one with the same Entity and a Profit Center dimension. You can combine both data sources into one model so that all the members for both Entity dimensions use the same Profit Center.

Watch this video to learn how to prepare data by combining data from two sources, in this case Microsoft Excel.

When data must be imported regularly into SAP Analytics Cloud models, you can use import method options to prevent data duplication. In addition, when importing from SAP systems, the import is typically filtered to the current period.

A choice of four import methods are available:

1. Update:Updates the existing data and adds new entries to the target model. The scope of this update is based on a combination of all dimensions in the target model.

   For a more refined scope, use either the Clean and replace selected version data or Clean and replace subset of data update options.

2. Append: Leaves the existing data unchanged and adds only new entries to the target model. When using append there are two options:
   1. Reject duplicated records in the model.
   2. Aggregate duplicated rows in the model.
3. Clean and replace selected version data: Deletes the existing data and adds new entries to the target model only for the versions that you specify in the import. You can choose to use either the existing version or specify a new version under Version.

   For example, if you specify to import data for the Actual version, only the data in the Actual version is cleaned and replaced. Other versions, for example Planning, are not affected.

4. Clean and replace subset of data: Replaces existing data and adds new entries to the target model for a defined subset of the data based on a scope of selected versions using either the Existing Version or New Version buttons. You can also limit the scope to specific dimensions. To define a scope based on a combination of dimensions, select + Add Scope and use the Select a dimension field to specify a dimension.

   When a Date dimension is defined in the scope, the time range in the source data (calculated based on the target model's granularity) combined with other dimensions in the scope will determine what existing data is cleaned and replaced.

   If, for example, Date and Region dimensions are defined as part of a scope, only entries that fall within the time range and match Region from the source data will be replaced in the target model. Existing data that does not match the scope will be kept unchanged.

Examine the example below, be sure to review the incoming data and see how the resulting data changes, depending on the import method selected.

Since the incoming data file only includes actual data, there will be no impact on the existing budget data.

1. Update method:
   • Benefits increase to 1600.
   • A new record for Direct Labor 160 is inserted.
   • All other records remain the same.
2. Append method:
   • Benefits increase to 3100.
   • A new record for Direct Labor 160 is inserted.
   • All other records remain the same.
3. Replace/clear method:
   • Benefits increase to 1600.
   • A new record for Direct Labor 160 is inserted.
   • Actual records not in the incoming file are removed.
   • All other records remain the same.