Bob Lambert

I’ve often thought that conceptual data modeling was an underused tool in the arsenal available to requirements analysts, and in a recent conversation I found that many were surprised that it would be used in the requirements phase at all.  Checking the Business Analysis Body of Knowledge (BABOK) I found data modeling listed among the tools available to requirements analysts to “to describe the concepts relevant to a domain, the relationships between those concepts, and information associated with them.”  There’s also Steve Hoberman’s excellent book on the topic, Data Modeling for the Business, an introduction to data modeling aimed at a business audience.

Data modeling has long been one of my requirements analysis tools of choice for custom operational applications.  To me, using data modeling techniques in requirements analysis reduces errors by improving requirements completeness, consistency, and communication, and provides unique continuity between analysis and design.   As David Elliott told me in conversation, “development of a data model uncovers many opportunities for clarification of existing requirements, or uncovering of additional detail.  At the very least, it confirms to one’s business customer that the BSA understands and can graphically demonstrate many business rules and relationships.”

I’ll hasten to add a these caveats.  (1) Perhaps strangely, conceptual data modeling is not useful in the same way in requirements for informational systems like data warehouses and marts (I’ll save that discussion for another post).  (2) Requirements definition for commercial off-the-shelf (COTS) applications follows a different methodology in which data modeling might be less applicable.  (3) This post is not about database design, but rather about use of conceptual data modeling as a tool for organizing and validating requirements.

Conceptual vs. logical data modeling

It is easy to see why in practice there are varying definitions of the different types of data models.  The Wikipedia entry on data modeling reflects the standard terminology based on the ANSI four-level database architecture, but features a confusing diagram that to me blurs the distinction between conceptual and logical models. The entries on Logical Data Model and Conceptual Data Model make them sound like the same thing: implementation-independent representations of business data.  Then, the entry on Database Design contradicts them by stating that the logical model “contains all the needed logical and physical design choices and physical storage parameters needed to … create a database.”

For this post I’ll follow the definitions offered in Simison and Witt’s Data Modeling Essentials:

• Conceptual data modeling identifies a set of data structures that will meet requirements, focusing on business and not on technical or DBMS-specific concerns
• Subsequent logical data modeling maps the conceptual model to structures supported by the particular DBMS, finalizing the design in DBMS-appropriate constructs but not yet optimizing for performance, which comes next in physical modeling

Use of data modeling in requirements definition

Conceptual data modeling is hardly an outlier technique in requirements definition:

• Perhaps in reaction to problems experienced by adopters of Structured techniques in the 80s, data modeling was the cornerstone analytical technique in Clive Finkelstein’s and James Martin’s widely-adopted Information Engineering methodology.
• The BABOK includes class modeling, data modeling’s object-oriented cousin, in its chapter on data modeling.  Class modeling is a core technique of object-oriented analysis.
• Scott Ambler’s Agile Modeling site offers conceptual (or “slim”) data modeling as an option in the initial envisioning stage.
• Informally searching requirements definition templates available on the web, I found that about a third recommend including conceptual data models.

Benefits of data modeling in requirements analysis

The BABOK separates requirements gathering from requirements analysis, defining requirements analysis as an essential step to organize, prioritize, and validate elicited requirements. Elicited requirements are the business objectives of the system. The analysis step organizes those objectives in a way that both makes sense to the business and guides subsequent application design.  Conceptual data modeling in this stage helps ensure requirements completeness, consistency, and communications:

Completeness: In my experience most requirements analysis is process-based, and the most common tool the “swim lane” activity diagram.  While such techniques are essential for understanding complex processes, they can miss requirements that aren’t directly involved in the process itself.  For example, a complex process might reference federal, state, and local tax rates by zip code.  Analysts who are heads-down in defining the process might neglect the need for at least annual refresh of the tax rate tables.  Data modelers thinking in terms of business objects and events and their life cycles would be less likely to miss that one.  This kind of review is formalized in the “CRUD Matrix” a table identifying which business activities create, read, update, or delete which business entities.

