Creating Metadata

Synonyms constitute the metadata that ibi Data Migrator requires to define unique names (or aliases) for each table or view that is accessible from the server. A synonym must be created for every table that ibi Data Migrator accesses.

Once you have created the metadata (synonym), it is a good idea to test it to make sure that the data is available for ibi Data Migrator processing.

You can also create and test metadata from the Managing Metadata page of the WebFOCUS Reporting Server browser interface. For more information, see the ibi™ WebFOCUS® Reporting Server Administration guide or the WebFOCUS Reporting Server browser interface online help.

Create Synonyms for Database Tables

    Procedure
  1. In the ibi Data Migrator desktop interface, right-click an application directory, point to New, and then click Synonym (Create or Update).

    The Select dialog opens, showing the adapters that you have configured.

  2. Expand a configured adapter folder, right-click a connection, and click Show DBMS Objects.

    The Create Synonym dialog opens, as shown in the following image.

    Note: The contents of this dialog depends on the adapter selected.
  3. To use the database specified when the connection was added, leave Default Database as is, or select a different database from the dropdown menu. This option is only available for Informix, Microsoft SQL Server, and Sybase ASE adapters.
  4. Optionally, filter the possible candidates by name and owner. Selecting this option adds Owner and Table Name parameters to the dialog.
    • For the Owner parameter, type a string for filtering the owner IDs and insert the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter ABC% to select tables or views whose owner IDs begin with the letters ABC, %ABC to select tables or views whose owner IDs end with the letters ABC, or %ABC% to select tables or views whose owner IDs contain the letters ABC at the beginning, middle, or end.
    • For the Table Name parameter, type a string for filtering the table or view names and insert the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter ABC% to select all tables or views whose names begin with the letters ABC, %ABC to select tables or views whose names end with the letters ABC, or %ABC% to select tables or views whose names contain the letters ABC at the beginning, middle, or end.
  5. If you have tables with identical table names, you can assign a prefix or a suffix to distinguish them. For example, if you have identically named human resources and payroll tables (most likely with different owners), assign the prefix HR to distinguish the synonyms for the human resources tables. Note that the resulting synonym name cannot exceed 64 characters. Alternately, you can specify different application directories for different schemas or owners.
  6. Expand the Customize data type mappings section to access additional settings, as shown in the following image.

  7. Optionally, change the scale and precision settings.
  8. From the Decompose Date fields into the components dropdown menu, select Off. This option must be off when using ibi Data Migrator.
  9. Complete your table or view the selection.
    • To select all tables or views in the list, select the checkbox to the left of the Default Synonym Name column heading.
    • To select specific tables or views, select the corresponding checkboxes.
  10. The Default Synonym Name column displays the name that is assigned to the synonym. To assign a different name, replace the displayed value.
  11. Click Add.

    Synonyms are created and added under the specified application directory.

    The status field of the Create Synonym window shows that the synonyms were created successfully.

  12. Click Close.

Create a Synonym for a Relational Stored Procedure

If you create a synonym for a relational stored procedure, you can use that stored procedure as a data source in a flow. When you create the synonym, you are prompted for values for the parameters of the procedure.

We are using Oracle® as an example for this procedure.

    Procedure
  1. In the navigation pane, right-click a configured relational Oracle connection and click Create or Update Synonym.

    The Select Synonym Candidates (Step 1) dialog opens.

  2. Select Stored Procedure from the dropdown menu and Filter by owner/schema and object name checkbox. Enter an Owner/schema or Object name or both and click Next.

    The Select Synonym Candidates (Step 2) dialog opens.

    Select the radio button for a stored procedure and click Next.

    The Select Synonym Candidates (Step 3) dialog opens.

  3. Select checkboxes for the parameters and supply a value for testing. Click Create Base Synonyms.
    Note: Values entered are used only when creating the synonym and are not saved.

    A confirmation message appears.

  4. Click Close.

    The synonym now appears in the navigation pane listed under the application directory that it was created in.

Update a Synonym

To update a synonym from the Available Objects dialog:

    Procedure
  1. Select Update Base Synonyms from the Action dropdown menu.
  2. Select a synonym from the list and click Update Base Synonyms.

    The Update Synonyms dialog opens, as shown in the following image.

    Update Synonyms dialog

  3. Select the attributes that you want to override those from the existing synonym and click Next.

    The selected synonym will now be updated

Update a Synonym

 
    Procedure
  1. Right-click a synonym in the Navigation pane, point to Metadata Management, and select Refresh.

    The Refresh Synonyms dialog opens, as shown in the following image.

  2. Select the attributes that you want to override from the existing synonym and click Next.

    The selected synonym will now be updated.

Update Multiple Synonyms at the Same Time

Using the Metadata Refresh option, you can refresh multiple synonyms at the same time if all the synonyms are located in the same application, belong to the same SQL-based adapter, and are not stored procedure synonyms.

    Procedure
  1. Multi-select the synonyms in the Navigation pane and select Refresh Metadata, as shown in the following image.

    The Refresh Synonyms dialog opens, as shown in the following image.

  2. Select the attributes that you want to override from the existing synonyms and click Next.

    The selected synonyms will now be updated.

