- 7+ years of IT experience in the development, use and management of MS SQL Server 2008R 2/2012/2014/2016 databases, Data Warehouses and Business Inteligence solutions.
- Extensive experience in MS SSIS 2008R2/2012/2016, Analyze and Design SSIS Packages, building cubes using SSAS and developing reports using SSRS and its different tools including SSMS, Visual Studio, BIDS, TFS, SQL Profiler, Execution Plan and SQL Server Agent.
- Hands on experience in extracting the data from heterogeneous sources such as Flat Files (.csv files), Excel Files, Oracle 11g/10g, MS SQL Server, IBM DB2, FTP Server and XML Source and load the data into target ODS, data marts and data warehouses using SSIS packages.
- Experience in using several control flow tasks sequence container, For Loop, For Each Loop, ActiveX Script Task, CDC task in SSIS 2012, data flow task, BCP (Bulk Insert Program) Task, Execute SQL, File System, FTP, Script tasks, Data Flow Transformations Look Up, Cache transformation, Checksum transformation, Conditional Split, Derived Column, Merge Join and Union All.
- Experience with Package level configurations (XML Package configurations in 2008R2) and Project level configuration (managed environment variables inside SSMS 2012).
- Experience with SSIS 2012/2016 new features including Integration Services Catalogue, execute the SSIS package through the SP’s, Parent child packages, Master Package Executioner, Execute Package task, SSIS DB and ETL Audit databases in tracking the ETL processes.
- Experience in using project parameters in project level configuration while deploying the SSIS projects to the SSIS Integration services catalogue SSIS - DB, validate, test and execute the SSIS packages before the deployment by switching to the test servers using a project parameter.
- Experience in SSIS variables, transaction support feature, transaction isolation levels, and precedent constraints, configuring Error Handing, Logging and Event Handling.
- Extensive experience with SSIS performance tuning using counters, adjusting fast load techniques, error handling, event handling, re-running of failed packages using checkpoints, trouble shoot using break-points and other tuning techniques.
- Experience in maintenance and administration of ETL processes by creating SQL Server Agent ETL jobs with multiple steps, Alerts, SQL Mail Agent, and schedule SSIS packages. knowledge in creating OLAP multidimensional and tabular cubes using SSAS.
- Experience in SSAS 2016 and SSRS 2016 combination by accessing SSAS tabular cubes through SSRS query designer in a way writing MDX queries including with parameters, single value selection and multivalued parameter selection and developed SSRS charts, graphs, gauges, maps, Tablix and matrix reports.
- Extensive experience in developing relational based and cube-based reports using SSRS.
- Very good experience in working with several types of SSRS reports including summary report, detail reports, drill-down, drill through, sub reports, Tablix, matrix, charts, graphs, gauges, Power view and Excel PowerPivot also working with front end UI application dashboard portals.
- Strong experience in T-SQL to write: stored procedures, views, functions, CTE’s, recursive CTEs, Merge statements, Triggers, OPENQUERY, CURSORS, WHILE LOOPS, indexes (covering index, clustered and non-clustered), DDL and DMLs.
- Expert in tuning the T-SQL Stored Procedures, SSIS Packages, SSAS cubes, SSRS Reports and deliver high performance and high - integrity business intelligence reports.
- Excellent knowledge in OLTP transactional, RDMMS concepts, conceptual, logical & physical models, Normalization (1NF, 2NF, 3NF), De-Normalization, OLAP multi-dimensional databases (ROLAP, MOLAP), DataMart’s , Data warehouses, ODS, Dimensional Star Schema Modeling, Snow-Flake Modeling, FACT and Dimensions.
- Quick learner and good performer both in team and independent job environments.
Operating Systems: MS-DOS, Windows 95/98/NT 4.0/XP/2000/2003 Server.
RDBMS/Databases: MS SQL Server 2008R 2/2012/2014/2016, Oracle 11g /10g.
Data modeling and ETL Tools: MS SSIS, Informatica.
Reporting & BI Tools: SSRS, MS Excel PowerPivot, Power BI.
Languages: T-SQL, PL/SQL, HTML, and C#
Microsoft Technologies: Sharepoint, TFS.
Confidential, Richmond, VA
MS SQL Server BI (SSIS SSAS SSRS ) D atabase Developer
- Participating in the business requirements gathering for Account Management System project.
- Worked with data architects and participated in the data modeling, database design and data warehouse design.
- Involved in data migration for loading data from source database into destination database.
- For one of the projects (AMS), designed the end-to-end architecture including developing the data model, designed the database and data warehouse, ETL process, SSRS report and SharePoint web dashboard page.
- Create stored procedures, table valued functions, scalar functions data manipulation.
- Designed the ETL database, data marts, Dimensional Model with Fact and Dimension tables.
- Designed the ETL Refresh Data Model master package using SSIS which contains collection of several master packages running several child packages in different stages.
- Converted the ETL process into a 1 package execution which scheduled to run refresh on AutoSYS
- Created ETL packages to extract the data from disparate data sources, Microsoft Excel files with data in multiple sheets, .CSV files, Oracle RDR source, Safe net files, corner stone files, SharePoint list etc., apply transformation logic using Containers (For Each loop, Sequence and For Loop), control flow tasks and dataflow tasks and load the data into target tables.
- Created the ETL packages in a way that avoid using the stored procedures and replace the code to in-line SQLs within the execute SQL task and convert the stored procedure code to SQL files and retrieve into SSIS using the “file connection” connection manager. This method is to eliminate the CRQ deployment process of databases changes from DEV to PROD.
- Generated SSAS tabular cubes, created measures using DAX expressions, hierarchies, partitions, generated SSRS reports connecting to Tabular cubes using SSRS 2016 new query designer feature to connect cube and write MDX & DAX expressions in it.
- Developed tablix, matrix, graphs, charts, gauges, sub reports, parameterized, drill through, drill up and drill down reports .
- Developed reports directly connecting to SSAS from SSRS and designed several reports.
- Heavily worked on new SSRS reports, existing reports, report performance issues and report support issues for the different UI dashboards.
- Tuned the SSRS reports using the concepts of parameter sniffing, aggregated calculations in stored procedures instead in the report Tablix, sorting in SP instead in Tablix, create a separate summary table with measures and parameter fields for summary report, do INSERT INTO Summary table from SELECT, create indexes on parameter columns and other SP tuning techniques to improve the performance for the large volumes of data.
- Used SQL Profiler, Actual and estimated execution plans to troubleshoot the Stored Procedure Table Scan, Index Scan, Index Seek, Key Lookup, loop and Sort etc. issues.
- Involved in the presentation and demo of the application to the internal Agency users, to get their suggestions and feedback.
Environment: MS SQL Server 2014/2016, Visual Studio 2012/2015, SSRS, SSAS, SSIS, TortoiseSVN, SharePoint, Oracle, TOAD, AutoSys, Agile methodology.
Confidential, Richmond, VA
MS SQL Server BI (SSIS SSAS SSRS ) D atabase Developer
- Participate in the team business requirements gathering and analyze, understand and convert them into database solutions.
- Design ETL Packages with SSIS 2016 to pull data from various sources like DB2, SQL Server and Flat Files to individual Data marts which ultimately go into Enterprise Data Warehouse.
- Extensively worked on CDC (Change Data Capture) at the database level and used control flow items CDC (Change Data Capture) task, CDC Split task into INSERT, DELETE and UPDATE in SSIS.
- Wrote complex Stored Procedures, functions and triggers as and when needed to convert data logically as per the business requirements.
- Extensively used Package Configurations and project level configurations while deploying the SSIS packages from DEV - Stage, Stage - Prod environments.
- Initially loaded data into staging tables using different SSIS control flow tasks, loop containers and data flow tasks, then moved the data into dimension and fact tables.
- Once the data is in place, developed the cube right from the scratch with multiple cube dimensions, hierarchies, measure groups and calculated measures.
- Tuned SSAS cubes and improved both cube query performance and processing performances by by tuning base cube query, creating cube partitions, performing parallel process on cube partitions, creating aggregations, optimize MDX queries, using better I/O systems and using bitmap indexes by setting appropriate properties.
- Extensively worked on SSRS relational based and cube-based reports.
- Implemented CDC techniques in a way that the SSRS reports display latest data, by creating different snapshots of CDC data INSERT, DELETE & UPDATE in DataMart database, and implemented 2 step SSIS job Source to Fact, Fact to DataMart and showed latest data in SSRS reports.
- Developed matrix, graphs, charts, gauges, sub reports, drill through, drill up and drill down reports.
- Deployed the reports to Stage and Production, wired the reports from SSRS report server to UI, and tested the reports on UI dashboard portal.
- Generated dashboards using POWER BI desktop and POWER BI Server.
Environment: MS SQL Server 2014/2016, SSRS 2016, SSAS 2016, SSIS 2016, Oracle 11g/10g, TOAD, MS Visual Studio 2015, Team Foundation Server, POWER BI.
Confidential, Chevy Chase, MD
MS SQL Server BI (SSIS SSAS SSRS ) Database Developer
- Responsible for managing BI environment, which includes Business model, Data Model, Data sources, ETL/SSIS tools, target data warehouse, Data marts, and Reporting tools.
- Design and tune ETL process.
- Working closely with the Guide ware team on business aspects.
- Creating large number of stored procedures and created parameterized SSIS Packages to move data from different sources to target. Responsible for implementation of data viewers, SSIS Logging, error configurations for error handling the packages.
- Configured files to deploy the SSIS packages across all environments. (test, uat and prod).
- Used Joins, correlated and non-correlated sub-queries for complex business queries involving multiple tables from different databases.
- Implemented Functions, views, triggers, stored procedures and enforced business rules via checks and constraints. Performed Replication, Backup compression for individual tables and indexes and reducing the disk usage and I/O resources.
- Extensively worked on error handling at different levels of SSIS packages like control flow level and data flow level.
- Analyzed and test data in the cube using SSAS.
- Used SSRS to create reports, customized Reports, on-demand reports, and involved in analyzing multi-dimensional reports in SSRS.
- Designed and implemented Parameterized and Cascading Parameterized reports using SSRS.
- Wrote Parameterized Queries for generating Tabular reports and Sub reports using Global variables, Expressions, Functions, Sorting the data, Defining Data sources and Subtotals for the reports using SSRS. Configured and maintained Report Manager and Report Server for SSRS.
- Creating ad hoc queries according to business need and tuning existing queries to meet business standards.
Environment: MS SQL Server 2008R2 & 2012, SSRS, SSAS, SSIS 2012, Oracle 11g/10g, TOAD, MS Visual Studio 2008 & 2010, MS Office.
Confidential, Lenexa, Kansas
SQL Server Developer
- Created and managed stored procedures, indexed views, user defined functions, triggers, database constraints to meet business requirements and policies.
- Thorough analysis and testing of database objects & T-SQL statements before deployment to the production server.
- Created SSIS packages to clean and load data to data warehouse and package to transfer data between OLTP and OLAP databases.
- Created and configured an OLTP replication database for disaster recovery purpose.
- Created SSIS Packages using Pivot Transformation, Derived Columns, Condition Split, Term extraction, Aggregate, Execute SQL Task, Data Flow Task, 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 ware house.
- Extracted data from XML to SQL server.
- Highly involved in creating, structuring, and formatting using Report Designer and Report Builder.
- Wrote and integrated stored procedures to generate special reports according to client needs.
- Worked on various complex requirements of developing monitoring dashboards with multiple KPIs in Performance Point Monitoring Server 2007 and SharePoint.
- Designed and deployed various complex reports using MS Reporting Services.
Environment: SQL Server 2005/2008R2/2012 Enterprise Edition, SQL SQL Server Data tools (SSIS, SSRS), Enterprise manager, PPS, XML, MS PowerPoint, MS Project, Oracle, SharePoint.
Confidential, Omaha, NE
- Worked with Data Modeler to gather business requirements from the business users.
- Worked ETL SSIS for transferring and reviewing data from heterogeneous sources like (Source Databases, FTP Server and Excel, CSV, flat file, Text Format Data).
- Developed T-SQL queries, triggers, functions, cursors and stored procedures.
- Created complex SSAS cubes named “Reporting Consolidated BIWarehouse” with multiple fact measures groups (Invoices, Opportunities, Pending Sales and GLTransaction) and multiple dimension hierarchies based on the OLAP reporting needs.
- Worked on SSRS reports, database performance tuning and query optimization, data integrity and statistics.
Environment: SQL Server 2008, T-SQL, SSIS, SSAS, SSRS, Microsoft Visio, SQL Profiler.
- Reporting Responsibilities - Execute reports on a planned basis and provide to business
- Look into the production issues and solve them within Service Level Agreement.
- Monitor, Identify, analyze and resolve job scheduling problems.
- Deploying the application on the various environments and fixing the issues being faced in the system.
- Handling HPSM tickets for security and access issues
- Handling the SATS administration tickets.
- Provider demographic and contract reporting.
- Complex querying and data manipulation.
- Used to identify data discrepancies and perform database reconciliation.
- Data comparisons between NDB (UHN Reporting) and various legacy systems.
- Approx. 150+ individual reports in current production.
- Monitoring of execution of various reports and looking into the issues in case of any abends.
Environment: SQL Server 2008, SQL Profiler and T-SQL.