Consistency: Another challenge with process-oriented techniques is, for large systems, the risk of inconsistency in definition of business objects and events.  For example, I worked on requirements definition of a specialized order processing system.  Separate sub-teams defined field and headquarters processes, and as a result there were incompatible definitions of critical concepts like “customer” and “order”.  Time pressures made it difficult for the two sub-teams to work together to make their work consistent.  A separate data modeling sub-team can provide a reference point for object and event definitions and promote consistency between separate process analysis teams (on COTS installations the product database itself serves as the reference data model for separate process definition teams).

Communications: Data management professional Peter Carr recounted to me his experience as a consultant on a large project: “the Conceptual Diagram helped us think about all the current state situations, and broadly about the relationships between entities in the organization.  It helped us to ask questions of the business when they were looking to enhance or build new systems to solve business requirements.  Paraphrasing executive colleague Rich Hartt, “the enterprise data model is like a piece of art, it provides a picture into the business that offers new insight through its drawing and interpretation’.  He went on to tell the key business leaders that the enterprise data model will continually be changing, but that it helped them gain understanding of their business in a different way than written business rules”.

For relational database applications, data modeling applies the same conceptual tool throughout the development cycle.  A conceptual data model used to define the problem domain uses the same structure and symbols as a physical database design, although of course it uses fewer.  On the other hand, activity diagrams and data flow diagrams are fundamentally different in nature from the software that they describe.  In effect, process designers need to translate analysis artifacts into a different language.  Logical and physical data modelers use exactly the same language as the business analysts who complete the conceptual data model.

My colleague Grayson Gorman cites “Poorly Defined / Missed Requirements” as a key contributor to IT project failure.  In my view making data modeling a more prevalent part of requirements definition could help by improving requirements completeness, consistency, and communication with business participants, and promote a seamless transition from requirements to design.

Thanks to all who attended my presentations at SQL Saturday on April 10.  Here are the materials from my two presentations:

- The Business End of Data Modeling (2.5m powerpoint presentation)

- Normalize Metadata For Data Integration Analysis (5.5m full version, zip including presentation and code samples)

- Normalize Metadata For Data Integration Analysis (small) (2m reduced size version, graphics removed from ppt file)

Here are some quick notes for those looking to run the Metadata prototype:

The prototype metadata database includes SQL Server 2008 data definition language and data manipulation language (DDL and DML) needed to create the database and populate it with tables and columns from Microsoft’s AdventureWorksDW sample database. It also includes a sample requirements spreadsheet and source-to-target map, and SSIS jobs to load the spreadsheets to corresponding metadata tables. These define fictional requirements and mappings to populate the AdventureWorksDW FACTInternetSales table from tables in the AdventureWorks sample database.

AdventureWorks and AdventureWorksDW are available here: http://msftdbprodsamples.codeplex.com/Wikipage (accessed 4/14/2010)

“Our goals can only be reached through a vehicle of a plan, in which we must fervently believe, and upon which we must vigorously act. There is no other route to success.” – Pablo Picasso

It is an old story: about 30% of IT application projects succeed, 45% are “challenged,” and the other quarter fail altogether.   That’s the consistent result over the years of the Standish Group Study of Project Outcomes.  Jorge Dominguez, here, displays a chart of the remarkably similar results since 1994.  Not a pretty picture, right?  Some question the validity of the Standish studies, but Scott Ambler parallels the Standish story in a recent Dr Dobbs column called “Lies, Great Lies, and Software Development Project Plans,” which itemizes the strategies commonly used by IT project managers to “stay out of trouble” when schedule/budget results don’t match initial estimates.  For example, “18% change the original schedule to reflect the actual results”.

The frequent reaction to stats like these is to scapegoat the IT folks by finding fault with their tools, processes, or skills.  If we just had a more efficient methodology, or a slick development suite, or a more highly skilled team, or a better project process, were more agile, or whatever, then application projects would be on time, resulting systems would be faultless, and we could drive down outrageous IT costs.

Mr. Ambler plays out the IT perspective in this column about Agile project estimation.  Here’s how I paraphrase the gist of his logic: you can’t accurately estimate early on due to normal uncertainty of high level requirements; you will get new requirements that will expand scope.  If you offer a range of predicted outcomes, management will hold you to the most optimistic, which will turn out to be a gross underestimate. Your best strategy is to use an accurate estimation method (net or average velocity, described in the article), and be straight with management even if they “don’t like what they are hearing”.

