The data warehouse team might not be the right party to decide who will be responsible for the business data but, in any case, it needs to decide what business metadata needs to be managed and where.

The list of business metadata that needs to be tracked includes the following metadata related to business definitions [2]:

This information is required for end-users to understand the information marts they are dealing with; the power users need it in order to find the raw data in which they are interested in the Raw Data Vault; and the ETL developers need this metadata to understand the meaning of the source data when loading the Raw Data Vault and information marts by implementing business rules. In short, these business definitions provide meaning to the source data.

Another set of business metadata includes information about source systems. The following list provides the metadata that needs to be tracked for the data warehouse [2]:

Section 10.2 will demonstrate how to implement these types of metadata with Microsoft SQL Server 2014.

Section 10.2 will demonstrate how to implement technical metadata in the Meta Mart using Microsoft SQL Server 2014.

While the list uses terminology from Microsoft SSIS, it can be applied to any other ETL tool. This kind of metadata should, on process execution, be held in the Metrics Vault and is usually separated from the business and technical metadata in the Meta Mart. Implementing a Metrics Vault and a Metrics Mart are covered in section 10.3. The next section covers implementing the Meta Mart.

By doing so, the toolkit extracts the metadata on its own and populates the Meta Mart of the data warehouse. Features of the toolkit include data lineage, covering business and technical metadata and providing impact analysis [5].

There are three tools included in the toolkit [6]:

The database model which is created by the dependency analyzer is shown in Figure 10.1.

From a modeling perspective, the database model in this figure is not well designed, for example because primary keys are missing. But because it serves only as an example for a Meta Mart, it still presents an acceptable solution. The following tables are present in the database model:

These tables are populated by the Dependency Analyzer tool which might be run from the command line or using the graphical front-end, the Dependency Executor, as shown in Figure 10.2 and Figure 10.3.

Once the Dependency Executor has performed its analysis, the data is available in the tables presented in Figure 10.1. In order to analyze the dependencies between objects, the following query might be used to receive a readable dependency list:

This query uses the ObjectDependencies table as the basis for the query and uses two LEFT JOINS to join the object names for readability. Because of the Dependency Viewer, it is not required to analyze the metadata using SQL queries. Instead, the Dependency Viewer provides a graphical way for the analysis of the metadata stored in the metadata repository (Figure 10.4).

The left tree view of the Dependency Viewer lists all objects found in the Microsoft BI stack. When selecting one of the objects, the object is displayed with all of its dependencies on the central area of the viewer. The bottom area displays the attributes of the selected object for further analysis.

The SQL Server BI Metadata Toolkit provides a powerful example for providing a simple Meta Mart on the technical metadata of the Microsoft BI stack. However, for tracking all artifacts of the enterprise data warehouse, we need to capture more metadata, as described in the previous sections.

The same approach is often used to denote entities in the Data Vault model. There are multiple options available for abbreviating the various Data Vault 2.0 entities and adding them to the actual identifier of the table. For example, many customers use one of the following abbreviations to name a Data Vault hub:

As you can see, many options exist to use a prefix or a suffix for naming a hub entity. Some customers use a prefix, others a suffix. In some cases, they use Camel Case, while in others they use underscores to separate terms in the table name. Because the options for naming conventions are often limited by development standards already available in the organization [2], project teams who start using Data Vault then only have the choice to decide which prefixes to use for each Data Vault entity. They cannot decide whether they want to use a prefix or a suffix any longer, because this decision has already been made a long time ago, by a prior project or, in larger organizations, a standards committee. In this way, organizations try to standardize their table and attribute namings among multiple projects.

The major question that is left to most project teams is instead: which types of Data Vault entities should have their own prefix? Should there be a prefix for every Data Vault entity type? Or are some entity types the same and should use the same prefix in turn? Table 10.1 lists the Data Vault entity types that should have their own individual abbreviations to be used as a prefix or suffix.

