DEBARUN BANDYOPADHYAY 011********** ac107s@r.postjobfree.com

ac107s@r.postjobfree.com

SUMMARY OF EXPERIENCE

Accomplished Datawarehouse Consultant with 9+ years of progressive IT industry experience including very strong background in Datawarehouse/ETL/Business Intelligence solution - design and development

Good experience in both Enterprise Datawarehouse Architecture (EDW) and Dimensional Datawarehouse Architecture – design & development

Have experience in 3NF Datawarehouse Database and Dimensional Database model – design & development

Good experience in BI Reports, Dashboards, Storyboards – design and development using reporting tools like Tableau, SQL Server Reporting Service (SSRS), Crystal Reports

Good experience in ETL Framework, Package (using Controlflow, Dataflow, Error Handling & Other components) – design and development using reporting tool like SQL Server Integration Service (SSIS)

Good experience in Cubes, Dimensions, Hierarchies, KPIs – design and development using SQL Server Analysis Service (SSAS)

Have experience of using new features of SQL Server Analysis Service (SSAS) like tabular model, using analytical query languages like MDX to fetch data for SSAS based reports, using Microsoft Excel with SSAS to analyze the data in much efficient manner

Good experience in datawarehouse database implementation and database objects implementation like Tables, Views, Stored Procedures, Table Valued Functions, Scalar Valued Functions, Triggers, Cursors, Macros, Synonyms, Columnstore Indexes, Partitioned Indexes, Table Partitions using Microsoft SQL Server database, Teradata database and Oracle database

Good experience in using SQL & TSQL for Microsoft SQL Server database development, SQL for Teradata database development, SQL & PL/SQL for Oracle database development, Query performance tuning, Batch SQL scripts creation (DDL, DML, Data correction, Data analysis)

Successfully completed two certifications in database development – Microsoft Certified Professional (MCP) and Oracle Certified Associate (OCA)

Have experience in using data modeling tool like ERWIN, Sybase Powerdesigner to create data models (Conceptual, Logical, Physical) and do Reverse Engineering to generate data models and create data dictionary reports and

Have Experienced with full SDLC process – Requirement Gathering, Requirement Analysis, Design Specification, Data Modeling, Coding, mentoring (Have experience in leading a team of size 4-6 people), Preparation of Test Cases, Review of Test Cases, Unit Testing, Code Delivery

Good experience in using Agile Scrum project management methodology at the time of working in various project development and product development activities

Interpersonal Skills with the ability to work both as an individual and a team player

Good Knowledge in using file management systems like Microsoft TFS, Clearcase, Tortoise SVN & Accurev

Have good exposure to all phases of software development life cycle (SDLC) with in-depth knowledge of agile methodology, EDW implementation life cycle, problem solving skill and escalation procedures

SKILLS

Datawarehouse & ETL- Design & Development (MSBI Stack – SSIS / SSAS)

Good experience in Data Warehouse implementation.

Having good understanding of ETL (Extract, Transform and Load) process using SSIS.

Good experience in Client Handling, Requirement gathering & Analysis, Data Modeling (Conceptual, Logical & Physical), ER Diagram Preparation using ERWIN, Coding, Preparation of Test Cases, Review of Test Cases, Unit Testing, Preparation of Test Reports.

Have good experience of analyzing the source data from different sources (SQL Server database and Flat files (CSV)) and creating ETL objects (tables, views, triggers) for the same.

Good experience in SSIS Control flow and Dataflow creation, package execution monitoring, Issue debugging & provide solution.

Good experience in using Merge, Merge Join, Sort, Data Conversion, Derived Column creation in SSIS.

Have experience in using Execute SQL task, Script task, and different type of containers (Sequence, For Loop, and For-each Loop) in SSIS.

Have experience in using various transformations like lookup, date, conversions, Merge, Merge Join, Union All, Derived Column, Audit for transformations using SSIS.

Have experience in extracting data from different sources (SQL Server database and Flat files

(CSV)) using SSIS.

Have good experience in developing ETL program for supporting Data Extraction, transformations and loading using SSIS.

