Sql Server Data Warehouse

408-***-**** ******.******@*****.*** La Habra, CA

SUMMARY:

Highly qualified MS SQL Server consultant with long years of experience in implementing end-to-end BI solutions using the Microsoft BI Stack (SSAS, SSIS, SSRS and Tableau) within several industries viz. Financial, Health, Service, and Insurance

Well versed with Software Development Life Cycle (SDLC) process and familiar with RUP, Spiral, XP and Agile and Scrum Models

Extensively worked on OLTP Relational Schema with Normalization and OLAP Dimensional Modeling using Star and Snowflake schema

Knowledgeable about Multi-dimensional cube and tabular Models

Very well versed in writing T-SQL and MDX for querying tables and Cubes

Knowledge of SDLC and experience in release and deployment management

Extensively created and debugged large T-SQL database scripts and batches to facilitate effective database management

Highly Proficient in T-SQL programming in creating stored procedures, triggers, views and user defined functions on SQL 2005/2008/2008 R2 2012 and 2014 Servers

Created partitions on tables, indexes and indexed views as well as querying and managing partitions from optimization point-of-view according to the business criteria

Optimized T SQL Scripts by analyzing Execution Plans as well as using Microsoft Tools like SQL Profiler and Database Tuning Advisor

Good understanding of OLAP data warehouse design practices to include the design of cube objects, the design of optimal and efficient cube processing strategies based on infrastructure as well as cube size in both multi-dimensional cubes and

Experience in Importing/Exporting Data from various data sources like Excel, SharePoint list, and Flat file using BCP, Bulk Insert & SSIS package

Experience using many of the SSIS transformations like derived column, Aggregate, Lookup, Fuzzy Lookup, Conditional Split, Derived Column, and Multicast

Designed ETL packages using proper Loggings and Checkpoints, secured packages with Encryption in conjunction with passwords

Well versed in creating Parameterized Reports, Drill down, linked, sub cascading Reports and Reports using SQL server Reporting Services

Proficient in using Report Manager for setting up roles, security on the reports and creating Standard and Data Driven Subscriptions in Delivery of Reports

Highly skilled in Creating Tableau 8.3 and Tableau 9.0 to create report and visualization from numerous sources

SKILLS:

Database Tools : Management Studio, ER-Win, MS Visio

RDBMS : MS SQL 2005/2008/2008 R2/2012,2016,2017,2019,2022 Oracle 9i, 10g, 11g, MySQL

Business Intelligence : BIDS/SSDT, SSIS, SSAS, SSRS, SharePoint 2010, 2013, Tableau, ProClarity

Operating Systems : Windows Server 2003/2008/R2, 7/8

PROFESSIONAL EXPERIENCE:

BD Inc.

San Diego, CA Oct 2020 –Present

Software Engr3

Key Responsibilities

Created Impact Analysis, Design Document and Implemented Database Server Migration.

Upgraded SQL Server Database Into SQL Server 2019.

Upgraded SSIS, SSRS & Database Projects into 2019 version in Visual Studio.

Redesigned legacy SSIS packages to incorporate environment variables for project parameter configuration.

Created SSRS reports and configured data driven subscription for report delivery to end users.

Generated a database script for creating SQL Server login, role definition and user -role mapping within Visual Studio project.

Utilized Visual Studio TFS for generating a build and automated deployment of SSIS, SSRS and SQL Server Database projects using Octopus.

Designed and implemented an ETL process using complex stored procedure and SSIS packages.

Created a process for tracking the overall ETL process using complex SSIS packages, SQL Server Tables and Stored Procedures.

Debugged SSIS Packages by utilizing SSIS features like breakpoints, Data-Viewers and custom loggings.

Produced and Documented test summary information and signed-off testing to facilitate implementation in production environment.

Developed complex stored procedure to recursively clean up data from multiple databases.

Performed shard split managed to optimize performance of data loading of large size data.

Fire Fighters First Credit Union Los Angeles, CA Oct 2018 –Oct 2020

Sr. Database Developer

Key Responsibilities

Responsible for gathering project requirements from business users for designing and developing Dimensional model data warehouse.

Designed and developed SSIS (ETL) packages to automate data import and export from and to various vendors

Set up a data warehouse environment, installed and configured data connection adapters to external sources like Oracle, Excel (ACE and Jet engine)

Created and configured Linked server to Oracle and SQL server environments.

Developed Complex stored Procedure using dynamic sql technique to import multiple tables from Oracle and SQL Server data sources to SQL Server data warehouse.

Created SQL Server Agent Job for the automation of data import to the data warehouse.

Administered third party loan origination software like Mortgagebot and managed data validation between Mortgagebot LOS and the Datamart.