Based on my experience that sequence of events can be distressingly true to life.  But I’ve been on the other kind of IT project as well, the kind that ends on time, stays on budget, and satisfies the business. The scope of Mr. Ambler’s article doesn’t include asking why the requirements are changing so drastically, but if it had, maybe he would have asked questions like these:

• Does the business unit involved have defined objectives and processes?
• Are strategic and tactical business objectives of the project defined, and do they support the business unit’s objectives?
• Are the critical business success factors of the project defined?
• Does the new project change business processes and if so have the new ones been defined yet?
• Are there documented business requirements and a process for evaluating/approving changes?

Based on what I’ve seen requirements shifts that nearly double the cost of an IT project, like the ones Mr. Ambler describes, result from inadequate business definition and business requirements analysis.  Of course it is true that the “initial high-level requirements and architecture envisioning” early in a typical project only enables an estimate “in the +/- 30% range”.  But effective requirements and architecture definition, when based on effective business definition, can enable a much closer estimate.  Further, it is possible to accurately estimate how long it will take to provide the more accurate estimate.

In some cases the business definition prerequisites clearly don’t yet exist at the outset of an application development effort.  One thing I’ve seen work in such cases is to divide the project into two separate projects: one to closely define the effort, and if needed make up for any lacking business definition, and another to build, test, and install the application. In between the two phases management will have the opportunity to review the costs/benefits of the project and evaluate whether to continue or not.  This post describes one project that successfully used that strategy.

Please don’t read this as repudiation of Agile methods and advocation of “big requirements up front”.  Agile methods work well whether or not business prerequisites are defined, but they seem not to work well when (1) the project’s goals shift with evolving business definition and (2) the plan and budget aren’t adjusted accordingly.

Business definition, completed as part of a discrete requirements phase, that leads to a management decision to continue or not, gives the team the opportunity to build on a solid business foundation.  It also gives management a reasonable estimate and a chance to bail if the project isn’t worth it.

Many see IT as application of technology to solve business problems. 

Of course, this is true but it leaves out the third element, which is to apply the right architectural pattern to solve the problem.  For example, when the business problem is that reporting is slow and reports from different departments don’t match, the astute IT professional immediately thinks in terms of a data warehousing pattern employing technologies like databases, extract-transform-load (ETL) tools, and multi-dimensional reporting suites.

A strategy based on the tools alone may solve the immediate problem, but understanding the solution-pattern enables the IT professional to bring to the business the additional benefits that come with the pattern, organizational and IT support impacts, and any risks that might emerge by applying the pattern.  In the data warehousing case the informed architect might cite improved executive dashboards and ability to drill down to root causes from summary reports, the need for data stewardship, and potential long term increase in data storage capacity needs.

Beyond that, when the architect lacks the pattern approach he or she seems to the business person like Bob Dylan’s debutante: “Your debutante just knows what you need, but I know what you want.” On the projects I’ve been on where designers lacked a pattern-based perspective the technical team did exactly what the business folks said they wanted, but for the most part didn’t contribute to the business value of the solution.  On projects like this developers slavishly ensure the solution matches each and every requirement, rarely bring to the table new business requirements that are logical consequences of the design, and tend to avoid questioning defined requirements even if they are contradictory or counterproductive.

Sure, patterns aren’t strictly necessary.  An outstanding architect can design from whole cloth an original solution that precisely matches business need.  However, that’s not how outstanding architects do business, at least the ones I’ve known.  In my experience the best IT architects know patterns, like data warehousing, SOA, and others, well enough to match the business problem to the right pattern and then evolve the architecture from the generalized pattern into a problem-specific architecture based on the particulars of the business problem at hand.

Some examples of common solution patterns in the world of business IT are Data Warehousing, Master Data Management, and Service Oriented Architecture (the Wikipedia article on this one is preliminary at this writing but still a good intro for the uninitiated).   Those interested in a more technical introduction to patterns might start with Avel Avram’s quick intro at InfoQ (Membership at InfoQ is required but free and worthwhile).

