Import and Prepare Fact Data for a Model with Measures

Once your new model is created, you might want to import fact data to your model.

The import process can be run once on demand, or scheduled to be executed on regular basis. In this section, we describe how to import data acquired from an external data source into the fact table of your new model type. Depending on the platform on which the model is based, the procedure can slightly differ. Use the links below to skip to the relevant procedure based on your configuration.

Import Your Fact Data in 5 Steps (Non-SAP Data Center Platform)

You are working on a non-SAP Data Center platform that uses a Cloud Foundry environment and want to import data acquired from an external data source into the fact table of your new model type.

Fact data can be imported from the Data Management workspace using a 5 steps process.
Note
If you are working from a Neo environment (SAP Data Center), you need to follow this procedure Import Your Fact Data in 4 Steps (SAP Data Center Only).
Step 1: Gather Your Data
Import your data using the menu Import Data from. You can import data from a file or a data source.
Note
To import data from a data source you need to select an existing connection or create a new one. For more information, see Import Data to Your Model.
Restriction
Note that the following data sources are not supported by the new model type:

  • SAP BPC

  • SAP Concur
  • SAP ERP
  • SAP Fieldglass
  • Workforce Analytics
  • Dow Jones

Create a new import data job.

  1. Select Start of the navigation path Import Data from Next navigation step FileEnd of the navigation path or Start of the navigation path Import Data from Next navigation step Data SourceEnd of the navigation path depending where your data is stored.

  2. Select your file, if your data is stored in a local file or connect to your data source.
  3. Select the data you want to import .

    The import job is added to the Import Jobs list for this model.

  4. Click Set up import to launch the import job.

You can save and exit the process and come back later clicking .

Step 2: Prepare Your Data

It's the step where you can wrangle your data: Rename your columns, create transformations, etc., and resolve quality issues in your data.

Selecting Dimension Types

When importing data to a new model, columns containing text are typically identified as Dimensions, and numeric data may be identified as Measures. If the assigned dimension type isn't appropriate, you can change it to another type; for example, to a Date or Organization dimension. For details, see Learn About Dimensions and Measures.

Location Dimensions

If your data contains columns of latitude and longitude coordinate data, or state and county data (for the USA only), you can create a Location dimension. Select the (Geo Enrichment) icon under Transformations, and choose either By Area Name or By Coordinates.

When creating locations using coordinate data, location dimensions can be based on a combination of latitude and longitude pairs. You also have the option to specify a column as an identifier for the location dimension, and another column as the location description.

For more information, see Creating a Model with Coordinate or Area Data for Geospatial Analysis.

Note
As you prepare the data, if you modify any of the coordinate data used to create the location dimension, the location dimension values will automatically reflect your modifications.

Maximum Number of Dimension Members

When importing data into a new model, there is an upper limit applied to the number of unique members in a dimension, to maintain good performance. See System Requirements and Technical Prerequisites.

However, for non-planning-enabled models only, you can import dimensions with more than the maximum number of members, with these restrictions:

  • In the data integration view, these dimensions cannot have any dimension attributes added to them, such as description or property.
  • Once the dimensions are imported into the Modeler, they will have only one ID column, and will be read-only.
  • The dimensions can't be used as exception aggregation dimensions or required dimensions.
  • The dimensions can't be referenced in formulas.

Calculated Columns

While you're preparing data in the data integration view, you can create a calculated column based on input from another column and the application of a formula expression.