Performed data manipulations using various SSIS Transformations like Filter, Expression, Lookup (Connected and Un-Connected), Aggregate, Update Strategy, Normalizer, Joiner, Sorter and Union.

Good experience in working with SSAS in creating cubes, dimensions, data source views, named queries, dimensions and deploying of analysis services projects.

Good Knowledge in SSAS storage and partitions, and Aggregations, calculation of queries with MDX, Data Mining Models, developing reports using MDX and SQL.

Good experience in report design & development (using Crystal Report 11 & SSRS 2008 R2).

Having good understanding of OLTP & OLAP database concepts.

Good experience in monitoring DAY-END jobs for data population in OLAP databases in Decision Support System (DSS).

Having strong experience in both of OLTP & OLAP database’s data analysis and solving data related issues in both OLTP & OLAP databases.

Report- Design & Development (Tableau / SSRS / Crystal Reports)

Hands on experience on Tableau Desktop versions 9.2.2, Tableau Reader and Tableau Server.

Produced attractive visuals/dashboards to convey the story inside the data using Tableau.

Built dashboards using techniques for guided analytics, interactive dashboard design, and visual best practices.

Involved in trouble shooting of performance issues which were associated with Tableau reports.

Created action filters, parameters and calculated sets for preparing dashboards and worksheets in Tableau.

Developed Tableau workbooks from multiple data sources using Data Blending.

Generated Tableau Dashboard with quick/context/global filters, parameters and calculated fields on Tableau.

Developed stories, dashboards related to different KPIs for Sales, Manufacturing & Insurance claim related business areas using Tableau.

Have good experience in creating audit related dashboards using Tableau, SSRS.

Extensive experience in analyzing business requirements and implementing complex reports using Crystal Report XI, SSRS.

Extensive experience in creating and designing complex reports (Single Page report, Multi Page Report, Sub Reports).

Created canned reports, Dashboards, Parameterized reports using Crystal Reports & SSRS.

Implemented report bursting framework in SSRS to restrict users to visualize specific reports.

Used report grouping, calculated fields, sub-reports during report development by using Crystal reports, SSRS.

Created conditional formatting during report development using Crystal Reports & SSRS.

Strong experience in generating reports, using SQL Server database (2008 R2, 2012).

Strong experience in generating reports, using Oracle (9i, 10g) databases.

Database Development (SQL Server, Teradata, Oracle)

Strong experience in creating stored procedures, Triggers, Functions (scalar valued & table valued user defined) and other database objects (Tables, Views, Cursors, CTE) using SQL Server Database (SQL Server 2005/2008/2012/2014)

Good Experience in monitoring SQL Server Performance using SQL Profiler, Index Tuning Wizard and Windows Performance Monitor

Good Experience in Creating and Updating Clustered, Non-Clustered and Column Store Indexes to keep up the SQL Server Performance

Good experience in Normalizing and De-normalizing the tables and maintaining Referential Integrity by using Triggers and Primary and Foreign Keys, creating Batch SQL, DDL, DML scripts, creating and scheduling of SQL Server agent jobs to execute T-SQL codes Daily/Weekly/Monthly basis

Completed Microsoft Certified Professional (MCP) certificate in SQL Server database development

Good experience in creating Tables, Views, Constraints, Index (B Tree, Bitmap and Function Based), stored procedures, triggers, functions, packages, cursors, object Types using Oracle database (9i/10g)

Good experience in using Ref Cursor, External Tables and Collections using Oracle database

Good experience in in creating Dynamic SQL, Collections and Exception handling using Oracle database

Completed Oracle Certified Associate (OCA) certificate in Oracle SQL & PL/SQL development

Have good understanding of MPP (Massive Parallel Processing) architecture of Teradata database (Ver 14)

Database Modeling

Good experience in creating different layers of Datawarehouse system like Stage, Operational Data Source (ODS), Datawarehouse & Data Visualization layers

Good experience in analyzing different data sources and identifying subject areas, reference data and transactional data to design the most efficient datawarehouse solution

Good experience in datawarehouse implementation using both Bill Inmon’s EDW concept and Ralph Kimball’s Dimensional Data model concept