Developed an SSIS package to inquire members FICO score from Experian and export the inbound data to a data warehouse environment to update member credit score and also feed data to Digital and Mobile Banking.

Responsible for creating Databases, Tables, Views, Store Procedure, Triggers, Functions etc. using T-SQL to provide structure to store data and to maintain database efficiently.

Migrated an existing dashboard into tableau

Designed and developed complex SSIS package to generate, export and import data files to / from vendors.

Created a drill through parametrized report using SSRS by applying a complex business algorithm in the query.

Designed dashboard using tableau for analyzing complex business metrics.

Performed table partitioning to increase the performance.

Managed index, statistics and optimizing queries by using execution plan.

Inspire Home Loans\Century Communities Irvine, CA Feb 2018 – Oct 2018

IT System Analyst/ Data Warehouse Architect

Key Responsibilities

Designed and implemented Enterprise Data Warehouse

Automated data file transport across the organization and external vendors using SSIS

Supported and administered third party Loan Origination tools such as Mortgagebot and Encompass.

Designed a Restful API for integration of Loan Origination System Encompass and a POS Blend.

Administered and developed enterprise report using SSRS.

Created and automated ETL process for loading the Data warehouse.

Developed Complex Stored procedures for supporting reports and ETL process.

Created data driven report subscription using email and windows file methods for automated report delivery

Environment: MS SQL Server 2016 /2012, SSIS, SSAS, SSRS, Erwin, Excel, Mortgagebot, Encompass, Blend, Visual Studio 2017, Windows server, SharePoint 2010/2013, Power Pivot, TFS, ProClarity, Tableau, Active Batch

Gateway One Lending and Finance Anaheim Hills, CA Sep 2015 – Jan 2018

MS SQL Server/BI Developer

Gateway One Lending & Finance is a leader in the indirect auto finance marketplace. Corporate headquarters is located in Anaheim, California with large sales and credit locations in Northern California, Illinois, New Jersey, and Georgia.

Key Responsibilities

Requirement gathering, translate business requirements into technical design.

Designed complex SSIS package to automate manual business process.

Created and deployed SSIS packages using various transformations such as Fuzzy Lookup, Fuzzy Grouping, Aggregate and Derived Column Transformations.

Created complex SSIS packages to pull data from Oracle, Excel, flat files to a SQL server database.

Designed SSIS package templates as base code for package development incorporating package configurations, connection managers and logging in it.

Implemented the Master Child Package Model for easier maintenance and better performance of the ETL strategy.

Created Tabular cubes as a source for power pivot based excel reports to performance and reduce downtime.

Created power BI site using Tabular Cubes as a source and published it on monthly basis for finance reviews.

Created and published a dashboard for complex business metrics with KPI using Power BI application.

Project management and documentation by creating Project charter, Business requirement document, Technical design document and Architectural design document.

Designed Oracle package to generate a text file for data importing to a SQL server environment.

Implemented package configurations on the development, test, and production environments.

Analyzed queries for optimization and performance tuning using Graphical Execution Plan, SQL Profiler and Database Tuning Advisor.

Followed the regular reporting life cycle Analysis, Design, Development, Testing and Scheduling to develop User Interface Reports.

Designed and deployed reports with Drill Down, Drill Through and Dropdown menu option and Parameterized and Linked reports.

Delivered on-demand and scheduled reports for business analysis or management decisions using SQL Server Reporting Services.

Scheduled Reports using SSRS to generate all daily, weekly, monthly and quarterly Reports including current status.

Designed an end to end application to automate loan sale process using SSIS package and provided end users a report through SSRS during investor due diligence period.

Designed a drill down, cascaded parameter SSRS report using stored procedure for creating the data set.

Designed a Complex query to resolve multi metrics business logic and provide the report to business through SSRS report.

Designed an Oracle package to automate data transfer from a csv source file into an Oracle data base.

Developed complex ETL Packages using SQL Server 2008R2/12 Integration Services to load data from various sources like SharePoint list/SQL Server/DB2 to Staging Database and then to Data Warehouse.

Environment: MS SQL Server 2008 R2/2012, SSIS, SSAS, SSRS, Erwin, Excel, Windows server, SharePoint 2010/2013, Power Pivot, TFS, ProClarity, Tableau, Active Batch

Carrington Mortgage Holdings LLC. Aliso Viejo, CA Nov 2014- Sep 2015

MS SQL Server/BI Developer

FOUNDED IN 2003, Carrington has evolved from a mortgage credit asset manager into a vertically-integrated financial services company that covers virtually every aspect of the single family residential real estate transaction, including investment in U.S. real estate and mortgage markets, loan origination and servicing, asset management and property preservation, real estate sales and rental, and title and escrow services. Carrington has built the infrastructure necessary to maximize value during any market cycle.

