This paper describes aspects of the data warehouse project at Lawson Mardon Singen, a German supplier of flexible packaging materials. We focus our work on the methodology of re-constructed business terms as our modeling approach. The goal is to provide high quality data based on clear defined business terms for end user's decision support in their business process.
leading manufacturer of flexible packaging materials. At the German location in Singen, blank and improved aluminum-foils are produced for the packaging industry in the market segments food, pharma and cosmetics. With almost 1200 employees, a revenue of over 600 million
project was initiated to substitute the existing host applications by SAP’s standard software package R/3. Subsystems are stock management, purchasing, production planing, company controlling, financial accounting, plant management, quality-management and sales and distribution. Several HP Unix systems are used as database servers based on Oracle DMBS . At the final stage about 600 clients have to be supported.
The copyright of this paper belongs to the paper’s authors. Permission to copy without fee all or part of this material is granted provided that the copies are not made or distributed for direct commercial advantage.
Heidelberg, Germany, 14. - 15. 6. 1999 (S. Gatziu, M. Jeusfeld, M. Staudt, Y. Vassiliou, eds.) http://sunsite.informatik.rwth-aachen.de/Publications/CEUR-WS/Vol-19/ language. These objects are elements of the (natural) business language and relate to database tables and columns. Business terms can be clustered subject oriented. If these clusters are named “material management” or “purchasing” end user can easily relate these structures to their daily business process. The complexity of data warehouse data structures are hidden behind a semantic layer of clear defined business term.
an used term such as “order entry”.
As an example image the key figure “order entry” as an important key performance indicator of a company. If you ask five persons in different departments for a definition of “order entry” you might get five different answers depending on the departments. A salesman has a different view on “order entries” (customer’s view: what has to be shipped) as a person who is involved in production and planing (manufacturing’s view: what has to be produced).
4
process “material management” in 1998 we setup a data warehouse as a prototype using the new SAP Business
gain experiences with methods and tools to obtain a proof of concept in a small, but representative environment (“start small – think big”). The database consists of information object (-types) from the material management process such as MATERIAL, SUPPLIER, STOCK or
In using the prototype our end users were conflicted with different business term definitions, such as “order entry” already mentioned above. The key figures were “interpreted” in as many different ways as definitions (interpretations) had been around. Based on the work from E. Ortner [Ort97] we established a methodology to obtain company wide clear defined business terms in using a critical language approach (figure 1). Collection of statements • What does the term mean? • Which objects are included within the term? • Which characteristics does the term possess? • Is the term suitable in general? • Etc.
such as weaknesses or contradictions. To define the meaning (semantics) of a term the two concepts “intention” and “extension” has to be introduced. Intention means the set of objects which are covered by the term while extension denotes the set of characteristics (criteria) which are necessary to identify that an object belongs to a term set or not. The following term defects can be identified: • Synonyms, e.g. terms which have the same meaning (intention and extension). Example: internal orders or workshop orders. Those terms are not forbidden in general if they are known and controlled. • Homonyms, e.g. terms which are written equal but have a different meaning (intention and extension). Example: order entry (customer view) and order entry (manufacturing view). Homonyms must be cleared and distinguished, such as customer order entry and production order entry. • Equipollents: e.g. same objects (extension) are seen under a different view (Intention) and denoted different. Example: stock quantity and stock value.
• Weakness, e.g. as no clear demarcation (Intention) is obtained the set of objects which are covered by the term (extension) are weak. Example: do orders with 7-2 • status “not shipped” belong to the key figure “open order quantity” or not? Weak terms must be cleared.
The collection of single statements can be classified for database applications into: • Statements about information objects and their attributes • Statements about relationships between information objects • Statements about events • Statements about integrity constraints.
The natural language documentation of the relationships between business terms is the basis for verification and discussion between IT and company departments. The (formal) language of a data model is the language of the data engineer but the dialog with the end user is based on natural language. 4.2.5
as our main interest. In an university research project the modeling approach was adopted to multidimensional analysis purposes. The method was expanded by the concepts “key figures” and “dimension”. Figure 3 shows the conceptual approach schema [Yak98]:
Real world Collection of Statements The results of the single modeling steps are documented in the meta data repository of the data warehouse. It is used as the basis for generating the logical structures (multidimensional or relational). The meta data of the repository has been extended with a lexicon component to serve as a dictionary for business term management and documentation of the relationship between them. 4.3
7-3 communicator between business process requirements and the project team. They organize and pre-filter the information requests and coordinate its realization. They initialize the definition of new business term definitions and they are responsible for the organization of the definition process. A business term is defined by a “god father” who is responsible for the meaning (semantics) of a term which belongs to his/her business process. He also determines the group of persons responsible for reviewing the definition.
the term to be valid company wide.
The business term administration is responsible for formal reviews to the completeness of cross references. It forwards the business terms to the review group, evaluates methods and procedures and administrates the tools used for business term definition.
An appropriated end user access to the data warehouse is a critical success factor for acceptance and for efficient and effective information supply. Without any structuring the end user will not find the information needed – a circumstance which may convert an elegant data warehouse solution into a data cemetery. It was the goal of our system architecture to obtain a complete, consistent and always actual documentation about the objects of the data warehouse. Therefore a web application was designed with Oracle tools. The application serve as an interface between the meta data repository and the frontend tools for SAP Business Information Warehouse. The end user reaches the business term dictionary with an “information navigator”, a kind of help system. The information navigator accesses the business term dictionary and displays the information objects in end users terminology. An example is (figure 5): • • • • •
Reports • Which are available ? • What is represented ? • Access rights ? ...
Data analysis • Which cubes exist ? • What is the content ? • Access rights ? ...
Key figures • Which key figures exit? • What is the content ? • Wer darf sie abfragen? ...
Business terms • Which term exists ? • How are they defined ? • Existing synonyms ? • Who is responsible ? • Impact / Cross refs ? ...
Warehouse-objects • Which infoobjects exist? • What does they mean ? • Access rights ? ...
Warehouse-Queries • Which queries exist ? • What is the description ? • Access rights ? ...
Business Support applications • Which application exist ? • What can you do with it? • Access rights ? ...
market it is necessary to obtain world wide access to the data warehouse. Therefore intranet technology plays an important role for our warehouse project. The tool used for designing an appropriated user interface is Dynasight from Arcplan [ARC99]. Dynasight is certified to access
Dynasight-Server connects to the system and asks for processing an end user request. The OLAP-Server processes the query in dialog with the data warehouse kernel. The data returned contains a link to the business term dictionary. The Dynasight-Server automatical creates a dynamic document with a hyperlink to the business term dictionary (figure 8). Any browser can present the (Java) documents.
Operative data SAP R/3 Business termst dynaSight-Server
File ODBO Insight
File SAP BW OLAP Server Insight
File SAP BW Startup pages dynaCGI dynaJava Applet HTML Insight with dynaSight extensions Internet
Our main focus was to integrate clear defined business terms into the meta data of the warehouse to obtain a high quality information supply. After 4 months we have defined 500 business terms. We expect 2000 terms until the end of 1999. The main experiences are: • • • • • • •
for the need to critical business term definition
End Users [ORA99] Oracle Corp., http://www.oracle.com, 11-apr99.