Using Metadata to Drive ETL Processing

The Business Environment

The company for which this application was designed (the “company”) manages prescription claims benefits for its clients. As an optional service, it offers disease management programs to its customers’ insured members. The programs benefit members by identifying gaps in care, inappropriate drug therapy regiments, helping control their weight and in other ways. Some DM programs are included as part of the standard offering of the company while others are optional. The company has full ownership and control over its clients’ drug claims, including acquisition, adjudication and payment on behalf of their clients.

Medical claims are an external source of data for the company. Medical claims are managed by other insurers, known internally as claim vendors, or simply “vendors”. Vendors supply paid medical claim files to the company at the client’s request. See The Sales Cycle for details.

Medical claims are valuable because they contain information such as diagnosis codes, revenue codes and medical procedure codes, which make the Disease Management programs more accurate and relevant to the insured. These codes make it possible to accurately determine members’ disease states and treatments. This information, combined with that contained in the drug claims makes for better member targeting and algorithm performance.

The Sales Cycle

Clients are required to supply medical claims for their insured members as a prerequisite

for enrollment in those DM programs that rely on medical data. Account Mangers work with customers and BA’s (?) to obtain that data. Clients then authorize their medical claim vendors to supply file formats and data definitions and sample files to the company. Agreements are signed to ensure the appropriate level of privacy and security on the medical claims data.

Business Analysis Phase

The documents supplied by vendors are analyzed by the company’s Business Analysts. The Business Analysts use the information to define file layouts, data mapping rules and to decide which quality tests are to be run against each client field.

The Business Analysts perform data entry and entered File Layouts, File Fields, Staging QA Template information (which tests are run on each field) into application metadata tables. They also enter data to set up clients, vendors and to associate vendors with clients. They then pass mapping rules and sample claim files to the IT team.

Staging Table Creation

The IT development team uses the File and Field metadata entered by the Business Analysts to construct SQL to build an appropriate staging table to hold the claims data supplied by the client via a query they run. They also construct a database view of the table using encoded names, as follows:

• Client fields that don’t map to a production claims table column are prefixed with “ZZ_” in the view to identify them as unused for ETL purposes.

• Client fields that map directly to a production claims table column are given the name of the production column in the view.

This encoding is done to allow Informatica, the software used for ETL, to utilize its auto-link function to quickly build the statements to move client fields to the staging table.

Finally, an ETL program is built to populate the staging table from the sample client claims file. The program is used to load the staging table and the Business Analysts analyze the data in the staging table to adjust their mapping rules, if necessary.

ETL Creation

When the Business Analysts are satisfied with the data mapping rules they are returned to IT for development of the stage-to-target ETL programs. This completes program development.

An Informatica workflow is created and a File Feed Definition row is built to tie the File Layout and Client-Vendor together. This allows the automatic scheduling feature to recognize client files when they arrive and initiate the appropriate ETL and QA programs. The QA function is completely table driven by the data entered in the Business Analysts Phase.