To achieve its goals, a DSS is comprised of an analytical models database that is fed with selected data extracted from source systems. Source systems are the operational systems that are available within an organization, but can include any other source of enterprise data. Examples might include exchange rates, weather information or any other information that is required by managers to make informed decisions. The raw data is aggregated within the analytical models database or on the way into the system [1, p57]. ETL (extract, transform, load) tools that have been developed to extract, transform, and load data from data sources to targets do the loading:

The analytical models database in Figure 1.2 is loaded by an ETL process with data from five data sources. The data is then aggregated either by the ETL process (in the data preparation process) or when the business user queries the data. Business users can query the analytical models database with ad-hoc queries and other complex analysis against the database. In many cases, the data has been prepared for their purpose and contains only relevant information. Because the decision support system is separated from the source systems, interactions with the DSS will not slow down operational systems [7, p1].

The next section discusses data warehouse systems that are covered in this book. These systems were introduced in the 1990s and have provided the data backend of decision support systems since then [7, p3].

Before business users can use the information provided by a data warehouse, the data is loaded from source systems into the data warehouse. As described in the introduction of this chapter, the integration of the various data sources within or external to the organization is performed on the business keys in many cases. This becomes a problem if a business object, such as a customer, has different business keys in each system. This might be the case if a customer number in an organization is alphanumeric but one of the operational systems only allows numeric numbers for business keys. Other problems occur when the database of an operational system includes dirty data, which is often the case when invalid or outdated, or when no business rules are in place. Examples for dirty data include typos, transmission errors, or unreadable text that has been processed by OCR. Before such dirty data can be presented to a business user in traditional data warehousing, the data must be cleansed, which is part of the loading process of a data mart. Other issues include different data types or character encodings of the data across source systems [9, p30f]. However, there are exceptions to this data cleansing: for example, if data quality should be reported to the business user.

Another task that is often performed when loading the data into the data warehouse is some aggregation of raw data to fit the required granularity. The granularity of data is the unit of data that the data warehouse supports. An example of different granularity of data is the difference between a salesman and a sales region. In some cases, business users only want to analyze the sales within a region and are not interested in the sales of a given salesman. Another reason for this might be legal issues, for example an agreement or legal binding with a labor union. In other cases, business analysts actually want to analyze the sales of a salesman, for example when calculating the sales commission. In most cases, data warehouse engineers follow the goal to load at the finest granularity possible, to allow multiple levels for analysis. In some cases, however, the operational systems only provide raw data at a coarse granularity.

An important characteristic of many data warehouses is that historic data is kept. All data that has been loaded into the data warehouse is stored and made available for time-variant analysis. This allows the analysis of changes to the data over time and is a frequent requirement by business users, e.g., to analyze the development of sales in a given region over the last quarters. Because the data in a data warehouse is historic and, in most cases, is not available anymore in the source system, the data is nonvolatile [9, p29]. This is also an important requirement for the auditability of an information system [10, p131].

The next section introduces enterprise data warehouses, which are a further development of data warehouses, and provides a centralized view of the entire organization.

In order to achieve this goal, all raw data that is required for the subject areas is integrated, cleansed, and loaded into the enterprise data warehouse. It is then used to build data marts that have been developed for a specific subject area. Such data marts are also called dependent data marts because they depend on the data warehouse as the source of data. In contrast, independent data marts source the data directly from the operational systems. Because this approach requires the same cleansing and integration efforts as building the data warehouse, it is often simpler to load the data from a central data warehouse [13].

The consistent, single version of truth of an enterprise data warehouse is an important goal for the consumers of data warehouses [12, pxxiv; 14, p23]. However, different departments often require a unique version of the truth because of a different definition of “what is the truth” [15]. That is why an enterprise data warehouse provides multiple subject areas, as covered in the previous section. Each subject area provides the required information for its individual users in the required context.

The single version of facts is also important under auditing and compliance requirements, which is covered in section 1.2.10. We will learn later in this book that Data Vault based EDWs provide both versions: the single version of truth and the single version of facts.

Mission criticality also requires a specific level of quality of the data warehouse data [12, pxxv]. If source systems don’t provide the raw data in the required quality, it is the job of the data warehouse to fix any data quality issues and improve the data quality by means of data cleansing, data integration or any other useful methods.

Another problem in data warehousing is that changing the data warehouse is often complex because of existing dependencies. While building the first version of the data warehouse was easily done, the second version takes more time. This is because the architecture of the data warehouse was not built with those changes in mind.

Section 1.4 discusses several data warehouse architectures. We will propose an alternate architecture in Chapter 2, Scalable Data Warehousing Architecture. The advantage of this architecture lies in its scalability regarding the absorption of changes to the data model, among other advantages.

