Data warehouse Architect

Satish Lepakshi

Senior Data warehouse Consultant

516-***-**** (M)

OBJECTIVE: To achieve excellence in the highly competitive field of Information Technology using Architecture, design, develop, test, deploy and maintain Data Warehouse Application software.

SUMMARY:

• Over 12 years of Information Technology experience including Data Warehouse Design, Architecture, Dimension Modeling, Business Intelligence strategy, Development, Testing, Implementation, Training and Documentation .

• Over 8 years Extensive experience in understanding business/ process requirements and translating them to technical requirements

• Over 5 years of Data Warehouse experience with Informatica, Ab Initio, Cognos Decision Stream, SQL Server, Oracle, Unix, DB2

• Over 5 years of Business Intelligence experience with Cognos.

• Over 3 years on writing proposals, presenting Data Warehouse solutions, developing BI strategies, evaluating DW and BI Tool and IT Vendors

• Experience in the documenting High Level Design, Flow diagram, mapping and technical specification documents for BI applications

• Extensive experience in ETL process consisting of data transformation, sourcing, mapping, conversion and loading

• Experience in Database Design, Entity-Relationship Modeling, Dimension Modeling, Star Schema & Snowflake with Kimball methodologies

• Strong Focus on Software Development Life Cycle (SDLC) with design, concept, architecture, planning, modeling, coding, development, writing support procedures.

• Experience in developing web based applications with ASP.NET, ASP and Visual Basic

SKILLS MATRIX:

OLAP Tools

Cognos 8.4,8.2 BI (Framework Manager, Report Studio, Analysis Studio, Event Studio), Cognos 7.0 / 7.1 (Impromptu, Power Play, Power Play with Excel, DSS and Power Play Transformer 7.1), Cognos 10, Micro Strategy

ETL Tools Informatica 5.0/6.0,7.0,8.1 Cognos Decision Stream, Ab Initio

Web Servers ORACLE, SQL/400, DB/400, DbaseIV, MS SQL SERVER, DB2 UDB

GUI Visual Basic 6.0, Oracle Forms/Reports 6i, Visual Studio .NET (VB.NET, ASP.NET, ADO.NET), Toad

OS UNIX (AIX, HP-UX), Windows 95/98/2000/NT

MODELING TOOLS Erwin 7.2, Rational Rose

OTHER TOOLS PLUMTREE

EDUCATION:

Bachelor of Electronics Engineering

Professional Experience:

Morgan Stanley,NY 03/2010- Current

Data warehouse Consultant

Morgan Stanley is a leading financial company. A pioneer in the global expansion of finance and capital markets, and in providing new opportunities for individual and institutional investors. Data is fed from various sources to a datamart. Cognos reports are developed on the datamart.

• Lead the efforts of the client architecture team in defining the Business Intelligence / Data Warehouse end-to-end architecture.

• Worked extensively with the Business Analyst team in understanding the business rules and writing requirement specs, technical specs and mapping documents from staging to data mart, cubes and reporting needs to the end user community.

• Designing and creating the architecture of data warehousing star schema logical database model using Erwin and Kimball methodologies

• Evaluated processes and on-going support structures to enable reuse and efficiency.

• Investigating and resolving data issues across platforms and applications, including discrepancies of definition, format and function.

• Generated Logical and Physical Data model.

• Generated High level design and process flow diagrams.

• Schedule workshops and deliver presentations.

• Provided a disaster recovery plan.

• Designing and creating the architecture of OLAP cubes, Aggregations, Query and Reporting.

• Generating High Level Architecture, High Level Design, OLAP Reporting and Process Flow diagram documents.

Environment: Cognos 8.4 BI (Framework Manager, Analysis Studio, Report Studio), UNIX, Windows XP/NT,Informatica.DB2,Erwin,Visio.

Praxair,Buffalo, NY 09/2009- 03/2010

Data warehouse Architect / Lead

Praxair, Inc. is the largest industrial gases company in North and South America and one of the largest worldwide. Praxair Logistics data is fed from various sources to a MS SQL database. The data is then translated to a data mart with business rules.

• Designing and creating the architecture of data warehousing star schema logical database model using Erwin and Kimball methodologies

• Lead the efforts of the client architecture team in defining the Business Intelligence / Data Warehouse end-to-end architecture.

• Worked with the client architecture team in defining tools, processes and technologies to be leveraged on the data warehouse.

