Import and Prepare Fact Data for a Classic Account Model

The data preparation step is where you can you can resolve data quality issues before the mapping step, but also wrangle data and make edits such as renaming columns, create transformations, etc…

First, make sure to resolve data quality issues if there are any. After the initial data import, the Details panel gives you a summary of the characteristics of the model with general information about the imported data, including any data quality issues.

For tenants based on an SAP Data Center, issues can be listed and described under the Model Requirements section if they are related to the model itself, or under the Data Quality section when you click a particular dimension.

For tenants based on a non-SAP Data Center, the Details panel list all the dimensions and measures of the model. You can click the  icon to access more information. If ever the application has detected issues with a dimension, the exact number of issues with that particular dimension is indicated right next to its name, and clicking that number takes you straight to the Validation tab, where you can see a description of the issue.

Select a message to see the options available to resolve any identified quality issue. Use the context-sensitive editing features and the transform bar to edit data in a cell, column, or card. When selecting a column, a cell, or content in a cell, a menu will appear with options for performing transforms. This menu is two-fold:

• Choose the Quick Actions option to perform actions such as duplicate column, trim whitespace, hide columns, or delete columns or rows. The table below lists all the available actions.
• Select the (Smart Transformations) icon to list suggested transformations to apply to the column, such as replacing the value in a cell with a suggested value. You can also select Create a Transform and choose from the options listed under the transformation bar displayed above the columns. The transformation bar is used to edit and create transforms.

As you hover over a suggested transformation, the anticipated results are previewed in the grid. To apply the transformation, simply select the transform. You can manually enter your own transformation in the transformation bar; as the transform is built, a preview is provided in the affected column, cell, or content within the cell. The table below summarizes the available transformations you can apply to selected columns, cells, or content within cells.

Transformation	Description	Transform Bar Format
Delete Rows	Delete rows in the data, either by selecting individual members, or by specifying a range (not possible for text columns; only numerical and Date columns).	Provided only as a quick action
Trim Whitespace	Remove spaces, including non-printing characters, from the start and end of strings.	Provided only as a quick action
Duplicate Column	Create a copy of an existing column.	Provided only as a quick action
Delete Column	Delete a column. Use the Shift key to select and then delete multiple columns.	Provided only as a quick action
Remove duplicate rows	Remove duplicate rows when creating or adding data to a model.	Provided only as a task bar icon
Concatenate	Combine two or more columns into one. An optional value can be entered to separate the column values.	Concatenate [<Column1>], [<Column2>]… using "value"
Split	Split a text column on a chosen delimiter, starting from left to right. The number of splits can be chosen by the user.	Split [<Column>] on "delimiter" repeat "#"
Extract	Use this transform to extract a block of text specified as numbers, words, or targeted values within a column to a new column.	Extract [<what to extract]>] [<where to extract>] [<which occurrence>] [""] from [<column name>] [<include value option> ]. Options for what to extract: number Limits extracted text to one number from column cell. word Limits extracted text to a word from column cell. everything Includes all text. Options for where to extract relative to the target value: before after between Note You must specify two target values when using between. containing: Extracts a word or number containing the target. For example, if your target is ship, both ship and shipping will be extracted. equal to: Extracts the specific target value if it exists in the cell. Options for specifying occurrence: first last occurrence: Allows you to specify the position of the target from one to ten. Note Use occurrence when there are multiple instances of the target. To extract the first number from all column cells, you would specify the following: Extract before first "" from [<column name>]. To extract all text between parenthesis in column cells, you would specify: Extract everything between "(" and ")" from [<column name>].
Change	Change a column to uppercase, lowercase, or title case.	Change [<Column>] to (<UPPERCASE>/<lowercase>/<TitleCase>)
Replace	Replaces either an entire cell or content that could be found in multiple different cells. Note You can optionally select   where to add a <where> clause to the transform bar. You need to specify an associated column and value when using a <where> clause to limit the replace action in a given column.	Replace (<cell/content>) in [<Column>] matching "value" with "value" With a <where> clause: Replace (<cell/content>) in [<Column>] matching "value" with "value" where[<Column>]is "value"

A history of all transforms you implement is displayed in Transform Log. There are two levels of transformation logs recorded: transforms on the entire dataset, and transformations on the currently selected column. Hover over a specific transform in the Transform Log to highlight the impacted column. You can roll back the change by either deleting the entries in the history or using the (Undo/Redo) buttons on the toolbar. You can remove transforms in the Transform Log panel out of sequential order provided that there are no dependencies.

After importing raw data into a new or existing model, or into a story, you may need to perform some data preparation.

You can combine data from another source with your acquired data by using up to three matching columns to join the two datasets.

1. In Actions section of the toolbar, under Actions, select the  (Combine Data) icon.

   The Let's add some data! dialog is displayed.

2. Select whether you want to add data using a file, or using a data source.

   Once the new data is loaded, the Combine Data dialog is displayed. Under Combine Settings, a table representing all columns in the original data is displayed on the left, and a table listing columns from the new dataset is displayed on the right.

3. Choose the most appropriate columns in each table to combine data. Click to select, or drag columns to the field provided under Combine Column for each table.
   Note

   The columns should be the best match and should not contain duplicates. You can use up to three columns to join the two datasets.
4. Select how you want to combine the columns:
   • All primary data: maintains the non-intersecting data from the original dataset.
   • Intersecting data only: omits any data from the original dataset that does not match in the new data.

   The results for the column combination in terms of Accepted, Duplicated, Nulls, and Omitted values are provided under Combine Preview. A sample of combined data rows is displayed under Data Sample.

5. Select Combine when you are ready to combine the two tables.
   Note

   Currently, when you combine data:

   • The number of combined cells cannot exceed 30,000,000.
   • The number of combined rows cannot exceed 1,000,000.
   • The number of combined columns cannot exceed 100.
   • You cannot run more than ten combine data processes on a given dataset.
   • You cannot combine two datasets using a calculated column.
   • You cannot combine two datasets in a story.