And of course, apologies to Mr. Dylan for the title…

A recurring theme in the literature on IT over the years has been frequent failure of IT projects.  Most studies lay the bulk of the blame on requirements (examples here and here).  One way to improve accuracy and fit-to-purpose of requirements, and thereby promote project success, is to include data analysis as well as process analysis in the requirements plan.

I’ve cited here the need to start data interface analysis early to avoid budget and schedule blow-ups when, as a result of not thinking early about interface complexity, data integration work turns out to be bigger and nastier than anticipated.

Early data study also helps business analysts elicit more detailed and accurate business requirements.  Say a mid-level football (soccer) team in the UK is looking to recruit a couple of strikers who can reliably punch home goals for the club.  The obvious data they seek is (1) the number of goals scored per game by each prospect, and (2) over their careers how much time have they spent on the bench due to injury.  At the same time, this club is building a strategic recruiting system to support growth into the higher echelons of English football.  A process-oriented requirements strategy (like the one described here) asks the team’s recruiters what they need to in order to get good people into the club, and often emerges with a list of statements about what the system will do (”The system shall provide an interface enabling entry of the following player statistics” or “The system shall provide a report ranking players by the following criteria:…”).

It isn’t necessarily wrong to start with process analysis, especially when backed up with formal techniques like use cases, data flow diagramming, or others, but addition of data analysis early provides ability to be far more perceptive into the real business needs.  Without interviewing anyone a data analyst can know that there are many goals in a game of soccer (OK, to some not nearly enough, but that’s another story), that the attributes of a game include location, weather conditions, date and time, whether it’s regular season or playoff, and more.  Attributes of a goal: time during the game; left foot, right foot, or head; did it come from a set play or in the run of play; from the left or right side of the field, and much more.

The analyst who knows the data and understands its structure can probe with questions like whether a player tends to score at the end of games, or would it be useful to find one striker who tends to score from the left side of the field and another who scores from the right?  By understanding the data an analyst can understand the business problem more deeply, build better rapport with business people  by asking more informed questions, and cross the business/IT communications gap to define the right requirements so that the right system gets built.

It may be just the organizations I’ve been exposed to, but in my experience data analysis isn’t typically part of the requirements effort.  Supporting this point, the author of the wikipedia page on business analysis entirely omits data analysis, apparently favoring a process-only approach.  On the other hand, object-based techniques offer a balanced approach, studying both data and process by representing things like goals, games, and players as objects with their own attributes and behaviors.  In addition, the International Institute of Business Analysts (IIBA) includes data-oriented along with process-oriented techniques in its Business Analysis Body of Knowledge (BABOK).

As process/data balance early on in the application lifecycle becomes more widespread analysts should generate more insightful requirements and, other things being equal, the success rate of IT application projects should improve.

In a recent very thoughtful post on data quality, Paul Erb plays out an analogy comparing data users with Don Quixote and data quality professionals with Sancho Panza, then reverses the analogy to cleverly coin the “Sancho Panza” test of data quality professionals.  He encourages data quality professionals promoting the critical role of data quality to apply a what would Sancho say test to ensure that they are aligned with the needs and interests of data consumers.

Here’s Paul’s description of the Sancho Panza test:

Think of Don Quixote [DQ] as the data-quality specialist or even the data management specialist or software vendor, bringing to the world his specialist’s perspective and vocabulary and enthusiasm, influenced by the books he’s read, visioning everyday business practices, with his value added, as goldmines for the organization.  Meanwhile Sancho Panza represents the person who does a practical job every day, who knows what works around here and what doesn’t.

I advocate to Data Quality (let’s call it DQ) consultants that they listen to this Sancho Panza, and consider themselves as Don Quixote.  Sancho doesn’t know much about data, but he knows what he likes… He’s open to listening, but slow to change, and he’ll tell you what he thinks.

Paul’s article reminded me that as a child I thought the problem with Don Quixote was that he tilted at windmills and attempted to ambush acting troupes because of his bad eyesight.  Of course this is not the case, but to me it provides a relevant perspective on data quality in many organizations.

