Data Analyst/ ETL Developer

SUMMARY:

More than * Years of IT experience in Analysis, Designing & Development, Implementation & Maintenance of Database and BI Applications using MS SQL Server 2014,2012,2008R2,2008 in Development, UAT and Production Environments.

Experienced in all the stages of Software Development Life Cycle and in the migration and upgradation of databases.

Accommodated and facilitated Joint Application Development (JAD) session, documented, analyzed and synthesized the results as parts of business requirements to prepare Business Requirement Document (BRD).

Involved in Quality Assurance, bug fixing and testing, Prepared user manuals, presentations and trained user and operators.

Experience working with multiple/cross-functional groups, conducting brainstorming/root cause analysis sessions.

Extensively worked on OLTP Relational Schema with Normalization and OLAP Dimensional Modeling using Star and Snowflake schema.

Experience in database design with T-SQL through conceptual Entity Relationship Diagrams and Erwin.

Expertise in creating database objects such as Stored Procedures, Tables, Constraints, Indexes, Views, Indexed Views, and Triggers using SQL Server.

Experience in troubleshooting SQL issues by using SQL Tools, Execution Plans, Trace, Statistics and Index Tuning Wizard.

Experience in using Performance Monitor, SQL Profiler and optimizing the queries, tracing long running queries and deadlocks.

Hands on experience in creating end to end ETL strategies that will include data profiling as well as extracting, transforming and loading data warehouses/ data marts for reporting purpose in both SSIS and Informatica Power Center (Repository Manager, Designer, Workflow Manager, Workflow Monitor).

Developed complex mappings in Informatica using variety of PowerCenter transformations, Mapping Parameters, Mapping Variables, Mapplets & Parameter files in Mapping Designer using both the Informatica PowerCenter and IDQ.

Expertise in developing ETL processes using Informatica, including ETL control tables, error logging, auditing, data quality, data conversion etc.

Designed SSIS packages using various Transformations such as Fuzzy Lookup, Fuzzy Grouping, Aggregate and Derived Column and many other Transformations.

Experience in Error Handling and Debugging packages using Checkpoints, Data Viewers, Breakpoints, Event Handlers and Loggings and securing packages secured packages with Encryption in conjunction with passwords.

TECHNICAL SKILLS:

Data Modeling : ER-Win, MS Visio.

Data base : Teradata 13, Oracle 11g, SQL Server 2008, 2008R2, 2012, 2014.

BI Tools : BIDS/SSDT, SSIS, SSAS, SSRS, PowerPivot, Power View, Power BI, Informatica 9.x,10.2 .0 HotFix 2

Languages : T-SQL, ANSI SQL, C#.Net.

Operating Systems : Windows XP, 7, 8, Windows Server, Tableau Server

SDLC : Spiral Model, Kanban, Waterfall, Agile, Scrum.

Others : MS Dynamics, CRM, TFS, VSS, SharePoint Dashboard.

PROFESSIONAL EXPERIENCE:

FannieMae, MD Jan 2017 – Till Date

Sr. SQL ETL Database Developer/Informatica Developer

Multifamily Mortgage Business [MMBD] Application is a Multifamily mortgage business analytical application provides various services for their business users providing Loans, Properties, Deals, Quotes, Pricing Analytics, Portfolio management, REO Properties, Risk management, Housing goals, Loss Reimbursement Statement (LRS), Loss mitigation etc modules.

Responsibilities:

Involved in business requirement gathering from the end users and convert into Technical aspects by providing optimal solutions.

Worked on data warehouse such as dimensional modeling, Star/Snowflake schemas, datamarts, fact and dimension tables identification and OLAP.

Involved into multiple projects, Migration from MS access DB to SQL Server, upgrading from SQL server 2008 to 2012 including SSIS packages and SSRS reports. Performed GAP analysis for building data mart involving into housing goal scoring and reporting projects.

Involved in maintenance of existing .Net windows based WPF application by providing various scoring and reports utilizing .Net code behind.

Responsible for database, complex ETL development activities like extracting data from heterogeneous systems and from various different files etc. for performing business operations and applying rules and handling production support for the operational data loads utilizing SQL Server, Oracle, SSIS and SQL Agent.

Responsible for creating and maintaining various database objects like Stored Procedures, Views, functions, Triggers and Constraints etc. to meet the business needs.

Created Indexes, Views, ANSI Join, Index views, Constraints, Stored Procedure, User-Defined functions, CTEs, and effective DDL/DML Triggers to facilitate efficient data manipulation and data consistency.

Responsible for utilizing various control flow tasks, data flow elements, event handling, logging, configuring, deploying and scheduling SSIS packages at various stages in different environments.

Responsible for performance enhancements using optimization techniques such as Normalization, Indexing and Transaction Isolation levels.

