Training: Load the Initial Budget
- Adam Davis
A common requirement for implementation of Apliqo FPM is to load the initial budget for the organisation. This can be done by loading the budget through the FIN General Ledger TB cube. In this training we are going to load the 2023 and 2024 budgets for Company 1000 using the Ad-hoc Data Intergration functionality. 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.
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.
Ad-hoc Data Load functionality can be used to load any source data to Apliqo FPM. In this training we are going to work through each of these steps to load the 2023 Budget for Company 1000. The ad-hoc data load steps should be applied when loading different sources of data to different parts of Apliqo FPM.
First some information about the data source.
For this walk-through the 2023 budget for Company 1000 has been provide in a CSV named, Budget_1000_2023.csv. This file is loaded in the SourceData directory as set in the Apliqo FPM Settings.
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 signage 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, not under the TB consolidation.
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.
Currency Version
To calculate the reporting currencies the Currency version needs to be set for the FBUD - Final Budget version. This can be done Administration > Currency Conversion. Open the Version Setup popup to set the currency version for the FBUD - Final Budget version. The FBUD - Final Budget version is currently set to use the ACT - Actual currency version.
If required a different currency version can be used. This is done be adding another Currency Version and entering the exchange rates to be used. Then change the FBUD - Final version to the new currency version. See Training: Currency Conversion to review how currencies are maintained in Apliqo FPM.
Roundtrip
Now the budget has load we need to run the roundtrip to calculate the FBUD version in the FIN General Ledger. Follow the walk-through to create the roundtrip to process the budget.
Exercise
Now that we have loaded the Budget 2023 repeat the steps to load 2024 Budget.
For this exercise the 2024 budget for Company 1000 has been provide at a CSV named, Budget_1000_2024.csv. This file is in the SourceData directory as set in the Apliqo FPM Settings.
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 signage 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.
Don’t forget to run the Load Budget roundtrip to load the 2024 budgets into the FIN General Ledger cube.
Next