Lecture
Это продолжение увлекательной статьи про метаданные в хранилищах данных.
...
style="margin-left:0px; margin-right:0px">Change rules: Records are inserted into the dimension once per current year. No updates to this dimension are allowed.
Load frequency: As needed.
Load statistics
Usage statistics
Archival rules: The data of this dimension is not archived.
Deletion rules: After 5 years, data will be deleted on an annual basis.
Deletion statistics
Data quality: No errors are expected in the data of this dimension.
Data accuracy: The data of this dimension is always accurate.
Dimension key: The key of the "Time" dimension is the date in YYYYMMDD format.
Key generation method: The date, represented as a string, is used as the key value.
Source
Attributes
Facts: Sale.
Metrics: Total cost, Total revenue, Total quantity sold and Discount amount.
Responsible person: DW Administrator.
We will illustrate the logical metadata structure for metrics using the "Total Cost," "Total Revenue," and "Total Quantity Sold" metrics as examples. It might look as follows.
Name: Total Cost.
Definition: This is the cost of all components used to manufacture the given product type (model) that was sold.
Alternative name: none.
Data type: Numeric.
Domain: 0.01 - 9999999.99
Value derivation rules: Total cost equals the unit cost of the product (model) multiplied by the number of units sold.
Usage statistics
Data quality: This metric is calculated based solely on the cost of components at the time the given product type was sold. No other production cost types are taken into account.
Data accuracy: The variance in the cost of components for the given product type is assumed to be +/- .5%.
Facts: Sale.
Dimensions: Customer, Manufacture, Product, Seller and Time.
Name: Total Revenue.
Definition: Total revenue equals the number of units of the product sold multiplied by the selling price of that product at the time of sale.
Data type: Numeric.
Domain: 0.01 - 999999999.
Value derivation rules: Total revenue is the selling price of the product model multiplied by the number of units of the product model sold.
Usage statistics
Data quality: This metric represents the number of product models sold.
Data accuracy: For the purpose of building sales trends and customer behavior patterns, high data accuracy is not required.
Facts: Sale.
Dimensions: Customer, Manufacture, Product, Seller and Time.
Name: Total Quantity Sold.
Definition: This is the number of units of product models sold.
Data type: Numeric.
Domain: 1 - 9999999.
Value derivation rules: This value is taken directly from the "quantity" column for each line item of the invoice.
Usage statistics
Data quality: This field represents only the quantity of product sold.
Data accuracy: For the purpose of building sales trends and customer behavior patterns, high data accuracy is not required.
Facts: Sale.
Dimensions: Customer, Manufacture, Product, Seller and Time.
The logical structure of source metadata might look as follows (using the description of the "Order" table from the source system as an example).
Table name: Order.
Data extraction method: Records with transactions completed as of the current date are selected from the source table for loading into the DW.
Data extraction schedule: Daily, at the end of the business day.
Data extraction statistics
As a rule, any standardization effort begins with building a classification of the objects for which the standard is being developed. The metadata standard is no exception to this rule. A second aspect of developing any industry standard is taking into account the proposals of the leading vendors. And a third point is the concept that lets the standard being developed be linked to standards already in force in the given subject area.
The subject of discussion in this section will be the "Common Warehouse Metamodel" (CWM) specification, one of the standards [66] that use XML technologies. This standard describes metadata exchange in information systems that employ data warehousing technologies, as well as in Business Intelligence systems and Knowledge Management systems.
As follows from our discussion of metadata in the previous sections, at a very high level metadata can be divided into two categories: shared metadata and unique metadata.
Elements of shared metadata are necessary for the precise definition of objects and their semantics across the DW as a whole. Thus, the definition of the "Customer" object in our example above must be consistent both for the sources and for the DW, i.e., it must have the same meaning across information systems at the scale of the organization. Unique metadata describes unique objects in the system, for example the attributes of dimensions or facts.
Another approach to building a metadata classification is dividing metadata elements by their functional purpose:
Business metadata contains definitions of subject-area entities in user terms, logical mappings between data at various levels of its representation in the system, and descriptions of the DW vocabulary, for example that of a database.
An example of business metadata could be the description of some attribute of a DW subject-area entity, such as the weight of a sold or purchased item. Consider the example in Fig. 14.4.
In two data marts of a data warehousing system there are two fact tables: "Purchase Invoices" and "Sales Invoices." Each mart is responsible for a specific line of business of the organization. Both tables have an attribute named "Weight." The semantic meaning of this attribute is the same in both cases: the weight of the item. However, one data mart may use "kg" as the unit of weight measurement, while the other uses "centner" (quintal).
Structural metadata contains a description of the structure of various data objects. It is used when implementing navigation schemes for presenting data to users. For example, the "Book" data object consists of the elements "Title," "Author," "Table of Contents," "Preface," "Chapter with text." Each chapter has a "Heading" and, possibly, a number of "Subheadings."
Technical metadata contains definitions and data about the physical objects of the DW. These are definitions of table and column names, constraints, rules for physical data transformations, and so on. For example, the format and length of the "Customer Name" field is a variable-length string of up to 100 characters.
Process metadata contains information related to the data processing process, such as load statistics, the data load schedule and so on.
Researchers in the field of DW design propose other approaches to building a classification as well, but as a rule the two principles named above are the ones used.
The main types of metadata are shown in Fig. 14.5.
Leading data warehousing software vendors wage fierce competition for leadership. Putting forward one's own solutions as an industry standard for this class of system is an integral part of that competition.
In mid-1998, IBM, Oracle, Unisys, Hyperion, SAS, Meta Integration, and a number of other software vendors submitted to the Object Management Group (OMG) a specification for the "Common Warehouse Metadata Interchange" (CWMI) standard. In the second half of 1999, Microsoft submitted for consideration to the Meta Data Coalition (MDC) consortium its own standard, the "Open Information Model" (OIM).
In 2000, MDC and OMG merged, and a year later, in early February 2001, the first version of the "Common Warehouse Metamodel" specification was published [67].
CWMI defines interfaces that can be used to exchange metadata between the DW and analytical applications through DW tools, hardware/software platforms, and metadata repositories in distributed, heterogeneous computing environments.
CWMI is based on three core standards:
The UML standard defines an object-oriented modeling language that supports a number of graphical notations. The MOF standard defines flexible facilities for defining a metadata model and provides software tools for storing and accessing metadata in a repository. The XMI standard defines specifications for exchanging metadata in the XML standard format. Using these standards does not impose strong constraints when implementing a metadata model in specific data warehousing systems.
The standards listed above form the core of the OMG metadata repository architecture, as shown in Fig. 14.6.
The main elements of the Common Warehouse Metamodel (CWM) include:
The four-level metamodeling architecture is analogous to the generally accepted modeling architecture, as shown in Table 14.1.
| Meta level | Modeling level |
|---|---|
| M3 | Meta-Metamodel / Meta-meta-metadata. For example, an MOF class, attribute, association |
| M2 | Metamodel / Meta-metadata. For example, a UML class, a CWM table attribute, a column |
| M1 | Model / Metadata. For example, Customer: Customer_Table_Type: Column |
| M0 | Data / Object. "LLC" ("Flint") |
The standard extends the base metamodel with metamodels for relational and multidimensional data, for OLAP transformation functions, and for the DW, including processes and operations. The CWM specification can be viewed as a language intended for defining DW models. The CWM specification extends the UML language: each CWM metaclass is inherited, directly or indirectly, from UML metaclasses. Thus, the CWM "Relational Table" metaclass is a direct descendant of the UML Class, while "Relational Column" is a direct descendant of the UML Attribute.
Thus, the CWM specification defines a metamodel for both business metadata and technical metadata. This metamodel is used to exchange metadata instances between heterogeneous software supplied by different vendors. Systems that support the CWM metamodel exchange data in formats that conform to this model.
The OMG "Meta Object Facility" (MOF) standard defines common interfaces and semantics for interoperating metamodels. Being a subset of UML, it represents an example of a meta-metamodel, or a model of a metamodel (a subset). The scope of this standard includes the definition of an Interface Definition Language, which establishes rules for managing models through software APIs. All CWM models are expressed in UML and implement MOF semantics.
The OMG "XML Metadata Interchange" (XMI) standard establishes rules for converting MOF metamodels into XML. XMI is directly involved in exchanging metamodels. MOF metamodels are translated into XML DTDs, and models are translated into XML documents that conform to their DTDs.
Thus, the CWM standard consists of a number of constituent metamodels (sub-metamodels), organized into the following 4 layers: the Foundation layer, Resources, Analysis, and Management, as shown in Fig. 14.7.
The Foundation layer consists of metamodels that support modeling such diverse elements and services as data types, system type conversion, abstract keys and indexes, expressions, business information, and the inclusion of software based on component objects.
The Resources layer provides the ability to model existing and new data sources, including relational databases, record-oriented databases, as well as XML- and object-based data sources.
The Analysis layer provides facilities for modeling information analysis services that are typically used in a data warehouse. It defines a metamodel for data transformation, OLAP, information visualization, and data mining.
The Management layer consists of metamodels representing standard DW processes and operations, logging, and job scheduling (for example, daily loading and unloading).
The set of CWM metamodels is sufficient for modeling an entire DW.
Once metadata requirements have been gathered and formalized, work can begin on developing the metamodel. In practice, following a standard's requirements is often difficult. This is due to a shortage of time and a lack of understanding, on the part of company management, of how important a well-developed metamodel is, especially when a company builds its DW using its own IT department's resources. The guide for designing and developing the CWM metamodel runs to more than 700 pages [68]. As a rule, IT management finds it difficult to explain to company leadership that building a DW in-house requires either hiring a new full-time employee or sending a specialist for training.
Whether a company outsources DW development to a third-party firm or intends to carry it out on its own, the following approaches to creating a metamodel can be identified.
To build a DW metamodel by hand, one must gather correct definitions of entities, their attributes, and the relationships between entities. Either object-oriented modeling or ER modeling can be applied to develop such a metamodel.
If a designer building a DW metamodel chooses to rely on a standard, they have the option of using either the "Open Information Model" (OIM) specification or the "Common Warehouse Meta-Model" (CWM) specification. CWM describes metadata exchange in data warehousing, knowledge management, and business intelligence systems. OIM is a set of metadata specifications for use in developing DW applications. Both specifications are based on industry standards such as UML, XML, and SQL.
The choice of approach to metadata design is largely determined by the designer's set of data warehouse tools and the choice of underlying DBMS. It is clear that a manually developed metamodel has an important advantage: as a rule, it most fully reflects the company's representation of metadata. But such a model has a major drawback — it must be maintained and kept up to date continuously, usually by hand. Models developed in accordance with standards take into account most of the requirements for representing the company's metadata in the DW. In addition, they are extensible and are supported by leading development tool vendors (Oracle, IBM, Microsoft).
The DW metadata repository should be maintained regardless of which metadata design method is used. In doing so, it is important to choose an architecture for it (whether it will be centralized or distributed) and ways of keeping it up to date (since metadata links together the semantics of all components of the data warehousing system).
The software components of the data warehousing system exchange metadata through the repository in the course of their operation. To organize the exchange of metadata, the CWM standard makes it possible to elaborate the architecture of the repository in detail. In this case, the metadata exchange format is an XML document.
An example of using AllFusion ERwin Data Modeler to create and maintain a DW metamodel is examined in detail in [37]. Next we will study how a metadata model can be built by hand.
In turning to the study of logical metadata model design, our aim is to understand the essence of the process of representing metadata in a DW and to acquire the skills of building a metadata model that can later be applied to managing metadata through the metadata repositories supplied by vendors of DW software.
In the section "Logical structure of data warehouse metadata" we examined examples of describing metadata for various DW objects. We will use these examples when creating our metadata model.
Let us first create a logical data model for the metadata of fact tables. It might look like the one in Fig. 14.8.
It is advisable to place the metadata about a fact table in two entities, one of which — "Fact Tables" — contains information about the fact table that practically never changes, while the other — "Load History" — contains data that changes according to the "Load Frequency" parameter.
The "Fact Tables" entity includes information:
The "Load History" entity includes information:
The remaining elements of the fact table's meta-description are in an inheritance relationship with the "Fact Tables" entity. To represent these meta-description elements, additional entities have been introduced in the diagram, which we will model further on.
The fact table of a multidimensional model contains data about facts and metrics. Let us build the corresponding fragment of the metadata model (Fig. 14.9).
The entities "Facts", "Fact Metrics", "Metrics" and "Star Schema Fields" represent a description of the characteristics of facts. The entities "Attributes" and "Attribute Domains" represent the physical definitions of fact metrics in the DW.
Note that the "Attributes" entity contains the attribute "Dimension Key", since this entity must also describe dimension attributes.
Let us look at modeling dimension metadata (Fig. 14.10).
The entities "Dimensions" and "Dimensions and Metrics" describe dimensions and their relationships with the metrics of the fact table. The "Attributes" entity describes dimension attributes.
Let us supplement the metadata model we have developed for our example with information about data sources, as shown in Fig. 14.11.
Data sources for the DW are described in the entities "Data Sources", "Tables", "Columns", "Data Load" and "Transformation Rules".
Thus, we have built a logical metadata model for the DW in our example from the section "Logical structure of data warehouse metadata" of this lecture.
Note that not all elements of the metadata description were used in constructing the DW metadata model. This is at the discretion of the DW designer, based on a study of the requirements for the data warehousing system.
Let us also note that what we created was a partial model that does not take into account a number of requirements placed on a metadata model. As a rule, when building a DW metadata model, a number of mandatory elements of metadata representation in the model must be taken into account, namely:
продолжение следует...
Часть 1 Metadata in data warehouses
Часть 2 Metadata standards - Metadata in data warehouses
Часть 3 Summary - Metadata in data warehouses
Comments