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.

Note: Standard exchange rates are not related to specific entities. Therefore, you cannot import standard exchange rates from a file. You must specify them in a report manually or import them from the integration table.
  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. Optionally, switch on the Include Statistical Opening Balance option.
    If switched on, the statistical opening balance is included in the data load and stored in the relevant cube.
    Note: This option is not displayed if schedule usage has not been enabled in the Consolidation Core configuration in Business Modeling.
  6. 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.
  7. Regardless of whether you load from the integration tables or a file, perform these steps:
    1. Click Next.
    2. If you load from a file, select or drag and drop the file to upload. Ignore this step if you load from the integration tables.
    3. Click Validate Data.
    4. When the results are displayed, select one of these options:
      • Load: Successfully validated data and, if applicable, data with warnings is loaded. If you load data with warnings, then data inconsistencies can occur in Financial Consolidation.
      • Skip: This options skips data with warnings from the load. Only successfully validated data is loaded.

      You can click Download Log to verify the details of errors and warnings that may have occurred.

    5. Click Load Data.
    Note: The file from which you load data 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 the financial data from a file are validated against the model. The validated data is loaded to the Financial Consolidation cubes, calculated, and all required elements generated. The results with errors are not 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.

  8. 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