Managing TIBCO Data Virtualization Metadata

When the server accesses a data source, it needs to know how to interpret the data stored there. For each object the server will access, you create a synonym that describes its structure and the server mapping of the data types.

Identifying the Adapter

The SUFFIX attribute in the Master File identifies the adapter needed to interpret a request. Use the SUFFIX value SQLTDV to identify the Adapter for TIBCO Data Virtualization.

Identify the Adapter

FILE[NAME]=file, SUFFIX=SQLTDV [,$]

where:

file

Is the file name for the Master File. The file name without the .mas extension can consist of a maximum of eight alphanumeric characters. The file name should start with a letter and be representative of the table or view contents. The actual file must have a .mas extension, but the value for this attribute should not include the extension.

SQLTDV

Is the value for the adapter.

Accessing Database Tables

If you choose to access a remote third-party table using JDBC, you must locally install the RDBMS' JDBC Driver.

The Server can access third-party database tables across the JDBC network. You must specify a URL for the data source and, possibly, a user ID and/or password for the database you are accessing. You can define these parameters in either the server's global profile or in a user profile.

Creating Synonyms

Synonyms define unique names (or aliases) for each object that is accessible from the server. Synonyms are useful because they hide the underlying data source location and identity from client applications. They also provide support for extended metadata features of the server, such as virtual fields and additional security mechanisms.

Using synonyms allows an object to be moved or renamed while allowing client applications to continue functioning without modification. The only modification required is a redefinition of the synonym on the server. The result of creating a synonym is a Master File and an Access File, which represent the server metadata.

Note: If you are creating a synonym for a JDBC data source, you must first add the syntax SET SYNONYM=BASIC to the edasprof.prf file.

Create a Synonym

To create a synonym, you must have previously configured the adapter and a connection.

    Procedure
  1. From the WebFOCUS Reporting Server browser interface Application page, click Get Data.

    The Get Data page opens.

  2. On the CONNECT TO DATA section of the page, in Simple Mode, right-click an adapter and click Show Connections.
  3. Right-click a connection and select Show DBMS Objects.

    The Create Synonym for TIBCO (R) Data Virtualization (JDBC) (Connection name) page opens.

  4. Enter values for the parameters required by the adapter as described in the chapter for your adapter.
  5. From the list of synonym candidates for the selected catalog, select a candidate for creating a synonym and then click Add.

    The synonym is created and added under the specified application directory.

Synonym Creation Parameters for TIBCO Data Virtualization

The following list describes the synonym creation parameters for which you can supply values.

Object Type

Restrict candidates for synonym creation based on the selected object type(s): Tables, Views, External SQL Scripts, and any other supported objects.

Restrict candidates for synonym creation based on the selected object type(s): Tables, Views, External SQL Scripts, Stored Procedures, and any other supported objects.

Choosing External SQL Scripts from the drop-down list enables you to represent an SQL Query as a synonym for read-only reporting. A Synonym candidate can be any file that contains one (and only one) valid SQL Query and does not contain end-of-statement delimiters (";" or "/") and comments.

Depending on the adapter, you can further restrict your search by choosing checkboxes for listed objects.

Catalog

Specify a catalog from which you can select a table or other object.

Schema

The user account that created the object or a collection of objects owned by a user.

Object Name

Type a string for filtering the object names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select all objects whose names begin with the letters ABC; %ABC to select all whose names end with the letters ABC; %ABC% to select all whose names contain the letters ABC at the beginning, middle, or end.

Customize data type mappings

To change the data type mappings from their default settings, expand the Customize data type mappings section. The customizable mappings are displayed.