Responsible for creating and maintaining the Source to Target mapping documentation for implementation and to track future changes.

Worked on Informatica Power Center tools like Designer, Repository Manager, Workflow Manager, and Workflow Monitor for RAD Application and also Created mapplets and worklets to use them in different mappings for Reusability.

Developed mapping parameters and variables to support SQL override.

Parsed high-level design specification to simple ETL coding and mapping standards, Developed mappings to load into staging tables and then to Dimensions and Facts. Used SCD Type 1 and SCD Type 2 logic in mappings.

Created sessions, configured workflows to extract data from various sources, transform, and load into data warehouse.

Modified existing mappings for enhancements of new business requirements.

Used Debugger to test the mappings and fixed the bugs.

Involved in Performance tuning at source, target, mappings, sessions, and system levels.

Prepared migration document to move the mappings from development to testing and then to production repositories.

Responsible for implement Unit testing and performance improvement purposes.

Reviewed software documentation to ensure technical accuracy, compliance or completeness or to mitigate risks.

Reviewed user test cases, defects and validated test results during user acceptance testing (UAT).

Environment: MS-SQL Server 2014/2012 Enterprise Manager, Oracle SQL Developer, MS-Access 2010/2007, TOAD, Erwin, MS Business intelligence (BIDS) [SSIS, SSRS, SSAS], SharePoint Server 2010, XML, MS Office 2013, MS Visio 2010, VB.Net, C#. Net, Visual Studio 2013/2010/2008, Windows Server 2010, Agile, Windows 10, Informatica 9.x,10.x, Github, Jenkins etc

BerkleyMid Atlantic Group - Richmond, Virginia Jun 2016- Dec 2016

SQL BI Developer/Business Analyst Qualitative Assurance

Berkley Mid-Atlantic Group is a commercial property and casualty insurer protecting a wide range of businesses and market segments. Our policies are issued exclusively through independent agents, who serve our insured’s throughout the Mid-Atlantic region and we are a member company of W. R. Berkley Corporation.

Responsibilities:

Design and implement new BI/reporting solutions as per requirements gathered with business stakeholders.

Supported Business Intelligence, Data Warehouse bug fixing and daily money imbalance tickets.

General production support including SQL querying databases, SQL Server Integration Services, SQL Server Reporting Services and SQL Server Analysis Services report creation running recurring reports.

Developed SQL scripts to resolve production issues and create reports.

Data Investigation and reporting – Extracts data from operational systems and reporting databases using a variety of tools, including SSIS.

Worked on identifying relationships within the data and between databases, understands requirements of the customer, and the location of required data.

Answers to developer and end user questions through the development life cycle.

Complete functional testing on assigned projects.

Prepared documentation of methods, business requirements, SQL coding, and reporting criteria to a level consistent with departmental standards.

Explicitly used third party tool like Red gate for SOX auditing code schema changes for production servers VS previous change sets and submitted weekly and quarterly schema changes to KPMG.

Handled all the deployments using TFS from development to production servers using Footprint ticketing tool.

Worked on generating monthly renewals after the month end load.

Worked on couple of projects like encrypting Personally identifiable information in data warehouse and Guide wire project etc.

Handled Performance Tuning and Optimization on SSIS, with strong analytical and troubleshooting skills for quick issue resolution in large-scale production environments located globally.

Written automated batch scripts to pull files from SFTP path and process the files and backup them to archived path and delete from source path.

Written various T-SQL scripts to check whether data is ready from different sources and to view database tables log that accessed last time.

Used master child SSIS packages for better design and ease of maintenance.

Verify backend data with frond end Agency dashboard application.

Rebuilding Indexes and statistics as part of performance tuning exercise.

Created complex stored procedures, functions, ETL packages.

Worked with a team of business analysts, data architects, application developers, ETL developers to fix policy/clearance related data in the data warehouse.

Involved in debugging of packages with help of Breakpoints, Checkpoints, Event Handlers and Loggings.

Environment: SQL Server 2008 R2, 2014, T-SQL, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), MS Excel, Windows Servers, SharePoint, TFS.

South Carolina Department of Revenue — Columbia, South Carolina Nov 2015 – Jun 2016

SQL Server ETL/SSIS/SSRS Developer

The South Carolina Department of Revenue administers 32 taxes, collecting nearly $9.8 billion annually. Our mission is to administer the revenue and regulatory laws of this state in a manner deserving the highest degree of public confidence in our integrity, effectiveness and fairness. The Department will administer these duties with a focus on information security and the protection of taxpayer information.

Responsibilities:

Developed Complex Packages to load data into Stage, ODS and Data Ware House systems.

Involved in automating SSIS Packages using SQL Server Agent Jobs, Window Scheduler, and third party tools like Control m.

