text:
P�j?oa�, mimetypeapplication/epub+zipPK P�j? iTunesMetadata.plist}��
This figure illustrates a loading knowledge module.PKv"�� � PK P�j? " OEBPS/img_text/rkm_description.htm�!�
This figure illustrates the reverse-engineering process with an RKM.PKƩ�� � PK P�j? ) OEBPS/img_text/ckm_static_description.htm��
This figures illustrates a static check with a Check Knowledge ModulePK��a� � PK P�j? " OEBPS/img_text/ikm_description.htm��
This figure illustrates an integration knowledge module process with a staging area on the target.PK�s � PK P�j? " OEBPS/img_text/jkm_description.htm�4�
This figure illustrates the journalizing process.PK?�h�� � PK P�j? ' OEBPS/img_text/ckm_flow_description.htm��
This figures illustrates a flow check with a Check Knowledge ModulePKѴ��� � PK P�j? $ OEBPS/img_text/ikm_description_2.htm ��
This figure illustrates an integration knowledge module process with a staging area different from the target.PK��V PK P�j? OEBPS/img_text/scd_example.htm�.�
This figures illustrates type 2 slowly changing dimensions.PKa�s� � PK P�j? OEBPS/ikm.htm ��
This chapter explains the integration strategies used in integration interfaces. These strategies are implemented in the Integration Knowledge Modules.
This chapter contains the following sections:
An integration process is always needed in an interface. This process integrates data from the source or loading tables into the target datastore, using a temporary integration table.
An integration process uses an integration strategy which defines the steps required in the integration process. Example of integration strategies are:
Append: Optionally delete all records in the target datastore and insert all the flow into the target.
Control Append: Optionally delete all records in the target datastore and insert all the flow into the target. This strategy includes an optional flow control.
Incremental Update: Optionally delete all records in the target datastore. Identify new and existing records by comparing the flow with the target, then insert new records and update existing records in the target. This strategy includes an optional flow control.
Slowly Changing Dimension: Implement a Type 2 Slowly Changing Dimension, identifying fields that require a simple update in the target record when change, fields that require to historize the previous record state.
This phase may involve one single server, when the staging area and the target are located in the same data server, on two servers when the staging area and target are on different servers.
The integration process depends strongly on the strategy being used.
The following elements are used in the integration process:
An integration table (also known as the flow table) is sometimes needed to stage data after all staging area transformation are made. This loading table is named after the target table, prefixed with I$. This integration table is the image of the target table with extra fields required for the strategy to be implemented. The data in this table is flagged, transformed or checked before being integrated into the target table.
The source and/or loading tables (created by the LKM). The integration process loads data from these tables into the integration table or directly into the target tables.
Check Knowledge Module. The IKM may initiate a flow check phase to check the data in the integration table against some of the constraints of the target table. Invalid data is removed from the integration table (removed from the flow).
Interface metadata, such as Insert, Update, UD1, etc., or model metadata such as the Slowly Changing Dimension behavior are used at integration phase to parameterize column-level behavior in the integration strategies.
A typical integration process works in the following way:
Create a temporary integration table if needed. For example, an update flag taking value I or U to identify which of the rows are to be inserted or updated.
Load data from the source and loading tables into this integration table, executing those of the transformations (joins, filters, mapping) specified on the staging area.
Perform some transformation on the integration table to implement the integration strategy. For example, compare the content of the integration table with the target table to set the update flag.
Modify the content Load data from the integration table into the target table.
The following sections explain some of the integration strategies used in Oracle Data Integrator. They are grouped into two families:
These strategies are used when the staging area schema is located in the same data server as the target table schema. In this configuration, complex integration strategies can take place
This strategy simply inserts the incoming data flow into the target datastore, possibly deleting the content of the target beforehand.
This integration strategy includes the following steps:
Delete (or truncate) all records from the target table. This step usually depends on a KM option.
Transform and insert data from sources located on the same server and from loading tables in the staging area. When dealing with remote source data, LKMs will have already prepared loading tables. Sources on the same server can be read directly. The integration operation will be a direct INSERT/SELECT statement leveraging containing all the transformations performed on the staging area in the SELECT clause and on all the transformation on the target in the INSERT clause.
Commit the Transaction. The operations performed on the target should be done within a transaction and committed after they are all complete. Note that committing is typically triggered by a KM option called COMMIT.
The same integration strategy can be obtained by using the Control Append strategy and not choosing to activate flow control.
In the Append strategy, flow data is simply inserted in the target table without any flow control. This approach can be improved by adding extra steps that will store the flow data in an integration table ("I$"), then call the CKM to isolate erroneous records in the error table ("E$").
This integration strategy includes the following steps:
Drop (if it exists) and create the integration table in the staging area. This is created with the same columns as the target table so that it can be passed to the CKM for flow control.
Insert data in the loading table from the sources and loading tables using a single INSERT/SELECT statement similar to the one loading the target in the append strategy.
Call the CKM for flow control. The CKM will evaluate every constraint defined for the target table on the integration table data. It will create an error table and insert the erroneous records into this table. It will also remove erroneous records from the integration table.
After the CKM completes, the integration table will only contain valid records. Inserting them in the target table can then be done safely.
Remove all records from the target table. This step can be made dependent on an option value set by the designer of the interface
Append the records from the integration table to the target table in a single INSERT/SELECT statement.
Commit the transaction.
Drop the temporary integration table.
Error Recycling
In some cases, it is useful to recycle errors from previous runs so that they are added to the flow and applied again to the target. This method can be useful for example when receiving daily sales transactions that reference product IDs that may not exist. Suppose that a sales record is rejected in the error table because the referenced product ID does not exist in the product table. This happens during the first run of the interface. In the meantime the missing product ID is created by the data administrator. Therefore the rejected record becomes valid and should be re-applied to the target during the next execution of the interface.
This mechanism is implement IKMs by an extra task that inserts all the rejected records of the previous executions of this interface from the error table into integration table. This operation is made prior to calling the CKM to check the data quality, and is conditioned by a KM option usually called RECYCLE_ERRORS.
The Incremental Update strategy is used to integrate data in the target table by comparing the records of the flow with existing records in the target according to a set of columns called the "update key". Records that have the same update key are updated when their associated data is not the same. Those that don't yet exist in the target are inserted. This strategy is often used for dimension tables when there is no need to keep track of the records that have changed.
The challenge with such IKMs is to use set-oriented SQL based programming to perform all operations rather than using a row-by-row approach that often leads to performance issues. The most common method to build such strategies often relies on the integration table ("I$") which stores the transformed source sets. This method is described below:
Drop (if it exists) and create the integration table in the staging area. This is created with the same columns as the target table so that it can be passed to the CKM for flow control. It also contains an
IND_UPDATE column that is used to flag the records that should be inserted ("I") and those that should be updated ("U").
Transform and insert data in the loading table from the sources and loading tables using a single INSERT/SELECT statement. The
IND_UPDATE column is set by default to "I".
Recycle the rejected records from the previous run to the integration table if the RECYCLE_ERROR KM option is selected.
Call the CKM for flow control. The CKM will evaluate every constraint defined for the target table on the integration table data. It will create an error table and insert the erroneous records into this table. It will also remove erroneous records from the integration table.
Update the integration table to set the
IND_UPDATE flag to "U" for all the records that have the same update key values as the target ones. Therefore, records that already exist in the target will have a "U" flag. This step is usually an UPDATE/SELECT statement.
Update the integration table again to set the
IND_UPDATE column to "N" for all records that are already flagged as "U" and for which the column values are exactly the same as the target ones. As these flow records match exactly the target records, they don't need to be used to update the target data.
After this step, the integration table is ready for applying the changes to the target as it contains records that are flagged:
"I": these records should be inserted into the target.
"U": these records should be used to update the target.
"N": these records already exist in the target and should be ignored.
Update the target with records from the integration table that are flagged "U". Note that the update statement is typically executed prior to the INSERT statement to minimize the volume of data manipulated.
Insert records in the integration table that are flagged "I" into the target.
Commit the transaction.
Drop the temporary integration table.
This approach can be optimized depending on the underlying database. The following examples illustrate such optimizations:
With Teradata, it may be more efficient to use a left outer join between the flow data and the target table to populate the integration table with the
IND_UPDATE column already set properly.
With Oracle, it may be more efficient in some cases to use a MERGE INTO statement on the target table instead of an UPDATE then INSERT.
Update Key
The update key should always be unique. In most cases, the primary key will be used as an update key. The primary key cannot be used, however, when it is automatically calculated using an increment such as an identity column, a rank function, or a sequence. In this case an update key based on columns present in the source must be used.
Comparing Nulls
When comparing data values to determine what should not be updated, the join between the integration table and the target table is expressed on each column as follow:
<target_table>.ColumnN = <loading_table>.ColumnN or (<target_table> is null and <loading_table>.ColumnN is null)
This is done to allow comparison between null values, so that a null value matches another null value. A more elegant way of writing it would be to use the coalesce function. Therefore the WHERE predicate could be written this way:
<%=odiRef.getColList("","coalesce(" + odiRef.getTable("L", "INT_NAME", "A") + ".[COL_NAME], 0) = coalesce(T.[COL_NAME], 0)", " \nand\t", "", "((UPD and !TRG) and !UK) ")%>
Column-Level Insert/Update Behavior
Columns updated by the UPDATE statement are not the same as the ones used in the INSERT statement. The UPDATE statement uses selector "UPD and not UK" to filter only mappings marked as "Update" in the interface and that do not belong to the update key. The INSERT statement uses selector "INS" to retrieve mappings marked as "insert" in the interface.
It is important that the UPDATE and the INSERT statements on the target belong to the same transaction. Should any of them fail, no data will be inserted or updated in the target.
Type 2 Slowly Changing Dimension (SCD) is a strategy used for loading data warehouses. It is often used for loading dimension tables, in order to keep track of changes on specific columns. A typical slowly changing dimension table would contain the flowing columns:
A surrogate key. This is usually a numeric column containing an automatically-generated number (using an identity column, a rank function or a sequence).
A natural key. This is the list of columns that represent the primary key of the operational system.
Columns that one must overwritten on change.
Columns that require to add row on change.
A starting timestamp column indicating when the record was created in the data warehouse
An ending timestamp column indicating when the record became obsolete (closing date)
A current record flag indicating whether the record is the actual one (1) or an old one (0)
The following example illustrate the Slowly Changing Dimension behavior.
In the operational system, a product is defined by its ID that acts as a primary key. Every product has a name, a size, a supplier and a family. In the Data Warehouse a new version of this product is stored whenever the supplier or the family is updated in the operational system.
In this example, the product dimension is first initialized in the Data Warehouse on March 12, 2006. All the records are inserted and are assigned a calculated surrogate key as well as a fake ending date set to January 1, 2400. As these records represent the current state of the operational system, their current record flag is set to 1. After the first load, the following changes happen in the operational system:
The supplier is updated for product P1
The family is updated for product P2
The name is updated for product P3
Product P5 is added
These updates have the following impact on the data warehouse dimension:
The update of the supplier of P1 is translated into the creation of a new current record (Surrogate Key 5) and the closing of the previous record (Surrogate Key 1)
The update of the family of P2 is translated into the creation of a new current record (Surrogate Key 6) and the closing of the previous record (Surrogate Key 2)
The update of the name of P3 simply updates the target record with Surrogate Key 3
The new product P5 is translated into the creation of a new current record (Surrogate Key 7).
To create a Knowledge Module that implements this behavior, it is necessary to know which columns act as a surrogate key, a natural key, a start date etc. Oracle Data Integrator stores this information in Slowly Changing Dimension Behavior field in the Description tab for every column in the model.
When populating such a datastore in an interface, the IKM has access to this metadata using the SCD_xx selectors on the getColList() substitution method.
The way Oracle Data Integrator implements Type 2 Slowly Changing Dimensions is described below:
Drop (if it exists) and create the integration table in the staging area.
Insert the flow data in the integration table using only mappings that apply to the natural key, overwrite on change and add row on change columns. Set the starting timestamp to the current date and the ending timestamp to a constant.
Recycle previous rejected records
Call the CKM to perform a data quality check on the flow
Flag the records in the integration table to 'U' when the natural key and the add row on change columns have not changed compared to the current records of the target.
Update the target with the columns flagged overwrite on change by using the integration table content filtered on the 'U' flag.
Close old records - those for which the natural key exists in the integration table, and set their current record flag to 0 and their ending timestamp to the current date
Insert the new changing records with their current record flag set to 1
Drop the integration table.
Again, this approach can be adapted. There may be some cases where the SQL produced requires further tuning and optimization.
These strategies are used when the staging area cannot be located on the same data server as the target datastore. This configuration is mainly used for data servers with no transformation capabilities (Files, for example). In this configuration, only simple integration strategies are possible
There are some cases when the source is a single file that can be loaded directly into the target table using the most efficient method. By default, Oracle Data Integrator suggests to locate the staging area on the target server, use a LKM to stage the source file in a loading table and then use an IKM to integrate the loaded data to the target table.
If the source data is not transformed, the loading phase is not necessary.
In this situation you would use an IKM that directly loads the file data to the target: This requires setting the staging area on the source file logical schema. By doing this, Oracle Data Integrator will automatically suggest to use a "Multi-Connection" IKM that moves data between a remote staging area and the target.
Such an IKM would use a loader, and include the following steps:
Generate the appropriate load utility script
Run the loader utility
An example of such KM is the IKM File to Teradata (TTU).
When using a staging area different from the target and when setting this staging area to an RDBMS, it is possible to use an IKM that moves the transformed data from the staging area to the remote target. This type of IKM is similar to a LKM and follows the same rules.
The steps when using the agent are usually:
Delete (or truncate) all records from the target table. This step usually depends on a KM option.
Insert the data from the staging area to the target. This step has a SELECT statement in the "Command on Source" tab that will be executed on the staging area. The INSERT statement is written using bind variables in the "Command on Target" tab and will be executed for every batch on the target table.
The IKM SQL to SQL Append is a typical example of such KM.
Variation of this strategy use loaders or database specific methods for loading data from the staging area to the target instead of the agent.
When the target datastore is a file or JMS queue or topic the staging area is set on a different location than the target. Therefore, if you want to target a file or queue datastore you will have to use a "Multi-Connection" IKM that will integrate the transformed data from your staging area to this target. The method to perform this data movement depends on the target technology. For example, it is possible to use the agent or specific features of the target (such as a Java API)
Typical steps of such an IKM will include:
Reset the target file or queue made dependent on an option
Unload the data from the staging area to the file or queue
This section provides example of integration strategies and customizations.
The simplest strategy for integrating data in an existing target table, provided that all source data is already in the staging area is to replace and insert the records in the target. Therefore, the simplest IKM would be composed of 2 steps:
Remove all records from the target table. This step can be made dependent on an option set by the designer of the interface
Transform and insert source records from all datasets. When dealing with remote source data, LKMs will have already prepared loading tables with pre-transformed result sets. If the interface uses source data sets on the same server as the target (and the staging area as well), they will be joined to the other loading tables. Therefore the integration operation will be a straight INSERT/SELECT statement leveraging all the transformation power of the target Teradata box.
The following example gives you the details of these steps:
This task deletes the data from the target table. This command runs in a transaction and is not committed. It is executed if the DELETE_ALL Knowledge Module option is selected.
Command on Target
delete from <%=odiRef.getTable("L","INT_NAME","A")%>
This task insert rows from the staging table into the target table. This command runs in the same transaction as all operations made on the target and is not committed. A final Commit transaction command triggers the commit on the target.
Note that this commands selects the data from the different datasets defined for the interface. Using a for loop, it goes through all the datasets, generates for each dataset a SELECT query. These queries are merged using set-based operations (UNION, INTERSECT, etc.) and the resulting data flow is inserted into the target table.
Command on Target
insert into <%=odiRef.getTable("L","TARG_NAME","A")%> ( <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%> <%=odiRef.getColList(",", "[COL_NAME]", ",\n\t", "", "((INS and TRG) and REW)")%> ) select <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%> <%=odiRef.getColList(",", "[EXPRESSION]", ",\n\t", "", "((INS and TRG) and REW)")%> FROM ( <%for (int i=0; i < odiRef.getDataSetCount(); i++){%><%=odiRef.getDataSet(i, "Operator")%>select <%=odiRef.getPop("DISTINCT_ROWS")%> <%=odiRef.getColList(i,"", "[EXPRESSION] [COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%> from <%=odiRef.getFrom(i)%>where <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %> JRN_FLAG <> 'D '<%} else {%> (1=1) <% } %><%=odiRef.getJoin(i)%><%=odiRef.getFilter(i)%><%=odiRef.getJrnFilter(i)%><%=odiRef.getGrpBy(i)%><%=odiRef.getHaving(i)%><%}%>)
A project requirements is to backup every data warehouse table prior to loading the current data. This can help restoring the data warehouse to its previous state in case of a major problem. The backup tables are called like the data table with a "_BCK" suffix.
A first solution to this requirement would be to develop interfaces that would duplicate data from every target datastore to its corresponding backup one. These interfaces would be triggered prior to the ones that would populate the data warehouse. Unfortunately, this solution would lead to significant development and maintenance effort as it requires the creation of an additional interface for every target datastore. The number of interfaces to develop and maintain would be at least doubled!
A simple solution would be to implement this behavior in the IKM used to populate the target datastores. This would be done using a single CREATE AS SELECT statement that creates and populates to the backup table right before modifying the target. Therefore, the backup operation becomes automatic and the developers would no longer need to worry about it.
This example shows how this behavior could be implemented in the IKM Oracle Incremental Update.
Before the Update Existing Rows and Insert New Rows tasks that modify the target, the following tasks are added.
This task drops the backup table.
Command on Target
Drop table <%=odiRef.getTable("L","TARG_NAME","A")%>_BCK
Some data warehousing projects could require keeping track of every insert or update operation done to target tables for regulatory compliance. This could help business analysts understand what happened to their data during a certain period of time.
Even if you can achieve this behavior by using the slowly changing dimension Knowledge Modules, it can also be done by simply creating a copy of the flow data before applying it to the target table.
Suppose that every target table has a corresponding tracking table with a "_RGG" suffix with all the data columns plus some additional regulatory compliance columns such as:
The Job Id
The Job Name
Date and time of the operation
The type of operation ("Insert" or "Update")
You would populate this table directly from the integration table after applying the inserts and updates to the target, and before the end of the IKM.
For example, in the case of the Oracle Incremental Update IKM, you would add the following tasks just after the Update Existing Rows and Insert New Rows tasks that modify the target.
This task loads data in the tracking table.
Command on Target
insert into <%=odiRef.getTable("L","TARG_NAME","A")%>_RGC(JOBID,JOBNAME,OPERATIONDATE,OPERATIONTYPE,<%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "")%>)select <%=odiRef.getSession("SESS_NO")%> /* JOBID */,<%=odiRef.getSession("SESS_NAME")%> /* JOBNAME */,Current_timestamp /* OPERATIONDATE */,Case when IND_UPDATE = 'I' then 'Insert' else 'Update' end /* OPERATIONTYPE */,<%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "")%>from <%=odiRef.getTable("L","INT_NAME","A")%>where IND_UPDATE <> 'N'
This customization could be extended of course by creating automatically the tracking table using the IKM if it does not exist yet.
This chapter provides an introduction to Knowledge Modules (KM). It explains what is a knowledge module, and describes the different types of KMs.
This chapter includes the following sections:
Knowledge Modules (KMs) are code templates. Each KM is dedicated to an individual task in the overall data integration process. The code in the KMs appears in nearly the form that it will be executed except that it includes Oracle Data Integrator (ODI) substitution methods enabling it to be used generically by many different integration jobs. The code that is generated and executed is derived from the declarative rules and metadata defined in the ODI Designer module.
A KM will be reused across several interfaces or models. To modify the behavior of hundreds of jobs using hand-coded scripts and procedures, developers would need to modify each script or procedure. In contrast, the benefit of Knowledge Modules is that you make a change once and it is instantly propagated to hundreds of transformations. KMs are based on logical tasks that will be performed. They don't contain references to physical objects (datastores, columns, physical paths, etc.)
KMs can be analyzed for impact analysis.
KMs can't be executed standalone. They require metadata from interfaces, datastores and models.
KMs fall into 6 different categories as summarized in the table below:
|Knowledge Module
|Reverse-engineering KM
|Retrieves metadata to the Oracle Data Integrator work repository
|Used in models to perform a customized reverse-engineering
|Check KM
|Checks consistency of data against constraints
|Loading KM
|Loads heterogeneous data to a staging area
|Used in interfaces with heterogeneous sources
|Integration KM
|Integrates data from the staging area to a target
|Used in interfaces
|Journalizing KM
|Creates the Change Data Capture framework objects in the source staging area
|Used in models, sub models and datastores to create, start and stop journals and to register subscribers.
|Service KM
|Generates data manipulation web services
|Used in models and datastores
The following sections describe each type of Knowledge Module.
The RKM role is to perform customized reverse engineering for a model. The RKM is in charge of connecting to the application or metadata provider then transforming and writing the resulting metadata into Oracle Data Integrator's repository. The metadata is written temporarily into the SNP_REV_xx tables. The RKM then calls the Oracle Data Integrator API to read from these tables and write to Oracle Data Integrator's metadata tables of the work repository in incremental update mode. This is illustrated below:
A typical RKM follows these steps:
Cleans up the SNP_REV_xx tables from previous executions using the OdiReverseResetTable tool.
Retrieves sub models, datastores, columns, unique keys, foreign keys, conditions from the metadata provider to SNP_REV_SUB_MODEL, SNP_REV_TABLE, SNP_REV_COL, SNP_REV_KEY, SNP_REV_KEY_COL, SNP_REV_JOIN, SNP_REV_JOIN_COL, SNP_REV_COND tables.
Updates the model in the work repository by calling the OdiReverseSetMetaData tool.
The CKM is in charge of checking that records of a data set are consistent with defined constraints. The CKM is used to maintain data integrity and participates in the overall data quality initiative. The CKM can be used in 2 ways:
To check the consistency of existing data. This can be done on any datastore or within interfaces, by setting the STATIC_CONTROL option to "Yes". In the first case, the data checked is the data currently in the datastore. In the second case, data in the target datastore is checked after it is loaded.
To check consistency of the incoming data before loading the records to a target datastore. This is done by using the FLOW_CONTROL option. In this case, the CKM simulates the constraints of the target datastore on the resulting flow prior to writing to the target.
In summary: the CKM can check either an existing table or the temporary "I$" table created by an IKM.
The CKM accepts a set of constraints and the name of the table to check. It creates an "E$" error table which it writes all the rejected records to. The CKM can also remove the erroneous records from the checked result set.
The following figures show how a CKM operates in both STATIC_CONTROL and FLOW_CONTROL modes.
In STATIC_CONTROL mode, the CKM reads the constraints of the table and checks them against the data of the table. Records that don't match the constraints are written to the "E$" error table in the staging area.
In FLOW_CONTROL mode, the CKM reads the constraints of the target table of the Interface. It checks these constraints against the data contained in the "I$" flow table of the staging area. Records that violate these constraints are written to the "E$" table of the staging area.
In both cases, a CKM usually performs the following tasks:
Create the "E$" error table on the staging area. The error table should contain the same columns as the datastore as well as additional columns to trace error messages, check origin, check date etc.
Isolate the erroneous records in the "E$" table for each primary key, alternate key, foreign key, condition, mandatory column that needs to be checked.
If required, remove erroneous records from the table that has been checked.
An LKM is in charge of loading source data from a remote server to the staging area. It is used by interfaces when some of the source datastores are not on the same data server as the staging area. The LKM implements the declarative rules that need to be executed on the source server and retrieves a single result set that it stores in a "C$" table in the staging area, as illustrated below.
The LKM creates the "C$" temporary table in the staging area. This table will hold records loaded from the source server.
The LKM obtains a set of pre-transformed records from the source server by executing the appropriate transformations on the source. Usually, this is done by a single SQL SELECT query when the source server is an RDBMS. When the source doesn't have SQL capacities (such as flat files or applications), the LKM simply reads the source data with the appropriate method (read file or execute API).
The LKM loads the records into the "C$" table of the staging area.
An interface may require several LKMs when it uses datastores from different sources. When all source datastores are on the same data server as the staging area, no LKM is required.
The IKM is in charge of writing the final, transformed data to the target table. Every interface uses a single IKM. When the IKM is started, it assumes that all loading phases for the remote servers have already carried out their tasks. This means that all remote source data sets have been loaded by LKMs into "C$" temporary tables in the staging area, or the source datastores are on the same data server as the staging area. Therefore, the IKM simply needs to execute the "Staging and Target" transformations, joins and filters on the "C$" tables, and tables located on the same data server as the staging area. The resulting set is usually processed by the IKM and written into the "I$" temporary table before loading it to the target. These final transformed records can be written in several ways depending on the IKM selected in your interface. They may be simply appended to the target, or compared for incremental updates or for slowly changing dimensions. There are 2 types of IKMs: those that assume that the staging area is on the same server as the target datastore, and those that can be used when it is not. These are illustrated below:
When the staging area is on the target server, the IKM usually follows these steps:
The IKM executes a single set-oriented SELECT statement to carry out staging area and target declarative rules on all "C$" tables and local tables (such as D in the figure). This generates a result set.
Simple "append" IKMs directly write this result set into the target table. More complex IKMs create an "I$" table to store this result set.
If the data flow needs to be checked against target constraints, the IKM calls a CKM to isolate erroneous records and cleanse the "I$" table.
The IKM writes records from the "I$" table to the target following the defined strategy (incremental update, slowly changing dimension, etc.).
The IKM drops the "I$" temporary table.
Optionally, the IKM can call the CKM again to check the consistency of the target datastore.
These types of KMs do not manipulate data outside of the target server. Data processing is set-oriented for maximum efficiency when performing jobs on large volumes.
When the staging area is different from the target server, as shown in Figure 1-6, the IKM usually follows these steps:
The IKM executes a single set-oriented SELECT statement to carry out declarative rules on all "C$" tables and tables located on the staging area (such as D in the figure). This generates a result set.
The IKM loads this result set into the target datastore, following the defined strategy (append or incremental update).
This architecture has certain limitations, such as:
A CKM cannot be used to perform a data integrity audit on the data being processed.
Data needs to be extracted from the staging area before being loaded to the target, which may lead to performance issues.
JKMs create the infrastructure for Change Data Capture on a model, a sub model or a datastore. JKMs are not used in interfaces, but rather within a model to define how the CDC infrastructure is initialized. This infrastructure is composed of a subscribers table, a table of changes, views on this table and one or more triggers or log capture programs as illustrated below.
SKMs are in charge of creating and deploying data manipulation Web Services to your Service Oriented Architecture (SOA) infrastructure. SKMs are set on a Model. They define the different operations to generate for each datastore's web service. Unlike other KMs, SKMs do no generate an executable code but rather the Web Services deployment archive files. SKMs are designed to generate Java code using Oracle Data Integrator's framework for Web Services. The code is then compiled and eventually deployed on the Application Server's containers.
The first guidelines when developing your own KM is to never start from a blank page.
Oracle Data Integrator provides a large number of knowledge modules out-of-the-box. It is recommended that you start by reviewing the existing KMs and start from an existing KM that is close to your use case. Duplicate this KM and customize it by editing the code.
When developing your own KM, keep in mind that it is targeted to a particular stage of the integration process. As a reminder:
LKMs are designed to load remote source data sets to the staging area into Loading ("C$") tables.
IKMs apply the source flow from the staging area to the target. They start from the Loading tables, may transform and join them into a single integration table ("I$") table, may call a CKM to perform data quality checks on this integration table, and finally write the flow data to the target
CKMs check data quality in a datastore or a integration table ("I$") against data quality rules expressed as constraints. The rejected records are stored in the error table ("E$")
RKMs are in charge of extracting metadata from a metadata provider to the Oracle Data Integrator repository by using the SNP_REV_xx temporary tables.
JKMs are in charge of creating and managing the Change Data Capture infrastructure.
Be also aware of these common pitfalls:
Avoid creating too many KMs: A typical project requires less than 5 KMs! Do not confuse KMs and procedures, and do not create one KM for each specific use case. Similar KMs can be merged into a single one and parameterized using options.
Avoid hard-coded values, including catalog or schema names in KMs: You should instead use the substitution methods getTable(), getTargetTable(), getObjectName(), knowledge module options or others as appropriate.
Avoid using variables in KMs: You should instead use options or flex fields to gather information from the designer.
Writing the KM entirely in Jython, Groovy or Java: You should do that if it is the appropriate solution (for example, when sourcing from a technology that only has a Java API). SQL is easier to read, maintain and debug than Java, Groovy or Jython code.
Using <%if%> statements rather than a check box option to make code generation conditional.
Other common code writing recommendations that apply to KMs:
The code should be correctly indented.
The generated code should also be indented in order to be readable.
SQL keywords such as "select", "insert", etc. should be in lowercase for better readability.
This chapter provides an introduction to the Oracle Data Integrator Substitution API using examples.
This chapter includes the following sections:
Section 2.5, "Working with Lists of Tables, Columns and Expressions"
Note:The substitution API methods are listed in Appendix A, "Substitution API Reference".
KMs are written as templates by using the Oracle Data Integrator substitution API. The API methods are java methods that return a string value. They all belong to a single object instance named "odiRef". The same method may return different values depending on the type of KM that invokes it. That's why they are classified by type of KM.
To understand how this API works, the following example illustrates how you would write a create table statement in a KM and what it would generate depending on the datastores it would deal with:
The following code is entered in a KM:
CREATE TABLE <%=odiRef.getTable("L", "INT_NAME", "A")%>(<%=odiRef.getColList("", "\t[COL_NAME] [DEST_CRE_DT]", ",\n", "", "")%>)
The generated code for the
PRODUCT table is:
CREATE TABLE db_staging.I$_PRODUCT( P RODUCT_ID numeric(10), PRODUCT_NAME varchar(250), FAMILY_ID numeric(4), SKU varchar(13), LAST_DATE timestamp)
The generated code for the
CUSTOMER table is:
CREATE TABLE db_staging.I$_CUSTOMER( CUST_ID numeric(10), CUST_NAME varchar(250), ADDRESS varchar(250), CITY varchar(50), ZIP_CODE varchar(12), COUNTRY_ID varchar(3))
As you can see, once executed with appropriate metadata, the KM has generated a different code for the product and customer tables.
The following topics cover some of the main substitution APIs and their use within KMs. Note that for better readability the tags "<%" and "%>" as well as the "odiRef" object reference are omitted in the examples.
The methods that are accessible from the Knowledge Modules and from the procedures are direct calls to Oracle Data Integrator methods implemented in Java. These methods are usually used to generate some text that corresponds to the metadata stored into the Oracle Data Integrator repository.
The substitution methods are used in any text of a task of a Knowledge Module or of a procedure.
They can be used within any text using the following syntax
In this syntax:
<%= %> tags are used to output the text returned by
java_expression. This syntax is very close to the syntax used in Java Server Pages (JSP).
Java expression is any Java expression that returns a string.
The following syntax performs a call to the
getTable method of the
odiRef java object using three parameters. This method call returns a string. That is written after the
CREATE TABLE text.
CREATE TABLE <%=odiRef.getTable("L", "INT_NAME", "A")%>
The Oracle Data Integrator Substitution API is implemented in the Java class
OdiReference, whose instance
OdiRef is available at any time. For example, to call a method called
getFrom(), you have to write
Note:For backward compatibility, the "odiRef" API can also be referred to as "snpRef" API. "snpRef" and "odiRef" object instances are synonyms, and the legacy syntax syntax
snpRef.<method_name> is still supported but deprecated.
The following syntax is used in an IKM to call the execution of a check procedure (CKM).
This syntax automatically includes all the CKM procedure commands at this point of in the processing.
The options for this syntax are:
CKM_FLOW: triggers a flow control, according to the CKM choices made in the Control tab of the Interface.
CKM_STATIC: Triggers a static control of the target datastore. Constraints defined for the datastore and selected as Static constraints will be checked.
DELETE_ERRORS: This option causes automatic suppression of the errors detected.
For example: the following call triggers a flow control with error deletion.
Flexfields are user-defined fields enabling to customize the properties of Oracle Data Integrator' objects. Flexfields are defined on the Flexfield tab of the object window and can be set for each object instance through the Flexfield tab of the object window.
When accessing an object properties through Oracle Data Integrator' substitution methods, if you specify the flexfield Code, Oracle Data Integrator will substitute the Code by the flexfield value for the object instance.
For instance:
<%=odiRef.getTable("L", "MY_DATASTORE_FIELD", "W")%> will return the value of the flexfield
MY_DATASTORE_FIELD for the current table.
<%=odiRef.getSrcTableList("", "[MY_DATASTORE_FIELD] ", ", ", "")%> will return the flexfield value for each of the source tables of the interface.
It is also possible to get the value of a flexfield through the getFlexFieldValue() method.
Note:Flexfields exist only for certain object types. Objects that do not have a Flexfield tab do not support flexfields.
An action corresponds to a DDL operation (create table, drop reference, etc) used to generate a procedure to implement in a database the changes performed in a data integrator model (Generate DDL operation). Each action contains several Action Lines, corresponding to the commands required to perform the DDL operation (for example, dropping a table requires dropping all its constraints first).
Action lines contain statements valid for the technology of the action group. Unlike procedures or knowledge module commands, these statements use a single connection (SELECT ... INSERT statements are not possible). In the style of the knowledge modules, action make use of the substitution methods to make their DDL code generic.
For example, an action line may contain the following code to drop a check constraint on a table:
ALTER TABLE <%=odiRef.getTable("L", "TARG_NAME", "A") %> DROP CONSTRAINT <%=odiRef.getCK("COND_NAME") %>
The Action Calls methods are usable in the action lines only. Unlike other substitution methods, they are not used to generate text, but to generate actions appropriate for the context.
For example, to perform the a Drop Table DDL operation, we must first drop all foreign keys referring to the table.
In the Drop Table action, the first action line will use the dropReferringFKs() action call method to automatically generate a Drop Foreign Key action for each foreign key of the current table. This call is performed by creating an action line with the following code:
<% odiRef.dropReferringFKs(); %>
The syntax for calling the action call methods is:
<% odiRef.method_name(); %>
Note:The action call methods must be alone in an action line, should be called without a preceding "=" sign, and require a trailing semi-colon.
The following Action Call Methods are available for Actions:
addAKs(): Call the Add Alternate Key action for all alternate keys of the current table.
dropAKs(): Call the Drop Alternate Key action for all alternate keys of the current table.
addPK(): Call the Add Primary Key for the primary key of the current table.
dropPK(): Call the Drop Primary Key for the primary key of the current table.
createTable(): Call the Create Table action for the current table.
dropTable(): Call the Drop Table action for the current table.
addFKs(): Call the Add Foreign Key action for all the foreign keys of the current table.
dropFKs(): Call the Drop Foreign Key action for all the foreign keys of the current table.
enableFKs(): Call the Enable Foreign Key action for all the foreign keys of the current table.
disableFKs(): Call the Disable Foreign Key action for all the foreign keys of the current table.
addReferringFKs(): Call the Add Foreign Key action for all the foreign keys pointing to the current table.
dropReferringFKs(): Call the Drop Foreign Key action for all the foreign keys pointing to the current table.
enableReferringFKs(): Call the Enable Foreign Key action for all the foreign keys pointing to the current table.
disableReferringFKs(): Call the Disable Foreign Key action for all the foreign keys pointing to the current table.
addChecks(): Call the Add Check Constraint action for all check constraints of the current table.
dropChecks(): Call the Drop Check Constraint action for all check constraints of the current table.
addIndexes(): Call the Add Index action for all the indexes of the current table.
dropIndexes(): Call the Drop Index action for all the indexes of the current table.
modifyTableComment(): Call the Modify Table Comment for the current table.
AddColumnsComment(): Call the Modify Column Comment for all the columns of the current table.
When working in Designer, you should avoid specifying physical information such as the database name or schema name as they may change depending on the execution context. The correct physical information will be provided by Oracle Data Integrator at execution time.
The substitution API has methods that calculate the fully qualified name of an object or datastore taking into account the context at runtime. These methods are listed in the table below:
|Qualified Name Required
|Usable In
|Any object named OBJ_NAME
|getObjectName("L", "OBJ_NAME", "D")
|The target datastore of the current interface
|getTable("L", "TARG_NAME", "A")
|The integration (I$) table of the current interface.
|getTable("L", "INT_NAME", "A")
|The loading table (C$) for the current loading phase.
|getTable("L", "COLL_NAME", "A")
|The error table (E$) for the datastore being checked.
|getTable("L", "ERR_NAME", "A")
|The datastore being checked
|getTable("L", "CT_NAME", "A")
|The datastore referenced by a foreign key
|getTable("L", "FK_PK_TABLE_NAME", "A")
Generating code from a list of items often requires a "while" or "for" loop. Oracle Data Integrator addresses this issue by providing powerful methods that help you generate code based on lists. These methods act as "iterators" to which you provide a substitution mask or pattern and a separator and they return a single string with all patterns resolved separated by the separator.
All of them return a string and accept at least these 4 parameters:
Start: a string used to start the resulting string.
Pattern: a substitution mask with attributes that will be bound to the values of each item of the list.
Separator: a string used to separate each substituted pattern from the following one.
End: a string appended to the end of the resulting string
Some of them accept an additional parameter (the Selector) that acts as a filter to retrieve only part of the items of the list. For example, list only the mapped column of the target datastore of an interface.
Some of these methods are summarized in the table below:
|Usable In
|The most frequently-used method in Oracle Data Integrator. It returns a list of columns and expressions that need to be executed in the context where used. You can use it, for example, to generate lists like these:
This method accepts a "selector" as a 5th parameter to let you filter items as desired.
|Returns the list of columns in the target datastore.
This method accepts a selector as a 5th parameter to let you filter items as desired.
|Returns the list of columns defined for an alternate key.
|Returns the list of columns in a primary key. You can alternatively use getColList with the selector parameter set to "PK" .
|Returns the list of referencing columns and referenced columns of the current foreign key.
|Returns the list of source tables of an interface. Whenever possible, use the getFrom method instead. The getFrom method is discussed below.
|Returns the list of filter expressions in an interface. The getFilter method is usually more appropriate.
|Returns the list of join expressions in an interface. The getJoin method is usually more appropriate.
|Returns the list of expressions that should appear in the group by clause when aggregate functions are detected in the mappings of an interface. The getGrpBy method is usually more appropriate.
|Returns the list of expressions that should appear in the having clause when aggregate functions are detected in the filters of an interface. The getHaving method is usually more appropriate.
|Returns a list of subscribers.
The following section provide examples illustrating how these methods work for generating code:
The following example shows how to use a column list to create a table.
The following KM code:
Create table MYTABLE <%=odiRef.getTargetColList("(\n", "\t[COL_NAME] [DEST_WRI_DT]", ",\n", "\n)")%>
Generates the following statement:
Create table MYTABLE ( CUST_ID numeric(10), CUST_NAME varchar(250), ADDRESS varchar(250), CITY varchar(50), ZIP_CODE varchar(12), COUNTRY_ID varchar(3) )
In this example:
Start is set to "(\n": The generated code will start with a parenthesis followed by a carriage return (\n).
Pattern is set to "\t[COL_NAME] [DEST_WRI_DT]": The generated code will loop over every target column and generate a tab character (\t) followed by the column name ([COL_NAME]), a white space and the destination writable data type ([DEST_WRI_DT]).
The Separator is set to ",\n": Each generated pattern will be separated from the next one with a comma (,) and a carriage return (\n)
End is set to "\n)": The generated code will end with a carriage return (\n) followed by a parenthesis.
The following example shows how to use column listing to insert values into a table.
For following KM code:
insert into MYTABLE ( <%=odiRef.getColList("", "[COL_NAME]", ", ", "\n", "INS AND NOT TARG")%> <%=odiRef.getColList(",", "[COL_NAME]", ", ", "", "INS AND TARG")%> ) Values ( <%=odiRef.getColList("", ":[COL_NAME]", ", ", "\n", "INS AND NOT TARG")%> <%=odiRef.getColList(", ", "[EXPRESSION]", ", ", "", "INS AND TARG")%> )
Generates the following statement:
insert into MYTABLE ( CUST_ID, CUST_NAME, ADDRESS, CITY, COUNTRY_ID , ZIP_CODE, LAST_UPDATE ) Values ( :CUST_ID, :CUST_NAME, :ADDRESS, :CITY, :COUNTRY_ID , 'ZZ2345', current_timestamp )
In this example, the values that need to be inserted into MYTABLE are either bind variables with the same name as the target columns or constant expressions if they are executed on the target. To obtain these 2 distinct set of items, the list is split using the Selector parameter:
"INS AND NOT TARG": first, generate a comma-separated list of columns ([COL_NAME]) mapped to bind variables in the "value" part of the statement (:[COL_NAME]). Filter them to get only the ones that are flagged to be part of the INSERT statement and that are not executed on the target.
"INS AND TARG": then generate a comma separated list of columns ([COL_NAME]) corresponding to expression ([EXPRESSION]) that are flagged to be part of the INSERT statement and that are executed on the target. The list should start with a comma if any items are found.
The following example concatenates the list of the source tables of an interface for logging purposes.
For following KM code:
insert into MYLOGTABLE ( INTERFACE_NAME, DATE_LOADED, SOURCE_TABLES ) values ( '<%=odiRef.getPop("POP_NAME")%>', current_date, '' <%=odiRef.getSrcTablesList("|| ", "'[RES_NAME]'", " || ',' || ", "")%> )
Generates the following statement:
insert into MYLOGTABLE ( INTERFACE_NAME, DATE_LOADED, SOURCE_TABLES ) values ( 'Int. CUSTOMER', current_date, '' || 'SRC_CUST' || ',' || 'AGE_RANGE_FILE' || ',' || 'C$0_CUSTOMER' )
In this example, getSrcTableList generates a message containing the list of resource names used as sources in the interface to append to MYLOGTABLE. The separator used is composed of a concatenation operator (||) followed by a comma enclosed by quotes (',') followed by the same operator again. When the table list is empty, the SOURCE_TABLES column of MYLOGTABLE will be mapped to an empty string ('').
LKMs and IKMs both manipulate a source result set. For the LKM, this result set represents the pre-transformed records according to the mappings, filters and joins that need to be executed on the source. For the IKM, however, the result set represents the transformed records matching the mappings, filters and joins executed on the staging area.
To build these result sets, you will usually use a SELECT statement in your KMs. Oracle Data Integrator has some advanced substitution methods, including getColList, that help you generate this code:
|Usable In
|Returns the FROM clause of a SELECT statement with the appropriate source tables, left, right and full outer joins. This method uses information from the topology to determine the SQL capabilities of the source or target technology. The FROM clause is built accordingly with the appropriate keywords (INNER, LEFT etc.) and parentheses when supported by the technology.
|Returns filter expressions separated by an "AND" operator.
|Returns the special journal filter expressions for the journalized source datastore. This method should be used with the CDC framework.
|Returns the GROUP BY clause when aggregation functions are detected in the mappings.
The GROUP BY clause includes all mapping expressions referencing columns that do not contain aggregation functions. The list of aggregation functions are defined by the language of the technology in the topology.
|Returns the HAVING clause when aggregation functions are detected in filters.
The having clause includes all filters expressions containing aggregation functions. The list of aggregation functions are defined by the language of the technology in the topology.
To obtain the result set from any SQL RDBMS source server, you would use the following SELECT statement in your LKM:
select <%=odiRef.getPop("DISTINCT_ROWS")%> <%=odiRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "", "")%> from <%=odiRef.getFrom()%> where (1=1) <%=odiRef.getFilter()%> <%=odiRef.getJrnFilter()%> <%=odiRef.getJoin()%> <%=odiRef.getGrpBy()%> <%=odiRef.getHaving()%>
To obtain the result set from any SQL RDBMS staging area server to build your final flow data, you would use the following SELECT statement in your IKM. Note that the getColList is filtered to retrieve only expressions that are not executed on the target and that are mapped to writable columns.
select <%=odiRef.getPop("DISTINCT_ROWS")%> <%=odiRef.getColList("", "[EXPRESSION]", ",\n\t", "", "(not TRG) and REW")%> from <%=odiRef.getFrom()%> where (1=1) <%=odiRef.getJoin()%> <%=odiRef.getFilter()%> <%=odiRef.getJrnFilter()%> <%=odiRef.getGrpBy()%> <%=odiRef.getHaving()%>
As all filters and joins start with an AND, the WHERE clause of the SELECT statement starts with a condition that is always true (1=1).
Oracle Data Integrator supports datasets. Each dataset represents a group of joined and filtered sources tables, with their mappings. Datasets are merged into the target datastore using set-based operators (UNION, INTERSECT, etc) at the integration phase.
During the loading phase, the LKM always works on one dataset. Duringthe integration phase, when all datasets need to merged, certain odiRef APIs that support working on a specific dataset are called using an index that identifies the dataset.
The following example explains how this dataset merging is done.
<%for (int i=0; i < odiRef.getDataSetCount(); i++){%> <%=odiRef.getDataSet(i, "Operator")%> select <%=odiRef.getUserExit("OPTIMIZER_HINT")%> <%=odiRef.getPop("DISTINCT_ROWS")%> <%=odiRef.getColList(i,"", "[EXPRESSION]", ",\n\t", "", "(((INS or UPD) and !TRG) and REW)")%>, <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %> JRN_FLAG IND_UPDATE <%} else {%> 'I' IND_UPDATE <%}%> from <%=odiRef.getFrom(i)%> where (1=1) <%=odiRef.getJoin(i)%> <%=odiRef.getFilter(i)%> <%=odiRef.getJrnFilter(i)%> <%=odiRef.getGrpBy(i)%> <%=odiRef.getHaving(i)%> <%}%>
A Java For loop iterates over the datasets. The number of datasets is retrieved using the getDataSetCount method. For each dataset, a SELECT statement is issued, each statement separated from the previous one by the dataset's set-based operator retrieved using the getDataSet method.
The select statement is build as in Generating the Source Select Statement, except that each method call is parameterized with i, the index of the dataset being processed. For example, getFrom(i) generates the from statement for the dataset identified by the value of i.
All the methods that support a parameter for the dataset index also support a syntax without this index value. Outside an IKM, then should be used without the dataset index. Within an IKM, if used without the dataset index, these method address the first dataset. This syntax is backward compatible with previous Oracle Data Integrator interfaces and knowledge modules.
The following methods provide additional information which may be useful:
|Usable In
|Returns information about the current interface.
|Returns information about the source or target server.
|Any procedure or KM
|Returns information about the current running session
|Any procedure or KM
|Returns the value of a particular option
|Any procedure or KM
|Returns information about a flex field value. Not that with the "List" methods, flex field values can be specified as part of the pattern parameter.
|Any procedure or KM
|Returns information about the CDC framework
|Returns information about the target table of an interface
|Returns information about the current model during a reverse-engineering process.
|Returns information about the current interface.
You can use conditional branching and advanced programming techniques to generate code. The code generation in Oracle Data Integrator is able to interpret any Java code enclosed between "<%" and "%>" tags.
The following examples illustrate how you can use these advanced techniques.
Using Java Variables and String Functions
The following KM Code creates a string variable and uses it in a substitution method call :
<% String myTableName; myTableName = "ABCDEF"; %> drop table <%=odiRef.getObjectName(myTableName.toLowerCase())%>
Generates the following:
drop table SCOTT.abcdef
Using a KM Option to Generate Code Conditionally
The following KM code generates code depending on the OPT001 option value.
<% String myOptionValue=odiRef.getOption("OPT001"); if (myOption.equals("TRUE")) { out.print("/* Option OPT001 is set to TRUE */"); } else { %> /* The OPT001 option is not properly set */ <% } %>
If OPT001 is set to TRUE, then the following is generated:
/* Option OPT001 is set to TRUE */
Otherwise the following is generated
/* The OPT001 option is not set to TRUE */
This chapter explains the data integrity strategies used for performing flow and static checks. These strategies are implemented in the Check Knowledge Modules.
This chapter contains the following sections:
Data Integrity Check Process checks is activated in the following cases:
When a Static Control is started (from Studio, or using a package) on a model, sub-model or datastore. The data in the datastores are checked against the constraints defined in the Oracle Data Integrator model.
If an interface is executed and a Flow Control is activated in the IKM. The flow data staged in the integration table (I$) is checked against the constraints of the target datastore, as defined in the model. Only those of the constraints selected in the interface are checked.
In both those cases, a CKM is in charge of checking the data quality of data according to a predefined set of constraints. The CKM can be used either to check existing data when used in a "static control" or to check flow data when used in a "flow control". It is also in charge of removing the erroneous records from the checked table if specified.
In the case of a static control, the CKM used is defined in the model. In the case of a flow control, it is specified for the interface.
Standard CKMs maintain 2 different types of tables:
A single summary table named SNP_CHECK_TAB for each data server, created in the work schema of the default physical schema of the data server. This table contains a summary of the errors for every table and constraint. It can be used, for example, to analyze the overall data quality of a model.
An error table named E$_<datastore name> for every datastore that was checked. The error table contains the actual records rejected by data quality processes (static and flow controls) launched for this table.
A standard CKM is composed of the following steps:
Drop and create the summary table. The DROP statement is executed only if the designer requires it for resetting the summary table. The CREATE statement is always executed but the error is tolerated if the table already exists.
Remove the summary records from the previous run from the summary table
Drop and create the error table. The DROP statement is executed only if the designer requires it for recreating the error table. The CREATE statement is always executed but error is tolerated if the table already exists.
Remove rejected records from the previous run from the error table
Reject records that violate the primary key constraint.
Reject records that violate any alternate key constraint
Reject records that violate any foreign key constraint
Reject records that violate any check condition constraint
Reject records that violate any mandatory column constraint
Remove rejected records from the checked table if required
Insert the summary of detected errors in the summary table.
CKM commands should be tagged to indicate how the code should be generated. The tags can be:
"Primary Key": The command defines the code needed to check the primary key constraint
"Alternate Key": The command defines the code needed to check an alternate key constraint. During code generation, Oracle Data Integrator will use this command for every alternate key
"Join": The command defines the code needed to check a foreign key constraint. During code generation, Oracle Data Integrator will use this command for every foreign key
"Condition": The command defines the code needed to check a condition constraint. During code generation, Oracle Data Integrator will use this command for every check condition
"Mandatory": The command defines the code needed to check a mandatory column constraint. During code generation, Oracle Data Integrator will use this command for mandatory column
"Remove Errors": The command defines the code needed to remove the rejected records from the checked table.
This section describes the typical structure of the Error and Summary Tables.
The E$ error table has the list of columns described in the following table:
|[Columns of the checked table]
|The error table contains all the columns of the checked datastore.
|Type of error:
|Error message related to the violated constraint
|Date and time when the datastore was checked
|Origin of the check operation. This column is set either to the datastore name or to an interface name and ID depending on how the check was performed.
|Name of the violated constraint.
|Type of the constraint:
The SNP_CHECK table has the list of columns described in the following table:
|Catalog name of the checked table, where applicable
|Schema name of the checked table, where applicable
|Resource name of the checked table
|Fully qualified name of the checked table. For example <catalog>.<schema>.<table>
|Type of error:
|Error message
|Date and time when the datastore was checked
|Origin of the check operation. This column is set either to the datastore name or to an interface name and ID depending on how the check was performed.
|Name of the violated constraint.
|Type of constraint:
|Total number of records rejected by this constraint during the check process
|ODI session number
|Unique identifier for this table, where appicable
This section provides examples of data integrity check strategies.
The CKM Oracle is a typical example of a data integrity check.
The commands below are extracted from the CKM for Oracle and provided as examples. You can review the code of this knowledge module by editing it in Oracle Data Integrator Studio.
This task drops the error summary table. This command runs only if the DROP_CHECK_TABLE is set to Yes, and has the Ignore Errors flag activated. It will not stop the CKM if the summary table is not found.
Command on Target (Oracle)
drop table <%=odiRef.getTable("L","CHECK_NAME","W")%> <% if (new Integer(odiRef.getOption( "COMPATIBLE" )).intValue() >= 10 ) { out.print( "purge" ); }; %>
This task creates the error summary table. This command always runs and has the Ignore Errors flag activated. It will not stop the CKM if the summary table already exist.
Command on Target (Oracle)
... create table <%=odiRef.getTable("L","CHECK_NAME","W")%> ( CATALOG_NAME <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%> , SCHEMA_NAME <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%> , RESOURCE_NAME <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, FULL_RES_NAME <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, ERR_TYPE <%=odiRef.getDataType("DEST_VARCHAR", "1", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, ERR_MESS <%=odiRef.getDataType("DEST_VARCHAR", "250", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%> , CHECK_DATE <%=odiRef.getDataType("DEST_DATE", "", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, ORIGIN <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, CONS_NAME <%=odiRef.getDataType("DEST_VARCHAR", "35", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, CONS_TYPE <%=odiRef.getDataType("DEST_VARCHAR", "2", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, ERR_COUNT <%=odiRef.getDataType("DEST_NUMERIC", "10", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%> ) ...
This task creates the error (E$) table. This command always runs and has the Ignore Errors flag activated. It will not stop the CKM if the error table already exist.
Note the use of the getCollist method to add the list of columns from the checked to this table structure.
Command on Target (Oracle)
... create table <%=odiRef.getTable("L","ERR_NAME", "W")%> ( ODI_ROW_ID UROWID, ODI_ERR_TYPE <%=odiRef.getDataType("DEST_VARCHAR", "1", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, ODI_ERR_MESS <%=odiRef.getDataType("DEST_VARCHAR", "250", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, ODI_CHECK_DATE <%=odiRef.getDataType("DEST_DATE", "", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, <%=odiRef.getColList("", "[COL_NAME]\t[DEST_WRI_DT] " + odiRef.getInfo("DEST_DDL_NULL"), ",\n\t", "", "")%>, ODI_ORIGIN <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, ODI_CONS_NAME <%=odiRef.getDataType("DEST_VARCHAR", "35", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, ODI_CONS_TYPE <%=odiRef.getDataType("DEST_VARCHAR", "2", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>, ODI_PK <%=odiRef.getDataType("DEST_VARCHAR", "32", "")%> PRIMARY KEY, ODI_SESS_NO <%=odiRef.getDataType("DEST_VARCHAR", "19", "")%> ) ...
This task inserts into the error (E$) table the errors detected while checking a primary key. This command always runs, has the Primary Key checkbox active and has Log Counter set to Error to count these records as errors.
Note:When using a CKM to perform flow control from an interface, you can define the maximum number of errors allowed. This number is compared to the total number of records returned by every command in the CKM of which the Log Counter is set to Error.
Note the use of the getCollist method to insert into the error table the whole record being checked and the use of the getPK and getInfo method to retrieve contextual information.
Command on Target (Oracle)
insert into <%=odiRef.getTable("L","ERR_NAME", "W")%> ( ODI_PK, ODI_SESS_NO, ODI_ROW_ID, ODI_ERR_TYPE, ODI_ERR_MESS, ODI_ORIGIN, ODI_CHECK_DATE, ODI_CONS_NAME, ODI_CONS_TYPE, <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "MAP")%> ) select SYS_GUID(), <%=odiRef.getSession("SESS_NO")%>, rowid, '<%=odiRef.getInfo("CT_ERR_TYPE")%>', '<%=odiRef.getPK("MESS")%>', '<%=odiRef.getInfo("CT_ORIGIN")%>', <%=odiRef.getInfo("DEST_DATE_FCT")%>, '<%=odiRef.getPK("KEY_NAME")%>', 'PK', <%=odiRef.getColList("", odiRef.getTargetTable("TABLE_ALIAS")+".[COL_NAME]", ",\n\t", "", "MAP")%> from <%=odiRef.getTable("L", "CT_NAME", "A")%> <%=odiRef.getTargetTable("TABLE_ALIAS")%> where exists ( select <%=odiRef.getColList("", "SUB.[COL_NAME]", ",\n\t\t\t", "", "PK")%> from <%=odiRef.getTable("L","CT_NAME","A")%> SUB where <%=odiRef.getColList("", "SUB.[COL_NAME]="+odiRef.getTargetTable("TABLE_ALIAS")+".[COL_NAME]", "\n\t\t\tand ", "", "PK")%> group by <%=odiRef.getColList("", "SUB.[COL_NAME]", ",\n\t\t\t", "", "PK")%> having count(1) > 1 ) <%=odiRef.getFilter()%>
This task removed from the controlled table (static control) or integration table (flow control) the rows detected as erroneous.
This task is always executed and has the Remove Errors option selected.
Command on Target (Oracle)
delete from <%=odiRef.getTable("L", "CT_NAME", "A")%> T where exists ( select 1 from <%=odiRef.getTable("L","ERR_NAME", "W")%> E where ODI_SESS_NO = <%=odiRef.getSession("SESS_NO")%> and T.rowid = E.ODI_ROW_ID )
The following use case describes an example of customization that can be performed on top of an existing CKM.
When loading a data warehouse, you may have records referencing data from other tables, but the referenced records do not yet exist.
Suppose, for example, that you receive daily sales transactions records that reference product SKUs. When a product does not exist in the products table, the default behavior of the standard CKM is to reject the sales transaction record into the error table instead of loading it into the data warehouse. However, to meet the requirements of your project you want to load this sales record into the data warehouse and create an empty product on the fly to ensure data consistency. The data analysts would then simply analyze the error tables and complete the missing information for products that were automatically added to the products table.
The following sequence illustrates this example:
The source flow data is staged by the IKM in the "I$_SALES" table to load the SALES table. The IKM calls the CKM to have it check the data quality.
The CKM checks every constraint including the FK_SALES_PRODUCTS foreign key defined between the target SALES table and the PRODUCTS Table. It rejects record with SALES_ID='4' in the error table as referenced product with PRODUCT_ID="P25" doesn't exist in the products table.
The CKM automatically inserts the missing "P25" product reference in the products table and assigns an '<unknown>' value to the PRODUCT_NAME. All other columns are set to null or default values.
The CKM does not remove the rejected record from the source flow I$ table, as it became consistent
The IKM writes the flow data to the target
In the sequence above, steps 3 and 4 differ from the standard CKM and need to be customized.
To implement such a CKM, you will notice that some information is missing in the Oracle Data Integrator default metadata. We would need the following:
A new flexfield called REF_TAB_DEF_COL on the Reference object containing the column of the referenced table that must be populated with the '<unknown>' value (PRODUCT_NAME, in our case)
A new column (ODI_AUTO_CREATE_REFS) in the error table to indicate whether an FK error needs to automatically create the missing reference or not. This flag will be populated while detecting the FK errors.
A new flexfields ca lled AUTO_CREATE_REFS on the "Reference" object, that will state whether a constraint should automatically cause missing references creation. See the Developer's Guide for Oracle Data Integrator for more information about Flex Fields.
Now that we have all the required metadata, we can start enhancing the default CKM to meet our requirements. The steps of the CKM will therefore be (changes are highlighted in bold font):
Drop and create the summary table.
Remove the summary records of the previous run from the summary table
Drop and create the error table. Add the extra ODI_AUTO_CREATE_REFS column to the error table.
Remove rejected records from the previous run from the error table
Reject records that violate the primary key constraint.
Reject records that violate each alternate key constraint
Reject records that violate each foreign key constraint, and store the value of the AUTO_CREATE_REFS flexfield in the ODI_AUTO_CREATE_REFS column.
For every foreign key error detected, if the ODI_AUTO_CREATE_REFS is set to "yes", insert missing references in the referenced table.
Reject records that violate each check condition constraint
Reject records that violate each mandatory column constraint
Remove rejected records from the checked table if required. Do not remove records for which the constraint behavior is set to Yes
Insert the summary of detected errors in the summary table.
The following command modifications are performed to implement the required changes to the CKM. The changes are highlighted in bold in the code.
The task is modified to create the new ODI_AUTO_CREATE_REFS column into the error table.
Command on Target (Oracle)
create table <%=odiRef.getTable("L","ERR_NAME", "W")%>
(ODI_AUTO_CREATE_REFS <%=odiRef.getDataType("DEST_VARCHAR", "3", "")%>
ODI_ERR_TYPE <%=odiRef.getDataType("DEST_VARCHAR", "1", "")%>
ODI_ERR_MESS <%=odiRef.getDataType("DEST_VARCHAR", "250", "")%>
ODI_CHECK_DATE <%=odiRef.getDataType("DEST_DATE", "", "")%>
<%=odiRef.getColList("", "[COL_NAME]\t[DEST_WRI_DT] " + odiRef.getInfo("DEST_DDL_NULL"), ",\n\t", "", "")%>,
ODI_ORIGIN <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%>
ODI_CONS_NAME <%=odiRef.getDataType("DEST_VARCHAR", "35", "")%>
ODI_CONS_TYPE <%=odiRef.getDataType("DEST_VARCHAR", "2", "")%>
ODI_PK <%=odiRef.getDataType("DEST_VARCHAR", "32", "")%> PRIMARY KEY,
ODI_SESS_NO <%=odiRef.getDataType("DEST_VARCHAR", "19", "")%>
The task is modified to take into account the new ODI_AUTO_CREATE_REFS column and load it with the content of the flexfield defined on the FK to indicate whether this constraint should automatically create missing references. Note the use of the getFK method to retrieve the value of the AUTO_CREATE_REFS flexfield.
Command on Target (Oracle)
... insert into <%=odiRef.getTable("L","ERR_NAME", "W")%> ( ODI_AUTO_CREATE_REFS, ODI_PK, ODI_SESS_NO, ODI_ROW_ID, ODI_ERR_TYPE, ODI_ERR_MESS, ODI_CHECK_DATE, ODI_ORIGIN, ODI_CONS_NAME, ODI_CONS_TYPE, <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "MAP")%> ) select '<%=odiRef.getFK("AUTO_CREATE_REFS")%>', SYS_GUID(), <%=odiRef.getSession("SESS_NO")%>, rowid, ...
The new task is added after the insert FK errors task. It has the Join option checked.
Note the following:
The getFK("AUTO_CREATE_FS") method is used to retrieve the AUTO_CREATE_FS flexfield value that conditions the generation of the SQL statement.
The getFK("REF_TAB_DEF_COL") method is used to retrieve from the flexfield the name of the column to set to '<undefined>'.
The getFKColList method is used to retrieve the list of columns participating to the foreign key and create the missing reference primary key columns content.
The filter made to retrieve only the records corresponding to the current checked foreign key constraint with the AUTO_CREATE_REFS flag set to Yes.
Command on Target (Oracle)
<% if (odiRef.getFK("AUTO_CREATE_REFS").equals("Yes")) { %> insert into <%=odiRef.getTable("L", "FK_PK_TABLE_NAME", "A")%> ( <%=odiRef.getFKColList("", "[PK_COL_NAME]", ",", "")%>, <%=odiRef.getFK("REF_TAB_DEF_COL")%> ) select distinct <%=odiRef.getFKColList("", "[COL_NAME]", ",", "")%>, '<UNKNOWN>' from <%=odiRef.getTable("L","ERR_NAME", "A")%> where CONS_NAME = '<%=odiRef.getFK("FK_NAME")%>' And CONS_TYPE = 'FK' And ORIGIN = '<%=odiRef.getInfo("CT_ORIGIN")%>' And AUTO_CREATE_REFS = 'Yes' <%}%>
This task is modified to avoid deleting the foreign key records for which a reference have been created. These can remain in the controlled table.
Command on Target (Oracle)
delete from <%=odiRef.getTable("L", "CT_NAME", "A")%> T
where exists (
select 1
from <%=odiRef.getTable("L","ERR_NAME", "W")%> E
where ODI_SESS_NO = <%=odiRef.getSession("SESS_NO")%>
and T.rowid = E.ODI_ROW_ID and E.AUTO_CREATE_REFS <> 'Yes'


text:
- Home
- Departments
- Social Services
- Licensing
- Adult Foster Care Licensing
Adult Foster Care Licensing
Adult Foster Care is a home setting for adults who are unable to live independently due to physical, emotional, developmental or mental health impairments. Residents receive meals, support, supervision, and some assistance with personal care and living skills as needed.
- ADL Estimation Guide
- Adult Foster Home Mobility Access Assessment
- Background form.pdf
- Cash Resource Procedure For Adult Foster Care
- Cash Resources Record
- Fact Sheet
- Fire Marshall Request new
- Guidlines and Timelines
- Home Safety Checklist for Community Residental Settings
- Homes Plus Resident Personal Record
- Incident Reports
- Individual Abuse Prevention Assessment and Plan
- Individual Resident Emergency Escape Plan
- Individual Resident Placement Agreement
- Individual Resident Placement Agreement-Respite
- Initial Contact Screening Form
- Injectable Medication Administration Authorization
- Medical Records Request Authorization to Release Confidential Information
- Medication Assistance For AFC Residents
- Medication Information Sheet
- Medication Record and Assistance Administration Authorization
- Medication Record Instructions and Form
- New Corporate Adult Foster Care Application
- Physicians Visit Order Form
- Release of Information
- Resident Information and Discharge Form
- Resident Records Checklist
- Resident Rights for Adult Foster Care Programs
- Residents Personal Possessions Inventory
- Residents Progress Notes Incident Report
- Workcomp Law

subset: commoncrawl


text:
Robbie Williams drives home after checking on his leased farmland just outside Hebbardsville. Robbie owns 600 acres of farmland and leases around 5,000 additional acres all over Henderson County. To oversee the land, Robbie does a lot of driving.
PHOTOGRAPH BY: Julia WallLink
Hal and Bobbie Branson share a morning blessing before breakfast. Hal is a retired minister, and both are very involved in the Spottsville Baptist Church.
PHOTOGRAPH BY: Maddie MeyerLink
Kate Smith, 92, reads "People" magazine while she waits for her hair to dry at Kelly's Mane Event. As one of the regulars of the salon, she visits on Friday mornings.
PHOTOGRAPH BY: Pinar IstekLink
Just before dinner, Seth Coomes, center, 10, was riding a RipStik being towed by his friend on a bike when he took a tumble. Carlos Krous, left, and Almarrow Talbott, 9, right, help him test out his ankle. "If we're not riding bikes, we're playing Xbox," says Carlos. "We laugh a lot."
PHOTOGRAPH BY: Jon HernandezLink
Kyle Arnett and Jon Hittner, center, host a dinner party in their apartment in downtown Henderson where the main course was venison seasoned with rosemary and wrapped in bacon.
PHOTOGRAPH BY: Maddie MeyerLink
Every morning Peggy Thomas makes breakfast for her 51-year-old son, Paul, right, before keeping him company on their Uniontown porch while he waits for a bus that will take him into Henderson. Her husband, Pat, center, occasionally joins the two. To help him deal with his mental disability and maintain an active social life, Paul takes a class at Henderson Community College and works at a bank and a job center for disabled adults.
PHOTOGRAPH BY: Carolina HidalgoLink
Herman Alles, 90, tends to his garden behind the house he shares with his sister Mary Frances, 92. Herman owns Alles Brothers Furniture Co. and still goes to work every day. When not managing his furniture company, Herman spends most of his spare time in his garden. "I just like to go out here and ride around on my chair," he said.
PHOTOGRAPH BY: Dorothy EdwardsLink
Damien Moore leans back in his chair and disconnects from class during a video presentation. Damien is usually the most talkative kid during class, always full of facts and answering most questions the teacher asks, but every once in a while he will take a break to collect himself.
PHOTOGRAPH BY: Connor ChoateLink
Robbie Williams, from left, Steve Frank and David McLevain chat in the Hebbardsville Store, owned by Jerry Knott. The store is also known as Bryant's Grocery and Carter's Dry Goods & General Store. Robbie doesn't visit the country store every day and doesn't hunt deer. But when he arrives, he finds the same friends there at breakfast.
PHOTOGRAPH BY: Julia WallLink
Ruby Jewell Yates has lived in Henderson all 93 years of her life. Ruby moved to the downtown area after her husband, a farmer, passed away. She said she now fills her days by sitting on her front swing and having her children and grandchildren visit her. "I've got so many (grandchildren)," Ruby says. "I couldn't even tell you how many."
PHOTOGRAPH BY: Carter McCallLink
The final step in the Santiago Salpo family's morning routine involves a minute of relaxation before they start the day. Vicente and Mirka enjoy their video games and mother, Adelino, watches a Spanish-language news station.
PHOTOGRAPH BY: Katherine McLeanLink
Vicente Salpo, 46, works as many hours as he can to support his family. This often means the limited time he gets with his family comes when he is already exhausted.
PHOTOGRAPH BY: Katherine McLeanLink
Mac's Barber Shop, a mainstay in the East End neighborhood, is home to two barbers, longtime patrons and daily laughs.
PHOTOGRAPH BY: Mika ChanceLink
Henderson's super fan, Rick "Poncho" Lambert, an accomplished painter, almost always gives away his work.
PHOTOGRAPH BY: Jon HernandezLink
After throwing a 30-minute tantrum, Caiden Williams, finally calms down when Constance Brooks lays down with him in her bed. "It's not easy. It doesn't get easier. You never just get used to it," Constance says.
PHOTOGRAPH BY: Stephen RemichLink
Bobbie Branson joins her husband, Hal, with Lady, a foster dog. Three of Lady's puppies stay close to their mother.
PHOTOGRAPH BY: Maddie MeyerLink
The work of Rose Wheeler, right, as religion educator at Holy Name of Jesus Catholic Church in downtown Henderson melds her personal and professional life.
PHOTOGRAPH BY: Carter McCallLink
Ernie "Deacon" Lingerfelt performs an "anointing of the oils" on Ro Woodley during a church service. "I've been suffering. I've been angry," said Ro, whose son is facing a possible 50-year sentence for committing a violent crime. "I know God is good, but He has put me through so much and I said some horrible things. I'm so sorry for saying those things."
PHOTOGRAPH BY: Ian MauleLink
Workshops photo participant Pinar Istek, a second-year graduate student at the Missouri School of Journalism, works on a scenic picture in rural Henderson County. Pinar, who is from Turkey, says she used to be a city girl, but now enjoys the countryside.
PHOTOGRAPH BY: Nina GreipelLink
More galleries on Frameworkreturn to gallery
Pictures in the News | June 24, 2015
In Wednesday's Pictures in the News, relatives of 19-year-old shooting victim Tavin Price hold a news conference at which Los Angeles police detectives announced multiple... View Post»
The Week in Pictures | May 5-11, 2014
Each week we bring you the very best in visual journalism. We start with a touching image out of Folsom Women's Facility, where a daughter hugs her mother after they had a... View Post»
Los Angeles Times' images of President Kennedy [updated]
John F. Kennedy, the 35th president of the United States, was assassinated on Nov. 22, 1963, in Dallas. Starting in the 1950s, Kennedy, a Democrat from Massachusetts,... View Post»
At left, Dec. 5, 1945: Thirty-foot waves threatened the already damaged home of Emilia Curtet in Redondo Beach as excessively high winds, high tide and rain caused damage to... View Post»


text: I have a Ruger New Model Blackhawk that I broke the rear sight blade. I searched the internet as I was having trouble installing the blade as it has a little nipple. The only advice I could find is that the rear sight assembly should have a half-moon cut-out. The instructions were to compress the spring, slip the sight blade in and then install the screw. The only problem is my gun does not have a half-mood cut-out for the sight blade to slide down in to. I thought of filing the nipple off of the blade and thought better of it as it would have nothing to hold it in. I know I can buy the entire sight assembly for $15 bucks, but 15 is 15 and I would rather spend 3 on the blade. Can anyone give me a tip on how to conquer this. Thanks in advance for all help.


text:
Long Term contract(18 months) postions availiable in Albany, NY. Here are the requirements. Please let me know if you're interested:
Job Requirements:
Mandatory Skills:
Minimum of three (3) years experience:
examining the methods and processes of the business flow within a diverse public or private service delivery organization.
assessing and documenting existing business processes and identifying unnecessary steps.
incorporating value-added steps into a new process through the translation of business practices into terminology and desk products that seamlessly merge computer and microcomputer technology with work processes.
assessing and documenting existing business processes and identifying where improvements can be made or where new processes can be installed.
researching best practices of comparable organizations and analyzing the feasibility of installing them where needed.
developing new methods and procedures, implementation plans and schedules, and written reports.
eliciting and documenting business, user, and system requirements.
preparing business requirements documents (BRD).
Desirable Skills:
1. Excellent communication and interpersonal skills.
2. 5+ years demonstrated experience analyzing and documenting current business processes, related system use and data flow, and re-engineering alternative system processes and data flow; very large Data Warehouse data mapping and data migration/conversion experience; and in-depth/complex data analysis and testing.
3. 3+ years demonstrated experience developing and testing complex reports against both OLTP and OLAP Oracle databases, using such tools as Cognos 8 or other web-based reporting tools.
4. 3+ years demonstrated experience with gathering and documenting data/reporting requirements; experience with human services or SACWIS systems a plus.
Please send resume and salary requirments to: firstname.lastname@example.com


text:
Stadium of Light, Sunderland
15 September, 2012; 17:30 BST
With the hope that the international break has sent players back with a fresh mind and approach to the Premier League, this weekend's fixtures will be an exciting to say the least. And one of such fixtures is the game between Sunderland and Liverpool. Both teams are yet to record a victory this season and are dwindling in the bottom half of the table. Nothing less than three points will be the aim as Martin O'Neill's side comes out against a Liverpool team that is treading on the path of resurgence.
Sunderland have drawn both the games they have played this season against Arsenal and Swansea. While this is a good show of their defensive side, O'Neill is not the kind who is happy just to hang around. The Black Cats have made some quality additions to the team this summer, with the likes of Adam Johnson, Steven Fletcher and Louis Saha joining the ranks and making Sunderland another team capable of upsetting the top teams in the League, as showcased by their away performance at the Emirates on opening day.
Martin O'Neill, who was appointed last season after Steve Bruce was sacked, acted quickly to lift the club from its poor performances and gave them a decent finish to the season considering the team was in the sixteenth spot in the League table under Bruce. Understandably, expectations are high from the Irish manager so a win against Liverpool this season will be an indication of the team's strengths and preparation for this year's campaign.
If Liverpool are to lose their third (out of four) game, then it is a cause for concern in the dressing room. What Brendan Rodgers is attempting to bring at the club is not a small task in any aspect; and although his methodology has been tried and tested and worked before, the present situation of Liverpool's absence from the European stage and competition for the top four means that Rodgers will be watched very, very closely. There is almost no room for error and the new manager needs to get things moving before hopes are dashed once again.
While there is promise and improvement in certain areas of the game with Liverpool this season, it doesn't discount for the lack of conversions so far. With an average of 17 shots per game, the Reds have come out with just an average of three shots on goal. The two goals scored so far have come from set pieces in the draw against Manchester City at Anfield. Liverpool will have to turn this around soon to avoid a struggle in the later part of the season. Sunderland could be the place where the Reds can rise on their learning curve and be more lethal in front of goal.
Team News & Tactical Brief
This weekend's fixture is one that Adam Johnson will eagerly be waiting for. The England international was forced to pull out of the national squad due to an injury and is currently recovering at the club's training ground. O'Neill, at this point, is only hopeful of his return which will be a big boost to the side going into Saturday's game against Liverpool.
"It's too early to know whether he'll be available but it would be great if he could make it." – O'Neill on Johnson.
Apart from the club's costliest signing this summer, Sunderland will be without the services of defenders Phil Bardsley and Wes Brown, both who have been out since pre-season due to a chipped ankle bone and a knee problem respectively. However, the pairing of new arrival Carlos Cuellar and John O'Shea should provide an ample stop-gap arrangement.
Sunderland put up a strong performance against Arsenal on opening day and have shown that they aren't an easy side to break. The key for the Black Cats in this game is the midfield. Liverpool have shown a marked improvement in possession in the middle of the pitch (averaging 44% of the distribution) and hence there is bound to be a strong midfield battle. Shutting out the likes of Allen and Sahin will be a huge responsibility for Cattermole and Larsson.
On the attacking front, O'Neill is likely to start with Sessegnon and new signing Steven Fletcher who has already had a bright start to this season having scored two goals already. The trickery of Sessegnon and the calmness of Fletcher in front of goal could prove to be a tough nut to crack for the Liverpool defence.
Larsson scored the leveller against Liverpool last season while he was at Birmingham City and few fans will be hoping he can cause an upset once again this time around.
Probable Starting Lineup (4-4-2): Mignolet(GK); Gardner, Cuellar, O'Shea, Rose; Meyler, Cattermole, Larsson, McClean; Sessegnon, Fletcher
Except for the injury of Lucas Leiva, Liverpool do not have any major injury concerns ahead of this game. The presence of Allen and Sahin in midfield seems to be an equally good pairing according to most pundits and media houses. Although we are yet to see the fruits of this partnership, much has been spoken about how Sahin and Allen can free up the front men (including Gerrard), hence posing a greater threat to the opposition.
Allen has been Liverpool's star so far this season. Rodgers needed somebody accustomed to his ideology when he arrive at Liverpool and Allen has proved that he can be a mentor to most people in the team who are expected to slot into Rodgers' style of play.
Ideally, each game should be an improvement over the previous one. This is the best way of judging Liverpool's progress this season. The points may not come in thick and fast but the key thing for Liverpool this season is growth in terms of a brand of football and applying that consistently and this is what each one in the Liverpool colors wants to see.
In all likelihood, Brendan Rodgers will bring out the 4-3-3 formation once again, giving game time to Sahin, Allen and Gerrard to gel with each other on the pitch. Sterling, who has shown a lot of confidence coming up to the first team and featuring against the likes of Manchester City and Arsenal could now be tested against a comparatively easy Sunderland side.
Suarez and Borini will complete the forward trio but with the lack of strikers at the club, the onus is on them to bring in the goals which transforms to quite a bit of pressure. The duo needs to pick up form soon.
Probable Starting Lineup (4-3-3): Reina(GK); Johnson, Skrtel, Agger, Enrique; Sahin, Allen, Gerrard; Sterling, Suarez, Borini
Key Match Facts
- In October 2009, Sunderland won this match thanks to a Darren Bent goal which infamously deflected off a beach ball past Pepe Reina.
- Luis Suarez has scored in two of his three Premier League appearances against Sunderland.
- Liverpool have kept only two clean sheets in their last 17 Premier League games.
- Martin O'Neill has coached a team 17 times against Liverpool in the Premier League, wining six, drawing six and losing five.
Player(s) to Watch Out For
Steven Fletcher (Sunderland)
The summer signing from Wolves has been in fine form at Sunderland right from the start. With the loss of Asamoah Gyan, O'Neill might have found just the right replacement to bolster his squad. He is surely going to be a threat to the Liverpool defence as he looks to add more numbers to his goal tally.
Nuri Sahin (Liverpool)
There is still a sense of excitement on the faces of Liverpool fans when the name Nuri Sahin is taken. The loan signing from Real Madrid made his debut against Arsenal in what was a fairly quiet game for the Turkish-born midfielder. Liverpool have him just for a season as of now so for the reputation he carries, the former German Footballer of the Year will be looking to make maximum impact in his time with Liverpool. Going into his second game with a little more confidence could set him apart from the rest.
Sunderland 0 – 2 Liverpool
It wouldn't be too wrong to say that spirits in the Liverpool squad will be a little uplifted now that the lid on the Hillsborough disaster is taken off and the truth finally came out. Such things do have a psychological impact on the players, especially for the likes of Gerrard, Carragher and Reina – the senior most players at the club. Rodgers knows that the sense of calm now in the dressing room will put the players in good stead to carry them ahead this season and we can witness a more convincing performance from them in this game.
Blast from the Past
Stats courtesy whoscored.com


text:
Hardworking housekeeper Mary Walker was given a special send off by staff at West Suffolk Hospital on Friday – after working on the wards for almost 35 years.
Mary, 68, of Fornham Road, Bury, said it was an emotional day saying goodbye to the team.
"I won't miss the work but I will miss the company," she said.
"I have been really happy here - it's sad to be leaving."
Mary's first day was on October 9 1978, four years after the hospital opened.
As she would have worked at the hospital for 35 years in October, Executive director for workforce and communications Jan Bloomfield awarded Mary a special certificate.
She said: "Mary will be sorely missed by her team and by her management.
"Thank you so much for your long service, the fact that there are so many people here shows how loved you are."
Mary now plans to spend more time with her husband and family.
"We were even talking about having another holiday which will be nice", she said.


text:
There were no huge surprises for internet browsing, other than a nicely configured home screen when you start the browser initially.
It shows links to several popular services, including Yahoo Mail, Facebook, news channels and Mapquest. Large icons for making bookmarks, typing in a URL and accessing browser settings make it easy to surf.
The only real gripe here is that the phone screen, at 640 x 360 pixels (about the size of two fingers side by side), is a bit small for reading text on a web page.
The screen quality – while bright and clear – does not match the sheer crispness and quality of the Google Nexus One, which uses OLED technology.
Of course, the lack of Wi-Fi rears its ugly visage here as well. Sites loaded slowly over a typical 3G connection, and not nearly as fast as they do over Wi-Fi.
And, while web page rendering worked fine for sites like ESPN.com and Gmail, there is a long lag on rich sites – like IGN.com – as they load in the background.
Combined with the slow processor, lack of Wi-Fi makes the Nokia 5230 a poor choice as a web device.


text:
3rd May 2007, 9:34 PM
I need help on getting the friend areas used to recruit the legendary pokemon (Rayquaza, Groudon, Kyrouge, Mewtwo, Dexoys, 3 legendary birds, 3 legendary dogs, Lugia, and etc.) Do you get the friend areas by completing missions or not? Because on serebi.net, it saids that there are only 4 missions that grant you friend areas. I already got all four. But I'm missing the legendary areas. Where did I get them?