Multi-billion Dollar Integration
Summary
Increase Alpha was engaged to design a cost effective solution that enabled consolidated reporting in the budgeting and forecasting cycle for one-half of the enterprise. The solution design:
Customer Profile
The client is a leading information services company with over 12 billion dollars in annual revenue. After a major acquisition, the organization struggled to integrate financial processes and management structures. Among the challenges was the integration of the financial budgeting and forecasting processes between disparate legacy structures and reporting solutions.
Problem
The client approached Increase Alpha to resolve inefficiencies in reporting methods within their corporate planning process. Their quarterly and annual budgeting and forecasting cycles utilized multiple IBM Cognos Planning applications that did not smoothly integrate to allow for a seamless reporting process. Expense and revenue data resided in distinct applications, and the revenue data was further subdivided into numerous additional applications that reflected the differences in business structures among business units. Each application possessed a different level of granularity, and due to technical limitations in IBM Cognos Planning, the applications could not be integrated. This posed tremendous challenges in generating reports that combined both expense and revenue data, such as balance sheets, which were critical to the corporate planning process. To enhance organizational effectiveness and productivity, the client sought a unified solution that would enable the delivery consolidated financial information during budgeting and forecasting cycles without causing disruption to existing business processes.
Engagement Objectives
Increase Alpha was engaged to identify and design an optimal technology platform to streamline the budgeting and forecasting reporting process for the client. Having successfully executed a number of enterprise planning and data warehousing solutions for client, Increase Alpha enjoyed a proven track record for the rapid design and implementation of robust applications that aligned with organizational needs.
Requirements Identification
The client turned to Increase Alpha as a trusted advisor for full technology selection and solution design services. As effectiveness in the budgeting and forecasting cycles hinged on seamless reporting capabilities, the client was strongly aware of the need for a solution. However, overall requirements from the client side were vague. Moreover, precise reporting needs and requirements were still being defined and would not be solidified until just before the final delivery. Thus, it was essential to remain agile in our process and ensure solution flexibility as we rapidly constructed our design. Leveraging our prior experience in working with the client as well as the results of our assessments, we identified the following primary high-level objectives and goals for the solution in alignment with client business needs:
Solution Approach
Increase Alpha employed its deep technical expertise in evaluating potential solutions. Numerous possibilities were identified and thoroughly explored, including:
Solution Design
Based on the established budgeting and forecasting applications, the Increase Alpha designed a star schema data model and ETL architecture as well as a business intelligence application that would enable end user access to the new data model. Parity was needed between the two systems to prove data integrity to the business community, and, thus, we ensured consistency between the view of the data in the BI application and the view in the source budgeting and forecasting applications.
The budgeting and forecasting applications were implemented using IBM Cognos Planning 8.4. The chosen reporting and analysis solution was Microsoft Excel 2003 on Microsoft Analysis Services 2008 cubes. The business community was using Excel 2003 due to technical limitations of other reporting software installed on their systems. Moreover, the client already had licenses for Infomatica PowerCenter 8.6.1 and a running environment on UNIX platform. The source data was in SQL Server 2005 in one state, while the data mart was stored in an Oracle 10g database across the country. The risk of poor performance over the WAN was identified, but once proper firewall access was implemented, only minimal network issues were observed.
The Increase Alpha project team consisted of the Cognos Planning developers who held responsibility for developing the client’s budgeting and forecasting applications (a separate engagement), a data warehouse architect, a business intelligence specialist and a project manager. The Cognos Planning developers provided necessary information about the source applications and underlying business logic, while the data warehouse architect designed the data model in coordination with the business intelligence specialist. The project manager coordinated team activities and communication to ensure the rapid delivery of a high quality design. The client then took ownership of the implementation, using Informatica ETL to develop and implement the solution in-house in accordance with our detailed designs.
Results and Benefits
The data mart solution with an associated Microsoft Analysis Services business intelligence cube enabled the client to combine expense and revenue information in the same report, and to slice and dice that information as necessary – a function they could not previously achieve. Empowered by this new functionality, the client is able to rapidly answer both expected business questions as well as unanticipated ones.
Lessons learned
Using a data mart to integrate data from similar or related budgeting and forecasting applications provides a framework for robust and flexible reporting to meet both known and unknown requirements. A strong relationship between Cognos Planning developers, data architects, and business intelligence professionals can yield greater overall customer satisfaction.
Increase Alpha was engaged to design a cost effective solution that enabled consolidated reporting in the budgeting and forecasting cycle for one-half of the enterprise. The solution design:
- Empowered the generation of consolidated reports, integrating financial information and improving productivity and accuracy in the planning and forecasting cycle
- Allowed for a consistent view of data across the organization
- Aligned with existing business processes, causing minimal process disruption
- Incorporated data mart ETL technology with a Microsoft SQL Server Analysis Services cube
- Integrated with existing end-user applications, including Microsoft Office
- Though it constituted a relatively small solution for the budgeting and forecasting process, it offered immense productivity and business benefits while requiring minimal time and resources
Customer Profile
The client is a leading information services company with over 12 billion dollars in annual revenue. After a major acquisition, the organization struggled to integrate financial processes and management structures. Among the challenges was the integration of the financial budgeting and forecasting processes between disparate legacy structures and reporting solutions.
Problem
The client approached Increase Alpha to resolve inefficiencies in reporting methods within their corporate planning process. Their quarterly and annual budgeting and forecasting cycles utilized multiple IBM Cognos Planning applications that did not smoothly integrate to allow for a seamless reporting process. Expense and revenue data resided in distinct applications, and the revenue data was further subdivided into numerous additional applications that reflected the differences in business structures among business units. Each application possessed a different level of granularity, and due to technical limitations in IBM Cognos Planning, the applications could not be integrated. This posed tremendous challenges in generating reports that combined both expense and revenue data, such as balance sheets, which were critical to the corporate planning process. To enhance organizational effectiveness and productivity, the client sought a unified solution that would enable the delivery consolidated financial information during budgeting and forecasting cycles without causing disruption to existing business processes.
Engagement Objectives
Increase Alpha was engaged to identify and design an optimal technology platform to streamline the budgeting and forecasting reporting process for the client. Having successfully executed a number of enterprise planning and data warehousing solutions for client, Increase Alpha enjoyed a proven track record for the rapid design and implementation of robust applications that aligned with organizational needs.
Requirements Identification
The client turned to Increase Alpha as a trusted advisor for full technology selection and solution design services. As effectiveness in the budgeting and forecasting cycles hinged on seamless reporting capabilities, the client was strongly aware of the need for a solution. However, overall requirements from the client side were vague. Moreover, precise reporting needs and requirements were still being defined and would not be solidified until just before the final delivery. Thus, it was essential to remain agile in our process and ensure solution flexibility as we rapidly constructed our design. Leveraging our prior experience in working with the client as well as the results of our assessments, we identified the following primary high-level objectives and goals for the solution in alignment with client business needs:
- Seamless User Experience: The desired solution would address the challenges posed by the disparate applications and allow for a seamless end user experience in generating and delivering reports for the forecasting and budgeting process.
- High Flexibility: As reporting requirements had not yet been fully vetted by the client, it was deemed critical that the solution be highly flexible and able to adapt as new requirements surfaced.
- Minimal Disruption: It was essential that the solution reflect existing business and reporting processes to maximize productivity and minimize the need for change management.
- Existing Technologies: To the greatest extent possible, the solution should employ existing technologies already used and owned by the client.
- Rapid Delivery: A narrow timeline for solution delivery was established between the finalization of reporting requirements and the launch of the next quarterly forecast cycle.
Solution Approach
Increase Alpha employed its deep technical expertise in evaluating potential solutions. Numerous possibilities were identified and thoroughly explored, including:
- Refactoring Existing Applications: This approach would be subject to many of the same technical limitations faced by the existing applications. Successful refactoring would require the redesign of underlying business processes, which did not align with project objectives and would not be feasible within the allotted timeframe for delivery.
- Building a Custom Business Intelligence Solution: A BI solution would facilitate consolidated reporting, but would not possess the high flexibility required as new reporting requirements surfaced. Furthermore, it was not clear that acceptable real-time performance could be achieved with a pure business intelligence solution.
- Constructing a Data Mart: A data mart with a star schema design using high-performance ETL would provide custom data structures for reporting. Any and all available business intelligence tools could use the data mart, which would enable very flexible reporting or multidimensional analysis. Moreover, the solution would simultaneously allow for the inclusion of optimal data structures for both the enterprise planning applications and the business intelligence solutions.
Solution Design
Based on the established budgeting and forecasting applications, the Increase Alpha designed a star schema data model and ETL architecture as well as a business intelligence application that would enable end user access to the new data model. Parity was needed between the two systems to prove data integrity to the business community, and, thus, we ensured consistency between the view of the data in the BI application and the view in the source budgeting and forecasting applications.
The budgeting and forecasting applications were implemented using IBM Cognos Planning 8.4. The chosen reporting and analysis solution was Microsoft Excel 2003 on Microsoft Analysis Services 2008 cubes. The business community was using Excel 2003 due to technical limitations of other reporting software installed on their systems. Moreover, the client already had licenses for Infomatica PowerCenter 8.6.1 and a running environment on UNIX platform. The source data was in SQL Server 2005 in one state, while the data mart was stored in an Oracle 10g database across the country. The risk of poor performance over the WAN was identified, but once proper firewall access was implemented, only minimal network issues were observed.
The Increase Alpha project team consisted of the Cognos Planning developers who held responsibility for developing the client’s budgeting and forecasting applications (a separate engagement), a data warehouse architect, a business intelligence specialist and a project manager. The Cognos Planning developers provided necessary information about the source applications and underlying business logic, while the data warehouse architect designed the data model in coordination with the business intelligence specialist. The project manager coordinated team activities and communication to ensure the rapid delivery of a high quality design. The client then took ownership of the implementation, using Informatica ETL to develop and implement the solution in-house in accordance with our detailed designs.
Results and Benefits
The data mart solution with an associated Microsoft Analysis Services business intelligence cube enabled the client to combine expense and revenue information in the same report, and to slice and dice that information as necessary – a function they could not previously achieve. Empowered by this new functionality, the client is able to rapidly answer both expected business questions as well as unanticipated ones.
Lessons learned
Using a data mart to integrate data from similar or related budgeting and forecasting applications provides a framework for robust and flexible reporting to meet both known and unknown requirements. A strong relationship between Cognos Planning developers, data architects, and business intelligence professionals can yield greater overall customer satisfaction.