• Worked extensively with the Business Analyst team in understanding the business rules and writing requirement specs, technical specs and mapping documents from staging to data mart, cubes and reporting needs to the end user community.

• Designing and architecting end-to-end ETL process including data Extraction, Transformation and Loading from source systems using SSIS.

• Designing and creating the architecture of data warehousing star schema logical database model using Erwin and Kimball methodologies

• Evaluated processes and on-going support structures to enable reuse and efficiency.

• Investigating and resolving data issues across platforms and applications, including discrepancies of definition, format and function.

• Generated Logical and Physical Data model.

• Schedule workshops and deliver presentations.

• Designing and creating the architecture of OLAP cubes, Aggregations, Query and Reporting.

• Generating High Level Architecture, High Level Design, ETL Mapping, OLAP Reporting and Process Flow diagram documents.

Environment: MS SSIS,SQL Server 2005, Cognos Impromptu 8 BI (Framework Manager, Analysis Studio, Report Studio), Erwin 7.2, UNIX, Windows XP/NT.

Deloitte Consulting, Camp Hill, PA 03/2009- 08/2009

Data warehouse Analyst / Lead

Agilent Technologies is on the leading edge of nearly every major trend in communications and life sciences. From optical and wireless communications to disease and discovery research, Agilent delivers product and technology innovations that benefit millions of people around the world. GDW (Global Data Warehouse) by Agilent supports functional areas within the company required an enterprise view of data, a trusted source for key enterprise analytical and management reporting, timely reporting of standardized metrics with the ability to drill down and perform analysis.

• Designed the ETL processes using Informatica tool to load data from Oracle 9i, DB2 into the target oracle 9i Datawarehouse.

• Monitoring of production ETL applications.

• Production Support through tickets. Executing Scripts.

• Managing scheduled activities based on business requirements

• Designed and developed Informatica Mappings and Sessions based on business user requirements and business rules to load data from source flat files and RDBMS tables to target tables.

• Used debugger to debug mappings to gain troubleshooting information about data and error conditions.

• Involved in fine tuning SQL overrides for performance Enhancements.

• Lead the efforts of the client architecture team in defining the Business Intelligence / Data Warehouse end-to-end architecture.

• Worked with the client architecture team in defining tools, processes and technologies to be leveraged on the data warehouse.

• Worked extensively with the Business Analyst team in understanding the business rules and writing requirement specs, technical specs and mapping documents from staging to data mart, cubes and reporting needs to the end user community.

• Designing and architecting end-to-end ETL process including data Extraction, Transformation and Loading from source systems using Informatica.

• Designing and creating the architecture of data warehousing star schema logical database model using Erwin and Kimball methodologies

• Evaluated processes and on-going support structures to enable reuse and efficiency.

• Investigating and resolving data issues across platforms and applications, including discrepancies of definition, format and function.

• Generated Logical and Physical Data model.

• Schedule workshops and deliver presentations.

• Installing and Configuring Cognos 8 BI Application server, Web server and content database.

• Administration and Security.

• Designing and creating the architecture of OLAP cubes, Aggregations, Query and Reporting.

• Generating High Level Architecture, High Level Design, ETL Mapping, OLAP Reporting and Process Flow diagram documents.

Environment: Informatica PowerCenter 8.5, Informatica Change Data Capture (CDC) PL/SQL, Erwin, Cognos Impromptu 8 BI (Framework Manager, Analysis Studio, Report Studio), Oracle10g, UNIX, Windows XP/NT.

XEROX , NY 09/2008-02/2009

Data warehouse Architect / Lead

Xerox Corporation is the world's leading document management technology and services enterprise and is the world's leading document management technology and services enterprise. Xerox’s Customer Information program maintains hierarchy for all global accounts. Also there is a Cognos 8.2 based reporting layer for displaying metrics data associated with these Global accounts

• Conduct a study of the current Xerox applications; do analysis of business rules and practices in the area of Customer Information applications.

• Understand functional gaps in existing applications and suggest business process improvements.

• Participate in Requirements gathering meetings, gather requirements and create requirements and proposal documents.

• Design and develop Oracle, Ab Initio and Cognos based applications.

• Prepare Project Plans, review test plans and monitor unit & integration testing.

• Manage off-shore team for functional enhancements & application interface development and delivery.

• Writing technical specifications, coding, debugging and testing, analyze the adequacy and applicability of the computer output.

