Importing new elements with Microsoft Excel

New elements can be imported into dimensions with Microsoft Excel. This uses the Process data command and the DE.ADD function.

The Process Data command takes the data from a source text file and enters it into Excel. The DE.ADD function enters the data from the Excel file into the dimension.

The source text file must be in the same format as this example: 
Product 1 Total Products 
Product 2 Total Products

The first column shows the names of the elements to be imported. The second column shows the consolidated elements of which they will become part.

To import elements with Excel:

  1. Close all open reports and select More > Process Data.
  2. Select External file from the Process from list.
  3. In the Source field, click Find and browse to the file from which to import.
    The Preview area displays a preview of the data to be imported.
  4. Click OK.
  5. Click Step.
  6. Click Cancel and confirm that you want to cancel the process.
    The first row of the import file has been written into the first line of the worksheet (cells A1 and B1).
  7. Click a blank cell in the worksheet and select Insert > Functionfrom the Excel menu.
  8. Select the DE.ADD function from the Alea Formulas category and click OK.
    The Function Arguments dialog is displayed.
  9. Add these parameters:
    Parameter Entry Comment
    Server Local Excel can only be used to edit dimensions on a local server. To edit dimensions on an OLAP server, use the Edit Dimension dialog.
    Dimension Enter the name of the dimension into which you are importing.
    Type N Only base elements are imported with this type .
    Element A1 Reference to cell A1, which contains the name of the element to be added.
    Parent A1 Can be a reference to a cell containing the parent or a string, e.g. "Total Products".
    Weight 1 Weight all elements with 1.
    Clear True/False To clear the target dimension of data before importing the new data, enter True. Otherwise, enter False.
    Description Enter a description of the data.
  10. Click OK.
  11. Click Process Data.
  12. Browse to the text file from which you are importing the data. Click Open and then OK.
  13. Click Continue.
    The import runs. The cell which contains the DE.ADD function displays the last element of the file from which you have imported.
    Note: A dimension can contain 10,000,000 elements and 127 consolidation levels.