You can write data to one or more complex flat files. You cannot write to MVS data sets or to files that contain multiple record types. CFF stage can recognize hdfs files if the file name begins with hdfs://. For example, if the file name is /tmp/input.txt, you need to specify hdfs:///tmp/input.txt in Complex Flat File stage file name field. DataStage ® Release: 8x: Job Type: Parallel: OS: Windows: I have an incoming file which I would like to read utilizing complex flat file stage. I do not have the cobol file definitions. Data looks like the following AAA123sdg20101120(0D0A) -header BBB2345ABCDE555(0D0A)- Batch header. File stage types. Sequential file is used to read data from or write data to one or more flat (sequential) files.; Data Set stage allows users to read data from or write data to a dataset. Datasets are operating system files, each of which has a control file (.ds extension by default) and one or more data files (unreadable by other applications).
- Complex Flat File Datastage
- Complex Flat File Stage Datastage Example Programs Download
- Complex Flat File Stage Datastage Example Programs Pdf
PROFESSIONAL SUMMARY:
- Over 6 years of Dynamic career reflecting pioneering experience and high performance in System Analysis, design, development and implementation of Relational Database and Data Warehousing Systems using IBM Data Stage 8.0.1/7.x/6.x/5.x (Info Sphere Information Server, Web Sphere, Ascential Data Stage).
- Excellent Experience in Designing, Developing, Documenting, Testing of ETL jobs and mappings in Server and Parallel jobs using Data Stage to populate tables in Data Warehouse and Data marts.
- Proficient in developing strategies for Extraction, Transformation and Loading (ETL) mechanism.
- Expert in designing Parallel jobs using various stages like Join, Merge, Lookup, Remove duplicates, Filter, Dataset, Lookup file set, Complex flat file, Modify, Aggregator, XML.
- Expert in designing Server jobs using various types of stages like Sequential file, ODBC, Hashed file, Aggregator, Transformer, Sort, Link Partitioner and Link Collector.
- Experienced in integration of various data sources (DB2-UDB, SQL Server, PL/SQL, Oracle, Teradata, XML and MS-Access) into data staging area.
- Expert in working with Data Stage Manager, Designer, Administrator, and Director.
- Experience in analyzing the data generated by the business process, defining the granularity, source to target mapping of the data elements, creating Indexes and Aggregate tables for the data warehouse design and development.
- Excellent knowledge of studying the data dependencies using metadata stored in the repository and prepared batches for the existing sessions to facilitate scheduling of multiple sessions.
- Proven track record in troubleshooting of Data Stage jobs and addressing production issues like performance tuning and enhancement.
- Expert in working on various operating systems like UNIX AIX 5.2/5.1, Sun Solaris V8.0 and Windows 2000/NT.
- Proficient in writing, implementation and testing of triggers, procedures and functions in PL/SQL and Oracle.
- Experienced in Database programming for Data Warehouses (Schemas), proficient in dimensional modeling (Star Schema modeling, and Snowflake modeling).
- Expertise in UNIX shell scripts using K-shell for the automation of processes and scheduling the Data Stage jobs using wrappers.
- Experience in using software configuration management tools like Rational Clear case/Clear quest for version control.
- Experienced in Data Modeling as well as reverse engineering using tools Erwin, Oracle Designer and MS Visio, SQL server management studio, SSIS and SSRS and store procedure.
- Expert in unit testing, system integration testing, implementation and maintenance of databases jobs.
- Effective in cross-functional and global environments to manage multiple tasks and assignments concurrently with effective communication skills.
Sonny with a chance snack cart a pult game. EDUCATIONAL QUALIFICATION:
Bachelors in Electronics and Communication,
TECHNICAL SKILLS:
Bachelors in Electronics and Communication,
TECHNICAL SKILLS:
ETL Tools
DATA STAGE- IBM Web Sphere Data stage and Quality Stage 8.0, Ascential Data Stage /7.5.2/5.1/6.0 Profile Stage 7.0, SSIS (SQL server 2005), Data Integrator.
Business Intelligence tools
Business Objects, Brio, SSRS(SQL Server 2005),IBM Cognos 8 BI
Development Tools and Languages
SQL, C, C++, Unix Shell Scripting, Perl, PL/SQL,oracle
Testing Tools
Auto Tester, Test Director, Lotus Notes
Data Modeling Tools
Gta 5 heavy traffic mod. Erwin 4.0, Sybase Power Developer, SSIS,SSRS
Osx on vmware player 12. Operating Systems
HP-UX, IBM-AIX 5.3, Windows 95/98/2000/ NT, Sun Solaris, Red-Hat Linux, MS SQL SERVER 2000/2005/2008& MS Access
WORK EXPERIENCE:Confidential, CANov 2010 – Present ETL Developer
NetApp Inc is leading Network Appliance Manufacturer Company as well as data storage Company which provide Network appliance like hard disk, shelf for small business owners, large business owners. Also NetApp provides efficient data storage facility. The main aim is to provide variety of services like Data storage, Data Analysis, Data warehouse, Data mart etc. which can adopt consistent tailored processes in order to strive and fulfill promise of commitment and reliability to Customers.
- Involved as primary on-site ETL Developer during the analysis, planning, design, development, and implementation stages of projects using IBM Web Sphere software (Quality Stage v8.1, Web Service, Information Analyzer, Profile Stage, WISD of IIS 8.0.1).
- Prepared Data Mapping Documents and Design the ETL jobs based on the DMD with required Tables in the Dev Environment.
- • Active participation in decision making and QA meetings and regularly interacted with the Business Analysts &development team to gain a better understanding of the Business Process, Requirements & Design.
- Used DataStage as an ETL tool to extract data from sources systems, loaded the data into the ORACLE database.
- Designed and Developed Data stage Jobs to Extract data from heterogeneous sources, Applied transform logics to extracted data and Loaded into Data Warehouse Databases.
- Created Datastage jobs using different stages like Transformer, Aggregator, Sort, Join, Merge, Lookup, Data Set, Funnel, Remove Duplicates, Copy, Modify, Filter, Change Data Capture, Change Apply, Sample, Surrogate Key, Column Generator, Row Generator, Etc.
- Extensively worked with Join, Look up (Normal and Sparse) and Merge stages.
- Extensively worked with sequential file, dataset, file set and look up file set stages.
- Extensively used Parallel Stages like Row Generator, Column Generator, Head, and Peek for development and de-bugging purposes.
- Used the Data Stage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions on ad hoc or scheduled basis.
- Developed complex store procedures using input/output parameters, cursors, views, triggers and complex queries using temp tables and joins.
- Converted complex job designs to different job segments and executed through job sequencer for better performance and easy maintenance.
- Creation of jobs sequences.
- Maintained Data Warehouse by loading dimensions and facts as part of project. Also worked for different enhancements in FACT tables.
- Created shell script to run data stage jobs from UNIX and then schedule this script to run data stage jobs through scheduling tool.
- Coordinate with team members and administer all onsite and offshore work packages.
- Analyze performance and monitor work with capacity planning.
- Performed performance tuning of the jobs by interpreting performance statistics of the jobs developed.
- Documented ETL test plans, test cases, test scripts, and validations based on design specifications for unit testing, system testing, functional testing, prepared test data for testing, error handling and analysis.
- Participated in weekly status meetings.
- Developed Test Plan that included the scope of the release, entrance and exit criteria and overall test strategy. Created detailed Test Cases and Test sets and executed them manually.
Environment: IBM Web Sphere DataStage 8.1 Parallel Extender, Web Services, Quality Stage 8.1, (Designer, Director, Manager), Microsoft Visio, IBM AIX 4.2/4.1 IBM DB2 Database, SQL Server, IBM DB2,Teradata, ORACLE 11G, Query man, Unix, Windows.
Confidential, NJJan 2010 - Oct 2010 Lead Sr. Datastage Developer
Project was to design and develop enterprise data warehouse. Extract data from heterogeneous source system, transform them using business logic and load in to data warehouse.
Project was to design and develop enterprise data warehouse. Extract data from heterogeneous source system, transform them using business logic and load in to data warehouse.
- Used the DataStage Designer to develop processes for extracting, cleansing, transforming, integrating and loading data into staging tables.
- Extensively used ETL to load data from IBM DB2 database, XML & Flat files Source to Informix Database Server.
- Involved in analysis, planning, design, development, and implementation phages of projects using IBM Web Sphere software (Quality Stage v8.0.1, Web Service, Information Analyzer, Profile Stage, WISD of IIS 8.0.1).
- Developed complex jobs using various stages like Lookup, Join, Transformer, Dataset, Row Generator, Column Generator, Datasets, Sequential File, Aggregator and Modify Stages.
- Created queries using join and case statement to validate data in different databases.
- Created queries to compare data between two databases to make sure data is matched.
- Used the DataStage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions on an ad hoc or scheduled basis.
- Created shared container to incorporate complex business logic in job.
- Monitoring the Datastage job on daily basis by running the UNIX shell script and made a force start whenever job fails.
- Created and modified batch scripts to ftp files from different server to data stage server.
- Extensively used slowly changing dimension Type 2 approach to maintain history in database.
- Created Job Sequencers to automate the job.
- Modified UNIX shell script to run Job sequencer from the mainframe job.
- Create parameter set to assign a value to job at run time.
- Standardized the Nomenclature used to define the same data by users from different business units.
- Created multiple layer report providing a comprehensive and detail report with Drill through facility.
- Used Parallel Extender for Parallel Processing for improving performance when extracting the data from the sources.
- Worked with Metadata Definitions, Import and Export of Datastage jobs using Data stage Manager.
- Providing the logical data model design, generating database, resolving technical issues, and loading data into multiple instances.
- Implemented PL/SQL scripts in accordance with the necessary Business rules and procedures.
- Developed PL/SQL procedures & functions to support the reports by retrieving the data from the data warehousing application.
- Used PL/SQL programming to develop Stored Procedures/Functions and Database triggers.
Environment: IBM Web Sphere DataStage 8.0.1 Parallel Extender, Web Services, Quality Stage 8.0, (Designer, Director, Manager), Microsoft Visio, IBM AIX 4.2/4.1 IBM DB2 Database, SQL Server 2000, IBM DB2,Teradata, ORACLE 11G, Query man, BMQ, Unix, Windows.
Confidential, VAOct 2008 – Dec 2009 Lead Datastage ETL Developer
Project was involved in design and development of a group insurance system, which processes claims for group insurance. It covers benefits with subsystems covering Term Life Insurance, Medical Indemnity and Managed Health Care.
Data Modeling:
Project was involved in design and development of a group insurance system, which processes claims for group insurance. It covers benefits with subsystems covering Term Life Insurance, Medical Indemnity and Managed Health Care.
Data Modeling:
- Gathered and analyzed the requirements of the in-house business users for the data warehousing from JAD sessions.
- Collected the information about different Entities and attributes by studying the existing ODS and reverse engineering into Erwin.
- Defined the Primary keys and foreign keys for the Entities.
- Defined the query view, index options and relationships.
- Created logical schema using ERWIN 4.0 and also created the Dimension Modeling for building the Cubes.
- Designed staging and Error handling tables keeping in view the overall ETL strategy.
- Assisted in creating the physical database by forward engineering.
ETL Process:
- Extracted data from source systems transformed and loaded into Oracle database according to the required provision.
- Primary on-site technical lead during the analysis, planning, design, development, and implementation stages of data quality projects using Integrity (now known as Quality Stage).
- Involved in system analysis, design, development, support and documentation.
- Created objects like tables, views, Materialized views procedures, packages using Oracle tools like PL/SQL, SQL*Plus, SQL*Loader and Handled Exceptions.
- Involved in database development by creating Oracle PL/SQL Functions, Procedures, Triggers, Packages, Records and Collections.
- Created views for hiding actual tables and to eliminate the complexity of the large queries.
- Created various indexes on tables to improve the performance by eliminating the full table scans.
- Used the DataStage Designer to develop processes for extracting, cleansing, transforming, integrating and loading data into Data Marts.
- Created source table definitions in the DataStage Repository.
- Identified source systems, their connectivity, related tables and fields and ensure data suitability for mapping.
- Generated Surrogate ID’s for the dimensions in the fact table for indexed and faster access of data.
- Created hash tables with referential integrity for faster table look-up and for transforming the data representing valid information.
- Used built-in as well as complex transformations.
- Used Data Stage Manager to manage the Metadata repository and for import/export of jobs.
- Implemented parallel extender jobs for better performance using stages like Join, Merge, Sort and Lookup, transformer with different source files complex flat files, XML files.
- Optimized job performance by carrying out Performance Tuning.
- Created Stored Procedures to confirm to the Business rules.
- Used Aggregator stages to sum the key performance indicators in decision support systems and for granularity required in DW.
- Tuned DataStage transformations and jobs to enhance their performance.
- Used the DataStage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions on an ad hoc or scheduled basis.
- Scheduled Datastage job using Autosys scheduling tool.
- Prepared the documentation of Data Acquisition and Interface System Design.
- Assigned the tasks and provided technical support to the development team.
- Monitored the development activities of the team and updated to the Management.
- Created complicated reports using reporting tool Cognos.
Environment: IBM / Ascential Data Stage E.E./7.5(Manager, Designer, Director, Parallel Extender), Quality Stage 7.5 Data Stage BASIC language Expressions,Autosys, Erwin 4.0, Windows NT, UNIX, Oracle 9i, SQL SERVER, Cognos, Sequential files, .csv files. Cod ghosts update 3.3 3 download.
Confidential, PAJan 2007 -- Sep 2008
Sr. Data Stage Developer
As a DW developer designed, developed, and deployed DataStage Jobs and associated functionality. The warehouse employed highly complex data transformations including Slowly Changing Dimensions and a series of Stored Procedures, which made performance tuning and efficient mapping highly critical. Along with designing jobs from scratch re-wrote existing code to enhance performance and trouble-shoot errors in both DataStage & Oracle10G.
Responsibilities:
Sr. Data Stage Developer
As a DW developer designed, developed, and deployed DataStage Jobs and associated functionality. The warehouse employed highly complex data transformations including Slowly Changing Dimensions and a series of Stored Procedures, which made performance tuning and efficient mapping highly critical. Along with designing jobs from scratch re-wrote existing code to enhance performance and trouble-shoot errors in both DataStage & Oracle10G.
Responsibilities:
- Used IBM Datastage Designer to develop jobs for extracting, cleaning, transforming and loading data into data marts/data warehouse.
- Developed several jobs to improve performance by reducing runtime using different partitioning techniques.
- Used different stages of Datastage Designer like Lookup, Join, Merge, Funnel, Filter, Copy, Aggregator, and Sort etc.
- Used to read complex flat files from mainframe machine buy using Complex Flat File Stage.
- Sequential File, Aggregator, ODBC, Transformer, Hashed-File, Oracle OCI, XML, Folder, FTP Plug-in Stages were extensively used to develop the server jobs.
- Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database.
- Worked on Complex data coming from Mainframes (EBCIDIC files) and knowledge of Job Control Language (JCL).
- Used Cobol Copy books to import the Metadata information from mainframes.
- Designed Datastage jobs using Quality Stage stages in 7.5 for data cleansing & data standardization Process. Implemented Survive stage & Match Stage for data patterns & data definitions.
- Staged the data coming from various environments in staging area before into DataMarts.
- Involved in writing Test Plans, Test Scenarios, Test Cases and Test Scripts and performed the Unit, Integration, system testing and User Acceptance Testing.
- Used stage variables for source validations, to capture rejects and used Job Parameters for Automation of jobs.
- Strong knowledge in creating procedures, functions, sequences, triggers.
- Expertise in PLSQL/SQL.
- Performed debugging and unit testing and System Integrated testing of the jobs.
- Wrote UNIX shell script according to the business requirements.
- Wrote customized server/parallel routines according to complexity of the business requirements.
- Designed strategies for archiving of legacy data.
- Created shell scripts to perform validations and run jobs on different instances (DEV, TEST and PROD).
- Created & Deployed SSIS (SQL Server Integration Services) Projects, Schemas and Configured Report Server to generate reports through SSRS SQL Server 2005.
- Used to create ad-hoc reports by MS SQL Server Reporting Services for the business users.
- Used SQL Profiler to monitor the server performance, debug T-SQL and slow running queries.
- Expertise in developing and debugging indexes, stored procedures, functions, triggers, cursors using T-SQL.
- Wrote mapping documents for all the ETL Jobs (interfaces, Data Warehouse and Data Conversion activities).
Environment:IBM Web Sphere Data stage and Quality Stage 7.5, Ascential Datastage7.5/EE (Parallel Extender), SQL Server 2005/2008, Linux, Teradata 12, Oracle10g, Sybase, PL/SQL Toad, UNIX (HP-UX), Cognos 8 BI
Confidential, NJ
Jr. DATASTAGE DEVELOPERJan 2006- Dec 2006
Jr. DATASTAGE DEVELOPERJan 2006- Dec 2006
Merrill Lynchwas a global financial service provides capital markets services, investment banking and advisory services, wealth management, asset management, insurance, banking and related financial services worldwide.
Responsibilities:
- Worked on the logical and physical design of the Data warehouse.Identified sources/targets and analyzed source data for dimensional modeling.
- Good knowledge on Voluntary Insurance plans to employers to offer total Insurance packages.
- Worked in design of Voluntary Disability, Voluntary Dental and Voluntary Life of data marts.
- Good knowledge on policy and claims processing
- Worked on integration of Health Claims ODS from legacy systems.
- Designed and developed jobs for extracting, transforming, integrating, and loading data into data mart using DataStage Designer, used Data Stage manager for importing metadata from repository, new job categories and creating new data elements
- Worked with EBCIDIC files to extract data in required format.
- DataStage jobs were scheduled, monitored, performance of individual stages was analyzed and multiple instances of a job were run using DataStage Director.
- Used Parallel Extenderfor splitting the data into subsets, utilized Lookup, Sort, Merge and other stages to achieve job performance
- Used DS Erwin MetaBroker to import Erwin 4.x Metadata into DataStage Repository.
- Developed user defined Routines and Transformations for implementing Complex business logic.
- Extensively used Shared Containers and Job Sequencer to make complex jobs simple and to run the jobs in sequence
- Involved in the preparation of ETL documentation by following the business rule, procedures and naming conventions.
- Created reports for various Portfolios using the Universes as the main Data Providers.
- Created the reports using Business Objects functionality’s like Queries, Slice and Dice, Drill Down, Cross Tab, Master Detail etc.
- As a part of report development, created the reports using universes as a main data provider and using the Powerful business objects functionalities, and formulae. Involved in trouble shooting of various reporting errors.
- Created Business Objects reports, Queries with constant interaction with the end users. Trained end users in understanding the reports. Functionalities such as Slice and Dice, Drill mode and Ranking were used for Multidimensional Formatting.
- Web Intelligence was used to generate reports on the internet/intranet.
- Exporting the Reports to the Broadcast Agent and Used the Broadcast Agent to Schedule, Monitor and Refresh the Reports.
- Developed Test plans, Test Scenarios and Test cases for Code testing.
- Trained team members
- Provided 24/7 production support
Environment: IBM Web Sphere DataStage 7.5, Metastage 7.0, Business Objects 6.5, Oracle 9i, PL/SQL, SQL * Plus, UNIX Shell Scripts, Windows 2000/NT 4.0, ERWIN 4.1.
ConfidentialJune 2004 – Dec 2005 Jr. Datastage developer
Description:ICICI Prudential Insurance provides a wide range of insurance policies such as Life Insurance, Health Insurance, Motor Vehicle Insurance and General Insurance etc. This project is developed as a process of automation for insurance policy management by using centralized data warehouse and Data Mart. This application provides the provision to take in various related information regarding the region, generates premiums and desired data in the form of reports.
Description:ICICI Prudential Insurance provides a wide range of insurance policies such as Life Insurance, Health Insurance, Motor Vehicle Insurance and General Insurance etc. This project is developed as a process of automation for insurance policy management by using centralized data warehouse and Data Mart. This application provides the provision to take in various related information regarding the region, generates premiums and desired data in the form of reports.
Responsibilities:
- Designed and developed mappings between sources and operational staging targets, using Star and Snow Flake Schemas.
- Provided data models and data maps (extract, transform and load analysis) of the data marts for systems in the aggregation effort.
- Involved in Extracting, cleansing, transforming, integrating and loading data into data warehouse using Datastage Designer.
- Developed various transformations based on customer last name, zip code for internal business analytical purposes, loaded warehouse based on customer credit card number with dynamic data re-partitioning.
- Developed user defined Routines and Transformations by using Universe Basic.
- Used Datastage Manager for importing metadata from repository, new job categories and creating new data elements.
- Used the Datastage Director and the runtime engine to schedule running the solution, testing and debugging its components and monitoring the resulting executable versions (on adhoc or scheduled basis).
- Developed, maintained programs for scheduling data loading and transformations using Datastage and Oracle 8i.
- Developed Shell scripts to automate file manipulation and data loading procedures.
Environment: Datastage 5.2/6.0, Oracle 8i, SQL, TOAD, UNIX, Windows NT 4.0.
Professional Summary
Highly Motivated, Solutions Driven with over 7 years of Data Warehousing experience in the areas of ETL design and Development. Involved in complete Software Development life-cycle (SDLC) of various projects, including Requirements gathering, System Designing, Data modeling, and ETL design, development, Production Enhancements, Support and Maintenance. Excellent Interpersonal and communication skills with an ability to remain highly focused and self-assured in fast-paced and high-pressure environments.
- Extensive ETL tool experience using IBM Infosphere/Websphere DataStage, Ascential DataStage.
- Worked on DataStage tools like DataStage Designer, DataStage Director and DataStage Administrator.
- Strong understanding of the principles of Data Warehousing using fact tables, dimension tables and star/snowflake schema modeling.
- Worked extensively with Dimensional modeling, Data migration, Data cleansing, ETL Processes for data warehouses.
- Developed parallel jobs using different processing stages like Transformer, Aggregator, Lookup, Join, Sort, Copy, Merge, Funnel, CDC, Change Apply and Filter.
- Used Enterprise Edition/Parallel stages like Datasets, Change Data Capture, Row Generator and many other stages in accomplishing the ETL Coding
- Familiar in using highly scalable parallel processing infrastructure using parallel jobs and multiple node configuration files.
- Experienced in scheduling Sequence and parallel jobs using DataStage Director, UNIX scripts and scheduling tools.
- Experience in troubleshooting of jobs and addressing production issues like data issues, ENV issues, performance tuning and enhancements.
- Knowledge in using Erwin as leading Data modeling tool for logical (LDM) and physical data model (PDM).
- Extensive experience in design and development of Decision Support Systems (DSS).
- Assisted in development efforts for Data marts and Reporting.
- Technical and analytical skills with clear understanding of design goals of ER modeling for OLTP and dimension modeling for OLAP.
- Extensive experience in Unit Testing, Functional Testing, System Testing, Integration Testing, Regression Testing, User Acceptance Testing (UAT) and Performance Testing.
- Worked with various databases like Oracle 10g/9i/8i, DB2, SQL Server, Teradata.
Educational Qualification
Bachelors of Science
Technical Skills
ETL Tools
IBM Infosphere DataStage 8.5, IBM Infosphere DataStage 8.1 (Parallel & Server), IBM Websphere DataStage 8.0.1 (Designer, Director, Administrator), Ascential DataStage 7.5.2 (Designer, Director, Administrator, Manager),Informatica 6.1
Database
Oracle 10g/9i/8i, IBM DB2/UDB, Teradata, SQL Server 2003/2005/2008.
Data Warehousing
Star & Snow-Flake schema Modeling, Fact and Dimensions, Physical and Logical Data Modeling, Erwin,Cognos Sims 4 deluxe edition mac torrent.
Operating systems
Windows 7x/NT/XP, UNIX, LINUX, Solaris, MS-DOS,MS Access
Languages/Scripting
C, C++, Java, D2K, Visual Basic, PL/SQL, UNIX Shell scripts
Testing/Defect Tracking
HP QualityCenter, Test Director, Bugzilla
Professional Experience
Confidential,Buffalo, NY Jun’ 11 -Current
Sr ETL Datastage Developer
Sr ETL Datastage Developer
As a part of migrating from the CareEnhance Resource Management Software (CRMS) from McKesson and Risk Navigator Clinical from MEDai, the new McKesson Medvantive will be handling the scoring system and supporting specific reporting needs for HealthNow New York Inc. going forward. This results in a lot of material value and cost savings for HealthNow New York Inc at an on-going basis.
The following will be the highlighted benefits of the MAA project
- Improve the medical management of our members
- Improve our reporting capabilities
- Provide actionable information to Healthcare Services in order to improve HealthNow’s quality of care scores
- Engage providers by sharing relevant reports and decrease unnecessary utilization
- Create a business driven approach to feeding data down stream
As a part of getting this project in place, ETL capabilities and tools of HealthNow New York Inc. will play a vital role in initial set-up of the MAA project and in on-going basis support the new system with the pre-requisite steps which need to be in place for the new system to obtain the business results that is expected out of the project.
MAA requires various data extracts from different subject areas to provide us with the scoring and supporting system. This includes member eligibility, claims, provider, RX claims, VED and LAB information to be processed in a specific file format provided by McKesson. The additional process and functionalities that ETL will use will be determined and documented as the project progresses to the later stages.
Responsibilities:
Responsibilities:
- Analyzed, designed, developed, implemented and maintained Parallel jobs using IBM info sphere Data stage.
- Involved in design of dimensional data model – Star schema and Snow Flake Schema
- Generating DB scripts from Data modeling tool and Creation of physical tables in DB.
- Worked SCDs to populate Type I and Type II slowly changing dimension tables from several operational source files
- Created some routines (Before-After, Transform function) used across the project.
- Experienced in PX file stages that include Complex Flat File stage, DataSet stage, LookUp File Stage, Sequential file stage.
- Implemented Shared container for multiple jobs and Local containers for same job as per requirements.
- Adept knowledge and experience in mapping source to target data using IBM Data Stage 8.x
- Implemented multi-node declaration using configuration files (APT_Config_file) for performance enhancement.
- Experienced in developing parallel jobs using various Development/debug stages (Peek stage, Head & Tail Stage, Row generator stage, Column generator stage, Sample Stage) and processing stages (Aggregator, Change Capture, Change Apply, Filter, Sort & Merge, Funnel, Remove Duplicate Stage)
- Debug, test and fix the transformation logic applied in the parallel jobs
- Involved in creating UNIX shell scripts for database connectivity and executing queries in parallel job execution.
- Used the ETL Data Stage Director to schedule and running the jobs, testing and debugging its components & monitoring performance statistics.
- Experienced in using SQL *Loader and import utility in TOAD to populate tables in the data warehouse.
- Successfully implemented pipeline and partitioning parallelism techniques and ensured load balancing of data.
- Deployed different partitioning methods like Hash by column, Round Robin, Entire, Modulus, and Range for bulk data loading and for performance boost.
- Repartitioned job flow by determining DataStage PX best available resource consumption.
- Created Universes and reports in Business object Designer.
- Created, implemented, modified and maintained the business simple to complex reports using Business objects reporting module.
Environment: IBM Info sphere DataStage 8.5, Oracle 11g, Flat files, Autosys, UNIX, Erwin, TOAD, MS SQL Server database, XML files, MS Access database.
Confidential,Milwaukee, WI Jan’ 10 – Mar’ 11 Sr. DataStage Developer/ Data Modeler
Harley Davidson Inc. is an American motorcycle manufacturer. The company sells heavyweight motorcycles designed for cruising on the highways.
Responsibilities:
- Extensively used DataStage for extracting, transforming and loading databases from sources including Oracle,DB2 and Flat files.
- Collaborated with EDW team in, High Level design documents for extract, transform, validate and load ETL process data dictionaries, Metadata descriptions, file layouts and flow diagrams.
- Collaborated with EDW team in, Low Level design document for mapping the files from source to target and implementing business logic.
- Generation of Surrogate Keys for the dimensions and fact tables for indexing and faster access of data in Data Warehouse.
- Tuned transformations and jobs for Performance Enhancement.
- Extracted data from flat files and then transformed according to the requirement and Loaded into target tables using various stages like sequential file, Look up, Aggregator, Transformer, Join, Remove Duplicates, Change capture data, Sort, Column generators, Funnel and Oracle Enterprise.
- Created Batches (DS job controls) and Sequences to control set of jobs.
- Extensively used DataStage Change Data Capture for DB2 and Oracle files and employed change capture stage in parallel jobs.
- Executed Pre and Post session commands on Source and Target database using Shell scripting.
- Collaborated in design testing using HP Quality Center.
- Extensively worked on Job Sequences to Control the Execution of the job flow using various Activities & Triggers (Conditional and Unconditional) like Job Activity, Wait for file, Email Notification, Sequencer, Exception handler activity and Execute Command.
- Collaborated in Extraction of OLAP data from SSAS using SSIS.
- Extensively usedSAP R/3 and SAP BW packs
- Collaborated with BI and BO teams to find how reports are affected by a change to the corporate data model.
- Collaborated with BO teams in designing dashboards and scorecards for Analysis and Tracking of key business metrics and goals.
- Utilized Parallelism through different partition methods to optimize performance in a large database environment.
- Developed DS jobs to populate the data into staging and Data Mart.
- Executed jobs through sequencer for better performance and easy maintenance.
- Performed the Unit testing for jobs developed to ensure that it meets the requirements.
- Developed UNIX shell scripts to automate file manipulation and data loading procedures.
- Scheduled the jobs using AutoSys, Tivoli and Corntab.
- Collaborated in developing Java Custom Objects to derive the data using Java API.
- Responsible for daily verification that all scripts, downloads, and file copies were executed as planned, troubleshooting any steps that failed, and providing both immediate and long-term problem resolution.
- Provided technical assistance and support to IT analysts and business community.
- Environment:IBM InfoSphere DataStage and QulalityStage 8.5 (Administrator, Designer, Director), IBM Information Analyzer8.0.1a, Microsoft SQL 2005/2008, IBM DB2 9.1, AIX6.0, Microsoft SQL 2008, Oracle 11g, Toad 9.5, Java, MS Access, SAP BW, SAP MDM, AS/400, shell scripts, PUTTY, WinSCP, ERWIN 4.0, HP Quality Center, Tivoli, Corntab, AutoSys.
Confidential,Newark, NJDec’ 08 – Nov ’09
ETL Developer
ETL Developer
Prudential has its branches worldwide, in order to keep track of the huge amounts of data generated, a data warehouse was developed which aided all levels of management in obtaining a clear perspective on the trend of the business. Budgeting for the company needs and forecasting the company business decisions were based on the reports produced using this data warehouse. The system was developed for analyzing and reporting the time variant data Database was maintained with the user account details, and change requests on user A/C.
Responsibilities:
- Provided Technical support to the team as the ETL developer. Addressed best practices and productivity enhancing issues.
- Worked on designing and developing the Quality stage.
- Loaded data into load, staging and lookup tables. Staging area was implemented using flat files.
- Created jobs in DataStage to import data from heterogeneous data sources like Oracle 9i, Text files and SQL Server.
- Generation of Surrogate IDs for the dimensions in the fact table for indexed and faster access of data in server jobs.
- Extensively worked on Job Sequences to Control the Execution of the job flow using various Activities & Triggers (Conditional and Unconditional) like Job Activity, Wait for file, Email Notification, Sequencer, Exception handler activity and Execute Command.
- Dicing and Slicing of the input data for the Business feedback. Testing of the system.
- Designing Data masking techniques to mask sensitive information when working with offshore
- Assisted Mapping team to transform the business requirements into ETL specific mapping rules.
- Enhanced various complex jobs for performance tuning.
- Responsible for version controlling and promoting code to higher environments.
- Worked on Teradata optimization and performance tuning.
- Performed Unit Testing, System Integration Testing and User acceptance testing
- Involved in ongoing production support and process improvements. Ran the DataStage jobs through third party schedulers
Environment: Ascential DataStage 7.5.2 (Designer, Manager, Director, Administrator), Oracle 9i, TOAD, SQL/PLSQL, Teradata, Erwin 4.0, UNIX (AIX).
Confidential,Southfield, MI Aug’ 07 – Oct’ 08 ETL Datastage Developer
Mellon Financial Corporation is a global financial services company that provides a wide range of services i.e. investment management, trust and custody, foreign exchange, securities lending, employee benefits consulting, outsourcing services for benefit plans, stock transfer, proxy solicitation, treasury management and banking services etc.
Responsibilities:
Responsibilities:
Complex Flat File Datastage
- Worked on DataStageDesigner, Manager, Administrator and Director.
- Worked with the Business analysts and the DBAs for requirements gathering, analysis, testing, and metrics and project coordination.
- Involved in extracting the data from different data sources like Oracle and flat files.
- Involved in creating and maintaining Sequencer and Batch jobs.
- Creating ETL Job flow design.
- Used ETL to load data into the Oracle warehouse.
- Created various standard/reusable jobs in DataStage using various active and passive stageslike Sort, Lookup, Filter, Join, Transformer, aggregator, Change Capture Data, Sequential file, DataSets.
- Involved in development of Job Sequencing using the Sequencer.
- Used Remove Duplicates stage to remove the duplicates in the data.
- Used designer and director to schedules and monitor jobs and to collect the performance statistics.
- Extensively worked with database objects including tables, views, indexes, schemas, PL/SQL packages, stored procedures, functions, and triggers.
- Creating local and shared containers to facilitate ease and reuse of jobs.
- Implemented the underlying logic for Slowly Changing Dimensions.
- Executed Pre and Post session commands on Source and Target database using Shell scripting.
- Worked with Developers to troubleshootand resolve issues in job logic as well as performance.
- Documented ETL test plans, test cases, test scripts, and validations based on design specifications for unit testing, system testing, functional testing, prepared test data for testing, error handling and analysis.
Environment: IBM Websphere DataStage 8.0.1, IBM AIX 5.2, Oracle 10g, XML files, Autosys, MS SQL Server database, sequential flat files, TOAD.
Confidential,Strongsville, OH
Complex Flat File Stage Datastage Example Programs Download
Oct ’06 – Jul’07 Technical Datastage DeveloperPNC Bank is a U.S based financial services corporation, with assets of approximately $269.9 billion. PNC operations include a regional banking franchise operating primarily in fifteen states and the District of Columbia, specialized financial businesses serving companies and government entities, and leading asset management and processing businesses.
Complex Flat File Stage Datastage Example Programs Pdf
Responsibilities:
.
.
- Involved in creating Table definitions, indexes, views, sequences, Materialized view creation
- Prepared documentation for addressing the referential integrity relations in between the tables at ETL level
- Redesigned the existing server jobs with a different logical approach to improve the performance
- Extensively used Ascential DataStage Designer for creating DataStage Jobs and created Shared Containers for reusability.
- Extensively used all the stages in Server Jobs like OCI, Hash File, Transformer, Sequential File, Link Partitioner, Link Collector and IPC.
- Used the DataStage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions (on an ad hoc or scheduled basis)
- Worked with DataStage Manager for importing metadata from repository, new job categories and creating new data elements
- Involved in designing the procedures for getting the data from all systems to Operational Data Store.
- Used Job Control routines and Transform functions in the process of designing the job.
- Worked on programs for scheduling Data loading and transformations using DataStage from DB2 to Oracle 9i using SQL* Loader and PL/SQL
- Involved in performance tuning of the ETL process and performed the data warehouse testing
- Prepared documentation including requirement specification.
- Designed XML stages for reading XML log files for capturing data stage jobs audit data.
Environment:IBM Websphere DataStage 8.1 (Parallel & Server), IBM AIX 5.2, Oracle 9i, PL/SQL,Flat files,XML files, Autosys, Clearcase, TOAD.
Confidential,Hyderabad, IndiaFeb’05 - Aug’06
DataWarehouse Consultant
ADP India Pvt Ltd is one of the world’s largest providers of business outsourcing solutions. ADP offers wide range of human resource, payroll, tax, benefit administration solutions from a single source.
Responsibilities:
- Analyzed, conceptualized/designed the database that serves the purpose of proving critical business metrics.
- Developed ETL procedures to ensure conformity, compliance with standards and lack of redundancy, translates business rules and functionality requirements into ETL procedures using Informatica –PowerMart.
- Worked with ERwin tool in Data Modeling (both Physical and Logical Design).
- Developed and documented data Mappings/Transformations, Audit procedures and Informatica sessions.
- Assisted in the design and Maintenance of the Metadata environment.
Environment: Informatica 6.1, Oracle 9i/10g, DB2, flat files, PL/ SQL, Windows 2003 Server, UNIX (Korn Scripts), and SQL* Loader, ERwin.