Good experience in implementation of Star Schema, Snowflake schema, Starflake & Fact Constellation schema.

Good experience in implementing different types of dimension tables like Slowly Changing dimensions, Rapid Changing dimensions, Conformed dimensions, Role Playing dimensions, Junk dimensions, Inferred dimensions, Degenerated dimensions

Have good understanding of different fact types (Additive, Semi-Additive & Non-Additive) and good experience in implementing different types of fact tables like Transactional fact table, Accumulating Snapshot fact table and Periodic Snapshot fact table

Have good understanding of OLTP & OLAP database concepts and good experience of creating ETL jobs to process data in full load and incremental load mode (responsible to populate data from Source to Datawarehouse/OLAP database)

Strong experience in complex data analysis using SQL queries, Excel sheets and resolve data related issues

PROFESSIONAL EXPERIENCE

Datawarehouse Consultant (Financial Services) 2017 – Present

Teradata Corporation / Pravam Pte Ltd

Working for client – Standard Chartered Bank to implement the Teradata FSLDM architecture and enhance it to fulfill the implementation of IFRS 9 regulations

Responsible to create Physical tables, Derived tables, Volatile table, Global temp tables, Views, Macros, Stored Procedures using Teradata database

Responsible to analyze large data mart views and convert those into physical tables using Teradata databases to generate report for further analysis and decision making

Responsible to perform complex data analysis against large data set to identify the characteristics of data using Teradata database

Responsible to test ETL jobs to check the data population, Surrogate/Datawarehouse key generation against Source Keys in different layers of EDW using Teradata database

Responsible to analyze different data sources and implement views and ETL Job entries with different execution types (Daily, Weekly, Monthly) in the existing ETL framework to extract data in different modes (Full Extract, Delta Extract, Ad-hoc Extract) from those data sources

Senior Consultant (Health Insurance, HRMS) 2015 – 2017

Capgemini Technology Services India Ltd.

Worked for clients – Willis, Bupa & Univers and delivered Datawarehouse, BI Reports, Data Quality solutions successfully from them

Worked in business domains like Health Insurance, Employee Benefit & Administration

Responsible to perform the full analysis of existing application (OLTP) database architecture and identify the main data sources, design - dimension data model (dimension and fact tables design) for OLAP database, data dictionary, data load sequence to load data into dimensions

and facts, logical and physical data model creation using Erwin and maintenance using Microsoft TFS

Responsible to create end-to-end data flow architecture as ETL process using SSIS 2014, extensive log event system, which will help to track each step of the ETL process, during execution through SSIS 2014

Responsible to design and create - metadata tables to control the overall ETL data flow process, SSIS packages using different types of SSIS tasks (Execute SQL Task, Derived Column, Merge Join, Conditional Split, Aggregate etc.), log based SSIS package to trace different execution issues by using different options (OnError, OnWarning, OnTaskFailed, OnProgress, OnPreExecution, OnPreValidation, OnPostExecution, and OnPostValidation) in event handler to extract data from various data sources/business systems including MS Excel, MS Access, Flat Files etc.

Responsible for ETL framework deployment using SSIS Project deployment model to deploy the SSIS solution in DEV,UAT.QA, PROD environments

Responsible to analyze different KPIs create report using - Cross tabs, Heat maps, Box and Whisker charts, Scatter Plots, Geographic Map, Pie Charts and Bar Charts and Density Charts with report development tools like SSRS, Tableau

Implemented parameterized reports, table calculations, Groups, Hierarchies, Drill Down, Drill through, linked and sub reports using SSRS

Created interactive dashboards and storyboards using Tableau

Developed Data Sources, KPIs, Cubes, Dimensions, Hierarchies using SSAS

Developed MDX queries to extract data from SSAS cubes to generate reports

Responsible to create tables, views, primary keys, indexes, creating triggers (instead of insert), stored procedures, DDL, DML, Batch SQL scripts for Unit Testing in Sql Server & Oracle databases

Responsible to create HLD (High Level Design), LLD (Low Level Design) – documents, High Level Project Development Plan, Weekly Status Reports

