Metadata File Import

A set of default Metadata Import Add-Ons are created on install to provide a way to source a full dimension hierarchy into the application staging tables. After the metadata is uploaded into the application, a process will run to compare the data against existing dimension hierarchy and automatically create a request with the detected changes.

You can use the interface on the Metadata File Import page to upload metadata using an Excel template (see Setup Metadata Import Excel Template), or create a custom Data Connector (see Setup Custom Metadata File Import).

Import: Select an Excel template to import. Data in the staging table will be replaced (not appended) with the new import dataset.

Process: Run the comparison process for the selected data management step.

Clear Table: Use this to clear imported data from the selected data management step.

Retain Source System Order: When checked, metadata will be sorted in the same order provided in the source data file.

Retain Source System Order

This allows you to specify how the metadata can be sorted in the dimension hierarchy. By default, this Retain Source System Order will be turned off. This is helpful when setting up and viewing financial reports. Enabling this feature will perform the following processes in the application:

  • The Sort Order column included in the metadata import file will be read into the application staging table.

  • The system compares the sort order with the existing hierarchy and generates a request if the sort order is changed.

  • Sort order changes are committed in the correct order, as specified by the import source file.

Import Add-On Components

The following different components are pre-installed with the solution to enable Metadata File Import engine to run.

Import Add-On Staging Tables

Each supported dimension has two related staging tables created during installation. Data imported using Excel templates are loaded into these tables.

  • Import Tree table: Use for storing the relationship details, parent, and child. Each dimension table contains a different set of properties, including relationship properties and varying properties, relevant to the dimension.

    The following columns exist in all tree tables:

    • Dimension: The dimension column must match the description of the dimension you are loading. This is set up in the Administration > Dimensions page.

    • Sort Order: Retain Source System Order checkbox must be set to True for functionality. You can use this to define how the member is sorted in the hierarchy. If order is not important, enter a value of 1 for all rows in the hierarchy table and ensure the Retain Source System Order checkbox is unchecked.

  • Import Member table: Use for storing the member properties detail. Each dimension table contains a different set of properties, including varying properties, relevant to the dimension.

ImportMetadata that Uses Double Quotations

Application Control Manager uses the double quote as the default parser character in the connector rule. If you are importing Metadata that includes double quotations in the member description, text fields, or formulas you must specify a different parser character. The following steps layout how to define a new parser character.

  1. Choose a character that is not used in your data set. For example, @, ^, or a pipe |.

  2. Navigate to Data Sources > Connectors and select the connector you want to update.

    NOTE: Be sure to check which Scenario Type the connector is set to. In our example, ACM_AccountSource is set to (All) scenarios.

  3. Navigate to Workflow Profile > ACM_MetadataImport > AccountSource > Actual > Text 1 and enter QuoteCharacter=[character of your choice]. If this filed is populated, Application Control Manager will override the default quote character.

  4. Navigate to Business Rules > Connector and select the rule for your source import. In this example, we are using the default ACM_AccountSource connector rule.

  5. Update all properties in the rule with the parser character you have chosen.

    NOTE: Only one parser character is used per connector rule, therefore you must update all properties to use the same character.

Cube and Dimensions

A default cube called ACM_MetadataImport and three members are created during installation. These connect to the default Transformation Rule Profiles and require components for the Metadata File Import process to function.

Data Sources and Business Rules

A Data Source Connector is created during installation for each supported dimension. These data sources are configured to connect to Application Control Manager Import Add-On staging tables using the default Business Rules Connector.

You can use the application interface to upload metadata using an Excel template. If you are not using an Excel template, modify these default data connectors to pull data from a specific file location (see Setup Custom Metadata File Import).

Each data source is setup to connect to the corresponding Business Rule Connector for the dimension type. For each of the supported dimension types, there is a Business Rule Connector created during installation.

The default Business Rules are coded with the properties associated with the dimension. The display order of these properties also matches with the default layouts created during installation.

Transformation Rule Profiles

A Transformation Rule Profile called ACM_ImportMetadata, and three rule groups for Entity, Account, and View are created during installation. This profile is configured with the default cube ACM_MetadataImport and dimension names created during the installation.

Workflow Profiles

A Workflow Profile called ACM_MetadataImport_Default is created during installation. Each supported dimension under the Workflow Profile is configured to connect to the default data source connector created during installation.

The Workflow Profiles are used to import metadata into the OneStream platform staging tables. After the information is loaded, Application Control Manager analyzes to determine differences that exist between OneStream and metadata loaded into the application staging table. The system looks for differences in this order:

  1. Missing members in the metadata compared to the source metadata

  2. Existing members that must be moved or copied to a different hierarchy

  3. Updates to existing member properties

If updates are found, a request in the application will be generated. The results of this process are displayed on the main home page of the application.

Data Management Groups

A Data Management Group called Application Control Manager Metadata Import (ACM) with default sequences and steps is created during installation. These steps are configured to handle the process of loading and committing metadata updates to OneStream

Each supported dimension type has associated steps that are pre-configured to connect to the default business rule. The parameters are setup with the default workflow profiles created during installation.

When running these steps, it will perform the following processes:

  • Loads the data from the database or file into the workflow for the current global POV time and scenario

  • Compares the data loaded into staging to the existing OneStream members for the selected dimension and determines which members must be added

  • Compares the data loaded into staging to the existing OneStream hierarchy for the selected dimension and determines which updates are required to the overall hierarchy

  • Compares the data loaded into staging to the existing OneStream members and determines which properties must be modified

  • Compares the existing members and hierarchy in OneStream to the data loaded into the staging to determine which members must be removed from the hierarchy. Any member not in the import file/table is marked as orphaned in OneStream. The member is not deleted.

  • Generates an Application Control Manager request that performs all the required operations in a single transaction

After a request has been generated, the Commit All Metadata Updates data management step runs to commit the updates to the system.

Global POV Time

When running a metadata file import, the data is loaded in the current year and month. Staging tables and the Global Time is set to a full year.

If Enforce Global POV is set to True, the load process uses the value set in the Global POV. If the Global POV is set to a date format other than YYYYMM, the workflow profile will not load properly and an error message will be displayed.

If set to False, the data management source system import process ignores any Global POV settings and instead uses the system date and time to determine the period for loading data.

Import Varying Properties

The following type of properties will require special setup in the import source file to account for storing multiple different intersections for the same member. These properties are:

  • Any type of varying properties. See the Properties section in this guide for more information.

  • Cultural and Cultural descriptions

  • Shared member or alternate hierarchy member

See Importing Shared Member and Varying Properties section for instructions.