The Customize data type mappings are given below.

  • Integer Precision. The TIBCO Data Virtualization data type used to store Integer Precision columns. The default value is 11.
  • Float Precision. The TIBCO Data Virtualization data type used to store Float Precision columns. The default value is 20.
  • Float Scale. The TIBCO Data Virtualization data type used to store Float Scale columns. The default value is 2.
  • Exact Datetime precision. The TIBCO Data Virtualization data type used to store DATETIME columns. This is a compatibility setting. When set to ON, the exact precision of date-time is preserved in the synonym field format. For example, rTIMESTAMP(5) is mapped to HYYMD5 instead of HYYMDs, thus preserves all 5 fractional digits. The default value is OFF.
  • Decompose Date fields into components. When set to ON, decomposes Date fields and date portion of Date-Time fields into Year, Quarter, Month, and Day components. The default value is OFF.
  • Date Order. Controls mapping of the date format into Master File as YYMD, MDYY, and DMYY. The default value is adapter specific.
  • Activate GEOGRAPHIC_ROLE assignment. When set to ON, assigns GEOGRAPHIC_ROLE based on column name analysis according to the vocabulary information stored and roles in the configuration file. Create Synonym maps the GEOGRAPHIC_ROLE Master File attribute to the certain geometry (GEOMETRY_AREA or GEOMETRY_POINT) or geography (e.g. CITY, STATE, and ZIP5) role defined in the server configuration file (edahome/catalog/geo_services.xml). The complete list of values can be obtained by running edahome/catalog/geo_srv_roles.fex. The default value is ON.
  • Application. Select an application directory. The default value is ibisamp.
  • Prefix/Suffix. If you have tables with identical table names, assign a prefix or a suffix to distinguish them. For example, if you have identically named human resources and payroll tables, assign the prefix HR to distinguish the synonyms for the human resources tables. Note that the resulting synonym name cannot exceed 64 characters.

    If all tables and views have unique names, leave the prefix and suffix fields blank.

Row Limit

Select the number of objects to display on the Create Synonym page.

Choose Columns

Select the columns to display for the synonym candidates of the selected catalog.

Filters

Filter the selection from the synonym candidates list. To filter your selection from the synonym candidates list, type the string to filter and click the search icon. For example, type ABC to search all objects whose names have the letters ABC in them.

Sample Generated Synonym

An Adapter for TIBCO Data Virtualization synonym consists of a Master File and an Access File. This is a synonym for the table wrd_wf_retail_customer.

Master File wrd_wf_retail_customer.mas 

FILENAME=WRD_WF_RETAIL_CUSTOMER, SUFFIX=SQLTDV  , $
  SEGMENT=WRD_WF_RETAIL_CUSTOMER, SEGTYPE=S0, $
    FIELDNAME=ID_CUSTOMER, ALIAS=ID_CUSTOMER, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=ID_AGE, ALIAS=ID_AGE, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ID_EDUCATION, ALIAS=ID_EDUCATION, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ID_GEOGRAPHY, ALIAS=ID_GEOGRAPHY, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ID_INCOME, ALIAS=ID_INCOME, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ID_INDUSTRY, ALIAS=ID_INDUSTRY, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ID_MARITAL_STATUS, ALIAS=ID_MARITAL_STATUS, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ID_OCCUPATION, ALIAS=ID_OCCUPATION, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ID_TIME_MIN, ALIAS=ID_TIME_MIN, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ID_TIME_MAX, ALIAS=ID_TIME_MAX, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=EMAIL_ADDRESS, ALIAS=EMAIL_ADDRESS, USAGE=A60V, ACTUAL=A60V,
      MISSING=ON, $
    FIELDNAME=FIRSTNAME, ALIAS=FIRSTNAME, USAGE=A20V, ACTUAL=A20V,
      MISSING=ON, $
    FIELDNAME=FULLNAME, ALIAS=FULLNAME, USAGE=A50V, ACTUAL=A50V,
      MISSING=ON, $
    FIELDNAME=GENDER, ALIAS=GENDER, USAGE=A1V, ACTUAL=A1V,
      MISSING=ON, $
    FIELDNAME=LASTNAME, ALIAS=LASTNAME, USAGE=A30V, ACTUAL=A30V,
      MISSING=ON, $
    FIELDNAME=INCOME, ALIAS=INCOME, USAGE=D20.2, ACTUAL=D8, $

Access File wrd_wf_retail_customer.acx 

