Importing and wrangling master data into a dimension

After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Import and wrangle data in a dimension

Import master data

data sources for public dimensions

Master data can be imported from SAP source systems or files. This includes the member id’s, descriptions, hierarchies, and properties.

Data can be imported from non-SAP systems via custom connectors.

Import Master Data - Workflow

File data sourceSAP source system
Go to data managementGo to data management
Choose import data → FileChoose import data → Data source
Select the source fileSelect the source system → select the connection
Transform data if neededChoose the source system object
Map file columns to dimension columnsSelect fields to be imported
Run the import → read the logAdd filters where needed
If records fail, debug and re-importTransform data if needed
In order to run another import, repeat the process manuallyMap file columns to dimension columns
 Run the import → read the log
 If records fail, debug and re-import
 In order to run another import, schedule the import query

Wrangle master data

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

The data wangling screen allows the user to view the data and perform any necessary transformations.

Concatenating columns is sometimes needed when dimension members are non-unique. For example, the same city name is used in multiple states so the state is inserted as a prefix. In SAP accounting, if there are multiple controlling areas, then it needs to be concatenated with cost center because the same cost center id is used in multiple controlling areas.

Smart Transformations lists 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 Whitespace - 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.

Scenario: Wrangling data

Import and wrangle data for the stores dimension

Task 1:

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 perform the following tasks:

  • Create an import query to import master data from a universe
  • Transform the incoming data
  • Map columns to properties
  • Run the import

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

Login or Register