Responsible to train and mentor a team of five people for project development work

Lead Consultant (Financial Services) 2015 – 2015

Genpact Headstrong Capital Markets

Working for client – Nomura Holdings to fulfill the implementation of BCBS 239 regulations

Responsible for analyzing the business (by extracting data from FpML message using PL/SQL) & doing database modeling according to that data by using Sybase Powerdesigner 16.5

Responsible for logical and physical data model creation and maintenance, data dictionary creation, report generation to show the table and column details with description, knowledge transfer session with development team

Responsible to implement and maintain Conceptual, Logical & Physical Data models

Responsible to provide support for report and cube development using SSIS and SSAS respectively

Senior Systems Consultant (US Healthcare) 2011 – 2015

Siemens Technology & Services Pvt Ltd.

Worked extensively on US Healthcare domain during working as a part of the product development team of Soarian Financial Revenue Cycle Management product of Siemens Technology & Services Pvt Ltd

Developed new reports/stored procedure/pipeline function based on business requirements using Crystal Reports, SSRS. Business logic is embedded in the stored procedure itself and

the result set of the procedures is taken as the input data for the crystal file where formatting of the data set used to be done

Developed ETL functionality i.e. loading of data from OLTP to OLAP databases is taken care by means of stored procedure, SSIS packages and was responsible for enhancement and/or bug fixing of the existing reports/ stored procedures, SSAS (facts, dimensions, cubes, MDX queries) objects to meet the business requirement

Responsible to capture control metadata by leveraging BCP command (SQL Server), check-in the data files in file management systems e.g. IBM Clearcase, Accurev etc.

Developed conditional formatting, calculation fields, report header & footer formatting, page header & footer formatting, parameterized reports, sub-reports, canned reports, bar-charts, pie-charts, donut-charts using SSRS and Crystal Reports

Developed Data Sources, Data Source Views, Calculated Fields, Derived Tables, KPIs, Cubes, Dimensions using SSAS

Received certificate of appreciation to create automation system to turn the manual effort into an automated one to capture control metadata modifications

Systems Consultant (E-Governance) 2008 – 2011

ESSPL

Worked on E-Governance domain at the time of working with development team to develop AMANDA web based platform for the client CSDC Systems (Canada)

Experienced with full SDLC process –

a)Requirement Gathering

b)Requirement Analysis

c)Design Specification

d)Data Modeling

e)Coding

f)Mentoring (Have experience in leading a team of size 2-3 people)

g)Preparation of Test Cases

h)Review of Test Cases

i)Unit Testing

j)Code Delivery

Developed tables, views, triggers, stored procedures, functions, cursors, sql scripts (DDL, DML, Batch SQL) with programming languages like T-SQL and PL/SQL using SQL Server and Oracle databases respectively

Developed parameterized reports, Sub-reports, Canned reports, complex reports using Crystal Reports

Responsible for develop and maintain physical data models using ERWIN

Developed data dictionary reports using Crystal Reports

CERTIFICATION & ACKNOWLEDGEMENT

1.Microsoft Certified Professional (MCP) – Querying SQL SERVER 2012

2.Oracle Certified Associate (OCA) – PL/SQL Developer

3.Certification of Appreciation from SIEMENS

EDUCATIONAL SUMMARY

1.Bachelor of Technology from Narula Institute of Technology, Major - Computer Science & Engineering.

2.Class XII from Icchapur Anandamath Vidyapith, Specialization - Math, Physics, and Chemistry & Biology.

3.Class X from Barrackpore Ramakrishna Vivekananda Mission Vidyabhaban, Specialization- Mathematics, Physical Science and Life Science.

PERSONAL DETAILS

Name : Debarun Bandyopadhyay

Address : 40, Thakur Ramakrishna Road, Barrackpore,

Kolkata, PIN – 700122, West Bengal, India.

Current Designation : Technical Specialist at IGATE

Date of Birth : 23-JUN-1986

Nationality : Indian

Marital Status : Married

Language : English, Hindi, Bengali

ACTIVITIES & INTERESTS

1.Play cricket

2.Watch Movies

3.Cooking

Contact this candidate