Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Using Integration To Mass Update Level Names

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

Adaptive Integration can be used to keep your Levels 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 Levels in Adaptive Insights using Excel as the datasource. 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 Levels 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 > Levels

  2. Click the “Generate printable view” icon to export your current Levels to Excel.

  3. 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 Levels and delete the two rows containing the time stamp information.

    3. Delete all columns except for:  Name, Rolls up to (optional, you could keep Short Name if you wish to update that information). 

    4. Delete the row that contains your Top Level (whatever it might be called), by now it should be row 2.

    5. The names in the Name column by default have blank spaces padded to the front of them that need to be removed.  The basic steps are:

      1. Add a new column.

      2. Add for formula to the new column to trim the spaces from Name column (=trim(a2) for example).  

      3. Copy the formula to all rows.

      4. Copy and Paste Special Values from the calculation results over the values in the Name column.

      5. Remove the extra column.

    6. Add 2 new columns:  SourceID, ParentID. It will be easier if you add them after the Name column.

    7. SourceID:  This column will be used for a unique ID number for each Level.  Starting with the first Level, populate the SourceID column by numbering the Levels starting with 1.

    8. ParentID:  Specifies the new unique ID for the Level that the current Levels rolls up to.  For these values we used a vlookup (=vlookup(D1, Acct_namedrange, 2, false) for example) checking the value from the Rolls up to column getting the SourceID based on the Name (created a table out of the Name and SourceID columns).

      1. For the Levels that roll up to your Top Level 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.

  4. Sample file:

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

  5. 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 levels.

    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 Level Loader, name it.

    4. Data Source Settings:

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

      2. Adjust the other settings as needed.

    5.  Column Mapping:

      1. Parent Id = ParentID column

      2. Level

        1. Source ID Column = Source ID

        2. Source Display name Column = Name

      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.

  6. 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?