Ad-hoc data can be loaded to FPM at Administration > Data Integration > Data Processing > Ad-hoc Data. The ad-hoc data can be sourced from:

• a csv file located on the server,

• a SQL query from an ODBC source,

• uploaded from an Excel or csv file, or

• pasted into a staging cube.

To load ad-hoc source data the following steps should be followed:

1. Create an ad-hoc data source entry.

2. Define the ad-hoc data source. There are three ad-hoc data source types; csv source file, SQL query, either uploaded to the staging cube from Excel, or copy-and-pasted directly into the staging cube.

3. Map the data source to the target cube.

4. Restrict the source data.

5. Load the data to the target cube.

1. Adding an ad-hoc data source.

2. Define the ad-hoc data source.

Different settings are required depending on the data source type.

CSV file.

ODBC connection

Staged ad-hoc data

The source data can be uploaded using the Excel Upload widget or copy-and-pasted into the Staged Records widget. The Excel upload is expecting the column index in the first row and row index in the first column. A maximum of 30 columns can be uploaded. The file size must be under 5MBs. Use this template to frame your ad-hoc data, . Copy-and-paste is limited to 1000 rows and 30 columns.

3. Map the data source to the target cube.

Pick the target cube from the drop-down list. The ad-hoc data needs to be aligned with the target cube using fixed dimensional references or the columns from the ad-hoc data source. Fixed dimensional references is a single leaf element in the dimension that is a member of the target cube. The ad-hoc source data will include dimensional references and values that need to load into the target cube. One or more columns can include data that needs to be mapped to the target cube.

Mapping an ad-hoc data source with a single column of values

In the following walk through we are going to map a csv file to be loaded to the FIN General Ledger TB cube to load the statistical data for 2022. The columns of the sample ad-hoc source data are as follows:

Mapping an ad-hoc data source with multiple columns of value

In the following example we are going to map currency data that has been uploaded to FPM to the FIN Currency xR cube for 2020. The columns of the sample ad-hoc data source are as follows:

4. Restricting and clearing data

Restricting Data

Based on the mapping provided the data will be loaded to the target cube. The ad-hoc data can be restricted to load to nominated elements listed as the valid elements for the dimension. Each element is separated by the Element Separator set in the settings. If the restricting element is a consolidated element, then the leaf elements under the restricting consolidated element are the restricted elements. Data cannot be loaded to consolidated elements. If there are no restrictions for the dimension, then all elements can be loaded from the ad-hoc data source. See Adding elements from the ad-hoc data source.

When loading the ad-hoc data any records that are not valid are listed as a Rejected Records. The Rejected Records should be reviewed, and the valid records adjusted as required.

Clearing Data

Before the data is loaded the data in the cube is cleared. The portion of the cube that is cleared is based on the fixed dimensional references and the restricted elements. If there are no restricting elements, then the data against all elements in the dimension will be cleared with respect to the restriction of the other dimension. If the restricted element is a consolidated element, then the data against the leaf elements under the consolidated element in the dimension will be cleared with respected the restrictions in the other dimensions.

5. Loading Ad-hoc Data

Once the Settings, Mappings and Valid Records have been set, the ad-hoc data can be loaded to the target cube. There is a Setting to accumulate values for the data source otherwise the last value for the data point will be the value that appears in the target cube.

The user and the date-time the ad-hoc data was loaded is recorded.

Adding elements from the ad-hoc data source

Elements will automatically be added if the dimension is the Operational CoA (FIN OCoA) or a Business Segment dimension (FIN BSEG 1, FIN BSEG 2, FIN BSEG 3, FIN BSEG 4) that is mapped to the ad-hoc data source. For the Operational CoA (FIN OCoA) the list of valid elements must be Total GL Accounts. New elements are first added to the Total GL Accounts. For the Business Segments there must be no restrictions. New elements are first added to the Total FIN BSEG x consolidation.

After the ad-hoc data has been load the new accounts must be mapped to the Management Trial Balance, see Chart of Accounts Management. New business segments must be mapped to the business segment reporting structures, see Setup and Maintenance of the Business Segments.