SUMMARY

• 7 years of IT experience including Design, Development & Support of BI Applications using MS SQL Server and BI Stack (SSIS/SSAS/SSRS) in Production and Development Environments.
• Worked on accommodating and facilitating Joint Application Development (JAD) sessions, documented, analyzed, and synthesized the results as a part of the business requirements.
• Well versed in creating OLTP relational data models and OLAP dimensional models using ER - Win.
• Proficient in writing T-SQL queries for creating Tables, Stored Procedures, Views, Indexes, Triggers, User Defined Functions, Relational Database Models and Data Integrity in observing Business Rules.
• Strong experience working with batches and scripts to perform ad-hoc queries and other related tasks.
• Extensive work with stored procedures, indexes, views, functions, and triggers on creation and optimization to improve response time and to enforce business requirements through T-SQL.
• Thorough knowledge on working with database maintenance including using Database Management Views, Dynamic Management Functions and DBCC commands to improve table fragmentation and index performance.
• Hands on experience working with Star and Snowflake schemas within SSIS and OLAP data warehouses.
• Hands on creating end to end ETL strategies that will include data profiling as well as extracting transforming, conforming and loading.
• Good experience in creating and managing very complex packages responsible for refreshing a data warehouse in a near real time ETL environment using SSIS (SQL Server Integration Services)
• Expertise with data cleansing operations using SSIS transformations and T-SQL scripts to normalize the data and remove and identify potential problem points when moving information into a data warehouse.
• Experienced in creating and managing end to end ETL strategies by creating Master/Child Package Models to ensure that only the Master Package is scheduled which will in turn execute the other packages.
• Developed complex mappings using SSIS to transform and load the data from various source systems like Excel, Flat files, Oracle into different version of SQL Server.
• Thorough knowledge of moving packages between the development environment to a production environment utilizing Parameters, XML package configurations and changing connection strings.
• Experience optimizing SSAS using the flight recorder to log queries submitted to the Analysis Services Engine and perform usage based optimization to improve the pre-calculated aggregates for reports.
• Extensive experience in different phases reporting using SSRS 2008/20012 (Authoring, Managing and Delivering.
• Experience creating reports which provide a visually appealing and descriptive representation of information utilizing conditional formatting.
• Hands-on experience setting up schedules for push and pull delivery methods using Standard and Data Driven subscriptions in SSRS; for different formats such as PDF, Excel, and Text.
• Developed Dashboard solutions for reporting, as well as published the Dashboards onto Web pages using Power BI, and SharePoint.
• A well-organized, goal-oriented, highly-motivated effective team member with excellent analytical, troubleshooting, and problem solving skills.

TECHNICAL SKILLS

SDLC: Agile, Scrum, Spiral, Waterfall

RDBMS Systems: SQL Server 2005/2008/R2/12, MS Access

Database Tools: SSMS, SQL Profiler, DTA, ER-Win, MS Visio

Business Intelligence: BIDS/SSDT, SSIS, SSAS, SSRS, Power BI, SharePoint, Power View, Power Pivot, Power BI

Languages: SQL, T-SQL, Java, Android Studio, HTML5, CSSVersion Control Team Foundation Server (TFS)

PROFESSIONAL EXPERIENCE

ETL/BI Developer

Confidential, Phoenix, AZ

Responsibilities:

• Involved in JAD sessions with Project Managers, Developers, Design Architects, and Design Modelers to solve deadlocks in the projects and to discuss the feasibility of a new idea being incorporated as a module.
• Transform the Business Requirements into Functional and Non Functional Requirements and interface between the client and the development team.
• Analysis of standard and specific business requirements and implementing the analysis in a stored procedure for use on a report.
• Perform error handling in T-SQL batches and test scripts using try catch and generated user defined error messages using throw in SQL 2012.
• Wrote complex stored procedures for index maintenance, data profiling and reporting purposes.
• Designed the 'Dimensional Model' of the Data Mart (Kimball Approach) and used Erwin to design the business process, grain of the data representation, dimensions and fact tables with measured facts using Star Schema.
• Created complex ETL (SSIS) packages to pull data from Oracle database, and load them to SQL server.
• Created SSIS packages for cleansing bad data from legacy system using Derived column, Lookups, Fuzzy Lookups, Conditional split.
• Identified and handled early arriving Facts and late arriving dimensions using inferred dimensions.
• Implemented the Master Child Package Model for easier maintenance and better performance of complex ETL strategies.
• Implemented Error Handling during ETL load in SSIS packages to identify dimensions and facts that were not properly populated.
• Performed troubleshooting of SSIS packages with help of Breakpoints, Checkpoints, Event Handlers and Loggings.
• Configured SSIS package properties using project and package level parameters.
• Created Integration services catalog (SSIDB) using SQL 2012 to deploy the SSIS packages.
• Optimized packages by avoiding use of non-blocking transformation, executing tasks in parallel and optimizing code at source.
• Developed SQL-based test and audit scripts to validate SSIS packages and data loads.
• Scheduled SQL Server Agent Jobs for executing SSIS packages.
• Documented the technical specifications and generated the reports for the implementation and testing of the SSIS packages.

Environment: SQL Server 2012, SSDT, SSIS, ER-Win, SSMS, T-SQL, Windows Server, TFS, Oracle, DTA, SQL Profiler

ETL/SSIS Developer

Confidential, San Diego, CA

Responsibilities:

• Involved in analyzing business requirements by conducting JRD session that will facilitate the planning and the implementation phase of a data mart.
• Used Erwin tool for dimensional modeling (Star schema). Forward Engineered Data Model of Staging Database to SQL 2012 Server.
• Created complex stored procedures to perform Index maintenance, data profiling, metadata search, critical staging procedures for loading data marts and reporting database.
• Worked on Index analysis for tables and came up with more efficient solutions to use Clustered and Non-Clustered Indexes for significant performance boost.
• 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 and Database Tuning Advisor.
• Monitored and Tuned SQL Server performance and, created baselines using log-file viewer activity monitoring system, DBCC utilities (database consistency checks), SQL profiler and Database Tuning Advisor (DTA).
• Implemented ETL strategy to transfer data from existing data warehouse to new data mart.
• Used SSIS packages to extract, transform and load data from multiple input sources like flat files, Production SQL Servers, Excel, etc. into SQL Server 2012 Staging Database.
• Used various transformations such as Derived Column, conditional split, fuzzy grouping & lookup, etc. for data scrubbing and cleaning.
• Used best practices for SSIS design such as Master Child architecture to leverage parallel execution of various packages.
• Written C# script to load data from multiple sheets of an Excel work book and to customize the loading from flat files.
• Implemented error & failure handling in SSIS packages using event handlers, row redirects, check points and used loggings for monitoring the execution.
• Added parameters to each package to configure various properties in order to deal with different servers like Development, Testing and Production.
• Involved in the debugging and troubleshooting of SSIS Packages using Microsoft best practices.
• Designed and developed SSIS Package template, so similar development practices are followed by all SSIS developers for logging, configurations and error handling.
• Deployed the entire ETL application to SSISDB catalog and automated the execution of SSIS packages using SQL Server Agent Jobs.
• Successfully performed and documented unit testing and system testing of the entire ETL Process.
• Developed drill through, drill down, linked, sub reports and parameterized reports for better business analysis using SSRS.
• Responsible for identifying and defining the datasets for the report generation, writing the queries for the drill down reports, and identifying and adding the report parameters.
• Formatted the reports by applying parameters, and assigned parameter defaults and data types and extended reports as well as invoked embedded functions and integrated assemblies using expressions.
• Involved in Report generation using SSRS and Excel services, Power BI and deployed them on SharePoint Server.
• Used Power View and created various analytical reports andDashboards tohelp managers to identify critical KPIs and facilitate strategic planning in the organization.
• Optimized the reports by optimizing the embedded queries, or optimized the stored procedures that generate them.
• Deployed SSRS reports to the reporting server and assisted in troubleshooting deployment problems.
• Generated different schedules for report delivery to e-mails and shared directories; enabled snapshots, subscriptions, caching to improve the response times and reduce traffic to the report server.
• Used TFS for the project version control & change management and bug tracking.

Environment: SQL Server 2012, SSDT, SSIS, SSAS, SSRS, ER-Win, Windows Server, SharePoint, Power BI, Power View, T-SQL, TFS

ETL/Reports Developer

Confidential, San Diego, CA

Responsibilities:

• Created System requirement gathering document based on extensive JRD sessions, that yielded the conceptual design.
• Developed and created logical and physical Database architecture using ER-Win Data Modeler.
• Analyzed the historical data and extracted required data to support the management decisions.
• Maintained data integrity by creating various constraints and DML triggers the relational databases.
• Created complex stored procedures and functions to support efficient data storage and manipulation.
• Optimized and restructured the Approval Workflow Stored Procedure for the application by evaluating Execution Plans, indexing and Partitioning large tables.
• Enhanced the functionality and performance of various SQL scripts and stored procedures by using UDFs, CTEs and System stored procedures along with usage SQL Profiler and DTA.
• Designed the ETL process using SQL Server 2008R2 SSIS Packages in which the data sources include Oracle.
• Involved in Full and daily Incremental loads into Staging and ODS areas, troubleshooting process issues and errors using SQL Server Integration Services (SSIS) 2008 R2.
• Included transformations like Slowly Changing Dimensions (Type 1 & 2), Look up, Derived Column, Aggregate, Fuzzy Lookup, Multicast, Conditional Split, Data Conversion, etc. in SSIS Packages.
• Designed and developed control tables that had to be used by various SSIS developers to store and retrieve source and destination Connection strings information, Incremental column and data info, table and columns names for Meta data driven implementation etc.
• Enabled Checkpoints on already implemented transactions so as to optimize entire ETL strategy so as to start the package execution from latest point of failure.
• Used various Package configurations available for SSIS Packages like XML files and SQL Server Tables for development, test, and production environments.
• Involved in debugging of packages with help of Event Handlers, Loggings, Breakpoints, Checkpoints.
• Created QA scripts and Stored procedures for SSIS packages to perform various QA operations during ETL i.e. data verification and Validation.
• Created several ad hoc reports, drill down, drill through, sub, linked, cached and snapshot reports using reporting services.
• Designed and deployed 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.
• Using Power Pivot, and created multiple rich dashboards visually telling stories of the business status, strength & weakness, potentials etc. for the company at a glance, also interact with data as necessary.
• Exported data from a report to several file formats likes PDF, Image and Excel according to the needs of management.
• Supported production environment by optimizing existing stored procedures and scripts.
• Deployed reports to the Report server in Native Mode and SP integrated mode and implemented Report delivery using Standard and Data driven Subscription in SSRS.
• Provided security for accessing the reports at the server level, report level and report item level.
• Used TFS to keeping track of the history, merging code between the different versions of the software, check-in/check-out options.

Environment: MS SQL 2008R2, BIDS, SSIS, SSAS, SSRS, T-SQL, ER-win, Oracle, Excel, SharePoint, Windows Server, TFS, Power Pivot

BI/DW Developer

Confidential, San Diego, CA

Responsibilities:

• Conducted major stakeholder interviews involving SME’s, Business Analysts and other stakeholders to determine the requirements.
• Translated the business requirements into workable functional and non-functional requirements at detailed production level by conducting JAD session.
• Created database objects like tables, views, indexes, indexed views stored procedures UDFs. Created and modified stored-procedures, triggers, cursors, and views in support of day to day admin duties and reports.
• Created several materialized views, stored procedures, and UDF’s which are used by the front end development team.
• Created a partition scheme, partition function to improve the performance of queries, reduce contention and increase the availability of data.
• Wrote complex store procedures for data profiling process needed to define the structure of the pre staging and staging area.
• Redesigned and tuned stored procedures, triggers, UDF, views, indexes and was successfully able to increase the performance of the slow running queries.
• Designed SSIS Packages to transfer data between servers, load data into database, and archived data file from various heterogeneous sources such as SQL Server, Excel, and CSV etc. on to SQL server 2008R2.
• Created complex SSIS packages (master-child) with properties like package configurations, SSIS loggings as well as custom loggings (using event handlers).
• Deployed the SSIS packages to MSDB database and automated the execution of those by scheduling them through SQL server agent jobs.
• Assigned permissions to users based on business requirements by creating roles (full control, process database, and read definition).
• Extensively involved in all the phases of report life cycle (Authorizing, Managing, and Delivery).
• Created ad-hoc reports, and complex enterprise reports (drill down, and drill through reports, parameterized reports, cascading report) using SQL Server Reporting Services and report manager. Created linked reports using report builder.
• Used Excel Power View and Power Pivot to create various analytical reports andDashboards tohelp management identify critical KPIs and facilitate strategic planning.
• Involved in report execution based on caching and snapshot and managed delivery of reports by creating standard subscriptions and data-driven subscriptions.
• Involved in trouble-shooting of reports in authoring, managing, and delivery phases on multiple servers.
• Used notification services to generate error messages and send them to the user through e-mails.
• Generated test data and tested the database to meet the functionality deliverables in the project documentation and specifications.
• Supported the logging framework by generating reports to identify the trends and set up the strategy to find bug trends, code changes using Team Foundation Server (TFS).

Environment: MS SQL Server 2008R2, BIDS, SSIS, SSAS, SSRS, T-SQL, Windows Server 2008, Erwin, Excel, TFS, SharePoint, Power Pivot

SQL/SSIS Developer

Confidential, Carlsbad, CA

Responsibilities:

• Involved in interacting with the Business Users and the Business Analysts to understand the requirements for development.
• Involved in Normalization and De-Normalization of existing tables wherever needed, for faster query processing.
• Encapsulated frequently used SQL Statements into stored procedures thereby reducing the query execution time.
• Altered and debugged some of the existing Stored Procedures to avoid complex Dynamic SQL queries.
• Created different SSIS packages to extract data from Excel and SQL Server connection source to create dimensional model for reporting purposes.
• Used appropriate data conversion tasks to convert data between different data types while transforming data from RAW to STAGE database.
• Used derived column transformation to handle package variables and derived values to load data in the Fact table.
• Used Cache Transform task to look up with the data coming from flat file to load the data in the Fact Table for a measure.
• Used XML and SQL Server table configuration for the management and migration of SSIS packages in the staging and pre-production environments.
• Involved in the troubleshooting of several package and solved several issues related to execution, development and performance.
• Responsible for the management and migration of packages in the staging and pre-production environments.
• Schedule jobs using SQL server agent to automate ETL packages to run as per the requirements.
• Created different dashboard reports for business stakeholders for POC of Data Warehouse implementation.
• Added SSIS packages and SQL codes to TFS for version control and change management.

Environment: MS SQL Server 2008R2/08, BIDS, SSIS, SSAS, SSRS, T-SQL, Erwin, Excel, TFS

SQL Server Developer

Confidential, San Diego, CA

Responsibilities:

• Gathered requirements from the client, Perform Gap Analysis, Facilitate JAD Sessions.
• Transformed the Business Requirements into Functional and Non Functional Requirements and interface between the client and the development team.
• Designed standards for database table, Column, Procedures and Views.
• Used Erwin tool extensively and performed the logical and physical data modeling
• Help database architecture to modify physical and logical data model for new add tables.
• Analyze the requirements and fine tune the stored procedures to improve performance.
• Involved in writing T-SQL Programming for implanting stored procedures and Functions for different tasks.
• Managed the entire ETL process involving the extraction, analysis, transformation, loading and presentation of information to the end users.
• Created packages with several Transformations in each Package making sure that the data is cleaned and confirmed before being sent to the Staging Area.
• Created complex SSIS packages to pull data from Excel and flat files to SQL server
• Involved in implementing proper package configuration features and finally securing the packages by using password and encryption methods.
• Designed SSIS package templates as base code for package development incorporating package configurations, connection managers and logging in it.
• Testing the Data Completeness by verifying all the data loaded in to the destination with source data.
• Created different kinds of SSRS reports such as Drill Down, Drill Through, cascaded Parameterized, Linked, Ad-Hoc, Sub-Reports for in depth business analysis.
• Deployed reports to the Report server in Native Mode and implemented Report delivery using Subscription in SSRS
• Created views to facilitate easy user interface implementation, and triggers on them to facilitate consistent data entry into the database.
• Formatted and extended SSRS reports by creating parameters within reports, multi value parameters and extended reports with expressions as well as embedding functions with expressions.

Environment: Microsoft SQL Server 2008, SSIS, SSAS, SSRS, SQL Server Management Studio, Excel, Flat Files, Power Pivot, TFS