SSEGNAME=WRD_WF_RETAIL_CUSTOMER, 
   TABLENAME=DB999."R729999D"."wrd_wf_retail_customer", 
   CONNECTION=CON01, 
   KEY=ID_CUSTOMER, $
  INDEX_NAME=PK__wrd_wf_r__7F6B0B8A8EAB90D6, 
      INDEX_UNIQUE=Y, 
      INDEX_COLUMN=ID_CUSTOMER, 
      INDEX_ORDER=ASC, $
  FOREIGN_KEY=wrd_customer_id_age_fk, 
     PRIMARY_KEY_TABLE=DB999."R729999D"."wrd_wf_retail_age", 
     FOREIGN_KEY_COLUMN=ID_AGE, 
     PRIMARY_KEY_COLUMN=ID_AGE, $
  FOREIGN_KEY=wrd_customer_id_education_fk, 
     PRIMARY_KEY_TABLE=DB999."R729999D"."wrd_wf_retail_education", 
     FOREIGN_KEY_COLUMN=ID_EDUCATION, 
     PRIMARY_KEY_COLUMN=ID_EDUCATION, $
  FOREIGN_KEY=wrd_customer_id_geography_fk, 
     PRIMARY_KEY_TABLE=DB999."R729999D"."wrd_wf_retail_geography", 
     FOREIGN_KEY_COLUMN=ID_GEOGRAPHY, 
     PRIMARY_KEY_COLUMN=ID_GEOGRAPHY, $
  FOREIGN_KEY=wrd_customer_id_income_fk, 
     PRIMARY_KEY_TABLE=DB999."R729999D"."wrd_wf_retail_income", 
     FOREIGN_KEY_COLUMN=ID_INCOME, 
     PRIMARY_KEY_COLUMN=ID_INCOME, $
  FOREIGN_KEY=wrd_customer_id_industry_fk, 
     PRIMARY_KEY_TABLE=DB999."R729999D"."wrd_wf_retail_industry", 
     FOREIGN_KEY_COLUMN=ID_INDUSTRY, 
     PRIMARY_KEY_COLUMN=ID_INDUSTRY, $
  FOREIGN_KEY=wrd_customer_id_marital_status_fk, 
     PRIMARY_KEY_TABLE=DB999."R729999D"."wrd_wf_retail_marital_status", 
     FOREIGN_KEY_COLUMN=ID_MARITAL_STATUS, 
     PRIMARY_KEY_COLUMN=ID_MARITAL_STATUS, $
  FOREIGN_KEY=wrd_customer_id_occupation_fk, 
     PRIMARY_KEY_TABLE=DB999."R729999D"."wrd_wf_retail_occupation", 
     FOREIGN_KEY_COLUMN=ID_OCCUPATION, 
     PRIMARY_KEY_COLUMN=ID_OCCUPATION, $
  FOREIGN_KEY=wrd_customer_id_time_max_fk, 
     PRIMARY_KEY_TABLE=DB999."R729999D"."wrd_wf_retail_time", 
     FOREIGN_KEY_COLUMN=ID_TIME_MAX, 
     PRIMARY_KEY_COLUMN=ID_TIME, $
  FOREIGN_KEY=wrd_customer_id_time_min_fk, 
     PRIMARY_KEY_TABLE=DB999."R729999D"."wrd_wf_retail_time", 
     FOREIGN_KEY_COLUMN=ID_TIME_MIN, 
     PRIMARY_KEY_COLUMN=ID_TIME, $

Access File Keywords

This chart describes the keywords in the Access File.

Keyword

Description 

SEGNAME

Value must be identical to the SEGNAME value in the Master File. 

TABLENAME

Identifies the TIBCO Data Virtualization table. The value assigned to this attribute can include the name of the owner (also known as schema) and the database link name as follows: 

TABLENAME=[owner.]table
 
CONNECTION

Indicates a previously declared connection. The syntax is: 

CONNECTION=connection

CONNECTION=' ' indicates access to the local data source.

Absence of the CONNECTION attribute indicates access to the default database server. 

KEYS

Indicates how many columns constitute the primary key for the table. Corresponds to the first n fields in the Master File segment.

