A new custom data warehouse geared at providing better business intelligence helps Papier Masson analyze paper quality/production parameters on a set and roll basis
	Analyzing Roll Production for Better Process Performance at Papier Masson
	by DANNY LAFLAMME

For its newsprint mill in Masson-Angers, Que., management at Papier Masson Ltée. (PML) wanted the capability to analyze the impact of production processes on customer satisfaction and mill efficiency. Previously, process engineers worked on production process issues by analyzing their effects using spreadsheets, mapping information from different production systems, and analyzing potential solutions. However, this approach proved to be extremely time consuming and could not provide the full range of analyses required.

To more efficiently analyze production processes, PML decided to partner with a supplier for the design and creation of a data warehouse. The goal of the first phase of the project was to provide users with valid data that answered the most valuable requests. Based on these needs, it was agreed that the first deliverable would cover roll production.

With its new business intelligence architecture in place, which included a data warehouse, managers at the mill can now analyze the effects of production parameters and paper quality on a set and roll basis. The users are in a position to make timely decisions based on facts, where in the past they could have only worked based on assumptions. Also, users can now establish relationships between mill performance, profitability, and production parameters, helping to improve competitive advantage. Because of this, PML is now in a better position to baseline its production process performance.

A NEED FOR ANALYSIS. In order to have a better understanding of the effects of production parameters on paper quality and their impact on its customers' operations, PML wanted to analyze quality information for every stage involved in a roll lifespan, from thermomechanical pulping to the customer's printing press. The analysis process required flexibility, mainly in the form of roll number indexing criteria.

As with many other organizations, PML's customer feedback, quality information, production parameters, and shipping information are managed in separate systems. In the mill's old system, this resulted in a high amount of data manipulation dispersed over several spreadsheets. Further, no interface was available to the mill's advanced roll information system used to help manage roll quality - the ARIS software from HTRC. Many tough questions were either left unanswered or partially answered, such as:

• What is the average basis weight shipped for a customer and for a given product during a given period, and is it different from those measured at the paper machine?
• What are the minimum tension/force/torque/ pressure parameters the mill can apply without affecting its customers' operations, and how much money, time, paper loss, complaints, etc., can be saved by modifying these parameters?
• What is the profitability by customer and by product?
• How much time, paper, and money does PML use during grade changes, and how many times does the mill change grades during a given time frame?
• How long can PML keep a reel "on the floor" without affecting its customers' operations due to changes in paper properties?
• Which customers require the creation of trim rolls, and what are the associated costs?
• What is the average inventory turnover for a given period of time, and how much money is sleeping "on the floor"?

Many of these questions were impossible to answer prior to the data warehouse project. Some of the questions took a production engineer an average of two days to solve, since the information was spread throughout various sources, such as the Microsoft SQL Server, Oracle, Ingress, OSI PI, and Progress. And, manually matching continuous and discrete process information was a considerable challenge.

GATHER, TRANSFORM, STORE, AND ANALYZE. The first phase of the project was to gather, transform, and store the information in a central location to provide for easier analysis of roll quality status, production parameters, and production events (such as roll creation, sheet/paper breaks, etc.) down to the roll number.

While the scope of the first phase was relatively small, the design had to include the capability for the warehouse to grow further with minimal impact on existing data. Many options were available to answer PML's needs, and each had to be thoroughly evaluated.

Option 1. The mill could have stayed with the procedure it had used for two years prior to the project. This option involved using a trainee to perform manual analyses, which had proven time consuming in terms of supervision. In addition, this option required that a new trainee be taught every year. Also, a simple analysis could take two weeks to perform and would involve manual data mapping from multiple sources.

Option 2. The mill could have also chosen to maximize the information stored in the OSI PI system. However, PI contains continuous process information and would have required an interface in order to include information from the discrete section. Also, PI is a time-based system and is not designed for multidimensional querying.