Key Responsibilities:

Supported the existing BI platform for populating data warehouses.

Designed complex stored procedures to perform data profiling in the pre-staging database.

Analyzed different heterogeneous sources like Excel, Oracle, Flat Files, and SQL Server 2005 for ETL strategy that needs to be implemented with the help of SSIS packages.

Created an ETL process and Automated for generating csv files to be exported out of the organization.

Managed the entire ETL process involving the extraction, analysis, transformation, loading and presentation of information to the end users.

Designed an ETL process for the data loading of Dimension tables in existing Data Warehouse.

Designed and implemented a database application for a documentation.

Designed an SSIS package for generating an index file of image files for compressing and storing image files in Nautilus.

Implemented Master/Child Package Models to ensure that only the Master Package is scheduled which will in turn fire the other packages.

Implemented transactions in SSIS by appropriately setting Transaction Option property to Supported, Not-Supported and required based on the type of strategy being implemented.

Written test scripts in T-SQL to validate data on staging tables and data warehouse tables to validate the data results.

Participated in the monitoring of the SQL server performance using tools like SQL profiler, database engine tuning advisor and command line tools.

Generated test data and tested the database to meet the functionalities deliverables in the project documentation and specifications.

Built SSRS project to generate reports with desired formats (Word, PDF, Excel, etc.) to present statistics information according to business requirements.

Generated various reporting services RDLs using reporting services for Account Aggregation reports.

Created Ad-hoc Reports using Report Builder and Report Models using Business Intelligence Development Studio to provide a template for end-users to create reports by themselves with a preselected group of relational tables.

Supported the logging framework by generating reports to identify the trends and set up the strategy to find bug trends, code changes using Team Foundation Server.

Environment: MS SQL Server 2008 R2, SSIS, SSAS, SSRS, Erwin, Excel, Windows server, Flat Files, SharePoint 2010, Team Foundation Server (TFS)

Exsilio/Microsoft Irvine, CA Dec 2013- Dec 2014

MS SQL Server BI Developer

Key Responsibilities:

Created and optimized objects such as: Stored Procedures, Triggers, User define functions, Indexes, Views, and Index views.

Designed a database optimization plan involving the creation of Table Partitions and the use of appropriate Indexes as recommended by Microsoft’s Index Guidelines.

Designed the entire ETL process involving the access, manipulation, analysis, interpretation and presentation of information from both internal and secondary data sources to the business.

Developed complex mappings using SSIS to transform and load the data from various source systems like Excel, Flat files, Oracle into the SQL 2008 Server target staging database.

Used master child SSIS packages for better design and ease of maintenance.

Implemented SSIS data transformation scripts, created maintenance procedures and provided data integrity strategies.

Implemented Error Handling during ETL load in SSIS packages to identify dimensions and facts that were not properly populated.

Involved in implementing proper package configuration features and finally securing the packages by using password and encryption methods.

Monitored all the packages that were scheduled. Involved in debugging the Packages by utilizing SSIS features like breakpoints, Data-Viewers and custom loggings.

Produced and Documented test summary information and signed-off testing to facilitate implementation in production environment.

Designed a cube(47.6GB) in SSAS using BIDS environment; created different set of hierarchies on multiple dimensions, defined relationship between measure groups and Attributes, set attribute and attribute hierarchy properties.

Used SQL 2012 Server Analysis Services to design cubes with dimensions and facts and created different cube objects like KPIs, Drill through Actions, Perspectives, Aggregations, and Translations.

Hands-on experience in managing Reporting Server and creating ad-hoc reports from OLTP and OLAP including its delivery using push and pull delivery methods.

Managed very large reports that return a large volume of records and optimized their performance using techniques like cascading parameters and filters.

Deployed SSRS reports to the reporting server and assisted in troubleshooting deployment problems.

Create caching report, snapshot to reduce the burden of report server. Along with creating snapshot, both standard subscription and data-driven subscription.

Query optimization using different techniques such as data compression, Index tuning, optimization for an optimal parameter combination considering variable sniffing, forcing some queries to recompile where is appropriate.

Used Team foundation Server for Version Control, Do schema compare and updated the TFS target for each release.

Created complex ETL strategies to extract, clean and deliver data to OLAP environment

Environment: MS SQL Server 2008R2/08, T-SQL, MS VISIO, Windows 2003/2008 Server, SSIS, SSAS, SSRS, Access

Union Bank Brea, CA March 2013 –Dec 2013

MS SQL Server/ Business Intelligence Developer

The objective of the project was to create Data Warehouse to track the performance, analyzing transactions.

Responsibilities:

Created new logical and physical design of database to fit new business requirement and implemented new design into SQL Server 2008.

