Importing and Wrangling Master Data to a Dimension

Objectives

After completing this lesson, you will be able to:

  • Import and wrangle data in a dimension

Import Master Data

Master data can be imported from SAP source systems or 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 the Account dimension.

Wrangle Master Data

Sometimes you may need to change the master data before importing it into your dimension. The data wrangling 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.

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.

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.

Import Master Data - Summary

File Data SourceSAP Source System
Go to Data Management.Go to Data Management.
Choose Import DataFile.Choose Import DataData Source.
Select the source file.Select the Source System and Connection.
Wrangle the data if needed.Choose the Source System source.
Map the file columns to the dimension columns.Select the fields to be imported.
Run the import then read the log.Add filters if needed.
If records fail, debug and re-import.Wrangle the data if needed.
In order to run another import, repeat the process manually.Map the source columns to the dimension columns.
Run the import then read the log.
If records fail, debug and re-import.
In order to run another import, schedule the import.

Import and Wrangle 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 a universe
  • Transform the incoming data
  • Map columns to properties
  • Run the import

Log in to track your progress & complete quizzes