Option 3. Building a data warehouse using an off-the-shelf product was also an option for the mill, although the initial cost of such products is generally expensive. Also, training and development learning curve costs can be significant in the overall project budget.

However, an off-the-shelf product can lower development costs in the long run. For example, less effort would be spent in standard coding implementation and review. And, project documentation and support might be easier for new support personnel with limited knowledge of Transact-SQL coding standards.

Option 4. Building a custom data warehouse was another option examined by PML, though it would involve custom coding and, therefore, additional development effort. However, internal business and production rules could be included in the design. The modeling of hidden patterns in large volumes of data, known as data mining, could be easily implemented while doing data extractions without added costs.

Another benefit of a custom data warehouse was that its infrastructure could be deployed on a company standard database already mastered by PML programmers and database administrators. In addition, a custom data warehouse would be vendor independent.

CUSTOM DATA WAREHOUSE WINS. The mill chose the option of building a custom Microsoft SQL Server data warehouse, working with Invensys Manufacturing Solutions (IMS) on its design and creation. PML had no previous experience in data warehousing and requested the help of IMS primarily because of the mill's long-lasting relationship with the supplier and its knowledge in the field of business intelligence.

IMS performed a readiness analysis with regard to implementing a data warehouse and pinpointed a key data mart, a subset of a data warehouse focusing on a specific production or business process, that would fulfill the mill's most important requirements. It was decided that the warehouse would focus on production and paper quality information, most of which would come from systems located at the winder process step and from the customer order fulfillment system. By focusing on activities at the winder, managers would be able to easily assess quality with respect to production parameters and associate the results with their respective customers.

Design decisions. During the project, knowledge of manufacturing execution systems and data management was applied in many fields and activities, such as:

• Establishing coding standards and server configuration to speed up development time and decrease development cost;
• Designing the process under a dimensional model to improve query performance while keeping the structure expandable. A dimensional model contains denormalized dimension tables linked to fact tables, providing fast query response;
• Implementing key objects to allow data mining, validation processes, and alerts;
• Creating cubes for analyses using Cognos PowerPlay;
• Managing the project to respect critical phases in the schedule and keeping mill management informed;
• Implementing a development methodology for quality control and consistency along with PML.

Flow of information. Microsoft SQL Server is the standard database management system at PML and is well known by its personnel, therefore requiring no additional DBA training or product support. For this project, an additional SQL Server license was required, but its cost was negligible in an existing Microsoft SQL Server environment.

To view and analyze the information in its new data warehouse, the mill and its supplier chose to use Cognos PowerPlay, an online analytical processing (OLAP) software. It enables users to quickly explore large volumes of summarized data in a web, Windows, or Excel environment. With this software, managers can perform their own multidimensional analysis, create reports, and share them for decision making.

FIGURE 1. Flow of information through the data warehouse

Cognos Transformer, another software product, was used to draw enterprise information from the mill's relational databases in order to model and build "cubes," or data sets stored under a multidimensional representation. These cubes allow users to "slice and dice" information in numerous ways. Figure 1 shows the flow of information through the data warehouse.

Cognos catalogs made up of data warehouse information in a user-friendly representation were also made available to mill users. The catalogs let users choose desired fields to produce ad-hoc reports through Cognos Impromptu software. These reports allow users to perform drill down in order to identify specific metrics from the cubes, such as a TAPPI number for a roll or a set ejection time. Impromptu reports could also be used to create a cube from a specific list of items.

Other capabilities. The ability of query statements to extract, transform, and load the data was improved by exploiting the querying capacities of the source servers, without affecting their primary roles. As a result, the data warehouse completely updates within three minutes, which could allow for almost continuous up-to-date information ready for analyses twenty-four hours a day.

An administrator is automatically notified by email if any problem occurs during an update (such as a network failure). In addition, security is a part of the design, with MS-SQL Server security used as a security module for the data warehouse, offering the ability to use domain authentication security for easier maintenance.