The entity types that should have their own prefix are presented in the first two columns of Table 10.1. Because the Business Vault is modeled after the same principles as the Raw Data Vault, there is often an equivalent of a Raw Data Vault entity in the Business Vault. For example, there might be a nonhistorized satellite in the Business Vault that has reduced the transactions for some reason, thus implementing parts of a business rule or the business rule as a whole. There are some exceptions: reference tables are primarily a concept of the Raw Data Vault, so reference data is usually not modified by a business rule (at least not in our experience). On the other hand, computed aggregate links, PIT tables and bridge tables are concepts of the Business Vault. In other cases, there is an equivalent entity in the Business Vault that follows the same modeling principles as its Raw Data Vault counterpart, but using a different name: for example, an exploration link is a computed link without any other modifications. The only difference is that the exploration link is not sourced from an operational data source, but includes links in the model that have been artificially generated (for example through a data mining algorithm). As a result of these observations, organizations tend to introduce an individual abbreviation for Business Vault entities, especially because it helps them to separate raw data from computed information.

In most cases, a different entity structure demands its own abbreviation. However, there are some exceptions to this rule: the same-as-link or the hierarchical link, for example. While they follow the standard structure of a Data Vault link, they serve a special purpose. Therefore, they should have their own abbreviation. The same applies to status tracking satellites or effectivity satellites which follow standard Data Vault structures for satellites as well. One exception to this rule is the nondescriptive link (or fact-less fact). It is a special link, but only because it has no satellites. The link itself, the relationship between two business keys, is the fact. The reason why this link should not have its own abbreviation is that a satellite could be attached to the link later on. For example when data from a new operational system is sourced and, all of a sudden, this source system provides data that describes this link (thus adding a Raw Data Vault satellite to the link). By doing so, the nondescriptive link transforms into a standard link and should use the same abbreviation as the standard links. The same applies to cases when computed satellites are added to the nondescriptive link.

When naming Raw Data Vault satellites, it is recommended to include an abbreviation for the source system because, in many cases, there are multiple satellites hanging off the parent hub or link. If our recommendation from Chapter 4, Data Vault 2.0 Modeling, is followed, each source system should be loaded to its dedicated satellite or satellites. This avoids the drawbacks of overloaded satellites as described in that chapter. But if each source system creates a new satellite on a hub, they need different names to distinguish them from each other, both in a logical view and in physical implementation. The logical diagram in Figure 10.5 shows this issue.

Three source systems provide descriptive data for the passenger. They are abbreviated as TRV (travel system), CRM (customer relationship system) and SEC (security). Using this simple naming convention makes it clear that the table is a satellite, to which parent hub each satellite belongs, and where the data comes from. The next issue is satellite splitting: if there are source columns that change frequently, the recommendation from Chapter 4 is to split the satellites by rate of change. For example, if the satellite SatPassengerCRM in Figure 10.5 is split by rate of change, the logical model shown in Figure 10.6 results from this split.

The preferred dish information was split from the other passenger data because it changes more frequently than, say, the name. In this case, the name of the entity was changed from SatPassengerCRM to SatPassengerPreferredDishCRM because the separated attribute makes it obvious how to name the new satellite. But what if this becomes more complicated because a couple of attributes are separated that don’t necessarily belong together? What if you decide to mechanically separate the attributes of a satellite by some rate of change? To make things more complicated, there are multiple definitions of rate of change as the (incomplete) Table 10.2 demonstrates.

Depending on your understanding of rate of change, one or another (or multiple) definitions might be useful in your project. There should be an abbreviation for each rate of change. For example, if we use the three speed levels slow, medium and fast to denote the rate of change in our satellites, a logical design of our model in Figure 10.5 could look like the design in Figure 10.7.

In this example, the satellite SatPassengerCRM has been split into three new satellites: SatPassengerSCRM, SatPassengerMCRM and SatPassengerFCRM. The character before the source system indicates the rate of change for each satellite.

We call this split a mechanical split because it could also be automated: after analyzing the current rate of change for each attribute in a satellite, the algorithm could generate the required DDLs for the new satellites without knowing any business definition of each attribute (thus unable to find a more meaningful name as in the previous example).

