Data Design Principles

Obey the principles without being bound by them.

- Bruce Lee

Taking a practical approach to developing a well-formed enterprise data warehouse – and by that, I mean one that is accurate, efficient and productive – involves basing it on sound design principles. These principles are specific to each sector of the reference architecture; each of which enables specific capabilities and serves specific functions. Here, I would like to lay out the principles of the Information Warehousing layer’s normalized central repository – the system of record.

The Information Warehousing layer is designed as a normalized repository for the data that has been processed “upstream”. It arrives cleansed, transformed and mastered; and is consolidated here into a single “System of Record”.  The discipline of normalization restructures the data, removing it from the confines of the single perspective of the source system, into the multiple perspectives across the enterprise. The data is modelled according to its “essence” rather than its “use”.

This process of redesign imposes a strict order on the data that promotes data integrity and retains a high degree of flexibility. The way the data is broken apart into separate tables makes it challenging to query, but this is not its main purpose. Data integrity and flexibility are the primary goals, and tuning is geared towards load performance rather than data access.

1. Data Integrity
   Take a proactive stance to protect referential integrity and reduce instances of redundancy or potential for inconsistency.
   
2. Scalability
   Allow for increases in volumes or additional sources of existing information, both within subject areas (e.g., issuers, counterparties) and core concepts (e.g., parties, locations).
   
3. Flexibility
   Allow for additional sources or changes in existing sources, so that design is not tied to a given source or mirrors the source. Design will give primary consideration to reuse, then extension and finally modification of existing structures.
   
4. Consistency
   Apply standard patterns for data design to promote efficiencies of data and ETL design. The decision-making process will be expedited as will data modelling work and ETL development.
   
5. Efficiency
   Focus efficiency on three aspects:
   1. Implementation
      Use of repeatable patterns for data design will minimize data modelling and ETL work effort.
   2. Operation
      Ease ongoing maintenance by keeping the number of data objects to a minimum; maintain consistent standards; and apply logical structures for ease of navigation and use.
      
   3. Load Performance
      Priority given to performance of ETL load processes; including those that use the System of Record as a source to load the Data Mart sector.
6. Enterprise Perspective
   For all data objects, retain and remain open to, a full range of existing and potential relationships between entities to ensure that data reflects an enterprise perspective that is not limited to the perspective of any given project’s requirements.

These foundational principles are implemented through strategies that impact storage of history, hierarchical structures, degree of normalization, classifications, surrogate keys and a number of other aspects of design. The principles form the criteria to judge the best approach to take in a given situation. It’s not always straightforward, even with the principles in place – at times one has to favour one principle over another (e.g., flexibility over load performance), but this list provides the guidance to frame the debate and take a considered approach.

As suggested by the opening quote, I’m not advocating a blind conformance to a set of rules; but, in my experience, one of the greatest obstacles to efficient and effective development is the decision-making process. Limiting the parameters of debate with intelligent guidelines can facilitate decisions being made quickly and correctly.

Feel free to suggest additions or amendments to this list of design principles for the System of Record.

Nine ETL Design Principles

The principles of ETL design define the guidelines by which data migration will be constructed. Below are 9 principles to guide architectural decisions.

1. Performance
In almost all cases, the prime concern of any ETL implementation is to migrate the data from source to target as quickly as possible. There is usually a load “window” specified as part of the non-functional requirements; a duration of time that is available to complete the process. The constraints are based on either the availability of the source system data, the need for the business to have access to the information, or a combination of both.

2. Simplicity
As with all programming, a premium is placed on simplicity of design. This is in the interests of productivity of development time, consideration of ongoing maintenance, and a likely improvement in performance. The fewer steps involved, the less chance of mistakes being made, or places for things to go wrong. When changes need to be made, or fixes applied, the fewer touch points, the better. During the life of the processes, ownership will likely change hands. The system’s simplicity will aid clarity for those who need to take it on.

3. Repeatability
One needs to be able to re-run jobs to achieve consistent and predictable results each time. This means it needs to be applicable to all relevant incoming sources, and in no way dependent on specific time parameters. If sources change, the process needs to handle those changes gracefully and consistently.

