Case Studies

BI Solution Enhances Publisher's Sales and Marketing Analysis Capabilities

Project at a Glance
RDA developed a business intelligence solution for this publisher that provides insight into how their Sales team is performing and what marketing activities contribute most significantly to improved sales. The solution also provides a strong foundation for planned future scorecarding and data mining initiatives.

About Our Client

RDA's client is a leading health publisher whose magazines are published in over 40 countries. The company also publishes books on a variety of topics.

Background

Our client wanted to better understand how their marketing efforts impacted sales revenue. Gaining this understanding required 1) improved access to reporting across the organization, and 2) a way for business analysts to access and analyze data in a rapid and ad-hoc manner. RDA consultants were brought in to collaborate with client staff in building a business intelligence solution using the Microsoft SQL Server 2005 platform.

Solution Detail

The solution involved two main phases. Phase One was focused on Sales. SQL Server Integration Services was used to build an ETL (extract, transform and load) framework that extracts Sales data from the Oracle transactional databases and loads it into a dimensionally modeled data warehouse in SQL Server, including auditing of every load process. A significant number of Sales reports were built on this data warehouse and made available to the user community via Microsoft Office SharePoint Server. Additionally, Analysis Services was used to build and deploy Sales cubes to the Analyst community, who perform ad-hoc analysis using Excel 2007. The solution processes nearly 5 million records each day in under 90 minutes.

Phase Two was focused on Marketing, and specifically integrating the marketing data with the Sales data. Marketing activities are focused around email campaigns. For this process, the ETL framework was leveraged to extract data from four separate data feeds from an external partner company and then loaded into additional fact and dimension tables in the data warehouse. Reports were created and delivered via SharePoint, and two more cubes were built and deployed via Analysis Services, allowing significant and rapid slicing of Marketing campaign results. This solution currently processes nearly 3 million records each day in approximately 1 hour, and is expected to grow to approximately 10 million records per day.

Benefits

The complete solution now provides users with significant insight into how Sales is performing and what marketing activities contribute most significantly to improved sales. The solution also provides a strong foundation for planned future scorecarding and data mining initiatives.

Technically Speaking

The data warehouse was designed based on Kimball methodologies and populated using a very structured and modular ETL framework, which provides significant auditing of every load process for historical analysis.

One of the most significant challenges on this project was the volume of data that needed to be processed. RDA's client had already selected very robust 64-bit servers with significant disk and memory resources, but extracting, transforming, and loading all the data required some creative designs in the ETL process. Staging processes were used in order to minimize connection to source systems, chunking techniques were used in order to control the amount of data being processed at a given time, and the load processes were configured to be self-healing in the event of process interruption. These techniques, along with a variety of others, have proven successful in delivering a structured, maintainable, and portable ETL solution that operates very efficiently.

Below is a graphical representation of the overall solution that was implemented:

RDA designed and developed three cubes with about 20 dimensions surfacing Sales and Marketing data to end users who analyze it using Excel 2007. The users are trying to understand what marketing campaigns are most effective and with these cubes they can easily identify which campaigns produce the most sales as well as slice the campaign and sales data by any of the common dimensions. This analysis allows them to focus future campaigns on the elements that are most productive and that lead to increased sales.