Sql Server Data

Senthil Palaniswami

*******.***********@*****.*** / 704-***-****

Professional Summary:

10+ Years of Business Intelligence experience in Implementing End-to-End BI solutions using Microsoft Business Intelligence Stack (SSIS, SSRS, and SSAS).

Technical Expertise on Microsoft Business Intelligence (MSBI) Stack SSIS, SSRS and SSAS.

In depth knowledge of various data modeling techniques: Start Schema, Snowflake Schema.

Experience in Architecting & Building Enterprise Data Warehouse (EDW) / DataMarts for large datasets.

Experience in Data Modeling (Conceptual, Logical and Physical Design of Databases), Normalization and building Referential Integrity Constraints.

Experience in Analyzing, Designing and Developing Business Intelligence (BI) database applications and various segments of Software Development Life Cycle (SDLC), using MS SQL Server 2012/ 2008/2005/2000, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS) and SQL Server Analysis Services ( SSAS)

Excellent in ETL Process. Worked extensively on data extraction, cleansing/ data integration, and loading from different data sources with large millions of data using SSIS 2012, 2008, 2005.

Experience in developing Drill-through, Drill-down, Linked Reports, and Chart Report, Sub-Report using SQL Server Reporting Services (SSRS).

Experience in configuration of report server and report manager scheduling, give permissions to different level of users in SQL Server Reporting Services (SSRS).

Experience in generating on-demand and scheduled reports for business analysis or management decision using SQL Server Reporting Services.

Good understanding of Microsoft Reporting Service (SSRS) with Report authoring, Report management, Report formatting, Report distribution, Report delivery and Report security.

Experience in building Multi-dimensional Data Cubes using SSAS. Experience in querying SSAS Cube data in MDX Queries. Experience in building dashboards with SSAS Cubes in Excel.

Expertise in Microsoft SQL Server Programming including Database Table Design, views creation, stored procedures, functions, joins, index, triggers and job creation. Experience in creating Jobs, SQL Mail, Alerts and Scheduling Packages using SQL Server Agent.

Excellent in query optimization & performance tuning using SQL Server Profiler, Execution Plan, Performance Monitor.

Exposure in Microstrategy Architecting Objects & Report creations.

Exposure to Apache Hadoop Technologies.

Excellent team player with problem-solving and trouble-shooting capabilities. Expertise in working with highly changing requirements, process driven environment, in project team also individual.

Technical Skills:

Databases

MS SQL Server 2012, 2008, 2005, 2000, 7.0, Teradata, Oracle

ETL, Reporting & Analysis Tools

MS SQL Server Integration Services ( SSIS), MS SQL Server Reporting Services ( SSRS), MS SQL Server Analysis Services ( SSAS)

Crystal Reports, Business Objects XI, Microstrategy, Talend

Programming Languages