4. Modularity
Units of work should be designed with an eye to repeatable patterns, interdependencies and discreet operations that function in isolation. The goal is to have as few modules as possible to be applied as templates to all future development work. This principle assists with clarity and efficiency of design, as well as reusability.

5. Reusability
As a principle, the goal should be not only to repeat modular patterns, but where possible re-use existing jobs and apply parameterization. This optimizes the efficiency of development and reduces the cycles required for testing.

6. Extensibility
Rather than “bring everything” from a given source when a data migration process is first built, it should be possible to include only that which is identified as valuable to the business in the context of a given project or release cycle. Over time, additional data elements from the sources can be added to the ETL jobs, with potentially, new targets. The ETL job should take this iterative approach into account.

7. Revocability
This refers to the ability to reset the database after a run and to return to the state it was in prior to running the process. This will be important for testing cycles during the development process, but also in production, in the event the database becomes corrupted or requires rolling back to a previous day.

8. Subject-orientation
Workloads are to be divided into units based on business subject areas rather than source-system groupings or strictly target table structures. This recognizes that a given source table may contain information about more than one subject area (e.g., Customers and Accounts) In addition, a given subject area may be composed of multiple source tables, which may populate multiple targets. Simply limiting the ETL jobs to a single source and a single target may compromise the other principles, particularly performance and simplicity. Similarly, orienting the ETL jobs to either the source or target layouts may degrade the efficiency of the design.

9. Auditability
It is essential to be able to trace the path that data takes from source to target and be able to identify any transformations that are applied on values along the way.

With these guiding principles, specific strategies can be employed with a set of criteria to judge their applicability.

I would be interested to hear feedback on this list; and am open to any additions.

Reference Domains Part IV: Metadata & Governance

This is the fourth and final part in the series on working with reference domains, also called classifications. The first part provided an overview of their nature, the second recommended an approach to data modelling, and the third explored collecting and documenting them. Here we will discuss metadata related to classifications and how it can be used to assist the governance of content, with particular reference to data quality.

Profiling

Classifications will first be encountered through the analysis process. As the reference domain is identified and the master source of the full list of codes and descriptions is found, it is possible to compare this data against profile results to determine the integrity of the data. Imagine that the field under investigation is the marital status of an individual. The master source reveals that the full list of codes and descriptions include: 1=Married, 2=Single, 3=Divorced. The table-level profile shows that the minimum value is “1″, while the maximum value is “4″. With the profile output stored as metadata, and the classifications loaded into reference tables, it is possible to automatically test that the actual values found in the source are within the range expected in the reference tables.

Similarly, a more detailed test could be run at the column-level, with the frequency distribution output compared against the reference values to check that no aberrant values appear.

Aberrant values could be a sign of integrity issues, or may indicate that additional values need to be added to the reference tables.

In order to make full use of this comparison of reference domain values and profiling results, it is important to collect the external classifications as part of the analysis process. This will allow the team to catch anomalies early and avoid rework.

Data Content Governance

For external classifications there may well be decisions to be made around the collection and consolidation of reference domains. A protocol should be developed to address any issues with inconsistent domains of values. Multiple domains will need to be rationalized into a single set of values that will be acceptable to all lines of business. Care needs to be taken to ensure the most authoritative source has been identified, and that a process is in place to handle change notification. This is particularly important in situations where the source is a hard-coded list drawn from documentation.

Naming Standards Governance

For internal classifications, the content is not subject to content governance so much as the enforcement of naming standards. This is especially important in the naming of relationships, to ensure the nature of the relationship is being accurately described. The time to do this is as the logical mapping document is passed through the screening process to govern all logical names.

Data Architecture Governance

The vast majority of reference domains should pose no challenge to data architecture governance. Most data elements will fit neatly into the simple structures of the Reference Domain and Reference Value tables described in part three of this series. There may be a decision to house long lists of values in separate tables; setting a threshold as assessment criteria. For example, if a classification contained more than 500 values, it would be held in its own reference table. This would be done to help access performance, although it may not be required, and should be tested to determine suitability. If a threshold is used to influence design, the profile results can again be used to programmatically assist the design process.

Likewise, there may be a call to create special structures for classifications that have unique attribution or particular structures. For instance, a set of classifications may form a balanced tree hierarchy that could be usefully held in denormalized structures. Again, these exceptions should be rare; and I would suggest they be avoided, with a premium placed on consistency of design.

