Data Project

CURRICULUM VITAE

Hiren G. Kapadia

Mobile +91-987*******

*********@*****.***

OBJECTIVE

High-performance BI/DWH Lead with an outstanding track record of success in Business Intelligence and Data warehousing technology, analytics, project management, and software development to lead / plan the analysis, architect and development of software solutions.

PROFESSIONAL SUMMARY:

Demonstrated ability to lead, manage and deliver results on both strategic and tactical levels, with domain expertise in various industries. Strengths include:

Over 20 years of experience in all phases of the SDLC. Analyze the client’s requirements, estimate and propose the solutions, Project Planning, Project Architecture, DB Designing, Release Management, Development and Team Management by setting protocols to be followed during the project execution

Extensive experience in the full life cycle of Enterprise Data Warehouse (EDW), Business Intelligence (BI) and Online Analytical Processing (OLAP) systems

Experience with implementing of ETL and data movement solutions using Azure Data Factory (ADF), SSIS. Loading data from Azure storage blob to Azure SQL

DB Architect for Data Warehouse (DW) and Business Intelligence (BI) projects; expertise in the design, data modeling methodologies and development for data warehouses

Hands on working knowledge of current and pipeline technologies; BI Semantics, Data Quality Services, metrics\KPI\scorecard driven analytics and management.

Very good experience in visualization, dashboard and data mining using BI tools such as MSBI, QlikView, Tableau, Spotfire, Telerik, AM Charts and devExpress

Well versed with the knowledge of RDBMS, Data modeling (Logical and Physical), Data warehousing i.e. OLTP & OLAP systems using Star Schema, Snowflake Schema, Fact Table and Dimension Table

Extensive experience in MSBI suite - using SSIS for ETL (data cleansing, data scrubbing, error handing, customized logging and data transformation), SSRS (to creating formatted cross-tab, conditional, drill-down, top n, summary, sub reports, Ad hoc reports, parameterized reports, interactive reports) and SSAS to design and developing complex cubes and MDX queries

Thorough understanding of Kimball/Inmon strategies and extensively involved in the SSAS storage and partitions, and Aggregations, calculation of queries with MDX, Data Mining Models, developing reports using MDX and SQL

Hands on experience on Pentaho BI Suite [PDI for ETL, PSW for designing Cube and BI server] community edition

Expertise in generating reports using Report Builder and Power BI. Data mining to build predictive model

Hands on experience in NoSQL technology like MongoDB (CRUD operations, Aggregation)

Involved to develop POC for ML using Azure ML and Big Data, NoSQL using technologies like Spark, Scala, Cassandra and Zookeeper

Expertise in installing, configuring QlikView server and publisher including QlikView access point. Hands on experience with thorough knowledge of schema/model creation, scripting, data source integration, advanced GUI development, complex logic using expression script, and creating complex dashboards

Expertise in database optimization and performance tuning of database and SQL queries using Query execution plan. Configuring/Troubleshooting SQL server, SQL server reporting service

Very good experience in .NET framework, VB.net, C#, ASP.Net ADO.net, Web Services, .Net Remoting and multithreading

In-depth knowledge and involved in Systems Analysis and Visual Modeling Using UML Diagrams (Flow Chart, Use Case Diagram, Activity Diagram, Class Diagram, & Sequence Diagram)

Pre-sales activity with ballpark estimation, Client-interaction, Documentation, Training, Technology research and Development

On site experience of total eighteen months at Florida, New York, Philadelphia in USA for Analysis and set up of data warehouse of Healthcare and Insurance, Retail, Ecommerce, as a Sr. DWH/ BI Architect

TECHNICAL SKILLS

ETL/ BI/ DWH/ Big Data/ Reporting Tools

Pentaho BI Suite [PDI, PSW & BI Server], MSBI [SSIS/ SSRS/ SSAS], Power BI [Power Pivot, Power View, Power Query, Power Map], QlikView 11, Qlik Sense, Tableau [Desktop & Online], Alteryx, Spotfire, Mondrian, Telerik, DevExpress, AM Charts, Crystal Reports, Cassandra, Spark, Python, Zookeeper

