The Data Vault modeling method

The next model component is responsible for context, namely for answering the questions of when, why, what, where, and who creates operations and business keys (the 5W methodology). For example, knowing a vehicle's number is not, by itself, a reason for a customer to buy it. The customer needs to know the color, make, and so on. Satellite entities exist to store such information.

Satellite Entities. Satellite entities contain descriptive information about hub keys, namely when, why, what, where, and who creates operations and business keys. For example, unlike a vehicle number, its color, make, and so on can change over time, and therefore the data structure must reflect these changes at every level of information structuring (granularity).

Satellite entities usually contain the following attributes:

• primary key — the primary key of the hub or link that has migrated into the satellite;
• load date/time stamp (Load Data/Time Stamp) – the date and time the descriptive information was recorded in the database;
• record source (Record Source) – recorded for data lineage tracing;
• a satellite entity may be assigned a surrogate primary key.

Fig. 18.5. Satellite entity for the customer's address

Fig. 18.5 shows a satellite entity containing information about changes in a customer's address over time. The "Load Time" attribute is part of the composite primary key. Since the data is ordered by time, the main purpose of the satellite entity is to provide a description of the hub key. Table 12.3 illustrates the contents of the database tables corresponding to these entities.

The "Point-In-Time" entity is derived from a satellite. It is built to support information lookup for given points in time. An example of a "Point-In-Time" entity is shown in fig. 18.6 below. Table 18.4 illustrates the contents of the database tables corresponding to these entities (the table structure is simplified for clarity).

Fig. 18.6. Example of a "Point-In-Time" entity

The "Point-In-Time" entity is derived from a satellite. It is built to support information lookup for given points in time.

The Bridge entity contains timestamps of the last load. This entity is similar to the "Point-In-Time" entity, but it spans the entire subject area or data schema.

Business users often want to see data grouped in different ways. In the simplest case, one department (say, marketing) has its own customer hierarchy, while another department (say, sales) has a different hierarchy of the same customers. Both hierarchies could be included in the "Customer" dimension. However, several hierarchies built directly into a dimension would make it unwieldy to use.

The business requirement for a more flexible implementation of additional hierarchies arises when several departments need to group the same data by different classification schemes, and moreover in several different variants. In such a case it is necessary to work with users and determine the most common data grouping. This grouping becomes the standard, default hierarchy and is built directly into the main dimension table. The same can be done for a few of the other most widely used hierarchies for ease of use by users.

To support additional hierarchies, a separate table is created in the DW that allows the user to group data by any of the available hierarchies. This is the bridge entity, or bridge table. Fig. 18.7 shows an example of such an intermediate table, "Customer Geographic Location" (CustomerRegionHierarchy), for grouping by geographic location.

enlarge image
Fig. 18.7. Example of a bridge entity

In a query, the value of the "Hierarchy Name" field ( HierarchyName ) specifies the required data grouping. For example, the predicate in the clause WHERE HierarchyName = 'Отдел маркетинга' specifies a data grouping for the marketing department.

Every hierarchy in the intermediate table must be complete, i.e., it must start at the level of the base dimension to which the intermediate table is attached and end at the topmost level. For example, the "Customer Geographic Location" table (CustomerRegionHierarchy) is connected at the "State" level.

To simplify analysis and reporting, the intermediate table should also contain a description of the standard hierarchy. The standard hierarchy becomes the default used in all pre-configured reports, but the user is given the option to switch to a different hierarchy. A separate "Hierarchy" table with one row per hierarchy simplifies system maintenance but visually complicates the design. If needed, denormalization can be performed by merging the "Hierarchy" table with the "Customer Geographic Location" (CustomerRegionHierarchy) table.

User Grouping Link entity contains information that provides the user with a particular point of view on the data, but does not affect the content of the data in the DW. Table 18.5 shows how the user grouping link entity is used.

Thus, the user grouping link entity makes it possible to establish how users will unfold the data in fact tables.

We have examined the main and additional elements of the "Data Vault" model and can now move on to describing the general algorithm for building a DW model using the method described above.

Algorithm for building a "Data Vault" model

When creating a "Data Vault" model, it is necessary first to create the entities and describe their attributes, and then to establish the relationships between them. Entities should be created in the following order.

1. Hub entities, which must contain the subject area's business keys.
2. Link entities to support relationships between business keys, i.e., information about the organization's activity in the context of business keys.
3. Satellite entities to describe the full picture of the organization's activity from the standpoint of business procedures.
4. "Point-In-Time" entities to support lookup of the points in time when descriptive information changed.