Model validation should ensure that the length of the source fields is accommodated by the target reference tables. The table profile results can be referenced to make this determination automatically.

This completes the series on reference domains. Please feel free to provide your feedback. What challenges have you faced with classifications? How did you resolve them?

Reference Domains Part I: Overview of Classifications

Reference Domains, often referred to as Classifications, are essentially the sets of reference codes and descriptions that are used to categorize or classify entities throughout the EDW data model. They can be divided into two main groups:

1. Populated Externally by the Source System
2. Populated Internally by values determined within the EDW

External

By external I mean that the domains of values are contained within the content of the source data, which is outside of the EDW. It may be that the status for a given bank account is held as a coded value on the Bank Account table in the source; while the master list of the codes and descriptions of what those codes mean is held in a different table. In some cases, the list of codes and descriptions will not exist in a source table at all, and must be found in the source documentation. It is also unfortunately sometimes the case that no documented descriptions exist for coded values. It may be necessary to determine descriptions from the context.

 

The classification in all of these cases is external, because the domains of values are explicit within the source system feeding the EDW.

 

Internal

Some reference domains will not come from the source data at all; their determination will be internal to the data model. For example, you may have a party (i.e., a person or a company) with multiple telephone numbers: a home number and a work number, each of which occupies a separate column in the source table. Assume that these telephone numbers are being modelled in a normalized target warehouse; so, the phone numbers are contained in a phone number table, the party is in a party table, and the relationship between the two is in an associative table. The Party to Telephone Number Relationship Type is a classification, the value for which will not be populated directly from the source. The nature of the relationship in the denormalized source table is actually contained in either the name of the column or the column’s description.

 

The classification is internal because the need for a reference domain of the type of relationship is driven by the design of the EDW data model.

 

Structural vs. Descriptive

Another way to group classifications is structural vs. descriptive. Structural values are related to the EDW’s target data structures.  For example, the Party Type is structural, acting as a determinant of whether the party is a person or a company. By contrast, the Marital Status Type of a person would be descriptive, being an attribute of the person. Either of those values could come directly from the source tables; or it’s equally possible that Party Type may be derived from the nature of the source (e.g., a list of employees being entered into Party).

 

Reference domains can be hierarchical, such as Standard Industry Codes. Some classifications seem to extend beyond the conventional pairing of code and description. Frequently, these are masking additional reference domains with related but separate domains of values. It is important to tease out the distinct sets of classifications during the analysis phase of the process.

 

In the following articles we will look at modelling classifications, collecting them, and ways of using them as part of initial quality assurance testing and governance.

In Defense of Surrogate Keys

Employing surrogate keys is an essential strategy to protect the integrity of data assets in the EDW. For those looking to defend such a position against those who would prefer human-readable business keys (a.k.a. natural keys), this article lays out some arguments. I hope by the end the non-believers will be converted, and those who already know the value of surrogates can move ahead without opposition.

Michelle A. Poolet has provided four criteria to determine whether a surrogate key is required:

1. Is the primary key unique?

Where a natural key is not unique, such as a person’s full name, a surrogate key must be used. However, a unique composite alternate key will be needed to match on the record for updates, to attach attribution or establish relationships.

2. Does it apply to all rows?

Take an example such as Customer. Customers may all come from the same source and be loaded into a Party table. However, parties that play different roles and that are loaded from different source systems, may have natural keys of different formats (e.g., company business number, vendor identifier)

3. Is it minimal?

Composite keys and long descriptive fields are obvious candidates for transformation to surrogate keys.(e.g., Postal Address)

4. Is it stable over time?

Identifiers generated by a Master Data Management (MDM) solution are designed not to alter over time. However, if the MDM solution was to change technology, or some sources were to be passed into the EDW through a different channel, the MDM key would be problematic. It is preferable for surrogate keys to be generated internally to the Information Warehousing layer.

Rationale for preference of Surrogate Keys over Natural Keys: 

