A Question of Scope

Don’t bite off more than you can chew because nobody looks attractive spitting it back out.

- Carroll Bryant

I apologize for the image – but the sentiment is apt. My mother would have said that “my eyes were bigger than my stomach” and admonished me for letting good food go to waste. The problem is that when the size of the project is beyond the budget constraints of the business, or the capacity of the development team, work goes unfinished. And so it’s essential that the scope of the effort be understood before any commitment is made to complete it.

The scope of a data management initiative is influenced by 3 factors:

1. Reference Architecture
2. Source Data Requirements
3. Target Dimensional Breakdown

Reference Architecture

The reference architecture determines the number of areas that need to be modeled and the number of times the data will be migrated from one sector to another. Each sector also has its own degree of complexity – some considerably less than others.

• What is the reference architecture of the target solution?
• How many sectors of the reference architecture are targeted for this initiative?
• Are we building on an existing structure or is this the initial project?
• Do you have existing architecture, naming and content standards?

EDW Reference Architecture

Source Data Requirements

The source data requirements are the list of fields that are to be brought into the target system. It is important to understand that a cost attaches to each one of these fields; and that if one field is being drawn from a source table, it will not be the same level of effort to bring in additional fields from the same table. Therefore, the scoping needs to be based on the number of individual pieces of information, not just the number of source tables.

• How many distinct fields are required? (e.g., Customer First Name, Date of Birth, Industry Classification Code)
• How many different source systems are involved?
• How many different source tables are involved?
• How many fields are being drawn from multiple sources? (e.g., Customer First Name coming from Marketing database and Point of Sale system)

Target Dimensional Breakdown

The target dimensional breakdown determines the breadth of subject areas being modeled, and the potential complexity of the processes involved.

The measures are numeric “counts and amounts” that either come directly from source systems, and so are relatively straightforward, or need to be calculated or derived from component values.

Obviously, these are more complex, and will involve the storage of both components and resulting calculations.

The dimensions give the measures context (e.g, sales by product, balances by branch). The number of different dimensions that need to be modeled and potentially mastered can have a significant impact on development time, particularly when they involve hierarchies.

• What are the measures?
• How many require calculations/derivations vs. coming directly from the sources?
• What dimensions are being requested (customer, employee, store, branch etc.)?
• What hierarchies are being requested?

Once you’ve established the scope of the project, you can estimate the time it will take to develop it. These are important inputs into understanding the order of magnitude of the task at hand; and an essential set of questions to be answered when communicating with the internal development team or a potential vendor.

We all know that scope can creep once the project gets going, and that becomes a management issue; but it’s important to start with a baseline understanding of the size of the task at hand. It helps set appropriate expectations for everyone involved. And it’s good for the digestion.

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 III: Collecting Classifications

This is part three in the series about reference domains (a.k.a. classifications). In the first part we looked at the nature of classifications. In part two, we discussed a recommended approach to designing the target structures in the EDW to accommodate them. Here we will look at the work of collecting and documenting reference domains.

The compilation of a master list of reference domains and their values straddles the analysis and design phases of development. For external classifications, those captured from the source system, the work must be done in the analysis phase, while internal classifications, being dependent on the design of the target data structures, are collected during the design phase.

As mentioned in part one, the external reference domains may first be encountered as code values in a source table; perhaps a status of a bank account, or a categorization of a customer. If you’re lucky, the word “code”, “type”, or “indicator” will appear in the column name. But it’s equally possible that the name will not make it immediately apparent that this is a classification. The analyst will need to determine it from the documentation or from the values contained within the data. Cryptic or abbreviated codes can take the form of numbers or character-based strings. It is important for the analyst to read the clues in the table layout, the documentation and the data to identify potential classifications and then follow up with the data steward or external provider of the source data to find the descriptions that explain the codes.

The code descriptions may accompany the codes in the same source table. However, even if this is the case, selecting a distinct set of the codes and descriptions from the table may not return a full set of potential values, only those that are currently being used. For example, pulling a distinct set of status types from a bank account table may return the values “open” and “closed”, but miss the fact that sometimes accounts are briefly “pending”. For this reason, it’s very important to find either a master table in the source that contains the full set of possible codes and values, or the documentation where the full set is listed. In either case, it’s essential to examine the list of occurring values and compare it against the master. The master may be out of sync with the newest list of allowable values, or there may be aberrant values appearing in the data which will need to be dealt with.

In some cases, no descriptions will exist. Business users may know the meaning of the codes, without requiring the definition. In these cases, it is best practice to create the description and include it in the reference value table. Codes should have explicit descriptions wherever possible, to avoid miscommunication and potential errors. It is a simple matter to misinterpret a status code of “A” as “Active”, when it actually means “Archived”.

Internal reference domains are quite different, in that they are entirely in the control of the EDW design team. The codes and descriptions are all generated and maintained internally. They take the same form of the external classifications, being codes and descriptions, and need to be compiled together with them in a master document and stored in the same reference tables in the EDW.

For compiling the master list, I recommend a pair of spreadsheets, one with a list of the reference domains (e.g., Account Type, Marital Status etc.) and the other a list of reference values for each domain. Here are some points to consider as you create and maintain this list:

1. Manually set the surrogate keys
   Rather than simply pass the setting of surrogate keys over to the ETL process, this master list is an opportunity to control the keys being applied. In most cases, each domain will contain a relatively short list of values (<50) and this will be a simple task. The control and predictability it supplies can be useful for development, testing and usage of the database.
2. Reserve ranges of values for the surrogate keys
   It’s a good idea to leave room between the domains of values to allow the reference domain to grow or be corrected over time. These ranges will not be uniform; one domain will reserve twenty sequential numbers while another will require a thousand.
3. Create dummy values for “Unknowns”
   Many times, incoming records will not find a match to the allowable domain of values. Rather than leave these values null, a surrogate key corresponding to an “unknown” reference value within the domain can be applied.
4. Create dummy values at finer grain
   In some cases it may be prudent to distinguish between different types of “unknown” values. For example, records for which a classification is not required could be marked “Not Applicable”, while an incoming value that is out of the allowable range could be assigned “Out of Range”. When applying this finer grain consideration should be given to what is practical as well as what will add value.
5. Create load-ready spreadsheet
   Some external classifications can be loaded from tables, while others, along with all the internal classifications will need to be manually entered. In both cases, a spreadsheet can be used to control the process. I have seen an implementation where a single spreadsheet contained both manual entries and references to tables, and was used as input to the ETL load process.

In the final part of this series we will look at the collection of metadata related to reference domains and how it can be used to assist the governance of data content.

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.

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.