Here’s the problem I’ve seen play out on a number of IT application projects:

1. A high level business study recommends replacement or improvement of a current application.
2. The organization approves the project described in a business case citing benefits named in the business study and costs detailed for infrastructure, package software, and application development, but data-related costs are glossed over or left out entirely.
3. The project begins with a requirements phase that collects hundreds of imperative statements (“The system shall…”)  from business people who will use the system.
4. Late in the requirements phase, the team finds that data integration work in system interfaces will be more complex than expected.  A common example: the project requires changes to a feeder application with no documentation and no in-house support expertise.
5. Project leadership goes back to the sponsor seeking more money.

In these situations the business case was incorrect because it did not account for all of the costs of data integration.  I’ve seen projects weather steps four and five well, but often discovery of previously unseen data complexity starts a disruptive chain of events.  (Sadly for the project manager, such situations are often seen as a failure of project management and corrected accordingly, but that’s a topic for another post.)

In my view the root cause of unforeseen data complexity on projects is the lack of a data constituency in current IT. It is only recently that success of companies like Google and Amazon have motivated emergence of data as a key business resource in the collective consciousness. Famous success stories notwithstanding (see this link), there are relatively few senior IT managers with data quality backgrounds.  Conversely, many rose through the ranks of the infrastructure, application development, or business (process) analysis groups.

It will be a while before, for example, a Mobil CIO’s predecessor jobs include definition of a metadata repository or elimination of multipurpose data, but in the meantime here’s what we can do:  add a business case to the application lifecycle as the last step in requirements.  Stop the project when the real costs are known, recalculate the cost/benefit, and ask the sponsors if the project should continue.  Give Sancho (in this case the project team) a chance to speak to the reality of the situation, and hand to Don Quixote (project sponsors) the eyeglasses of in-depth visibility into real costs. If the decision is to move ahead with the project, then all share the same vision and the sponsors have endorsed the actual project, not the fuzzy image from earlier on that might have been a windmill.

In a new post at Insurance Networking News Ara Trembly provides a balanced perspective on IT/business misalignment (Business/IT Misalignment: Whose Responsibility?).  He describes the problem as cultural, more amenable to relational than management solutions.    His conclusion sums it up: “Take a geek/suit to lunch today!”

To me (speaking as an IT professional) IT should take the initiative to solve the problem.  Quoting Trembly, “business executives … make decisions, but they are for the most part mystified at the magical incantations and actions that produce IT results” and “IT people, on the other hand, are jealous of the sheer power wielded over them by business people who just don’t get IT.”  In other words, business people contend with an emotional and a substantive problem, “fear and lack of knowledge,” while IT people have only the emotional problem of jealousy.

If we take the emotions out of the picture (its just a job, right?) then that leaves IT folks with knowledge that business people need in order to maximize the value of IT and efficiency of business processes.  Ever since mainframes roamed the prehistoric rain forests of the ’60s application developers have often been the most knowledgeable about how business processes really work, understanding both the intricacies of the application logic and how business people use the system to get things done.  These individuals can add value to the business discussion by bringing their knowledge to the table in a way that business people can understand.

In many organizations IT manages the forum in which these conversations can occur: the requirements process.  In my experience a good requirements process is long enough for the business and IT teams to get to know each other, offers generous opportunity for both structured and unstructured conversations about business needs, and brings together knowledgeable business and IT participants.  IT is typically able to bring the insights of seasoned application developers to the fore in a well planned requirements effort.

Yes, everyone has responsibility to “cultivate personal relationships based on mutual need and respect,” but IT can and should bring substance to the relationship in requirements definition.

History is littered with IT application projects that end late, go way over budget, or abandoned altogether.  I was fortunate enough to see one work out really well (almost – please read on).  It was no mistake.  It came down to a simple method advocated by a gentleman named named John Carpenter.

The project was an HR management software conversion from one commercial off-the-shelf software (COTS) package to another.  The company concerned was conservative about spending money.  A previous business case had proposed a similar project.  The problem with that business case was that the benefits were really tough to conceptualize, so the cost/benefit analysis relied on soft benefits like “improved access to information” and “more consistent reporting data”.  The folklore was that the CFO had physically thrown that business case out of his office.