Creating and managing schema objects such as Tables, Views, Indexes, Unique/Check Constraints and referential integrity depending on user requirements and converting them into technical specifications.

Developed SSIS packages to transfer data from OLTP to OLAP database.

Developed the SSIS packages with different transformations, precedence constraints, configuration settings, check points and security levels while gathering data from different sources.

Maintaining existing reports, preparing presentations per management requests, extracting data, data entry into spreadsheets, employing complex and dynamic filters, scheduling reports and managing distribution lists, documenting work procedures, versioning control, and validating data for accuracy.

Created OLAP cubes on top of the data warehouse basing various fact and dimension tables for analysis purposes using SQL Server Analysis Services (SSAS) 2008 and refined the existing cubes by adding the new attributes to the dimensions and defining the relationship between the new attributes and the measure groups.

Created/modified a variety of parameterized, drill down, click through, chart, matrix, and sub reports using SSRS 2008 and migrated the reports from SSRS 2005 to SSRS 2008.

Wrote complex MDX queries to develop the Calculated Members and while gathering the data from the OLAP cubes while generating the reports in SSRS.

Integrated the reporting services with MS Share Point while deploying the reports.

Scheduled the reports to run on daily/weekly/monthly basis and email the results to the business users in the required format.

Worked with SQL Profiler, Tuning Wizard, Execution Plan and Performance Monitor in order to analyze and optimize the queries, procedures.

Added/modified Users, Groups, folders, rights and implemented log shipping, mirroring to maintain the high availability of the servers.

Ran/Tested required queries, reports before publishing and scheduled the tasks to run every day to update changes in the base tables.

Published the reports.

Designed and implemented Stored Procedures and Triggers for automating tasks.

Managed all indexing, debugging, optimization and performance tuning using T-SQL.

Created/modified SQL Joins, sub-queries, and other T-SQL and PL/SQL code to implement business rules.

Involved in documenting the project steps and presenting to the team members.

Hardware/Software:

MS SQL Server 2008/2005, SSRS 2008/2005, SSIS 2008, SSAS 2008, SSMS, MDX, ASP.NET, VB.NET, C#.NET, T-SQL, MS Share Point and Excel.

Bartronics America\Wells Fargo Bank San Francisco, CA Apr 2011 – Feb 2013

MS SQL Developer/BI developer

The objective of the project was to design and develop the data warehouse. Create SSIS packages & reports for Trading Investment banking services. Migration of database from SQL 2005 to SQL 2008.

Responsibilities:

Gathered business requirements, definition and design of the data sourcing and data flows, data quality analysis, working in conjunction with the data warehouse architect on the development of logical data models.

Designed and implemented complex SSIS package to migrate data from multiple data sources for data analyzing, deploying and dynamic configuring of SSIS packages.

from the OLTP to OLAP with the help of SSIS.

Extensively used SSIS for transferring data manually from DTS packages to SSIS packages using SSIS 2005.

Developed and maintained a combined data store for reporting purposes.

Generated reports using Global Variables, Expressions and Functions using SSRS 2005.

Identified the database tables for defining the queries and defined datasets for report generation.

Created parameterized, drill down and sub reports in MS SQL environment using SSRS 2005.

Generated daily, weekly and monthly reports for the managers.

Administered interface to organize reports and data sources, schedule report execution and delivery, and track reporting history using SSRS 2005.

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

Managed the Metadata associated with the ETL processes used to populate the Data Warehouse.

Integrated the reporting services with MS Share Point while deploying the reports.

Worked in conjunction with the data warehouse architect on the development of data mart.

Designed and optimized complex stored procedures to perform data profiling and auditing purposes in the pre-staging database.

Optimized the performance of various T-SQL scripts, stored procedures and triggers by using embedded UDFs, CTEs and System stored procedures.

Created Dimension and Fact Tables for the Staging Database following the Kimball Methodologies.

Designed Mappings between Sources to Operational Staging Targets, Using Star Schema, and designed logic for incremental load using slow changing dimension and Check sum.

Created complex ETL strategies to extract, clean and deliver data to OLAP environment.

Identified and effectively handled early arriving Facts and late arriving dimensions.

Deployed various types of SSIS Transforms in packages including slowly changing dimension, aggregate, fuzzy lookup, conditional split, row count, derived column, script component, etc.

Hardware/Software:

MS SQL Server 2005/2008, SSRS 2005, SSIS 2005, T-SQL, C#.NET 2.0, Visual studio 2005, ASP.NET, DTS, Data Analyzer, java script, MS Access, Excel, SQL Profiler, Windows XP, Erwin 4.2, VSS 2005

EDUCATION:

San Jose State University – Course work towards Masters of Electrical Engineering

Jimma University - Bachelors in Electrical Engineering

Contact this candidate