Loading data from various sources like OLEDB, flat files, MS Access to SQL Server database Using SSIS Packages and created data mappings to load the data from source to destination.

By using the java logic from Embarcadero tool, developed SSIS packages.

Experience in Performance Tuning in SSIS packages by using Row Transformations, Block and Unblock Transformations.

Modified Java Refresh to run post ETL process which cleanses data, A&G to automate the package.

Extensive use of DTS packages and BCP utility including extensive knowledge of exporting and importing data to and from SQL Server to any OLEDB and ODBC provider.

Involved in Monitoring and troubleshooting complex packages by using Data Viewers Grids.

Created traces using SQL server profiler to find long running queries and modify those queries as a part of Performance Tuning operations.

Deployed SSIS Package into Production and used Package configuration to export various package properties to make package environment independent.

Generated periodic reports based on the statistical analysis of the data from various time frame and division using SQL Server Reporting Services (SSRS)

Extensive working knowledge in Application testing, Data Modeling, Data migration, Data Warehousing, and OLTP, OLAP Database projects.

Troubleshoot problems by checking sessions and workflow logs.

Involved in code reviews, change control and auditing procedures.

Developed different kind of reports such a Drill-through, Drill-down, Sub Reports, Charts, Matrix reports, Linked reports in SSRS

Written and executed complex T-SQL queries using SQL Server Management Studio for back end data validation testing.

Used cascaded parameters to generate a report from two different Data Sets.

Coordinated with the front-end developer to put in the business rules. Created procedure to check some of the rules at the backend at the time of entering data.

Environment: SQL Server 2008/2008 R2, T-SQL, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), MS Excel, ER-Win, Windows Server, SharePoint, Power Pivot, Micro Strategy Analytics, TFS.

BroadRidge Financial solutions – Jersey city, New Jersey Jun 2015 – Oct 2015

Senior SQL Server ETL/SSIS/SSRS Developer:

BroadRidge Financial Solutions, Inc. is the leading provider of investor communications and technology-driven solutions for wealth management, asset management and capital markets firms. We help drive operational excellence to manage risk, accelerate growth and deliver real business value.

Responsibilities:

Implemented the agile process in the scrum methodology and integrated all the scrum processes with our TFS environment.

Involved in designing of Sprint stories and further down to tasks.

Created new database objects like Procedures, Functions, Indexes and Views using T-SQL.

Created Service Broker Queue for internal or external processes to send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML).

Utilized Dynamic SQL for user customizable queries to be answered by the SQL Server.

Data mapping between Scot trade’s back office CRI to Broad ridge back office BPS/A.

Migrated existing Account numbers from CRI to BPS/A and implemented a logic in our development to generate account number on daily basis to satisfy Scot trade’s new customers.

Worked with .net mid-tier team to insert and pull SBQ messages asynchronously.

Implemented different built in transformations tasks including SCD, Aggregate, Lookups and Data Conversion to implement complete ETL strategy.

Used various Package configurations available for SSIS Packages like XML files and SQL Server Tables.

Implemented the Master Child Package Technique to manage big ETL Projects efficiently.

Involved in debugging the Packages by utilizing SSIS features like breakpoints, Data-Viewers and custom loggings.

Designed SSIS package templates as base code for package development incorporating package configurations, connection managers and logging in it.

Automated the ETL process by scheduling SSIS package execution by creating jobs.

Created QA (Quality Assurance) scripts and sanity checks to further test ETL packages by comparing QA output with output from ETL package.

Tested the packages developed in DEV server to find out any bug and handled the errors encountered at the time of testing.

Scheduled and monitored all maintenance activities including database consistency check and index fragmentation.

Involved in trouble shooting and fine-tuning of database for its performance.

Created Parameter-driven reports using parameters tab in the SSRS GUI procedures by Setting up the report-level parameters, along with Building parameter-driven queries and stored and placing them, in the data field.

Created ad-hoc reports using power pivot and performance point extracting data from heterogeneous sources.

Identified and defined the Datasets for the report generation and wrote the queries for the drill down reports, identifying and adding the report parameters.

Developed, designed & implemented - Executive Dashboards (BI), Marketing Analytics, and Life cycle using Micro Strategy Analytics.

Created package configurations to provide an easy solution to migrating packages from different business environments.

Environment: SQL Server 2008 R2, 2014, T-SQL, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), MS Excel, ER-Win, MDX, Windows Server, SharePoint, Power Pivot, TFS.

Genworth Financial, Richmond, VA Nov 2014– Jun 2015

SQL Server BI/Informatica Developer

Genworth financial has leadership positions in products and services that assist consumers in protecting themselves, investing for the future and planning for retirement. Including life insurance, long term care insurance, financial protection coverage’s, and independent advisor-based wealth management and mortgage insurance that helps consumers achieve home ownership while assisting lenders in managing their risk and capital.

