1.4. Operational Data Integration
- Adam Davis
Operational data is load using a Round Trip configuration. The data integration is managed at Administration
Apliqo FPM powered by IBM Planning Analytics / TM1 database. The TM1 database consists of multiple cubes to stores data and performs calculations. The TM1 database includes process that scripts that extract, transformed and load data into the cubes.
Generally, external data is imported from a data source into the intermediate cube when the data is evaluated for completeness. In Apliqo FPM the intermediary cube is the "FIN General Ledger TB". Once the external data is evaluated and correct it is moved into the reporting cube, FIN General Ledger.
The FIN General Ledger data make be extract, transformed and loaded to other cubes to complete financial forecasts and budgets. This is true for the main planning cubes; FIN Revenue Planning, FIN Expenses Planning and the FIN Balance Sheet Planning cube. In these planning cubes the year-to-date actual may be aggregated for certain business segments. The financial forecast or budget is submitted, i.e. copied back into the FIN General Ledger cube for reporting.
The following schema illustrates these data flows on a high level.
Loading Financial Data into the Trial Balance cube
The first step is to import the "raw" data into the FIN General Ledger TB cube, which mirrors the dimensional structure of the FIN General Ledger cube except for an additional FIN Delta dimension. Refer to the following table to determine the dimension that the raw financial data should be loaded in the FIN General Ledger TB cube:
Cube Dimension | Usage |
|---|---|
FIN Delta | This dimension has two N elements: Source and Current, with a calculated "Delta" between the two to allow for incremental loading of data. The direct import of data always has to happen on the "Source" element. |
FIN Version | Always use the ACT version to load the financial data. |
FIN Currency | Data always should be loaded to the "LOCAL" element in the local currency of the company in question. |
T Year-Month | The lowest level represents a financial period / month, e.g., 2022-M01, that the data needs to be loaded to. M01 represents the first period of the financial year used in the system, i.e., if a financial year starting in January has been configured M01 is January. Likewise, M12 represents the last month of the financial year. Optionally a period M00 can be used to load opening balances to and a period M13 is available to load end-of-year journals. |
FIN Company | The data is loaded to the company elements as set up as part of the dimension setup covered in an earlier chapter. |
FIN BSEG 1...4 | The maximum available four business segments can be used at the customers discretion. The data import happens to a N level element within these dimensions. If a business segment is not used or the information not available for some of the records the data is to be loaded to the default / dummy elements, e.g., "NA FIN BSEG 1". |
FIN OCoA | The data has to be loaded to a N level element in the operational CoA dimension. As far as the Trial Balance itself is concerned it will be a descendant of the main balance sheet rollup (C3_MGMTTB) but the same concept also applies to the import of statistical accounts. |
Scale | This is a reporting dimension, the "BASE" element always has to be used for importing the data. |
Time Analysis | This is a reporting dimension, the "BASE" element always has to be used for importing the data. |
FIN Consolidation Value Measure | In the context of the TB cube the "TB - Trial Balance" consolidation is what will get further processed. The import should target a N level element underneath this consolidation and depending on the source system there is mainly two scenarios:
|
Pre-built connectors
There are a number of pre-built connectors available for different source systems, which will accelerate the data import as well as the build of dimension structures. Those connectors are sometimes able to connect via the source systems API and also offer additional features like drilling down to transactions in the source system.
If a connector for your source system is available it is preferable to make use of it. Please refer to the connector-specific documentation for the setup steps and the detailed functionality.
Custom import process
If a pre-built connector is not available or custom requirements rule the usage of a connector out the go-to approach would be to handle the data import into the TB cube by developing an ETL process using the TM1 standard scripting tool Turbo Integrator. This is a development task that requires in-depth knowledge of the proprietary language and should furthermore follow Apliqo's best-practice guidelines. For more information please refer to the the Apliqo FPM developer guide.
Upload from Excel
Especially for prototyping and smaller datasets and where a full automation of the data imports is not a requirement or not feasible, uploading the data from an Excel template can be a viable option. Â The standard Apliqo UX component for Excel Uploads can be used for this purpose. The setup is relatively straightforward and can even cater for different template layouts as long as the general guidelines of using the appropriate elements for all dimensions described above is being followed.