Categorizing Codesets and Defining Code Values

You can create and manage codesets in Codesets Manager. Its workspace has two sections, Enterprise Codesets and Codeset Mappings. You can categorize and define codesets in the Enterprise Codesets section, while you can create codeset crosswalks (mappings) in the Codeset Mappings section.

Before defining codesets, you need to create categories to hold the codesets.

To create categories, follow these steps:

  1. Go to Application Menu > Data Catalog > Codeset Manager.
  2. In Codesets Workspace, right-click the Codesets node.
  3. Click New Category.

    4. The New Category page appears.

  4. Enter Category Name and Category Description.

    5. For example:

    • Category Name - EDW
    • Category Description - This category contains three codesets, Country Codes, Gender, and Marital Status.
  5. Click .

    6. A new category is created and added to the category tree.

After creating a category, you can define codesets, which are stored inside the category.

To define codesets, follow these steps:

  1. Right-click the category node created by you in the above step.
  2. Click New Codeset.

    3. The New Codeset page appears.

  3. Enter Codeset Name and Codeset Description.

    4. For example:

    • Codeset Name - Country Codes
    • Codeset Description - This codeset has code names and code values for four countries.
  4. Click .

    5. A codeset is created and stored in the codesets tree.

We can populate code values in codesets by scanning the database.

To populate code values in codesets via DB scan, follow these steps:

  1. Click the codeset created by you.
  2. In Code Value Grid, click .
  3. Click and expand the Quick Connection pane.

  4. Enter appropriate values in the fields (connecting parameters). Fields marked with a red asterisk are mandatory. Refer to the following table for field description.

    5. Field Name

    Description

    DBType

    Specifies the database type.

    For example, Sql Server.

    Select the database type from which you wish to scan codes.

    Driver Name

    Specifies the JDBC driver name for connecting to the database.

    For example, com.microsoft.sqlserver.jdbc.SQLServerDriver

    It is autopopulated depending on the DB type. You can also update the driver name.

    IP Address/Host Name

    Specifies the IP address or server host name of the database.

    For example, localhost.

    Port

    Specifies the port to connect with the database.

    For example: 1433 is the default port for a Sql Server database type.

    Database Name

    Specifies the database name being used to connect to the codeset.

    For example, ErwinDIS931.

    System Name

    Specifies the name of the system related with the codeset.

    For example, EDW.

    The name of the system should be same as provided in Metadata Manager.

    System Environment Name

    Specifies the name of the environment related with the codeset.

    For example, EDW-DEV.

    The name of the environment should be same as provided in Metadata Manager.

    User Name

    Specifies the user name to connect with database.

    For example, sa.

    Password

    Specifies the password to connect with database.

    For example, goerwin@1.

    URL

    Specifies the full JDBC URL that is used to establish a connection with the database.

    For example, jdbc:sqlserver://SERVER_ NAME:PORT#;databaseName=DatabaseName

    It is autopopulated based on the other parameters.
  5. Click to test the connection.

    6. If connection is established then a success message pops up.

  6. Write a query in the Query Panel and click to validate the query.
  7. Click to preview the query result.
  8. Double-click the Select CSMHeader Template cell of the required column.

    9. The columns of the Code Value Grid appears as an option list.

  9. Select the required Code Value Grid column.

    10. Note: You can select multiple columns from the data base.

  10. Click to import the selected columns in the Code Value Grid.

    11. The selected columns are imported in the Code Value Grid.

You can also enter codes in the Code Value Grid:

  • Manually
  • Using MS Excel files

For more information on maintaining codesets, refer to the Maintaining Enterprise Codesets section.