Sql Server Data

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, 2005 and 2000 in Development and Production Environments.

Experienced in all the stages of Software Development Life Cycle and in the migration and up gradation 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.

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.

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

Experience in performing integrity checks. Methods include configuring the database maintenance plan wizard and DBCC utilities.

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, conforming and loading data warehouses/ data marts for reporting purpose.

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

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

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

Adept knowledge in creating calculated members and KPI's using basic MDX expressions.

Created automated backup of OLAP cubes as well as normal backup using SQL server management studio.

Proficient in configuring the Report Server and Report manager to set up roles and security on the reports.

Hands-on experience of setting up schedules for push and pull delivery methods using Standard and Data Driven subscriptions in SSRS.

TECHNICAL SKILLS:

Data Modeling : ER-Win, MS Visio.

Data base : SQL Server 2000/2005/2008, 2008 R2/2012.

BI Tools : BIDS/SSDT, SSIS, SSAS, SSRS, Power Pivot, Power View, Tableau Desktop 6.x/7.x/8.x, Tableau Public, Tableau Reader, Tableau Server, Tableau Online, Micro Strategy Analytics, Micro Strategy Mobile, Micro Strategy Cloud.

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

Web Development : HTML, DHTML, XML, IIS, LINQ, Asp.Net

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

SDLC : Spiral Model, Waterfall, Agile, Scrum.

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

CERTIFICATIONS:

MCSE-DP : Data Platform Solutions Expert

070-464 : Developing Microsoft SQL Server Databases

070-465 : Designing Solutions for SQL Server

MCSM/MCSE-BI : Business Intelligence Solutions Expert

070-466 : Implementing Data Models and Reports with MS SQL Server

070-467 : Designing Business Intelligence Solutions with MS SQL Server

PROFESSIONAL EXPERIENCE:

South Carolina Department of Revenue Columbia, South Carolina Sep 2015 – Present

Sr. 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, flatfiles,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 cleanse 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 various operational Drill-through and Drill-down reports using SSRS

Developed different kind of reports such a Sub Reports, Charts, Matrix reports, Linked reports.

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 Sept 2014 – Aug 2015

Senior SQL Server ETL/SSIS/SSRS Developer

Broad ridge 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 Scottrade’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 Scottrade’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.

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.

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, Micro Strategy Analytics, Micro Strategy Mobile, TFS.

Genworth Financial, Richmond, VA June 2012– Aug 2014

Senior SQL Server ETL/SSIS/SSRS 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:

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 these databases in different server.

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 System stored procedures along with usage of tools like SQL Profiler and Database Tuning Advisor (DTA).

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 SSIS to implement the Slowly Changing Transformation, to maintain Historical Data in Data warehouse.

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

Written Code in C#.Net to make Web Application and connect with Backend (SQL Server).

Implemented Master/Child Packages so only the Master Package has to be scheduled which will in turn fire the other packages.

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 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.

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

Created complex SSAS cubes with multiple fact measure groups and multiple dimension hierarchies based on the OLAP Reporting needs.

Designed and implemented Tabular model using SSDT 2012 and enrich the model by adding relationships, calculated columns, measures, KPIs, and hierarchies.

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.

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

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.

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

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), SharePoint, TFS, Power Pivot, Windows Server, Tableau (Desktop, Server).

Chase Bank, Columbus, Ohio Feb 2010 – May 2012

Sr. ETL SSIS/ Data Warehouse Developer

Chase is the U.S. consumer and commercial banking business of JPMorgan Chase & Co., a leading global financial services firm with assets of $2.4 trillion and operations worldwide.

Responsibilities:

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

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

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

Created Views for each ETL Requirement so that all the data that has already been pulled from data sources are not pulled again into the staging database.

Managed ETL strategy to extract, clean and deliver data to OLAP environment and ensure accuracy, consistency and integrity of the data warehouse.

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.

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

Created SSAS cubes to facilitate the reporting of the business based on historical and current data.

Created various complex cubes with usage based aggregations and set-count aggregations and used appropriate storage modes like MOLAP, HOLAP and ROLAP depending upon how important the data is and how frequently it needs to be fetched from the DW.

Implemented Key Performance Indicator Objects, Actions, Hierarchies and Attribute Relationships for added functionality and better performance of SSAS Warehouse.

