Adding column to a destination table

To add a column:

  1. Right-click on any column and click Add Column. Alternatively click New to add a column. The Add a New Destination Column pop-up window is displayed.
  2. Provide the column name and description.
    Note: Avoid using the studio reserve word 'ID' when naming the columns. For more details, see studio reserve words .
  3. Provide the column type - lookup or standard.
    • Standard: If a column is marked standard, the values or data it contains will be displayed exactly as they are in the source ERP system. The data does not undergo any transformation during mapping.
    • Lookup: If a column is marked lookup, the values in that field will undergo a transformation during the mapping phase. Extracted data will be mapped based on the values and settings defined in the lookup (see Lookup Categories) to the values in a lookup table. Using the lookup option allows you to simplify and or standardize values from a single ERP or across ERPs in Infor Risk & Compliance, simplifying rule building for users.

      Example: Currency codes may not be saved in the same format in every ERP. So when currency codes are extracted from the source ERP, they are verified against a lookup table and map to the required value in the lookup table. When building a rule in Infor Risk & Compliance, users can then reference 'USD' for US Dollars, regardless of how it is defined in the source system.

      Note: Lookup column applies to projects created using earlier version of Studio.
  4. When a column is marked 'Lookup', an additional field Lookup categories is enabled. Lookup columns will actually lookup against one of the categories defined. The value in the lookup category will be populated into the column. Several lookup categories are already provided in the base projects available with the Studio. New categories may be defined as required.
  5. Provide the data type for the column. By default, an Insight supports 4 data types:
    • bigint: This column stores only numeric values. Values can range from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).
    • datetime: Stores dates and times
    • decimal: The largest possible value is +/-79,228,162,514,264,337,593,543,950,335
    • nvarchar: This column saves textual information such as descriptions or names. It can store any alphanumeric value limited to 1000 characters.
  6. If the data type provided is decimal or nvarchar, then user must specify the data length. The data length for the data type decimal is 19,2 by default and for nvarchar it is 1000.
  7. Specify the column attributes required.
    • View key: This is the primary object in rule building. View keys will be displayed on level 2 of the exceptions browser. It is mandatory to specify one view key per table.
    • Display: This check box is selected by default. When selected, the table column will be displayed in the Infor Risk & Compliance on the rule building interface.
    • Advanced: Determines whether or not the field will be shown in a basic view in Infor Risk & Compliance.  Basic views include pages like the Data Browser and IntelliLinks display for the selected object. You should only have as many basic fields as are required to identify the object in these displays and all others should be marked as Advanced.  Choosing too many basic fields will result in too many columns and fields displayed in the user interface of Infor Risk & Compliance.
    • Column is a Flag: If this check box is seslected, the column name is appended with _FLAG. Only Y or N values may be stored in this column. The Data Type will have (1) next to it indicating that only a single character may be stored in the column.
    • Analysis scope: This applies to date columns and enables you to define the scope of your analysis through Infor Risk & Compliance. If, for example, you select the column CREATION_DATE for analysis scoping and provide date filters in Infor Risk & Compliance while scheduling an analysis, the date filters  will apply to the CREATION_DATE column and analysis will be scoped on this column.
    • Ignore in extract: If this check box is selected, the columns in this table will not be extracted. On the Columns panel, each column will display the Ignore in Extract status as 'true'. If the check box is not selected, the status will be 'false'.
    • Index flag: If this check box is selected, the index in the table will be built on this column.
    • Clustered index: A clustered index optimizes the grouping of data in the database and the data will be grouped on the column selected. Each table can have only one clustered index.  Selecting the Clustered Index check box automatically selects the Index flag setting for this column.
    • Personal Data: If you select this check box, the data available in this column is considered as personal information. The columns selected will reflect in the Personal mapping section. These options are displayed:
      • Date: if the data in the column is date type.
      • Email: if the data in the column has an email address.
      • Other: if the data in the column is other than a date or an email.
      Select the appropriate option to indicate the personal data type. This information is passed on to the IRC application through the generated Insights and the information of the selected option is anonymised to:
      • Date: Current date
      • Email id: anonymous@ anonymous.com
      • Other: 0 (Zero)
  8. Click Save. The column is saved and a confirmation pop-up window appears on the bottom right of the screen.