Bob Lambert

Excerpt from "Show Me the Money: A DM/BI Business Value Primer", Bob Lambert and Tri Truong, Information Management Special Reports, March 24, 2009

Before starting the Business Intelligence business case, the BI advocate should do the homework required to ensure its success, including these essential steps:

1. Know the organization’s goals and objectives.
2. Identify a BI champion.
3. Identify and work with BI stakeholders.
4. Identify an application with tangible business value.
5. Define and quantify a quick win prototype project.

Know the organization’s goals and objectives. It is human nature for any of us, including executives, to be receptive to help with our own goals and objectives but less receptive to new ideas that aren’t related to our own goals. Furthermore, senior executives facilitate intensive strategic planning processes to set the right corporate goals and objectives. A proposed BI initiative should clearly and tangibly help achieve strategic objectives already in place.

Identify a BI champion. BI is in a unique position within the application stack. Most organizations can operate without a BI strategy. However, most companies would greatly improve their market position with a comprehensive BI solution. The impetus for deploying such a solution needs to come from a leader within the corporation who champions the value that BI brings to the organization as a whole. Often, this champion is someone at the top level of the business chain of command with a solid grasp of the BI’s potential.

Identify and work with BI stakeholders. BI projects should be driven by BI stakeholders, those who will see direct effects (good or bad) from the BI project. Some stakeholders look to benefit from BI-based solutions to concrete problems. Other stakeholders will have to be convinced about the potential value of BI. Both types of stakeholder must be involved in defining and supporting the goals of a BI project.

Identify an application with tangible business value. Again, in order for the BI application to return value, it must focus on achieving business goals. These goals should be measurable so that the value of the BI application can be determined, and the application should contribute to overall organizational strategy.  Scroll down to “Business Value Examples” here for more.

Define and quantify a quick win prototype project. Businesses must quickly see the value that BI brings in order for it to catch fire in the organization. A prototype project is often the best way to showcase BI’s value proposition. These projects should typically produce tangible results in a matter of weeks and target a well-defined business area. The prototype should have a well-defined goal and ROI metric, and produce data or case studies that show progress toward, if not achievement of, that goal.

- Thanks to co-author Tri Truong for assistance with this post.

Excerpt from "Protecting Your Data with Row Level Security for SQL Server Databases," March 17, 2009

Data security is not optional in today’s business environment. High-visibility hacking and fraud, Sarbanes-Oxley, HIPAA regulations, and the Patriot Act all reinforce the need to present the right data to the right users and prevent the wrong ones from gaining access. Typically, “row level security” (RLS) is one requirement: to allow or permit access to particular users based on data in a particular database row. SQL Server does not provide built-in row level security.

In an article at Dr. Dobbs I present a way to build your own SQL Server row level security that restricts user access to data based on data in the row, without changing content of business tables, without affecting application or presentation developers, and regardless of how users access the data. Here’s my example application: How to can I add security to my existing orders database that will limit managers to departments they manage — and departments that report to those they manage — regardless of how users get to the tables and what queries and reports are developed against the database?

There are a number of ideas and code samples for hand-built RLS solutions ideas available on the web.  My favorite is A Fairly Capable Authorization Sub-System with Row-Level Security Capabilities (AFCAS) by Kemal Erdogan.  His solution is based on a lookup table, similar to the one I present. His solution is better than many others because it doesn’t involve business table changes, like adding a security code to each row, but still leaves tables unsecured if users access tables directly rather than through a given application.

The essence of the approach is to (1) use cross reference tables linking userid (or Active Directory group id) to business data values, and (2) protecting data tables behind table-valued functions that accept userid as a parameter and join it to the cross reference tables, returning only those rows that the user is permitted to see.

Using this solution can reduce security admin overhead and enable users to get to what they need without putting secure data into the wrong hands.

Here’s a little-recognized fact about data integration: if you run a business or any sizable chunk of one, someone is integrating your data.

In my professional life I have on occasion suggested data integration efforts.  Sometimes my suggestions have been accepted and sometimes not.  As an IT professional I understand that different managers have different priorities, and in a given business situation sometimes other things are more important for example than having a single, consistent source for all customer records, or making sure production data matches financial data.

But as a customer?  That’s different.

A couple of years ago I bought a laptop from a company renowned for quality and customer service.  For the first weeks the computer was all it was cracked up to be, but then it cracked up.  The screen developed a mysterious flicker.  After a few diagnostic conversations they replaced the main logic board.  The problem recurred a few months later, and this time the company traded the lemon for a new computer.

All was well, but here we encounter the first data integration problem: they said I needed to call a different number to have my three-year service agreement transferred, which I did.

Months later I called service for a minor problem, and they had no record of the service agreement for the computer.  My warranty was still connected to the lemon.  After about an hour on the phone this company’s outstanding support staff came up with a more than satisfactory solution.

Even so, this company’s service records weren’t integrated with its warranty records.  In this case data integration happened because of my insistence and the service staff’s creativity.  The cost?  Only considering my last encounter, three service professionals were tied up for about an hour, and I’ll think twice before I buy again from this company.

