Exporting Data from a Dense/Sparse Optimized (DSO) Application

In Dense/Sparse optimized (DSO) applications, the density of the Period and Movement dimensions is set to Dense, and the density of the Account dimension is set to Sparse. The density of the other dimensions is Sparse. This has implications when exporting data from a DSO application to a file when you need to include Period as a column in the output. If you have migrated your application to DSO, and have existing data integrations that export data from the application into a file, you must review the below recommendations and modify existing data integrations accordingly.

  • Include Period in the export file definition (see Registering a Data Export File Application in Administering Data Integration. Make sure to set the Dimension Classification for the column representing the period to Period in the target application definition and make sure to leave the Data Table Column Name blank (see Defining Application Dimension Details in Administering Data Integration). This ensures that the system handles the Period dimension appropriately when generating the export file. Make sure that the Dimension Classification for columns mapping to Account and Entity is also defined as Account and Entity respectively. It is not required to include amount in the export file definition because it is added as the last column by default.

    For example, see the export file definition for a file with column headers ACCOUNT, BUSINESS_UNIT, ACCOUNTING_PERIOD, and Amount. Note that the Data Table Column Name for ACCOUNTING_PERIOD is blank and that the Dimension Classification for ACCOUNTING_PERIOD is set to Period.


  • Application Details: Data Export File
  • To export a specific date format, you need to configure an Application Mappings (in Administering Data Integration) for the target data file application under Period Mapping Options (in Administering Data Integration) in Data Exchange. The example below shows how to map P12-FY21 to Dec 21 for output in the file.


    Period Mapping

  • If Period is shown as a dimension in Data Integration, Mapping Dimensions, remove it.


    Edit Integration: Data Export

  • Specify the Data Extract Option under Defining Direct Integration Options. There are likely to be more dynamically calculated Sparse dimension members in DSO applications than in non-Hybrid applications. This is in part because all parent members are configured as Dynamic Calc in DSO. It is important to note that the Account dimension is Sparse in DSO applications which creates the potential for many Dynamic Calc parent Account members. When exporting data from a DSO application you can export Dynamic Calc members of Sparse dimensions by selecting All Data as the Data Extract Option. See, Defining Direct Integration Options in Administering Data Integration. All Data allows exporting both stored and Dynamic Calc members of any dimension.
    Data Extract Option

    The All Data option has the potential to generate huge data sets and take a long time if not restricted by dimension Filters. See Defining Filters in Administering Data Integration. For example, all the dimensions other than Period and Year have filters specified below ensuring that the export is restricted to the required intersections only.


    Edit Integration Filters

    For example, if you don't specify a filter on the Account dimension, the system will attempt to retrieve data for every member of the Account dimension including parents.

    Note:

    Use the Stored Data only or Stored and Dynamic Calculated Data (Dense only) options (see: Defining Direct Integration Options in Administering Data Integration) if you do not need to export dynamically calculated members of Sparse dimensions.

  • Select Refresh Members and Refresh Metadata for your Financial Consolidation and Close application under Data Exchange, then Applications, then your Consolidation application name.