Test Synonyms for Tables or Files

    Procedure
  1. In the ibi Data Migrator desktop interface, right-click a server name and click Connect.
  2. Expand the Application Directories folder, and expand the application directory where you created your synonyms.
    Note: If the synonyms are not listed, on the Home tab, from the Filter group, click Synonyms.
  3. Right-click the synonym that you wish to test and click Sample Data. Sample data appears in the workspace, and looks similar to the following image.

Create Sample Procedures and Data

    Procedure
  1. From the ibi Data Migrator desktop interface, right-click Application Directories in the navigation pane, point to New, and click Tutorials.

    The Create Tutorial Framework dialog opens, as shown in the following image.

  2. From the Tutorial dropdown menu, select one of the following options:
    • WebFOCUS - Getting Started Demo
    • WebFOCUS - Retail Demo
    • WebFOCUS - Data Science Demo
    • WebFOCUS - SSAS Cube Join Demo
    • WebFOCUS - State Population Demo
    • WebFOCUS - Custom SQL Security Provider
    • ibi Data Migrator - General
    • ibi Data Migrator - Iterator
    • ibi Data Migrator - File Listener
    • ibi Data Migrator - Star Schema
    • Create Legacy Sample Tables and Files
    Note: The WebFOCUS and Legacy sample tables and files are not used with ibi Data Migrator.

    The Retail Demo option loads tables with a pre-determined amount of data designed for WebFOCUS reports.

  3. From the Select DBMS dropdown menu, choose a DBMS type, and click Create. DATREC, a binary file format, will be the only choice if you do not have other adapters configured.

    For the Prefix for SQL Tables field, use the default value unless you have a reason to avoid tables starting with the indicated default. If so, type a different value.

    When the creation process is complete, a confirmation message appears. The message depends on the type of samples you create, for example, ibi Data Migrator - General, ibi Data Migrator - Iterator, ibi Data Migrator - File Listener, or ibi Data Migrator - Star Schema.

    The message is also displayed in the Console Log.

    The procedures and synonyms appear in the ibisamp application directory, which is created and added to the server search path during installation. For FOCUS, the sample data is also stored in ibisamp. For other adapters, it is stored in the database of the configured adapter. The source data for the file listener flow is stored in ibisamp in four files with names starting with dmord and an extension of data.

  4. Ensure that the ibisamp application directory is in the server path. It will be in the path unless you delete it.
    • In the ibi Data Migrator desktop interface, examine the navigation tree in the server you are using.
    • On the Applications tab of the WebFOCUS Reporting Server browser interface, click the Application Directories link in the navigation pane to see the application path. For more information, see Configure the Application Path.

Create Column Names in Lowercase

Note: By default, column names are created in the database with uppercase column names. This is problematic for databases, such as PostgreSQL and those based on it, such as Greenplum, where column names are expected to be in lowercase.
    Procedure
  1. In the ibi Data Migrator desktop interface, expand the Adapters folder.
  2. Right-click the folder name of the adapter that you wish to configure and click Change Settings.

    The Change Settings dialog opens.

  3. In the Metadata section, from the ALIAS_CASE dropdown menu, select Enforce lower case.
  4. Click Save.
Result

This setting is also used by ibi Data Migrator when creating tables.

Create Sample Procedures and Data for Star Schema

    Procedure
  1. From the ibi Data Migrator desktop interface, right-click Application Directories in the navigation pane, point to New, and then click Tutorials.

    The Create Tutorial Framework dialog opens. Your display depends on the specific adapters you have configured.

  2. Select ibi Data Migrator - Star Schema from the Tutorial dropdown menu.
  3. From the Select DBMS dropdown menu, select a relational database that you have configured.

    For the Prefix for SQL Tables field, use the default value unless you have a reason to avoid tables starting with the indicated default. If so, type a different value.

  4. Click Create. The following status message should appear:

    Star Schema sample procedures created for database

    where:

    database

    Indicates the database type that you have selected.

Result

The procedures and synonyms appear in the ibisamp application directory, which is created and added to the Server search path during installation.

Create Sample Procedures and Data for File Listener

From the ibi Data Migrator desktop interface:

    Procedure
  1. Right-click Application Directories in the navigation pane, select New, and then click Tutorials.

    The Create Tutorial Framework dialog opens. Your display depends on the specific adapters you have configured.

  2. Select ibi Data Migrator - File Listener from the Tutorial dropdown menu.
  3. From the Select DBMS dropdown menu, select a relational database that you have configured.

    For the Prefix for SQL Tables field, use the default value unless you have a reason to avoid tables starting with the indicated default. If so, type a different value.

  4. Click Create to create the sample procedures and data for the file listener.

Create Sample Procedures and Data for Iteration

    Procedure
  1. From the ibi Data Migrator desktop interface, right-click Application Directories in the navigation pane, point to New, and then click Tutorials.

    The Create Tutorial Framework dialog opens. Your display depends on the specific adapters you have configured.

  2. Select ibi Data Migrator - Iterator from the Tutorial dropdown menu.
  3. From the Select DBMS dropdown menu, select a relational database that you have configured.

    For the Prefix for SQL Tables field, use the default value unless you have a reason to avoid tables starting with the indicated default. If so, type a different value.

  4. Click Create. The following status message should appear:

    Sample procedures created for database

    where

    database

    Indicates the database type that you have selected.

    The procedures and synonyms appear in the ibisamp application directory, which is created and added to the server search path during the installation.

  5. Load the created tables and expand the ibisamp directory.

    Right-click the dmplntpf flow and click Submit.