Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

How to Convert a Standard Custom Dimension to a List Dimension

For custom dimensions, there are a number of advantages to using list dimensions over standard dimensions, including:

  • Faster system performance.
  • Support for unlimited views of the dimension, via attributes.
  • Supports version availability, both improving performance and reducing the chance for errors.

Going forward, all new custom dimensions you create will default to being a list dimension.  This article shows you how to convert previously created dimensions. NOTE: Dimensions tagged as “Use on levels” can’t be list dimensions.

For this example we will use the following standard dimension:

There are 2 key things to notice in this standard dimension:

  1. There are a number of unused items (no inuse blue dot).  Before converting to a list dimensions we should also remove unneeded items, that will improve performance.

  • If you have this situation, there is another article that shows you the steps to clean up the dimension.   Search the knowledge center for: “Import Dimension Structure Update for Bulk Delete”.
  1. At some point, and probably by mistake, someone entered data into the rollup for “Prod A”.  This is a common error often seen when a standard dimension is on a modeled sheet. This needs to be fixed before we convert to a list dimension.  There are 2 options:

  • Review your modeled sheets and find where the data is set to “Prod A” and change that to the real product it should have been.  The problem with this option is that you have to do this for every single version and is often too much work.
  • Leave “Prod A” in the dimension but once we convert to a list dimensions, set it to unavailable so it no longer shows up as a choice.  We will use this option for this example.

Everything that you can do with a standard dimension you can do with a list dimension, but some things will be a little different.  One example is how you navigate the dimension, with a standard dimension you drill down to dimension values:

With a list dimension “Prod” would display as a list of all dimension values, but you can use dimension attributes to filter the list (first select the attribute value, then the dimension values are pre filtered for you):

Below are the steps to convert the dimension to a list dimension.  You should be comfortable with using Import to update dimensions to make following these steps easier.

NOTE:  Now is the time to leverage the “Import Dimension Structure Update for Bulk Delete” article if you want to clean up unused items, once the dimension becomes a list dimension you can only manually delete unused items.

  1. Navigate to Modeling -> Model Management -> Dimensions and filter the dimensions to the dimension you wish to update (“Prod”), click the “Generate printable view” button highlighted here to generate an Excel file we will use to update the dimension.  Save the file (you will need it later).

  2. Delete all the values from the Parent column and save the file with a new name, return to Adaptive Insights.

  3. Click the “Import dimension structure” icon , make sure “Update” is selected and click “Choose File” select the file you saved in Step 2, click “Upload”.

  4. Once completed, re-filter the list of dimensions to your dimension (“Prod”).  You will see that the dimension is now a flat list of items (image below). Delete any of the old rollup values either manually or use the same steps from the “Import Dimension Structure Update for Bulk Delete” video.  
    NOTE:  This is your last chance to follow the steps to clean up any unneeded dimension values.

  5. Make the dimension a list dimension:

    1. Click on the name of your dimension (“Prod”).

    2. Check the “List dimension” box on the right side of the screen.

    3. Click the “Save” icon.
      The dimension is now a list dimension so we can add some attributes to get our original rollups back.

  6. Create the main rollup attribute:

    1. Navigate to Modeling -> Model Management -> Dimension Attributes, select your dimension from the dropdown (“Prod Attributes”).  

    2. Click the “Create new dimension attribute” button .  

    3. Name your attribute (“Prod Group”), click the “Save” icon.

    4. With the new attribute selected, click the “Create new dimension attribute value” botton .

    5. Add all your rollup values.  If it is a small list you can type them in, if it is a larger list you may want to import them (you can leverage the very first file you saved in Step 1, the Parent column has all the values).  NOTE: Attributes can be a hierarchy of values.

  7. Mapping the dimension values to the new attributes:

    1. Open the Excel files saved in Step 1.

    2. Add a new column at the end with your attribute name (“Prod Group”).

    3. Copy the values from the Parent column to the attribute column you just created

    4. Clear the values from the Parent column.

    5. Save the file with a new name.

    6. Navigate to Modeling > Model Management > Dimensions.

    7. Click the “Import dimension structure” icon , click Choose File and select the file you saved in step 7.5, click “Upload”.

    8. Spot check your dimension values and validate they have the correct attribute values on the right side of the screen.

  8. Now you can update any sheets/reports that use the dimension:

    1. Often you would add the attributes to modeled/cube sheets to serve as filters for the dimension.

    2. Dimension attributes can also be used to filter reports, or to mimic the old rollup view

Standard dimension report

List dimension report