The advantage of using naming conventions for the Data Vault tables is that the information about the entity type is encoded in the table name. This information becomes available in all tools that present SQL Server tables to its end-users. The disadvantage, however, is this metadata is not easily retrievable as dedicated metadata, attached to the entity.

Instead, much more metadata is required to describe the source system for business and IT usage. For example, the fact that data has been sourced from a source system is important in agile projects, because not all available source system tables are loaded to the data warehouse at once. Instead, only the source systems that provide raw data that is required to build a certain report or other information artifact are sourced into the data warehouse (and only partially, table by table). We source data just in time. Identifying the source system tables that have not been implemented in the data warehouse is required because the data hasn’t been loaded in previous sprints. Therefore, these missing source system tables need to be loaded in the current iteration.

Typically, organizations decide to use at least the following metadata attributes to define the source systems sourced by their data warehouse [7]:

Note that some source systems provide data in separate batches that might not be delivered in the same schedule. For example, some data could be delivered on a daily basis, while other data is delivered in real-time. For that reason, the actual data delivery (a data package) should be defined in another metadata table that includes the following attributes [7]:

Note that a data package consists of multiple files or relational tables. Depending on the actual source systems and their intended data package formats, there might be need for more metadata attributes. This list should serve only as a starting point. Chapter 11, Data Extraction, lists more potential source systems and data feeds (such as Web sources, social networks, mainframe systems, etc.) and describes the requirements for sourcing them into the data warehouse.

Note that the local smart keys should only be used under the described circumstances: to apply the format of the smart key to a local business key. It should be avoided to use a more complicated mapping when applying a hard rule, for example to map the business key “12345” to “XYZ”. Because the mapping between these numbers is not a technical mapping but defined by the business (in general), the mapping is actually a soft business rule that should be applied later.

The primary definition of the hard rules for the data warehouse is done using documents that define the hard rules and their applicability. This definition also includes how and where deviations from the expected format and actual errors should be logged. The following metadata attributes should be used to define hard rules:

The above list provides some commonly used metadata attributes. Not all of them are required in all projects and, in many cases, organizations decide to add additional ones that make sense in their context.

Spreadsheets are often used to define which hard rules apply to which source and target attribute mappings. The form in Table 10.4 can be used to provide a detailed definition of a hard rule.

Because hard rules can be applied when loading the staging area and when loading the Raw Data Vault, the definition is referenced in the mapping table for either or both layers. Section 10.2.5 and 10.2.7 show examples for mapping tables that include such references.

For each table in a source system, the following metadata attributes should be managed at a minimum [10]:

Each table consists of multiple source columns and their respective target column mappings, which are described by an additional metadata table [10]:

It is also possible to denormalize these two tables. While adding redundancy, it is often easier to use.

Note that it should be possible to apply multiple hard rules to a source to stage column mapping. If it is not possible, another option is to define a hard rule that packages multiple hard rules, thus applying multiple hard rules to the referencing mapping. However, such an approach requires more hard rules with fewer chances for reuse.

When sourcing hierarchical files (or tables), additional and more complex metadata is required because the goal of a hierarchical mapping is to map each column (or XML attribute and element) to a relational column in the staging area. Loading hierarchical sources requires normalization for that reason, as Chapter 11, Data Extraction, will demonstrate. One approach to define the metadata for hierarchical sources is to include the hierarchical path in the source column physical name and add a target table physical name and target table schema name (if actually required) to support multiple target tables.

Note that the lists of metadata attributes in the following sections are suggestions only. Before maintaining metadata for the data warehouse, the list should be tailored to the actual needs of the data warehouse team. One problem with metadata management is that it often tends to become outdated because many teams stop maintaining the metadata tables. To overcome this problem and speed up the design and development processes of the data warehouse, it is worth investing in modeling and generation tools.

The following definitions also cover only basic Data Vault entities (as covered in Chapter 4). In order to define the metadata for intermediate or advanced concepts, additional metadata attributes might be required.

However, the business changes. And so do the definitions of how the raw data should be modified to create information that is useful for the business, because the business rule definitions document the perception of truth by which the business operates on a day-to-day basis. But this truth changes more often than the market changes, for example when the business learns more about the market.