When creating relationships in the "Data Vault" model structure, the rules of referential integrity must be observed.

1. Hub keys cannot migrate into other hubs, i.e., a parent-child relationship between hubs is not supported.
2. Hubs interact with one another through link entities.
3. A link entity can be used to connect more than two hubs.
4. Link entities can be connected to one another.
5. Link entities must connect at least two hubs.
6. Surrogate keys can be used for hubs and links.
7. Hub keys always migrate.
8. Business keys of hubs never change, nor do their primary keys.
9. Satellites can be linked to hubs and link entities.
10. Standalone timestamp tables, such as calendar, time, code, and description tables, may be used.
11. Satellites always contain either a load timestamp or a numeric pointer to a load timestamp (referencing a standalone timestamp table).
12. Link entities can have surrogate keys.
13. If a hub has more than two satellites, a "Point-In-Time" entity can be created.
14. Satellites must not contain duplicate rows.
15. Data in satellites is separated by type of information or by rate of change.

Data changes are collected in satellites. If a satellite's size grows very quickly, two new satellites can be created to limit this growth. Data in the new satellites can be separated by type of information or by rate of change.

Hubs store the business keys of the organization's main lines of activity (subject areas). Business keys typically change very rarely. A hub's primary key is used in link entities to represent operations of the main lines of activity.

Now let us consider applying the above algorithm to a tutorial example, i.e., let us build a "Data Vault" model for the tutorial example's schema.

Example of designing a "Data Vault" model

Tutorial database

Let us consider the Northwind database, developed by Microsoft for training purposes. Its data model is shown in fig. 18.8.

As can be seen from the figure, this model is characterized by the use of nonstandard data types: bit, ntext, image, money. Using nonstandard data types can lead to data conversion problems if a different DBMS is used to build the DW than the one used in the OLTP system. In our case, the "Data Vault" will be built on the same DBMS as the OLTP system, namely Microsoft's MS SQL Server 2008.

enlarge image
Fig. 18.8. Schema of the tutorial database in third normal form

The "Data Vault" modeling process

Let us examine the process of converting the normalized model from the example (fig. 18.7) into a "Data Vault" model. As discussed in previous sections, the conversion process includes the following stages.

1. Identifying business keys and creating a set of surrogate keys, forming the model's hubs.
2. Identifying the relationships between tables that must be supported, forming the model's link entities.
3. Identifying descriptive information, forming the model's satellites.
4. Normalizing (regrouping) satellites either by rate of change or by type of information. Adding bridge entities and "Point-In-Time" entities.

Let us note right away that for the tutorial example's model, adding bridge entities and "Point-In-Time" entities is not required.

When working with more than one data model (integrating several data sources), the conversion should begin with the model that is the primary one from the standpoint of the organization's system's lines of activity, and then proceed in stages to the models of other subsystems, in order to arrive at a unified point of view on data representation in the system's model as a whole.

Now let us proceed to implementing steps 1-3 of the process of forming a "Data Vault" model for the tutorial example.

Forming hub entities

Let us follow the process we have described for building a "Data Vault" model. First we identify the business keys and place them in standard-structure hub entities. Since hubs are, by definition, a list of business keys, it is important to place them together with surrogate keys, if such exist in the model.

By examining the model in fig. 18.8 (unique indexes, data queries, etc.), we can build the following groups of business keys/surrogate keys and identify the entities that are candidates for the role of hubs in the "Data Vault" model.

The "Categories" table ( Categories ) has the business key "Category Name" ( CategoryName ) and the surrogate key "Category ID" ( CategoryID ). These will be the constituent elements of the "Category Hub" hub entity ( HUB_Category ).

The "Products" table ( Products ) has the business key "Product Name" ( ProductName ) and the surrogate key "Product ID" ( ProductID ). These will be the constituent elements of the "Product Hub" hub entity ( HUB_Product ).

The "Suppliers" table ( Suppliers ) has the business key "Supplier Name" ( SupplierName ) and the surrogate key "Supplier Identifier" ( SupplierID ). They will become the components of the hub entity "Hub_Supplier" ( HUB_Supplier ).

The "Order Details" table ( Order Details ) has no business key, does not represent a business process, and therefore cannot have its own hub.

The "Orders" table ( Orders ) has a surrogate key, which may or may not be tied to a business key, depending on the business requirements. This table is transactional in nature and is a better candidate for a link entity than for a hub.

