Importing and wrangling transactional data in a model

Data can be imported into public dimensions and models from files as well as source systems. However, note that date and version dimension data is system-provided so there is no import option for date.

A dimension can receive data from one source system or several depending on the scenario. The same is true for models.

Master data is normally imported before transaction data. When transaction data is imported, it is stored in the model’s fact table.

We will show you the steps required to import transaction data, however, please note that the workflows describe the import process for new models not classic models.

Workflow to import transaction data from a file:

1. Go to data management
2. Choose import data → File
3. Select the source file
4. Set up import:
   • Prepare data
   • Map to target
   • Map properties
   • Review import
5. Run the import and read the log
6. If records fail, debug and re-import
7. In order to run another import, repeat the process manually

Workflow to import transaction data from an SAP source system:

1. Go to data management
2. Choose import data → Data source
3. Select the source system → select the connection
4. Choose the source system object
5. Select fields to be imported
6. Add filters where needed
7. Set up import:
   • Prepare data
   • Map to target
   • Map properties
   • Review import
8. Run the import and read the log
9. If records fail, debug and re-import
10. In order to run another import, repeat the process manually or schedule it

Note

The worklow for importing transaction data is similar to master data. One key difference is the steps for setting up the import, which are shown in both of the examples above.

When you import data into a model from an SAP source system, you will need to select the fields to import. In addition, you can set a filter for the import, for example a filter for year and period.

1. Available data: The source system fields are shown on the left side of the dialog. You can scroll through the list or search for specific fields that are required.
2. Selected data: The fields that have been selected to be imported are displayed here.
3. Filters: Filter the data where needed. In the example, week 53 is not required so the filter is set to Less than or equal to 52.

After creating the import query, the filter can be changed but the field selection can not be changed.

When the import is created, it appears in the data management workspace of the model with a hyperlink: Set Up Import.

As previously discussed in this course, data used in SAP Analytics Cloud 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 a 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.

In the prepare data step, you can perform easy wrangling, do quality checks, perform 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.

Note

For source system imports, the four steps in the import workflow can be edited after it is run.

When importing into a new type model, simple transformations are available via the smart transformation and quick actions. If more complex transformations are needed, the custom expression editor is available.

The custom expression editor features includes:

• Ability to create formulas to perform more complicated transformations
• Available for new model and dataset imports
• Auto-complete
• In-line help

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

The solution is to use the custom expression editor to add a 0 if the week is less than 10 and concatenate the Year and Week values with no separator into the required YYYYWK format. This is 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 propertiesstep can be used to map properties of 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 Property 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.

If there are no issues, then the dimensions restriction will be blank, however, if a member is being imported that doesn't already exist, then it appears in the dimensions restrictions.

There are two options to resolve the issue:

1. Go to Prepare Data and correct the data, mapping again if required. Then, return to Review Import and validate.
2. In another session, run the dimension import again.

In the Review Import step, the system simulates the import and generates messages if something goes wrong. For example, in the example below, you can see the values that require review. Someone forgot to parse out e-Fashion as it is showing up in the review as a member that does not exist. Once the issue is resolved, the review import step can be re-validated.

Another issue that may arise is when member IDs are in the import source but not in the target dimension. This should not happen very often since master data is updated before importing into the model. The solution would be to add the new dimension member ID and re-validate the review import step (and then run if OK).

After the import process is complete, there are several important options in the data management workspace:

• Edit this allows 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 switch the import method (e.g. from update to clean and replace).
• Refresh is used to run the import again

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

When importing into a classic model, you can merge data from multiple sources. The prerequisite is that you need some common members in the source data.

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

Quite often, monthly data is imported every day 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

When some incoming records are okay but some have issues, the good records are imported and the bad records are identified in the rejection summary file.

Rejected records: In this example, 1116 rows were rejected. The rejection summary shows that the dimension member doesn't exist. You would have to update the dimension and refresh the data into the model.

Note

If there is an issue with a scheduled import, the schedule status log can be access via Connections in the navigation bar.