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.

Using Guided Imports for Cube Data Imports

Planning Analytics Workspace supports Guided Imports, which let you save and reuse your cube import configuration. Guided imports preserve:

  • Source selection
  • Member and data mappings
  • Formulas
  • Cube selection or cube creation options

Creating a new cube during a guided import

When importing data into a new cube you create during the wizard, you can introduce both new and existing dimensions. After the cube is created, the import continues.

A dimension created during cube creation cannot be assigned as the Measures dimension within the same wizard session. To assign Measures, complete the cube creation and then run a subsequent data import.

For more information, see Use Guided Imports in Planning Analytics Workspace.

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 database on the Databases tree, then click Import/Export > Import data.
    • Right-click a Cubes node on the Databases tree, then click Import data.
    • Right-click a cube on the Databases tree (either a regular data cube or a control cube), then click Import/Export > 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 a Data source.

    • For a delimited text file source, select File from the menu.

      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.

    • For an ODBC source, select Database connection. Select the connection you want to use from the Database connection list, then provide authentication credentials if required. If the database supports Unicode, enable the Unicode option.

      TM1 Database 12 requires the ODBC Data Connector to use a database connection for a data source.

  4. Click Next.
  5. Preview the data source.
    • For a file source, preview the file and confirm that the correct Delimiter, Quote character, and Header records formatting options are applied. If necessary, modify any of the formatting options and then click Refresh to see the impact on the preview.
    • For an ODBC source, enter a query to extract data, then click Refresh to preview the results.
  6. 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.