The "Shippers" table ( Shippers ) has the business key "Company Name" ( CompanyName ) and the surrogate key "Shipper Identifier" ( ShipperID ). They will become the components of the hub entity "Hub_Shipper" ( HUB_Shippers ). Note that if the business requirements call for integrating shipping companies, the "Company Name" ( CompanyName ) field can be used as the business key. However, if the business requirements call for shippers to be maintained separately from one another in the system, that field is not descriptive enough for the business process and should be replaced with the "Shipper Name" field ( ShipperName ).

The "Customers" table ( Customers ) has the business key "Company Name" ( CompanyName ) and the surrogate key "Customer Identifier" ( CustomerID ). They will become the components of the hub entity "Hub_Customers" ( HUB_Customers ). Note that if integrating customers and shippers is needed, the hub could be named "Hub_Company" ( HUB_Company ) so as to integrate shippers and customers together.

The "Customer_Customer" table ( CustomerCustomerDemo ) has no business key and therefore cannot be converted into a hub. This table is a candidate for a link entity.

The "Customer Demographics" table ( CustomerDemographics ), at first glance, has the business key CustomerDesc and the surrogate key CustomerTypeID, and a hub "Hub_CustomerDemographics" ( HUB_CustomerDemographics ) could be created for it. However, note that this table could also be treated as a satellite entity for customers.

The "Employees" table ( Employees ) has the business key "Employee Name" ( EmployeeName ) and the surrogate key "Employee Identifier" ( EmployeeID ). They will become the components of the hub entity "Hub_Employee" ( HUB_Employee ).

The "Employee Territories" table ( EmployeeTerritories ) has no business key and therefore cannot be converted into a hub. This table is a candidate for a link entity.

The "Territories" table ( Territories ) has the business key "Territory Description" ( TerritoryDescription ) and the surrogate key "Territory Identifier" ( TerritoryID ). They will become the components of the hub entity "Hub_Territory" ( HUB_Territories ).

The "Region" table ( Region ) has the business key "Region Description" ( RegionDescription ) and the surrogate key "Region Identifier" ( RegionID ). They will become the components of the hub entity "Hub_Region" ( HUB_Region ).

We can now compile the list of hub entities of the model that need to be built (Fig. 18.9).

• "Hub_Category" - Hub_Categories
• "Hub_Product" - Hub_Producst
• "Hub_Supplier" - Hub_Suppliers
• "Hub_Shipper" - Hub_Shippers
• "Hub_Company" - Hub_Customers
• "Hub_CustomerDemographics" - Hub_CustomerDemographics
• "Hub_Employee" - Hub_Employees
• "Hub_Territory" - Hub_Territories
• "Hub_Region" - Hub_Region

enlarge image
Fig. 18.9. Identifying the hub entities of the "Data Vault" model for the tutorial example

To denote business keys we will use different names than those in the diagram in Fig. 18.8.

Let us look at the list of hubs in Fig. 18.9. Note that the surrogate keys in the diagram in Fig. 18.8 uniquely determine the business keys of the corresponding tables. Consequently, we can replace the business keys in the hubs with their corresponding surrogate keys. It makes sense to do this because the size of a surrogate key is, as a rule, considerably smaller than the size of the corresponding business key. For instance, the type of the surrogate key "Category Identifier" ( CategoryID ) is int, which takes up 2 bytes, as opposed to the 16 bytes of the business key "Category Name" (CategoryName) (the CTO_NAME field in the hub). This is standard practice in data warehouse design.

Fig. 18.10 shows the final list of hub entities for the tutorial example model.

The hub table for a hub entity is created with the CREATE TABLE command, as shown below for the "Hub_Categories" hub ( Hub_Categories ). The remaining hubs of the model are created in the database in the same way.

CREATE TABLE Hub_Categories (
    CategoryID int NOT NULL,
    CTO_LOAD_DTS datatime NOT NULL,
    CTO_REC_SRC nvarchar(20) NOT NULL,
PRIMARY KEY (CTO_SEQ_ID)
);
CREATE UNIQUE INDEX Hub_Categories_idx
ON Hub_Categories (CategoryID);

enlarge image
Fig. 18.10. List of hub entities of the "Data Vault" model for the tutorial example

We can now move on to building the link entities for the "Data Vault" model of the tutorial example.

Building the link entities

In the second stage of converting the source model into a "Data Vault", it is necessary to identify the link entities, i.e., the relationships between business keys. Link entities represent the organization's business processes; their purpose is to connect business keys with one another. They are the most important elements of a data warehouse in the "Data Vault" model — without them it is difficult to relate data to one another. Transactional tables and tables used to resolve many-to-many relationships are good candidates for link entities.