#	Issue	Natural Key	Surrogate Key
1	Primary and Foreign Key Size	Natural keys and their indexes can be large, depending on the format and components involved in making a record unique.	Surrogate keys are usually a single column of type integer, making them as small as possible.
2	Optionality and Applicability	The population of natural keys may not be enforced in the source data.	Surrogate keys can be attached to any record.
3	Uniqueness	Natural keys always have the possibility of contention. Even where a single source has protected against it, contention may exist across sources.	Surrogate values are guaranteed to be unique.
4	Privacy	Natural keys may be prone to privacy or security concerns.	Surrogate keys are cryptic values with no intrinsic meaning.
5	Cascading Updates	If the business or source changes the format or datatype of the key, it will need to be updated everywhere it is used.	Surrogate values never change.
6	Load Efficiencies	With the exception of very short text values, VARCHAR values are less efficient to load than INTEGER.	Surrogate values can be loaded more efficiently.
7	Join Efficiencies	With the exception of very short text values, joins using VARCHAR values are less efficient than INTEGER.	Surrogate values can be joined more efficiently.
8	Common key strategy	A lack of a common key strategy will lead to longer decision-making process and more patterns for code development.	A common key strategy based on the use of surrogates will streamline design and development.
9	Relationships	Natural keys with different data types will lead to the creation of more objects in the database to accommodate the associations between entities.	The use of surrogates promotes reuse of associative entities for multiple types of relationships.
10	History	Related to points above, the use of natural keys inhibits the flexibility and reuse of objects and patterns to support change capture.	Surrogate keys enable a strategic design to store history using associatives.

The list above is drawn in part from an article by Lee Richardson, discussing the pros and cons of surrogate keys.

In summary, the reasons to employ surrogate keys as a design policy are related to two factors

1. Performance

In the majority of cases the surrogate key will perform more efficiently for both loading and joining tables.

2. Productivity

Even in the minority of cases where a natural key will perform as well as a surrogate, by adopting a consistent surrogate key policy there are benefits to efficiencies gained in design, development and maintenance.

Supporting Quality Assurance

The EDW development life cycle includes a reference to the Quality Assurance process. I’ve outlined some (hopefully useful) strategies to follow when testing the EDW layers, and would like now to touch on how the outputs of preceding work serves as invaluable input to the QA team.

Recently, I have seen different clients use the terms Quality Assurance Testing and User Acceptance Testing to describe the same process, which confirms my suspicion that these terms are rather loosely applied across the industry. Here, I am referring to the process of the Information Technology department testing and confirming the accuracy of data design, ETL processes and business intelligence reporting. This testing is performed by personnel other than the developers who have designed and built those models, processes and reports.

QA teams face challenges understanding the design complexities of EDW data designs and load processes. They can be assisted in their efforts through the work product of other parts of the EDW development group. These are 4 processes which contribute to quality assurance:

• Logical Mapping
• Logical Data Model
• Physical Mapping
• Report Specification

Communication can arrive late if logical mappings, with notes on the sources together with target designs, are not provided early in the development cycle. Some environments develop only physical models, and draft mapping documents after the design is complete. However, this provides an insufficient level of detail to QA, with basic source to physical target mappings, but lacking in design information.

In such cases, communication of additional details is transmitted verbally. This comes with obvious and serious limitations. Details can be missed, miscommunicated, or misunderstood. With no written record, errors can be left unidentified and compounded. I’m not against talking. Keeping lines of communication open is essential; but speech alone is just too ethereal to be relied on as a point of reference.

Logical Mapping

By formalizing the mapping of sources to logical entity and attribute names, the thought processes behind design decisions and potential complexities can surface early. Changes to existing designs can be highlighted, giving QA advance warning of potential regression test requirements. With this input provided up front, QA has the opportunity to plan accordingly and anticipate areas that might require particular attention.

Logical Model

Likewise, the creation of a logical data model brings additional clarity to the QA team, with its full names of entities and attributes and descriptions of fields. QA can use this in the development of test case scenarios, prior to the implementation of the physical model. The nature of the data being tested, the complexity of the relationships between database objects, and the degree of change being applied are made clearer through the logical model.

Physical Mapping

The most important input to QA is the Physical Mapping Document, the target-oriented guideline to populate the EDW. This is where the details of sources and targets are documented and kept up to date. Mappings are included, and so are process dependencies, complex expression logic, table joins, and change capture logic. It forms the basis for the ETL design, but equally informs QA design. What is more, it ensures that ETL and QA are synchronized, working from the same requirements and expecting the same results.

