User Community Service Desk Downloads

Tutorial: Export published reference data to a database

This tutorial shows how to maintain reference data in ONE and export it to external databases for downstream applications.

Scenario overview

You maintain a reference table of US states in ONE and need to export the published data to a database that feeds multiple applications. This ensures all systems use the same standardized state codes and names.

What you’ll learn

  • How to manage reference data records.

  • How to publish reference data changes.

  • How to create transformations that export reference data.

  • How to update source catalog items with published reference data.

What you’ll need

For this tutorial, prepare:

  • Source catalog item: Any catalog item containing reference-type data (for example, product codes, country lists, or status values).

  • Reference data table: A corresponding reference table where you’ll manage the authoritative version of this data.

  • Database connection: Access to write data back to your source system or data warehouse.

Prerequisites

Before starting this tutorial, ensure you have:

  • Appropriate role on reference data tables:

    • Owner or Editor role to create and modify reference data tables.

    • Approver role to publish changes (if using approval workflows).

    • At minimum Viewer role to access table data.

  • Working knowledge of data transformations plans.

  • Sample data or catalog items to work with.

For information about roles and permissions, see User roles.

Step-by-step instructions

  1. Review your source catalog item

    1. Go to your source catalog item that contains the reference data.

    2. Display the record preview to see the current data structure.

    3. Note the existing codes, names, or values that need to be managed as reference data.

  2. Modify the reference data table

    1. Go to Manage Data > Reference Data.

    2. Open your reference data table (or create one if it doesn’t exist).

    3. Add new records, modify existing ones, or delete outdated entries as needed.

    4. Review your changes in the draft state.

  3. Publish your changes

    1. Select the records you want to publish.

    2. Select Publish to make the changes available for downstream use.

    3. Verify the published status of your records.

  4. Create a transformation plan

    1. Go to Manage data > Transformation plans.

    2. Create a new transformation plan.

    3. Configure the input to use the published reference table.

    4. Add a Database Writer output step.

    5. Configure the Database Writer to target the original catalog item.

    6. Set the write mode to Replace to overwrite existing data.

  5. Execute the transformation

    1. Validate the transformation plan configuration.

    2. Run the transformation plan.

    3. Monitor the execution progress.

  6. Verify the results

    1. Return to your source catalog item.

    2. Display the record preview again.

    3. Confirm that the data now reflects your published reference data changes.

    4. Verify that your added, modified, or removed records appear correctly.

Expected outcome

The catalog item now contains the exact data from your published reference table, ensuring consistency across all systems that consume this data.

Next steps

After completing this tutorial:

Was this page useful?