However, modifying any part of the data warehouse requires changing, or at least testing, all parts downstream of the data warehouse that depend on the modified part. That is the major reason why soft rules should be placed in the architecture as close as possible toward the business, downstream in the data warehouse architecture. Because the definitions of how to modify the raw data to retrieve useful information change so frequently, we need to reduce the number of dependencies on these fluid parts of the data warehouse. And that is why the hard rules described in section 10.2.4 should only modify technical aspects of the raw data, but not the data itself.

When capturing business rules, organizations often depend on free-form descriptions, because the soft rule definitions tend to be complex and require verbal explanations in written form. The following sections will describe some approaches to describing soft rules using text or graphical notations that help the business to define the business rules in an easy to interpret format. Regardless whether the business decides to use an unstructured, free-text format for describing the business rules, some text-based or graphical notation, or a combination of both, in any case, metadata should be captured that describes the collection of soft rules in the data warehouse. Such a metadata table should consist of the following attributes [11,8]:

The data flow name is an optional metadata attribute because most business rules are implemented in virtual business vault entities or information mart entities. In this case, the record source column is used to refer to the rule identifier as Chapter 12 demonstrates. Note the similarity to the metadata attributes for the definition of hard rules in section 10.2.4. The following table lists the possible rule classifications:

The following table provides commonly used rule types:

Again, this table is similar to the hard rule types in Table 10.3. However, Table 10.3 has been condensed to rule types that make sense to hard rules. In contrast, Table 10.11 provides more rule types, because the modification of data is allowed in soft rules.

Similar to metadata tables in other sections of this chapter, the metadata attributes and the values in the tables presented in this section are suggestions only. Each project team or organization has to adapt these lists and come up with their own definitions. Use the information given in this section as a starting point.

Note that this metadata is not only used to describe the detailed metadata for soft business rule execution towards information marts, but also for additional tables in the Business Vault, such as computed satellites.

Table 10.12 shows an example of a Data Vault tables to information mart tables mapping.

There are two dimensional target tables defined: one dimension DimAirline and a fact table FactConnection. The dimension is sourced from a Data Vault hub HubAirline and an accompanying satellite SatAirline. All three source attributes from the hub and the satellite are directly mapped to a target column in the information mart. The soft business rule, which is further defined in SR443.2.1, might change the values from the source before writing the data into the dimension.

The loading process of the fact table, identified as F001, is sourced from a bridge table. In this case, all dimension references, business keys (loaded as dimension attributes) and measures are directly sourced from this source bridge table. The hash keys in the bridge table already reference the later dimension hash keys, which works seamlessly: in the case of Type 1 dimensions, as in Table 10.12, the hash key is directly sourced from the hub. In the case of Type 2 dimensions, it would be sourced from PIT tables. Chapter 14, Loading the Dimensional Information Mart, demonstrates how to load virtual and materialized information mart entities.

Note that there is one set of metadata entries per soft rule. For each soft rule, there are multiple inputs and outputs defined. The relationship between inputs and outputs is multiple to multiple (m:n) with both sides of the relationship being optional. It is possible to define an input to the soft rule that has no direct associated output (for example, because the input is used in a filter condition). Similarly, an output might be defined using a constant value or calculated using a complex formula that might not be traced back to a source column. This is the case for the second Carrier target column of FactConnection in Table 10.12.

The cross-reference table describes which information mart dimension or fact tables are used by a given report or OLAP item. There are three reports in this example: Passenger, Airplane Utilization and Connections. While the Passenger report uses only the Passenger Information table in the information mart, the Connections report uses the Connections and the Airplanes table. These entity names are the logical names; the physical names are also provided as a reference. It also references the requirements documentation. It is also possible to add the attributes in the information mart, similar to the requirements to source table in section 10.2.6.

The major value of this cross-reference is:

While these advantages sound interchangeable at first glance, they are important, in both orders. The Data Vault 2.0 methodology embraces change, like other agile methodologies. Requirements are first affected by such change. A change requirement will affect one or more artifacts in the information marts, such as a dimension in an OLAP cube. If this dimension is modified, it affects all requirements that are related to this artifact. Using this cross-reference table, we can track such dependent changes in the data warehouse.

