Metadata in data warehouses

Fig. 14.1. Main functions of metadata for data warehouses

Composition of metadata in a data warehouse

At present there are no strictly defined requirements for the composition of metadata in information systems, including those that use a data warehouse. We will discuss the proposed metadata standards, in particular for data warehouses, a bit later. For now, let us focus on the list of elements or components of metadata that must necessarily be present in a data warehouse.

The basic components of data warehouse metadata do not differ greatly from the basic components of operational data processing systems. This is the description of tables, their attributes, keys, and so on. A significant difference for a data warehouse is support for metadata versioning. The basic components tell us what data is stored in the data warehouse.

The next group of metadata components, characteristic of data warehouses, is the description of transformations. As a rule, the description of data transformations for a data warehouse includes:

• identification of source data fields;
• the correspondence between the attributes of source-data entities and the attributes of data warehouse objects;
• attribute transformations;
• physical characteristics of the transformations;
• transformations of code tables and reference tables;
• name changes (the correspondence between source names and data warehouse object names);
• changes to key attributes;
• default field values;
• the logic (algorithms) for building data warehouse data from several sources (source prioritization);
• data transformation algorithms, and so on.

Transformation components tell us how the data in the data warehouse was obtained.

An important component of data warehouse metadata is the history of the data's arrival into the warehouse. The metadata component — the data extraction history — tells us when the data entered the data warehouse, and also allows us to judge how completely the data is represented in the warehouse. For conducting data analysis this information is very important, since it is the basis on which users' assertions about the correctness of the analysis and the reliability of its results are formed.

Information about synonyms, or terminological correspondences between concepts, is yet another component of data warehouse metadata. It includes alternative names (aliases) for data warehouse data. Such information typically makes the data warehouse more "user-friendly".

The next important component of metadata is information about the state and usage statistics of the data warehouse's data. This information forms the basis for optimizing the data warehouse's performance and includes data such as the number of rows in tables, the rate of table growth, statistical profiles of table usage (average and maximum number of queries per day), archiving and deletion statistics, table indexing, the frequency with which indexes are used in queries, and so on.

Another component of data warehouse metadata consists of data aggregation and summarization algorithms, source selection criteria, the rules for transforming source data before loading it into the data warehouse, descriptions of the relationships between data warehouse objects, and their cardinality, and so on. Such information plays an important role in conducting data analysis and is often needed by analysts to assess the reliability of the analysis results.

Information about who is responsible for the content and currency of the various data sources makes up another metadata component. This information matters to the data warehouse support team and makes it possible to organizationally address the quality, accuracy, and reliability of the data in the warehouse.

Metadata often includes components that describe data access patterns (when and how data migrated to another storage tier). These are also used to optimize the physical flow of data in the data warehouse and to optimize performance.

Sometimes the data processing algorithms in a data warehouse use information about objects from external systems — so-called extension tables (code tables and electronic reference directories). In such cases, the data warehouse's metadata must record a description of such tables and the history of their changes, since if the codes change, corresponding changes need to be made in the data warehouse's data processing so as not to lose the historical relationships in the data.

Data warehouse designers often include additional information in the metadata that they consider important.

From what has been said, it is clear that designing data warehouse metadata is a rather complex and creative task for the data warehouse designer, one whose solution often requires literary skill, knowledge of the data warehouse's subject domain, and a lot of time.

Fig. 14.2 shows the main elements of metadata for a data warehouse.

Fig. 14.2. Main elements of metadata for data warehouses

Example of representing metadata for a data warehouse

Let us look at how metadata can be described using the example of a data mart intended for analyzing the sales of some hypothetical company. Suppose the company manufactures and sells its own products. The data mart is used by the company's analysts to study in detail the relationship between the company's expenses and revenue from product sales, and to prepare sales reports for management.

Let us assume that our hypothetical company has opened a network of points of sale (retail and wholesale warehouses) and organized a chain of stores, i.e., it has expanded its operations. Company management wants to assess the effectiveness of this expansion and to have more detailed information about the relationship between sales and production in terms of costs and revenue.

