In the previous , we got acquainted with the basics of DATA VAULT, expanding DATA VAULT to a more analysis-friendly state and creating a BUSINESS DATA VAULT. It's time to conclude the series with the third article.
As I announced in the previous , this article will focus on the topic of BI, specifically preparing DATA VAULT as a data source for BI. We will discuss how to create fact and dimension tables, thus forming a star schema.
When I started studying English-language materials on building data warehouses over DATA VAULT, I felt that the process was quite complex. Since the articles are substantial, they contain references to changes in the phrasing introduced in the Data Vault 2.0 methodology, emphasizing the importance of these formulations.
However, upon delving into the translation, it became clear that this process is not as complicated as it seems. But perhaps you might have a different opinion.
So, let's get to the point.
Dimension and Fact Tables in DATA VAULT
The most difficult information to understand:
- Dimension tables are built on information from hubs and their satellites;
- Fact tables are built on information from links and their satellites.
And this becomes clear after reading the article on . Hubs store unique keys of business objects, their satellites store the state attributes of business objects tied to time, satellites tied to links supporting transactions store numeric characteristics of these transactions.
This is basically where the theory ends.
However, I believe it is necessary to note a couple of concepts that may appear in articles on the DATA VAULT methodology:
- Raw Data Marts – 'raw' data warehouses;
- Information Marts – informational warehouses.
The concept of 'Raw Data Marts' refers to warehouses built on DATA VAULT data by performing relatively simple JOINs. The 'Raw Data Marts' approach allows for flexible and quick project expansion with analyzable information. This approach does not imply performing complex data transformations or business rule executions before placing them in the warehouse; however, the data in 'Raw Data Marts' must be understandable to business users and is meant to serve as a foundation for further transformation, such as through BI tools.
"Information Marts" emerged in the Data Vault 2.0 methodology, replacing the older concept of "Data Marts." This change reflects the understanding of the task of implementing a data model for reporting as transforming data into information. The "Information Marts" schema primarily aims to provide business-relevant information for decision-making.
The rather verbose definitions reflect two simple facts:
- "Raw Data Marts" are built on raw (RAW) DATA VAULT, a storage containing only basic concepts: HUBS, LINKS, SATELLITES;
- "Information Marts" are built using elements of BUSINESS VAULT: PIT, BRIDGE.
For example, when storing information about an employee, a showcase displaying the current (as of today) phone number of the employee is a "Raw Data Mart". To create such a showcase, the employee's business key and the MAX() function applied to the load date attribute in the satellite (MAX(SatLoadDate)) are used. When it is necessary to store the history of changes to an attribute in the showcase – it is essential to understand from what date to what date the phone was valid. The primary key in such a table would consist of a compilation of the business key and the load date in the satellite, and a field indicating the end date of the period of validity is also added.
Creating a showcase that stores the current information for each attribute of several satellites included in the hub, such as phone number, address, and full name, involves using a PIT table, through which it is easy to obtain all relevant dates. Such showcases are classified as "Information Marts."
Both approaches are relevant for both dimensions and facts.
To create showcases that store information about multiple links and hubs, reference to BRIDGE tables may be utilized.
With this article, I conclude the series on the concept of DATA VAULT. I hope the information I shared will be useful in the implementation of your projects.
As always, to conclude, here are a few useful links:
- Kenta Graziano, which contains detailed descriptions and model diagrams;
- Book: "Building a Scalable Data Warehouse with DATA VAULT 2.0";
- ;
- .
Source: habr.com
