Editing dimensions using a spreadsheet

You can edit dimension values with a spreadsheet instead of using the web page user interface. The dimension data that you can download as an Excel file includes all of the data that is currently available in the dimension editor on the web.

Deleting dimensions in the downloaded spreadsheet does not remove the dimension from the system. In fact, if you have a large amount of data in the spreadsheet and to help improve performance, we recommend that you remove all the unchanged rows from the downloaded file before uploading.

Pay special attention to the following before using this feature:

• DO NOT rename the file name or the worksheets in this file.

• DO NOT add columns, change column names, or change sheet tab names.

• Ensure that there are no duplicate records.

• General formatting is applied to all numbers downloaded in the spreadsheet that you will need to correct before uploading. For instructions, see Spreadsheet formatting.

• All required columns must be completed for each record before uploading.

• Use caution when entering data into the spreadsheet. Neither the spreadsheet nor the system validates the data upon upload.

• Deleting records in the spreadsheet does not remove them from the system.

  TIP: If you have a large amount of data in the spreadsheet and to help improve performance, we recommend that you remove all the unchanged rows from the downloaded file before uploading.

• If the spreadsheet includes a lot of data, it may take several minutes for the upload to complete before the system shows a confirmation message.

To edit dimensions using a spreadsheet

1. Open a dimension, and on the upper right-hand side of the page, do one of the following:

   • To add or edit records to an existing dimension table, select Download table.
   • To add records by starting with an empty spreadsheet template, select Download template.

2. Open the spreadsheet, or save the spreadsheet to a location first, and then open it.

3. Add a new dimension by adding a row, or edit the column information for an existing dimension. If adding new dimension rows, see Spreadsheet formatting.

4. After making your changes, save the spreadsheet.

5. In the dimension editor page for the spreadsheet to upload, select Upload table.

6. The Review uploaded table changes prompt displays information regarding the number of changes made, and the number of rows updated and/or added. Select OK.

7. The system displays the row(s) where changes occurred and/or new rows added in the Confirm Uploaded Data page. Do one of the following:

   • If the information is correct and you are ready to commit the changes to the system database, select Confirm Upload.

     IMPORTANT: This action will save ALL of the CHANGED dimension rows that you upload, not just the rows that display within the web editor confirmation page. For example, if you upload 20,000 rows of changes and set the filter (while in confirm mode) to the max 10,000 records, you will only be viewing half of the changes that will be saved on Confirm.

   • To correct the spreadsheet and/or make further changes, select Cancel Upload. Repeat the upload process starting with step 4.
8. At the confirmation prompt, select OK.

Spreadsheet formatting

When downloading the dimensions values, they may appear in the spreadsheet with General formatting. This is indicated by the green tick mark in the left corner in some cells—specifically number-based cells.

Large numbers

If you add new dimension rows that include large numbers, reformat them so they properly add to the database on return to the system by changing the number formatting to Number and the Decimal places field to zero.

Leading zeroes

For numbers that include leading zeroes, change the formatting to Text.

You can also simply add a single quote in front of the zero (this quote mark is not included in the data when it is uploaded).

Formulas

You can include formulas in the spreadsheet, and the system will only import the results into the database.