Sql Server Data

Shah Adnan

Summary:

• Over *+ years of strong experience in the IT industry as a MS SQL Server 2012/ 2008R2/ 2008/

**** *********/** *********.

• Expert knowledge of all phase of Software Development Life Cycle (SDLC) involving

Systems Analysis, Design, Development and Implementation.

• Hands on experience in installing, configuring, managing, upgrading and migrating,

monitoring and troubleshooting SQL Server 2000/2005/2008 R2/2012.

• Skillful in ETL-Extraction Transformation Loading, Data warehousing, Operations Data

Store concepts, data marts and OLAP-Online Analytical Processing technologies.

• Proficient knowledge of Data Warehousing methodologies and concepts, including star

schemas, snowflakes, ETL processes, dimensional modeling and reporting tools.

• Highly proficient in the use of T-SQL for developing complex stored procedures, triggers,

tables, user functions, user profiles, relational database models and data integrity, SQL joins

and query writing.

• Experienced in designing high level ETL architecture, creating, configuring and fine tuning

ETL workflows designed in DTS for overall data transfer from the OLTP to OLAP with the

help of SSIS.

• Expert in developing and modifying multi-dimensional cubes using MS SQL Server

Analysis Services (SSAS).

• Hands on experience in implementing Key Performance Indicator (KPI) Objects and

partitions in SSAS.

• Strong in developing and modifying cubes using SSAS and transform into pivot table.

• Experienced in writing Parameterized Queries for generating Tabular reports, Formatting

report layout, Sub reports using Global Variables, Expressions, Functions, Sorting the data,

Defining Data Source and subtotals for the reports using SSRS 2008/2005.

• Expert in developing Drill through and Drill down reports using Crystal Reports and MS

SQL Server Reporting Services (SSRS)

• Extensively used performance monitor/SQL profiler to solve dead locks/long running

queries.

• Experience in Data Conversion and Data Migration using SSIS and DTS services across

different databases like Oracle, MS access and flat files.

• Expert in using Bulk Copy Program (BCP), Data Transformation Services (DTS) packages

and SSIS (integration services) with MS SQL Server 2008 R2/2008/2005/2000.

• Experience in migration of SSRS reports from SQL 2000 to SQL 2005, SQL 2005 to SQL

2008 and SQL 2008 to SQL 2012.

• Hands on experience in Database Installation, Configuration, Maintenance, Monitoring,

Backup and Disaster Recovery procedure and Replication.

• Quick learner, excellent communication skills and good performer both in team and

independent job environments.

Technical Skill:

MS SQL Server 2012/2008R2/2008/2005/2000/7.0, MS SQL Server Analysis Services (SSAS),

MS SQL Server Integration Service (SSIS), MS SQL Server Reporting Services (SSRS),

Informatica, Business Intelligence Development Studio (BIDS), BCP, SQL Profiler,

Management studio, Microsoft Access 2000, ERWIN 4.0/4.1/4.2/7.2,Microsoft Visio 10.0/11.0,

T-SQL, ASP.NET, C#, VB.Net, HTML, XML, XSL, Oracle 9i/10g, Crystal Reports 8.5/9/10/11,

Visual source safe 2005, MS office 2007/2003,DMEXpress,Windows 2003/2000 Advanced

Server/Professional, Windows NT 4.0 Server/Workstation.

Professional Summary :

1. Cognizant Technology Solutions Apr 2014- Till date

PNC Bank, Pittsburgh, PA

MS SQL Server/ SSRS /SSIS/SSAS Developer

PNC offers a wide range of services for all our customers, from individuals and small

businesses, to corporations and government entities. No matter how simple or complicated your

needs, we're sure to have the products, knowledge and resources necessary for financial success.

PNC provide its services in Retail Banking, Asset Management Group, Corporate &

Institutional Banking and Residential Mortgage Banking. In addition, it provides risk

management, investment system outsourcing, and financial advisory services to a growing

number of institutional investors. This project involved automation of PNC’s Liquidity

Coverage Ratio (LCR) calculation projected to be reported daily.

Responsibilities:

• Created complex Stored Procedures, functions, tables, temp tables, views and using T-

SQL and PL/SQL.

• Created complex SSIS packages to extract data from Oracle and Teradata sources using

Attunity adapters.

• Extensively used variables and expressions to change values at runtime. Used system and

user variables for logging package and task level information and displayed the status in a

Java application.

• Used script task to make decisions and count rows in the source flat files before loading to

SQL Server.

• Thorough understanding of performance tuning, and optimization of SQL queries. Using

parallelism in SSIS and decreasing the number of joins in single queries decreased the time

from 72 hours to 17 minutes.

• Used SQL Server logging, flat file logging and CDC to track inserts, updates and deletes.

• Performed all aspects of database administration, including data modeling, backups

and recovery.

• Extensively created new reports dashboards and modified existing reports using

Tableau.

• Generated Dashboards with Quick filters, Parameters and sets to handle views more

efficiently.

• Published Workbooks by creating user filters so that only appropriate teams can view it.

• Embedded Tableau views in to SharePoint

• Analyzed the source data and handled efficiently by modifying the data types.

• Used excel sheet, flat files, CSV files to generated Tableau ad hoc reports.

• Generated context filters and used performance actions while handling huge volume of data.

• Generated tableau dashboards for sales with forecast and reference lines.

• User Java Script API to customize the views, selects, filters, date and time.

• Generated tableau dashboards with combination charts for clear understanding.

• Excellent report creation skills using Microsoft Reporting Services ( SSRS). Extensively

used SQL Reporting Services and Report Builder Model to generate custom reports:

Designed and created Report templates, bar graphs and pie charts based on the financial

data; Wrote queries to generate reports for analysis of data; Used table control, matrix

control to create cross tab reports; handled sub reports and defined query for generating drill

down reports in SSRS.

• Extensively used T-SQL in creating and altering stored procedures, functions, views,

indexes, user profiles, relational

Database models, data dictionaries, and data integrity.

• Writing reports using Microsoft Reporting Services (SSRS)

• Created jobs, alerts to run SSIS packages periodically. Created the automated processes for

the activities such as database backup and SSIS Packages to run sequentially using SQL

Server Agent job.

• Designed, built and tested SSAS Cubes on SQL Server 2005.

• Created Excel workbooks to enable Cube (SSAS) access for business users.

• Process the cubes (SSAS), partitions as and when required.

• Enhanced performance using optimization techniques: normalization, indexing and

Transaction Isolation levels, monitoring logs to ensure the completeness of batch

processes, scripts and processes.

• Worked with development teams in the design of physical databases and in modification.

• Created packages in SSIS with error handling.

• Developed Query for generating drill down reports in SSRS 2008.Prepared AD HOC

reports through report builders.

• Worked with different methods of logging in SSIS and advanced data mapping.

• Experience in creating complex SSIS packages using proper control flow and data flow

elements.

• Migrated data from Oracle, Teradata, Flat files and Excel in to SQL Server using SSIS

Packages.

• Have deployed packages on different servers by configuring it.

• Installed and configured SQL Server 2008/2005 in Test environment with latest service

packs and migrated few databases.

• Used System tables, System Stored Procedure, DBCC commands for Database

maintenance.

• Implemented computational sensitive queries Stored Procedures.

• Migrated DTS packages from SQL Server 2000 to SQL Server 2005 as SSIS Packages.

• Used SSIS Configurations files, parent package configuration, XML and SQL Server

Configurations for SSIS packages.

• Created SQL Server Profiler traces & used with Database Tuning Advisor (DTA) for

optimum performance of queries.

• Developed DTS packages to extract, transform and load into the Campaign database from

OLTP database using of SQL Server 2005 Integration Services (SSIS)

• Experience writing T-SQL scripts, dynamic SQL, complex stored procedures, functions,

triggers, SQLCMD, and SMO.

• Involved in migration from SQL Server 2000 to SQL Server 2005.

• Implemented Complex business logic with User-Defined-Functions, Index Views and also

created User Defined Data type, Clustered & Non-clustered Indexes.

• Written DDL / DML Triggers as per company policy.

• Actively involved in Normalization (3NF) & De-normalization of database.

• Experience in installation, un-attended deployments, configuration and administration

of Windows NT, 2000 and 2003 servers in very large environments.

Environment: MS SQL Server 2008 R2/2008/2005, SQL Server SSAS/SSRS/SSIS, MDX

Cubes, OLAP, Microsoft Windows 2003 Advanced Server, ASP.NET, C#, ETL, Enterprise

Edition, Microsoft Visual Basic 6.0, SQL OLAP.

2. All State Insurance, Illinois. Jan 2013- Mar 2014

MS SQL Server BI Developer

Allstate Corporation provides insurance, investment management and other financial products

and services to both retail and institutional customers. The services provided include life

insurance, annuities, mutual funds, pension and retirement related investments. The project was

to display the details of the customers’ investment and other financial products online and

generate reports for the customers.

Responsibilities:

• Used breaks, calculations, sorts, filters and sections to enhance data presentation in reports.

• Optimized the performance of queries with modifications in T-SQL queries.

• Created drill down and sub reports from SSAS cubes that can be browsed on web.

• Created SQL Server Reports using SSRS for management. Tasks included defining

requirements, design, develop, test, implement and maintain reports using SSRS Reports.

• Developed several reports with consistent drill-down functionality including Drilldown

reports, Summary reports and Master Detail reports

• Created SSIS packages to migrate data from Oracle 8i, MS SQL 2005 to MS SQL Server

2008.

• Used BCP utilities, Data Transformation Services (DTS), Extract Transform Loading tool

(SSIS) to populate data from various data sources, created packages for different data loading

operations for application.

• Created SSIS package to vendors in which records were loaded daily, also to other data

resources such as Excel, Access, flat file, Oracle and XML in order to create and maintain a

centralized data warehouse.

• Analyzed business requirements and built logical data models that describe all the data and

relationships between the data.

• Worked on Authoring Reports and managing reporting services.

• Created variables and defined formula to capture user responses to a prompt and to display

document information for Web reports

• Designed, Developed and Deployed reports in MS SQL Server environment using SSRS

• Generated Chart-Reports, Drill down reports, Drill through reports and Parameterized reports

using SSRS

• Generated reports using Expressions and Functions using SSRS.

Environment: MS SQL Server 2008, SQL Server 2005, SSRS, SSIS, SSAS, T-SQL, MS-Excel,

XML, Windows 2003/NT/XP.7

3. AT&T, Seattle, WA May 2011- Dec 2012

Role: SQL BI Developer.

AT & T, one of the biggest telecommunication companies in the United States, provides

wireless communication, serving nearly 50 million customers nationwide. The project that I

worked is a National Sale Tracking (NST) system, which uses Microsoft SQL Server 2000. The

project involved maintenance of existing OLTP databases as well as designing SSIS packages

for custom warehousing needs during the second half of 2005.

Responsibilities:

• Involved in reviewing the Investments application and interacting with business users to

understand the user requirements

• Created SSIS packages to extract data from OLTP to OLAP systems and Scheduled

Jobs to call the packages and Stored Procedures.

• Used Joins, correlated and non-correlated sub-queries for complex business queries

involving multiple tables from different databases.

• Implemented Functions, views, triggers and stored procedures and enforced business

rules via checks and constraints.

• Involved in SSAS Cubes design by creating KPI, Actions and Perspective from huge

dataset using SQL Server 2008 Analysis Services (SSAS).

• Generated reports using SSRS, Excel (Pivot) that could be used to send information to

diverse group of users, clients, and managers.

• Tested, Cleaned and Standardized Data meeting the business standards using Fuzzy

/exact lookups using SSIS tasks.

• Implemented Event Handlers and Error Handling in SSIS packages and notified process

results to various user communities.

• Used ETL tool to communicate with the many different relational databases and read

the various file formats used throughout in the organization.

• 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 various reports using Crystal Reports and responsible for

the migration to SSRS.

• Designed, developed, tested, deployed, supported, maintained and documented (KPIs),

Scorecards, Time Aware Dashboards and accompanying Analytical Grids and charts with

URL actions using Performance Point Server 2007 and SQL Server Analytical Services.

• Designed and build the OLAP cubes using Star schema and Snow Flake Schema using

native OLAP Service Manager.

Environment: SQL Server 2008/2005, Oracle, SSIS, SSRS, Data Ware Housing, Visual studio

2008, MS Visio 2007, Enterprise Edition, ASP.NET 3.5, XML, MS Access 2003, Oracle 9i.

4. Direct Energy, Pittsburgh, PA Feb 2010- Apr 2011

MS SQL Server Developer/Report Programmer and Developer

Direct energy is Energy Supply (Power and Gas) Company. Direct Energy migrate Manual

Billing System to Automatic billing system called P2C (Prospect to Cash). Billing information

used for different team (Commission, Sales, Customer relationship, Finance) for reporting

purpose and update legacy system for analysis and Future use.

Responsibilities:

• Defined and Designed data based on business requirement and provided documentation.

• Created SSIS package to load data from XML Files, Ftp server and SQL Server to SQL

Server 2008 by using Lookup, Derived Columns, Condition Split, Ole DB Command, Term

Extraction, Aggregate, Pivot Transformation, Execute SQL Task and Slowly Changing

Dimension.

• Worked with Agile Methodology tracking the product backlog and resolved the defects

assigned in daily scrum.

• Used SSIS to create ETL packages to validate, extract, transform and load data to Data

Warehouse.

• Developed Validation Plans for SSIS packages for checking the functionality of each

component and code review.

• Planned and executed SSIS Package step process to load data, validate data and

recommending task to resolve performance issue with loading data into the Data Warehouse.

• Scheduled jobs for executing the stored SSIS packages which were developed to update the

database on daily basis using SQL Server Agent.

• Involved in creating alerts for successful or unsuccessful completion of scheduled jobs.

• Worked in setup auditing, logging environment to track the status of the events like error

raise, task fail.

• Designed tool to automatically added derived column, parameter and configuration value to

the package.

• Designed, Developed and Deployed reports in MS SQL Server environment using SSRS

2008.

• Generated Sub-Reports, Cross-Tab, Conditional, Drill down reports, Drill through reports

and Parameterized reports using SSRS 2008

• Created reports to retrieve data using function, views, cube and stored procedures using

parameters.

• Created various database objects like stored procedures, functions, tables, views, triggers.

• Configured the report viewing security for various users at various levels using Report

Manager.

• Fine-tuned SQL Queries for maximum efficiency and performance using SQL Profiler and

Database Engine Tuning Advisor.

• Provided documentation for all kinds of SSIS packages.

• Designed and implemented stored procedures and Triggers (DDL & DML) for automating

tasks.

Tools & Technologies:

MS SQL Server 2008, SQL BI Suite (SSMS,SSIS, SSRS), XML, MS Excel, MS Access 2007 &

Windows Server 2008 Access 2003 & Windows Server 2003, SQL Profiler, Data Analyzer,

Erwin r 7.3.,Net 4.0/4.5.,TFS 2005/2008.

5. TSI Logistics, Atlanta, GA Oct 2008- Jan 2010

SQL Server BI Developer

Logistics Supply Chain Suite

TSI is a leading global Freight Bill, Invoice Audit, Payment & Logistics Management Services

Company offering enterprise-wide supply chain solutions. As a global business Partner, N

Vision not only provides a prompt, accurate Freight Payment solution but, industry-leading

information management Services necessary to help clients maximize efficiencies within their

supply chain.

Responsibilities:

● Conducted Data Migration processes in the SQL Server environment using SQL Server

Integration Services 2005.

● Conducted and automated the ETL operations to Extract data from multiple data sources,

transform inconsistent and missing data to consistent and reliable data, and finally load it

into the Multi-dimensional data warehouse.

● Extensively used T-SQL in constructing user functions, views, indexes, user profiles,

relational database models, and data integrity.

● Used different transformations like Conditional Split, Lookup, Merge Join, Aggregate,

Fuzzy Grouping etc. for creating many SSIS packages.

● Created many Variables that were used to pass parameterized values into the package

which makes the package Dynamic.

● Designed many Configuration files that were used to pass parameters from outside the

Business Intelligence Development Studio (BIDS) into the packages using XML files.

● Designed Incremental Load Packages which were used to load the updated and new data

into the Enterprise Data Warehouse without removing the existing data.

● Created Complex SQL Queries using Views, Indexes, Triggers, Roles, Stored procedures

and User Defined Functions Worked with different methods of logging in SSIS.

● Used SSIS and T-SQL stored procedures to transfer data from OLTP databases to staging

area and finally transfer into data marts and performed action in XML.

● Worked on Event Handlers to do Event handling on error at the package and task level on

error, on warning and configured it using send email task and script task.

● Created packages in SSIS with error handling.

● Involved in performance tuning and monitoring of T-SQL blocks.

● Migrated all the packages to Test Environment and finally deployed from Test

Environment.

● Designed reports using SQL Server Reporting Services (SSRS).

● Designed and Developed matrix, tabular and parametric Reports in the SQL Server

Reporting Services 2005

● Designing and implementing a variety of SSRS reports such as Parameterized, Drilldown,

Ad hoc and Sub-reports using Report Designer and Report Builder based on the

requirements.

● Designed SSRS reports with sub reports, dynamic sorting, defining data source and

subtotals for the report and deployed SSRS reports.

● Developed Multi-dimensional Objects (Cubes, Dimensions) using MS Analysis services

(SSAS).

● Developed and modified existing Unix Shell Scripts.

Environment: SQL Server 2008, DTS, VSS, Informatica, VBScript, XHTML, XML, Windows

2003 Server, IIS5.0, 6.0, 7.0.

6. Thomson Financial, Rockville, MD. Mar 2007- Sep 2008

SQL Server/SSIS/SSRS Developer

Thomson Corporation is a leading global provider of integrated information-based

solutions to business and professional customers. The project revolved around the

Fixed Income branch of the financial sector of Thomson, which is an incredible online

trading marketplace for fixed-income securities called Trade Web.

Responsibilities:

• Checking database integrity, Creating new databases, tables, views and Stored

Procedure.

• Creating complex SQL Queries to partition and aggregate the data based on cookie and

session ids of millions of visitors.

• Performed backups using Backup / Recovery, Attach / Detach data tools, Developed

complex DTS packages.

• Involved in migration of SQL 2000 to SQL 2005.

• Dashboards and Scorecards with Key Performance Indicators (KPI) in SQL Server 2005

Analysis Services (SSAS).

• Administered the MS SQL Server by creating user logins with appropriate roles,

dropping and locking the logins, monitoring the user accounts, creation of groups, granting

the privileges to users and groups.

• Developed complex T-SQL code, stored procedures, functions, views and triggers for the

application.

Created ETL SSIS packages both design and code to process data to target databases.

•

Fine-tuned DML, DDL scripts with lock hints for performance boost.

•

• OLAP model based on Dimension and FACTS for efficient loads of data based on Star

Schema structure on levels of reports.

• Constructed Cubes based on data volumes mostly adhering to Star Schema.

• Responsible for testing to optimize the procedures and triggers to be used in production.

• Involved in project planning also schedule for database module with project managers

• Responsible for writing T-SQL code for creating cursor and handling data validations.

Environment: MS SQL Server 2000/2005, SQL Server 2005/2000 Enterprise Edition,

SSIS/SSRS, T-SQL, OLAP, Windows Server 2003, Windows XP.

Contact this candidate