Informatica sample project - 1
CareFirst – Blue Cross Blue Shield, Maryland (April 2009 – Current)
Senior ETL Developer/Lead
Model Office DWH Implementation (April 2009 – Current)
CareFirst Blue Cross Blue Shield is one of the leading health care insurance provided in Atlantic region of United States covering Maryland, Delaware and Washington DC. Model Office project was built to create data warehouse for multiple subject areas including Members, Claims, and Revenue etc. The project was to provide data into EDM and to third party vendor (Verisk) to develop cubes based on data provided into EDM. I was responsible for analyzing source systems data, designing and developing ETL mappings. I was also responsible for coordinating testing with analysts and users.
· Interacted with Data Modelers and Business Analysts to understand the requirements and the impact of the ETL on the business.
· Understood the requirement and developed ETL design document to develop mappings. Also prepared strategy to load historical and incremental loading of the mappings.
· Worked closely with Data Architects in designing the data model for EDM.
· Analyzed source system data, which helped in designing the ETL rules.
· Designed and Developed complex ETL mappings for type -1 and type -2 Dimensions and complex fact tables.
· Prepared test cases and completed Unit testing of developed mappings. Coordinated ETL testing with Business analysts and users.
· Assisted other team members in designing and developing their mappings. Also involved in doing the peer review of team members mappings.
· Analyzed ETL code and tuned the mappings to improve their load performance by tuning mappings or redesigning the code.
· Migrated code from individual user folders to Project folder.
· Analyzed the defects identified by the project team and fixed them.
Technologies: Microsoft Project, Microsoft Office tools, Informatica 8.1, Oracle,
Informatica sample project - 2
Daimler Trucks North America, Portland OR (December 2007 – March 2009)
DWH Technical/Project Lead
Paragon DWH - Sales and Marketing Data warehouse project (December 2007 – Current)
Daimler Trucks North America is a leading manufacturer of trucks in entire North America. Daimler Trucks is part of Global Daimler Corporation and is one of the major revenue generators for the corporation. Daimler Trucks has multiple data warehouses and aftermarket parts group has their own data warehouse. The data warehouse consist data for sales and marketing and forecast and planning operations. This data warehouse was expanded by adding functionality from many other source systems such as I2 (Planning System), MDI (Managed Dealer Inventory) and Dview besides SAP. This provided added functionality in analysis dealer order patterns, accuracy of I2 planning and reason of parts returned by dealers. I was responsible for leading the existing support team and working as project/technical lead for the expansion project.
· Interact with business directors/sponsors to finalize the scope of the enhancement of existing data warehouse.
· Interacted with business managers for Gap analysis in existing Data warehouse for new business requirements.
· Created Project plan and cost and resource estimate for the project and tracked the progress/finances of the project
· Understand current business process and collected requirement for the project.
· Interacted with business to prepare business case for justifying cost associated with the project.
· Worked with business steering committee members to prioritize enhancement requests and scheduling releases to move these requests into production.
· Worked with source system analysts to analyze the source system and design the interface with existing data warehouse.
· Interacted with support team members on weekly basis for scheduling and prioritizing work and releases.
· Worked closely with technical team in designing solution for various enhancements. This involves redesigning some components of data warehouse to enhance the functionality of data warehouse.
· Modified the current interface with SAP to capture change data in Data warehouse from SAP on daily basis.
· Improved the ETL performance from 12 hours to 2 hours by using tuned queries and optimizing the database and Informatica ETL mappings. Performed major role in analyzing the database and ETL mapping and providing inputs to modify ETL and database for better performance.
Technologies: Microsoft Project, Microsoft Office tools, SAP, I2, Informatica 7.1.3, Informatica 8.x, DB2 8.0, Cognos 7.1
Informatica sample project - 3
Cascade Corp, Portland OR (June 2007 – November 2007)
Sales and Finance Data warehouse project (June 2007 – November 2007)
Cascade Corp is global market leader in fields and material handling device and its operations are spread across North America, Europe, China and Australia. The Data warehouse was built to provide Sales and Finance information to senior stake holders in organization across all geographies. This information was used to see trends in sales occurring in organization.
· Understand current business process and collect requirement for the projects.
· Analyze source system and understand various data issues pertaining in source systems.
· Created ETL Architecture and source system analysis document for the Project. Prepared development standards and guidelines for the project to be followed by team.
· Designed data model for the project and came up with space requirement for the database and its growth.
· Created ETL Source to target mapping document. Prepared strategy to load history and incremental data into Data warehouse.
· Estimated the scope of development and tracked the development effort of ETL work from other team members.
· Designed, developed and reviewed ETL mappings for the project.
· Involved in User testing phase of the project. This involves interacting with the users, explaining to user queries regarding data and tracking any issues occurred during user testing.
· Prepared strategy to migrate code from development to production. Migrated code initially to production from their test environment.
· Interacted closely with Microstrategy team in coordinating the loads and testing of the project.
Technologies: Microsoft Project, Microsoft Office tools, JDE, Informatica 7.1.3, SQL Server 2000, Microstrategy 8
Informatica sample project - 4
Cardinal Health, Dublin, OH (February 2006 – June 2007)
DWH Onsite Lead
Annual Maintenance Support Transition (September 2006 – June 2007)
Cardinal Health is 80 billion dollar company currently involved in distribution, producing Medical products and drugs. Wipro was awarded an outsourcing deal to takeover support activities of Cardinal Health. These activities involve analyzing the current scope and transition the knowledge of systems to Wipro employees and start supporting these applications. As a DWH program manager, I was responsible for transitioning DWH activities to Wipro. Besides managing lead activities, I am also supporting applications in Hyperion Planning and Essbase.
· Estimate the scope of transition by gathering requirements from applications stake holders.
· Worked with Resource Managers to get the right resource to complete the activity.
· Create a project plan for transition activity.
· Track the transition activity and resolve issues occurred during transition to complete transition on time.
· Work in setting up infrastructure for steady state to support transitioned application.
· Coordinate all activities with offshore team in India.
· Transitioned activity for supporting Hyperion Planning and Essbase.
· Currently working as Hyperion and Essbase administrator. Currently supporting 4 business applications in Essbase and Planning.
· Designed and developed HAL routines to load metadata and data into Essbase.
· Designed informatica mappings to retrieve data from Data Warehouse for Essbase.
Technologies: Microsoft Project, Microsoft Office tools, Informatica 7.1.3, Essbase 126.96.36.199, Hyperion Planning 3.5.1, HAL 7.1
Informatica sample project - 5
PTS Metrics Project (February 2006 – August 2006)
PTS (Pharmaceutical Technology Services) is a part of Cardinal Health focusing majorly in manufacturing drugs and other medical products and shipping it to respective clients. Currently PTS has 38 branches and all branches have different source systems. The data warehouse was built to provide business an option to see consolidated operational reports for all branches.
· Analyze source systems and understand source system applications with the help of PTS Business Analysts.
· Created ETL Architecture and source system analysis document for the Project. Interacted with Informatica Administrator group, source system and Data warehouse DBA’s and source system application owners.
· Design data model for PTS Metrics data warehouse using Sybase Power Designer
· Worked with team in preparing Source to Target Mapping and unit test case documents.
· Designed, Developed and Reviewed ETL mappings from source to staging and from staging to Data warehouse.
· Prepared Test Strategy and coordinated system, integration and user acceptance testing.
· Prepared strategy to implement ETL code in production and load historical data in production.
Technologies: Informatica7.1.3 , Power Designer 9.0, Oracle 10g, JDE (Oracle), Track wise (SQL Server)
Informatica sample project - 6
Cadbury Schweppes, North America (April 2004 – January 2006)
Project Lead / ETL Architect
M2MR (Move to Monthly Reporting) (September 2005 – January 2006)
Cadbury Schweppes is one of the largest confectionary companies across the globe spreading its operations in more than 80 countries. Cadbury Schweppes decided to move their current system from 13 periods to 12 months across the globe. NAC DSS M2MR project is to convert NAC DSS (US and Canada) from 13 periods to 12 months. This involved converting history data to months and converting programs to have data in 12 months from ongoing basis.
· Interacting with clients in Analyzing Business Requirements.
· Created Functional and Technical Design document of the Project. The document contains strategy of converting history data from 13 periods to 12 months and modifying incremental mappings from 13 to 12 periods.
· Created ETL mappings to convert history data from 13 periods to 12 months. Modified incremental ETL mappings to load ongoing data in 12 periods.
· Created parameter files to remove any hard coding which was used in previous mappings for 13 periods.
· Interacted with support team for configuration management of changed mappings for this project.
· Interacted with Cognos team in explaining requirements and modifying cubes and reports due to these changes.
· Involved in testing with business users from system testing to regression testing.
Technologies: Informatica 6.2, UNIX, Oracle, Cognos.
Informatica sample project - 7
Path to Probe NAC DSS Toronto, Canada (September 2004 – August 2005)
The Path to Probe – NAC DSS project involved in developing an infrastructure (data structures and processes) to provide a single unified source of information for Sales Orders, Invoices, Trade Spend activities, Sales and Demand Planning at brands and Key Accounts happening at Cadbury Adams Confectionary business of North America. The North American Confectionery Decision Support System (NAC) DSS Sales & Marketing (S&M) project provided an integrated, non-SAP based information delivery for sales & marketing in North America. I was involved in Canadian implementation of this project.
· Interacted with business users in Analyzing Business Requirements. Collect requirement to transfer and load history data into data warehouse.
· Create a data model for the project based on business requirements.
· Work with SAP and TPM source system resources to understand source system data.
· Created mapping design documents and unit test cases to transfer data from source system to data warehouse.
· Extracted data from various source systems. Extracted data from SAP using Informatica Power Connect. Extracted data from other source systems via directly connecting to source system using native informatica connections.
· Developed ETL mappings and mapplets using various transformations in informatica.
· Scheduled Informatica mappings through Unix shell scripts.
· Involved in testing the system with business users during various testing stages.
· Helped Cognos team in testing Cognos reports and Cubes. Also worked in Cognos in developing some Cognos impromptu reports.
Technologies: Oracle 8.1.3, Informatica Power Center 6.2, Cognos, ERWIN, SAP R/3 4.7, AIX.
Informatica sample project - 8
Consumer Relations Datamart for DPSU, Dallas, USA (April 2004 – August 2004)
DPSU is a subsidiary of Cadbury Schweppes located in Plano, Texas. The project is to analyze the consumer complaints received by consumer relations department and develop a datamart, which will help them in analyzing the customers call behavior. My role was analyzing business requirement and build a datamart based on requirements. I was also involved in ETL development of the project.
· Interacting with clients and Analyzing Business Requirements.
· Designing Consumer Relations Datamart
· Created Functional and Technical Design document of the Project.
· Extracting data for Siebel Source System using Power Connect for Siebel and load into datamart.
· Development of Informatica Mappings, Sessions, Worklets and Workflows.
Technologies: Oracle 8.1.3, Informatica Power Center 6.2, Cognos, ERWIN, Siebel
Informatica sample project - 9
PacifiCorp, Portland, USA (May 2002 – March 2004)
EDW (Enterprise Data Warehouse) Development
Enterprise Data Warehouse is a conversion project of migrating existing data marts at an integrated place to get the advantage of corporate wide Data warehouse. It involves rewriting/Developing existing Data marts and adding new Subject areas to existing data marts. It helps business users a platform to perform queries across various subject areas using a single OLAP tool (Cognos).
· Created Mapping Design document to transfer data from source system to datawarehouse.
· Development of Informatica Mappings, Sessions, Worklets, Workflows.
· Created Complex mappings using various transformations using Informatica.
· Scheduling of Informatica Jobs through Shell script.
· Migrating form Informatica Power Center 5.1 to Informatica Power Center 6.2
· Wrote Shell Scripts to monitor load on database and Perl Scripts to format data extracted from Data warehouse based on user requirements.
· Prepared a process document from moving Informatica code from Development to Production.
· Developed Cognos Reports Using Impromptu Administrator.
· Developed Catalogs to build reports
Technologies: UDB 7.2, Informatica Power Center 6.2, Informatica Power Center 5.1, Cognos, Perl
Informatica sample project - 10
Datamart, PacifiCorp USA (September 2001 – April 2002)
Development and Implementation of RVN (Revenue Accounting System)
This project is used for analyzing revenue system in PacifiCorp. Various division and regional managers to get account of revenue generation from their respective divisions use the system. It is also used to analyze the changes in revenues of power generations based on various factors. RVN Datamart has five input sources to get its data. The reports are based on revenue generated due to different customers across the organization. It also took the various factors, which affect the revenue generation such as weather adjustments, taxes etc.
· Creating Universe in Business Objects Designer
· Creating Reports using Business Objects
· Managing Users using Business Objects Supervisor
· Implementation and optimal Configuration of Web Intelligence and BCA.
· Developing, Analyzing and Maintaining Informatica mappings and sessions.
· Supporting Application and handling user queries
Technologies: Oracle 8.1, Business Objects 5.1.2 and Informatica PowerCenter 5.1