Import members and attributes into a dimension in Planning Analytics Workspace

About this task

You can import members into a dimension from multiple locations on the Databases tree. Depending on the launch point, your ability to create or modify metadata might be limited.

If you click Dimensions, Import members on the Databases tree or use the Import members option on the dimension editor, there are no limitations on metadata updates. You can:
  • update an existing dimension and an existing hierarchy
  • update an existing dimension and create a new hierarchy
  • create a new dimension and a new hierarchy

If you click <dimension name>, Import members on the Databases tree, updates apply to the dimension you clicked, but you can pick the hierarchy to update or choose to create a new hierarchy.

If you click <hierarchy name>, Import members on the Databases tree, updates apply only to the hierarchy you clicked.

Procedure

  1. Use one of the options described above to start the import.
  2. As applicable on the Import members pages, specify the Dimension and Hierarchy to update.

    If you want to create a new hierarchy, select the New hierarchy option and enter a name for the new hierarchy.

  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.
  7. Select the Mapping type that you want to apply to the import.
    Leaf only
    Imports a flat list of leaf members. You can pick which column to import when you define mappings.
    Parent-Child
    Imports two columns of data with a parent-child relationship. For example, New England is the parent and Vermont is the child. Parent-Child is the default setting. You can specify the parent-child relationship in the source columns when you define mappings.
    Multi-level
    Creates more than two levels of consolidation in a dimension. You can add levels of consolidation and specify the consolidation path in the source columns when you define mappings.
  8. If you chose to update an existing hierarchy during the import, you must specify how to update the hierarchy.

    Select Recreate hierarchy to delete all members from the existing hierarchy and recreate the hierarchy with the members that are contained in the import file.

    Select Update hierarchy with new members to update the existing hierarchy by adding any unique members from the import file to the hierarchy.

  9. You can change the default Decimal separator and Thousand separator.
  10. To create a process that contains the options that you specified, select the Save as process option and name the process.
    The process is saved in the Processes node of the database.
  11. To schedule the process that you created, select the Schedule process as option and name the chore.
    A chore is created and set to run daily using the time of the initial import. The chore is saved in the Chores node of the database.
  12. Map your data source columns to the hierarchy levels, leaf member weights, and (if applicable) attributes for your hierarchy.

    Data source mapping example showing multiple levels of consolidation

    1. Click the items in the Mapping column and map to the corresponding level of consolidation in your hierarchy. If there aren't enough levels in the hierarchy to accommodate mapping from the source, click Add level to insert a new higher level of consolidation.
    2. To set the Member type, click the Mapping column and select either Numeric or String if all members have the same type, or select the column in the source file that contains the member type.
      Strings are used in pick lists and in cells where users type comments.
    3. To assign the same weight to all leaf members in the hierarchy, type the weight value, such as 1.0 in the Weight field of the Mapping column. To import weights from the source file, select the column that contains the weight values.
      To find out more about weights, see Weights.
    4. To map an existing attribute to a column of data in the source, click the appropriate attribute field on the Mapping column and select the attribute.
    5. To create a new attribute, click Add attribute +, enter the name of the attribute, and select the type: String, Number, or Alias.
      Then, click the corresponding field on the Mapping column to map a column in the source to the attribute.

      To remove an attribute that you added, hover over the attribute and click Delete.

      Removing an attribute from a data source

  13. Click Import.