• Cognos User and Server Administration.

Environment: Cognos 8.2 BI (Framework Manager, Report Studio, DMR), Ab Initio, Visio, Oracle 10, HP-UNIX,Erwin 7.1.

Cargill, MN 05/2007 – 08/2008

Data Warehouse Architect

CPGM Data Warehouse: Cargill is an international provider of food, agricultural and risk management products and services. CPGM is a data warehouse project developed for the energy traders of Cargill to analyze energy prices, outages, weather patterns etc. Data is retrieved from various files and databases and then extracted to a common staging area and loaded to a data mart to build reports and cubes to analyze the prices, outages, weather patterns etc.

• Lead the efforts of the client architecture team in defining the Business Intelligence / Data Warehouse end-to-end architecture.

• Worked with the client architecture team in defining tools, processes and technologies to be leveraged on the data warehouse.

• Worked extensively with the Business Analyst team in understanding the business rules and writing requirement specs, technical specs and mapping documents from staging to data mart, cubes and reporting needs to the end user community.

• Designing and architecting end-to-end ETL process including data Extraction, Transformation and Loading from source systems using Informatica.

• Designing and creating the architecture of data warehousing star schema logical database model using Erwin and Kimball methodologies

• Evaluated processes and on-going support structures to enable reuse and efficiency.

• Investigating and resolving data issues across platforms and applications, including discrepancies of definition, format and function.

• Generated Logical and Physical Data model.

• Generated High level design and process flow diagrams.

• Schedule workshops and deliver presentations.

• Installing and Configuring Cognos 8 BI Application server, Web server and content database.

• Administration and Security.

• Designing and creating the architecture of OLAP cubes, Aggregations, Query and Reporting.

• Generating High Level Architecture, High Level Design, ETL Mapping, OLAP Reporting and Process Flow diagram documents.

• Created UAT test plan.

• Oversee users during UAT

• Created Production Migration Playbook.

• Oversee migration to production.

• Provided training to user on Cognos user tools

Environment: Cognos 8 BI (Framework Manager, Analysis Studio, Report Studio), Cognos 7.4 Power Play Transformer, Informatica 8.0, Visio, Oracle 10, Autosys, HP-UNIX, Erwin..

Capital One, VA 05/2006 – 04/2007

Data Warehouse Business Analyst

Migration of datamarts: Capital One(Richmond, VA) is a diversified financial services company offering a broad array of credit, savings and loan products to customers in the United States, UK, and Canada. Product Services IT is an existing project. The applications are in Ab initio and C language and reporting in Cognos. As part of this migration the existing Unisys Payment system would be migrated to TSYS payment system. The existing system had accounted number as 10 numbers. To be migrated to TSYS the account prefix had to be added to the account number for a total of 16 digits.

• Lead the efforts of the client architecture team in defining the Business Intelligence / Data Warehouse end-to-end architecture.

• Worked with the client architecture team in defining tools, processes and technologies to be leveraged on the data warehouse.

• Worked extensively with the Business Analyst team in understanding the business rules and writing requirement specs, technical specs and mapping documents from staging to data mart, cubes and reporting needs to the end user community.

• Designing and architecting end-to-end ETL process including data Extraction, Transformation and Loading from source systems using Ab Initio.

• Designing and creating the architecture of data warehousing star schema logical database model with Kimball methodologies

• Evaluated processes and on-going support structures to enable reuse and efficiency.

• Investigating and resolving data issues across platforms and applications, including discrepancies of definition, format and function.

• Worked with Lead Data Architect to transition logical model to physical model.

• Worked Extensively involved in Design and Development for ETL conversion of 10 to 16 digits using Ab initio

• Generating High Level Architecture, High Level Design, ETL Mapping, OLAP Reporting and Process Flow diagram documents.

• Created UAT test plan.

• Oversee users during UAT

• Created Production Migration Playbook.

• Oversee migration to production.

• Provided training to user on Cognos user tools

Environment: Ab Initio 13.1, Maestro, Cognos 8.2(Framework manager, Report Studio),a Oracle 9i, HP-UNIX, Erwin

Black & Decker , CA 02/2005 – 04/2006

Cognos Technical Specialist

Hardware and Home Improvement Sales Reporting System :The reports were being developed for Black & Decker Corporation (BDC). Hardware and Home Improvement Products segment offers security hardware and plumbing products. Its security hardware products consist of residential and light commercial door locksets, electronic keyless entry systems, exit devices, keying systems, general and decorative hardware, lighting, home accents, and collectibles.