For the tutorial example model, the following link entities can be identified.

The "Order Details" table ( OrderDetails ) is in a many-to-many relationship, and therefore a link entity "Link_OrderDetails" ( LNK_OrderDetails ) will be built for it.

The "Orders" table ( Orders ) is the parent table for the "Order Details" table OrderDetails, so a link entity "Link_Orders" ( LNK_Orders ) will be built for it.

The "Customer Customer" table ( CustomerCustomerDemo ) is in a many-to-many relationship, and therefore a link entity "Link_Customer_Customer" ( LNK_CustomerCustomerDemo ) will be built for it.

The "Employee Territory" table ( EmployeeTerritories ) is in a many-to-many relationship, and therefore a link entity "Link_Employee_Territory" ( LNK_EmployeeTerritories ) will be built for it.

The "Territory" table ( Territories ) is in a many-to-many relationship, and therefore a link entity "Link_Territory" ( LNK_Territories ) will be built for it.

Let us continue examining the tutorial example model. It is easy to notice that some tables that are candidates for hub entities are in a parent-child relationship with respect to a foreign key. For the "Products" table ( Products ), these are the "Category Identifier" ( CategoryID ) and "Supplier Identifier" ( SupplierID ) keys. It therefore makes sense to build a link-entity table LNK_Products that includes "Product Identifier" ( ProductID ), "Supplier Identifier" ( SupplierID ), and "Category Identifier" ( CategoryID ). Note that this table does not require a surrogate key, since "Product Identifier" ( ProductID ) is sufficient to represent suppliers and categories.

The "Employees" table ( Employees ) has a recursive relationship. To represent it, we introduce the link-entity table "Link_Employees" ( LNK_ Employees ).

There are no other relationships in the tutorial example model that can be extracted into link entities. We can now combine the hub entities and the link entities in the model diagram, as shown in Fig. 18.11.

enlarge image
Fig. 18.11. Identifying the hub entities and link entities of the "Data Vault" model for the tutorial example

The database table for a link entity is created with the CREATE TABLE command, as shown below for the "Link_Products" link ( LNK_Products ). The remaining link entities of the model are created in the database in the same way.

CREATE TABLE LNK_Products (
ProductID int NOT NULL,
CategoryID int NOT NULL,
SupplierID int NOT NULL,
CTO_LOAD_DTS datatime NOT NULL,
CTO_REC_SRC nvarchar(20) NOT NULL,
PRIMARY KEY (ProductID),
FOREING KEY(SupplierID) REFERENCES HUB_Suppliers,
FOREING KEY(CategeoryID) REFERENCES HUB_Categories
);

Having built the hub entities and the link entities, we can move on to building the satellite entities for the "Data Vault" model of the tutorial example.

Building the satellite entities

The remaining fields of the tables in the diagram in Fig. 18.8 are instances of entities that change over time and will therefore be placed in satellite entities. In the model under consideration, the following tables can serve as satellite entities:

• the "Categories" table (Categories) - satellite entity CAT_CATEGORIES ;
• the "Products" table (Products) - satellite entity CAT_PRODUCTS ;
• the "Suppliers" table (Suppliers) - satellite entity CAT_SUPPLIERS ;
• the "Orders" table (Orders) - satellite entity CAT_ORDERS ;
• the "Customers" table (Customers) - satellite entity CAT_CUSTOMERS ;
• the "Shippers" table (Shippers) - satellite entity CAT_SHIPPERS ;
• the "Employees" table (Employees) - satellite entity SAT_EMPLOYEES ;
• the "Territories" table (Territories) - satellite entity SAT_TERRITORIES ;
• the "Region" table (Region) - satellite entity SAT_REGION ;
• the "Customer Demographics" table (CustomerDemographics) - satellite entity SAT_CUSTOMERDEMOGRAPHICS.

Note that satellite entities do not contain attributes that depend on a foreign key. The primary key of a satellite entity is a composite key: the primary key of the corresponding hub entity plus the data load time.

We can now combine the satellite entities with the hub entities and link entities in the model diagram, as shown in Fig. 18.12.

Fig. 18.12. Identifying the hub entities, link entities and satellite entities of the "Data Vault" model for the tutorial example

enlarge image
Fig. 18.12. Identifying the hub entities, link entities and satellite entities of the "Data Vault" model for the tutorial example