The first characteristic is volume. What someone calls “big data” often means that the data is much more than this person is used to handling. However, this statement is highly subjective. Big data for one person or one company might be one gigabyte of raw data but this is rather small data for a person who loads terabytes or even petabytes of data. Loading data in real-time has different requirements and therefore a different definition of big data than loading data in nightly batches or near real-time (near real-time means that the data from operational systems is available in the data mart within a time frame of typically 15 minutes). The definition of big data also depends on the hardware available to the data warehouse.

The second characteristic of big data is velocity. It is not only that there is a lot of static data available in the source systems. Loading this data can become a complex task. However, data stored in an operational system is changing frequently. The more data that is available in a source system, the more changes are applied to it. Therefore, the typical big data project has to deal with lots of updates, data changes or new data that is added to the source system.

The third characteristic is variety. Big data often doesn’t have the same structure. Instead, the data structures of big data might change over time or, such as in “unstructured” datasets (e.g., texts, multimedia), has no ordinary structure at all: instead of using columns and rows as relational tables, unstructured datasets use other types of structures, such as linguistic structures. From a computing standpoint, these structures are considered unstructured because the structure is not as obvious as in relational tables. In other cases, there are data from so many different small data sources that the sum of this data is “big data” with high variety in data structures.

Because there is more and more data available nowadays, a data warehouse structure must not only be able to scale (which refers to the volume), it should also be able to deal with velocity and variety of the incoming data. In other cases, data is always in motion: by that we mean that it is currently processed or transferred in packets that are smaller than the actual data asset. Consider for example the transfer of data over a TCP/IP network: the data which needs to be transmitted is usually divided in smaller chunks and stored in IP packets, which are then transmitted over the network. This adds other problems to big data because data is flowing into and out of the network device. In order to analyze the data, it has to be collected, combined, and aggregated – in some cases at real-time. This raises the bar on what and how big data is architected and planned for and leads us to performance issues, which are covered in the next section.

The performance of data warehouse systems is influenced by the way a database system stores its data on disk: data is stored in pages with a fixed data size. For example, a Microsoft SQL Server allocates 8 KB disk space for each page [18]. Each page holds some records of a particular table. The wider a table is and the more columns a table has, the fewer rows fit into one page. In order to access the contents of a given column for a given row, the whole page where this piece of data exists must be read. Because analytical queries, which are often used in data warehousing, typically aggregate information, many pages must be read for accessing the contents of only one row. A typical example of an aggregation is to sum up the sales of a given region; this could be the sum of an invoice_total column. If there are many columns in the table, a lot of data must be read that is not required to perform the aggregation. Therefore, a goal in data warehousing is to reduce the width of columns in order to improve performance. Similar concepts apply to the loading of new data into the data warehouse.

Other ways to improve the performance of data warehouse systems include (1) the parallelization of loading patterns and (2) the distribution of data over multiple nodes in MPP settings like in the NoSQL databases. Instead of loading one table after the other, the goal of the first option is to load multiple tables at once. The second option increases performance by the distribution of data to multiple nodes. Both ways are critical to the success of Data Vault 2.0 in such environments and have influenced the changes in Data Vault modeling compared to the initial release (Data Vault 1.0).

Sourcing issues are problems that arise from the system from which the data is extracted. The following are typical examples of problems [19, p16f]:

Transformation issues arise during the transformation of the data to meet the expectations of the target. Often, the following operations are performed directly within the transformation:

The last area of issues is located at the target. These issues arise when loading the data into the target and include:

A common reason for these issues is that many data warehouse systems are trying to achieve too much in one loading cycle instead of splitting up the work. The result is that many loading processes become too complicated, which reduces overall performance and increases maintenance costs. In the end, it also affects the agility and performance of the whole team because they have to fix these issues instead of implementing new features.

Inmon, however, presents reasons for not adding auditability in the data warehouse [9, p61]:

It is our opinion that auditability should be limited to answering questions such as:

The data warehouse should not answer the question of how the data was acquired by the operational system. This answer can often only be provided by the source system itself. Only in some cases, the data warehouse will receive information about the user and time when the record was created or modified. If this data is available to the data warehouse, we tend to store this information for informational purposes only.

To support the auditability of the data warehouse, we add meta-information to the data to track the data source and load date and time. However, it is more complicated to answer the question of where the data was used because data marts often aggregate data to create information to be used by the business users. In order to enable data warehouse maintainers to answer such questions, the data warehouse processes should be simple and easy to understand.

The cost of storage is an often unaccounted for cost factor in data warehousing. At the beginning of a data warehouse project, the costs are typically low. If the data warehouse started as a shadow IT project, i.e., projects driven by the business, implemented by external IT consultants and bypassing internal IT, the costs might have even been hidden in the budget of another project or activity. However, when some time has passed and the amount of data that is processed by the data warehouse has increased, the storage cost increases as well. In some cases, this happens exponentially and does not only include the costs for adding new disks. If more data is added to the data warehouse, faster network access is required to access the data; more computing power is required to process the data; and better (and more expensive) hard disk controllers are required to access the disks [9, p335ff].