Without going into more detail regarding these activities, it becomes possible to identify the following metadata attributes that are required to define the security measures:

Table 10.14 provides an example of a data classification matrix that can be used to classify the raw data in the data warehouse:

While Table 10.14 provides a list of data classifications that can be used in data warehouse environments, it is still unclear how these levels can be applied to a Data Vault 2.0 model, among the other metadata attributes.

Classified data might be stored in all Raw Data Vault entities. Business keys might be classified as Table 10.14 shows: a credit card number or social security number by itself is already considered as prohibited and should only be stored in a data warehouse if approved by the organization. In that case, the hub that holds this business key should be elevated to the appropriate sensitivity level, restricted (because the organization allowed the storage of the data in the data warehouse). The same applies to satellites which hold descriptive data that might be classified as restricted or confidential. A best practice is to separate the data by sensitivity level. This can be done using satellite splits. For each resulting satellite, the database engine can enforce separate security controls.

However, the data is not only stored in Raw Data Vault entities. There are dependent entities in the Business Vault and in information marts that provide access to information which is based on the raw data provided by the Raw Data Vault, either in a virtual or materialized form. In both cases, the information should be classified to the same sensitivity level as the underlying raw data. When information is generated from multiple entities in the Raw Data Vault, each of them classified with different sensitivity levels, the most restrictive sensitivity level should be used for the derived information as a whole. For example, if a computed satellite in the Business Vault consolidates raw data from a satellite classified as unrestricted and another satellite classified as confidential, the computed satellite should be classified as confidential as well.

Such a classification of derived entities is not necessary if the grain of data is modified, for example during an aggregation. In this case, the classification of the computed satellite might be actually less restrictive because detailed information is not available anymore [14].

By default, there is no logging defined for a SSIS control flow. In order to set up the destination of log events, select one of the following provider types in the selection box [21]:

In addition, it is also possible to develop a custom log provider [22].

To store the log events in a relational Metrics Vault, choose the SSIS log provider for SQL Server and select the Add… button. A new provider is added to the list below (note that you can configure multiple log providers). Select <New connection…> in the selection box behind the configuration column. This will open the dialog to set up a new OLE DB connection (Figure 10.9).

Once the log provider connection has been set up, check the package in the left tree and activate the log provider in the provider list by activating the check mark on the provider (in front of the row), as Figure 10.10 shows.

After setting up the log provider connection to the Metrics Vault, the event types that should be logged have to be selected on the Details pane of the dialog (Figure 10.11).

The following log event types are available [21]:

In addition to these standard event types, many tasks and containers define additional event types that are raised when something happens inside the task or container [21]. For example, the data flow task provides the following event types [23]:

Whenever one of the selected events occurs, SSIS will send information about the event to the log provider destination. Using the Advanced button, it is possible to configure the attributes per event that should be logged. The following attributes are logged by default and can be configured (deselected) in the dialog [21]:

In addition to these optional attributes, the following attributes are always included in the event information [21]:

Note that the MessageText attribute is often used to encode other, valuable information, such as the records written to the target. When writing the raw data into the Metrics Vault, the MessageText should be split and loaded into separate satellites, following standard Data Vault practices by capturing the raw data in the actual data types of the source system.

Once the event types are selected and the dialog has been confirmed, the selected metrics are captured during execution of the control flow.

In this example, a log user has been created on the database server. When connecting to the database for the first time, SSIS creates two objects in the database:

SSIS will call the stored procedure whenever an event occurs that is configured for logging. Interestingly, the table doesn’t need to exist; only the stored procedure is required. It is also not required that the table be stored in the dbo schema of the database. A modified stored procedure can exist in any schema possible; the only setting that needs to be adjusted is the default schema setting of the logging user. That is the reason why another user has been configured in Figure 10.9. The default schema for this user is set to the log schema where the stored procedure is located.

