Importing and Wrangling Transactional Data in a Model

Just as with public dimensions, data can be imported into models from files as well as source systems. However, note that the Date and Version dimension data is system-generated; therefore, there is no import option.

Master data is normally imported before transaction data is imported into the model. When transaction data is imported, it is stored in the model’s fact table. A model can receive data from one or several source systems depending on the scenario.

The work flow for importing transaction data into the model is similar to importing master data into a dimension. One key difference is the Set Up Import activity. The Set Up Import process is made up of four work steps.

The following figure shows the workflow for importing transaction data into the model.

The workflows below describe the import process for a new model not a classic model.

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. Perform an easy wrangling 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.

Note

All target fields must either be mapped or use a default value or Unassigned. Some source fields may be unmapped if they are not needed.

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 filter values in 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.

Quite often, data must be imported regularly into SAP Analytics Cloud 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.

When data is imported multiple times for the same data region, the update method is used to control the data update.

A choice of four import methods are available:

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

1. Since the incoming data file only includes actual data, there will be no impact on the existing budget data.
2. Update method:
   • Benefits increase to 1600.
   • A new record for Direct Labor 160 is inserted.
   • All other records remain the same.
3. Append method:
   • Benefits increase to 3100.
   • A new record for Direct Labor 160 is inserted.
   • All other records remain the same.
4. 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.