Report Specification

One final piece of information that can add value to the QA process is the collection of metadata that maps data elements to BI reports. This can be compiled as a by-product of the documentation of report specifications. Again, for identification of regression testing requirements, the capability to perform efficient impact analysis will assist QA enormously.

EDW Roles and Responsibilities: Part I

In the next couple of articles, we look at the organizational structure of the EDW development team. This needs to be considered in the context of the development life cycle and areas of governance that I have discussed elsewhere, with particular reference to Release Management.

A few things to note while reading this list:

• Technical infrastructure is not included – this is taken as a given. In my experience, the hardware and software is set up and maintained by a different group, who are not usually involved directly in any design or development decisions. Of course, they are involved when it comes to the underlying technical architecture of the overall solution, and the infrastructure needs to support the flow of data through the warehouse layers.
• The organization tree structure diagram above is conceptual, and intended to represent a generic organization. Some organizations are larger and have many resources, internal and external within any branch. Others have a small core team that does all the development, with a single person handling many roles.
• The main thing to take away from the diagram is that the EDW Program team analyse, data model, ETL and QA their work, while project streams gather requirements, spec and build reports. The EDW Council is a body of higher authority who can arbitrate design decisions and provide governance of the process.

Program/Release Manager

The program manager is responsible for the overall management of the EDW release. This includes the fulfillment of all data requirements, adjusted after identification and analysis of the sources; as well as delivery of the validated, repeatable processes to populate the target databases.

Throughout the release life cycle, the program manager is accountable for all the major milestone deliverables that sit within the responsibility of the EDW development team. This excludes only the detailing of requirements for each project stream and the development of the business intelligence solution that is built on top of the data mart structures.

The program manager must be part of the intake process to determine the scope of each release. To this end, he/she is consulted on the definition of each project’s requirements.

While the program manager is not directly involved in, or responsible for, business intelligence, he/she is kept informed of BI progress to remain responsive to needs that may arise.

Project Manager

The project manager of each stream is directly accountable for the detailed definition of requirements feeding into the EDW, as well as the documentation of BI specifications and the delivery of BI reporting that will draw from the data mart.

The project manager will not be involved in the development of the EDW, but will remain informed of its progress. Early in the process, it is particularly important that the project manager be apprised of data quality issues that arise through identification and analysis of sources. The system of record is not relevant to an individual project, while the data mart will be a critical aspect of the project’s success; and so the project manager is informed of major milestones in the development of the data mart.

Business Analyst

The business analyst is responsible for the compiling the detailed definition of requirements. It is also the business analyst’s responsibility to identify the sources that will fulfill those requirements. It is to be expected that not all analysts will have the business knowledge to complete the first task as well as the technical knowledge to complete the second. This is a case where a single role could be performed by different people.

The business analyst supports processes that involve, or require input from, analysis. This means that may be actively involved in the analysis of profile results and writing SQL queries to interrogate the source data. He/she will find, amend and compose business definitions of fields. When it comes time to map sources to targets, the analyst will be engaged to communicate his/her understanding to the person writing the document. It may even be the case that the analyst’s skills in documentation mean that he/she writes does the writing.

Data content governance involves insight into the nature and quality of the data. To this end, the business analyst supports this governance process. The analyst is also consulted on the information and reporting requirements to determine suitable structures for the data mart.

Data Steward

The data steward is responsible for governing what data will be brought into the EDW, the governance of definitions for each of the data elements, and the identification and definition of business rules to ensure data quality. This means, that while the business analyst will see that definitions are attached to each field, the data steward must ensure that the definitions are complete and accurate.

The data steward supports processes that depend on an understanding of the business meaning of data. This includes the definition of requirements, the identification and analysis of sources, and the mapping of those sources to the system of record.

The data steward also supports naming standards, although, due to each steward being attached to a given subject area domain, or set of domains, they are not responsible for setting or maintaining these standards.

Data Quality Analyst

The data quality analyst is responsible for assessing the quality of data at various layers. Initially, the quality analyst is engaged in the activity of running the data profiling functions and any interrogation queries. While the data analyst may draft queries, it is ultimately the responsible of the quality analyst to ensure that the analyst’s questions get answered. Later in the development life cycle, the data quality analyst is responsible for testing the data populating the system of record and the data mart.