It seems the choice is either pay now to integrate so all applications work from consistent data or pay later by having staff, customers, and suppliers do it on a case-by-case basis.

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.

Excerpt from "Illusions, Allusions – Let’s Get Real about Database Design", October 4, 2002

Much of today’s IT application development – custom or off-the-shelf – involves integrating data from legacy systems, third- party software products and external data sources such as demographics or mail lists.  More often than not, data integration is unexpectedly complex, either due to data quality issues or the nature of the data integration itself.

Here are some typical examples:

• One ERP package uses the same table for both Sales Quotes and Sales Orders. Columns that mean one thing for Quotes mean quite something else Orders. One team extracting data from this ERP package continually mixed up, for example, Date Received on the Quote with Date Prepared for the Order. The designer who blindly copies data from input systems can propagate these issues. In this case, the correct solution is to extract the two documents into separate tables in the destination system, making each column describe either a quote or an order, not both.
• Marketing databases often store data purchased from several third parties on the same set of customers. These sources usually include overlapping columns with different values. For the same customer, different sources might store different values for the person’s address, credit scores or even name. It is sometimes important to preserve all of the columns from all of the sources and to maintain the information on where the data came from as well as what its value was. This can result in a messy database design, where columns again carry dual meaning: their value and their source.
• Codes from legacy databases tend to evolve into complex forms, embedding more and more information into a single field. This is perhaps a natural reaction to the slow evolution of the system relative to changes in business, as users shoehorn information into the system that it was not designed to store. For instance, in a legacy system a one- character code might classify customers by “customer category,” with values 1 for small business, 2 for mid-size, and 3 for Fortune 5000. Users might add codes 4, 5 and 6 for corresponding values for aerospace customers, then 7 for federal government, and so on. The database designer must know the data well to extract each embedded concept into a different destination column.

When data integration is part of a project, expect complexity and leave room in interface development estimates for devils in the details of source system analysis and integration design.

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

To me, calling someone a “user” contributes to the gulf between IT and other business people.

Most of us don’t think about it and say “user” or “end-user”. Those who do think about it say something like client, customer, or business customer, or something similar.  Sometimes these terms work and sometimes they seem contrived, but there’s a better way.