Let us further assume that the company produces about 200 kinds (models) of some product. Each product has a base set of components. Additional components are used to create a specific product model. The company's market policy is structured so that the number of models produced remains constant. This means that the number of new models is roughly equal to the number of models discontinued.

For each model of each product, a flexible discount system is applied depending on demand. As a rule, the discount size for buyers of large batches of product is determined by the retail warehouse manager.

When a decision is made to discontinue production of a given model, information about it is kept in the company's database for 6 months after all remaining stock of that product has been sold or written off. Data about the product is deleted at the moment when the data about the last model of that product is deleted.

The company supports two sales channels: through a wholesale store and through a retail store. The wholesale store sells goods only to wholesale buyers. A buyer is considered wholesale if they purchase more than 20 batches of goods per year. A wholesale buyer may submit an invoice either directly to the wholesale store or by fax to the company's central office. Any buyer may purchase from several of the company's stores.

The retail store sells for cash. Regardless of any discounts offered, the price of the goods varies. Although an invoice is issued for every product sale, the company does not track buyers in retail sales.

Our company's data mart is designed to solve problems in analyzing cost and revenue indicators. The typical queries that the system should be able to answer are as follows.

1. What is the total cost and total profit for each product model sold today, summed by point of sale, by point-of-sale type, by region, and by wholesale warehouse?
2. What is the total cost and total profit for each product model sold today, summed by factory and by region?
3. What percentage of models received discounts, and which models were actually sold at a discount (as a percentage) in retail stores – for all sales this week? This month?
4. For each product model sold in the current month, determine what percentage of sales came from retail sales, from wholesale sales by non-cash payment, and from wholesale sales through sales representatives.
5. Which models, and of which type, were not sold during the past month? During the past week?
6. Which five models sold over the past month brought in the most profit? Over the quarter's sales? Over all sales?

The data source for the data mart is a fragment of the company's operational data processing system database. One possible data-mart structure, obtained as a result of the design process, is shown in Fig. 14.3.

enlarge image
Fig. 14.3. Multidimensional model of the data mart for analyzing the company's sales

Let us examine a description of the metadata for such a data mart. Note that the description given is an example of one of several possible approaches and should not be considered complete or final.

Logical structure of data warehouse metadata

Logical structure of the model's metadata

This section provides logical schemas of metadata for the data warehouse in our example. The example does not claim to be exhaustive, but it gives a clear picture of the approaches to describing metadata.

The logical structure of the metadata model may be as follows.

Logical structure of fact metadata

The logical structure of fact metadata may be as follows.

Name: Продажа (Sale).

Definition: This fact contains sales data for every order recorded in the operational order-processing system for each retail and wholesale warehouse.

Alternative name: None.

Load frequency: Daily.

Data load statistics

• Date of the last load.
• Number of rows loaded.

Data usage statistics

• Average number of queries per day.
• Average number of records selected per query.
• Average query execution time.
• Maximum number of queries per day.
• Maximum number of records selected in a query.
• Maximum query execution time.

Data archiving rules: Data will be archived after 36 months, on a monthly basis.

Archiving statistics:

• Date of the last archiving run.

Data deletion rules: Data will be deleted after 48 months, on a monthly basis.

Deletion statistics:

• Date of the last deletion.

Data quality: Staff errors in filling orders are possible. However, the records represented in the database are accurate.

Data accuracy: The metrics of this fact are 100% accurate, since they represent sales that have already occurred.

Granularity of the "Time" dimension: The metrics of this fact represent the sale of a given product under a given order.

Key field. The key for the sale fact is the combination of the dimension keys: Customer, Manufacture, Product, Seller, and Time.

Key generation method: The time portion of the key is simply the date of the product sale. The product key, manufacturer key, seller key, and customer key are selected from the operational database's reference directories.

Sources

• Name: Order Table.
• Transformation rules: Rows from the order table are copied into the sales fact table on a daily basis.
• Data selection criterion: Rows are selected for which the order was completed as of the current date.
• Name: Product Dimension.
• Value computation rules: The Product dimension is used to compute the cost of the model sold in a given order. The factory unit cost of the product is compared with the purchase or selling price to determine whether a discount was given. If a discount was applied, its amount is calculated.
• Selection criterion: Product data is processed before a row is inserted into the fact table.

