Importing and wrangling transactional data in a model

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

After completing this lesson, you will be able to:

  • Explain data transformation functions in an import model
  • Import data into a model

Import transaction data

Importing transactional data from files and sources systems

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.

Data flows from source systems into dimensions and a model. The model provides data to a story.

Data import workflows

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.

Steps for importing transaction data from files

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

Steps to importing transaction data from SAP source systems

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.

Edit SAP Universe Query dialog with Available Data, Selected Data and Filters showing. The filter is set to Less than or equal to 52 weeks.

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

Wrangle transaction data

Wrangle data

Prepare the data

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.

Prepare data

  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.
The Prepare Data step with highlights on the data table for quick actions, the overview pane, the custom expressions editor icon, and change job settings icon.
Note

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

Custom expression editor

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.

At the top, a data table with Year and Week in separate columns and August shown at 8. Below it, concatenate formula putting the data into Year_Wk format.

Job settings

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
Job setting options showing Import Settings and Validation Settings sections.

Map to target

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.

Map to target

  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 to Target screen. On the left are source columns, in the middle are unmapped target columns, on the right are mapped target columns

Map properties

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.

Review Import

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.

The Review Import screen showing the Dimension Restrictions tab. A drop down showing a list of values that are considered issues is shown. Someone forgot to parse e-Fashion.

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).

Import query options

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
Data management workspace is open. Icons on top right of screen are highlighted. Edit, Schedule, and Select import method. The more options has been selected in the import job to show the Refresh option.

Combine data from two sources

Combine data from two sources

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.

Update methods

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:

Import MethodDescription
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.

AppendWhen using append there are two options:
  1. Reject duplicated records in the model.
  2. Aggregate duplicated rows in the model.
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.

Clean and replace subset of dataReplaces 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 as is.

Comparison: Import methods

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

A data table containing examples of the data that has already been imported, the data to be imported and three versions of the data depending on if you choose Update, Append or Clean and replace.
  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

Debug transaction data

Debug transaction data issues

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.

The model's data management screen with a rejection summary. The issues are shown in the rejection summary with how to fix the issue.
Note
If there is an issue with a scheduled import, the schedule status log can be access via Connections in the navigation bar.

Import data into an analytic model

Task 1:

Business example

You need to acquire and wrangle weekly data from a universe into an analytic new model.

Task flow

In this practice exercise, you will perform the following tasks:

  • Import data from a universe
  • Wrangle data by concatenating the month and year data for the date dimension

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

Login or Register