Lior Arussy of the Strativity Group argues that what we call our customers is critical to effectively serving them (http://www.crmxchange.com/focus_customer/Oct06_2.asp). According to Arussy the problem is that many of us use terms for customers “based on their role in our process, encouraging a “one size fits all” approach that treats all customers the same. The diversity and complexity of customers is subsequently ignored and replaced with a customer that does not exist but who fits perfectly within the organization’s business model. The customer is thus subjected to the company’s processes and efficiency needs rather than the other way around.”

When we in IT call them “users” we do the same thing. Rather than thinking about a business person’s role in a business process, we think about them in terms of how they use “our” system. This encourages us to think narrowly about how, for example, a recruiter uses a personnel workflow system. The word “user” encourages us to disconnect from the business process rather than understanding how the system fits into the hiring process as a whole.

Maybe I’m exaggerating and this one small point doesn’t matter much. However, in most organizations there is a a communications gap between IT and business, and as the service provider it is IT’s responsibility to bridge the gap. Communications gaps are made up of lots of little habits of thinking and practice, and this is one of them.

So what should you call the user of a business application? When I’m talking generally about business people who use IT applications, I use the term business people, which after all is what they are. When I’m talking about the users of a specific system I try to use the term that everyone else in the organization uses for what they do, like “financial analyst” or “recruiter” or “production line supervisor.”

I hope that being specific and correct about business people’s role encourages me to understand the system I’m building in business context, making it easier for me to build the application exactly as they need it.

Free form diagramming doesn’t only mean “no rules”, it also means “just right”.

This post, last in a three part series on free form diagramming, gives some simple guidelines for getting the technique right.  Part one talked about the tension between rigor and expression in diagramming for analysis and design, and how more precise diagrams can hinder rather than help communications with business people.  Part two reviewed free form diagramming in practice.

While it is impossible to specify format and structure of a free form diagram in advance, here are some useful guidelines to follow when building your free form diagram:

•    Rule number one: draw it as you see it. Typically, an analyst uses a free form diagram because he/she already pictures a business process.  Trust your mental picture and get it down on the page.  Then, go through the following checklist to make sure it says what you want it to say.

•    Model real world processes, things, and events. Free form diagrams have one great advantage over Use Case Diagrams, Data Flow Diagrams, and the rest: they are concrete rather than abstract.  For example, in a free form diagram you can symbolize a shopper with a clipart picture of someone choosing a soup can from a grocery store shelf.  The free form diagram should clearly represent things from the real world: organizations, locations, business processes, interfaces, etc.

•    Use symbols consistently. Look at each repeated rectangle, line, circle, icon, etc, and verify that everything with the same shape represents the same type of thing or event.

•    Speak the language of the audience. A free form diagram should depict things business people care about in recognizable terms.  For example, accountants might readily understand boxes labeled GL, AP and AR for general ledger, accounts payable, and accounts receivable.  A shipping clerk might quickly interpret a process illustration showing labeled icons shaped like trucks and warehouses.

•    Arrangement on the page conveys meaning. Frequently, free form diagrams group objects that belong together on the page.  In other cases, the diagram shows a definite process flow by the arrangement of objects.  For example, Business Intelligence Architecture diagrams typically show information flow from source systems on the left to business reporting on the right.  Could this kind of flow or grouping improve your diagram?

•    Limit the number and complexity of objects on the diagram. Most often, a meaningful diagram shows relatively few objects, organizes them in a sensible way, and does not cross lines.  If you need many objects to tell the story, reduce complexity by arranging them logically.

•    Work at one level of detail, or clearly indicate differences between levels of detail. If your diagram includes a box labeled “AP System” then it would not likely make sense for it also to contain another labeled “Journal Voucher Key”.  Diagrams that communicate well are all at the same level of detail or clearly indicate differences in level of detail.

The free form diagram can be an essential part of a successful IT application project by enabling all to understand the target system in the same way and helping business people understand critical functionality.

This is part two of a three part series on free form diagramming for IT projects.  This entry reviews free form diagramming in practice. Part one talked about the tension between rigor and expression in diagramming for analysis and design, and how more precise diagrams can hinder rather than help communications with business people.  Part three will provide a few simple guidelines for getting it right.

Some current development tools and methods do include the free form diagram in their toolbox.  For example, Scott Ambler’s Agile Modeling site includes a page on free form diagrams (http://www.agilemodeling.com/artifacts/freeForm.htm), and the IBM tool XDE has provided an integrated free form diagram (http://www-128.ibm.com/developerworks/rational/library/915.html).

A Free Form Diagram Showing the Broad Outlines of a System

Free form diagrams can be especially useful in illustrating the overall scope of an IT development effort.  For example, XDE product literature cites the free form diagram’s “capabilities to communicate broad ideas about the direction of [a] solution”.  IT software product marketers frequently bear out this advantage over structured modeling with attractive diagrams describing their products and services, as in the diagram above.  Such diagrams enable companies to “level set” the terms of a conversation, providing a reference point for providing more detailed information about the product.

A similar diagram can provide a reference for IT project participants.  One, produced jointly early on in custom development of a financial system by the primary business contact and project architect, helped build consensus with designers, database administrators, programmers, and business people.  Some on the project considered this shared vision of the planned system one key to the project’s success.

Both of these examples illustrate the most common use of the free form diagram: to depict “the fundamental organization of a system [or business function], embodied in its components, their relationships to each other and the environment, and the principles governing its design and evolution” (http://en.wikipedia.org/wiki/System_architecture).

Because the free form diagram can seem more concrete than other, more abstract, models, a well-constructed free form diagram is a great way to communicate complexity to an audience of both technical and non-technical participants.

The free form diagram is also useful in other situations.  Sometimes a requirements team works with business users who have a diagram describing their current process.  In that case the team might use the same format to describe the new process.  In other cases it might be useful to improve readability by drawing a process flow on a map of the shop floor, or using icons representing real objects and events rather than class symbols on a class model.  The point is to create a medium that precisely matches the audience with the message.

Part 3 of the Free Form Diagrams series provides a few simple guidelines for success with this technique.

One effective way of communicating complexity, especially in the overall architecture of a system, is the free form diagram.  A free form diagram can directly address unique characteristics of a system in a way that business people can understand.

Out on a walk some years ago I met an acquaintance who happened to be a professor of Computer Science.  We talked shop (I worked in the IT department of the local electric utility) and he asked me what methods we used for mathematically proving program correctness.  I confess I laughed.  My team was struggling to figure out just what the business really wanted – forget mathematical proofs!

Our conversation highlights the tension between rigor and expression in diagramming to support IT projects.  Developers need precise diagrams rigorous enough to accurately reflect complex processes.  However, that precision and detail can make the diagrams at best boring and at worst intimidating to business people.  Often they don’t have the time or patience required to sit through the explanations needed to understand such diagrams. Requirements meetings frequently end early with business participants walking off grumbling about IT non-alignment.

UML is today’s de facto analysis and design diagramming standard. To the OO professional UML provides a rich, expressive, and consistent set of conceptual tools that continue to evolve.  Work is underway to improve the accuracy and precision of UML with respect to the target code. (http://www.omg.org/docs/ad/00-01-07.pdf).
The problem is that more precision will make the UML diagrams more complex and less understandable to non-coders, making the diagrams even less useful in communication with business people.

For project success business people need to be able to communicate their needs freely and completely.  Requirements analysts need to capture, record, and replay business needs for confirmation, and let the business expert return to other work as quickly as possible.  These communications often work best with pictures rather than words, and of course when everyone understands the picture the same way.  Asking a busy floor supervisor to review a formal class model is literally having him or her review specifications in a foreign language.

One solution is for developers to communicate with business people using free form diagrams, expressive yet rigorous diagrams with drawing conventions tailored to the audience.

More to come in parts two and three of this series.  Part 2 talks about using free form diagrams in practices, and Part 3 provides some simple guidelines for successfully using free form diagrams.