Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Using Integration To Mass Update Custom Dimension Names

This article leverages an optional/additional component of the Adaptive Insights solution.

Adaptive Integration can be used to keep your dimensions in sync with a source system or database (getting that setup will require paid professional services support).  It can also be used if you need to do a mass update to your custom dimensions in Adaptive Insights using Excel as the datasource. You can only update 1 custom dimension at a time.  To use this article, you will need to know how to do a couple things in Excel that we will not detail here (trim spaces off a string and vlookups). A basic knowledge of Adaptive Integration is also required.  If you have less than 100 values to update, you will find that it is much faster to just manually update them.

  1. Login to your model (administrator login required), navigate to Model Management > Dimensions

  2. Use the drop down to filter the view from “All Dimensions” to the dimension you want to work on.

  3. Click the “Generate printable view” icon to export your current dimension to Excel.

  4. Make the following edits to the file, then save the file.

    1. Delete the top 3 rows.

    2. Scroll to the bottom of your list of values and delete the two rows containing the time stamp information.

    3. Delete all columns except for:  Dimension Value, Parent (optional, you could keep Short Name or any Attributes for list dimensions if you wish to update that information). 

    4. Delete the blank row that contained the dimension name, by now it should be row 2.

    5. Select the following based on your dimension:

      1. For non-list dimensions:  Add 2 new columns: SourceID, ParentID.  It will be easier if you add them after the Name column.

      2. For list dimensions:  Add 1 new column: SourceID. 

    6. SourceID:  This column will be used for a unique ID number for each dimension value.  Starting with the first value, populate the SourceID column by numbering the values starting with 1.

    7. ParentID:  Only used for non-list dimensions.  Specifies the new unique ID for the value that the current dimension value rolls up to.  For these values we used a vlookup (=vlookup(D1, A:B, 2, false) for example) checking the value from the Parent column getting the SourceID based on the Dimension Value(created a table out of the Name and SourceID columns, are just highlight both columns).

      1. For the Dimension Value that have no Parent, the result will be #N/A, delete the values leaving them blank.

      2. Copy and paste special values the results of the vlookup to the ParentID column.

  5. Sample file:
    For non-list dimension

    For a list dimension

    NOTE:  Even if you want to only update select dimension values you will need the full list of values to prevent issues.

  6. At this point do not make any changes to the names in this file as it must first be loaded to create the relationship between the new SourceID and the existing values.

    1. Navigate to Integration > Data Designer.

    2. Create a new Spreadsheet data source, name it.

      1. Click “Import spreadsheet” and browse for the file you created in Step 3. Click “Import”.

    3. Create a new Planning Dimension Loader, name it.

    4. Data Source Settings:

      1. Source Table:  Set to the tab in your Excel file.

      2. Planning Dimension:  Select the custom dimension you are updating.

      3. Adjust the other settings as needed.

    5.  Column Mapping:

      1. Parent Id = ParentID column (for non-list dimensions only, not applicable for list dimensions)

      2. Dimension Name

        1. Source ID Column = SourceID column

        2. Source Display Name Column = Dimension Value

      3. Map any other columns you kept for updating.

    6. Click Save, then click “Preview loader output”.  If there are no errors then your loader is ready to be run, either manually run it or create a task (recommended) and run it to create the relationship between the file and your model.

  7. Now you can mass update the data in your Excel file to the updated values, you can update Names and any other info you kept.  Once the file is updated just re run the Task/Loader created in Step 5. Once complete verify that your model reflects the updated information.


  • Was this article helpful?