Importing data into a cube

Modelers can import data into a new or existing cube.

You can create a process and a chore so that you can schedule regular updates to the data in the cube, and you can import data into control cubes.

Before you begin

You must be a member of the ADMIN group for the TM1 database to be able to import data into cubes. See Add users to user groups.

Your import data must be correctly structured and formatted to get the correct results, see Cube import file format.

Procedure

  1. There are multiple ways to begin importing data on a modeling workbench or in a book. When importing data in a book, you must be in edit mode.
    • Right-click a cube on the Data tree (either a regular data cube or a control cube), then click Import data.
    • Right-click the Cubes node on the Data tree, then click Import data.
    • Right-click a database on the Data tree, then click Import > Import data.
  2. Specify a Cube action.
    • To import data into an existing cube, select the cube from the list of available cubes.
    • To import data into a new cube, select the New cube option and enter a name for the new cube.
  3. Specify the file that contains the data you want to import.
    • To import a file that is external to your Planning Analytics database, enable the Upload file option, then drag the file onto the drop zone or click the link to navigate to the file.
      Tip: In IBM Planning Analytics Workspace Local, drag and drop requires the Planning Analytics Administration agent to be configured and running.
    • To import data from a file that is stored in File Manager on your Planning Analytics database, disable the Upload file option, then click Select file.
  4. Click Next.
  5. Confirm that the Delimiter, Quote character, and Header Rows values match what is in your import data file.
    If you change these values, click Refresh Refresh to update the data preview.
  6. Review the Preview panel to understand how data is structured in your source file.
  7. Click Next.
    The remainder of the import procedure varies depending on whether you are importing data into an existing cube or importing into a new cube.

Importing data into an existing cube

When you import data into an existing cube, you must specify how to handle existing cube data and map columns in your source data file to existing cube dimensions.

Procedure

  1. Select a Before loading data option to specify how to handle existing cube data.
    • Select Do not clear to leave existing cube data as-is.
    • Select Clear all data to reset all values in the cube to 0.
    • Select Clear data from view and then select a view. All values in the selected view are set to 0.
  2. Select a While loading data option to specify how to handle values while loading data from your source file.
    • Select Overwrite existing data to overwrite existing values in the cube. If there is more than one value in the import file for a cell, the last value is written to the cube. This option has no impact on string cells. If the cell contains text, the current value is always overwritten.
    • Select Accumulate with existing data to add the values that are imported to existing cell values. If there is more than one value in the import file for a cell, all the values are accumulated. This option has no impact on string cells. If the cell contains text, the current value is always overwritten.
  3. If necessary, update the Decimal separator and Thousands separator.
  4. You can optionally save the import configuration as a process and create a chore that you can schedule for automatic process execution.
    1. Select the Save process as option and enter a name for the new process.

      The process is created on your database and is available in the Processes node of the Data tree.

    2. Select the Schedule process as option and enter a name for the new chore.

      The chore is created on your database and is available in the Chores node of the Data tree, but the chore frequency is not set and the chore is not enabled. See Create and edit chores for more information on scheduling chores.

  5. Map the dimensions in the cube to columns in the data source.

    For each cube dimension, click Select mapping and pick the column in the source that corresponds to the dimension.

  6. Specify Data mapping.
    1. Specify the type of data contained in your source file, Numeric or String.
    2. Click Select mapping and pick the column in the source file that contains data values.
  7. Click Import.

Importing data into a new cube

When you import data into an new cube you must specify a dimension mapping format, specify how to handle data values, and map columns in your source data file to new or existing cube dimensions.

Procedure

  1. Select the Dimension mapping format that matches the structure of your source file.
    • If your source contains only leaf members for each dimension, select Leaf only.
    • If your source contains nothing more complex than a simple parent-child structure for dimensions, select Parent-Child. For example, you might have two columns in your source, Regions and Cities, where Regions is the parent, and Cities is the child.
    • If you are importing any dimensions with more than two levels, select Multi-level. An example of a multi-level dimension is one with the following columns: Product, Product type, Product category, Total product.
    Tip: Your selection should reflect the most complex dimension structure that is present in your source file. For example, if your source contains multiple levels of consolidation for some dimensions, but only leaf members for other dimensions, you should select Multi-level.
  2. Select a While loading data option to specify how to handle values while loading data from your source file.
    • Select Overwrite existing data to overwrite existing values in the cube. If there is more than one value in the import file for a cell, the last value is written to the cube. This option has no impact on string cells. If the cell contains text, the current value is always overwritten.
    • Select Accumulate with existing data to add the values that are imported to existing cell values. If there is more than one value in the import file for a cell, all the values are accumulated. This option has no impact on string cells. If the cell contains text, the current value is always overwritten.
  3. If necessary, update the Decimal separator and Thousands separator.
  4. You can optionally save the import configuration as a process and create a chore that you can schedule for automatic process execution.
    1. Select the Save process as option and enter a name for the new process.

      The process is created on your database and is available in the Processes node of the Data tree.

    2. Select the Schedule process as option and enter a name for the new chore.

      The chore is created on your database and is available in the Chores node of the Data tree, but the chore frequency is not set and the chore is not enabled. See Create and edit chores for more information on scheduling chores.

      Now you can map columns in your data source to new or existing dimensions on your database and identify the column(s) that contain data values.

  5. The default Data source mapping panel is configured for a data source with two dimensions. If your data source contains more than two dimensions, click Add dimension until the Data source mapping panel is configured to accommodate all dimensions in your source.
  6. For each dimension represented in you source, use the Mapping column to specify if the dimension maps to a New Dimension, exiting Dimension, or existing Control Dimension.
  7. If mapping to a new dimension, enter a dimension name in the Cube dimensions column. If mapping to an existing dimension, select a dimension name in the Cube dimensions column.
  8. Define mappings for all of the dimensions represented in your data source.
    • For Leaf only dimension mapping, in the Child column, select the column in your source that contains the leaf members.
    • For Parent-Child dimension mapping, select the column of data that contains the parent members in the Parent column. Then, in the Child column, select the column of data that contains the child members.
    • For Multi-level dimension mapping, complete these steps.
    1. When you specify Multi-level dimension mapping, the default Data source mapping panel is configured for a data source with two levels of consolidation, Level 1 and Level 2. If your data source contains more than two levels of consolidation, click Add level until the Data source mapping panel is configured to reflect the levels of consolidation that are present in your source.
    2. In the Leaf column, select the column of data that contains the leaf members for the dimension.
    3. In each of the Level columns, select the column in your data source that corresponds to the level of consolidation.

      For example, if your source contains the Product column at the leaf level, Product Type at the first level of consolidation, Product Category at the second level of consolidation, and Product Total at the third level of consolidation, the configuration would look like this.

      Mapping for a multi-level data source showing multiple levels of consolidation.
  9. Specify Data mapping.
    1. Specify the type of data contained in your source file, Numeric or String.
    2. Click Select mapping and pick the column in the source file that contains data values.
  10. Click Import.
    Tip: If the Import button is disabled, it means you haven't mapped all the columns in your source. Click any of the current mappings to see a list of the columns in your data source. Any column without a corresponding check mark has not been mapped.