Analysis Patterns in Dimensional Data Modeling
Stephan Schneider1, Dirk Frosch-Wilke11 University of Applied Sciences Kiel, Institute of Business Information Systems, Sokratesplatz. 2, 24149 Kiel, Germany
{stephan.schneider, dirk.frosch-wilke}@fh-kiel.de
Abstract. The construction of conceptual dimensional data models is one of the most important, fundamental and challenging tasks during the analysis phase in the systems development life cycle of a data warehouse system. Such data models are representing operational as well as strategic business requirements. Dimensional data models are used for implementing dimensional databases within the data warehouse system, which itself will be used for generating crucial information for decision-making. Although the enormous importance of conceptual dimensional data models is well known, the use of approved analysis patterns is not common practice. The non-consideration of analysis patterns can yield to poorly planned and therefore qualitative unproven dimensional data models respectively databases, which similarly yields to qualitative unproven generated decision-relevant information. Up to now the use of analysis patterns in dimensional data modeling is given no attention to in literature and in practice. This paper will overcome this gap in building data warehouse systems by introducing analysis patterns for dimensional data models which address well known and recurring problems in specific contexts. Keywords: Analysis, Patterns, Dimensional Data Modeling, Data
Warehousing.
1
Introduction
The methodical development of information systems is divided into the core phases analysis, design and implementation. The results of a preceding phase (e. g. analysis) in form of information models (e. g. conceptual data models) serve as inputs for the succeeding phase (e. g. design). The analysis phase is the first step in the development cycle of a system and therefore the fundamental and most important phase. All system requirements are defined in the analysis phase and documented in conceptual models.
A very effective and proven means for developing normalized conceptual data models is the use of analysis patterns like described in [8], [17], [18] and also in [5]. The use of analysis patterns leads to a time- and costsaving construction process and to qualitative proven data models.
Up to now the use of analysis patterns in dimensional data modeling for construction of information systems for business intelligence solutions is given no attention to in literature and in practice. Only in the books of [12], [13] and [14] some
common exemplary problems and their solution approaches are described and modeled, but not in the form of patterns. Considering the enormous importance of the analysis phase, it must be noted that the non-consideration of analysis patterns in dimensional data modeling leads to poorly planned and qualitative unproven dimensional data models. In short, the non-consideration of analysis patterns is a research gap in dimensional data modeling and therefore as it is in data warehousing.
2
Dimensional Data Models
Since its introduction in 1990, data warehouse systems have been established as a solid and integrated part of the IT systems landscape of an enterprise [2], [6], [10], [12], [13], [14], [19]. A data warehouse system is used to improve and to optimize the data logistics within companies. A data warehouse system is an enterprise-wide information system, which consists of applications and databases harnessing the data warehouse [19]. A classic definition of a data warehouse was coined by William Inmon, who defines a data warehouse as a subject oriented, integrated, non-volatile and time variant collection of data in support of management’s decisions [10]. Based upon this definition a data warehouse can be interpreted as an integrated pool of databases that is both target for integration activities on operational databases and source for generating crucial information within business intelligence applications [15], [16].
In the center of a data warehouse system is the data warehouse. A data warehouse is the hub within a data warehouse system and thus the basis for enterprise-wide data logistics. Depending on the architecture of the data warehouse system, a data warehouse is composed of different database types [16]. However dimensional structured databases are always part of a data warehouse. Such dimensional structured databases are used to generate crucial information for decision-making.
Dimensional structured databases based upon dimensional data models. A dimensional data model is a special data model representing the structure of multidimensional data on type-level, and therefor relies on the concepts "dimension" and "fact" [9], [13]. Multidimensional data can be seen as a n-dimensional data cube.
The cells of the n-dimensional data cube contain the facts. A fact is a quantitative measure, which describes an analyzable and a (business) relevant piece of information of a perceptible (economic) situation in a concentrated form [16].
The edges of the multidimensional cubes are representing the dimensions. A dimension is a unique, orthogonal structure element of multi-dimensional data [2].
Within the scope of an n-ary relationship all declared dimensions (classes) are combined to the fact table. A fact table is an association class whose primary key consists of the primary keys of all dimensions within the n-ary relationship. In addition, the fact table contains the facts as attributes.
3
Analysis Patterns
3.1 Problem-solving behavior of experts
An expert is a person who is well grounded in a domain and has comprehensive domain knowledge, which is knowledge about one or more subject areas. According to [4], including the identification of relationships and watching out for repeatable structures as for superficialities are key properties of an expert’s behavior. In addition, experts as distinguished from laity are normally characterized by a lot of successful solved problems and thereby solve upcoming problems not always from ground up, but draw on well-proven solution patterns for ancient problems [3], [7]. An expert draws on in using well known patterns for old problems to solve new problems. He doesn’t reinvent the famous wheel anew, he adopts proven problem solving knowledge in new problem situations. Prerequisite for the usage of an already practiced solution is first, that the solution is principle applicable, and secondly, it exists in an appropriate level of abstraction. The elaborated and in an appropriate level of abstraction existing domain knowledge, which describes the solution for a recurring problem in a special context, is called a pattern [5], [16]. This easy-held definition also explains why it is spoken of a problem-solution-context triple in relation to a pattern.
The idea, to record and reuse knowledge in canonical form goes back to the architect Christopher Alexander. In his book "The Timeless Way of Building" he describes various patterns and their properties especially for usage in house building and urban planning [1].
In the year 1994 the published book "Elements of Reusable Object-Oriented Software" of [7] has exerts one of the greatest influences and an initiating effect for the pattern movement in software development history. Although the pattern movement records several roots [5], at least design patterns are an integral part of software development since the publication of this book. As the title of this book hinted, the presented patterns are design patterns for the design phase of the software development lifecycle.
The pattern concept as a successful artifact of reuse in the design phase has contributes to build and use patterns also in the analysis phase. In analogy to design patterns during the design phase patterns during the analysis phase are called analysis patterns. The term analysis pattern was introduced by Martin Fowler. In his book "Analysis Patterns: Reusable Object Models" [5] he defines a pattern as „an idea that has been useful in one practical context and will probably be useful in others.“ He deliberately leaves the definition open to indicate that a pattern could be anything in the end. The phrase "practical context" indicates that a pattern is the result of real and practical work and results from knowledge about a domain.
Every model construction process in software development can be seen as a problem, which must be solved from an expert’s point of view with the help of patterns. Since the analysis phase is the most important phase in the whole systems development life cycle, therefore analysis patterns must be given a great importance.
3.2 Schema of the description form
For analysis patterns specification a minimalistically description form is chosen in this paper. This description form consists of the parts problem, context and solution. A more comprehensive pattern description structure offers [11]. In addition to problem, context and solution also methodological, organizational and economical aspects are used to describe patterns in a comprehensive way.
Unique Name. Each pattern is given a unique, precise and concise name.
Problem. For each pattern a description of the problem is made. The problem description outlines the problem, which can be defined as an unsatisfactory situation with negative impacts and recurs in a context. In addition, all the forces, which are necessary for the solution, are considered too.
Context. For each pattern a description of the context is made. A context identifies the real or intangible environment, where the perceived phenomena are located and a problem recurs. In addition, a context describes the conditions for the use of the pattern.
Solution. For each pattern a description of the solution is made. A solution can be defined as a satisfactory situation without negative impacts or in more general terms as the treatment of a recurring situation (problem). The pattern offers the solution for a specific problem in a graphical representation form. The graphical representation shows the pattern structure and its contribution to the problem solving.
4
Selected Analysis Patterns for Dimensional Data Models
Like mentioned in chapter 1 analysis patterns is given no attention to in literature and in practice. Only Kimball et al. [12], [13] and [14] describe some exemplary problems and their solution approaches in their graduate course on dimensional data modeling, but not in form of patterns.
The most practical experiences of the authors in data warehousing projects and the exemplary problem situations are suitable starting points for the construction and appropriate abstraction of analysis patterns. This chapter prepares some of these examples respectively experiences in a common way and presents the analysis patterns in the form of the triple problem-context-solution.
4.1 Different change intervals of dimension attribute values
Name. Dimensions attribute values changing.
Problem. Normally a dimension has multiple attributes, which come from a normalized structured net of classes thru denormalizing processes. The conception of a dimension deliberately follows the principle of denormalization. On the principle of normalization built, and therefore divided among several classes, originally split attributes are brought together into one dimension. In the course of this reunion there is no distinction been made, what kind of change intervals the individually attribute values have.
The resulting dimensions are constituted of attributes, whose values have different change intervals.
The consequence of this inconsiderate attributes merge is a continuously increasing number of dimension objects that possesses a high degree of redundancy. The individual objects only differ in those attributes values that are often have been changed, while the rest of the attributes values have been retained unchanged.
For example, a normalized data model describes product data in a simple classification hierarchy as follows: product (n) (1) product group (n) (1) product family (n) (1) product category. A product is classified into one product group, whereas one product group encompasses one or more products. A product group itself is classified into one product family, whereas one product family encompasses one or more product groups, and so on. The relationship-types between the different product classes are many-to-one relationship-types.
In the course of denormalization the attributes of the classes product, product group, product family and product category are brought together within the dimension product. It is common business practice that product category data are more often and faster be changed than product specific data.
Context. The context of this problem is all-encompassing. Any perceived problem
domain is context of this problem.
Solution. The solution of this problem is shown in figure 1. As shown the origin dimension Dimension is split into two dimensions. The one dimension (Dimension A) contains those attributes that values are not often been changed, while the other dimension (Dimension B) contains those attributes that values are often been changed.
To prevent possible hierarchy information loss caused by splitting the attributes into two dimensions it’s advisable to include hierarchy level attributes (static and dynamic attributes hierarchy level) in both dimensions. A hierarchy level attribute stands for the level of hierarchy of a specific attributes group within the dimension. For example, the hierarchy level of product specific attributes is 0. This hierarchy level represents the highest level of granularity (LoG). The hierarchy level of product group is 1, of product family 2 and of product category 3. Hierarchy level information is needed in drill-down and roll-up operations.
4.2 Multiple occurrences of dimension objects per fact(s) by given fact table grain
Name. Many-to-many Dimensions by given fact table grain.
Problem. The choice of the fact table grain (degree of detail) is a very important step during the construction process of a dimensional data model. The fact table grain determines what a fact table record represents. If the fact table grain is given because of impending analysis or reports, there may be dimensions, which objects (dimension records) occur several times per fact entry.
The non-consideration of this situation causes a violation of the fact table grain and a mutation of the fact values and thus to wrong results in the analysis of multidimensional data. The mutation of the fact values means that a priori additive facts become to semi-additive facts or non-additive facts, which will lead to significant conception losses.
As an example, an invoice in health care will be used. An invoice is basically composed of several invoice items (or invoice positions). Especially in health care an invoice item represents many diagnoses made by multiple practitioners and along with that many treatments. Practitioner, diagnosis and treatment as well as patient and time are dimensions of the dimensional data model. For reporting purpose in health care invoicing the fact table grain is to be fixated on an invoice item. That is, a record of the fact table represents an invoice item.
An immediate consequence of this fact table grain is a multiple occurrence of practitioners, diagnoses and treatments per fact entry. An opportunity to avoid the problem of multiple occurrences of dimension objects per fact entry is to fix the fact table grain on time × patient × practitioner × diagnosis × treatment. This solution, which implies a multiple insertion of the respective primary key of the corresponding dimension as foreign key in the fact table, is de facto a violation of the fact table grain. Therefore this solution will not been followed up.
Context. The choice of the fact table grain is one of the first steps in the conception process of a dimensional data model, and therefore essential in the whole conception of a dimensional data model [9], [13], [14]. The consequence of a fixation of the fact table grain can be a multiple occurrence of dimension objects per fact entry in any dimensional data model. Therefore the context of this problem is all-encompassing. Any environment that is perceived and leads to the construction of dimensions and facts is context of this problem.
Solution. The solution of this problem is shown in figure 2. Between Dimension and fact table there will be integrated a dimension bridge class (Dimension Bridge). The primary key of Dimension Bridge is a composite primary key that consists of Dimension Group Key and Dimension Key. Dimension Key is from Dimension and Dimension Group Key is a surrogate key that indicates a group. Under a Dimension Group Key the n-fold occurrences of dimension objects are subsumed. Instead of every single primary key (Dimension Key) of the dimensions the dimension bridge group key (Dimension Group Key) is set as a foreign key and part of the primary key in fact table.
Fig. 2. Many-to-many Dimensions Pattern
5
Conclusion
The analysis patterns presented in this paper should be regarded as a first approach to address the pattern thought in dimensional data modeling. Research works, practical experiences as well as some exemplary problems treated in Kimball et al. in conjunction with the organization of data structures for decision-support tasks and their solutions in form of dimensional data models served as an introduction in this subject. The presented patterns help to solve problems recurring in certain contexts in a proven way. In addition, the used solutions promise an additional benefit effect with regard to the quality management for dimensional data models constructed with the help of analysis patterns.
In the course of further research and development work in the field of analysis patterns in dimensional data modeling the authors plan to build a wiki-based pattern repository for dimensional data models.
From the point of a strong scientific view it must be noted that this work is at the beginning of a research cycle. The explicated patterns are based on abstract units of knowledge. The knowledge base consists of own domain knowledge and own practical experiences as well as in parts foreign knowledge described in the literature. Moreover, this knowledge base is enhanced in an inductive and logical manner, transferred into abstract units of knowledge thru cognitive synthesis to build the basis for pattern construction. In the sense of the research objective of behavioral science, which seeks to develop and verify theories, the abstract units of knowledge have the state of hypotheses. Planned empirical studies must now provide evidence which leads to competent knowledge in the new research field of pattern driven dimensional data modeling development.
References
1. Alexander, C.: The Timeless Way of Building. Oxford University Press, New York (1979)
2. Bauer, A., Günzel, H. (eds.): Data-Warehouse-Systeme: Architektur, Entwicklung, Anwendung. Dpunkt, Heidelberg (2009)
3. Batra, D.: Conceptual Data Modeling Patterns: Representation and Validation. Journal of Database Management, 16 (2), 84--106 (2005)
4. Chi, M.T.H., Glaser, R., Farr, M. J. (eds.): The nature of expertise. Lawrence Erlbaum Associates, Hillsdale, NJ (1988)
5. Fowler, M.: Analysemuster: Wiederverwendbare Objektmodelle. Addison-Wesley, Bonn et al. (1999)
6. Frosch-Wilke, D.: Data Warehouse, OLAP und Data Mining - State of the Art und zukünftige Entwicklungen. DuD - Datenschutz und Datensicherheit 10, 597--604 (2003)
7. Gamma E., Helm, R., Johnson, R., Vlissides, J.: Design Patterns: Elements of Reusable Object-Oriented Software. Addison-Wesley, Reading, Mass. et al. (1994) 8. Hay, D. C.: Data Model Patterns: Conventions of Thought. Dorset House, New
York (1996)
9. Imhoff, C., Galemo, N., Geiger, J. G.: Mastering Data Warehouse Design: Relational and Dimensional Techniques. Wiley, Indianapolis, Indiana (2003) 10. Inmon, W. H.: Building the Data Warehouse. Wiley, Indianapolis, Indiana
(2005)
11. Jung, J., Sprenger, J.: Muster für Geschäftsprozessmodellierung. In: Schelp, J., Winter, R., Frank, U., Rieger, B., Turowski, K. (eds.) Integration, Informationslogistik und Architektur. LNI, P-90, pp. 189--204. Köllen, Bonn (2006)
12. Kimball, R., Reeves, L., Ross, M., Thornthwaite, W.: The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and Deploying Data Warehouses. Wiley, New York (1998)
13. Kimball, R., Ross, M.: The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling. Wiley, New York (2002)
14. Kimball, R., Ross, M., Thornthwaite, W., Mundy, J., Becker, B.: The Data Warehouse Lifecycle Toolkit: Practical Techniques for Building Data Warehouse and Business Intelligence Systems. Wiley, Indianapolis, Indiana (2008)
15. Schneider, S.: Ein Ansatz zur Konstruktion generischer (Unternehmens-) Datenmodelle auf fachkonzeptioneller Ebene. In: Schelp, J., Winter, R., Frank, U., Rieger, B., Turowski, K. (eds.) Integration, Informationslogistik und Architektur. LNI, P-90, pp. 173--188. Köllen, Bonn (2006)
16. Schneider, S.: Konstruktion generischer Datenmodelle auf fachkonzeptioneller Ebene im betrieblichen Anwendungskontext: Methode und Studie. Shaker, Aachen (2007)
17. Silverston, L.: The Data Model Resource Book: Volume 1, A Library of Universal Data Models for All Enterprises. Wiley, New York (2001)
18. Silverston, L.: The Data Model Resource Book: Volume 2, A Library of Universal Data Models By Industry Types. Wiley, New York (2001)
19. Winter, R.: Zur Positionierung und Weiterentwicklung des Data Warehousing in der betrieblichen Applikationsarchitektur. In: Jung, R., Winter, R. (eds.) Data Warehousing Strategie, pp. 127--139. Springer, Berlin et al. (2000)
