How to build an efficient BI data warehouse

News

How to build an efficient BI data warehouse

Alan Joch, Contributor

Circor International Inc. has a history of looking outward to grow its markets. The Burlington, Mass.-based manufacturer of valves, tubing and other industrial products acquired more than a dozen companies over the last two decades to expand its markets and better serve its customers.

But now the midsize manufacturer’s focus is shifting inward. It’s building its first

Continue Reading This Article

Enjoy this article as well as all of our content, including E-Guides, news, tips and more.

business intelligence (BI) data warehouse, in part to glean insight into how to tie the individual entities together into a more efficient whole.

“The primary driving factor is how to understand all of those businesses so it can make the processes seamless and get them to be more effective,” said Vinay Balasubramanian, CEO of Mercury Software Consulting Inc. (Marlborough, Mass.), a management consulting and technology services company that’s helping to build Circor's data warehouse.

Unlike storehouses of production data, data warehouse technologies exist to provide a foundation for BI and analytical reports. Managers slice and dice data aggregated from production systems to spot emerging marketplace trends or potential production efficiencies.

For example, if multiple facilities across geographic regions buy some of the same raw materials, a data warehouse can help managers decide whether to consolidate sources or negotiate volume discounts.

Circor isn’t the only company to understand the importance of a data warehouses. “The data warehouse is essential for a modern enterprise,” said William McKnight, president of McKnight Consulting Group LLC, based in Plano, Texas.

Planning a BI data warehouse project

Although data warehouses are overcoming their reputation for being expensive and difficult to launch, they still require significant up-front planning and a keen eye for selecting the right hardware and software.

A critical first step is to define the overarching business goals the warehouse will address, Balasubramanian said. “Most companies make the mistake of buying the technology before they understand their business problem,” he added. “That usually comes back to bite them later.”

Similarly, organizations shouldn’t view warehouses solely as an IT project. A cross-functional team composed of technology and business leaders is often required to build a solution that meets the needs of business users.

Manufacturers can also use these definitions to build requests for proposals (RFPs) when they select from the many hardware and software choices in the marketplace. On the plus side, the plethora of products means a solution probably exists to meet any manufacturer’s needs. The downside is the time it takes to whittle all the alternatives down to a manageable list.

Consultants advise clients to avoid being drawn into features shootouts among vendors and focus instead on each one’s expertise in manufacturing and how well it serves small, medium-sized or large companies. Of course, cost also matters. “If you’re a $5 million manufacturing company, and it will take $10 million to build the data warehouse, the return on investment may not be appropriate,” Balasubramanian said. “However, if you spend a half a million dollars and the data warehouse saves $3 [million] or $4 million then the ROI is definitely justified.”

New data warehouse technologies

Data warehouse technology is evolving in ways that have the potential to lower costs and speed implementation. At the forefront are data warehouse appliances and cloud computing.

Appliances integrate the necessary hardware, software, operating systems and storage resources into one prefabricated package. Analysts say this level of integration can reduce a sizable portion of the project’s cost. Depending on the appliance, companies can also save money with open source software and databases that offer an alternative to pricey, name-brand products. But there’s a tradeoff: Because appliance vendors typically excel in only one or two technology areas, not all of an appliance’s components will be best in class.

Nevertheless, data warehouse appliances saw a growth spike last year, according to a recent report by technology research firm Gartner Inc. (Stamford, Conn.) As the market—and vendor marketing efforts—heat up, Gartner analysts are advising potential buyers to let customer references and proof-of-concept projects guide their buying decisions.

Some industry observers expect the warehouse-in-a-box trend will gain even more steam with cloud-based on-demand computing services and Software as a Service (SaaS) solutions. But for now, some manufacturers aren’t ready to make the leap. “We hear comments like, ‘We don’t want to be the guinea pig just yet,’” Balasubramanian said.

Building demand-driven forecasts

With a functioning data warehouse in place, manufacturers have an opportunity to home in on a particularly important analytical area: demand forecasting. Demand signal repositories (DSRs), a tool long used by large consumer products companies, rely on a central database to aggregate daily sales data and related information to help managers analyze the latest customer buying patterns, often at the detailed SKU level. Users report DSRs offer a number of benefits, including minimizing stock outages, improving sales forecasts and reducing inventory levels. New SaaS-based DSRs could pave the way for DSR adoption beyond the largest companies.

But Balasubramanian offers a final cautionary note. Demand data doesn’t always fit neatly into the rows and columns of the relational databases that underlie traditional data warehouses. “There is so much unstructured data associated with market-based demand, and it can be very difficult to seamlessly integrate it [with traditional databases],” he warned.

<< Tutorial home page

EDW business case tips >>