Clearwater Seafoods Speeds up Data Warehouse Development 17 Apr ’08
Thanks to Clearwater Seafoods for providing a case study on how WhereScape RED sped up their development. We really appreciate when our customers are prepared to talk about how WhereScape RED has assisted them.
WhereScape RED Enables Clearwater Seafoods To Adopt a Self-Service Reporting Model
RED Slashes Time in Building Data Warehouse Prototype by 30 Percent.
From its humble beginnings in 1976 when founders John Risley and Colin MacDonald, with one pickup truck, operated their company as a small lobster distributor in Halifax, Canada, Clearwater Seafoods has grown to become a premier supplier of premium shellfish and sea food globally. Today, with a large fleet of freezer processor vessels and several processing plants throughout eastern Canada, Clearwater Seafoods has delivered 70 million pounds of premium seafood –including clams, scallops, shrimp, lobster, crab and ground fish –to 30 countries.
As part of its corporate structure, Clearwater operates separate companies, located in Canada, the U.S., U.K., Belgium, and Argentina, to sell and distribute their seafood around the world. These distinctive operations, however, were adding to the complexity and accuracy of corporate-wide reporting. Each business unit was reporting differently through a variety of methods that included spreadsheets and manual input of data.
Since Clearwater sells their products worldwide, business analysis reports such as profit margins, inventory, and sales are critical to their business. As a global company, fuel and other costs as well as foreign exchange rates affect how the company evaluates its profit margins, according to Tony Scallon, business systems analyst at Clearwater Seafoods, As a company selling internationally, Clearwater needs to know the true cost and profitability of what they are selling by region. For instance, if Clearwater sold a particular seafood for $5 a pound in the West Coast of the U.S., are they making money for that region once fuel costs, exchange rates, harvesting and production costs are factored in. With each company using different reporting tools and formats, this type of business analysis reporting would be time consuming and challenging. With its IT staff of 21 employees, Clearwater Seafoods had to dedicate two IT professionals full time to running business analysis reports for managers throughout the company. Depending on the complexity of the reports, it would take several hours to numerous days to complete reports.
To improve its reporting capabilities, the decision was made to centralize all of its data into one data warehouse. Clearwater uses Great Plains as its core ERP system that includes financials, sales and inventory management with Siebel CRM as the front end system used for order entry, and MS SQL Server as the RDBMS.
‘We selected WhereScape RED to manage our entire data warehouse,” said Scallon. With RED, Clearwater Seafoods can now manage all their schema DDL (data definition language), into one meta data repository which is fully documented.
“RED automated the time consuming process of writing SQL scripts and documentation and slashed the time to do this by more than 20 percent. If you are manually writing SQL scripts, you also are susceptible to numerous errors. WhereScape RED not only automated this process but it provided the documentation where you could compare the data to assure accuracy.” RED also has a fully integrated scheduler that performs ongoing loads and logs all activities during processing.
From an IT perspective, WhereScape RED allows Clearwater to develop data transformations when needed and to automatically generate the documentation that shows all the data lineage based on the data that is loading into the warehouse. These transformations allow the application of business rules to be applied to make a company’s data usable. For instance, Clearwater may want to run a report on the total weight, in tons, of how much fish they distributed. However, the data they have been capturing was only based on the number of boxes packed.
With WhereScape RED, a prototype of the sales data mart, with analysis cube and reporting services, was built within 30 days. Scallon projected the prototype would take at least 90 days and was “very impressed” how quickly it took to prototype. “WhereScape Red proved to be very scalable and an all in one tool that we needed in building our data warehouse,” he said.
With RED, data rates were proving faster than ever since Clearwater was able to build multi-dimensional cubes by leveraging Microsoft Analysis Cubes. Once its data warehouse is in full production, scheduled for April 2008, Clearwater expects maintenance on the data warehouse to be easy and fast with RED since many of the mundane maintenance processes are all automated.
Another key benefit that may not be as tangible to measure is the peace of mind that RED provides, says Scallon. “I no longer have to validate the data and worry about the accuracy of the scripts. RED automates all that and gives me the time to focus on more strategic IT initiatives that will impact the company for the future.”
The Business Problem:
• Business required more sophisticated business analysis reporting
• Separate business units within Clearwater Seafoods were all reporting differently
• Reports took several hours to several days to prepare
• Remove the need for IT department to produce reports and make it into a self-service model
The WhereScape Solution:
• A prototype was built with RED within 30 days compared to the projected 90 days estimated by Clearwater’s consultants
• Reporting is now a self-service model with users now specifying and running their own reports
Return on Investments:
• Faster and Accurate reporting across its separate business units operating throughout North America and Europe
• Able to run more sophisticated analytics and “what if” business scenarios
• More productive use of IT staff with two IT professionals, previously running reports, now reassigned to more strategic IT projects