Mr. Carpenter’s method was to divide requirements definition and implementation into two distinct projects, with a different business case for each.  Under his direction, we wrote a ~1m business case for requirements definition only.  We proposed that this first project would result in another business case precisely specifying the schedule, method, cost, and benefits of the implementation project.

According to John, “the approach we used would not be considered a textbook approach for an ERP (enterprise resource planning) implementation.  What we did was more of a strategy to address the the CFO’s concerns.  The company was very risk-averse so we needed a way to take out as much risk as we could.  This was a large project because it involved four major modules affecting the three main areas of HR, and the company wanted to know costs and benefits at each step.  Complicating matters, HR business processes and therefore requirements were not clearly understood – the HR department seemed to rely on on the job training rather than documented procedures.  So we presented the first phase as an investment into understanding HR processes, as well a precise roadmap for implementation.”

This first business case was accepted by that same CFO and we got started on the 7-month effort. We brought in a consulting team experienced in the proposed COTS package, and followed their lead in requirements definition and prototyping.  During the prototyping step they walked HR staff through each relevant function in the software package, detailing how to configure the package for their specific needs and where we’d need to customize it.   The result was a definitive, detailed document that showed how the package fit HR process and how it would need to be customized.  Then, we used those results to build a business case that included specific configuration, customization, hardware, and software costs, as well as the process and organizational changes that would be required, not to mention the benefits that would accrue.  The business case showed substantial improvement, predicting real financial benefits within 4 years.  Even better, on a depreciated basis the project literally was almost free, costing only $1,800 in the first year and returning benefits thereafter.

The business case was accepted by the company’s executive committee and the project started.  It ran exactly as outlined by the results of the requirements effort, with very few of the nasty surprises often typical of large projects, and it tracked to forecast schedule and budget.

Proving that no good deed goes unpunished, the company, whose core business was real estate, in effect folded in the financial crash of last autumn ’09 , one month from implementation.

At any rate, the lesson I took away from the effort was that dividing requirements and development into separate projects gives business visibility into a project, helps manage financial risk, and enables the project to ground predictions rather than guessing at costs and benefits before they can be known.

Recently I was asked for a high level requirements plan for a large IT conversion.  I googled around a little for something standard.  I found some good references (see links at the bottom of this post), but not exactly what I was looking for: a simple, method-agnostic layout of the high level steps and checkpoints in requirements for a big project, emphasizing interactions with business people.   I then rifled my files to find the example below.

This summary plan frames up interactions with business subject matter experts and their review of results.  The table lays out the steps, “granularity” (meaning how often each step is carried out), what comes out of each step, who does the work, and offers a few notes.

Before the table, here are two important definitions:

• Sponsor: Very early on in your project you should identify the one person who you need to make happy in order to succeed.   The bigger the project, the higher up the sponsor.  Keep in touch to make sure they know what’s going on, keep them happy, and if something happens that will make them not happy don’t keep it secret.  Maintain a vibrant risk/issue process so that you can give them early warning of bad possibilities and they can help early.
• Stakeholder: A stakeholder is anyone who will benefit from or be harmed by your project.  Requirements come from stakeholders.  Be sure to build support even with the latter group if at all possible, and at least make the adjustments that will keep them from working to prevent you from succeeding.

Careful, this is just an empty vessel.  Within this high level framework a team can apply whatever requirements techniques they want.  (In fact, I highly recommend structured analysis techniques like use cases or process models, but that’s for the requirements team and this framework is for the PM).  Of course a smaller effort suitable for agile techniques wouldn’t need something like this.  This is for big transitions like conversion to a new COTS package, for example, where it is easy to get lost in the detail.

Hopefully if you’re a PM on a big project you’ll find this framework as useful as I did.

 Here are some of the other references I found along the way, caveat emptor:

• http://www.outsource2india.com/software/RequirementAnalysis.asp
• http://en.wikipedia.org/wiki/Requirements_analysis
• http://www.jiludwig.com/HCI_Journal_article.html