Loading data from the integration tables or a file

Entity accountants can load data only for entities for which they are responsible. Group accountants can load data for the whole group.

You can load data from a file or from the integration tables. These file formats are supported:

  • .xls
  • .xlsx
  • .csv: Only with comma delimiter
  • .txt: Only with comma delimiter

You can load data from these integration tables:

  • Facts Local Currency: Actuals and other financial data are loaded in the local currency from the BudgetingMain integration import table. For each entity, data can be periodic or YTD. In Financial Consolidation, financial data is stored as YTD. To calculate YTD from periodic data, available source system data for all periods of a fiscal year is required.
  • Facts Transaction Currency: Intercompany financial data is loaded in the transaction currency from the FactsTransactionCurrency integration import table. For each entity, data can be periodic or YTD. In Financial Consolidation, financial data is stored as YTD. To calculate YTD from periodic data, available source system data for all periods of a fiscal year is required.
  • Exchange Rates: The source rate types are the closing rate (F) and periodic average rate (PA). In Financial Consolidation, the PA rate is recalculated into the average rate (A). The PA rate requires 12 previous periods. Otherwise, it cannot be taken as the source for the rate import to Financial Consolidation.

Before you load data to Financial Consolidation, you must complete these tasks:

  • Provide financial data in the integration tables for entities and periods for which to load data.
    Note: Providing data in the FactsTransactionCurrency integration table and the ExchangeRate integration table is optional but recommended.
  • In Business Modeling, perform these tasks:
    1. Activate the Consolidation module.
    2. Activate dimensions on the Business Objects Configuration page.
    3. Set up structures and properties for the Groups, Entities, Accounts, and Group Accounts business objects. If required, set up structures for the Schedules business object.
      Note: In Financial Consolidation, data from the Accounts business object is not used. However, local accounts in the Accounts business object can be mapped to the Group Accounts business object. Therefore, the Group Account Mapping property must be specified for all records in the Accounts business object.
    4. Define the calendar.
    5. Ensure that at least one currency is configured in the Currencies business object and currencies from the BudgetingMain and FactsTransactionCurrency integration tables are configured as entity currencies.
    6. Configure the top profit and loss account.
    7. If the balance sheet is used, then configure the balance sheet accounts such as Total Assets, Total Liabilities, and Balance Sheet Profit/Loss.
    8. If required, configure segments and schedules in the Core module.
    9. Complete the configuration of the required business objects and any business objects that are required by your business.
    10. Publish the model.
  • In Financial Consolidation, define these entity parameters:
    • Local currency
    • Translation method
    • Scaling factor
    • Segments, if applicable
  • Review validation rules for data loads into Financial Consolidation.
  • In Financial Consolidation, to enable loading data from integration tables, ensure that these steps have a status of Open on the Status Monitor tab on the Landing Page:
    • Trial Balance Input and Import Entity Data on the Entity Status tab
    • IC Matching on the Entity Status tab
    • Standard Exchange Rates on the Group Status tab
Caution: 
If you load new data for a specific context, you lose the previously imported data and the calculated results for that context. A context is the combination of the configuration set, version, period, entity, and level that is configured for data import. The level is configured for data import if the Import attribute for the Level element is set to True. Data that does not match the selected context, including the level that is configured for data import, is not lost.

If you make changes in Business Modeling, the previously loaded data might be affected. We recommend that you reload financial data after you publish changes to the OLAP database.

  1. On the Landing Page, click the Entity tab and specify the context.
  2. Click the three dot icon on the Trial Balance card and select Data Import.
  3. If required, change the context of the load definition displayed in the Load Data window.
    You can select any version or versions, period or periods that are open for the selected versions, and an entity or entities for which the selected periods are open.
  4. Select the required load option.
  5. Click Next and review which business objects are assigned to segments. Business objects are assigned to segments based on the configuration of the Financial Consolidation core module.
  6. If you load from the integration tables, click Load Data.
    If you load from a file, click Next, select or drag and drop the file to upload, and click Load Data.
    Note: A file must comply with the schema of the BudgetingMain integration table. We recommend that you download the template and specify your data in it.

    The staged data (exchange rates and fact data) from the integration tables and a file is validated against the model. The validated data is loaded to the Financial Consolidation cubes, calculated, and all required elements generated. If there is an error, no data is loaded. Click Download Log to download an error log.

    Caution: 
    After each successful data load, we recommend that you download a log. You may find warnings that describe validation discrepancies identified during the load. Those discrepancies do not block a data load, but unresolved discrepancies may cause data inconsistencies in Financial Consolidation.

    To correct errors and discrepancies in the source data, use relational modeling. Select Dashboards > Data Integrations > Relational Modeling. After making corrections, load data again.

  7. Click Finish.
    Note: If you have access, you can view and download error logs also in EPM Administration. Select Dashboards > Logs > View Application Logs and specify applicable filters. In the Events field, select Modeling > Business Modeling > Load Facts (Load facts from Integration table). After you apply the filters, a list of log entries is displayed. Double-click a log entry to view detailed information in the Message pane. You can copy the message to the clipboard, if required. There are two log entries for each data load. One is for data validation, the other for the data import process.
    Note: 

    For imported data, a new Opening Balance schedule is created automatically in Business Modeling outside the main schedule hierarchy. That is, such a schedule is excluded from the closing balance calculation and is populated only by the Data Import process. Opening balances that are populated by the Carry Forward process are still part of the closing balance calculation.

    Each schedule is validated against the existence of imported opening balances. If they exist, validation evaluates if the imported values match the values from the Carry Forward process. Importing opening balances is optional, but they are useful for data integrity checks.

    Similar to the Carry Forward process, Data Import includes an automatic run of the Align Schedule Data with Finance and Segment Data process. Data Import does not verify the status of the Carry Forward Schedules that is displayed on the Status Monitor tab on the Landing Page.

Related topics