• Led a team of six with ETL and report developers.

• Worked with the users to define and document data warehouse business requirements as the foundation for database design, data extraction and analysis.

• Documented data mapping rules for movement of data between applications.

• Evaluated processes, tools and on-going support structures to enable reuse and efficiency.

• Resolved data issues across platforms and applications, including discrepancies of definition, format and function.

• Populated meta-data into repositories.

• Created data models with entity-relationship-attribute and star schema with Kimball methodologies

• Created data flow diagrams and process models and integrated models across functional areas and platforms.

• Worked with DBA in transition logical model to physical model.

• Generated Logical and Physical Data model.

• Generated High level design and process flow diagrams.

• Schedule workshops and deliver presentations.

• Created UAT test plan.

• Oversee users during UAT.

• Developed detailed ETL, cube and report specifications by coordinating with the Solutions Architects and Data Architects.

• Interpret the System requirements document and create Unit Test Plan and Unit Test specifications.

Environment: Cognos Impromptu 7.1, Cognos Power Play 7.1, Cognos Power Play with Excel, Cognos Power Play Transformer 7.1 and Cognos Decision Stream, Oracle 8i, Windows XP, Erwin.

State Govt. of Pennsylvania, PA 06/2003–01/2005

Subject Matter Expert

This module was being developed for the reconciliation of the HCSIS eligibility data with CIS. A data extract in the form of text files is provided from CIS to compare the eligibility data with HCSIS. The data files contain potentially 3 million consumer records. The data files data needs to be compared with the HCSIS data for reconciliation. The reconciliation process was achieved with the aid of Cognos and Informatica tools. Once the module was into development there is a weekly refresh of data in HCSIS.

.

• Lead the efforts of the client architecture team in defining the Business Intelligence / Data Warehouse end-to-end architecture.

• Worked with the client architecture team in defining tools, processes and technologies to be leveraged on the data warehouse.

• Worked extensively with the Business Analyst team in understanding the business rules and writing requirement specs, technical specs and mapping documents from staging to data mart, cubes and reporting needs to the end user community.

• Designing and architecting end-to-end ETL process including data Extraction, Transformation and Loading from source systems using Informatica.

• Designing and creating the architecture of data warehousing star schema logical database model with Kimball methodologies.

• Evaluated processes and on-going support structures to enable reuse and efficiency.

• Investigating and resolving data issues across platforms and applications, including discrepancies of definition, format and function.

• Developed mappings with the aid of Informatica Tool, which transfers data from the files to the HCSIS Data warehouse database.

• Developed mappings, which matches potential consumers with HCSIS. Created Catalogues and Administration.

• Designed and created the database, which stores the data generated from Informatica mappings.

• Developed various reports using Cognos Impromptu 7.1.

• Developed cubes with the aid of Cognos Power Play Web Transformer.

• Manually transferred data to the Cognos web server after the refreshment of cubes.

• Refreshed data weekly from the files to the database

• Refreshed the cubes weekly using Power Play Transformer

• Generating High Level Architecture, High Level Design, ETL Mapping, OLAP Reporting and Process Flow diagram documents.

• Created UAT test plan.

• Oversee users during UAT

• Created Production Migration Playbook.

• Oversee migration to production.

• Provided training to user on Cognos user tools

Environment: Cognos Impromptu 7.1, Power Play Transformer, Informatica 5.1, Oracle 8i, Erwin, MS-Access, Visio, Windows 2000.

State Government of Pennsylvania, PA 06/2001 – 05/2003

Subject Matter Expert

Windows based Internet-enabled (HCSIS) health care application using technologies .NET, XML, Oracle 8i/9i and COM +. This is the pilot system to implement the Oracle Fine Grained Access Control (FAGC). This has enabled department of public welfare to manage the information at the tip of fingers based on the role and scope of the user to make administrative functionality as real time system, improving the lives of thousands of individuals. I have the highest pride being a part of this system, which has the direct impact on the society and the mentally impaired individuals.

• Involved in the System Analysis, Design and Development of the project.

• Gathering User Requirements.

• Generation of Business Logic Description document, which provides a prototype to the client of the business and technology supported.

• Created Flow Diagrams with Visio, Report Mockups in the design phase of the system.

• Leading the design team.