Responsibilities:

Responsible for data analysis, data designing, data mapping, data quality, business and Minimum data requirements gathering, Technical Specification writing, project management.

Created and refined the performance of various SQL scripts and stored procedures by using UDFs, CTEs and System stored procedures along with usage of tools like SQL Profiler.

Used various SSIS tasks such as Conditional Split, Derived Column, which were used for Data Scrubbing, data validation checks during Staging, before loading the data into the Data warehouse.

Used SSIS to implement the SCD to Implement Historical Data load into Data warehouse.

Involved in debugging of packages with help of Breakpoints, Checkpoints, Event Handlers and Loggings

Handled Performance Tuning and Optimization on SSIS, with strong analytical and troubleshooting skills for quick issue resolution in large-scale production environments located globally.

Automated the ETL process by scheduling SSIS package execution by creating jobs.

Performed Unit testing and Data validation testing using Validation scripts.

Extensively worked on Power Center Client Tools like Power center Designer, Workflow Manager, and Workflow Monitor.

Designed and Developed ETL mappings using transformation logic for extracting the data from various sources systems.

Extensively used various transformations like Source Qualifier, Joiner, Aggregation, Update Strategy, lookup, Rank and Filter.

Responsible for the Performance tuning at the Source Level, Target Level, Mapping Level and Session Level.

Used the feature EXPLAIN PLAN to find out the bottlenecks in a given Query, thus improving the performance of the job.

Created Process Control and Metadata for informatica jobs.

Created Reusable and Non-Reusable command task in Workflow Manager.

Extensively used Teradata utilities like Fast load, Multiload to load data into target database.

Documented ETL test plans, test cases, test scripts, test procedures, assumptions, and validations based on design specifications for unit testing, system testing, expected results, preparing test data and loading for testing, error handling and analysis.

Involved in providing 24/7 production support to solve critical issues during extended hours and also worked with offshore team.

Environment: ER-Win, SQL Server 2008R2/2012, SQL Server Agent, SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), Informatica 9.x, SharePoint, TFS, Power Pivot, Windows Server, Tableau (Desktop, Server).

ProLeverage IT Solutions, CA - Internship Oct 2013 – Nov 2014

Jr. SQL BI Developer

ProLeverage IT Solutions is an Information Technology Consulting organization with deep expertise in delivering solutions to its fortune 500 clients in the areas like Application Development, Data Integration, Data Warehousing, and Business Intelligence.

Responsibilities:

Data warehouse design using Top down and bottom up and Data Mart design using Star Schema and Snow Flake schema.

Responsible for making weekly backup databases by using SQL Server 2012 and restore databases in different servers.

Responsible for data analysis, data designing, data mapping, data quality, business and MDR (Minimum data

requirements) gathering, Technical Specification writing, project management.

Created and refined the performance of various SQL scripts and stored procedures by using UDFs, CTEs and

Created complex stored procedures to perform Index maintenance, data profiling, metadata search, critical

staging procedures for loading data marts and reporting database.

Used various SSIS tasks such as Conditional Split, Derived Column, which were used for Data Scrubbing, data

validation checks during Staging, before loading the data into the Data warehouse.

Used C#.Net to write Script in SSIS tasks like Script Task and Script Component.

Used SSIS to implement the Slowly Changing Transformation, to maintain Historical Data in Data warehouse.

Created Sequence in SQL Server 2012 to insert same identity value in multiple dimension tables as per the

business rules.

Created SSISDB catalog with SQL Server 2012 to support SSIS package deployments in SSMS with Environment

variables respective to each environment.

Involved in debugging packages with help of Breakpoints, Checkpoints, Event Handlers and Loggings.

Handled Performance Tuning and Optimization on SSIS, with strong analytical and troubleshooting skills for quick

issue resolution in large scale production environments located globally.

Automated the ETL process by scheduling SSIS package execution by creating jobs.

Developed SQL based test and audit scripts to validate SSIS packages and data loads, SSRS reports and report

stored procedures.

Created several reports using SSRS like drill through reports, linked and sub reports.

Deployed SSRS reports to the reporting server in SharePoint Integrated mode and assisted in troubleshooting

deployment problems.

Created tabular models and reports that retrieve data and aggregates directly from a relational database system, using Direct Query mode.

Partitioned the Tabular models for optimized processing and secured to the row-level by using role based security.

Generated reports using SSRS and Excel Spreadsheets with Power pivots and deployed them in SharePoint integrated mode.

Used Power View and created various analytical reports and Dashboards from Tabular Models to help managers to identify critical KPIs and facilitate strategic planning in the organization.

Environment: ER Win, SQL Server 2008R2/2012, SQL Server Agent, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), SharePoint, TFS, Oracle 11g/12c, Windows Server.

Contact this candidate