Cloud Technologies

Azure [Blob storage, SQL DB, DW, Azure Data Factory, Databricks], AWS [S3, Redshift]

Databases

SQL Server [All Versions after 7.0], MySQL, Maria DB, MongoDB, MS Access and Oracle 9

Data Modeling

Physical & logical data modeling, Erwin Modeling, FACT & Dimensions tables, Star & Snowflake schema, dimensional and multidimensional modeling and De-normalization techniques, Kimball & Inmon Methodologies

Development Tools/ Programming Languages

VB.NET, C#, ASP.NET, AJAX, Visual Basic, JavaScript, Python, VBA/Macro [AutoCAD, Word, Excel], Dream weaver, HTML, XML

OS/Servers

Microsoft Windows, CentOS

Developing Methods

SDLC, Agile Scrum

Soft Tools

UML, Visio, Microsoft Office Suite, Microsoft Project 2010, VSS/ TFS/ SVN, AutoCAD, RS232/RS485 communication

INDUSTRY AND FUNCTIONAL KNOWLEDGE:

INDUSTRIES

Transportation - Healthcare – Real Estate - Retail POS - Insurance – Ecommerce – Waste management

FUNCTIONAL AREAS

Master Data Management - Data Architecture - Metadata - Database Analysis - Data Warehousing – Data/ Dimensional Modeling - Project Management – Business Intelligence

PROFESSIONAL EXPERIENCE: (Latest First)

Head of BI @ MicroOrbit Consulting Services, Ahmedabad, INDIA

[June 2019 – April 2020]

Project – BI & Analytics [Waste Management]

Responsibilities:

Consult with Sales, Marketing, Logistic and Finance departments to monitor KPI data and measurements that promote budgeting and forecasting

Migrating of aggregate data from SQL DB to Redshift DB using Alteryx tool

Pulling of 3rd party data using API end points

Oversees report / model updates for forecasting trend and performance analysis

Various dashboards for bin collection, logistic, call center departments

Closely involved with Sales & Marketing team to support initiatives by providing quality insights and recommendations to achieve maximum value creation

Manage and execute ad-hoc reporting, dash boarding and business analysis

Administration, maintenance and strategic usage planning of company reporting tools

Work according the agile methodology

Principal Technical Architect @ Cygnet Infotech Pvt. Ltd., Ahmedabad, INDIA

[Feb 2016 – May 2019]

Project – BI & DWH for POS [Retail]

Responsibilities:

USA visits for the meetings and requirement analysis

Finalized technology stacks – MySQL, Pentaho, Mondrian, MongoDB, Power BI, Python

Dimensional modeling to design tenant specific Data Warehouse

Table partitioning and performance improvement to optimize daily ETL operations

Designed ETL solutions using Pentaho Data Integration (PDI/Kettle), Pentaho Design Studio to Load Data from legacy system to Data warehouse

Implemented complex business requirements in the backend using efficient SQL queries and facilitated easy implementation of front-end applications

Built efficient, complex, parameterized SQL queries and stored procedures to produce output data sets

Used Pentaho Data Integration Designer to create ETL transformations

Created Mondrian schema for various Data marts and developed Cubes for Sales Inventory, Customers, Employees and Digital signage

Testing and analyzing relational and OLAP data using MDX queries

Statistical analytics to design user specific dashboards to gain business

Rule based system for the predicting sales, store transfer, ordering inventory

Designing several dashboards using Power BI [Sales, Customers, Stores, Employees and Inventory]

Project – Data migration for the Healthcare

Responsibilities:

Pull EMR data from various types of data sources - MSSQL, MySQL, Firebird, CCDA files, CSVs

Load data to the Sandbox [Linux env] using Python 3.7 code and ODBC connectors

Moving data files to the Amazon S3 bucket using Python code [jobs]

Load data from S3 bucket to Amazon Redshift using Python jobs

Metadata stored into MariaDB.

Project – GSP [GST Suvidha Provider, India Government]