Project costs. As shown in Figure 2, development represented 46% of the total effort. However, in PML's case, due to license fees, this custom solution is 33% less expensive than a solution using an off-the-shelf product. This takes into consideration development effort with a package product to be half the amount required with a custom solution.

FIGURE 2. Distribution of implementation costs

•Quality control and production cost management analyses that previously took up to two weeks to execute now only take four minutes;
• Paper losses and product declassification information is available by grade, helping managers estimate a more precise production standard cost;
• Effects of production parameters and paper quality are now easily analyzed, which causes production parameters to be revised for better performance;
• Impacts from decisions are easily monitored.

Process benefits. While designing and building the data warehouse, the information from the different contributing systems was analyzed and manipulated. This led to the identification of areas of improvement in the production process itself, even before the data warehouse was completed. Some of these discoveries were in the following areas:

• Roll set/reel hierarchy. The ARIS system for managing roll quality and the WrapMation TraqManager roll tracking system were not managing the roll set/reel hierarchy the same way for sets made up with two reels. When a roll set contained paper from two reels, TraqManager associated the roll set to the first reel mounted on the winder, while ARIS associated the roll set to the second reel mounted.

The inconsistency was resolved by modifying the hierarchy rule in ARIS. This finding was significant, since quality measures are taken from a reel and transposed on its associated rolls. The result was skewed quality information because of the two different business rules.

• Manual reel creation. New reel numbers are manually reentered into the roll tracking system, causing some mismatch with ARIS, which is the master system for reel creation. Most mismatches occurred when someone forgot to increment the reel number in TraqManager for a bad start occurring at the paper machine. Reel quality and production information would then be associated with the wrong rolls. The situation is currently being corrected by adding an interface between both systems that informs TraqManager when new reels are created.
• Manual reel selection at the winder. The winder operator has to select the proper reel number from a list before beginning winding operations. The operator can sometimes choose a different reel than the one mounted on the winder, which modifies the roll set/reel hierarchy. No modification has yet been implemented as production personnel are currently assessing the situation.

ALIGNING WITH BUSINESS STRATEGY. PML's data warehouse has had direct impact on both the company's top and bottom line. This solution contributed in closing the gap between customer order (top line) and production order (cost of goods sold/COGS). By focusing on the winding process step, the PML data warehouse team addressed the needs of two different types of users - the salespeople responsible for the company revenues and the production people responsible for the product quality, production cost, and timely delivery. The outcome of this initiative has allowed PML to shift its performance analysis from a product/revenue-centric model to a customer/profit one.

The IT group at PML had the foresight to exploit its unleveraged data assets and push information directly to decision makers. This outcome is what all senior managers ask of their IT function - alignment with business strategy. The data warehousing initiative facilitates a better utilization of the company's assets and a better return on these same assets that translates into increased shareholder returns, and many elements played into the success of the data warehouse project at PML.

Recipe for success. One element providing success for the mill was that management realized the benefits of such a project and involved the key personnel from required departments. Also, production personnel were involved early in the design in order to identify the information needed, the applicable business rules, and the approach to present the information. Production personnel were also very involved during the entire project, and teamwork was key.

Knowledge about data warehousing principles and database coding techniques was shared between IMS and PML personnel. A positive team environment where individual contribution was recognized and valued was essential, since quality assurance methodology required verification and testing of every module by a member other than the original programmer.

Involvement from source system vendors was also key, with HTRC, the vendor/developer of ARIS software, playing a critical role as it changed the way the roll set/reel hierarchy was handled into the ARIS system. The software vendor also provided training and support to interface the data warehouse to the ARIS system. In addition, HTRC provided support in explaining the different business rules behind its production reports so the data warehouse would match them.

Another key element in PML's success was the decision to start small and work with only one production process. The focus was originally kept to one business process, e.g., roll production only. This allowed them to provide the first deliverable within six months.

Danny laflamme is a business intelligence consultant for Invensys Manufacturing Solutions in Montreal, Que.