Subrata Debnath
SKILLS AND ABILITIES:
·
OLAP Expert (Hyperion Essbase 5.X, 6.X and 7.X,
Hyperion Planning , Microsoft Analysis Services, Outlooksoft EAP, db2 OLAP and
Oracle 9i)
·
Certified Hyperion Essbase Professional.
·
Trained Outlooksoft Administrator.
·
Financial analysis and Project Management
Experience
·
Excellent track record of successful Business
Intelligence solutions.
·
Outstanding communication skills.
·
Solid technical and functional experience to bridge
both functional users and IT requirements successfully.
·
Over 7 years of designing and implementing
successful Business Intelligence Applications to some of the most demanding
companies in the world.
EXPERIENCE:
Subrata is an accomplished senior level information
technology professional in the Business Intelligence (BI) or Business
Performance Management (BPM) industry. He has an extensive track record of
success in applying BI/BPM solutions to improve business performance. He possesses
extensive experience with Hyperion, Outlooksoft, Microsoft Business Intelligence
products. Subrata is a Hyperion Essbase and Hyperion Planning expert and he has
been working with all facets of Essbase for over seven years providing
customers enormous success in developing successful business intelligence
solutions.
Consultant
� Analysis Express LLC,
09-03 � Current
Hyperion, Outlooksoft, SQL server, Excel VBA, asp.net
Phelps Dodge Corporation: Reviewed Hyperion
software applications (Essbase and Planning) and provided guidance on
short-term urgent solutions. A long term recommendation was also made aiming to
achieve higher level of system efficiency.
Info-link Corporation: Reviewed their
SQL server based house holding name grouping system and its limitations.
Investigated and then suggested SQL server based data-mining and SQL server
Analysis Services based solutions to take advantage of.
Lead one
person team to migrate Micro-strategy based reporting systems to MS Analysis
Services OLAP database and Proclarity reporting.
Thomson Learning: Prepared Outlooksoft
EAP reports for the A/P Direct Cash Flow system. These excel based reports
featured drill-down, detail/summary sheets, drop-down member selection,
write-back, and automation using excel VBA and Outlooksoft API. Assisted in
resolving structural issues with Accounts and Organization dimensions.
Programmed the data transformation and sourcing for the Outlooksoft system using
MS SQL server DTS packages. Assisted team members with technical direction and
financial systems knowledge.
Universal Studios:  Designed and
programmed asp.net 2.0 and SQL server 2005 based evaluation management solution
to replace their existing stand alone applications.
DM Federal Credit Union: Developed and implemented a Risk Based Lending system
at DM Federal Credit Union. Hyperion Essbase was used to create the analytical
solution while Access and custom VBA programming was developed to source
necessary data into the system. Web based Hyperion Analyzer reports were
developed for reporting month reports used by Finance Managers and Vice
Presidents. The system saved almost two weeks of work by all of the executives
each month after implementation.
General Motors: Made modifications to existing Powertrain Budget and
Forecast Essbase application to include Powertrain Europe, Latin America and
Designed and
developed Cost Driver Forecast system. Driver based forecasting is a paradigm
shift in manufacturing forecast process where instead of forecast dollars the
drivers are forecasted. Met with finance users numerous times to finalize the
requirements; then to draft a design of a database. Created calculation scripts
for people cost (salary and hourly) calculation; then, using the variation of
production forecast modeled the volume-mix calculation. Performed other system
related tasks, for example, user security filter development, VBA based
templates etc.
General Dynamics: Reviewed financial systems and provided enhance ideas.
Advised on resolving technical issues, provided technical training and
mentoring. Upgraded Essbase system to version 7.1.5 hosted in Solaris OS.
Abbott Labs: Designed and developed highly
customizable excel/VBA (with Hyperion API) based reporting system for CED
division of Abbott Labs.
Consultant
� Iasis Healthcare,
04-03 � 07-03
Upgraded Hyperion systems to HPUX
Upgraded previously
developed Hyperion based systems from Windows NT to HPUX operating system. This
includes Hyperion Essbase 6.51, Hyperion Analyzer 6.5 and Hyperion Essbase
Integration Server. Ported virtual cube based Analyzer solution to EIS to
provide hybrid analysis functionality to retrieve Oracle based transactional
detail.
Consultant
� Trimas Corporation,
04/03-
04-03
Outlooksoft
Inspected
Outlooksoft systems already implemented by some prior consultant. Reviewed
business process, input schedules and reports, SQL database, and Analysis
services database. Recommended corrections in system design and provided
optimization suggestions. Interfaced between business users and Outlooksoft
technical support group to help solve software errors (bugs).
Consultant
� Lear Corporation,
01/03-
04-03
Budget and Forecast -Optimization
This
short-term project involved Essbase optimization on database size, database
calculation time and data retrieval time in Forecasting database. An inspection
of current system and business requirement was performed. Provided suggestions
on structural change in the database and later implemented a solution which was
a database- 25% of its original size with 50 times improved calculation time,
and at least 150 times better retrieval time (without making any modifications
in their reporting system-Actuate).
Consultant
� MSX Corporation,
03/03-
03-03
Hyperion
Essbase
Optimized
and tuned calculation scripts for the General Ledger Essbase cube reducing the
calculation times by over 50%.
Consultant-
Spectrum Health,
12/02-01/03
Hyperion Planning- Budget and Forecast
The budgeting
and forecasting process for this not-for-profit healthcare company is composed
of P/L, FTE and Revenue based planning and wanted to use Hyperion Planning,
Hyperion Reports, and Hyperion analyzer to implement these modules in 3 month
time frame. Provided direction to fellow consultants and project team members
from Spectrum during the design phase in gathering business requirements.
During development phase, provided advice and solution on implementing Hyperion
Planning on as needed basis to overcome software limitations by finding
workarounds.
Consultant-
Daimler Chrysler Services,
3/02-12/02 Hyperion
Planning- Budget and Forecast
The scope of Forecast Redesign
Project at this financial services company involved information on portfolio
management, funding, general expenses and allocations (G&A), insurance and
sales using Hyperion Planning with Essbase. In the critical stage of this
project, provided technology solutions to keep the project on target.
Developed and solved allocation
problems for G&A application. Almost 300 different G&A allocations that
are driven by various drivers and allocated down to cost centers were researched
and simplified by breaking each allocation problem down to an allocation rule
which all of the allocations follows. As a result, this application was put
into production months before the due date.
For Funding application, solved
Debt and ABS (Asset Backed Securities) related complex expense calculation
problems for fixed vs. variable, short-term vs. long-term, various flavors of
each kind to calculate expenses between issue date and maturity date. Algebraic
formulas were invented to simplify calculations. An innovative looping
technique (using conventional Fix statement) was developed to solve time
dimensions related problems and made it possible to calculate all the complex
funding problems under 5 min. This technique was later used in other parts of
the database and even other applications to solve problems that involve
calculations over a period of time (months, year, etc).
Solved numerous calculation
problems for Portfolio databases and made optimization recommendations.
Solved currency conversion
problem that were used in all of the databases under forecast redesign
applications umbrella. The currency conversion solution was at least 1000 times
optimized compared to the solution bundled with Hyperion Planning (1.2) and was
recognized as a great achievement among all parties including Hyperion.   Developed and implemented data integration
strategy between databases among Forecast Redesign databases.
Redesigned a consolidation
database- Corporate Controlling, which contained financial information related
to Operative Plans (OP) and Estimated Actual (EA) of Balance sheet and income
statement items, portfolio, sales and penetration related figures, Debt and ABS
related figures etc., which are reported on a monthly basis to head office.
Data retrieval and reporting to
Consultant- Iasis Healthcare,
10/01-3/02
General Ledger Reporting
For Iasis Healthcare, designed,
developed and automated end-to-end GL reporting solution with Hyperion Essbase
and Hyperion Analyzer for over 300 employees. Created and customized multiple
web Analyzer views for Hospital CFOs and Directors to quickly access and
analyze Essbase based GL data. Added seamless assess to Lawson transactional
data with Hyperion Analyzer. This
reporting solution removed the use of static and manually created
Net Revenue Forecasting
Designed and developed a
forecasting model with Essbase and web Analyzer to realistically forecast net
revenue and other key measures including statistics consolidating approximately
five hundred thousand (additive each month) patient�s charge related
information together with actual and expected payments from various insurance
companies. The forecasting model loaded data from McKesson HBOC mainframe system and loaded
into Essbase to produce drillable reports crucial for Hospital CFO and
directors to identify and control expenses in order to maximize profit.
Automated monthly database building process along with maintaining users�
security to alleviate future maintenance.
Decision Support System
Developed a comprehensive patient level charge
information based Essbase Database which enabled spot deviances of patient
population by various patient related attributes i.e. diagnostics, severity,
age, location, admit type, source, visit duration, insurance contract. Analysis
on operational, outcome, clinical and encounter detail became possible which
were measured against company/industry specific benchmarks to derive various
management and financial performance. The data was sourced from McKesson PDS
(Pathways Decision Support System) utilizing complex load procedure in essbase.
The solution replaced DSS reporting of PDS system, disparate data marts created
from PDS or HBOC and tedious manipulation in numerous excel reports.ÂÂ
6/01-8/01
Customer Profitability
Provided OLAP and VBA programming directions and assistance to the
project team members to meet the target deadline in Customer Profitability
Model (CPM) Reporting project, which provided bank management, relationship
managers and the finance organization the ability to understand the depth of
each customer�s relationship with the bank and the profitability of that
relationship. The solution was architect in Essbase 6.0 which captured the functionality and data used by existing Customer
Profitability Model (CPM) in Oracle. An excel application, CPM Forecasting
Application (CFA), was created to calculate what-if scenarios of profitability
relationship.
2/01-6/01
Expense and Planning
Lead reports design and
development responsibility for Expense and Planning Essbase project For PPD
(Pharmaceutical Products Division) of Abbott Labs.
The design and prototype
was created with a group of reports to analyze expenses of PPD Sales Force in
various regions using Excel VBA and Essbase Excel Add-in API, which will bring
the ease of creating reports to Essbase users. These reports are highly
customizable by the end user, which requires no VBA programming knowledge.
Consultant- General
Motors,
10/00-2/01
Budget and Forecast
Core Budget and Forecast (CBF) was the first major module of the
Business Planning Capability to be
implemented as part of the overall global deployment of the Decision
Support Workbench (DSW) for
General Motors Finance. It is based on input received from
representatives of each region through a
formal review and buy in process, review of previous GM Finance DSW
projects including Hyperion
Pillar applications, as well as input from the Investigation team. The
scope included standard financial
budget and forecast consolidations, historical data store, multiple
views of data, value-add analysis, ad
hoc analysis, operational and financial metrics, web delivery and
automated data feeds from source
systems via the Financial Data Repository (FDR).
Preformed the role of key developer for Core Budget and Forecasting
(CBF) system for the operations of
General Motors in Latin America (LAAM), Asia Pacific (AP), Power Train
(PT),
(GMNA), and
·
Programming in
VBA in Excel�97 with Essbase Add-in to solve input/output related user
interface
development. With the design and development of 3-tier code
layers, the enterprise class Excel templates maintained standards within its
class of templates and could accommodate any future development of such
application.
·
Used complex
load rules, calc script and report scripts to architect ETL functionality and
data
integrity in Essbase to get the data from legacy system and/or to report
data from the Essbase
system.
Performed various unit and system testing to facilitate User Acceptance Testing
(UAT).
Consultant - Detroit Diesel Company,
7/00-8/00
Activity Based Management
For the engineering support group in Detroit Diesel Company, a premier
diesel engine manufacturer, Essbase databases were created using Lawson
Information Architect to solve Activity
Based reporting problems.
Consultant - Hub
Group, Lombard, IL
4/00-6/00
Logistics Analysis
Designed a front-end application in Wired for OLAP Virtual
Cube for a logistics company, which enabled them to view and report real-time
tracking of freight cars, carrier performance, and cost-savings generated.
Consultant- Ohio
Casualty Group,
6/99-2/00
Decision
Support System
Developed
and deployed a Corporate Portal to Decision Support System (DSS) for management
reporting
purposes. The DSS management reporting included most measures needed for
managing the
company
and consists of a set of standard reports, planning tools and plan monitoring
reports. The
standard
set of reports included financial, process, resource and customer based
reporting in order to
collect,
transform, and deliver relevant information for analysis and strategic
decision-making.
DB2OLAP
was used to host multi-dimensional databases, while the presentation was in
Hyperion
Wired for
OLAP. Responsibilities included user interface development, OLAP database
design, data sourcing and cleaning, database calculation using member formulas
and calc script, and database tuning for improved performance.
Developed stand-alone corporate Balanced Scorecard and planning
application to track and plan measures/goals of four broad categories of
management reporting- financial, process, resource and customer using IBM DB2
OLAP, Visual Basic, ESSBase Objects and ESSBase API in Windows NT environment.
Senior Programmer. Universal Software Solutions.
9/97- 6/99
Worked with Windows NT and SQL server, Borland's Interbase database,
Access database to provide solutions to various businesses in a rapid
development cycle. The business clients
includes FMAC (
·
Performed numerous data conversion among these
databases.
·
VBA programming to customize Saleslogix- contact
management software. Designed numerous IQ reports to report the data from
SalesLogix.
·
VBA to develop custom Access applications.
·
C and VB respectively to support Electronic Claims
Processors (eClaims) of MedAssist and Medicat. Reporting tools used are
PRODUCT EXPERTISE:
OLAP �
Hyperion Essbase, Hyperion Planning, Hyperion Application Link, Hyperion Business
Rules, Hyperion Reports, DB2OLAP, Hyperion Analyzer, SQL Server Analysis
Services, Outlooksoft EAP, Oracle 9i.
Data Mining -Intelligent Miner for Data
(IBM)
Languages-Microsoft Visual Basic, Essbase
Objects, Essbase Excel Add-in, Visual Age for Java, XML, SQL, MDX.
Spreadsheets-Excel
Operating
Systems-Windows, Unix
EDUCATION:
BS � Computer Science,
CERTIFICATION:
Certified Hyperion Essbase Professional. 2000.
Certified DB2 Application Developer. 2000.
TRAINING:
Intelligent Miner training from IBM Global Learning Services. 1999.
SQL Server Analysis Services training from
Outlooksoft EAP Administrator training from Outlooksoft. 2002.