Responsibilities:

Gathered business requirements for analytical BI reports.

Participated in requirements meetings and data mapping sessions to understand business needs.

Identified and logged detailed business rules.

Dimensional modeling based on required Visualizations and Dashboard charts.

Designed complex stored procedures to support ETL packages and staging DBs.

Implemented various control flow and transformation components for ETL (data cleansing, data scrubbing, error handing, customized logging and data transformation)

Developed solution to create dynamic SSAS DBs along with multiple cubes using BIML [created Meta data to deploy and process Dimensions, Measuregroups, partitions and Cubes].

Developed complex calculated measures using MDX and DAX.

Integrated Custom Visuals based on business requirements using Power BI desktop.

Developed Power BI reports using query logic based on business rules, and visuals selected by stakeholders.

Embedded Power BI reports on Salesforce portal page and managed access of reports and data for individual users using Roles.

Involved in data migration using Azure SQL, Azure storage and Azure Data Factory, SSIS and PowerShell

Project – Passenger Information and Fleet Management [UK & Australia]

Responsibilities:

Requirement gathering, analysis, estimation for each sprint, code review, dimensional modeling.

Responsible and accountable for the enterprise data management team.

Architected and maintained Visio logical and physical data models for Data warehouse.

Architected framework for MDM and data warehouse solutions to provide datamart solutions on Transportation analysis.

Designed complex SSIS Packages to Load Data from different feeds/sources to Data mart databases.

Involved to develop and optimize MDX queries to populate aggregate data on the various business performance reports.

Designed the Score carding, Reporting and Dash boarding solution in SSRS.

BI/ DWH Architect @ SAN Healthsoft Pvt. Ltd., Ahmedabad, INDIA

(Offshore unit of JSA [Davita Group] Florida, USA) [Apr 2015 – Jan 2016]

Project - Enterprise Data Warehouse and Analytical Views

Responsibilities:

Onsite visit for requirement gathering, requirement analysis to fit the current, prioritized goals of the project, phase wise estimation, designed logical and physical modeling of database.

Involved in extracting large volumes of data from different data sources, performing transformation and loading that data into another data sources.

Configured and fine-tuned all the ETL (Extract, Transform and Load) workflows.

Metadata driven SSIS package and developed procedures and functions for biological rules.

Created Data marts for Billing, Claims, Clinical, and Demographic, Patient population & Financial.

Worked with Kimball's Data modeling concepts including data marts, dimensional modeling, star schema, fact aggregation and dimension tables, slowly changing dimensions and surrogate keys.

Developed SSAS cubes using multiple dimensions, hierarchies and measures groups. Designed OLAP cubes with star schema and multiple partitions using SSAS.

Provided source data to OLAP engines to create cubes to generate reports on Tableau and SSRS.

Large set of various reports and data models for Power BI was developed.

Created various dashboards using Tableau desktop and deployed using Tableau online.

DB Architect @ Apima Consulting at Vadodara, India

(Subsidiary of MarketTrack LLC, USA) [July 2011 to March 2015]

Project - Online Analytics for USA Retailers [SAAS]

Responsibilities

Worked as a DB Architect - gathering requirement from business users to understand business processes, document project requirements and translate them into functional and non-functional specifications for BI reports and applications, Keeping track of changes in requirements on daily basis.

Identified slow running queries, optimized stored procedures, tested applications for performance and data integrity using SQL Profiler.

Created complex Stored Procedures, Triggers, Functions, Indexes, Tables, Views, SQL joins and other T-SQL code to implement business rules

Created SSIS script task with C# code for obtaining parameters from the applications.

Created SSIS package to automate maintenance, database back up, update statistics, re-build indexes as well as create error log for event during data load.

Data cleansing, data quality tracking and process balancing checkpoints using SSIS.

Designed complex SSIS packages with error handling as well as using various data transformations like conditional split, fuzzy look up, multi cast, column conversion, fuzzy grouping.

Created SSIS Templates for developing SSIS Packages that can be dynamically deployed into development, Testing and Production Environments.