However, ever-increasing storage costs are not the big cost factor in data warehousing. Cost factors include:

The cost of low quality and bad planning is an even bigger factor: even if the project team has carefully planned the data warehouse and ensured the quality, there is nothing it can do against changing business requirements, except anticipatory planning [21, p335].

This is particularly evident when the business requirements are located upstream of the data warehouse. As introduced earlier, this not only negatively affects performance, but it also drives up the cost of maintenance. Business requirements should not be embedded in the data warehouse loading cycle but rather should be moved downstream to the data mart loading – closer to the business users. This allows the team to be agile and control maintenance and development costs (through auto-generation) and provides better, more rapid response to changing business requirements. In other words, it controls costs of data mart production as well.

The agility of the team is directly proportional to the amount of complexity built into the data handling processes. By separating the complex business requirements into respective components, multiple loading sections of the architecture become streamlined; to a point where a majority of the implementation can actually be generated. The mechanics of this separation provide for extreme agility when responding to business requirement changes.

In contrast, agile software development methods have been designed to improve software by using customer feedback to converge on solutions [23]. To support this requirement, the data warehouse must be adaptive and resilient to change [12, p3]. A change to the existing data warehouse structures should not invalidate existing data or applications. One of the major advantages of agile methods is the ability to quickly react on business changes, as we will learn in Chapter 3, Data Vault 2.0 Methodology.

To support both the data warehouse engineers as well as the data warehouse business users, a set of tools to query, analyze and present information is required [12, pxxiv]. Examples include reporting tools, query analyzers, OLAP (on-line analytical processing) browsers, data mining tools, etc. Microsoft SQL Server 2014 includes these tools out-of-the-box.

Another business requirement is the ability of the project team to cope with the natural fluctuation of team members. An important success factor in data warehousing is to keep the knowledge and skills of the data warehouse members within the team, regardless of the retirement or withdrawal of key members. Solutions for this include a well-documented data warehouse system and an easily understandable design. Another solution is to use business intelligence (BI) solutions from a major vendor, such as Microsoft, that is well known in the industry and supported by other vendors and consultancy firms [20, p9].

These are major components of Data Vault 2.0, and the innovation contained within. DV2.0 addresses Big Data, NoSQL, performance, team agility, complexity, and a host of other issues by defining standards and best practices around modeling, implementation, methodology, and architecture.

The raw data from the source systems is loaded into the stage area. The goal is to have an exact copy of all data that should be loaded into the data warehouse. The main purpose of the stage area is to reduce the number of operations on the source system and the time to extract the data from it. The tables in the stage area are modeled after the tables in the source system. A stage area is required when the transformations are complex and cannot be performed on-the-fly or when data arrives from multiple source systems at different times [17, p33].

Once the data has been loaded to the stage area, Kimball suggests loading the data into the data warehouse. This data warehouse has been modeled after the dimensional model and is made up of data marts (representing the business processes), “bound together with […] conformed dimensions” [25]. It was first proposed by Kimball in 1996. The dimensional model is a de-facto standard that is easy to query by business users and analytical tools, such as OLAP front-ends or engines. Because it is a logical association of conformed data marts, business rules have to be implemented before the data warehouse layer in order to conform and align the datasets. We will discuss dimensional modeling in Chapter 7, Dimensional Modeling. Data access applications use the dimensional model to present the information to the user and allow ad-hoc analysis.

The advantage of a two-layered architecture is that it is easy to build a dimensional store from the source data as compared to other architectures. However, the disadvantage is that it is more complex to build a second dimensional model from the same source data because the data needs to be loaded again from the staging. It is not possible to reuse existing ETL packages [17, p34f].

This architecture has been introduced by Inmon and introduces an atomic data warehouse, often a normalized operational data store (ODS) between the staging area and the dimensional model. The stage area in this architecture follows that of the two-layer architecture. The data warehouse, however, holds raw data modeled in a third-normal form. It integrates all data of the enterprise, but is still based on physical tables from the source systems. By doing so, it acts similarly to a large operational database.

On top of the normalized view of the business data, there is a dimensional model. Business users can access and analyze the data using subject-oriented data marts, similar to the two-layer architecture. However, it is much easier to create new data marts from the data available in the operational data store because the data is already cleaned and integrated. Therefore, it is not required to perform data cleaning and integration for building new data marts [17, p38]. In practice, two-layer data warehouses often have multiple data marts, serving the requirements by heterogeneous user groups, by providing different subject areas to its users.

However, it is more complex and requires more data processing to build the entire data warehouse, including the operational data store and the dependent data marts. Another problem is that changes to the data model can become a burden if many data marts depend on the operational data store. We will discuss an alternate, three-layer architecture to enable faster changes to the data warehouse in the next chapter.