IT Challenge: Creating a manufacturing data warehouse

In this IT Challenge, we'll find out how to build an efficient data warehouse for a manufacturing environment.

The IT Challenge of the Month for June 2011 is:

My organization is in the process of building a data warehouse. We are having some difficulty in deciding what sort of data – and what steps in the manufacturing process – should be included in this warehouse. Are there special considerations for building a data warehouse in a manufacturing environment? What departments should have access to this data to make manufacturing data reporting efficient?

Do you have a solution to this challenge? Have you encountered a similar issue at your business? If so, please contact the editors and share your suggestions or experiences. 

IT Challenge respondents will receive a free copy of a new book from SAP Press.

And be sure to check back here all this month -- we'll be posting solutions from experts and readers as we receive them.

From industry expert Steve Phillips:

One of the most common mistakes manufacturers make while setting up a data warehouse is attempting to synchronize the “dynamic” information that is stored in the manufacturing system for operational purposes. Dynamic information constantly changes; some examples are current inventory on-hand at the part number level or quantity completed at the work order level. If dynamic data in your manufacturing system is not in the right format, first try to fix this problem instead of interfacing it to a data warehouse to convert it into a different format. Inevitably, there will be timing issues. When the data is not in sync at all times, users will complain.

Any type of data and level of detail in the warehouse is fair game. However, a data warehouse should primarily be used for management reporting needs, such as tracking performance measures, data analysis, summarization, and drilling down into details that provide a snapshot in time. Only you can define your reporting and analysis requirements, but quality, schedule adherence, delivery, manufacturing variances, utilization and efficiencies are good candidates.

From industry analyst and CEO of Constellation Research Ray Wang:

I'd suggest a framework that allows you to identify the information requirements at each step of the processes. Classify the processes as time dependent, regulatory, and non-time dependent. Once you've done that, you can figure out what information needs to be delivered in real-time mode.

The key considerations tend to be more on the regulatory side, especially if you have compliance issues. All departments should have read access; update and delete access are separate issues. You'll want to have good role-based controls from your security model.

Dig Deeper on Manufacturing management