Bhavesh Patel
Burlington,NJ
Summary
A skilled Database Developer and Data warehouse architect with more than 17 years of MS SQL Server 7.0/2000/2005/2008/2008 R2 experience as a back-end developer, data warehouse developer/Architect in financial industries and broker dealer. Strong experience with database architecture, design, coding, implementation and production support. Extensive experience in writing T-SQL (DDL/DML) queries, Stored Procedures, views, functions and Triggers. Solid experience with query optimization, performance tuning of Stored Procedures and with upgrading from MS SQL Server 2000 to MS SQL Server 2005/2008/2012/2016/2022. Experience in developing and designing reports on Crystal Reports X. Well versed with the design, implementation of backup strategies and building extensive Disaster Recovering Plan. Strong experience migrating from heterogeneous sources including Oracle to MS SQL Server, data extraction and loading using DTS in MS SQL Server 2000 and SSIS in MS SQL Server 2005 and with the development of reports utilizing Crystal Reports X. Good experience in VBScript, HTML. Expertise using tools like SQL Profiler, Index tuning, Windows Performance for monitoring and tuning MS SQL Server performance. Strong knowledge and experience extracting and transforming data using DTS Import Export Data, DTS Packages, Bulk Insert and BCP. Good knowledge in developing and implementing Disaster Recovery and Replication Models. Experience with the Master Data Service /MDM to maintain reverence data and integration with the Data warehouse. Strong and influential communication and interpersonal skills with efficacy in follow-up and leadership roles. Highly motivated with a drive to take up challenges and put problem-solving to effect in a dynamic environment. Having experience in large Data warehouse system as Core member of Data warehouse designing team. Exposure to different reporting suites such as Tableau, Micro strategy reporting Suite.
Overview
17
17
years of professional experience
Work History
Director
ICBC FS
NYC
06.2022 - Current
- Worked on conversion project as Tech lead on different deliverables
- Technology lead for Trade Feed reconciliation, ETL for data load to DW, portal on top of real time data and Batch data for client and internal use
- Lead development for client portal using SSO, asp.net,c#, web service, rest API ,dev express tool
- Developed report suite for client including daily/outstanding activity, position, daily/Monthly pnl, cash balance etc
- Internal reporting development for accounting, compliance, product control, credit and risk department using new platform
- Delivered extract for downstream system such as GL, HO reporting,2052 A and 2590, Liquidity and sales margin reporting
- Responsible for defining process for BCP and daily data synch
- Responsible for BRD, documentation for project related tasks
- Delivered in-house tool for reconciling daily suspense, wash, cash and firm OOB accounts on Top of Broadridge data
- Developed data quality check reporting suite for IT to make sure Realtime and EOD data quality and verification.
Vice President
ICBC FS
NYC
06.2013 - 01.2022
- Join ICBC as Fulltime and responsible for day-to-day IT task including BCP/DR set up configuration and testing
- Responsible for daily support, development and troubleshooting of downstream reporting solutions
- Worked on in-house project as developer for FR 2052A,2590 reporting, liquidity reporting
- Worked with PMO team for GAP analysis, legacy trading system migration to Broadridge financial for Finance and cash trading
- Responsible for Broker feed translation and centralize to new format for Broadridge system includes Dealer web, broketech, fenics, Bloomberg Toms, FTP
- Database architect/ETL Lead for Datawarehouse to centralize data from different feed Broadridge, such as IREP ,BPSA,Bony,DTC from source like SQL database, MQ, swift, CSV, fixed length Realtime and EOD batch mode
- Experience for Managing in house development team including BA/QA/ Developer
- Responsible for coordinating with PMO for Project plan and deliverables.
MS SQL Server / Data warehouse Developer (TSQL, SSIS, SSRS, SSAS 2008 R2)
ICBC FS
NYC
06.2012 - 01.2013
- Working as MS SQL Server / Data warehouse Developer on firm reporting platform to replace legacy application reporting system for Accounting, revenue reporting, Trading PnL reports ,Margin Reporting, Fixed income, Equities, Income Expense allocation, Clearance Revenue, Daily compliance, Client profitability, Position, Market data, Risk Reporting, Liquidity,FR2052A/2590 , Technology using MS SQL server 2008 R2 BI Tools
- Working with Business users to maintain and categories contra party, client, products, Security Master, Trades, Holding, position, Balance, margin Accounting, Internal Risk Rating, Price Marking for valuation, portfolio Information for the Post trade analysis system called InteliClear
- Worked with business users on requirement gathering of the report’s requirements, underlying data analysis
- Responsible for the solution architect including data warehouse architecture, SQL object design ETL design and implementation, analytic solution and end reporting using excel, SSRS
- Responsible for the technical documentation such as metadata document of the data warehouse db, ETL, Analytical solution
- Used Microsoft Visio for the data modeling
- Created common File load process to using SQL server and SSIS package to allow loading any fixed length or csv file into staging environment using control table in SQL server
- Used control table to store the file information and the format detail will be used by the common csv load process
- Created Stored Procedure to populate tables for reporting use such as PnL, position, Margin reports and Haircut reports, revenue allocation
- Used log tables to keep track of the loaded files and created dependency mechanism for SSRS report generation
- Used SSAS cube for repo/reverse Repo reporting
- Created multiple calculated measures in cube for the reporting purpose
- Created different hierarchies based on different mapping information such client, trading account security type mapping across the different system sources
- Developed SSRS reports, used MDX and SQL stored procedure to query the data
- SSRS Subscription is used to deliver report in excel format
- Responsible for the SQL object design such as tables, UDF, Procedure, views etc
- Responsible for the documentation of solution and deployment instruction.
MS SQL Server / Data warehouse Developer (TSQL, SSIS, SSRS, SSAS 2008)
UBS
Jersey City,NJ
10.2010 - 05.2012
- Working in UBS Mortgage IT department as Data warehouse Developer to replace legacy application reporting system with the analytical solution with using data from different system such as Trading , Reference data, agency pool and the position data, loan
- Work with business user to identify valuation and PnL attributes and appropriate risk model for different security and asset types such as Swaps,Equtiy,Strips,Government bonds,Munis,MBS,CDS,CMO, ABS,CMBS,ABS,Whole loan Performance etc
- Ultra Bound Reporting: UB is application allow users from different trading Desk to mark the position and maintain reference data such as risk mapping,criteria,bookbag for the daily reporting solution
- BI solution required to capture reference data, trade, position, instrument,risk_type,book,ratings,security type, asset type, Deal data from different source system for the analytics Purpose
- Mortgage AS is the analytical solution based on underlying Data warehouse
- Excel and SSRS Reports, Tableau are used for the reporting purpose
- AgencyDW: AgencyDW/AgencyAS is analytic solution to replace Sybase/PERL script process which runs on 4th business day of month to load monthly pool data from different agency source and runs the complex SQL for the calculation and reporting MBS pools
- This was time consuming process as Perl script used for ETL and SYBASE db was not indexed properly
- Worked with business user, Business analyst and trading assistant on requirement gathering of the reports requirements, underlying data analysis
- Responsible for the solution architect including data warehouse architecture, ETL design and implementation, analytic solution and end reporting using excel ,SSRS Tableau
- Responsible for the technical documentation such as metadata document of the data warehouse db, ETL, Analytical solution
- Responsible for the end user training of using the BI solution
- Used Erwin for the data modeling
- Designed star-schema design for the data warehouse used SQL 2008 database engine
- Used SSIS packages to load data from different sources such as Sybase, SQL Server, CSV, Excel to Staging and DW and Process cube
- Used Custom error handling to capture the error message and send as email in case of the on error event
- Used look up cache task to improve performance of the fact table load
- Used SCD for the dimension data load
- Designed analysis cube using SSAS 2008
- Created multiple calculated measures in cube for the reporting purpose
- For monthly load mechanism added in SSIS package to create and process new partition for each month of data
- Implemented role based security on restrict the user to view books
- Used multiple bridge tables as hidden measure for implementing security in cube
- Created different hierarchies based on different mapping information such as reference instrument mapping, Book bag, critera as well as from some user define columns for the risk analysis
- Used time intelligence and currency conversion in SSAS cube
- Developed SSRS reports, used MDX to query the data
- SSRS Subscription used to deliver report in excel format
- SVN has been used for the version control
- Responsible for the ETL performance tuning used log analyzer
- Responsible for the SQL object design such as tables, UDF, Procedure, views etc
- Worked with DBA and GSD for the performance tuning of the SQL queries and MDX queries
- Responsible for the documentation of solution and deployment instruction
- Developed SSRS reports based solution for the SSAS database documentation and data warehouse data dictionary.
MS SQL Server Developer/MS SQL BI Developer (TSQL, SSIS, SSRS, SSAS 2005/2008)
Credit Suisse
NY
05.2008 - 10.2010
- Vantage: Business requirements and deliverables agreed in regards to Expense Harmonization - PeopleSoft Reporting Cube (Vantage)
- Worked with Business users for creating different alternative hierarchy and entity for IF else reporting purpose
- Manage and deploy Reporting Reference Data and Hierarchies using the Master Data Management Platform
- Build a Reporting Cube that integrates PeopleSoft Actual Hierarchies, Actual Data, Presentation Hierarchies, and Budget/Forecast Data
- Generate the FRL AI Fund Head Financial Expense Details report from the Cube
- Enhancements to the existing AI Financial reports to handle the new PeopleSoft Expense Harmonization structure
- Allow user to View Reports in Multi currency
- Trax/Spider: Is another BI solution which replaces existing reporting system with the SSAS cube which allow user to view the expense and invoice information at the one dashboard from different source system
- KAM: Is the replacement of the legacy application reports system to DW/SSAS cube
- OLTP application is used to maintain transactions for the different fund / legal Entities
- Designed data warehouse to load data from different accounting environment to central Data Warehouse system
- Worked with the business user on EDM(MDS) to maintain portfolio and fund accounting information to use with downstream system for reporting purpose
- Created ETL Process using SQL stored Proc and SSIS packages to import and export data from EDM
- Part of team who responsible for the requirements gathering and logical and physical design of DW for the use with SSAS cube to replace legacy application reporting system for the accounting reporting
- Used Erwin/Visio for data modeling
- Responsible for logical and physical design of ETL Process
- Designed Package template for the SSIS
- Implemented common functionality in SSIS package templates such as common event handling, Error handling, package configuration etc
- Designed ETL packages which loads Reference data, to staging environment on daily bases and sends Email to notify user groups with detail information such as file name, number of record, date and process time
- Designed ETL Packages which loads data into DW from staging environment
- Implemented SCD for dimension with parent child relationship
- Implemented intelligent system in SSIS package to restrict unnecessary processing of SSAS object
- Used various dataflow and control flow controls for the ETL
- Package loads data from different source system such as FTP (CSV files), SQL server 2005,Sybase, Web service (Currency exchange rate)
- Responsible for the validation of the aggregate data from the source system to DW
- Worked on design of ETL process to load and unload data to/from EDM for maintaining and exporting Hierarchy information for the reporting purpose
- Written stored procedure and function for the ETL
- Responsible for the migration of SSIS Packaged from SQL 2005 to SQL 2008
- Worked on performance tuning of overall ETL process
- Developed the Slowly Changing Transformation to maintain the Historical Data
- Cube designing includes Dimension Modeling, Hierarchical relations, Attributes definition, Start schema using Business Intelligence Development Studio (BIDS)
- SSAS 2005 and SSAS 2008
- Responsible for the deployment and maintenance of Cubes
- Worked on SSRS reports with the use of data from SSAS cube as well as from SQL server database (SSRS 2005, 2008)
- Worked on Performance tuning of ETL process as well as SQL code used in Stored Procedure
- Involved in performance tuning and optimization of complex SQL queries
- Designed stored procedure and function for the ETL functionality and with the use of Reports
- Used Team Foundation Server (TFS) for versioning and source code control
- Maintained good working relationships with internal support teams and Client
- Converted legacy application reports from crystal report to SSRS report with use data from SSAS cube
- Designed reports in complex summary and detail reports in SSRS, Used matrix, table and sub reports controls.
MS SQL Server & Report Developer
KPMG
Montvale, NJ
08.2007 - 04.2008
- IA Reporting Solution: The main aspect of IARS is to develop the reports based on the OLTP Roster database for the ticket management and the activity analysis of the user of the Inter Action application users
- Responsible for logical and the physical design of the data mart for the use reporting services with the use of the OLTP Roster Database in SQL Server 2005
- Designed the data mart with the use of the fact and dimension table model
- Also designed the aggregate table for the usage summary reports
- Responsible for the design of the SSIS package for the update the data mart from OLTP database
- Stored configuration information in SQL Server table for the migration from development server to the production server
- Used various dataflow and control flow controls for the ETL
- Responsible for the validation of the aggregate data from the ODS and the OLTP Database
- Return the stored procedure for the ETL functionality from ODS to Data mart in SQL Server 2005
- Developed the Slowly Changing Transformation to maintain the Historical Data
- Created Alerts for successful or unsuccessful completion of scheduled Jobs
- Designed the usage summary and detailed reports in SSRS 2005 from the requirement document and the old report Mock-Up in MS Excel
- Responsible for the reports deployment on the reporting services website and created subscription for the windows file share
- Returned the stored procedure and the UDF for the reports
- Implemented three layer security for the reports in SP for the reports, SSRS website, and in the Intranet web application
- Returned the custom code in reports to avoid the case statement in the SQL stored procedure.
MS SQL Server & .NET Developer
ESPN
Bristol, CT
02.2007 - 08.2007
- Scope: User Administration Application
- Worked on the upgrade of UAA database from SQL 2000 to SQL 2005
- Responsible for the performance tuning of Stored Procedures and Triggers
- Designed SSIS packages which automate the process of the creating Active Directory user with the use of SAP data comes from the Intranet application
- Upgraded SQL Server 2000 databases to 2005, using Detach/Attach, Back up & Recovery and by Replication
- Used Database Constraint Checker commands to check database and maintain database
- Updated ETL packages into the SSIS Packages
- Developed Jobs to extract data from SAP DATA source systems using SSIS packages
- Created the alert feature for successful or unsuccessful completion of scheduled Jobs
- Utilized data grid, data list control, repeater control, Crystal Report and SQL reports in .Net for the intranet application master detail report
- Updated class and function from VB to C# which gave functionality to create and update the Active Directory account in addition to work with the Exchange Server 2000
- Used IadsUser and the CDO Com component for the application.
Education
Skills
- DTS (Data transformation services)
- SQL Server Integration Services (SSIS)
- SSAS
- SSRS
- MDM(Master Data Management)
- MDS (Master data service)
- VB 60
- Visual StudioNet
- Crystal Reports X
- Visual Basic 60
- VBNET
- ADONET
- C#
- NET Frame Work Class Libraries
- C
- C
- XML Web services
- OOAD
- MS-Access
- Oracle 80, 9i and 10g
- MS SQL Server 2000 20052008/2012/2016/2022
- MS Office
- Outlook 2000
- Windows 95/98/2000/NT/XP/2008/2012/2016/2022
- Azure cloud
- Office 365
- Staff Management
- Project Management
- Project Coordination
- Budget Control
- Verbal and written communication
- Script review and revision
Business Knowledge
Expert Business knowledge and understanding of different investment products such as Agency pools, Bonds,ABS,MBS,Equities,Swaps,Strips, Whole Loan, Investment Fund accounting and valuation and PnL attributes. Familiar with different Risk Model/Security type such as Credit Risk, Pre Payment Risk, Liquidity Risk, Default Risk, Market Risk etc. Knowledge of Repo/Rev Repo, Stock loan /Borrow Product as Broker Dealer. Experienced in designing solution for Margin call, Daily Transaction to GL recon, Stock loan, balance sheet, netting reporting ,client profitability.
Timeline
Director
ICBC FS
06.2022 - Current
Vice President
ICBC FS
06.2013 - 01.2022
MS SQL Server / Data warehouse Developer (TSQL, SSIS, SSRS, SSAS 2008 R2)
ICBC FS
06.2012 - 01.2013
MS SQL Server / Data warehouse Developer (TSQL, SSIS, SSRS, SSAS 2008)
UBS
10.2010 - 05.2012
MS SQL Server Developer/MS SQL BI Developer (TSQL, SSIS, SSRS, SSAS 2005/2008)
Credit Suisse
05.2008 - 10.2010
MS SQL Server & Report Developer
KPMG
08.2007 - 04.2008
MS SQL Server & .NET Developer
ESPN
02.2007 - 08.2007
Similar Profiles
Watcharakorn SrirangkulWatcharakorn Srirangkul
IT Support&Operation at Industrial And Commercial Bank Of China Limited (ICBC) & ICBC (Thai) Leasing Co., Ltd.IT Support&Operation at Industrial And Commercial Bank Of China Limited (ICBC) & ICBC (Thai) Leasing Co., Ltd.
Tommy FelsteadTommy Felstead
Intern at FS Custom Turf & FS FarmtownIntern at FS Custom Turf & FS Farmtown
Adriana MeloAdriana Melo
Legal Assistant at ICBC - Insurance Corporation of British ColumbiaLegal Assistant at ICBC - Insurance Corporation of British Columbia
Sarah WinderSarah Winder
Patient Transporter at Virtua Memorial HospitalPatient Transporter at Virtua Memorial Hospital
Claudia SwanClaudia Swan
Researcher at Scrooge & Marley Investigating and ConsultingResearcher at Scrooge & Marley Investigating and Consulting