The default implementation of the stored procedure just inserts the incoming data into the sysssislog table. However, this behavior can be modified to write data into another target or different target structure… the Metrics Vault. The tables shown in Figure 10.12 have been created to capture the events from SSIS.

The central table in the model is a transaction link TLinkEvent. It references four hubs: HubEventType, HubComputer, HubOperator, and HubSource. In addition, it contains an id attribute among some other descriptive attributes that are included in the transaction link. Other descriptive attributes, especially the large attributes such as the message and databytes, are stored in a transaction satellite TSatEvent to keep the transaction link as small as possible. Because the message attribute uses a specific format for some event types, additional satellites have been added to capture the message in the best format possible. For example, TSatDiagnosticExEvent stores the message in an xml field because this event type is using XML formatted messages. On the other hand, many other SSIS event types are using a text-based format, delimited by colons. To support easy access to the elements of the message, TSatOnPipelinePreComponentCallEvent is used to store the separate elements. These satellites are transaction satellites without a LoadEndDate, thus not supporting any updates by inserting new versions of the data and end-dating old records. However, for the given task, which is storing unmodifiable events, these entities are the optimal choice. There is one standard satellite in the model, SatSource, which allows changing the name of a task or component in SSIS. Referential integrity is implemented using foreign keys but disabled.

The transaction link in the Metric Vault implements the structure described in Chapter 5, Intermediate Data Vault Modeling:

Four hash keys are calculated to reference the hubs in the model. The primary key is on the EventHashKey, which is based on the business keys from the referenced hubs and the id attribute which is required for uniqueness of the primary key. The implicit index on the primary key is stored in the INDEX filegroup and the data table itself is stored in the DATA filegroup. Refer to Chapter 8, Physical Data Warehouse Design, for details. The other attributes of the transaction link (executionid, starttime, endtime, and datacode) are included in this table because they are interesting for later aggregations based on the link. Also, they don’t increase the size of the row much. The remaining descriptive attributes given to the stored procedure are stored in the satellite TSatEvent:

Because this satellite is a nonhistorized satellite, it doesn’t contain a LoadEndDate. The implicit index on the primary key is stored in the INDEX filegroup again, while the data is stored in the DATA filegroup. The other satellites are just modifications of this satellite and include only data for specific event types. SatSource, however, is a standard satellite, because it allows and tracks modifications in the source system (SSIS in this case):

Other than the LoadEndDate, the structure is very similar to the other satellites. The next chapter covers how to calculate the hash keys and how to end-date the satellites, which is required for SatSource.

The described model is loaded by modifying the sp_ssis_addlogentry stored procedure. Instead of writing all events directly into the sysssislog table, the events are split into business keys, relationships and transactions, and descriptive attributes and loaded into the tables of the Metrics Vault. Apart from creating a custom log provider in SSIS, this option is the most feasible one to load the Metrics Vault from SSIS.

One final note on the LoadDate in this specific Metrics Vault for SSIS: the recommendation in Chapter 4, Data Vault 2.0 Modeling, is to set the date to a single value for the whole batch in order to identify which records belong together. However, in the case of SSIS logging, each event is reported in a single transaction to the stored procedure. Therefore, each event receives its own load date, following a real-time loading pattern, which is not covered by this book.

In addition to the metrics directly obtained from SSIS using the preceding procedure, more metrics can be retrieved from Integration Services Catalogs, introduced in Microsoft SQL Server 2014. SQL Server maintains this catalog when SSIS packages are deployed to the server, a practice often used in production environments. The following internal views are available and could be integrated into the Metrics Vault [25]:

Using the raw data stored in these catalog views, it is possible to create a comprehensive Metrics Vault that integrates metrics from different sources.

However, both sets of metrics are based on SSIS and don’t include any information about the hardware and network infrastructure. Such data can be obtained from Windows Management Instrumentation (WMI), provided by Microsoft Windows Server. WMI provides information about the server environment, including the hardware and network infrastructure. Table 10.17 lists the WMI providers currently available.

