We built MS Society of Canada a data warehouse.
Project
Data Warehouse
Industry
Non-Profit
Technology
SQL Server
BACKGROUND
The MS Society of Canada (www.mssociety.ca) is the only national voluntary organization in Canada that supports both MS research and services for people with MS and their families. Its mission is to be a leader in finding a cure for multiple sclerosis and enabling people affected by MS to enhance their quality of life.
The Challenge
With a membership of 28,000, MSSC needed to perform analysis and reporting across a number of different areas, such as campaign and donation data. The company’s existing reporting infrastructure was based on Crystal Reports and could only provide customized reporting views using custom reports. The slight variations among the reports needed by the system’s different users had resulted in a need to maintain over 400 reports on the same six key data points. When MSSC approached Whitecap about adding 40 new reports to their existing system, we quickly identified that a new approach to their reporting architecture was needed to minimize the need for expensive maintenance and custom programming.
The Solution
Whitecap initially proposed the development of a business intelligence system composed of MS SQL Server Analysis Services (i.e. Data Cubes), using SQL Server Integration Services to capture source data, and using SQL Server Reporting Services for reporting. However, when MSSC requested a more inexpensive solution, Whitecap was able to develop a data warehouse architecture using the SharePoint 2010 Business Intelligence Centre, providing a powerful and flexible environment with drill-down and custom view capabilities.
The Results
The resulting system provides a framework for the rapid analysis of reporting data without requiring intervention from IT staff for custom requirements. By building and implementing a data cube, it is no longer necessary to write a separate query for every required report; one report in the new framework accommodates up to a dozen of the previously existing Crystal Reports via the enablement of different views and drill-downs. A single SharePoint Business Intelligence dashboard was developed which effectively replaces between 300 and 400 of MSSC’s pre-existing reports, providing them with significant efficiency gains in their administrative practices.