Importing Dataset from Queries

Users can load external data into a DMP dataset by using reusable queries and field mappings. The Import Dataset Queries feature supports repeatable import processes, flexible period handling, and multiple options for managing existing data. It also provides a clear and structured setup process through import templates.

This feature includes the ability to:

  • Save and reuse import templates for recurring import jobs, reducing the need to recreate configurations for each import.
  • Map returned query columns to dataset keys, data types, and periods so the imported data aligns with the target dataset structure.
  • Select options for managing how imported data interacts with existing values, including replacing, retaining, or adding to existing data.
  • Support multiple period mapping methods, such as year-based, date-based, and time mark mapping, to accommodate different data formats and business requirements.
  • Optionally create missing records during import and update key state settings as part of the import process.

Importing from Data Lake

Before using this feature, import the required data from Data Lake.
  1. From the menu, select Version > Import > Import Data Lake.
    The Import Data Lake dialog box is displayed.
  2. In Input, specify the query to run in Data Lake.
  3. In Output, specify this information:
    Type
    Select Database Table.
    Name
    Specify a name for the configuration.
    Reset Table
    Select the check box to create a new database table.
  4. To save the configuration, click the ellipsis icon then specify a name for the configuration. Click OK then click Yes.

Setting up a workflow for the Import Data Lake configuration created

  1. From the menu, select Data > Workflows.
  2. In the Action column, set Import Data Lake.
    In the Parameter column, select the saved Import Data Lake configuration.
  3. In the Action column, set Wait for Server Task.
    In the Parameter column, select Import Data Lake.
  4. Click Run.
  5. Optionally, save the workflow configuration. This option is available only when you click the ellipsis button in Configuration.

Accessing the Database Table

From the menu, select Data Administration > DB Tools.
The database table includes all data imported from Data Lake.
Note: DB Tools is only accessible if EnableDBTool is enabled in Special Settings and user the user is an Administrator

Enabling the Special Setting for Import Dataset Queries

  1. From the menu, select Setup > Settings > Logging > Special Settings.
  2. Locate the ImportDatasetQueries option from the list.
  3. To enable the configuration, double-click the ImportDatasetQueries field and set the value to 2.
  4. Click OK.

Creating and Saving Import Dataset Queries configuration

  1. From the menu, Version > Import > Import Dataset Queries.
    The Import Dataset Queries tab is displayed.
  2. In Parameters, specify this information:
    Dataset
    Select a dataset from the list.
    Transaction Type
    Select a transaction type from the list.
    Key State
    Optionally, select a Key State to assign to updated records. Newly imported records are always assigned the New key state, regardless of the selected Key State.
    Period Mapping
    Select a Period Mapping option from the list.

    This table shows the available Period Mapping options and the fields that are displayed based on the selected option.

    Period Mapping Description
    Year and Period Shows the Year Column Name and Period Column Name fields. This is the default Period Mapping option.
    Time Mark Provides a list of existing Time Marks.
    Date Time Shows the Date Time Column Name and Format fields.

    In Format, select the format used for date values in the source data:

    • DD/MM/YYYY
    • YYYY/MM/DD
    • MM/DD/YYYY
    Year Column Name
    The year column name corresponds to the column in the imported table in DB Tools.
    Period Column Name
    The period column name corresponds to the column in the imported table in DB Tools.
    Date Time Column Name

    The date time column name corresponds to the column in the imported table in DB Tools.

  3. In Query, click New.The Query field is set to default values, but you can still update it as needed.
    Note: To include the query in the configuration, click Add. You can then create and add additional queries, which are processed one after another.
  4. Click Validate to check if the query is valid.
  5. To save the configuration, click the ellipsis icon then specify a name for the configuration. Click OK then click Yes.

Running the Import Dataset Queries from Workflow

  1. From the menu, select Data > Workflows.
  2. In the Action column, set Import Data Queries.
    In the Parameter column, select the saved Import Data Query configuration.
  3. Click Run.
  4. Optionally, save the workflow configuration. This option is available only when you click the ellipsis button in Configuration.

Viewing the output in Favorite View

  1. Select Datasets > Open > [Dataset].
  2. Select the Favorite View properties based on your preferences.
  3. Click OK and open.
    The Imported Dataset from Queries are displayed in the Favorite View.