PARAG SAXENA E-mail: ***********@*******.***
MS BI SQL CONSULTANT
Phone: 972-***-****
Professional Summary:
• A skilled software professional with 7+ years of industry experience in design, development and implementation of business requirements with Microsoft SQL Server 2008/2005/2000 database system in client/server environment.
• Domain expertise in Construction, Transportation, Banking, Financial and Communications.
TSQL/Design, Development:
• Expert level skills in Data Mapping, Table Normalization, Optimization and Performance Tuning.
• Expert in writing T-SQL queries with DDL and DML commands, complex stored procedures, indexes, triggers and views.
• Expert in handling Source Control tools like Visual Source Safe (VSS) and Team Foundation Server (TFS).
• Optimized the performance of queries with modifications in T-SQL queries, removed unnecessary columns, and eliminated redundant and inconsistent data.
• Worked extensively on system analysis, design, development, testing and implementation of projects in Software Development Life Cycle (SDLC).
ETL Development (SSIS):
• Experience in developing, deploying and monitoring packages for ETL tasks using SQL Server Integration Services (SSIS), DTS Import/Export Utility, BCP and DTS packages.
• Followed the Microsoft recommended best strategies in designing, developing, and implementation and testing of the ETL using SQL Server Integration Services (SSIS).
• Expert in providing logging, error handling by using event handler and break points for SSIS packages.
• Migrated organization orders, adjustments, objectives and data in various formats like text based files; excel spreadsheets, to SQL Server databases using SSIS to overcome the transformation constraints.
• Experience in handling Performance Tuning of SSIS using Horizontal Partitioning and Vertical Partitioning.
• Experienced in monitoring and scheduling ETL processes using DTS EXEC utilities and batch files.
Analysis (SSAS):
• Migrated and recreated existing dimensions and cubes using star schema on SQL Server 2005 to achieve the efficiency of SQL Server Analysis Server (SSAS).
• Worked extensively on the data warehouse design and analyzed various approaches for maintaining different dimensions and facts in the process of building a data warehousing application.
• Experience on Drill down, Drill through and Parameterized Reports on SSRS.
• Experience in Online Analysis Processing (OLAP), created cubes, dimensions for schemas.
• Developed Scorecards with advance set of dashboards using different Business Intelligence tools such as ProClarity and Performance Point Server.
Administration:
• Excellent hands on experience in scheduling SQL Server Agent Jobs for database Backups, Restoration and Disaster Recovery Procedures and implementing Backup strategies.
• Created and administered all SQL Server databases, objects, logins, users, and permissions in each registered server.
• Played an important role in improving efficiency in databases including re-indexing, updating statistics, recompiling stored procedures and performing other maintenance tasks.
• Highly committed, motivated and a natural team player with strong analytical ability.
Technical Expertise:
Operating Systems Microsoft Windows 7/Vista/XP/2000/95/98/NT , MAC OSX, Linux, Unix
RDBMS MS SQL Server 2008/2005/2000/7.0, MS ACCESS 97/2000,Oracle
Database Tools SQL Server Management Studio, SQL Server Business Intelligence Development Studio, Share Point Server, Visual Studio
ETL Tools Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Import/Export Data, Bulk Copy Program(BCP)
Reporting Tools SQL Server Reporting Services (SSRS), Crystal Reports
Languages PLSQL,TSQL,C,C++, VB Script, MDX
Web Technologies HTML,JavaScript,CSS,PHP5.0
Professional Experience:
IMS Healthcare, Milwaukee, WI May’11-Present
Sr. SQL/BI Developer
IMS Healthcare is a top Healthcare provider nationwide. Our objective was to design and develop Rebate
Ready project which calculates Rebate for all clients. I was actively involved in the design phase of the ETL/SSIS development, develop and load data and post production support. Worked on Drill down, Drill through and Parameterized Reports on SSRS
Responsibilities/Roles:
• Responsible for gathering/refining requirements from the customer for developing reports, developing tech specs, mapping documents and validating data.
• Created SSIS packages for File Transfer from one location to the other using FTP task with Master SSIS
Package to run all other packages
• Involved in Data Migration of Packages from DTS to SSIS by using advanced features in SSIS (Aggregate, Sort).
• Managed index, statistics and optimizing queries by using execution plan for tuning the database.
• Executed batch files using SQL Agent and developed Stored Procedures for schedules, reports and other client interfaces.
• Maintained the physical database by monitoring performance, integrity and optimize SQL queries for maximum efficiency using SQL Profiler.
• Used SQL Server Integration Services (SSIS) to populate data from various data sources using the concept of OLEDB.
• Responsible for Data Migration from one environment to the other using DTS packages.
• Worked DTS Import/Export for transferring data from Database (Oracle and Text format data) to SQL Server.
• Identified the Data Source and defining them to build the Data Source Views.
• Responsible for report generation using SQL Server Reporting Services (SSRS) and Crystal Reports based on business requirements.
• Worked on querying the data and creating-on demand reports using Report Builder in SSRS and send the reports via email.
• Developed query for generating drill down reports using Reporting Services.
• Identified and worked with parameters for parameterized reports in SSRS 2008.
• Responsible for scheduling the subscription reports with the subscription report wizard.
• Inspected the reports and fixed bugs in stored procedures and also tuned Reporting Services to the .NET.
• Worked on Change Management Cases to resolve the production change request issues based on the Specifications provided.
• Used Control Flow Tasks like For Loop Container, For Each Loop Container, Sequential Container, Execute Task, Email Task, and different Data Flow Tasks.
• Worked on Extracting Transforming and Loading data using SSIS Import/Export Wizard.
• Migrated Organization, Orders, Adjustments and Objectives in various formats like text based files, Excel spreadsheets to SQL Server databases using SQL Server Integration Services (SSIS) to overcome the transformation constraints.
Environment: SQL Server 2008 R2 , T-SQL, Windows XP, Enterprise Manager, SQL Server Management Studio 2008 R2, SQL Server Business Intelligence Studio, SQL Server Profiler, IIS, SSRS, SSAS, SSIS, MS Performance Point Server, XML, Visual Studio 2008.
Blue Cross Blue Shield, Jacksonville, FL Aug’10-May’11
Sr. SQL/BI Developer
Blue Cross Blue Shield is a major Health Insurance provider with more than 88 million members and 39 branches nationwide. Our team’s objective was to design and develop database according to company’s requirement. Actively involved in the design phase of the development, conducted detailed Data Modeling for the database to be developed. Developed SSIS packages for Data Migration from Server 2005 to 2008 and created Drill down reports using SSRS.
Responsibilities/Roles:
• Gathered requirements from end user and involved in developing logical model and implementing requirements in SQL Server 2008, data validation, complete technical specs and post production support
• Developed tables, indexes, stored procedures, triggers, views, and adding/changing tables for data load and transformation and data extraction.
• Used SQL Server Profiler for troubleshooting, monitoring and tried to Optimize SQL queries for improved performance and availability.
• SQL Server Integration Services (SSIS) was used to define imports of the data, build scheduling packages, and to define integration flow.
• Created and executed SSIS packages to populate data from the various data sources, and packages for different data loading operations for many applications.
• Expertly handled performance tuning of the SSIS packages by using the concept of Partitioning and Aggregations.
• Used SSIS to create ETL packages (.dtsx files) to validate, extract, transform and load data to data warehouse databases, data mart databases.
• Designed and implemented data marts, facts, dimensions and OLAP cubes using dimensional data modeling standards in SQL Server 2008 that maintained data.
• Experience in building multidimensional cubes using SSAS and create multidimensional expressions (MDX) queries and Data Mining Expression (DMX) queries for Analysis Services & Reporting Services.
• Developed Score Cards and advanced set of Dashboards using Microsoft Performance Point Server 2007 to implement KPIs
• Designed and implemented Parameterized, Cascading parameterized, Drill –through and Tabular reports using SSRS.
• Developed queries for drill down reports to get the dataset required building the report, created data source and data sources views for metadata view.
• Deployed generated reports onto the Report Server to access it through browser, involved in SharePoint integration while deployment of SSRS and ProClarity Reports.
• Maintained data integrity by performing validation checks and also fine-tuned the database objects and server to ensure efficient data retrieval.
• Involved in optimizing code and improving efficiency in databases including re-indexing, updating statistics, recompiling stored procedures and performing other maintenance tasks.
• Provided 24/7 production support for the production database and also to deploy the code into the production environment.
Environment: SQL Server 2008 R2, T-SQL, Windows XP, Enterprise Manager, SQL Server Management Studio, SQL Server Business Intelligence Studio, SQL Server Profiler, IIS, SSRS, SSAS, SSIS, MS Performance Point Server, ProClarity, XML, Visual Studio 2008, ERWIN.
Delta Airlines, Atlanta, GA Mar’08–Aug’10
Sr. SQL/BI Developer
Designed and developed online reservation system at United Airlines. Airline reservation system allows user to search available flights and their timings from the database. It allows full access to customers and new customers to login. This web application will not allow duplicate records instead it will provide secure information on available flights. It was developed using complex SQL queries on SQL server 2008, Analysis/Integration system (SSAS/SSIS) and generated reports using SSRS 2008.
Responsibilities/Roles:
• Responsible to meet the information demands of our business users by delivering timely, accurate, meaningful and standardized data and reporting.
• Created Query Parameters and Report Parameters to control data returned by the query and filter data at report level.
• Optimized the performance of queries with modifications in T-SQL queries, removed unnecessary columns, and eliminated redundant and inconsistent data.
• Created and administered all SQL Server databases, objects, logins, users, and permissions in each registered server.
• Created SSIS Packages to integrate data from heterogeneous file sources like Comma separated files, Text files and Excel files to Ole DB destination
• Created SSIS packages to retrieve Personal Identifiable Information (PII) user data from the Flat Files on monthly and Weekly basis.
• Created SSIS packages for File Transfer from one location to the other using FTP task with Master SSIS
Package to run all other packages
• Involved in Data Migration of Packages from DTS to SSIS by using advanced features in SSIS (Aggregate, Sort).
• Used Control Flow Tasks like For Loop Container, For Each Loop Container, Sequential Container, Execute Task, Email Task, and different Data Flow Tasks.
• Worked on Extracting Transforming and Loading data using SSIS Import/Export Wizard.
• Worked extensively with Key Performance Indicators (KPIs) and published them to SharePoint for further Business Analysis.
• Import Data Sets from various sources like Databases, Oracle, Flat Files, and Excel into SQL Server Data Warehouse.
• Developed Tabular Reports, Sub Reports, Matrix Reports, Drill down Reports and Charts using SQL Server Reporting Services (SSRS).
• Performed Sorting, formatting, and Grouping to custom reports in (SSRS) and also published reports to the Report Server virtual directory.
• Fully utilized the Software Development Life Cycle (SDLC) processes including requirements gathering, design, implementation, quality and deployment.
• Worked on Change Management Cases to resolve the production change request issues based on the Specifications provided.
• Troubleshooting deadlock issues by setting Trace Flags and Analyzing Locks.
• Excellent understanding of multi tiered application environments. SQL Server interaction with client based desktops, application servers, and Microsoft Internet Information Server (IIS).
Environment: SQL Server 2000/2005, Windows 2003/XP, Enterprise Manager, SQL Server Management Studio, SQL Server Business Intelligence Studio, C#, .Net 2.0 Framework , SQL Server Profiler, SSIS, SSAS,SSRS, IIS, T-SQL, PL/SQL
Motorola, Chicago, IL Jul’07–Mar’08
MS SQL/SSIS/AS/RS Developer
Motorola maintains a huge repository of information about its employees. The project was to continuously update and insert employees’ details, using complex SQL Queries, in the Data warehouse. Any changes in the employee database were monitored and migrated from OLTP to OLAP.
Responsibilities/Roles:
• Responsible for gathering/refining requirements from the customer for developing reports.
• Managed index, statistics and optimizing queries by using execution plan for tuning the database.
• Executed batch files using SQL Agent and developed Stored Procedures for schedules, reports and other client interfaces.
• Maintained the physical database by monitoring performance, integrity and optimize SQL queries for maximum efficiency using SQL Profiler.
• Used Data Transformation Services (DTS) to populate data from various data sources using the concept of OLEDB.
• Responsible for Data Migration from one environment to the other using DTS packages.
• Worked DTS Import/Export for transferring data from Database (Oracle and Text format data) to SQL Server.
• Identified the Data Source and defining them to build the Data Source Views.
• Responsible for report generation using SQL Server Reporting Services (SSRS) and Crystal Reports based on business requirements.
• Worked on querying the data and creating-on demand reports using Report Builder in SSRS and send the reports via email.
• Developed query for generating drill down reports using Reporting Services.
• Identified and worked with parameters for parameterized reports in SSRS 2005.
• Responsible for scheduling the subscription reports with the subscription report wizard.
• Inspected the reports and fixed bugs in stored procedures and also tuned Reporting Services to the .NET Applications.
Environment: SQL Server 2000/2005, T-SQL, Windows XP, Enterprise Manager, SQL Server Management Studio, SQL Server Business Intelligence Studio, SQL Server Profiler, SSRS, DTS, Crystal Reports, XML.
Home Depot, Atlanta, GA Jan’06 – Jun’07
DBA/Database Developer
Home Depot is one of the largest retailers in United States which provides home improvement and also construction products. The project was to perform Extraction, Transformation and Load the online customer information from OLTP to Data warehouse. Complex T-SQL queries were written to retrieve meaningful data from Data warehouse and reported by drill-through and parameterized reports using SSRS application.
Responsibilities/Roles:
• Involved in installing and maintaining MS SQL Server 2000 and Clients.
• Design and create SQL Database, tables, indexes, and views based on user requirements.
• Worked as a developer in creating complex Stored Procedures, Triggers, Functions, Indexes, Tables, Views and other T-SQL code and SQL joins for applications.
• Responsible for Physical and Logical design of database.
• Create database maintenance plans for the performance of SQL Server including database integrity checks, update database statistics, re-indexing and data backups.
• Monitored and modified Performance using execution plans and Index tuning.
• Employed various constraints such as Primary Key constraint and Foreign Key constraint on tables to maintain Data Integrity and also created user defined Functions, Stored Procedures, and Triggers.
• Schedule jobs using MS SQL server Agent for Backups and developed high availability solutions through Replication and Database Mirroring.
• Create and managed users, login IDs, security, and add groups to Windows and SQL Server.
• Performance tuning of existing database objects including stored procedures.
• Prepare documentation related to database design, objects security and rules.
• Created DTS Packages for migration of data between MS SQL Server database and other databases like Access 2003, Office Excel 2003 and Flat Files.
• Perform daily database backup & restoration and monitor the performance of Database Server using SQL Server Profiler.
Environment: SQL Server 2000 Enterprise Edition, SQL Server 7.0, Windows 2000, Enterprise Manager, SQL Profiler, SQL Query Analyzer, DTS, T-SQL, Access 2003, Excel 2003
State Bank of India, India March’04–Dec’05
Jr. Database Developer
State Bank of India (SBI) is largest bank in India with more than 10,000 branches nationwide. It maintains a huge customer and employee database in from of various heterogeneous sources. The project was to re-organize the customer database with the latest information on loans, payments, fixed-deposits and huge transactions. As a Junior Database Developer, I was involved in altering the physical design, developing stored procedures and functions, developing DML scripts, fine tuning complex queries and also maintaining backups.
Responsibilities/Roles:
• Created new database objects like Procedures, Functions, Packages, Triggers, Indexes and Views using T-SQL in Development and Production environment for SQL Server 2000.
• Developed Database Triggers to enforce Data integrity and Referential Integrity.
• Developed SQL Queries to fetch complex data from different tables in remote databases using joins, database links and formatted the results into reports and kept logs.
• Defined relationship between tables and enforcing entity referential integrity (Parent-Child relationship) using
Primary Key constraint
• Involved in Implementation of database design and administration of SQL based database.
• Established connectivity to database and monitored systems performances.
• Involved in performance tuning and monitoring of both T-SQL and PL/SQL blocks.
• Used SQL Profiler and Query Analyzer to optimize DTS package queries and stored procedures.
• Wrote T-SQL procedures to generate DML scripts that modified database objects dynamically based on user inputs
• Created Stored Procedures to transform the Data and worked extensively in T-SQL for various needs of the transformations while loading the data
• Involved in performance tuning using indexing (Cluster Index, Non Cluster index) tables and eliminating table
scans and implementing index seeks
Environment: SQL Server 2000, Windows 2000, Enterprise Manager, SQL Profiler, SQL Query Analyzer, DTS, T-SQL.
Educational Qualification:
Bachelor of Technology, Shri Govind Ram Seksariya Institute of Technology, India