Bulk Loading in the Chart of Accounts

Instead of setting up the CoA manually / account by account it can be much more efficient to load the structure from a source system or using an Excel template. A wizard will guide you through this process. To open the popup dialogue click "Bulk load" in the Operational CoA main page.

Configurations

You will have to confirm a warning before being able to proceed, then either select an existing bulk load configuration by clicking on it (in case you have made changes to the source and want to run it again) or click "Create" to start the bulk load of a configuration. You will have to define a unique name and select the source type:

• Import from CSV: A CSV file in a valid format that has to be present in a source directory on the server.

• Import from ODBC: An ODBC connection needs to be available on the server, the query can be defined in the next step.

• Import from staging cube: A cube will be used to stage the rollup definition, use this option if you want to upload an Excel template through the front end, this is the recommended procedure for the initial setup of the system.

Upload

In the next step you will be able to upload from your source into a rollup within the staging cube in order to validate and review the correctness of the rollup structure. You can use an existing target rollup or create a new one using the "+" icon. In most cases you will use one rollup for every configuration, i.e. if you have created a new configuration you probably want to create a new rollup to be associated with it as well. Occasionally it might make sense to use the same rollup for multiple configurations, for instance if you want multiple configurations for loading the dimension with alternative options as described in the next step. If you are using an already existing rollup you also have the option here to download the content as an Excel file.

Upload from Excel

Use drag-and-drop or the select button to upload an Excel template in a valid format (description of the two formats below). The system will validate the contents and present you with potential validation issues if any. When you click next the selected rollup will be cleared and reloaded from the source.

Load from ODBC

If you have selected ODBC as the data source, please select an available ODBC connection and specify a query. The query result needs to meet the valid import format requirements. A preview is available by clicking the preview button.

Load from CSV

If you have selected a CSV as source you have to define the source file, quote character and delimiter. A direct preview is not available in this step but you will see the result in the following step before the CoA dimension is actually updated.

Review

The next step will show a review of what has been uploaded from the source to the staging cube. You can technically change the data directly in the staging cube, in most cases it would be a cleaner approach though to make any changes in the source (e.g. the Excel file that you uploaded, the query you provided, ...).

Update structure

This is the final step before updating the dimension structure from the staging cube. Pay special attention to the settings:

• Rebuild Option: Choosing the appropriate option is crucial, a unsuitable setting can compromise the structure.

  • Add Components Only: This option should be chosen if you are uploading a rollup for the first time. As the name suggests elements will only be added, i.e. no existing parent-child relationships will be unwound and no elements will be deleted.

  • Unwind: This option will reveal an additional field in which you have to specify the consolidation(s) that should be unwound, i.e. all parent-child relationships under this node will be removed before the content of the rollup will be reloaded. Be aware that there might be elements in the hierarchy that have been loaded from a different rollup, which will loose their structure as well if you choose this option. This is not necessarily an issue, but you might have to reload the structure for those potentially impacted rollups as well by executing the bulk loads for those rollups as well.

  • Delete All Elements: This option will delete all elements in the operational CoA dimension before reloading from the source. This option would normally only be used in initial stages of the project or when a fundamental restructuring of the CoA dimension takes place.

• Clear attribute values: If turned on all attribute values (descriptions etc.) will be cleared and only be re-populated if they are contained in the rollup source. Turn this option on if this is intended because you want to leave the attribute blank intentionally for some elements. Do not turn this option on if attribute values are loaded from multiple sources.

• Override attribute values: Turn on this option if you want to overwrite current attribute values from the source, this option can normally safely be turned on, consider turning it off if you are only intending to incrementally fill attribute values without overwriting values that have been loaded with a previous rollup.

• Override element type: Turn this option on if element types should be changed if applicable. This is a safety feature to avoid accidentally changing N elements to C elements, which could lead to data loss because data will only ever be stored on the lowest level (N / numeric elements).

• Parent Root: You can optionally select a parent root that all elements on the highest level of your rollup structure will be added to. Oftentimes this will be the node "Reporting Rollups", that is meant to contain all customer specific rollups.

• Parent Root Weight: If a parent root has been selected you can specify the weight your rollup(s) should be applied to. If you are just adding your rollup to Reporting Rollups it usually makes sense to choose a weight of "0", i.e. your rollup will visually be grouped under the "Reporting Rollups" node but the value of any data on Reporting Rollups itself will be zero to avoid confusion due to adding up numbers that don't make sense to be added up.

• Add Total Elements: This option can always be turned on safely. It will ensure that technical nodes like "Reporting Rollups" are created if they don't exist yet or have been deleted because you are using the "Delete All Elements" update option.

All selections that you make in this step will be stored with the configuration you did select at the beginning of the wizard, so that you don't have to select them again before reloading the rollup for instance as part of an automation.

When you click "Next" the dimension update will be executed.

Finish

If the update was successful the wizard will continue to the "Finish" screen. You might see a couple of warning messages that will inform you about remaining issues with the CoA dimension that you should attend to next such as the presence of unmapped accounts or the requirement to synchronise the dimension. This is just a reminder, no further action is required in the wizard and you can click finish for now or use the close icon.