The quality analyst enables data analysis, and so supports the governance of data content in assessing the integrity and suitability of the sources.

The data quality analyst may be consulted on questions arising from mapping sources to the system of record. They may offer technical insights or be able to perform additional quality tests to support the mapping exercise.

The requirements and source fields are an input to the quality analyst’s activities, and so he/she is informed of the results of these processes.

Enterprise Data Architect

The enterprise data architect is responsible for the governance of data architecture, including the establishment of principles and guidelines, as well as reviews and final sign-off of the data models. He/she is also responsible for establishing guidelines for logical and physical naming standards.

The enterprise data architect is consulted on all data modelling activities, relating to both the system of record and the data mart. Data models for any other component of the architect will also pass through enterprise architecture for consultation.

The governance of data content relates to the placement of relevant data within the context of the reference architecture. The governance process involves the consultation of the enterprise data architect.

The requirements and source fields are an input to the enterprise data architect’s activities, and so he/she is informed of the results of these processes.

Data Modeller

The data modeller is responsible for all processes that are directly related to data model artifacts, including the logical mapping of the source to the system of record, logical modelling and physical modelling.

The data modeller supports processes that modelling depends on, including the analysis of the sources through profiling and interrogation; or are dependent on the models, which includes the documentation of the physical mappings to populate both the system of record and the data mart.

The governance of business definitions, naming standards and data architecture all pertain directly to the data models; and so the data modeller supports these processes, by ensuring that all fields contain complete, well-formed and accurate definitions and conform to standard naming conventions, and that the structure aligns with the design standards of the given component within the reference architecture.

The data modeller is informed of requirements and source fields, as these are inputs to the process of designing data structures.

Part II will complete the list of EDW Roles and Responsibilities.

Measuring Success

When measuring success the notion of what value is being offered to the business is always raised. This is fine and well – and should be assessed. However, this is done as part of the business rationale given in the requirements for a project, or even earlier, as part of the project charter. If the requirements of a given project are met, then it follows that the value of that project’s goals are realized. But that doesn’t give the EDW the ability to measure itself. How long is it taking to deliver projects? How do you quantify how much is being delivered? Is the work being done with sufficient accuracy and efficiency? Once it is delivered, is it being used?

EDW management needs a set of measures to determine its own performance; in terms of its own qualitative capabilities, as well as quantitative measures of data quality, process quality and utilization of the system.

Here is a list of potential measures of the EDW; measures that can continue to be applied beyond initial delivery to monitor ongoing performance. Success critieria are broken into capabilities and measures; with the measures being further divided into the sections:

• Data Quality – measuring accuracy
• Process Quality – measuring efficiency
• Utilization – measuring productivity

Capabilities

• Data Quality Assessment
  The ability to assess the accuracy and integrity of data. Data profiling and interrogation capabilities are acquired by setting up a suitable staging area with profiling and SQL query tools.
• Data Lineage
  The ability to track the source of data elements used in reports and found in the various layers of the EDW; as well as make visible the transformations that may be applied along the way.
• Impact Analysis
  The ability to assess the impact of a change in a source data element to EDW objects and processes such as target fields, ETL processes and reports.
• Model Validation
  The ability to assess the validity and completeness of data models with regards to information and reporting requirements.
• Release Management
  The ability to assess the extent to which requirements have been met and at what stage of development each required data element sits; as well as identify overlapping requirements across projects.

Data Quality Measures

• Missing Values Count
  The number of fields that fall below an acceptable threshold of having null values.
• Single Values Count
  The number of fields that fall below an acceptable threshold of having a single non-null value where more than one value is expected.
• Zero Values Count
  The number of numeric fields that fall below an acceptable threshold of having zero or null values.
