Subrata@AnalysisExpress.com
513-942- 7752 x111

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, Cincinnati, OH.

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 Asia. Performed necessary changes in outline, calculation scripts, VBA based template. The responsibility of finance user interview and requirement gathering was included. While making the enhancements, much needed cleanups of outlines and calculation scripts were done. Devised a superior way (than conventional popular solutions) of translating currency within native Essbase system that will use least amount of space and time.

 

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, Nashville, TN

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, Bloomfield Hills, MI

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, Southfield, MI

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, Southfield, MI

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, Grand Rapids, MI.

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, Farmington Hills, MI.

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 Berlin were simplified with an Excel based flexible reporting system that will solve their dilemma of change in the Berlin specified reporting format.

 

Consultant- Iasis Healthcare, Nashville, TN.

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 Crystal reports.  The solution provided ways to analyze key statistical ratios quickly to improve decision-making.  The solution was a tremendous success and led to additional projects. 

 

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. 

 
Consultant- ABN Amro, Chicago, IL

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.

 

Consultant- Abbott Laboratories, Waukegan, IL

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, Detroit, MI

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), North America

(GMNA), and Europe (GME).

·         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, Detroit, MI

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, Cincinnati, OH

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 (Dallas, TX),Chickasaw Technology products (OKC, OK), Mega Manufacturing ( Hutchinson, KS), Hogan Information Services (OKC, OK).

·         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 Crystal reports, Access reports.

 

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, University of Central Oklahoma, Edmond-Oklahoma. 1997.

 

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 IMG University. 2001.

Outlooksoft EAP Administrator training from Outlooksoft. 2002.