SR-ED1:Standarad Integration Between Cognos Controller and Great

[background]
6306896 CANADA INC provides consulting services in the CPM(Corporate Performance Management) and Enterprise Resource Planning (ERP) area. The consultants of the company have been in the Business Intelligience for over 10 years.
In January 2006, the sales and development team decided that the market has the need of the standard integration between major ERP system and major CPM system. Currently the integration between these two systems are limited. Lots of resources are put into the work. The implementation in one project cannot be reused in another project.
After initial assessment, according to the complexity, resource arrangement and market position of different systems, the sales and development team decided to choose Microsoft's Great Plains as the major ERP financial system, and choose Cognos's Cognos Controller as the target Consolidation system, Informatica as the data transfer tool.
/>[/background]

[Detailed Project Description]
Project Name: Standard Integration Process Between Great Plains and Cognos Controller
Start Date: January 2006                    End Date: December 2006
 [Section a: Scientific or Technological Objectives]
The objective of the project was to develop a standard, reusable and customizable process that extract, transform the financial data which comes from Great Plain's financial system and load into the model of Cognos Controller which is an industry leading financial consolidation system.
The resultant process can be reused, customized in future to meet the increasing integration needs between Great Plains and Cognos Controller.
[/Detailed Project Description]

[Technological or Knowledge Base Level]
The researchers in 6306896 CANADA Inc have many years experience in consulting services and IT services in different business intelligience and corporate performance management projects. An important industry need is noticed by the market anaylsts and researchers: the business model of the major ERP financial system are stable, the business model of the major Corporate Perforance Management system are stable, while there are not enough mature and reusable integration solution between the two systems. Which means for different project, new investment has been made for the developemnet of integration from scratch.
According to the assessment, the company decided to choose Great Plains as the ERP financial system, Cognos Controller as the consolidation system, Informatica as the data transfer tool. The reason is:
1.Great Plains is Microsoft's ERP solution for medium,small business, it has thousands of customers in the market;
2.Cognos Controller is the leading Consolidation system in the market, and there are enough resource on it in the company;
3.Informatica is the No.1 Data transfer tool in the market and has the most business users, so very easy to be accepted by the potential users.
In order to accomplish the successful integration, the company had to resolve the following challenges:
1. Make the evaluation, research on Great Plains, to fully grasp the business model, make sure the compatibility from Great Plains to Cognos controller;
2.Make the evaluation, research to gather the major financial system configurtaion so that we can make the standard design and solution for these configurations;
3.Research, develop and implement the integration logic according to Cognos Controller's requirements.
The result product should meet the following requirements:
1.Set the standard Great Plains preparation procedure requirements for the integration to Cognos Controller;
2.Set the starndard Cognos Controller system setup procedure requirement;
3.Provide the easy-to-use integration process solution if users meet the requirements of 1 and 2.
4.Provide the flexibility for users to make customization.
[/Technological or Knowledge Base Level]

[Section C:Scientific or Technological Advancement]
Making integration between different systems is a complex work. Especially the source system is an ERP system, the complexity of ERP system itself and the coplexity of different business logics of clients is the main reason that there is not enough integration solutions in the market.
Most business users has strong knowledge and experience in their source ERP system, but they don't have enough resource,experience and skills on the consolidation module and data transfer module.
6306896 CANADA INC has enough resources and experience in Business Intelligience, Corporate Perforance and integration between ERP and CPM.
As a result of this work, 6306896 CANADA Inc added a new capability to their existing consulting services. This advancement in the integration between ERP and CPM enable them to produce the first of a range of such three-field integration product, allowing the creation of a new cross-industry solution.
[/Section C:Scientific or Technological Advancement]

[Section D: Description of Work in the Tax Years 2005 and 2006]
The company before had the consulting experience in implementing the datawarehouse solution for Great Plains. They also had the experience in implemenation the consolidation solution using Cognos Controller. Similarly they did a lot of data integration work using Cognos DecisionStream and Infomratica.
Up to now, the project has gone through six major phases:
Phase 1. Feb.2005 to April. 2005
Compatible research of GreatPlains financial data model and Cognos Controller data model
Description:
 (1.1) Performed the initial research and analysis of the general data importing secenarios of Cognos controller data transfer process. Setup the basic data transfer file format requirement;
 (1.2) Performed lots of research and testing on Great Plains to figure out the core data access logic to extract the necessary financial data from the fundation database of Great Plains.
Phase 2. April. 2005 to June. 2005
Designed, prototyped ,tested and implemented the ETL process for single company code from Great Plains to Cognos Controller
 (2.1) Setup the standard procedure, requirement and template of Account Mapping between Great Plains and Cognos Controller;
 (2.2) Designed the data model of Account mapping, ETL process to load and refresh the Account Mapping;
 (2.3) Designed and implemented the core data transfer process V1.0 using Informatica to load the financial data of a single company code into the flat file which meets the format requirments of Cognos controller.

Phase 3. June.2005 to Sep.2005 
Designed, prototyped, tested and implemented the controlling process which controls the overall loading management of multiple company codes from Great Plains to Cognos Controller.
 (3.1) Informatica has the limitation which doesn't support LOOP in the workflow management. We had to find a solution to support that feature because the data from multiple company codes must use different database connection and share the same data transfer logic. After the research and evalution, we chose an open source third party software which called ANT to be the external workflow controller.
 (3.2) Then we setup  the standard configuration file and overall controlling script.
 (3.3) Some code adjustments were done on the ETL code to support the controlling process.
Version 2.0 of ETL was completed and the data transfer of multiple Company Code was supported.

Phase 4. Sep.2005 to Feb.2006
Data quality test, data quality issue analysis, error handling procedure creation.
 (4.1) To ensure the data quality, lots of tests were performed and several major data quality issues were discovered;
 (4.2) After investigation and analysis, we grouped the major data issues into two groups: source account synchronization issues and account mapping issues.
 (4.3) Two error handling components were embedded into the ETL process to capture the data loading exception or trace the information about the errors in the mapping.

Phase 5. Feb.2006 to May.2006
Added the support for Account Range functionality
With the experience of data quality analysis and the communication with industry users, we found that it would be better to support multiple to one mapping instead of the existing one to one mapping relationship in the Account Mapping process, this would greatly simplified the maintainence work and avoid the data loading synchronization issues.
(5.1) With the research and analysis, we setup a standard and robust Account Mapping template and procedure;
(5.2) The existing Account Mapping refreshing process was modified to support the new Account Range feature;
(5.3) The ETL code was also adjusted to support the new feature.
With this feature implemented, the Version 3 of ETL process was created.

Phase 6. May.2006 to Sep 2006
Enhancement of the feature of front end support
(6.1) With the communication with industry users, we realized the need to provide some useful frontend reports. Based on the fact that we chose Cognos Controller as the consolidation tool, we chose the reporting tool of Cognos as the front end tool.
(6.2) We developed three major kinds of reports:
          Source accounts exception report;
          Account Mapping report;
          Trial Balance and account mapping report.

Phase 7. Sep 2006 to Dec 2006
Performance evaluation and optimization
(7.1) Acceptance tests were performed to ensure the overall process is correct and user friendly;
(7.2) Performance tests were performed and some performance tunning work was done.

[Supporting Information]
1.Description of the work of the employees;
2. Operational manual demo;
3. Great Plains database Architecture document;
4. ETL Architecture Document;
5. Phase testing results