Provided logging, error handling by using event handler, and custom logging for SSIS Packages.

Data modeling, Data Visualization and Dashboard designing using Tableau for business users [POC].

Created reports and Dashboards using Power Pivot and Power View.

Designed complex SSAS solutions using multiple dimensions, perspectives, hierarchies, measures groups. Designed OLAP cubes with star schema and multiple partitions using SSAS

Designing and development of data warehouse, data marts and business intelligence using multi-dimensional models such as star schemas and snow flake schema for developing cubes and building attribute relationships for optimal MDX query performance of Hierarchies and Fact Dimensions.

Developed MDX queries for KPIs, Dashboards and Visualizations.

Implemented Data Quality Service.

Developed data mining models and KPIs.

Worked with tabular, matrix, gauges & chart reports, drill down reports as well as interactive reports according to business requirements.

Designed and implemented Transactional and Snapshot Replication to synchronize data between production and standby server in SQL Server 2014

Migration SQL Server 2000 DTS packages into SQL Server 2008 R2 using SSIS

Sr. DB Analyst/ Project Manager @ Gateway Technolabs Ahmedabad, INDIA. [June 2006 – July 2011]

Project - EDW & BI Solution for HMO

Responsibilities:

Onsite visits for requirement gathering. Set up data warehouse for Financial, clinical and billing data.

Responsible for Analysis, Business User Interaction, Report and database design for Healthcare Applications

Designed and developed several database procedures to improve internal reporting processes and reduce inefficiencies in data gathering.

Created Triggers, Functions, Indexes, Tables, Views, SQL joins and other T-SQL code to implement business rules

Involved in Performance tuning and Query Optimization by using SQL profiler.

Typical projects entailed defining User requirements, identifying relevant data, programming and debugging code and Writing technical documentation to support applications and project implementation.

Worked on developing Transformation, Define Workflows &Tasks, Monitoring Sessions, Export & Import Mappings and Workflows and Backup/Recovery using MSBI ETL tool.

Performed extensive testing on the mappings and wrote queries in SQL to check if the data was loading to the dimension tables and fact tables properly

Created SSIS packages to load data from different legacy systems.

Data cleansing, data quality tracking and process balancing checkpoints.

Worked with Type 1 and 2 dimensions, Fact Tables, Star Schema design and other Data Warehouse concepts.

Designed and deployed reports with Drill Down, Drill Through and Drop down menu option and parameterized and Linked reports.

Identified and worked with parameters for creating cascading parameterized reports in SSRS 2008.

Involved in Debugging and Deploying reports on the production server and analyzed reports and fixed bugs in stored procedures on the ongoing database operations, as needed, to resolve business problems.

Many SSRS Reports (using creating formatted cross-tab, conditional, drill-down, top n, summary, sub reports, Ad hoc reports, parameterized reports, interactive reports) using TSQL and MDX queries.

Building of Datamarts for clinical and financial data to generate various reports.

Architecture and design of QlikView server and integration with SQL Server data warehouse.

Configurable bulk loading from multiple databases into QVDs. Developed incremental loaders.

Development of the business dashboards.

Installed and configured QlikView Server, including iPad offline support.

Assisted in design reviews and prepared strategies for QVD files.

Formulated and executed security standards for use of QV apps.

Project - Online market place, Europe

Responsibilities:

Requirement gathering from client, client interaction and overall execution.

Database Design, Project architecture design, leading the team, task assignments.

Monitored the performance of SQL Server using SQL Server Profiler.

Performed data transformation services and bulk inserts for large amounts of data.

Generated multiple Enterprise reports using SSRS from SQL Server Database (OLTP) and SQL Server Analysis Services Database (OLAP) and included various reporting features such as group by, drilldowns, drill through, sub reports etc.

Created SSIS Packages using Pivot Transformation, Fuzzy Lookup, Derived Columns, Condition Split, Term extraction, Aggregate, Execute SQL Task, Data Flow Task, and Execute Package Task etc. to generate underlying data for the reports and to export data from Excel Spreadsheets, Text file, MS Access and CSV files.

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.

