Cross Functional Data Warehouse for a Canadian CPG Company

This data warehouse, started in 2004 and built using Kalido on Oracle with Informatica and Cognos, has grown into a complex, cross functional solution comprised of more than 100 dimensions including close to 400 master data objects and 300 fact tables. Data subject areas include finance, sales, supply chain, operations and human resources. In this project, we assumed the roles of Primary Data Architect and Modeler as part of the initial build and through the subsequent phases.

See case studies related to this project:

  • Labatt Brews Up a Data Warehouse (Intelligent Enterprise, October 26, 2005)
  • Kalido Customers (Refer to Labatt Breweries of Canada)

Cross Functional MDM for a Canadian CPG Company

The design and build of this Kalido MDM solution started in 2006, with an aim to integrate employee, customer and supplier information to support a call centre system. Since then, the solution has grown to become a master data hub responsible for both authoring master data and acting as an integration point for all master data shared by two or more systems. Presently the solution is made up of over 400 categories (logical tables) holding more than two million subjects (logical master data rows). Master data is sourced from, and provided to, multiple systems including two separate SAPs, CRM, Planning and BI systems. The solution was also used to ease the migration from a legacy HR system to SAP HR and later the migration from Oracle ERP to SAP.

As with the related data warehouse implementation, our team took on the roles of Primary Data Architect, being responsible for all modeling and design, including the specifications for ETL into the systems. Also included was a publication layer that automatically made master data available through a replicated environment to support legacy applications.


MDM with a Focus on Authoring Data

This Kalido MDM solution started with a focus of authoring data for SAP. It has expanded into a solution that also includes the management of financial hierarchies for analysis and reporting, and integrating master data from independent SAP implementations. Our team has been involved in the design and build of this solution, and expects to continue in this role as future extensions are completed.


Data Warehouse to Support Global and Western Europe BI

Taking on the role of Primary Architect and Modeler, our team has worked with a number of integrators over the last 4 years to build and extend this data warehouse solution based on Kalido DIW on Oracle, with Informatica and Cognos. The solution includes global data for a large CPG company, as well as focused detail for their Western Europe zone to support detailed analysis. This warehouse is being migrated into the Global Federated solution referenced above. Portions will become part of the new warehouse directly while others require changes to conform to the new global model.


Asia / Pacific BI

This solution, based on Kalido DIW and MDM, integrates financial data from 5 separate ERP solutions to provide consistent and conformed reporting. The MDM is used to support integration of master data from the different sources as well as to manage hierarchies required for BI. Informatica is used for ETL and Business Objects for reporting.

A separate system integrator took responsibility for the actual build working from Shanghai, but we defined the high level models and laid the foundation for the architecture and approach. They were also actively involved throughout the project to assist in design and ensure standards were adhered to at every phase of the project.


Operational and Supply Chain Planning

This solution involved the architecture and design of an Oracle based system, integrating data from Oracle ERP and the data warehouse, to interface with Manugistics to support sales forecasting, supply chain and production planning. The system included the management of complex master / reference data to support translations for Manugistics as well as managing the rules driving the sourcing and production planning.


Custom Built Sales Data Warehouse

This application, built on Oracle, using oracle design and development tools, was designed to integrate sales and pricing data from over 50 sources for a Canadian CPG company making it available for analysis and reporting.


Custom Built Sales Forecasting Systems

These applications were designed to support forecasting and sales analysis for a Canadian CPG company. The resulting forecast was used both for target setting and demand planning. Federation of multidimensional cubes was in the core of the family of analytical applications designed for multi-facet sales analysis.


Forecasting, Sourcing and Scheduling System

We were responsible for the analysis, design, and build, using oracle design and development tools, of a national system to collect sales, distribution, and production data from multiple sources, and organize it for sales forecasting, replenishment planning, and production scheduling. Actual forecasting and planning was completed outside of the database using a number of analytical applications. The Oracle database and application was designed to support measurement and analysis, including functions to assist in supply chain management.


Banking, Insurance and Financial Compliance

The challenges with Financial Services companies go beyond the normal realm of business intelligence. In addition to meeting key strategic and operational business objectives, MDM must be able to support compliance requirements.
In recent years, with Basel II and III, the coming and going of SOX, banks have had to deal with frequently changing regulatory environments. It is not uncommon that within such large organizations, multiple and often inconsistent versions of the same master data exist in different parts of its operations Another challenge for such global enterprises is the span and movement of customers between geographical areas. In addition, as with other enterprises, the quality of data, consistent classification and identification of data, and data-reconciliation issues are common. Mergers and Acquisitions can also cause significant stress on master data integration and reconciliation.

We have been involved in two projects focusing on these challenges.

Firstly, a project in the banking industry involved the consolidation and migration of customers in the Agricultural and SME lines of business. It also provided hierarchy management to facilitate calculation of exposures (PD, LGD) in the retail and commercial data warehouses.

A second project in the insurance industry focused on the design and delivery of hierarchies, to be used in business intelligence reporting related to brokerage network reporting to regional offices. A further project involved the development of hierarchies to facilitate actuarial analysis for rate submission, and catastrophic loss reporting.


Below are some of the customers we have worked with. References are available on request.

Anheuser-Busch InBev
Abbott Laboratories
Labatt Breweries of Canada
Canadian Imperial Bank of Commerce
ING
Insurance Bureau of Canada
Cinergy
EMC2