Created various cubes based on the reporting and application requirements of the business users using SSAS.

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 management - from customer acquisition to collections management, Mobile strategy, Digital Analytics using Micro Strategy Analytics.

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

GE Health Care, Dallas, Texas Oct 2009 – Jan 2010

ETL/Reports Developer

GE Healthcare provides transformational medical technologies and services to meet the demand for increased access, enhanced quality and more affordable healthcare around the world.

Responsibilities:

Designed logical and physical models for staging database and data warehouse using Erwin database modeling tool.

Created database projects for staging database and data warehouse in visual studio 2008, to store all related objects at one centralized location.

Designed the entire ETL process involving the access, manipulation, analysis, interpretation and presentation of information from both internal and secondary data sources to the business.

Created complex ETL (SSIS) packages to pull data from DB2/Oracle, load monthly data from Excel to SQL server.

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

Implemented error & failure handling in SSIS packages using event handlers, row redirects, check points and used loggings for monitoring the execution.

Optimized packages by avoiding use of non-blocking transformation, executing tasks in parallel and optimizing code at source.

Created alerts and mail confirmation of all packages and cube processes in SQL server Agent to get event messages such as success/failure of package, procedure.

Deployed SSIS packages to the production server using XML and SQL server configurations.

Created different kind of reports like parameterized reports, drill down reports, and sub reports to support business needs and involved in report deployment and report security.

Developed complex reports like Drill capabilities, Complex freehand SQL queries, Functions, Master Detail, and Cross Tab with multiple breaks.

Created Ad-hoc reports using Report Models (.smdl) files and deployed it on Report Manager to create reports on the fly for the people who are less adept with technical skills.

Configured deployment of SQL Server Reporting Services to work with deployment of Microsoft SharePoint Integrated mode.

Optimized the performance of reports by using various techniques like optimizing data-sets, making use of less number of input parameters along with caching and taking snapshot of reports.

Implemented security on reports at Server Level, Report Level and Item Level according to the hierarchy in the organization and business requirement.

Administered interface to organize reports and data sources, schedule report execution, delivery and track report history.

Environment: SQL Server 2008, Transact-SQL, SQL Server Integration Services (SSIS), SSRS (Reporting Services), Erwin, Windows Server.

Client: PERI Software, Chennai, India June 2007- Sep 2009

ETL/ SSIS Developer

PERI is a global business solutions company, which has grown to more than 700 plus employees and delivers high value-cost effective technology based business solutions.

Responsibilities:

Created ER diagrams and performed normalization on them to ensure faster querying.

Wrote complex queries, stored procedures, UDFs in support of the ETL processes, front end applications and reports.

Designed staging database as intermediate stage for population recovery Data Mart to perform various ETL data massaging operations such as data profiling, data cleansing and data verification.

Created ETL process using SSIS for populating data store in SQL Server.

Successfully transferred old data from various sources like flat files, MS Access, and Excel into MS SQL Server 2008R2 using SSIS.

Filtered bad data from legacy system using Derived column, lookup, Fuzzy Lookup, Fuzzy Grouping and Conditional Split.

Performed various transformations like conditional split, sorting and included data about the environment using the audit transformation.

Created Complex SSIS Packages implementing Master-child packages with properties like package configurations, SSIS loggings as well as custom loggings (using Event handlers).

Involved in implementing proper package configuration features and finally securing the packages by using password and encryption methods.

Implemented transactions in SSIS by appropriately setting Transaction Option property to Supported, Not-Supported and required based on the type of strategy being implemented.

Enabled Checkpoints on already implemented transactions to optimize entire ETL strategy so as to start the package execution from latest point of failure.

Created the design documents for the entire project including table structures and design logic of stored procedures, packages and Reports.

Developed data access queries/programs using SQL Query to run in production on a regular basis and assists end users with development of complex Ad Hoc queries.

Involved in designing of shift based reports, chart reports (line and column), parameterized reports for performance analysis.

Set up report delivery schedules and report back up schedules by configuring subscriptions and snap shot respectively.

Environment: SQL Server 2008, T-SQL, BIDS, SSIS, SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), MS Excel, ER-Win, Access, Flat Files, Visual Studio

Contact this candidate