A common requirement for implementation of Apliqo FPM is to load the initial budget for the organisation. This can be done by load the budget through the FIN General Ledger TB cube.
In the following exercise we are loading the 2023 and 2024 budget for Company 1000 using the Ad-hoc Data Intergration. Once the budget has been loaded to the FIN General Ledger TB cube, we will use the Initialise Budget round trip to post the data to the FIN General Ledger cube and the planning cubes.
Ad-hoc Data Load
Ad-hoc data loads can be accessed at Administration > Data Integration > Data Processing > Stage Ad-hoc Data.
The ad-hoc data can be sourced from number of different sources:
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:
Create an ad-hoc data source entry.
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.
Map the data source to the target cube.
Restrict the source data.
Load the data to the target cube.
We are going to work through each of these steps to load the 2023 Budget for Company 1000. First some information about the data source.
For this walk-through the 2023 budget for Company 1000 has been provide at a CSV named, Budget_1000_2023.csv. This field is already loaded in the SourceData directory.
The field delimiter in the file is a ';'.
There are no thousand separates or quote makes around the values.
There is one header row.
The movement are in the same signature that we use for management reporting. That means the movements Liabilities and Revenues are expressed as positives and Expenses are expressed as negatives. For this reason, we are going to load the budget to OPV element in the FIN Consolidated Value Measure dimension.
The column of the are as follows:
Column A (v1) - Is the FIN BSEG 1 (Cost Center) element.
Column B (v2) - Is the FIN BSEG 2 (Customer) element.
Column C (v3) - Is the Account Code.
Column D (v4) - Is the full year amount for 2023.
Columns E - R (v5 - v18) - Are the monthly movements.
Create a Data Source
Ad-hoc data loads can be accessed at Administration > Data Integration > Data Processing > Stage Ad-hoc Data.
Define the Data Source
Click on the CSV icon to configure the data source.
Map the Data Source to the Target Cube
Now that we have set the data source and target cube in the settings, we need to map the columns in the data source to the dimensionality of the target cube. There are three types of mapping:
Fix - The element in the dimension that the data is to be loaded to is nominated in the mapping.
Variable - The element in the dimension that the data is to be loaded to is a column in the data source.
Variable Column - The multiple columns of data are to be loaded to multiple elements in the dimension.
Value Column - maps the value in the data source to the data point in the target cube.
To load the 2023 Budget from the CSV file we will use a combination of these Mapping Types to load the data to the FIN General Ledger TB. Follow the walk-through to map the data source.
Restrict the Data Source
Before the data is loaded the area of the cube that to be loaded is cleared. When the cube is clear the values of the data point are set to zero. The numbers are the cube are reloaded from the ad-hoc source data. The area of the cube that is clear is already restricted by the dimension that have a Fixed mapping type. The area of the cube can be further restricted. In our example we are loading the 2023 Budget. The T-Year Month dimension does have the Fixed mapping type. If the dimension is not restricted further that all months in the T Year-Month dimension will be cleared. In the following walk-through we will further restrict what is cleared.
Restricting what is cleared also validates the data. If the data is to be loaded outside of what is cleared, then the record would be rejected and a message would appear. For example, if we restricted the accounts to be loaded to the elements under the Management Trial Balance any records in the source data that load to statistic accounts would be rejected.
Load the Data
Once the data source has been defined, mapped and restricted it is ready to be loaded to the target cube. This is done by clicking on the Load button on the Load to Target widget.