See the KEY attribute below for information about specifying the key fields without having to describe them first in the Master File.

 
KEY

Specifies the columns that participate in the primary key without having to describe them as the first fields in the Master File. The syntax is:

KEY=fld1/fld2/.../fldn
 
WRITE

Specifies whether write operations are allowed against the table. 

KEYFLD
IXFLD

Supply the names of the primary key and foreign key fields that implement the relationships established by the multi-table Master File. Together, KEYFLD and IXFLD identify the field shared by a related table pair.

  • KEYFLD is the FIELDNAME of the common column from the parent table.
  • IXFLD is the FIELDNAME of the common column from the related table.

KEYFLD and IXFLD must have the same data type. It is recommended, but not required, that their lengths also be the same.

Note: An RDBMS index on both the KEYFLD and IXFLD columns provides the RDBMS with a greater opportunity to produce efficient joins. The columns must have the same data type. If their length is the same, the RDBMS handles the join more efficiently.

Managing Synonyms

Once you have created a synonym, you can right-click the synonym name in the navigation pane of either the WebFOCUS Reporting Server browser interface or ibi Data Migrator desktop interface to access the following options.

Option

Description

Open

Opens the Master File for viewing and editing using a graphical interface. If an Access file is used, it is also available.

Sample Data

Retrieves up to 50 rows from the associated data source.

Data Profiling

Data Profiling provides the data characteristics for synonym columns.

Followed by these options, if applicable:

Statistics. Alphanumeric columns provide the count of distinct values, total count, maximum, minimum, average length, and the number of nulls.

Count. The numeric column provides the data count value.

Row Count. Numeric columns provide the count of distinct values, total count, duplicate values, unique, non-unique values, and empty values.

Key Analysis. Allows you to select data set columns for analysis. You can also analyze entire data in a single operation.

Analysis Report

Followed by these options, if applicable:

Impact Analysis. Generates a report showing where this synonym is stored and used, with links to the synonym instances. Impact Analysis reports enable you to evaluate changes before they are made by showing which components will be affected. See the ibi™ WebFOCUS® Reporting Server Administration manual for details about Impact Analysis reports.

Dependencies Analysis. Generates a report showing information about the synonym and other synonyms and objects that are referenced within it.

Metadata Management

Followed by these options, if applicable:

Create Cluster Synonym. Creates a cluster synonym in WebFOCUS Reporting Server Browser Interface.

Classify Data. Gives options to classify columns of the data source.

Refresh. Regenerates the synonym. Use this option if the underlying object has been altered.

Edit as Text. Enables you to view and manually edit the Master File synonym.

Note: To update the synonym, it is strongly recommended that you use the graphical interface provided by the Open option, rather than manually editing the Master File.

Edit Access File as Text. Enables you to view and manually edit the Access File synonym.

Note: This option is available only when an Access File is created as part of the synonym.

Download Master File. Allows you to download the Master File to your computer.

Download Access File. Allows you to download the Access File to your computer.

Prepare Translation File. Allows you to prepare metadata translation files for the selected data set.

Note: You must have a Language File before using this option.

Data Management

Followed by these options, if applicable:

Create Indexes. Creates indexes on the data source table. You are asked to confirm this selection before the indexes are created.

Delete All Data. Deletes all existing data. You are asked to confirm this selection before the data is deleted.

Drop Table. Drops the table so that it is removed from the DBMS.

Insert Sample Data. Inserts specified number of sample records, populating all fields with counter values.

Show/Modify Data. Opens a window that shows the data in the data source with buttons you can click to insert values, filter values, reload the data source, and customize the view.

Quick Copy. Enables you to copy the data to a table in a target adapter

Flow

Opens the data flow page for creating flows.

Copy

Copies the synonym to the clipboard.

Delete

Deletes the synonym. You are asked to confirm this selection before the synonym is deleted.

Cut

Deletes the synonym and places it on the clipboard.

Rename

Allows you to rename the synonym.

Properties

Displays the properties of the synonym, including physical location, last modified date, description, and privileges.