Project - Online Ticketing System, USA

Responsibilities:

Onsite visits for requirement gathering, project inception, planning and requirements engineering. FRS prepared by detailing of design docs including flow chart, use case diagram & class Diagram preparation and its detailing for each module. Estimation prepared using FPA.

Database design and development, project architecture design, leading the team and task assignments.

Designed and developed data validation, load processes (ETL) using SSIS Package, reports using SSRS. Automated reports using subscription.

Implemented new T-SQL features in SQL Server 2005 like Partitioning, Advanced Error handling and Common Table Expression (CTE).

Developed medium to complex reports like detailed list report, summary reports, Matrix reports, drill through reports, Sub-reports, ad-hoc reports to meet various business needs using SSRS.

Identified and worked with parameters for parameterized reports in SSRS and used SSIS import/export wizard for performing the ETL operations.

Module Testing, client interaction and release management.

Integrated XML with DB and write\store procedure to save and retrieve data from DB and send to GUI (.net) application using XML

Optimized the performance of queries by removing unnecessary columns, eliminated redundant and inconsistent data, normalized tables, established Joins and created clustered/non-clustered indexes wherever necessary.

Project - Real Estate Agents, USA

Responsibilities:

Understanding system requirements Database Design, Project architecture design, leading team, work assignments.

Writing stored procedures and functions.

Project Leader @ GlobalTech (I) Pvt. Ltd., Ahmedabad, INDIA. [Feb 2005 – Jun 2006]

Project - Web order management service

Responsibilities:

Understanding system requirements, communication with client, Database Design, Project architecture design, leading team, work assignments.

Preparing prototype and SRS

Coding (Development), class and UI designs, testing, Web service Configuration.

Writing of stored procedures and complex TSQL queries to generate Crystal reports.

Project - IOMS (Integrated Online Management System

Responsibilities:

It was under maintenance & enhancement phase.

Understanding system requirements, communication with client, Database Design, leading team, work assignments, work with Web services.

Writing of Stored procedures, Functions and TSQL queries.

Coding (Development), class and Reports and UI designing, testing.

Giving training to system users.

Sr. Software Engineer @Arraycom, Gandhinagar, INDIA [Oct 2000 – Feb 2005]

Project - Monitoring & Control System for Delhi Doordarshan, Indian Meteorological Department (Weather Department), All India Radio, Delhi

Responsibilities:

Understanding client requirement, Database Design, Project architecture design, defining coding standards, Leading team, work assignments.

Created Stored procedures for the business logic.

Communication programming using RS-232, RS-485 Hardware (MOXA switches), COM+, Multi threading.

Coding, class and UI designs, testing

Project - School Information Management System (SIMS), AIS

Responsibilities:

Coding, Testing, implementation of Forms, class modules, TSQL, Stored procedures, Functions, views and Reports (using Crystal Report – 8 and office automation).

GUI Design, Development of Business Tier and Data Tier.

Writing User and Technical Manual.

Project - Vehicle Tracking (AVL) for Gandhinagar Police, Hyderabad Police (NPA), Sterling Hospital, L&T Hazira

Responsibilities:

Defining GUI and development standards.

GUI Design, Development of Communication modules and reports.

Writing COM components & COM DLL Library for general routines & validations.

Shape viewer (Map format) Developed in VB using windows API

Communication programming with VHF/GSM units using RS-232.

Data migration using DTS package.

Project - Inventory Management System for ArrayCom (I) Ltd.

Responsibilities:

Module programming, Transaction module, Data entry forms etc.

Created all master details, Order inquiry, Work Order, Purchase Indent, Purchase Order, QC, Goods receipt note, Material issue, Invoice etc.

Created VB classes and VB Modules, Reports and Statements of all transactions, Monthly & annual summary.

Created many stored procedures for DML operations.

Database backup – restore facility, User level security

Freelance Developer [Jan 2000 – Oct 2000]

Project - Hospital Management System for USA Based consultant

Project - Pay Roll System for Godhra Taluka Panchayat

Contact this candidate