• Special Characters Count
  The number of alphanumeric fields with one of a set of special characters (e.g., #,$,& etc.)
• Invalid Values Count
  The number of fields with values that are outside the expected range. For classifications, this can be automated with a cross-reference of expected and actual values.
• Invalid Dates Count
  The number of date fields with invalid dates, either malformed, inconsistently formatted or outside the expected range.
• Multiple Domains Count
  The number of fields that contain multiple domains of values; either due to lack of rationalization or overloading.
• Aberrant Records Count
  The number of records that are rejected, for any reason, from the source as they are processed.
• Misidentified Fields Count
  The number of fields, the name and/or definition of which, do not accurately describe their content.
• Orphan Records Count
  The number of records that lack expected relationships to other entities. (e.g., Positions without Securities, Securities without Issuers, Transactions without Stores)

Process Quality Measures

• Malformed Definitions Count
  The number of fields that are blank or have only the logical name as a description.
• Non-standard Names Count
  The number of tables and fields with names that contain components not found in the list of standard abbreviations.
• Source Mappings Count
  The number of required source fields with no mapping to target tables.
• Target Mappings Count
  The number of target fields with no mapping to source fields where a source is expected.
• Productivity Count
  The number of fields added, and populated, to the System of Record  and Data Mart each month. The other EDW component sectors can be tracked in this way as well.
• ETL Performance Measure
  The length of time it takes to load the System of Record and Data Mart. (Use number of records, tables and columns being processed to determine a relative measure.)
• Rework Count
  Number of issue tickets generated. These may further broken down into type and severity.

Utilization Measures

• Reports Count
  The number of reports in active use each period
• Users Count
  The number of users running reports each period.
• Access Count
  The number of access requests (through business intelligence tools) by table each period.

Naming Standards Governance

Naming standards in the EDW apply to full descriptive names as well as abbreviations.

Full descriptive names are commonly referred to as Logical Names, and appear in documentation such as requirements, logical data models and report output. Abbreviations of logical names are referred to as Physical Names, and are applied to physical data models, ETL guidelines, database tables and columns, and report specifications.

The scope of naming standards extends to the determination of:

1. Terminology - with a common understanding of each term’s definition
2. Word Order – placement of words within a name (e.g., “Birth Date” vs. “Date of Birth”)
3. Domain of Class Words – use of a single convention (e.g., “Count” vs. “Number”)
4. Abbreviations – centrally-managed list of standard abbreviations for physical names

Issues

The challenges surrounding both logical and physical names arise from the variety of source systems from which the data is obtained and the disparate user communities who consume it.

With source systems, problems arise with internal inconsistencies in all aspects of naming standards outlined above.  Even more prevalent are inconsistencies across sources from different vendors or systems.

The problem is carried into the EDW database, where the absence of naming standards and a process to govern them leads to inconsistent column names appearing in the tables. Inconsistencies can also appear in reports. Names are displayed in the heading and body of a report. These names are frequently determined by whatever group within the organization has requested the report and defined the requirements.

Solution

The solution is to apply standardization of names where it is practical, and ensure that those standards are followed through the layers of the EDW system to the point they become visible to end users.

Ungovernable Names

Through requirements gathering and analysis of the sources, it may be difficult or impractical to apply naming standards.

In the absence of a corporate standard, it would be difficult to have all requirements conform to a single set of names. In the long term, this may become achievable, as users become accustomed to the conventions and vocabulary incorporated into business intelligence from EDW.

For source systems, either internal or external, standardization of names will likely never be realized. Conventions will remain specific to a vendor or system. In the context of EDW, these names will remain visible only to Information Systems personnel involved in the development and maintenance of EDW.

For these reasons, both the impracticality and the low value to users, the names within these processes can remain ungoverned.

Governable Names

There are 3 points at which it becomes practical to apply governance to names:

1. Logical Source-Target Mapping / SoR Pre-model
2. Determination of Structures / DM Pre-model
3. Report Specification

Logical Source-Target Mapping / SoR Pre-model

This is part of modelling the System of Record (SoR), taking a list of source fields and assigning a placeholder in a target table. This work is done in a spreadsheet, with the target placeholders listed as logical names. Potentially cryptic or inconsistently abbreviated source fields are transposed into fully descriptive and standardized logical names. The logical names can be assessed as a complete list, which will help to determine patterns and identify anomalies.

Determination of Structures / DM Pre-model

As a part of modelling the Data Mart, the dimensions and measures are listed in a spreadsheet. The input to this process is twofold: the reporting requirements that have been gathered, as well as the information requirements that have passed through the SoR. Names that are inherited from the SoR will already conform to the MDS standard. However, there will likely be additional fields, derived from the SoR in some cases, or pulled from other sources, or representing computed or aggregated measures. These names need to be passed through governance as well.

Report Specification

In order for report output to conform to the same naming standards as the MDS database, governance should apply to report specifications as well. Many names will simply be drawn directly from the dimension and fact tables that already conform to the standard. In these cases, non-standard names from the original report requirements should be transposed. There will some cases where reports contain derived values; and in these cases, the new names should be identified, amended as per the standard, and passed through a governance process.

Governance Process

The governance of the standard can be performed in 3 stages of development, before, during and after:

1. Prescriptive: Documented standards that prescribe the various aspects of the names to be applied.
2. Decisive: Logical names and physical abbreviations are approved by a Naming Standards Governance body, potentially a task to be performed by the EDW Council, as described in the discussion on Data Architecture Governance.
3. Approbative: Modelling tools have embedded features to validate the logical word order and use of standard physical abbreviations (applicable to the System of Record and Data Mart models).

The process flow would be as follows:

Data Architecture Governance

In September of 2010, Marlene was a director responsible for the EDW program. She was juggling four or five project streams concurrently, each with overlapping subject areas and different delivery dates. Even though she had half a dozen data modellers available to her within the team, data design always seemed to be the bottleneck. Project managers would come to her complaining that the modellers were delivering late, or were occupied with rework. The business stakeholders were complaining that they couldn’t find the information they were looking for, and when they did, they didn’t trust it. A year into the EDW program with millions of dollars invested, the whole thing looked ready to collapse.

The director put together a task force to investigate the pain points. What they turned up surprised everybody. While the development was focussed on delivering each project’s requirements, it wasn’t keeping an eye on the bigger picture. The data modellers were working independently and when it came time to integrate, invariably realized that the pieces didn’t fit together.

The task force recommended a consolidated approach, with governance of the data architecture a key component. Marlene implemented all the task force’s suggestions. With clarity in the design, a process supportive of integration and a cohesive organizational structure, the bottleneck became an efficient pipeline.

EDW operations ran more smoothly and the conversation Marlene had with business stakeholders turned from fixing problems to enhancing capabilities.

How did Data Architecture Governance help to turn the situation around?

It was incorporated into three stages of the design lifecycle: before, during and after:

1. Prescriptive – What should be done – with design principles and guidelines
2. Decisive- What is being done – with regular review sessions and revision notes
3. Approbative - What has been done – with an approval work flow to authorize or disallow a design

Governance of Data Architecture coexisted with other domains of governance, including Data Content, Naming Standards, Business Definitions and Release Management; and took various forms in artifacts, processes and organizational structures.

Reference Architecture -

This is where proactive governance was applied; providing guidance to the modelling effort.

• The reference architecture was fully documented, including a definition of the purpose, design and content parameters of each data sector.
• An Enterprise Model template was purchased from a vendor specializing in the industry. Alternatively, this could have been developed in-house, but the opportunity cost of re-inventing the industry-specific wheel was too high. Instead, the standard model was customized to reflect the company’s differentiation in the market.

Development Process -

This is where decisive governance was applied; actively monitoring and collaborating in design work.

• Regular Model Reviews were conducted between the data modeller and the Enterprise Data Architect.
• In some cases these were twice weekly, in order to ensure the model was being mapped and revised accurately and consistently.
• The same Data Architect was the central point of contact for all the modellers, and was given the necessary authority to make decisions.
• In the event of a dispute, issues were escalated to the EDW Council.

Release Management -

This is where approbative governance was applied; acting as gatekeeper to ensure the quality of data design.

• The release management strategy centralized the fulfillment of requirements as part of the EDW program.
• Each release incorporated management of the data model artifacts from the various streams of development: comparing, merging and versioning the models.
• A final Model Review was conducted between the data modeller and the EDW Council for authorization.

Organization -

The organizational structure supported the implementation of governance.

• Data modellers were assigned sets of requirements, based on the intake assessment for the given release.
• The Enterprise Data Architect was given responsibility for the oversight of the various streams of data design, with accountability to the EDW Release Manager.
• The EDW Council was made up of the Release Manager, the Enterprise Data Architect, the ETL Architect, the lead Data Steward, and the Database Administrator.