Select the icon from the menu toolbar. Use the Create Calculated Column interactive dialog to build up your calculated column. Enter a Name for the calculated column. In the Edit Formula space start building the formula for your column. You can select an entry from Formula Functions as a starting point or enter [ to view all the available columns. Press control + space to view all the available functions and columns. Select Preview to view the results of the formula.
Note
The data displayed in the preview will be a random 10 line sample.
To add the calculated column to the model, select OK.
Note
Once the calculated column is added to the data, you can always modify the formula that it's based on by selecting Edit Formula in Designer.

For a listing of supported functions, see Supported Functions for Calculated Columns.

Dimensions Attributes

Dimension attributes are information that is not suitable to be standalone dimensions.

For example, if you have a Customers column, and a Phone Numbers column, you could set Phone Numbers to be an attribute of the Customers dimension.

You can use dimension attributes to create charts, filters, calculations, input controls, linked analyses, and tables (with “import data” models only).

These are the available dimension attributes:

  • Description: The column can be used for descriptive labels of the dimension members when the member IDs (the unique identifiers for the dimension members) are technical and not easily understandable.

    For example, if your imported data contains a pair of related columns Product_Description and Product_ID, containing data descriptions and data identifiers, you can set Product_Description to be the Description attribute for the Product_ID dimension. Note that the Product_ID column would then need to contain unique identifiers for the dimension members.

    ID

    Description

    X123

    Computer and equipment

    Y456

    Books
  • Property: The column represents information that is related to the dimension; for example, phone numbers.
  • Parent-Child Hierarchy (Parent): The column is the parent of the parent/child hierarchy pair.

    For example, if your imported data contains the two columns Country and City, you can set the Country column to be the parent of the Country-City hierarchy.

    The hierarchy column is a free-format text attribute where you can enter the ID value of the parent member. By maintaining parent-child relationships in this way, you can build up a data hierarchy that is used when viewing the data to accumulate high-level values that can be analyzed at lower levels of detail.

    You can also create level-based hierarchies when your data is organized into levels, such as Product Category, Product Group, and Product. When the data is displayed in a story, hierarchies can be expanded or collapsed. In the toolbar, select (Level Based Hierarchy). For more information about hierarchies, see Learn About Hierarchies.

  • Currency: If you set a column to be an Organization dimension, the Currency attribute is available. The Organization dimension offers an organizational analysis of the account data, based, for example, on geographic entities. You can add the Currency attribute to provide currency information for the geographic entities.

Data Quality Considerations

During data import, anomalies in your data can prevent the data from being imported properly, or prevent the model from being created. If any of the following issues are found in your data, those data cells are highlighted, and messages in the Details panel explain the issues. You'll need to resolve these issues before the data can be fully imported or the model can be created.

  • In account dimensions only, dimension member IDs cannot contain these restricted characters , ; : ' [ ] =.
  • Numeric data cells in measures cannot contain non-numeric characters, and scientific notation is not supported.
  • When importing data to an existing model, all dimensions of the existing model need to be mapped.
  • When importing data to an existing model, cells in a column that is mapped to an existing dimension must match the existing dimension members. Unmatched values will result in those rows being omitted from the model.
  • For stories, if any member IDs are empty, you can type values in those cells, or select Delete empty rows in the Details panel to remove those rows.
  • When creating a new model, if member IDs are empty, they are automatically filled with the “#” value if you select the Fill applicable empty ID cells with the "#" value option. Otherwise, those rows are omitted from the model.
  • In dimensions and properties, a single member ID cannot correspond to different Descriptions in multiple rows (but a single Description can correspond to multiple member IDs).

    For example, if member IDs are employee numbers, and Descriptions are employee names, it's allowable to have more than one employee with the same name, but not allowable to have more than one employee with the same member ID.

  • In a Date dimension column, cell values must match the format specified in the Details panel. The following date formats are supported:
    • dd-mm-yy
    • dd.mm.yy
    • dd/mm/yy
    • dd-mm-yyyy
    • dd.mm.yyyy
    • dd/mm/yyyy
    • dd-mmm-yyyy
    • dd-mmmm-yyyy
    • mm-dd-yy
    • mm.dd.yy
    • mm/dd/yy
    • mm-dd-yyyy
    • mm.dd.yyyy
    • mm/dd/yyyy
    • mm.yyyy
    • mmm yyyy
    • yy-mm-dd
    • yy.mm.dd
    • yy/mm/dd
    • yyyq
    • yyyy
    • yyyymm
    • yyyy-mm
    • yyyy.mm
    • yyyy/mm
    • yyyymmdd
    • yyyy-mm-dd
    • yyyy-mm/dd
    • yyyy.mm.dd
    • yyyy/mm-dd
    • yyyy/mm/dd
    • yyyy.mmm
    • yyyyqq

    Examples:

    • mmm: JAN/Jan/jan
    • mmmm: JANUARY/January/january
    • q: 1/2/3/4
    • qq: 01/02/03/04
  • Latitude and longitude columns, from which location dimensions are created, must contain values within the valid latitude and longitude ranges.
  • For planning-enabled models, in a hierarchy measure, non-leaf-node members are not allowed.
    Note
    For analytic (non-planning-enabled) models only, non-leaf node members are allowed, but be aware of the effects of this behavior. Example: in an organizational chart that includes employee salaries, the Manager has her individual salary, and her staff members have their own salaries as well. In a visualization, do you expect the Manager’s data point to reflect her individual salary, or the sum of her staff members' salaries?
  • Nodes in a hierarchy cannot be circular. For example, this hierarchy arrangement is not allowed: USA > California > San Francisco > California.

Mapping Considerations

  • At least one measure must be defined for the model.

    If the source data doesn't contain a column that can be used directly as a measure, you can create a measure column based on a count of an existing column that contains 100% unique values. The generated measure column will contain the value 1 for all rows, representing the count of the unique values.

    If you proceed to create the model without having created a measure column, a measure column will be generated automatically, based on the first column that contains 100% unique values.

    To create the measure column yourself, select a column with 100% unique values, and then select Create Count in the Details panel.

    If there isn't an existing column that contains 100% unique values, a measure column will be generated anyway when the model is created, containing the value 0 for all rows.

  • There can be only one of each of these dimension types:
    • Account
    • Organization
    • Version

Viewing the Model Details

During the initial data import, the data type of each column is inferred, as well as dimension type in the data integration view. In the right hand Details side panel, you will have a summary of the characteristics of the model:
  • Model level: shows general information about the imported data, including any data-quality issues including mapping issues, and model-specific information.
  • Column level: allows you to map columns to existing dimensions in the model, displays histograms for the currently selected column (rectangles indicating frequency of data items in successive numerical intervals), lists the attribute and dimension types that you can apply to the column, and displays any data-quality issues represented as quality bars under Data Quality. The green section indicates correct values, while those in red indicate incorrect values. Those in gray are empty.
When you select a column in (Grid View) numerical or textual histograms are displayed under Data Distribution.
  • Text histograms are horizontal, and the values are clustered by count. The number of clusters can be adjusted by dragging the slider shown above the histogram. When a cluster contains more than one value, the displayed count is the average count for the cluster. The count is prefaced by a tilde symbol (~) if there are multiple different occurrences. Expand the cluster for a more detailed view of the values in the cluster along with individual counts. Use the search tool to look up specific column values, and press Enter to initiate the search. When you select a value in the histogram, the corresponding column is sorted and the value is highlighted in the grid.
    Note
    The displayed histogram is determined by the data type of the column. A column with numbers could still be considered as text if the data type is set to text.
  • Numerical histograms are vertical, and represent the range of values along the x-axis. Hover over any bar to show the count, minimum, and maximum values for the data in the bar. The number of bars can also be adjusted by using the slider above the histogram. Toggling the Show Outliers box includes or removes outliers from the histogram.
    Note
    Below the histogram, there's a box and whisker plot to visualize the histogram's distribution of values.

You use level-based hierarchies when your data is organized into levels, such as Product Category, Product Group, and Product. When the data is displayed in a story, hierarchies can be expanded or collapsed. In the toolbar, select (Level Based Hierarchy). For more information, see Learn About Hierarchies.

Select whether you want to create a Planning or analytic Model using the Planning Capabilities toggle. Finally, specify whether you want empty ID cells to be filled with automatically generated values. If you clear this check box, rows with empty ID cells will be omitted.

Note
Data that includes a date dimension needs to be imported using the calendar year for the initial model creation. Once you enable the fiscal year setting, then you can later import and map data stored in fiscal years.

Converting Date Values into Calendar Date

If your model uses fiscal period for the date dimension, and the imported source data is in fiscal format already, you can use the Custom Expression to convert the date values to calendar date before mapping to the date dimension. If incoming data is in calendar date format, the column can be mapped to the date dimension of the model as is regardless of whether fiscal period is enabled for the date dimension or not. It is because the date dimension of a model stores date values in calendar date format internally even if the fiscal year option is turned on.
Example
You want to achieve the date conversion by shifting the month number. In this example, the original data in the column fiscalDate is shifted by ten months which matches the fiscal period setting of the date dimension to generate a new column called convertedDate:
Step 3: Map Imported Data to Your Model

At this step you need to map your new imported data with your current model's data. The application tries to do the mapping for you but it can happen that it could not map all data. It's up to you to do the mapping manually.

Data that could not be matched appears on the left side panel under Source Columns. Simply drag the source columns that need to be manually mapped and drop them on the right Unmapped ones. Or use the Quick map menu to do so.

Note
All dimensions must be mapped. For measures at least one measure needs to be mapped.

You can also set default values to dimensions:

If there is no source column suitable for the target dimension, you can also set a fixed value for the dimension. The value must be an existing dimension member, including the "#" default member.

Importing Data with Flexible Date Dimensions

Importing data to models with measures requires extra attention when dealing with date dimensions. It’s important that all the date settings are set up correctly before moving on to the next step.

With flexible date dimensions, depending on whether you want your source data to match your model target, you might need to do a bit of data preparation. That’d be the case for example if your source data has a traditional calendar month granularity while the target date dimension has a week granularity. However, you can also decide to keep the granularity differences between the source and target data. The things you want might want to consider when importing data from date dimensions are the fiscal periods, weeks, and additional periods.

Date dimensions must be mapped manually. Using the Review Settings button, you can check the mapping settings in one consolidated, three-fold dialog. The Mapping Summary gives the general mapping settings, while the Fiscal Settings and Week Settings tabs respectively display info about the fiscal and week settings.

When the granularities of the source and target dimension don’t match, a toast message lets you know about the conversion. As an example, let’s say the source column has dates with Month granularity while the target date dimension is configured to have Year granularity. The month values from the source dimension will be processed as their corresponding years and a toast message will alert you of this.

You can fine tune the mapping in the Mapping Summary tab, and edit both the data type and the conversion format of the source date dimension. The formats available depend on both the granularity and data type you select. You can customize the source dimension and set the data type to either Date, String, or Integer.

If you leave the data type set to Date, you can change the date format during the data preparation process. If you set the data type to String, you can choose from formats allowed by the target date dimension, especially if the target dimension has fiscal period settings and week granularity. Lastly, if you set the data type to Integer, your values are assumed to be dates formatted without separators.

If the target date dimension has fiscal settings, you can also automatically replicate the fiscal settings to the source date dimension using the Apply fiscal settings to column values option.
Note
  • If you want to enable the Apply fiscal settings to column values option, you must select the String data type. Also, using that option automatically switches the data type to String.
  • You can’t edit the fiscal settings or week settings of the source date dimension.

If you can’t find the format you’re looking for, try switching the data type. You can also click Can’t find a date format? if your need further explanation.

Note
To go back to the previous step, click Start of the navigation path Map to Target Next navigation step Data PreparationEnd of the navigation path.
Step 4: Review Your Import
At this step, you need to validate your import by reviewing any issues found and that prevent a correct import of data:
Issue with Solution Examples
Mapping You need to go back to the Map to Target step and correct the issue.

A data type is invalid and data cannot be matched

All the dimensions are not mapped
Dimensions You need to go back to the model itself to solve issues with dimensions. A dimension member is invalid
Data quality You need to go back to the Data Preperation step to solve this type of issue. An invalid format for a date or a number
All issues must be solved to make sure all data is imported into your model. If you decide to create the model ignoring some of the detected errors, either entire rows or invalid cells will be omitted from the new model.
Note
If there are too many errors in a single report, it's not possible to display all errors in one shot. Therefore, it might happen that you need to re-validate your import several times until you get no more errors.
Step 5: Run Your Import

Once the 4 previous steps are completed and all issues have been solved, you can import data to your model by clicking Run Import.

Define your import settings:

Import Data Options
Options What it does
Update The target model’s measure values for the dimension member combinations specified by the source data are updated by the corresponding measure values in the source data. If the particular dimension member combination has no measure values in the model prior the import, new value is inserted.
Append The target model’s measure values for the dimension member combinations specified by the source data are added by the corresponding measure values in the source data (summed together). If the particular dimension member combination has no measure values in the model prior the import, new value is inserted.
Clean and replace selected version data Deletes the existing data and adds new entries to the target model, only for the versions that you specify in the import. You can choose to use either the existing version or specify a new version under Version. If you specify to import data for the "actual" version, only the data in the "actual" version is cleaned and replaced. Other versions, for example "planning", are not affected.
Clean and replace subset of data

Replaces existing data and adds new entries to the target model for a defined subset of the data based on a scope of selected versions using either the Existing Version or New Version buttons. You can also limit the scope to specific dimensions. To define a scope based on a combination of dimensions select + Add Scope and use the Select a dimension field to specify a dimension.

When a Date dimension is defined in the scope, the time range in the source data (determined by the minimum time value and maximum time value of the source data, as well as the granularity of the date dimension) combined with other dimensions in the scope, will determine what existing data is cleaned and replaced.

If for example, Date and Region dimensions are defined as part of a scope, only entries that fall within the time range and match Region from the source data will be replaced in the target model. Existing data that does not match the scope will be kept as is. Other dimensions that are not part of the scope will be cleaned and replaced regardless of whether the dimension members are in the source data or not.
Schedule an Import Data Job
Schedule a data import job if you want to refresh data against the original data source. You can import data from multiple queries and data sources into a model, and each of these imports can be separately scheduled.
  1. Select the import job your want to schedule:
    • Select one import job
    • Select several import jobs: In this case, you set a group name for the job and you can order the import jobs. You need to choose one of the group processing option:
      Option What's happen?
      Stop if any query fails If any of the import jobs fails, the group processing stops. You can then cancel the remaining jobs, or try to fix the cause of the failure, and later resume execution of the grouping from the same point where execution stopped.
      Skip any failed query If any of the import jobs fails, the remaining jobs are still processed.
      Note

      A grouping can include jobs from public dimensions as well as the model. Running the grouping refreshes the public dimensions and model together. You can ungroup your import at anytime clicking .

  2. Define the frequency for the scheduling.
    • None: Select this option when you want to update the data manually.
    • Once: The import is performed only once, at a preselected time.
    • Recurring: The import is executed according to a recurrence pattern.

    You can update the schedule at anytime clicking .

  3. Define or Update your import settings.

Import Your Fact Data in 4 Steps (SAP Data Center Only)

You are working on SAP Data Center (Neo environment) and want to import data acquired from an external data source into the fact table of your new model type.

Fact data can be imported from the Data Management workspace using a 4 steps process.

Step 1: Gather Your Data
You import your data using the menu Import Data from. You can import data from a file or a data source.
Note
To import data from a data source you need to select an existing connection or create a new one. For more information, see Import Data to Your Model.

  1. Select Start of the navigation path Import Data from Next navigation step FileEnd of the navigation path or Start of the navigation path Import Data from Next navigation step Data SourceEnd of the navigation path depending where your data is stored.

  2. Select your file, if your data is stored in a local file or connect to your data source.
  3. Select the data you want to import .

    The import job is added under Draft Sources.

    .
Note
The import process only import fact data, it does not insert/update dimension members at the same time.
Step 2: Prepare Your Data

Click on your draft source to edit your data. It's the step where you can wrangle your data: Rename your columns, create transformations, etc, and resolve quality issues in your data.

Selecting Dimension Types

When importing data to a new model, columns containing text are typically identified as Dimensions, and numeric data may be identified as Measures. If the assigned dimension type isn't appropriate, you can change it to another type; for example, to a Date or Organization dimension. For details, see Learn About Dimensions and Measures.

Location Dimensions

If your data contains columns of latitude and longitude coordinate data, or state and county data (for the USA only), you can create a Location dimension. Select the (Geo Enrichment) icon under Transformations, and choose either By Area Name or By Coordinates.

When creating locations using coordinate data, location dimensions can be based on a combination of latitude and longitude pairs. You also have the option to specify a column as an identifier for the location dimension, and another column as the location description.

For more information, see Creating a Model with Coordinate or Area Data for Geospatial Analysis.

Note
As you prepare the data, if you modify any of the coordinate data used to create the location dimension, the location dimension values will automatically reflect your modifications.

Maximum Number of Dimension Members

When importing data into a new model, there is an upper limit applied to the number of unique members in a dimension, to maintain good performance. See System Requirements and Technical Prerequisites.

However, for non-planning-enabled models only, you can import dimensions with more than the maximum number of members, with these restrictions:

  • In the data integration view, these dimensions cannot have any dimension attributes added to them, such as description or property.
  • Once the dimensions are imported into the Modeler, they will have only one ID column, and will be read-only.
  • The dimensions can't be used as exception aggregation dimensions or required dimensions.
  • The dimensions can't be referenced in formulas.

Calculated Columns

While you're preparing data in the data integration view, you can create a calculated column based on input from another column and the application of a formula expression.

Select the icon from the menu toolbar. Use the Create Calculated Column interactive dialog to build up your calculated column. Enter a Name for the calculated column. In the Edit Formula space start building the formula for your column. You can select an entry from Formula Functions as a starting point or enter [ to view all the available columns. Press control + space to view all the available functions and columns. Select Preview to view the results of the formula.
Note
The data displayed in the preview will be a random 10 line sample.
To add the calculated column to the model, select OK.
Note
Once the calculated column is added to the data, you can always modify the formula that it's based on by selecting Edit Formula in Designer.

For a listing of supported functions, see Supported Functions for Calculated Columns.

Dimensions Attributes

Dimension attributes are information that is not suitable to be standalone dimensions.

For example, if you have a Customers column, and a Phone Numbers column, you could set Phone Numbers to be an attribute of the Customers dimension.

You can use dimension attributes to create charts, filters, calculations, input controls, linked analyses, and tables (with “import data” models only).

These are the available dimension attributes:

  • Description: The column can be used for descriptive labels of the dimension members when the member IDs (the unique identifiers for the dimension members) are technical and not easily understandable.

    For example, if your imported data contains a pair of related columns Product_Description and Product_ID, containing data descriptions and data identifiers, you can set Product_Description to be the Description attribute for the Product_ID dimension. Note that the Product_ID column would then need to contain unique identifiers for the dimension members.

    ID

    Description

    X123

    Computer and equipment

    Y456

    Books
  • Property: The column represents information that is related to the dimension; for example, phone numbers.
  • Parent-Child Hierarchy (Parent): The column is the parent of the parent/child hierarchy pair.

    For example, if your imported data contains the two columns Country and City, you can set the Country column to be the parent of the Country-City hierarchy.

    The hierarchy column is a free-format text attribute where you can enter the ID value of the parent member. By maintaining parent-child relationships in this way, you can build up a data hierarchy that is used when viewing the data to accumulate high-level values that can be analyzed at lower levels of detail.

    You can also create level-based hierarchies when your data is organized into levels, such as Product Category, Product Group, and Product. When the data is displayed in a story, hierarchies can be expanded or collapsed. In the toolbar, select (Level Based Hierarchy). For more information about hierarchies, see Learn About Hierarchies.

  • Currency: If you set a column to be an Organization dimension, the Currency attribute is available. The Organization dimension offers an organizational analysis of the account data, based, for example, on geographic entities. You can add the Currency attribute to provide currency information for the geographic entities.

Data Quality Considerations

During data import, anomalies in your data can prevent the data from being imported properly, or prevent the model from being created. If any of the following issues are found in your data, those data cells are highlighted, and messages in the Details panel explain the issues. You'll need to resolve these issues before the data can be fully imported or the model can be created.

  • In account dimensions only, dimension member IDs cannot contain these restricted characters , ; : ' [ ] =.
  • Numeric data cells in measures cannot contain non-numeric characters, and scientific notation is not supported.
  • When importing data to an existing model, all dimensions of the existing model need to be mapped.
  • When importing data to an existing model, cells in a column that is mapped to an existing dimension must match the existing dimension members. Unmatched values will result in those rows being omitted from the model.
  • For stories, if any member IDs are empty, you can type values in those cells, or select Delete empty rows in the Details panel to remove those rows.
  • When creating a new model, if member IDs are empty, they are automatically filled with the “#” value if you select the Fill applicable empty ID cells with the "#" value option. Otherwise, those rows are omitted from the model.
  • In dimensions and properties, a single member ID cannot correspond to different Descriptions in multiple rows (but a single Description can correspond to multiple member IDs).

    For example, if member IDs are employee numbers, and Descriptions are employee names, it's allowable to have more than one employee with the same name, but not allowable to have more than one employee with the same member ID.

  • In a Date dimension column, cell values must match the format specified in the Details panel. The following date formats are supported:
    • dd-mm-yy
    • dd.mm.yy
    • dd/mm/yy
    • dd-mm-yyyy
    • dd.mm.yyyy
    • dd/mm/yyyy
    • dd-mmm-yyyy
    • dd-mmmm-yyyy
    • mm-dd-yy
    • mm.dd.yy
    • mm/dd/yy
    • mm-dd-yyyy
    • mm.dd.yyyy
    • mm/dd/yyyy
    • mm.yyyy
    • mmm yyyy
    • yy-mm-dd
    • yy.mm.dd
    • yy/mm/dd
    • yyyq
    • yyyy
    • yyyymm
    • yyyy-mm
    • yyyy.mm
    • yyyy/mm
    • yyyymmdd
    • yyyy-mm-dd
    • yyyy-mm/dd
    • yyyy.mm.dd
    • yyyy/mm-dd
    • yyyy/mm/dd
    • yyyy.mmm
    • yyyyqq

    Examples:

    • mmm: JAN/Jan/jan
    • mmmm: JANUARY/January/january
    • q: 1/2/3/4
    • qq: 01/02/03/04
  • Latitude and longitude columns, from which location dimensions are created, must contain values within the valid latitude and longitude ranges.
  • For planning-enabled models, in a hierarchy measure, non-leaf-node members are not allowed.
    Note
    For analytic (non-planning-enabled) models only, non-leaf node members are allowed, but be aware of the effects of this behavior. Example: in an organizational chart that includes employee salaries, the Manager has her individual salary, and her staff members have their own salaries as well. In a visualization, do you expect the Manager’s data point to reflect her individual salary, or the sum of her staff members' salaries?
  • Nodes in a hierarchy cannot be circular. For example, this hierarchy arrangement is not allowed: USA > California > San Francisco > California.

Mapping Considerations

  • At least one measure must be defined for the model.

    If the source data doesn't contain a column that can be used directly as a measure, you can create a measure column based on a count of an existing column that contains 100% unique values. The generated measure column will contain the value 1 for all rows, representing the count of the unique values.

    If you proceed to create the model without having created a measure column, a measure column will be generated automatically, based on the first column that contains 100% unique values.

    To create the measure column yourself, select a column with 100% unique values, and then select Create Count in the Details panel.

    If there isn't an existing column that contains 100% unique values, a measure column will be generated anyway when the model is created, containing the value 0 for all rows.

  • There can be only one of each of these dimension types:
    • Account
    • Organization
    • Version

Viewing the Model Details

During the initial data import, the data type of each column is inferred, as well as dimension type in the data integration view. In the right hand Details side panel, you will have a summary of the characteristics of the model:
  • Model level: shows general information about the imported data, including any data-quality issues including mapping issues, and model-specific information.
  • Column level: allows you to map columns to existing dimensions in the model, displays histograms for the currently selected column (rectangles indicating frequency of data items in successive numerical intervals), lists the attribute and dimension types that you can apply to the column, and displays any data-quality issues represented as quality bars under Data Quality. The green section indicates correct values, while those in red indicate incorrect values. Those in gray are empty.
When you select a column in (Grid View) numerical or textual histograms are displayed under Data Distribution.
  • Text histograms are horizontal, and the values are clustered by count. The number of clusters can be adjusted by dragging the slider shown above the histogram. When a cluster contains more than one value, the displayed count is the average count for the cluster. The count is prefaced by a tilde symbol (~) if there are multiple different occurrences. Expand the cluster for a more detailed view of the values in the cluster along with individual counts. Use the search tool to look up specific column values, and press Enter to initiate the search. When you select a value in the histogram, the corresponding column is sorted and the value is highlighted in the grid.
    Note
    The displayed histogram is determined by the data type of the column. A column with numbers could still be considered as text if the data type is set to text.
  • Numerical histograms are vertical, and represent the range of values along the x-axis. Hover over any bar to show the count, minimum, and maximum values for the data in the bar. The number of bars can also be adjusted by using the slider above the histogram. Toggling the Show Outliers box includes or removes outliers from the histogram.
    Note
    Below the histogram, there's a box and whisker plot to visualize the histogram's distribution of values.

You use level-based hierarchies when your data is organized into levels, such as Product Category, Product Group, and Product. When the data is displayed in a story, hierarchies can be expanded or collapsed. In the toolbar, select (Level Based Hierarchy). For more information, see Learn About Hierarchies.

Select whether you want to create a Planning or analytic Model using the Planning Capabilities toggle. Finally, specify whether you want empty ID cells to be filled with automatically generated values. If you clear this check box, rows with empty ID cells will be omitted.

Note
Data that includes a date dimension needs to be imported using the calendar year for the initial model creation. Once you enable the fiscal year setting, then you can later import and map data stored in fiscal years.

Viewing the Card Details

The Card View is diplayed after you import data into an existing model. By default, cards representing dimensions and measures are displayed. Double-click a card to display attribute cards under the selected card.

Cards display as either mapped or unmapped entities. A card represents mapped data if it is shaded solid and has defined borders.

A card represents unmapped data if it doesn't have a border and appears transparent.

A red dot in the top right corner of the card indicates there are mapping errors that need to be addressed. A blue dot indicates that new values have been added from the import to the existing data.

Cards display sample values and icons for specific data types. The table below summarizes the available data typology:
Icon

Data Type
Generic dimension
Date dimension
Account dimension
Measure
Version dimension
Organization dimension

When you select a card, the Details panel is populated with information specific to your selection – the number of rows, unique values, data types, and corresponding histograms. A pie chart is provided under Mapping Quality showing the proportion of Accepted versus Rejected values.

To the left of the card view, a panel containing the imported data columns is displayed. Use any of the following sort options:
  • Original Order: reflects the original order in the imported data source.
  • A-Z: alphabetical order based on column headings.
  • Z-A: reverse alphabetical order based on column headings.
  • Mapped First: mapped columns listed alphabetically before unmapped columns.
  • Unmapped First: unmapped columns listed alphabetically before mapped columns.

Resolve Quality Issues in Your Data

  1. You should resolve any items that are listed in the Details panel under either the Data Quality or Model Requirements when creating a new model, or Mapping Quality.
  2. 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. There are two parts to this menu:
    • 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 Start of the navigation path Next navigation step whereEnd of the navigation path 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"
  3. 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.
Step 3: Map Imported Data to Your Model
At this step you need to map your new imported data with your current model's data.
Caution
All measures need to be mapped!

You can quickly see thanks to the horizontal diagram, if there are remaining columns to map in dimensions or measures:

  1. Determine which unmapped imported columns can be associated with specific transparent borderless cards in the card view.
  2. Drag and drop an unmapped imported column on the associated card.
    Note
    If you need to undo a mapping, hover over the card and select the (Cancel) button.
  3. Map the remaining imported columns.
    Note
    The final import cannot be started until all dimensions have been mapped to a corresponding card. You also need to map at least one imported measure. If there are fewer cards than imported columns, any extra columns will be ignored.
    Note
    Check Fiscal Year in Details to map imported fiscal period data into a date dimension in a model enabled to support fiscal year. Review the format listed under Fiscal Year Format and change it if required.
  4. To map an unmapped location dimension, select the corresponding card and from Start of the navigation pathDetails Next navigation step  LocationEnd of the navigation path choose Map LocationsGeo by Area Name if your locations are based on area data, or the to display either the Map Latitude/Longitude dialog if your locations are based on coordinates. For coordinates, use the Latitude and Longitude fields to select corresponding columns. If your locations are based on area, select a column corresponding to Region and another for Sub-Region 1.
  5. Under Mapping Options, you can use the option Fill applicable empty ID cells with the "#" value if necessary: When creating a new model, if member IDs are empty, they are automatically filled with the “#” value if you select this option. Otherwise, those rows are omitted from the model.
  6. Select the Import Method to determine how existing data is handled.
    Note
    These options affect measures and dimensions. To include both measures and dimensions, see Update and Schedule Models.
    Update Updates the existing data and adds new entries to the target model. The scope of this update is based on a combination of all dimensions in the target model. For a more refined scope, use either the Clean and replace selected version data or Clean and replace subset of data update options.
    Append Keeps the existing data as is and adds new entries to the target model.
    Note
    When importing data into a planning model using the Append option, you can choose between aggregating duplicated rows, or rejecting them.
    Clean and replace selected version data Deletes the existing data and adds new entries to the target model, only for the versions that you specify in the import. You can choose to use either the existing version or specify a new version under Version. If you specify to import data for the "actual" version, only the data in the "actual" version is cleaned and replaced. Other versions, for example "planning", are not affected.
    Clean and replace subset of data

    Replaces existing data and adds new entries to the target model for a defined subset of the data based on a scope of selected versions using either the Existing Version or New Version buttons. You can also limit the scope to specific dimensions. To define a scope based on a combination of dimensions, select + Add Scope and use the Select a dimension field to specify a dimension.

    When a Date dimension is defined in the scope, the time range in the source data (calculated based on the target model's granularity) combined with other dimensions in the scope will determine what existing data is cleaned and replaced.
    Note
    Blank dates (allowed in non-Planning models) are not part of the time range: They are matched directly in order to clean and replace data with blank dates in addition to data within the time range.
    If for example, Date and Region dimensions are defined as part of a scope, only entries that fall within the time range and match Region from the source data will be replaced in the target model. Existing data that does not match the scope will be kept as is.
  7. Choose which Category you want to use for the imported data.

Resolve Data Quality Issues

You should resolve any items that are listed in the Details panel under either the Data Quality or Model Requirements when creating a new model, or Mapping Quality if you are adding new data to an existing model.

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.

Note
Data that includes a date dimension needs to be imported using the calendar year for the initial model creation. Once you enable the fiscal year setting, then you can later import and map data stored in fiscal years.
Step 4: Run Your Import
Once you've finished preparing your data and have resolved all data or model quality issues, you're ready to create the new model or finish mapping the data you're adding to the model or story.
Note
When embedding a model in a story, there are no Create Model or Finish Mapping options. Once you switch from Data to Story or Data Exploration view, the model is automatically embedded in the story.
  1. a. If you're working with a dataset sample, choose Validate Data to apply your transforms across the entire dataset and check for data quality errors.
    Note
    Any newly detected errors will be retrieved in the sample you're working on, and appear under the Quality Issues or Model Requirements areas in the Details panel. You should resolve these errors before proceeding.
  2. b. Choose either Create Model to import the entire dataset and create your new model, or if you're adding data to a model or story, choose Finish Mapping.
    Note
    If you choose to create the model without resolving detected errors, you'll be asked if you wish to continue. Depending on the types of errors, either entire rows or invalid cells will be omitted from the new model.
    Note
    If any rows in the dataset were rejected, you can select Download Rejected Rows to save the rejected rows as a .csv file. You can then examine this file to see which data was rejected. You could fix the data in the .csv file and then import the .csv file into the model using the Import Data workflow, or fix the data in the source system.
    Note
    Once you have saved the model, you can view the data mapping and transform history by selecting (View Mapping and Transforms). The Mapping Summary shows which columns in the data source were mapped to which attributes (for example, dimension properties or geo enrichment mapping) for the mapping targets (the dimensions and measures in the model). For an import job for a dimension, that dimension is the only mapping target. The Action Log shows the transforms, version mapping, and any renaming of source columns that were done during the data import. You can select a row in the mapping summary to show only the actions that apply to that mapped column.
Note
You can schedule new import in your data. For more information, see Update and Schedule Models.