SUMMARY:

• Highly qualified MS SQL Server consultant with over 10 years of experience in implementing end - to-end BI solutions using the Confidential 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 Confidential 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
• Experience in using ProClarity Professional for browsing cubes and creating reports

SKILL:

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

RDBMS: MS SQL 2005/2008/2008 R2/2012, 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:

Confidential, Los Angeles, CA

Sr. Database Developer

Responsibilities:

• Responsible for gathering project requirements from the users for designing and developing data warehouse as needed Responsible for Dimensional modeling.
• Designed and developed SSIS (ETL) packages to extract, transform and load data from OLTP system to the Data warehouse and Report-Data mart.
• Set up a data warehouse environment, install and configure 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.
• Generated code 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.
• Created SSIS Packages to perform filtering operations and to import the data on daily basis from the OLTP system.
• Performed table partitioning to increase the performance.
• Managed index, statistics and optimizing queries by using execution plan.

Confidential, Irvine, CA

IT System Analyst/ Data Warehouse Architect

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, SSIS, SSAS, SSRS, Erwin, Excel, Mortgagebot, Encompass, Blend, Visual Studio 2017, Windows server, SharePoint 2010/2013, Power Pivot, TFS, ProClarity, Tableau, Active Batch

Confidential, Anaheim Hills, CA

MS SQL Server/BI Developer

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 Drop down 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

Confidential, Aliso Viejo, CA

MS SQL Server/BI Developer

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)

Confidential, Irvine, CA

MS SQL Server BI Developer

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

Confidential, CA

MS SQL Server/ Business Intelligence Developer

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.

Confidential, San Francisco, CA

MS SQL Developer/BI developer

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