While the business data model portrays the enterprise view of the business rules, the system model provides an understanding of the business rules within the realm of the individual application's needs. Often, individual applications address only part of the organization, and hence the model to be used for the application needs to be adjusted for both scope and business rules. Let's consider how this applies to an application being built to handle only commercial customers for a firm that has other types of customers, such as consumers and industrial customers. The scope adjustment removes entities (e.g., household) and attributes (e.g., consumer subtype attributes) that do not apply to commercial customers; the business rule adjustment impacts relationships (some may not apply), as well as optionalities and cardinalities of relationships that remain. The rules depicted in the system model are transferred into the physical model, with retention of mandatory foreign key relationships and accommodation for nullable foreign keys for tables with optional parents.
The system data model is derived from the business data model and adjusted for the application scope and view. Further, it adjusts the business data model to provide the basis for a structure that not only conforms to the enterprise rules, but also meets the performance constraints of the application. Various denormalization activities are performed in creating the system (and subsequently the technology) data model.
For a data warehouse hub, these include accommodation of the historical views, incorporation of summaries, aggregations, and derived fields, table merges, creation of arrays and creation of partitions, etc. These adjustments are created to improve performance of the data loads and/or the data distribution. Vertical partitions, for example, facilitate data loads by segregating data into tables based on their volatility or sourcing constraints. This may result in additional joins, however, when loading the data marts.
For the dimensional data marts, or bus architecture data warehouses, these include flattening of the dimensions, creation of arrays, creation of partitions, etc. Arrays, for example, are created to enable retrieval of data that would be stored in three separate rows (e.g., current month sales amount, last month sales amount, same month last year sales amount) from a single row.
The relationship between the business data model and system model is not unidirectional. The relationship also flows upward from the system model to the business data model. This upward flow enables coordination among multiple applications. Often, during the development of a system model, the need for additional data elements or a refinement in existing data elements is uncovered. (This is common in an incrementally developed business data model.) When this is suspected, the situation should be discussed with appropriate businesspeople, and if it is deemed valid, the information should be added to the system model and the business data model. Once these elements are added or adjusted in the business data model, then other derivative system models should be examined and potentially adjusted to incorporate the changes. Through this process, data consistency among applications will be better assured.
It is important to understand why we create the system and technology data models. These models have several purposes. First, they ensure that the application developers understand and apply the business rules. Second, they enable development and application of specific steps for consideration and application in building the physicals structures. Finally, they provide a means to coordinate the data structures created by multiple project teams.
I welcome your feedback if you have additional input regarding the purpose of the data models.
Jonathan G. Geiger is executive vice president at Intelligent Solutions, Inc. Geiger has been involved in many corporate information factory and customer relationship management projects within the utility, telecommunications, manufacturing, education, chemical, financial and retail industries. In his more than three decades as a practitioner and consultant, Geiger has managed or performed work in virtually every aspect of information management. He has authored or co-authored numerous articles and three books, presents frequently at national and international conferences, and teaches several public seminars. Geiger may be reached at JGeiger@IntelSols.com.
Be the first to comment on this post using the section below.