The database table for a satellite entity is created with the CREATE TABLE command, as shown below for the "Satellite_Products" satellite ( CAT_Products ). The remaining satellite entities of the model are created in the database in the same way.

CREATE TABLE SAT_Products (
ProductID int NOT NULL,
PRD_LOAD_DTS DateTime NOT NULL,
QuantityPerUnit nvarchar(20),
UnitPrice money,
UnitsInStock smallint,
UnitsOnOrder smallint,
ReOrderLevel smallint,
Discontinued bit,
PRD_REC_SRC nvarchar(20) NOT NULL,
PRIMARY KEY (ProductID, PRD_LOAD_DTS)
FOREING KEY (ProductID) REFERENCES HUB_Products
);

This completes the construction of the "Data Vault" model for the data schema of the tutorial example.

We can now move on to discussing how to populate the objects of the resulting physical model.

Populating the objects of the "Data Vault" with data

In practice, it is advisable to use virtual tables, or views, one for each object of the model, to populate the "Data Vault" objects.

Data is only ever inserted into hub entities: business keys are added when the data warehouse is initially populated. Link entities are populated in a similar way. Data is inserted into satellite entities whenever data changes occur in the source systems.

When loading a hub entity, only those business keys (together with their surrogate keys) that are not yet in the hub are selected. To implement this process, the view shown below can be used, using the "Hub_Categories" hub as an example.

CREATE VIEW V_INS_HUB_CATEGORIES AS

SELECT DISTINCT
A.CATEGORYID,
GETDATE() LOAD_DATE,
'NORTHWIND' RECORD_SOURCE
FROM NORTHWIND..[CATEGORIES] A with (NOLOCK)
WHERE NOT EXISTS
(SELECT * FROM HUB_CATEGORIES WITH (NOLOCK))

When loading a link entity, only those composite keys (together with their surrogate keys) that are not yet in the link are selected. To implement this process, the view shown below can be used, using the "Link_Orders" link as an example.

CREATE VIEW V_INS_LNK_ORDERS AS
SELECT DISTINCT
A.ORDERID,
A.CUSTOMERID,
A.EMPLOYEEID,
A.SHIPVIA,
GETDATE() LOAD_DATE,
'NORTHWIND' RECORD_SOURCE
FROM NORTHWIND..[ORDERS] A with (NOLOCK)
WHERE NOT EXISTS
(SELECT * FROM LNK_ORDERS WITH (NOLOCK))

When loading a satellite entity, the set of records matching or joined by the business key (or the composite key) whose columns have changed in the source tables for the satellite is selected, and only the most recent changes are taken. To implement this process, the view shown below can be used, using the "Satellite_Employees" satellite as an example.

Views work well when the source database and the "Data Vault" are entities of the same relational database. If this is not the case, two solutions are possible: 1) using a staging area for the data source so that views can still be used; 2) using ETL tools to transform, compare, and load the data.

Summary

A "Data Vault" is a subject-oriented set of normalized tables that preserves history and unique relationships in the data, providing information support for one or more of an organization's lines of business.

A "Data Vault" has three main "building blocks".

• Hub entity (Hub) – a table containing a list of unique business keys. Used to identify the organization's lines of business.
• Link entity (Link) – a link table containing unique relationships between keys. Used to record relationships between hub entities and link entities.
• Satellite entity (Satellite) – a table containing descriptive and historical data. Used to store descriptive information for hub entities and link entities.

In general terms, the algorithm for building a "Data Vault" consists of designing entities in the following order:

• hub entities, which must contain the business keys of the subject area;
• link entities to support the relationships between business keys, i.e., the organization's activity schema in terms of business keys;
• satellite entities to describe the full picture of the organization's activity from the point of view of business procedures.

One of the main advantages of the "Data Vault" method is a dynamic representation of the relationships within the data warehouse's subject area. Relationships are defined through the business keys of the hubs and are recorded in the link entities. They exist over time, and their history is preserved in the satellites. This makes it possible to display the dynamics of how a relationship develops.

It makes sense to use the "Data Vault" method in the following cases:

1. for building Dynamic Data Warehousing, when it is necessary to account for the dynamics of change in both data processing and data structures;
2. for building Data Mining/Exploration Warehousing, when users need to change the data structure without losing information;
3. when embedding data mining (DM) procedures in the data warehouse.

Thus, we have covered yet another method for modeling data warehouses.

See also

• Relational data warehouses
• [[b4470]]
• [[b6923]]
• [[b9333]]
• [[b9334]]
• [[b9335]]
• [[b9336]]