Importing and Preparing Master Data for 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 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.

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

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 Source	SAP Source System
Go to Data Management.	Go to Data Management.
Choose Import Data → File.	Choose Import Data → Data 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 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.

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