The table also shows the Management Instrumentation (MI) and Open Management Infrastructure (OMI) providers that are available, because they use the same object format. The so-called Management Object Format (MOF) allows querying the data from classes in each provider. Access to the data in these management classes is performed using the WMI Query Language [27]. It is a subset of the ANSI SQL language known from database systems and supported in SSIS by the WMI Data Reader Task in the control flow.

The error output contains all the columns in the default data flow plus some columns that describe the error. Therefore, all error outputs are different from each other. When building the Error Mart, there are two options:

The advantage of the first option is that the data model of the Error Mart is fairly simple. However, it doesn’t allow us to store the raw data that we might need for error analysis. The second option allows us that but the price is to have a lot of fact tables in the Error Mart which must be analyzed individually. For the sake of this chapter, we will describe a solution that implements option 2 because the goal of the Error Mart is to provide detailed information of the errors in the data warehouse. The production of mere statistics (which is the primary result of option 1) should be left to the Metrics Mart, which can keep error statistics as well.

Consider the staging process for master data in Chapter 9, Master Data Management (Figure 10.14).

Using this process, data on regions was sourced from a flat file and loaded into the Master Data Services (MDS). By doing so, data can be loaded into MDS automatically, without typing it in manually using the Web interface or Microsoft Excel. After adding some columns required by the staging process in the second step, the data is loaded into the appropriate staging table for the Region entity in the BTS model.

In some cases, the MDS destination might reject records that don’t fit into the relational structure. These are technical problems that result from errors in the SSIS implementation, unexpected data or other data-related issues (such as transmission errors in the flat file). On the other hand, MDS business rules which might be defined on the MDS entity could be violated as well. For example, there could be a business rule that requires that the abbreviation of the region must be different from the code value of the region. If the staged data from the source violates this rule, the record would be marked by MDS after applying the business rules on the entity. MDS would not reject the data. But it is possible to evaluate the result in the subscription view of the target entity and start appropriate measures to deal with invalid data in MDS.

Assuming that the staging table could reject some of the incoming data if there is a hard, technical error, in order to log these erroneous records into the Error Mart, the data flow is extended by redirecting the error output into another destination (Figure 10.15).

All records that have been rejected by the MDS destination output in the data flow of Figure 10.15 are redirected into the Error Mart Destination. Before doing so, some audit information, gathered from the SSIS engine, is added to the data flow (Figure 10.16).

The Audit Type column provides predefined audit information attributes that can be added to the data flow of the error output. On the left side of the table, the corresponding column names are set. In addition to these columns, more audit information can be added using a Derived Column component added to the data flow of the error output.

Note that the column names use a readable structure with spaces. In actual projects, the column names should adhere to the standard naming conventions of the data warehouse.

The audit columns should be used as conformed dimensions across the Error Mart. However, to keep things simple, the Error Mart in this example doesn’t contain any conformed dimensions, not even dimension tables. All dimensions are directly stored in the fact table, which is not a good practice but works for the purposes of this chapter. A better practice would be to create the following dimension tables:

The remaining audit columns should be directly added to the fact table in a realistic environment. While using dimension tables is the preferred solution, it would require a more complicated loading process to populate the data in the dimensions as well. Therefore, the Error Mart Destination setup becomes fairly simple. First, the connection to the Error Mart has to be set up (Figure 10.17).

The connection directly accesses the ErrorMart database and uses a table or view data access mode to the target fact table. The New button in Figure 10.17 allows one to quickly create the target table, based on the data flow of the error output and the added audit columns (Figure 10.18).

Because it is possible to modify the statement directly in the dialog, it is a good place to apply naming conventions to the fact table name. The last step is to map the columns in the data flow to the destination table, which is shown in Figure 10.19.

Because of the simple structure, only a direct mapping between the input columns from the data flow and the destination columns in the target fact table is required.

To implement this solution with dimension tables (as previously described), multiple options are available:

Which option you choose depends on a number of factors regarding maintainability and programming requirements. Stick to the solution shown in this chapter if storage is negligible compared to the overhead to maintain the dimension tables. Regardless whether dimension tables are created or if the dimensions are integrated in the fact table, analysis can be performed on the fact tables, either directly or using OLAP cubes in SSAS.