Metrics: Total cost, Total revenue, Total quantity sold, and Discount amount.

Dimensions: Customer, Manufacture, Продукт (Product), Продавец (Seller), and Время (Time).

Employee responsible for the data: Manufacturing plant director.

Logical structure of dimension metadata

Let us illustrate the logical structure of dimension metadata using the "Customer" and "Time" dimensions as an example. It may be as follows.

For the "Customer" dimension

Name: Customer.

Definition: A customer is any individual or legal entity that purchases the company's products. A customer may purchase goods at several of the company's points of sale.

Alternative name: none.

Dimension hierarchy: Data along this dimension can be summarized at two levels. The first (lowest) level of summarization is the shipping address of the goods to the customer. Data for each legal entity's address can later be summarized by customer.

Change rules: Shipping addresses for each legal entity are inserted as new rows in the dimension. Changes to existing customer addresses are made by updating the dimension table directly.

Load frequency: Daily.

Load statistics

• Date of the last load.
• Number of rows loaded.

Usage statistics

• Average number of queries per day.
• Average number of rows selected per query.
• Average query execution time.
• Maximum number of queries per day.
• Maximum number of rows selected per query.
• Maximum query execution time.

Archiving rules: Data for this dimension is not archived.

Archiving statistics:

• Date of the last archiving run.

Deletion rules: Customers who have not purchased the company's products in the last 5 years are removed from the dimension table on a monthly basis.

Deletion statistics:

• Date of the last update.

Data quality: When a new customer is added to the dimension, a lookup is performed to determine whether that customer has already had sales recorded at a different address. Regardless of whether such sales exist, the customer with the new shipping address is inserted as a new row.

Data accuracy: A five-percent margin of error is permitted in determining the relationships between a customer and their shipping addresses.

Dimension key: A system-generated number that identifies the customer.

Key generation method: When a customer record is copied from the source system, a check is performed to see whether the customer is already present in the data warehouse. If that customer is not in the data warehouse, a new identifier is generated and the record is inserted into the dimension.

Sources

• Name: "Customer" Table.
• Transformation rules: Rows from the source system's "Customer" table are copied daily.
• Selection criterion: Only rows that are new or were modified as of the current date are selected.
• Name: "Customer Location" Table.
• Transformation rules: Rows from the "Customer Location" table are copied daily into the dimension table. For existing customer addresses, the shipping address is updated. For new customer addresses, a key is generated and records are inserted.
• Selection criterion: Only records that were updated or added as of the current date are selected.

Attributes

• Name: Customer Key
• Definition: This is an arbitrarily chosen number that guarantees the uniqueness of each customer and their address.
• Change rules: Once inserted into the dimension, the value of this attribute is never changed.
• Data type: Numeric.
• Domain: 1 - 999999999
• Value derivation rules: A system-generated key.
• Source: Generated by the system.
• Name: Name.
• Definition: The name by which the customer is known to the company.
• Change rule: When the customer's name changes, it is updated throughout this entire dimension.
• Data type: Character.
• Domain: A valid character string.
• Value derivation rules: To distinguish customers from different organizations that share the same name, a number is appended to the organization name.
• Source: The "Name" field from the Customer table of the source system.
• Name: Ship-to Address.
• Definition: For legal entities, this is the address to which goods are shipped. A single legal entity may have several ship-to addresses. This field is not supported for individuals and retail customers. Thus, for such customers only a single record is maintained in the dimension table.
• Change rules: When the ship-to address changes, this value is updated in the dimension.
• Data type: Character.
• Domain: An address record in a valid format.
• Value derivation rules: The ship-to address is copied from the source table.
• Source: The "Ship-to Address" field from the "Customer Location" table of the source system.

Facts: Sale.

Metrics: Total cost, Total revenue, Total quantity sold and Discount amount.

Data steward: Vice President of Sales and Marketing.

For the "Time" dimension

Name: Time.

Definition: The "Time" dimension contains the points in time when the company records sales data.

Alternative name: None.

Dimension hierarchy: The lowest level of data summarization is the day. Data for a given day can be summarized either by week or by month.