Project #4: CMS EOB Load and Extract
Description: CMS Explanation of Benefit (EOB) file extract will be produced using a compilation of
data coming from four sources namely CIP on Facets, Optum Behavioral Health, Avesis, and Optum Rx.
The production of the file extract will be done and provided to UPC on a monthly basis and in
automated fashion using SQL Job.
Roles & Responsibilities:
* Single point of contact for all EOB(Extraction of benefits) related data loads/extractions and
issues.
* Mainly involved in Creation of SSIS packages, SQL DB design and T-SQL development.
* Created the Packages in new versions like Package and Project deployment models and used
Integration catalogs for monitoring and scheduling.
* Involved in Deployment Activities, created jobs and scheduling the ETL packages at desired
frequencies using SQL Server agent jobs.
* Used multiple Transformations and various control flow tasks in SSIS to meet the Business
Requirements.
* Hands on experience in implementing Event Handlers, Logging, Checkpoints, Transactions and
package configurations.
* Created technical specifications, DLP (Desktop level process), HLD, LLD, Review& design
documents.
* Involved in review and unit testing and created review documents.
* Creating SQL objects like Tables, Stored Procedures, Views and Indexes.
* Altering and created indexes on necessary table columns as per the requirement.
Project #5: OptumBH (Optum Behavioral Health)
Description: This project is to assure that the eligibility file will include all members with 2010
eligibility onwards. For members active in 2010 with an effective date of earlier than 2010, this
earlier effective date should not be altered. This may be updated in the future to use a shortened
time frame of past eligibility spans with added fields particularly for OptumBH.
Roles & Responsibilities:
* Creation of SSIS packages, SQL DB design and T-SQL development.
* Created the Packages in new versions like Package and Project deployment models and used
Integration catalogs for monitoring and scheduling.
* Involved in Deployment Activities, created jobs and scheduling the ETL packages at desired
frequencies using SQL Server agent jobs.
* Used various Transformations and tasks in SSIS to meet the Business Requirements.
* Hands on experience in implementing Event Handlers, Logging, Checkpoints, Transactions and
package configurations.
Project #6: MOOP Automation
Phase 1: Optum Rx MOOP Claim load
Description: With the change CIP's of pharmacy vendor in 2014 from ESI to OptumRx (ORxPD) we must
load the daily claims Out of Pocket (OOP) data to Transactional Data Management (TDM). This will
enable monitoring whether a member has reached the plan's maximum out of pocket (MOOP) limit.
Phase 2: Optum Rx MOOP Claims extract toFacets
Description: OptumRx Part D sends a daily file to CIP containing Out of Pocket (OOP) claim
information. This is loaded to CIP's Transactional Data Management environment through automated
process Load_OrxMoopClaims. This data must be transformed into a file format that Trizetto can
accept and loaded to Facets OOP accumulators.
Phase 3: OptumRx MOOP Members extract to OptumRx PartD
Description: OptumRx Part D sends a daily file to CIP containing Out of Pocket (OOP) claim
information. This information is transferred to Trizetto daily where the OOP data is loaded to
Facets accumulators during the nightly scheduled batch. This accumulator data must be queried daily
for members who have met their maximum out of pocket limit (MOOP) and OptumRx must be informed of
members who have reached their MOOP.
Roles & Responsibilities:
* Involved in all three phases of development and created packages for Data load/Extracts using
SSIS.
* Mainly involved in Creation of SSIS packages, SQL DB design and T-SQL development.
* Created SSRS reports for end user as per requirements.
* Created complex logics to handle multiple areas in backend database.
* Hands on experience in implementing Event Handlers, Logging, Checkpoints, Transactions and
package configurations.
* Created technical specifications, DLP (Desktop level process), User test cases.
PROJECT 8#: Web Interface
Description: Web Interface (Exception Report) provides facilities to indicate minimum, maximum, Data
through date, Missing values on Load table columns. By using this Business user or process owner can
check the values in different table and related columns in metadata tables and also provides
information regarding the expected values for the related columns and by using this interface we can
track the related report for metadata load tables.
Roles & Responsibilities:
* Web interface application is use to track the Metadata of master tables information.
* Created SSRS reports to show the error occurrence and master table missing data.