Trillium provides a database read and write back function as one of its database connectivity capabilities. You can connect a variety of databases on Windows and UNIX systems without using any ETL tools, and build an integrated process flow from initial loading and analysis, all the way through to the Quality processes, and return the results to the database.
You can use the database read function by creating an entity from a relational data source, and optionally configure a batch export to extract data from the database. The database write function is available as a Database Write process in TS Quality which can be added to a process flow at any point in the project.
Warning: This functionality is designed to
support interest in writing to staging environments in production
processes or temp tables in test environments. The Database Write process
will overwrite the database table and will not append or update the data.
The attributes in the table that are not selected for mapping will be
blanked out. We recommend the database write operation always be
performed in a staging area and not directly in the original tables.
|
For example, say you have a master table for an online sales system in the Oracle database, which includes one million customer records. Using only the subset of test data (10000 records), you created a project for profiling, standardizing and linking, and completed testing in the Control Center. Now you want to load all the records, run through the workflow and update the master table with the new information. Your database administrator sets up a staging area and creates a new table or a duplicate of the original table. You add a Database Write process to the project, include the database read to the export, and create a batch script. By running the batch script, you will load all the records from the table and return the output back in the staging area. You then select the information in the staging area and update the master table in the database.
Guidelines
Note the following guidelines when using the database read/write function:
- Loader connections. For database read, you need to establish the loader connections for the source database in the Repository Manager. You may also need to configure the Windows or UNIX system (for example, Windows System DSN) depending on your database. See the Trillium Repository Administrator's Guide for information about loader connections and supported databases.
- Target Database Configuration. For database write, an appropriate configuration for the target database must be set up on the server where TS Quality is installed.
- Outputs. The output will be written to the database. In the Control Center, the output entities will also be generated with a type "TSQDA" and contain a snapshot of what is written to the database table. The "snapshot" output entities will not be generated when a Database Write process is exported to a batch script.
- Metadata. The Database Write process will write back only records, not any metadata.
- Size. The maximum attribute size is 32KB for database read/write. Attributes that are bigger than 32KB such as Text fields in SQL Database will be truncated.
See the following topics to learn how to use the database read/write function:
- Creating an entity from a relational data source
- Reading data from a database for a batch script
- Writing data back to a database