- 8+ years of total IT experience in the business requirements analysis, application design, dimensional data modeling, data modeling, designing, coding, development, testing and implementation of business applications with RDBMS, Data Warehouse/Data marts, ETL, and business intelligence of Microsoft SQL Server 2012/2008R2 /2005with various business domains like Healthcare, Insurance, Finance and etc.
- Experience in Creating Tables, Joins, Views, Indexes, Triggers, Stored Procedures, User Defined Functions, synonyms, collections, Exception handling, Query Tuning using query optimizer.
- Experienced in strategically implementing the indexes such as Clustered index, Non - clustered index, covering index appropriately on data structure to achieve faster data retrieval.
- Good knowledge in Normalizing and De-normalizing the tables and maintaining Referential Integrity by using Triggers and Primary and Foreign Keys.
- Experience in Database Development, modeling, Data Warehousing, Design and Technical Management.
- Expert Knowledge of Integration Services (SSIS) and Reporting Services (SSRS). Worked Extensively on ETL process using SSIS packages.
- Strong Knowledge on concepts of Data Modeling Star Schema/Snowflake modeling, FACT & Dimensions tables and Logical & Physical data modeling.
- Excellent in creating SSIS Packages for integrating data using OLE DB connection from heterogeneous sources (Excel, CSV, Oracle, flat file, Text Format Data) by using multiple transformations provided by SSIS such as Data Conversion, Conditional Split, Bulk Insert, merge and union all.
- Experience in Developing, Deploying and Monitoring Packages for ETL Tasks using SSIS, DTS Import/Export Utility, BCP (Bulk Copy Program) and DTS Packages.
- Migration of SSRS reports from SQL 2008 to SQL 2012. Extensive experience with data modeling techniques, logical and physical database design.
- Strong experience in Data Warehousing and ETL using InformaticaPowerCenter 9.6.1/9.1/8.6.2 .
- Developed mappings that perform Extraction, Transformation and load of source data into Derived Masters schema using various power center transformations like Source Qualifier, Aggregator, Filter, Router, Sequence Generator, look up, Rank, Joiner, Expression, Stored Procedure, SQL, Normalizer and update strategy to meet business logic in the mappings.
- Performance tuning is performed at the Mapping level as well as the Database level to increase the data throughput.
- Experience in writing Test cases and SQL queries to validate the data that has been loaded into QA direct, test execution and defect reporting using HP-QCtest management tool.
- Expertise in ETL/DWH testing concepts, InformaticaPowerCenter 9.6.1/9.1/8.6.2 , TOAD, Base SAS concepts, basic understanding of Perl.
- Expertise on various manual testing types like System testing, Regression testing, Functional testing, UAT testing etc.
- Experience in using Data Modeling Tools like Erwin, MS access and Microsoft Visio.
- Worked in OLTP & OLAP environments that include QA, Production and development, implement maintain database schema and Entity relationship diagrams from business requirements.
- Involved on administration tasks such as disaster recovery with Backup and Restore Strategies, Data Loading, Batch Jobs, Data Unloading, Upgrades, Creating Databases, Files, File groups, Transaction logs, Clustering, Replication, Data Mirroring, Linked Servers, SQL Profiler, Log Shipping and Re-Indexing.
Databases: MS SQL Server 2012, 2008 R2, 2008/2005, Oracle 8i/9i/10g, MS-Access
ETL Tools: SQL Server Integration Services (2005/2008), Data Stage, Informatica 9.6.1
Operating System: Windows 2012/2008/2005 Advanced Server, Windows (95/98/00/XP, vista), WinNT, UNIX.
Data Modeling Tools: ERWIN 4.5/4.0/3.5/3.2 , ER Studio
Languages: C, C++, Visual Basic, SQL, UNIX Shell (Ksh/Csh), PL/SQL, SAS 9.2
Reporting Tools: SQL Server Reporting Services (SSRS), Tableau
Test Management Tools: Quality Center (HP-QC).
Scheduling Tools: Control M
Confidential, Phoenix, AZ
ETL Developer/SQL BI Developer
- Experience in Agile methodology.
- Created database objects like tables, complexStored Procedures, Functions, Views, Triggers,Indexes and T-SQL queries (DDL and DML) to meet the business requirements using T-SQL.
- Created and Modified T-SQL stored procedures for validating the integrity of the data.
- Deployed the created SSIS packages in Development and Testing environments.
- Used SSIS to create ETL Packages to validate, extract, transform and load data to data warehouse databases and data mart databases.
- Generated packages using different Transformations like Lookups, Derived Column, Merge Join, Fuzzy Lookup, For Loop, For Each Loop, Conditional Split, Union all, Script component.
- Experience in providing Logging andError handling by using Event Handler and Custom Logging for SSIS Packages.
- Extensively used Informatica Power Center for loading the data from various sources involving flat file and Oracle tables.
- Data Extraction, Transformation and Loading using Informatica for fact and dimension tables.
- Developed mappings, sessions and workflows as per new requirements.
- Most of the transformations were used like Source qualifier, Aggregators, expression, lookups, Router, Sequence Generator and Update strategy.
- Worked on Informatica client tools - Data warehousing designer, Workflow manager, and Workflow Monitor and Repository manager.
- Develop/Execute ETL Test cases to validate business requirements.
- Create stored procedures for Incremental extract based on time stamp, version date and record flag.
- Implemented SCD I, II to the extracts based on the business requirement and client expectations.
- Create test plan, test cases and do the Unit testing, SIT and UAT and send them to projects lead for approval.
- Responsible for Full Report cycle including Authoring, Managing, Security and Generation ofreports.
- Worked on database security, Replication and Log shipping activities.
- Worked on SQL Server Reporting Services (SSRS) and Tableau to generate custom reports.
- Created Custom Reports and Ad-hoc Reports using SQL Server Reporting Services.
Environment: SQL Server 2012, T-SQL, MS SQL Server Integration Services (SSIS), Informatica, Excel, MS Access, Microsoft BI, Windows Server 2008, Visual Studio, SQL Server Data Tools, Query Analyzer, SQL Server Profiler, SVN version controlling.
Confidential, Austin, TX
Sr. SQL Server SSIS/SSRS Developer
- Filtered and cleansed bad data from legacy system using complex Transact- SQL statements in staging area, and implemented various constraints and triggers for data consistency.
- Performed Data Modeling in the initial stages of the project.
- Optimized the performance of queries with modification in TSQL queries, removed unnecessary columns, eliminated redundant and inconsistent data, normalized tables, established joins and created clustered/non-clustered indexes wherever necessary.
- Developed complex Stored Procedures to process prospective customer information and balanced the processing load between front end and back end.
- Extensively used joins and sub queries to simplify complex queries involving multiple tables. Developed views using complex split week logics.
- Created SSIS Packages using Pivot Transformation, Fuzzy Lookup, Derived Columns, Condition Split, Term extraction, Aggregate, Execute SQL Task, Data Flow Task, and Execute Package Task etc to generate underlying data for the reports and to export cleaned data from Excel Spreadsheets, Text file, MS Access and CSV files to data warehouse.
- Built efficient SSIS packages for processing Fact and Dimensiontables with complex transforms and type 1 & type 2 changes.
- Extensively used SQL queries for data validation and backend testing.
- Validating the data files from source to make sure correct data has been captured to be loaded to target tables.
- Preparation of Test Cases based on ETL Specification Document, Use Cases, Low Level Design document.
- Played role in creation and testing of designed scalable, reusable, and low maintenance ETL templates.
- Design, deploy, and maintain various SSRS Reports in SQL Server 2008 R2.
- Generated Daily, Weekly, and Monthly Reports as per the customer requirements using subscriptions and Created Tabular Reports, Cross Tab Reports, Sub Reports.
- Built effective queries for high-performance reporting and rendered them to HTML, XML, PDF and Excel format using SSRS 2008.
- Designed different kinds of reports like Linked, cascaded and parameterized reports and Worked on Drill Down and Drill-through Reports, Tabular, Matrix etc.
- Experienced in developing stored procedure driven reports, building custom application on reports, formula writing/editing in reports, Report Administration and performance tuning of reports.
- Involved in query optimization and performance tuning.
- Used Performance tools like SQL Profiler, Index Tuning Wizard and Database Engine Tuning Advisor
- Implemented error handling in several existing Store Procedures using Try Catch blocks.
Environment: SQL Server 2008 R2/2008, T-SQL, Excel, MS Access, Microsoft BI, Windows Server 2008, Visual Studio, SQL Server Data Tools, Data stage, Query Analyzer, SQL Server Profiler, Quality Center (HP-QC).
Confidential, Grand Junction, CO
SQL Server SSIS/SSRS Developer
- Actively participate in gathering of user requirement and system specification.
- Responsible for writing SQL queries, stored procedures, views, triggers, T-SQL and SSIS.
- Increased query performance necessary for statistical reporting by 25% after performance monitoring, tuning and optimizing indexes.
- Created/Maintained clustered/non clusteredIndexes for fast and efficient reporting process. Indexed views, dimensions and stored outlines for optimal performance.
- Check the performance of each and every query using SQL Profiler, Index Tuning Wizard and Estimated query plan optimize the performance tuning of SQL queries and stored procedure.
- Backups and Restoration / Database Recovery, Resource allocation / Monitoring
- Deployed SSIS packages and Reports to Production Servers.
- Designed dynamic SSIS Packages to transfer data crossing different platforms, validate data during transferring, and archived data files for different DBMS.
- Creating the expected results by executing the queries according to the test conditions.
- Raising defects where discrepancy found in the database and reporting it to the development team.
- Delivered file in various file formatting system (ex. Excel file, Tab delimited text, Coma separated text, Pipe delimited text etc.)
- Developed inline view queries and complex SQL queries and improved the query performance for the same.
- Did extensive work with ETL testing including Data Completeness, Data Transformation & Data Quality for various data feeds coming from source
- Created Datawarehourse Cubes in SQL Server Analysis Service (SSAS) .
- Created partition table for very large database for good performance.
- Performing DBA functions, installing, configuring and supporting database systems.
- Generated database monitoring and data validation reports in SQL Server Reporting Service (SSRS ).
- Reported all events and requirements through established reporting mechanisms in SSRS.
- Reviewed and developed data models and database designs with development teams
- Designed and developed OLAP using MS SQL Server Analysis Services
Environment: SQL Server 2008 R 2/2008/2005/2000 , Oracle 9i, T-SQL, Excel, MS Access, Advanced Windows 2000/2003 Server, Visual Basic .Net, SQL Server Data Tools, Query Analyzer, SQL Server Profiler.
Confidential, Houston, TX
SQL Server BI Developer
- Installation, configuration of MS SQL Server 2005/2008.
- SQL server Upgrade from 2005 to SQL 2008.
- Designing and creation of tables and stored procedures with E-R diagrams.
- Merging 2 different versions of databases to one single database and upgrading to SQL 2008.
- Designed the Dimensional modeling of the Data warehouse using Erwin.
- Directly responsible for the Extraction, Transformation and Loading of data from Multiple Sources to Data Warehouse
- Extensively involved in designing (Logical and Physical) of customer database.
- Data Warehousing using Data Transformation Services Package (SSIS).
- Created SSRS Report Model Projects in BI studio and created, modified and managed various report models with multiple model objects, source fields and expressions.
- Highly involved in creating, structuring, formatting using Report Designer and Report Builder.
- Creating and managing schema objects such as tables, views, cursors, indexes, procedures dynamically for any tables, triggers and maintained Referential Integrity.
- Database and log backups and restoration, backup scheduling the backups. Backing up master and system databases and restoring them.
- Query optimization and performance tuning. Writing triggers and stored procedures and optimizing it.
- Managing the use of disk space, memory and connections. Running DBCC consistency checks, fixing data corruption in application databases.
- Ensured developers coordinated their efforts in managing and maintaining different application databases.
- Reduced systems downtime risk through automatic offsite backup and recovery over network.
Environment: MS SQL Server 2005/2008, Windows 2003 Server, SQL Query Analyzer, SSIS, TSQL, C#,ASP.NET.
SQL Server Developer
- Involved in Physical and Logical design of the databases.
- Developed DTS Packages to transfer the data between SQL server database and files.
- Created and maintained Data Transformation Service (DTS) packages for migrating data.
- Scheduled Jobs for DTS packages.
- Used BCP/Bulk Insert to publish table output to text files.
- Created advanced T-SQL queries using DDL, DML and Indexes, Views, Cursors, Triggers, User Defined Functions and Stored Procedures.
- Used all Declarative Constraints for maintaining Data Integrity.
- Created Views to restrict access to data in a table for security.
- Involved in performance tuning of T-SQL queries and Stored Procedures.
- Implemented Auditing using triggers.
- Created schema objects, rules and granted roles to the users.
- Used SQL Profiler to debug T-SQL and slow running queries.
- Monitored the Database Performance using execution plans and Index tuning.
- Optimized the query performance with modifications in TSQLqueries, removing unnecessary columns, eliminating redundant and inconsistent data, normalizing database, and establishing necessary joins.
- Involved in scheduling backups of databases and logs and restoring them when necessary.
Environment: MS SQL Server 2005, T-SQL, Windows 2000 server, Windows NT 4.0.