Data Warehouse Projects

Resonate offers a world-class team of industry professionals with the full range of data warehouse construction and utilization skills. Members of our community have been building and supporting data warehouses since the late 1990’s.

Organizations typically collect large amounts of line-of-business and administrative data. Most of the time, this data is tucked away in different formats on different platforms. Staff and management find it extremely challenging to share and utilize these isolated "islands" of data effectively.

Integrated data warehouses build bridges between organizational data islands. They make it possible for staff to use tools to access and investigate data, discovering meaningful, comprehensive information that they can use to support decision-making.

Some of the data warehouses Resonate has most recently designed and implemented are:

Healthideas Data Warehouse - BC Ministry of Health Services

Healthideas logo wht bg

2005 - 2009

Overview

Healthideas is the Ministry's Oracle-based health information data warehouse that enables evidence-based planning and decision-making, meeting the Ministry’s reporting and analysis needs.

Healthideas provides a decision support environment where answers can be found to evaluate program outcomes and provide planning advice, grounded in context and provided with an acceptable level of confidence.

Resonate provided the experienced team that developed this multi-terabyte, state-of-the-art data warehouse.

Detail

The Resonate development team used the Ministry’s Enterprise Data Model as the foundation, reverse-engineering source system data models into Oracle Designer and synchronizing them into the warehouse infrastructure.

The team took advantage of experience gained in previous large data warehouse projects to simplify warehouse schemas, developing more efficient data structures for event data loads. Mapping specifications were defined directly in metadata and used to provide robust development specifications.  ETL code was written and registered with the infrastructure as data load processes.  OLAP definitions were developed and generated, then deployed as Oracle relational objects. The very large volume of data involved required infrastructure modifications to fully utilize Oracle features like partitioning, set based, parallel processing, and Oracle Scheduler. Most of the source systems are providing data in the form of flat files so common methods to dealing with receiving, validating, and loading files using Oracle external table features was developed.

  • Healthideas was developed using the Oracle toolset, specifically Oracle 10g server RAC, Oracle Designer 6i, Oracle Developer, PL/SQL, Oracle Application Server, Oracle Discovery, Oracle Application Express and Oracle XML Publisher.
  • Oracle Designer is used for managing change and versioning of source system definitions, data warehouse integration models, and release management of custom code.
  • Healthideas on Map is used to provide a spatial perspective
  • Subversion is used for release management of non-Oracle code
  • Microsoft SharePoint is used to manage change requests
  • Mercury Interactive TestDirector is used to manage user acceptance testing and issue tracking

Healthideas was implemented using an iterative approach. The first major production delivery was a summary report set followed by a set of information products based on flat file source datasets.

All ETL and OLAP operations were successfully transitioned to Ministry staff and Resonate is now providing  second tier support to the operations team.

Education Data Warehouse - BC Ministries of Education and Advanced Education

2001 - 2009

Overview

The Education Data Warehouse gathers information from a variety of source systems, providing a consistent set of information to Ministry staff and education stakeholders to support BC’s education system.

Staff can respond to information requests and produce reports to assist with student monitoring, support system administration and governance and provide information to the public.

Members of the Resonate community developed the Education Data Warehouse using the Oracle toolset, specifically Oracle 10g, Oracle Developer, Oracle Designer and PL/SQL.

Detail

The Resonate development team used the Ministry’s Enterprise Data Model as the foundation, reverse-engineering source system data models into Oracle Designer and synchronizing them into the warehouse infrastructure.

Mapping specifications were developed, ETL code was written and registered with the infrastructure as data load processes and OLAP definitions were developed and generated, then deployed as Oracle relational objects.

The Education Data Warehouse was implemented using an iterative approach, with the first major production delivery using Oracle Reports on an Oracle 9i server.  Subsequent releases added new source systems and new information product releases.

The Education Data Warehouse is currently implemented on Oracle 10g server and operational support has been successfully transitioned to the Ministry’s operations team.

Vista Data Warehouse - BC Vital Statistics Agency

2000 - 2001

Overview

Vista is the vital event statistics data warehouse / decision support system that the Agency uses to derive medical, social and demographic information products from vital event data captured during the registration of vital events in British Columbia.

Vista produces business intelligence information products from time-sensitive event data and external data from a variety of sources on a variety of platforms.

Resonateprovided the senior team that conceptualized, developed and implemented the VISTA warehouse.

Detail

Vista is designed to support local health related organizations and various levels of government by providing the information products that support research; health planning and administration; health program development; epidemiological monitoring; health service delivery; social trend analysis; and policy development.

Oracle Designer was used for the enterprise model definition and synchronized with the metadata repository. Oracle Express was used for delivering the business intelligence information products used by internal and external consumers. Other Oracle tools used for this data warehouse were Oracle Forms and Oracle Reports.

All development and operational tasks were transitioned successfully to Ministry staff with Resonate consultants facilitating the smooth transition by providing quality documentation and training that succeeded in motivating the Ministry staff.