Importing and Preparing Master Data for a Dimension

Objective

After completing this lesson, you will be able to import and prepare data in a dimension.

Import Master Data

Master data can be imported from SAP source systems and files, including the member IDs, descriptions, hierarchies, and properties. Data can also be imported from non-SAP systems.

The following example shows the process of importing master data into three different public dimensions.

Example of importing master data into the account dimension. Data moves from the source systems (example SAP systems, files) into the public dimensions (example account, region, and product).

Prepare Master Data

Sometimes you may need to change the master data before importing it into your dimension. The data preparation area allows you to view the data and perform any necessary transformations. For example, you may need to concatenate columns when dimension members are non-unique, such as the same city name used in multiple states. In this situation, concatenating City and State creates a single, more meaningful dimension.

Data wrangling screen for master data showing how to add a calculated column and perform quality checks

Smart Transformations

The Smart Transformations list suggested transformations to apply to the column, such as replacing the value in a cell with a suggested value. There are many transformation options, including but not limited to:

  • Trim White Space: Remove spaces, including non-printing characters, from the start and end of strings.
  • Duplicate Column: Create a copy of an existing column.
  • Concatenate: Combine two or more columns into one. An optional value can be entered to separate the column values.
  • Split: Split a text column on a chosen delimiter, starting from left to right. The number of splits can be chosen by the user.
  • Change: Change a column to uppercase, lowercase, or title case.
Smart Transformation Example

Custom Expression Editor

You can also use the Custom Expression Editor to use more complex logic for your transformations. The custom expression editor features include:

  • The ability to create formulas to perform more complicated transformations.
  • Being available for new model and dataset imports.
  • Auto-complete and in-line help while writing the expression.
Custom Expression Editor Example

Import and Prepare Data for the Stores Dimension

Business Example: You are creating a model for your team and you need to populate the stores dimension with data from an SAP Universe.

Task Flow: In this practice exercise, you will:

  • Create an import query to import master data from an SAP Universe
  • Transform the incoming data
  • Map columns to properties
  • Run the import
ExerciseStart Exercise

Lesson Summary

In summary, master data can be imported into a dimension in the following ways:

File Data SourceSAP Source System
  1. Go to Data Management.
  2. Choose Import Data→File.
  3. Select the source file.
  4. Transform the data, if needed.
  5. Map the file columns to the dimension columns.
  6. Run the import then read the log.
  7. If records fail, debug and re-import.

In order to run another import, repeat the process manually.

  1. Go to Data Management.
  2. Choose Import Data→Data Source.
  3. Select the Source System and Connection.
  4. Choose the Source System source.
  5. Select the fields to be imported.
  6. Add filters, if needed.
  7. Transform the data, if needed.
  8. Map the file columns to the dimension columns.
  9. Run the import then read the log.
  10. If records fail, debug and re-import.

In order to run another import, repeat the process manually.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn