Going Beyond the Close: Leveraging Data Management to Export Consolidated Financial Data in Oracle FCCS
April 23, 2019
Oracle Financial Consolidation and Close Cloud Service (FCCS) was developed as a powerful tool for companies to complete their monthly close and financial reporting activities. For a majority of companies, FCCS is the final step in the data flow process from point-of-sale and billing systems through GL systems to consolidated financial reports. However, for some companies, this may not be the case.
Depending on a company’s close process, they may need to complete additional activities after financial data has been consolidated. A significant example of this need can be found in government contractors, who complete billing allocations for their projects based on a series of calculations that can be driven by costs in several currencies, entities, and resources. For these companies, a single source of consolidated financial data can increase the accuracy and efficiency of these allocation processes tremendously.
While Smart View and Financial Reporting Studio reports can be used to query FCCS data, these tools have limited capabilities for scheduling and automation. In cases where the external system metadata doesn’t exactly match FCCS metadata, this can create an additional burden of manipulating the FCCS data once it is extracted. To alleviate these burdens, FCCS offers the ability to export data an additional way: by leveraging Data Management.
Data Management is a utility available in all Oracle EPM Cloud applications which mirrors the on-premises Oracle Hyperion Financial Data Quality Management, Enterprise Edition (FDMEE) tool. Data Management is primarily used as a tool to integrate data from source systems into Oracle EPM Cloud applications, or to move data between them. However, Data Management can also be used to extract data files for use in other external systems.
Data exports from Data Management are set up using the same steps as creating any other integration through Data Management, with a few slight changes.
First, create a Target Application for the GL Data to be exported. Choose to create a Local application, set the Type to Custom Application, and Deployment Mode to Not Applicable. For the Dimension Details, create a field for each column that should be included in the export file, with the appropriate description for that field. Note that these are the target application dimensions, not FCCS dimensions. In Export Options, ensure that Enable Export to File is enabled.
Next, create an Import Format for the data to be extracted. Set the Source to your FCCS application, and the Target to the new application you just created. In the Mappings table, select which FCCS dimension will be used to map data to each of the export file’s columns.
After that, create a Location for the file export. Once you select the Import Format you just created, the Source and Target fields will automatically populate. Ensure that these fields have populated correctly, and then save.
Once the Location is created, you can begin customizing the data that will be extracted.
Customizing the Data Extract
In any Data Management Location, you must create a Load Rule before the data can be extracted. Generally, data extract Load Rules are created in the same way as other Load Rules, but there are a few additional steps to complete.
First, in the Details section, the Source Cube must be set. In FCCS, this will be the Consol cube. In other applications (for example EPBCS), this would be the cube with consolidated data you intend to extract.
Next, in the Source Options tab, you must create a Source Filter for each dimension in FCCS besides Period. This includes the following: Account, Data Source, View, Scenario, Movement, Intercompany, Entity, Currency, Consolidation, and each custom dimension. These can be single members, multiple members, or member functions. The Source Filter will tell Data Management which lines of data to pull from the database to be mapped to the target file.
After that, in the Target Options tab, ensure that Enable Export to File is enabled, that the Column Delimiter in the target file meets your requirements. From there, create a default file name for the exported file and make sure to hit Save after completing these updates.
Finally, navigate to Data Load Mapping. For each dimension, create any necessary mappings to convert the FCCS metadata to the appropriate metadata for your target system. Remember that in this case, the Source Value is the FCCS member, and the Target Value is the label that will be shown in the export file. As in any other Data Management export, multiple FCCS members can be mapped to a single target line, specific FCCS data items can be ignored, and wild cards can be used to apply a template to any or all applicable members.
Generating Extract Files
Once the Data Load Rule is created, you can begin exporting data to the target file. To do so, navigate to the Data Load Workbench. Select the appropriate Location, Period, Category, and Rule. Import the data from FCCS, Validate it against the mappings you’ve created, and then Export the data to the target file. If you see an error in any of these steps, ensure that the Source Filters are created properly, that mappings exist for each dimension, and that the selected time period has data at the selected intersection.
Once you have exported data successfully, navigate to the Process Details screen. Look for the most recent process, and confirm that it matches the rule you just ran. Locate the Output File column, and then select Download to download the file.
The downloaded file will have a unique row for each combination of target mappings in each column. Once it is downloaded, it can be uploaded into any target system as needed.
Once you have set up your Data Management extract from FCCS, there are a few additional design considerations you can make.
In some cases, you may want to map a single FCCS line item to multiple targets. For example, a cost can be used as the base for multiple different allocations, or a department can roll up into multiple business lines. In order to process a line multiple ways, you will need to create multiple load rules with different mappings. To assign a mapping to a specific rule, use the Apply to Rule dropdown in the Data Load Mapping screen.
When extracting year to date data from FCCS, keep the Source Filter for View at “FCCS_Periodic”. Instead, update the Period Mapping so that each Period Key is set to include the full year to date. For example, to extract May 2019 YTD data, set the Period Key to 5/31/2019 and the Prior Period Key to 12/31/2018. Make sure to create these in the Application Mapping and to select the correct Target Application, so that you don’t affect any other integrations.
While consolidation systems are generally thought of as the final destination for closed consolidated actual data before financial reports are generated, it can also be used as a tool within a wider matrix of systems. Beyond extracts to planning solutions, data can also be sent to other systems for allocations, reconciliation, or warehousing. Using Data Management, you can customize your FCCS extracts to take out whatever data you need, whenever you need it.
If you’d like to speak with the SC&H team about how we can help you empower your decision-making with clear, concise visualizations at your fingertips, reach out to us today. We’ll help you make the most of your data, so you can make the most of your time.