• Leading the development team.

• Analysis, Design and development of different subsystems.

• Designed the physical and logical database using Erwin...

• Designed and developed core business layer objects as VB.NET Assemblies to interact with data layer using ADO.NET and Oracle data provider for .NET.

• Integrated ADO.NET type Datasets/Data reader with XML/XSL, XSD and DTD for end-to-end data integration.

• Implemented a hybrid application deployment with .NET objects, existing ASP pages and VB COM components.

• Design and developed various batch jobs for the system with OPCON and web methods.

• Extensive development of VB COM components and deployment on COM

• Extensive Design and development of oracle stored procedures/functions/packages.

• Extensive Design and Development of asp pages using ASP, Java Script and HTML.

• Developing common functionality.

• Created migration playbooks for the different subsystems while migrating to higher environments.

• Trouble shooting and business and technical support to the team

• Design and Development of following subsystem like, Register with MRIndividual Service Plan (ISP), Develop Budget and Allocate Funds, Financial Management, HCSIS MCI Interface, HCSIS CIS Interface, HCSIS PROMISe Interface

Environment: Microsoft. NET, ASP.NET, XML, XSL, Oracle 8i/9i, Windows 2000, Web Services, Microsoft Visual InterDev, ASP, XML, VB Script, Java Script, COM-DCOM and Active-X, COM +, MS Visual SourceSafe, Internet Explorer, MS Project, Visual Basic, VB Script, Windows 2000, OPCON, Web Methods, Visio, Erwin

Kmart Corporation, MI 05/1998 – 5/2001

Programmer Analyst

The Web Enabled EIS system is created to provide sales report to the Managers of Kmart. This system is interfaced with Mainframe to obtain Sales information, Customer grouping Sales, Margin Management Report. The data is displayed on the web for analyzing the sales and to track details from highest level - divisions drilled to the lowest level - SKU. The system also uses Pilot Internet Publisher dll for handling the data with multi-dimension database using Pilot analysis Server. The system uses UNIX operating system and allows the users to view the reports over the web. There are different applications involved in this project. Conversion of web based sales reports to excel spreadsheet format is one of the application, which allows the managers to download the report and modify according to their needs

• Involved in the System Analysis, Design and Development of the project.

• Involved in Coding and Unit Testing of all the modules of Daily Sales Reports, Super Service Index and Daily Category Sales reports for conversion of Web based reports to excel spreadsheet format. This is implemented using server-side VB scripting and client-side JAVA scripting.

• Involved in Coding and Unit Testing of all the modules of Daily Sales Reports, Super Service Index and Daily Category Sales reports for copying a Web report and pasting it to clipboard. This is implemented using server-side VB scripting and client-side Java scripting.

• Involved in Coding and Unit Testing of all the modules of Daily Sales Reports, Super Service Index and Daily Category Sales reports for the printing the whole report window

Environment: Windows NT, SQL Server 7.0, Visual InterDev 6.0, Visual Basic 6.0, IIS 4.0, Power Designer 6.0, ScriptX, ASP 2.0, Plumtree portal 4.0

Taj Group of Hotels, India 06/1996 – 04/1998

Programmer

This project is conversion of Banquet Reservation System used by Taj Group of Hotels. The application was originally done in VB 3.0 and was converted to VB 6.0 and SQL Server 6.5 database. The major steps in this project are to convert the btrieve files to SQL tables, design indexes constraints (i.e. Primary Key and Foreign key) including identity constraints, converts queries into views or stored procedure depending upon the availability of parameters, DAO data accessing objects and methods were replaced by ADO objects and methods using Visual Basic 6.0. This project was also developed on ASP 2.0 for browsing Hall Details, Seating arrangement available in halls and Hall Availabilities using VBScripting and JavaScript.

• Performed the conversion of the DAO data accessing code in the project to corresponding ADO data accessing code using Visual Basic 6.0.

• Worked with Visual Basic IDE objects to create VB project, VB Form with all control & coding, VB Module & all other VB components.

• Performed the conversion of queries into SQL views and stored procedures using MS-SQL 6.5.

• Involved in Design and Development of Hall Details Module using Active Server Pages

• Performed Unit Testing and Documentation

• Environment - Windows NT, SQL Server 7.0, Visual InterDev 6.0, Visual Basic 6.0, IIS 4.0, Power Designer 6.0, ScriptX, ASP 2.0

Contact this candidate