Importing Data into Public Dimensions

You can import master data from an external data source into a public dimension.

Context

If you update model data by importing data into a model using the “Update dimension with new values” option, public dimensions are not updated. Follow this procedure to import data into public dimensions. Dimensions and their attributes can be imported:

  • Descriptions
  • Properties
  • Parent-child hierarchies

You can use this procedure to import data into public account dimensions as well. The following account dimension properties are supported at this time:

  • ID
  • Description
  • Account Type
  • Hierarchy
  • Unit Type
  • Measure Unit
  • Scale
  • Decimal Places
  • Rate Type
Note

Unit Type and Measure Unit both correspond to the Units & Currencies column in the Modeler, for account dimensions.

The Unit Type value should be either Currency or Unit. When the value is Currency, the Measure Unit column is blank. When the value is Unit, the Measure Unit value can be a unit of measure or a packaging unit, such as Bottles or Pieces.

Importing Data Access Control Information

You can import data access control (DAC) information into a public dimension, to respect the access control that you've already defined in your on-premise system.

The DAC properties that can be imported are:

  • Read
  • Write
  • Data Locking Owner
  • Person Responsible

The values in the DAC column need to be formatted as follows:

  • Names separated by semicolons
  • All upper-case text

When you finish the column mapping and click Finish Mapping, a job will be submitted and shown in the Data Management tab. This job will start to run automatically after a few seconds, so you don't need to (and shouldn't) execute the job directly. Also, while the job is running, you can navigate away from the Data Management page; the job will continue to run in the background. When the job completes, a completion status will be displayed.

Note
  • This feature is available for generic and organization dimensions, but not account dimensions.
  • Be sure that the user and team names to be imported are valid, because the information isn't validated during the import process.
  • You can import DAC information for up to 30 names per row of data. Both user and team names are supported, but team names need to be prefixed with the keyword “TEAM:”. For example: TEAM:ABCTEAM.
Note

The following procedure describes how to import data from a file into a public dimension. If you import data from other supported data sources, refer to the following topics for data-source-specific details:

Procedure

  1. Go to the Public Dimensions list by selecting Start of the navigation path (Main Menu) Next navigation step  Browse Next navigation step  DimensionsEnd of the navigation path.
  2. Select the check box beside the public dimension that you want to import data to.
  3. Select (Import Data).
  4. Choose the data source that you want to import data from; for example, File.
  5. In the Import Data From File dialog, choose whether you want to import data from a file on your local system, or from a file server.
    If you don't see the option to import data from a file server, see Allowing Data Import and Model Export with a File Server.
  6. If you're importing from a file server, choose a file server connection, or select Create New Connection.

    If you create a new file server connection, specify the path to the folder where the data files are located. For example: C:\folder1\folder2 or \\servername\volume\path or /mnt/abc.

  7. Choose the file you want to import.
  8. If you are importing from a local Excel workbook containing multiple sheets, select the Sheet you want to import.
    If you are importing from an Excel file on a file server, the first sheet is automatically imported.
  9. Specify whether you want to use the first row of the data as column headers.
  10. If you're importing a .csv file, select which delimiter is used in the file.
  11. If the dimension specified in the Target Dimension field isn't correct, choose the correct one from the list.
  12. Select Import to begin the initial import of the source data.

    After the import completes, the data integration view is displayed, where you can complete the mapping of your new data to the public dimension.

    Note
    If you import a large-volume dataset, you'll be informed that a data sample will be displayed rather than the entire dataset. Choose OK to continue.
  13. In the Dimension Mapping section of the Details panel, map the new data columns to your dimension's properties and attributes.
  14. If there are any remaining issues shown in the Mapping Requirements section, resolve them.
  15. If you want to omit validation for specific hierarchies, to allow non-leaf members to contain fact data, click Select hierarchies in the Conditional Validation section of the Details panel, and then select the hierarchies that you want to omit from validation.
    For details about storing data in non-leaf members, see Entering Values with Multiple Hierarchies.
  16. If any cells in the grid appear with red highlighting, those cells have data quality issues, and those rows will be omitted from the model if you don't resolve the issues. Select a highlighted cell to see information about the issue in the Details panel.

    When you select a column or cell, a menu appears, with options for performing transforms. There are two parts to this menu:

    • Choose the Quick Actions option to perform actions such as deleting rows that contain the selected value. For details, see Preparing Data.
    • Select the (Smart Transformations) icon to list suggested transformations to apply. You can also select Create a Transform to customize a transform in the transformation bar.
  17. Select Finish Mapping.
    Caution
    A dimension data import job never deletes existing members, but only adds additional ones. For example: You have a dimension with members A, B and C. You trigger a data import job that imports members B, C and D. You'd expect that the dimension would then have members B, C and D, but in fact the dimension has now members A, B, C and D.