C, C++,VB/VBA, .Net ( C#,VB.Net, ASP.Net)

Data Modeling Tools

ERWin tool, Microsoft Visio 2000/2003

BigData Technologies

Apache Hadoop, Hive, Pig, HBase, Sqoop

Educational Qualification:

4 Years Bachelor of Engineering (B.E) degree with specialization in Computer Science (CS) from Madras University, India.

Professional Experiences:

Accolade LLC -- MSBI Consultant June 2014 to Present

Deerwalk Project Implementation

Deerwalk project is to implement the entire different vendors like Comcast, Time Warner Cable (TWC), Independence Blue Cross (IBC) Alabama, HP, Lowes, Medtronic, Truven, Temple University Health System and Greatland’s employees & dependents eligibility, claims ( Medical, Pharmacy) Analytical data into DataMart. This DataMart is used in a Qlikview application to represent the data to the users.

ETL system is built to load the data from multiple data source systems; the same data is processed by SSIS Packages to load into DataMart. Microsoft SQL Server 2012 & SSIS 2012 is used on this project.

Designed the ETL framework to load the data, log the progress, log the Audits for every data feed load.

Designed all the Analytical tables and views for the data.

Developed Indexing Strategy for all the 300+ tables in the DataMart.

New packages were developed to integrate into the warehouse. Coordinated all the packages to deploy in “Integration Services Catalog”.

Designed SSIS Package Execution Plan on Tidal Job Scheduler.

Involved in the Performance tuning on the large FACT data pull.

Datastrong LLC -- MSBI Consultant (SSIS, SSRS, SSAS Developer) Nov 2012 to Present

American Public University System (APUS) - Project STAR Phase II

Project STAR system is built to generate daily, weekly and Adhoc reports on Student, Course, Degree progress and scores. ODS (Operational Data Store) system is built to gather the data from multiple data source systems, the same data is processed by SSIS Packages to load in Lookup/Dimension/ FACT level data into Data warehouse(DW). Materialized Views are created on top of DW tables to use in Microstrategy environment.

Redesigned the ETL run framework logging, including the additional Audit logs for yearly auditing.

Integrating Google Analytics user data into the data warehouse for reporting.

Developed Indexing Strategy for all the 300+ tables in the data warehouse.

New SCD (Slowly Changing Dimension) developed are handled in the SSIS packages.

New packages were developed to integrate into the warehouse. Coordinated all the packages to deploy in “Integration Services Catalog”. Designed SSIS Package Execution Plan on Tidal Job Scheduler.

Involved in the Performance tuning on the large FACT & Dimension tables data pull.

BCBS Arizona - Claims Data Warehouse Performance Improvement Recommendations

Blue Cross Blue Shield Arizona process & maintains the claims data in SHIFT_CDW. Enterprise data warehouse is built on SQL Server 2005 with 1.7 TB in size. SHIFT_CDW Process runs 1st of every month for the previous month claims for almost 5 days. Due to long running of the data processing, users are awaiting to process the last month claims data for the first 5 days of every month. To fix this long running ETL process, we have analyzed the complete system and recommended efficient methods.

Detailed System Analysis performed on Environment, Data Warehouse design & ETL Processing.

Analyzed Bottleneck queries, Index requirements, long running queries and more I/O used queries on the tables by using DMV Queries.

Analyzed the complete ETL Processing and recommended the Parallel Processing & Batch Processing methods to utilize the high powered server to minimize the processing time.

Provided best Database Partition methods, in this case we recommended to use Horizontal partition for staging tables for faster access.

Provided Standardized approach to migrate the DTS packages to SSIS packages.

Setup the best practices for “SSIS Code Migration from one environment to another environments, Versioning control, Configuration, Error Handling, Audit & Logging methods”.

Tuned and changed some of the long running queries to execute faster. Materialized some of the views for faster data retrieval.

RetailData Data Integration

RetailData provides market intelligence services to over 150 clients including the leaders in the retail Food and Drug channel, Grocery Wholesalers, as well as Specialty Retailers. New Data warehouse is built to accommodate different data sources with the Snowflake model contains Dimension and FACT tables. SSIS ETL Process is designed to Extract – Transform – Load the data into the Data warehouse.

SSRS is used for reporting & SSAS Cube is used for Multi-Dimensional data view in Power Pivot.

Architected Snowflake Schema Enterprise Data Warehouse (EDW) system. Used Data Modeling ERWin tool to design EDW & Data Marts.

Architected & developed the complete SSIS ETL data flow to Extract-Transform- Load data from multiple data mart sources into Data Warehouse (EDW). HashID Concept is used to lookup the existing data to compare.

SQL Server 2012 SSIS Project Deployment is used to deploy the SSIS Packages. Created SQL Server Job to execute the SSIS Packages &Job Schedule for the SSIS Job to run schedule basics.

Data Processing Improvements on Query Optimization. Worked on SQL Query Execution Monitoring & Performance tuning for Large Transaction data.

Developed Drill-through, Drill-down, Linked Reports, Chart Report and Sub-Report using RDL.

Generated periodic reports based on the statistical analysis of the data using SQL Server Reporting Services (SSRS).

Built effective queries for high-performance reporting and rendered them to HTML, XML, PDF and Excel formats using Reporting Services (SSRS).

Designed and developed SSAS Cubes geared towards to create self-service report by executives using Excel as a front end tool to access the cubes.

Created Analysis Services Cube with more than 20 dimensions, Developed calculated members for rates and percent calculation, Developed named set to make the cube more user friendly.

Provided trainings and live demonstrations to end users on how to make analysis using excel pivot table and OLAP cubes.

Extracted valuable information from cubes of Retail database using Multidimensional Expression (MDX) Language.

MedStar Health PatientSurvey Data Integration & ETL Automation

MedStar Health system gets the patient survey details from NRC (National Research Corporation) Picker for every hospital and health care center in Washington DC, Maryland and Virginia. Data are received as raw format in a tab delimited files. New data model is designed to accommodate the data into Lookup, Dimension & FACT tables by SQL Server 2005 SSIS ETL Process. These materialized data are then used in Microstrategy to generate Static & Adhoc reporting for clients.

Architected Enterprise Data Warehouse (EDW) & Data Marts systems. Used Data Modeling ERWin tool to design EDW & Data Marts. Created Start Schema Dimensional Model on the EDW.

Designed and Developed the SSIS ETL data flow to Extract-Transform- Load data into data warehouse from multiple sources of data files.

Architected Microstrategy Objects and Developed reports for the end-users.

Created a XML Configuration file for the SSIS Components and Variables. Deployed the SSIS Package & Job in the SQL Server Environment. Created SQL Job Schedule for the SSIS Job to run schedule basics on SQL Server Agent.

Data Processing Improvements on Query Optimization. Worked on SQL Query Execution Monitoring & Performance tuning for Large Transaction data pull on the reports.

End to End documentation for gathering Business requirement, HLD, LLD & Design docs.

American Public University System (APUS) - Project STAR

Project STAR system is built to generate daily, weekly and Adhoc reports on Student, Course, Degree progress and scores. ODS (Operational Data Store) system is built to gather the data from multiple data source systems, the same data is processed by SSIS Packages to load in Lookup/Dimension/ FACT level data into Data warehouse(DW). Materialized Views are created on top of DW tables to use in Microstrategy environment.

Architected Enterprise Data Warehouse (EDW) & Data Marts systems. Used Data Modeling ERWin tool to design EDW & Data Marts.

Architected the complete SSIS ETL data flow to Extract-Transform- Load data from multiple data mart sources into Data Warehouse (EDW).

Implemented the Custom and SSIS Catalog Level of Loggings on the ETL Process.

SSIS Packages configurations are stored on the physical table to minimize the code change in future, also Adhoc parameterized package execution.

SCD (Slowly Changing Dimension) are handled in the SSIS packages.

Coordinated all the packages to deploy in “Integration Services Catalog”. Designed SSIS Package Execution Plan on Tidal Job Scheduler.

Involved in the Performance tuning on the large FACT & Dimension tables data pull.

Tata Consultancy Services, USA Jan 2009 to Nov 2012

IT Analyst - MSBI Developer (SSIS, SSRS, SSAS)

Bank of America: Anti-Money Laundering (AML) Application

CheckWatch: CheckWatch is a tool used in the daily surveillance of Cash Letter items from offshore and domestic correspondent banks. Application monitors Cashier Checks, Regular Checks, Postal Money Orders, Negotiable Instruments and Travelers Checks for unusual trends or suspicious activity, with special focus on high-risk areas.

Negotiable Instrument Data Integration:

Creation of SSIS packages for loading the Negotiable Instrument data from Teradata Source to SQL Server.

Created SQL Server Job to execute the SSIS Packages with the XML Configuration File.

Data Processing Improvements on Query Optimization.

Created SQL Objects like table, Dynamic Stored Procedure, Stored Procedure, Views, Indexes, Triggers, Rules, Defaults, User Defined Data type and functions.

Worked on SQL Query Execution Monitoring & Performance tuning for Large Transaction data pull on the UI.

Developed stored procedures using T-SQL to generate Ad hoc reports within SQL Server Reporting Service (SSRS)

Party ID Data Addition for Negotiable Instrument Transitions:

This project is to add the Party Details for all the Negotiable Instrument Transaction data into the system. Party Details are more details about the customer of the transaction. Party details contain the Customer ID/ SSN/ Data of the Account open/close, Active Balance and other details. This Batch process is created due to large volume of records in the party details. Estimated 60+ Million Party’s in the Bank.

Creation of SSIS packages to pull the particular day Negotiable Instrument Account and ID details from Teradata Source to SQL Server.

Created a Stored Procedure to construct the Teradata SQL Query to pull the Party details for the Account & ID Numbers.

Created a Stored Procedure to Merge Party and Negotiable Instrument data based on the Role to integrate the Party Details for the transactions.

Created Job Schedule for the SSIS Job to run on Wednesday to Sunday basics.

OLM Data Integration for TrX Data Filtration:

This project is to get the OLM (Online List Management System) system’s feed into SQL Server. This Feed Contains the Account Details of the customer who customer is more interested in it. This is a XML Data Feed to SQL Server on Monthly Basics. These data are used on daily when the Transaction data are loaded the system.

Created a Secured FTP Location to get the XML Source Data File in SQL Server.

Created a Stored Procedure to process the XML File into Tables.

Used Open Row Set Function to get the data from XML by node wise to load in SQL Table. Created SQL Server Job to execute the Stored Procedure to load the XML data.

XML Data File Distribution:

This project is to generate the XML file from the OLM (Online List Management System) data in the SQL Server table. This Process is developed to send the Account Details to different downstream systems. Data Contains the Account Details of the customer depend on the source system. This is a XML Data Feed send to downstream system on Monthly or On-Request Basics.

Created a Stored Procedure to generate the XML File from the Table records based on the downstream data conditions.

Used XML PathFunction to generate the data in XML file by node wise.

Created SQL Server Job to execute the Stored Procedure to generate the XML data.

Created a Table to keep all the Configuration details including File Location, File Name, and Table Name and used them on the Stored Procedure.

WireWatch: WireWatch is a tool used in the daily surveillance of Wire Transfers of Domestic & International Transactions. Application monitors Domestic (WTX) Wires & International Wire Transactions (GBS) for unusual trends or suspicious activity, with special focus on high-risk counties, persons, organizations.

Creation of SSIS packages to get the Domestic Wire Transactions from Teradata Source to SQL Server 2005.

As the data load is huge on daily basis around 250,000 transactions, used “Access Mode of Open Row Set” to get the data in batch to SQL Server Table.

Created SQL Server Job to execute the SSIS Packages with the XML Configuration File.

Created Job Schedule for the SSIS Job to run on weekday (Tuesday to Saturday) basics.

Worked on SQL Query Execution Monitoring & Performance tuning for Large Transaction data pull stored procedure for the User Interface Display.

Developed Drill-through, Drill-down, Linked Reports, Chart Report and Sub-Report using RDL.

Generated periodic reports based on the statistical analysis of the data using SQL Server Reporting Services (SSRS).

Built effective queries for high-performance reporting and rendered them to HTML, XML, PDF and Excel formats using Reporting Services (SSRS).

ESM (Enhanced SAS Monitoring): Enhanced SAS Monitoring system enables the identification of relevant unusual activity by applying parameter driven scenarios against primarily bank deposit products – cash, ATM and wire activity. Due to the increasingly sophisticated attempts of money launderers to hide their activities, as well as growing regulatory demands for automated AML monitoring, it has been determined that Bank of America’s current AML monitoring tools must be augmented to additionally enhance the ability to support high to medium risk products throughout the company’s footprint. The implementation of these enhancements is necessary in order to provide vital asset protection and assure regulatory compliance in a potentially high-risk environment.

Created SSIS package to get the GBS (Global Banking System) Transactions from Teradata Source to Teradata Tables. It is a Data Collection SSIS to use in SQL Server.

As SQL Server 2005 doesn’t support data load into Teradata, I have used third party components to load the record. Attunity (http://www.attunity.com/products/attunity-connect/data-connectivity) connection manager to manage the connections between the source and destinations.

Created another SSIS to get the Collected Data from Teradata to SQL Server.

Event Manger: Event Management is pre-processing application designed to consolidate and provide relevant data necessary to begin the case management process. This system collects all the TRM/STM data from the detection channel application with transaction, account, customer, Risk Attribute & suspicious activity to generate a detail Case for Case Management System.

Created SSIS Package to get the data from Teradata, Comma Separated Files and SQL Server to SQL Server Destination.

Created a SQL Server data processing to process all the records based on the region, country, risk of the customer, and Attributes by Stored Procedures & Functions.

Created SQL Server Job to execute the SSIS Packages with the XML Configuration File and Stored Procedures. Created Job Schedule for the SSIS Job to run on weekday basics.

Tata Consultancy Services, Chennai Dec 2006 to Dec 2008

IT Analyst - MSBI Developer (SSIS, SSRS, SSAS)

CheckWatch & WireWatch - Bank of America, USA

WireWatch & CheckWatch Applications are part of the tools by the Anti-Money Laundering Surveillance

Group in the daily surveillance of wire and check transfer activity from offshore and domestic

correspondent banks. WireWatch and CheckWatch are client-server applications with Visual Basic as

user interface using Microsoft SQL Server as database. WireWatch and CheckWatch are used to monitor

daily wire transfer and check activity from correspondent Banks for unusual trends or suspicious activity,

with special focus on high-risk areas.

Migrated all the SQL Server 2000 DTS Packages to SQL Server 2005 SSIS Packages.

Created a XML Configuration file for the SSIS Components and Variables, which contains the SQL Data Pull query from Source System, SQL Connection properties.

Created Job Schedule for the SSIS Job to run on weekday basics.

Deployed the SSIS Package, Stored Procedures, and Functions & Job in the SQL Server.

Data Processing Improvements on SQL Server ETL & Query Optimization

Developed Drill-through, Drill-down, Linked Reports, Chart Report and Sub-Report using RDL.

Generated periodic reports based on the statistical analysis of the data using SQL Server Reporting Services (SSRS).

Built effective queries for high-performance reporting and rendered them to HTML, XML, PDF and Excel formats using Reporting Services (SSRS).

Pinnacle Netvision Pvt Ltd, Chennai Feb 2005 to Sep 2006

.Net & MS SQL Server Developer (C#, ASP.net, VB.Net, SQL Server 2000)

RxPertise - Managed Healthcare Associates Inc. USA

RxPertise is a total solution provider for the Care Center Management. This Package includes Care Center, Resident, Physician, Medication, Lab and Reminder Modules. This system can also generate Adhoc reports for Monthly/Daily reports according to the needs of Care Center Hospital Management Systems. The system is developed in C# Windows Application with MS-Access as a database.

Developed User Interface in C# Windows Application

Involved in creation of Tables, Views, Indexes and Stored procedure in MS SQL Server

Involved in MSI Package Creation to push the package to multiple users.

Comprehensive Asset Management Systems (CAMAS) - CompuTech Inc., USA

This System provides a computerized system for streamlining the earnings of the clients in the proper channel. This project includes Client Information, Asset Managers, Representatives, Accounts, Transactions and Reporting Modules .The main function of the application is to keep track of the fixed asset. The acquisition of fixed asset and commission can be monitored through this project. This system can also generate Adhoc reports for Monthly/Daily reports according to the needs.

Involved in designing of Web User Interface

Constructing Web templates and web services for repeated task.

Involved in creation of Tables, Views, Indexes and Stored procedure in MS SQL Server

Ardor Technology Solutions, Chennai Sep 2004 to Feb 2005

.Net & MS SQL Server Developer (C#, ASP.net, VB.Net, SQL Server 2000)

ClaimDx Medical System - ClaimDx Medical Systems, USA

This system is a fully automated report generation for a Care Center. It generates month summary

Report of Care Center details, Patient details, Doctor Details, Doctor Visits, medication utilization by

Patients/Care Center and also by stations, and lab details of patients. The system generates MSSQL

Tables per months and creates statistics data and generates reports depends on it.

Review the Architecture Specifications and Understand the Client Change Requests

Involved in creation of Stored procedures in SQL, Created Reports using Crystal Reports

Creation of MSI setup for Deployment .Scheduling and sequencing the jobs according to the requirement. Analyzing the existing system and Documentation

Contact this candidate