This chapter covers the following topics:
Business intelligence (BI), with a focus on BI decision-support applications, such as sales forecasting
The need for and the structure of Business Intelligence Roadmap as a development guide
The 16 development steps applicable to BI decision-support projects
The three parallel development tracks usually followed by BI project teams
Project team structure, including the roles and responsibilities assigned to the core team members and the extended team members
A brief justification for using Business Intelligence Roadmap as your development guide for BI decision-support projects
Business Intelligence Definition
BI is neither a product nor a system. It is an architecture and a collection of integrated operational as well as decision-support applications and databases that provide the business community easy access to business data. Business Intelligence Roadmap specifically addresses decision-support applications and databases.
BI decision-support applications facilitate many activities, including those listed below:
- Multidimensional analysis, for example, online analytical processing (OLAP)
- Click-stream analysis
- Data mining
- Forecasting
- Business analysis
- Balanced scorecard preparation
- Visualization
- Querying, reporting, and charting (including just-in-time and agent-based alerts)
- Geospatial analysis
- Knowledge management
- Enterprise portal implementation
- Mining for text, content, and voice
- Digital dashboard access
- Other cross-functional activities
Examples of BI decision-support databases include the following:
- Enterprise-wide data warehouses
- Data marts (functional and departmental)
- Exploration warehouses (statistical)
- Data mining databases
- Web warehouses (for click-stream data)
- Operational data stores (ODSs)
- Operational marts (oper marts)
- Other cross-functional decision-support databases
Business Intelligence Roadmap is primarily a project lifecycle guide for developing BI decision-support applications using structured data. For BI applications with specialized requirements, such as using unstructured data (e.g., mining for text, content, and voice), building an enterprise portal, or incorporating XML-enabled features and services, you will need to expand the activities and roles in the relevant development steps
BI Decision-Support Initiatives
BI decision-support initiatives are expensive endeavors. Disparate business data must be extracted and merged from online transaction processing (OLTP) systems, from batch systems, and from externally syndicated data sources. BI decision-support initiatives also call for new technology to be considered, additional tasks to be performed, roles and responsibilities to be shifted, and analysis and decision-support applications to be delivered quickly while maintaining acceptable quality.
A staggering 60 percent of BI projects end in abandonment or failure because of inadequate planning, missed tasks, missed deadlines, poor project management, undelivered business requirements, or poor quality deliverables. Project managers need to know the dos and don'ts of BI implementations based on reliable hands-on experience.
What is needed is a new, proven method for understanding and implementing the processes required in the successful deployment of BI decision-support applications.
Development Approaches
Almost every kind of engineering project—structural engineering as well as software engineering—goes through six stages between inception and implementation, as illustrated in Figure 0.1.
As the arrow in Figure 0.1 indicates, engineering processes are iterative. Once deployed, a product is continually improved and enhanced based on the feedback from the business community that uses the product. Each iteration produces a new product release (version) as the product evolves and matures. (This release concept is explained in detail in Step 16, Release Evaluation.)
Stage 1. Justification: Assess the business need that gives rise to the new engineering project.
Stage 2. Planning: Develop strategic and tactical plans, which lay out how the engineering project will be accomplished and deployed.
Stage 3. Business analysis: Perform detailed analysis of the business problem or business opportunity to gain a solid understanding of the business requirements for a potential solution (product).
Stage 4. Design: Conceive a product that solves the business problem or enables the business opportunity.
Stage 5. Construction: Build the product, which should provide a return on investment within a predefined time frame.
Stage 6. Deployment: Implement or sell the finished product, then measure its effectiveness to determine whether the solution meets, exceeds, or fails to meet the expected return on investment.
The Traditional Development Approach
Since BI is an enterprise-wide evolving environment that is continually improved and enhanced based on feedback from the business community, the system development practices of the past are inadequate and inappropriate.
In the past, systems were never designed or built with integration in mind. Every system had a beginning and an end, and every system was designed to solve only one isolated problem for one set of business people from one line of business. The old "single-swim-lane" development practices were suitable for such static stand-alone systems. However, they are not well suited for integrated BI initiatives because the old practices do not include any cross-organizational activities necessary to sustain an enterprise-wide decision-support environment. In the past, cross-organizational activities were not only deemed unnecessary but were also perceived to stand in the way of progress because they slowed down the projects.
For nonintegrated system development, conventional waterfall methodologies are sufficient. They provide enough guidance for planning, building, and implementing stand-alone systems. However, these traditional methodologies do not cover strategic planning, cross-organizational business analysis, or evaluation of new technologies with every project; nor do they embrace the concept of application releases. Traditional methodologies typically start with a functional business need, then concentrate on design and development, and finally end in maintenance, as illustrated in Figure 0.2.
BI applications are mostly driven by business opportunity rather than business need.
BI applications implement a cross-organizational decision-support strategy rather than departmental decision-support silos.
BI decision-support requirements are mostly strategic information requirements rather than operational functional requirements.
Analysis of BI projects emphasizes business analysis rather than system analysis, and analysis is the most important activity when developing a BI decision-support environment.
Ongoing BI application release evaluations promote iterative development and the software release concept rather than big-bang development.
The Cross-Organizational Development Approach
With the expansion of e-business comes an increasing demand for cross-organizational integration. This integration does not refer merely to bridging old systems across different platforms using enterprise application integration (EAI) middleware. Instead, it refers to:
- Information consolidation
- Information integration
- Information integrity
- Seamless business functionality
- Streamlined organizational business processes
Moving an organization from a "single-swim-lane" development approach to a cross-organizational, "cross-swim-lane" development approach requires organizational changes, including a culture shift. No other initiative demonstrates this as vividly as customer relationship management (CRM). If organizations would implement more cross-organizational BI operational applications (front-office as well as back-office) like CRM, they could significantly reduce their construction efforts on BI decision-support applications.
Although in Business Intelligence Roadmap we do not address organizational changes and culture shifts, we do define the necessary BI project activities that support an integrated enterprise-wide infrastructure. Both technical infrastructure and nontechnical infrastructure are required core competencies for cross-organizational integration. In addition to defining project activities, we identify the roles and responsibilities to be assigned to project team members for each development step.
The development steps outlined in this book form an engineering roadmap that provides a framework for developing different kinds of BI decision-support projects. The flexible entry and exit points of this framework allow you to start with any step as long as you meet the "entry criteria" outlined in the Entry and Exit Criteria and Deliverables Matrix. We also designed these steps to be agile and adaptive so that you can organize and manage the development of a BI application as multiple subprojects, each going through several of its own iterations or releases. For example, Figure 0.4 shows two iterations each for the Extract/Transform/Load (ETL), Application, and Meta Data Repository subprojects.
The approach presented in Business Intelligence Roadmap encourages the use of parallel development tracks (subprojects) so that multiple development steps can be performed simultaneously and multiple project activities can occur concurrently. Some project teams may choose to roll up project activities from multiple development steps into one step, while other project teams may not need to perform some steps or activities at all. Figure 0.5 illustrates the dynamics of a typical BI decision-support project, showing several steps running simultaneously (such as Step 5, Data Analysis, and Step 6, Application Prototyping) and multiple iterations of the same step (such as Step 9, ETL Design).
Figure 0.5. The Dynamics of a BI Decision-Support Project
Engineering Stages and the Development Steps
BI projects are organized according to the same six stages common to every engineering project. Within each engineering stage, certain steps are carried out to see the engineering project through to its completion. Business Intelligence Roadmap describes 16 development steps within these stages, as outlined below.
The Justification Stage
Step 1: Business Case Assessment
The business problem or business opportunity is defined and a BI solution is proposed. Each BI application release should be cost-justified and should clearly define the benefits of either solving a business problem or taking advantage of a business opportunity.
The Planning Stage
Step 2: Enterprise Infrastructure Evaluation
Since BI applications are cross-organizational initiatives, an enterprise infrastructure must be created to support them. Some infrastructure components may already be in place before the first BI project is launched. Other infrastructure components may have to be developed over time as part of the BI projects. An enterprise infrastructure has two components:
Technical infrastructure, which includes hardware, software, middleware, database management systems, operating systems, network components, meta data repositories, utilities, and so on.
Nontechnical infrastructure, which includes meta data standards, data-naming standards, the enterprise logical data model (evolving), methodologies, guidelines, testing procedures, change-control processes, procedures for issues management and dispute resolution, and so on.
Step 3: Project Planning
BI decision-support projects are extremely dynamic. Changes to scope, staff, budget, technology, business representatives, and sponsors can severely impact the success of a project. Therefore, project planning must be detailed, and actual progress must be closely watched and reported.
The Business Analysis Stage
Step 4: Project Requirements Definition
Managing project scope is one of the most difficult tasks on BI decision-support projects. The desire to have everything instantly is difficult to curtail, but curtailing that desire is one of the most important aspects of negotiating the requirements for each deliverable. Project teams should expect these requirements to change throughout the development cycle as the business people learn more about the possibilities and the limitations of BI technology during the project.
Step 5: Data Analysis
The biggest challenge to all BI decision-support projects is the quality of the source data. Bad habits developed over decades are difficult to break, and the damages resulting from bad habits are very expensive, time consuming, and tedious to find and correct. In addition, data analysis in the past was confined to the view of one line of business and was never consolidated or reconciled with other views in the organization. This step takes a significant percentage of the time allotted to the entire project schedule.
Step 6: Application Prototyping
Analysis of the functional deliverables, which used to be called system analysis, is best done through prototyping so it can be combined with application design. New tools and programming languages enable developers to relatively quickly prove or disprove a concept or an idea. Prototyping also allows business people to see the potential and the limits of the technology, which gives them an opportunity to adjust their project requirements and their expectations.
Step 7: Meta Data Repository Analysis
Having more tools means having more technical meta data in addition to the business meta data, which is usually captured in a computer-aided software engineering (CASE) modeling tool. The technical meta data needs to be mapped to the business meta data, and all meta data must be stored in a meta data repository. Meta data repositories can be licensed (bought) or built. In either case, the requirements for what type of meta data to capture and store should be documented in a logical meta model. When licensing a meta data repository product, the requirements documented on this logical meta model should be compared to the vendor's meta model, if one is provided. In addition, the requirements for delivering meta data to the business community have to be analyzed (e.g., online help function).
The Design Stage
Step 8: Database Design
One or more BI target databases will store the business data in detailed or aggregated form, depending on the reporting requirements of the business community. Not all reporting requirements are strategic, and not all of them are multidimensional. The database design schemas must match the information access requirements of the business community.
Step 9: Extract/Transform/Load Design
The ETL process is the most complicated process of the entire BI decision-support project. It is also the least glamorous one. ETL processing windows (batch windows) are typically small, yet the poor quality of the source data usually requires a lot of time to run the transformation and cleansing programs. Finishing the ETL process within the available batch window is a challenge for most organizations.
Step 10: Meta Data Repository Design
If a meta data repository is licensed, it will most likely have to be enhanced with features that were documented on the logical meta model but are not provided by the product. If a meta data repository is being built, the decision must be made whether the meta data repository database design will be entity-relationship based or object oriented. In either case, the design has to meet the requirements of the logical meta model.
The Construction Stage
Step 11: Extract/Transform/Load Development
Many tools are available for the ETL process, some sophisticated and some simple. Depending on the requirements for data cleansing and data transformation developed during Step 5, Data Analysis, and Step 9, ETL Design, an ETL tool may or may not be the best solution. In either case, preprocessing the data and writing extensions to supplement the capabilities of the ETL tool is frequently required.
Step 12: Application Development
Once the prototyping effort has firmed up the functional requirements, true development of the access and analysis application can begin. Developing the application can be a simple matter of finalizing an operational prototype, or it can be a more involved development effort using different, more robust access and analysis tools. In either case, the front-end application development activities are usually performed in parallel with the activities of back-end ETL development and meta data repository development.
Step 13: Data Mining
Many organizations do not use their BI decision-support environment to the fullest extent. BI applications are often limited to prewritten reports, some of which are not even new types of reports but replacements of old reports. The real payback comes from the information hidden in the organization's data, which can be discovered only with data mining tools.
Step 14: Meta Data Repository Development
If the decision is made to build a meta data repository rather than to license one, a separate team is usually charged with the development process. This becomes a sizable subproject in the overall BI project.
The Deployment Stage
Step 15: Implementation
Once the team has thoroughly tested all components of the BI application, the team rolls out the databases and applications. Training is scheduled for the business staff and other stakeholders who will be using the BI application and the meta data repository. The support functions begin, which includes operating the help desk, maintaining the BI target databases, scheduling and running ETL batch jobs, monitoring performance, and tuning databases.
Step 16: Release Evaluation
With an application release concept, it is very important to benefit from lessons learned from the previous projects. Any missed deadlines, cost overruns, disputes, and dispute resolutions should be examined, and process adjustments should be made before the next release begins. Any tools, techniques, guidelines, and processes that were not helpful should be reevaluated and adjusted, possibly even discarded.
You do not need to perform the development steps in sequence; most project teams will likely perform them in parallel. However, because there is a natural order of progression from one engineering stage to another, certain dependencies exist between some of the development steps, as illustrated in Figure 0.6. Steps stacked on top of each other in the diagram can be performed simultaneously, while steps that appear to the right or left of each other are performed relatively linearly (with less overlap) because of their dependencies.
Parallel Development Tracks
As illustrated in Figure 0.7, every BI decision-support project has at least three development tracks running in parallel after the project requirements have been defined and before implementation.
The ETL Track
The ETL track is often referred to as the back end. The purpose of this development track is to design and populate the BI target databases. The ETL track is the most complicated and important track of a BI decision-support project. The fanciest OLAP tools in the world will not provide major benefits if the BI target databases are not designed properly or if they are populated with dirty data. The team working on the ETL track is usually staffed with knowledgeable business analysts, experienced database administrators, and senior programmers.
The Application Track
The Application track is often referred to as the front end. The purpose of this development track is to design and build the access and analysis applications. After all, the key reasons for building a BI decision-support environment are to:
Deliver value-added information
Provide easy, spontaneous access to the business data
The team for the Application track is usually staffed with subject matter experts, "power users," and programmers who know Web languages, can effectively use OLAP tools, and have experience building client/server-based decision-support applications that incorporate graphical user interfaces.
The Meta Data Repository Track
Meta data is a mandatory deliverable with every BI application. It can no longer be shoved aside as documentation because it must serve the business community as a navigation tool for the BI decision-support environment. Therefore, the purpose of this development track is to design, build, and populate a meta data repository. The team members are responsible for designing and building the access interfaces as well as the reporting and querying capabilities for the meta data repository. The team working on the Meta Data Repository track is usually staffed with a meta data administrator and developers who have experience with building client/server-based interfaces and are knowledgeable about Web applications.
Figure 0.7. Parallel Development Tracks (for Steps 5–14)
BI Project Team Structure
Every BI project team must have a complementary skill set to perform the necessary activities for the three development tracks. Although each track will have its own subproject team members, from the overall BI project management perspective the BI project team structure contains only two types of teams:
The core team
The extended team
The Core Team
The core team can be thought of as a SWAT team. A project SWAT team is a self-organizing team—the members redistribute the workload among themselves, peer-review each other's task deliverables, make decisions together, brainstorm together, and co-lead the project. The core team has permanent project core team members and permanent step core team members.
Permanent project core team members must be available 100 percent of their time from beginning to end of the BI project to perform project activities applicable to the roles assigned to them. More importantly, they must co-lead the project. The optimum size for this team is four or five people, never exceeding seven people. This team should be staffed with:
- One project manager (not an administrator)
- One representative from the business side
- One business analyst from the information technology (IT) department (either a data administrator or a business liaison)
- One technical person from the IT department (either a senior systems analyst or a senior programmer)
The business person's full-time availability is a critical success factor for all BI projects. If the business executives resist releasing one business person full-time, it indicates that they neither view nor support the BI project as a critical cross-organizational strategic business initiative.
Permanent step core team members must be available 100 percent of their time from beginning to end of those development steps that require their full-time involvement. For example, the ETL lead developer must be fully dedicated to lead the activities of the ETL track.
All core team members brainstorm together, assign work to each other, review each other's deliverables, resolve issues, and make project-related decisions together.
Each person on the core team can and probably will be assigned multiple roles, regardless of whether they are permanent project core team members or permanent step core team members.
Table 0.3 lists the core team roles (in alphabetical order) and their major responsibilities.
The business representative role on the core team is usually assigned to the primary business person representing the business community for whom the BI application is being developed. He or she participates on the project as a full-time member of the project core team. If necessary or desired, this role can be assigned to more than one business person, with the stipulation that every business person will dedicate 100 percent of his or her time to the BI project.
Table 0.3. Core Team Roles and Responsibilities Role
Major Responsibilities
Application lead developer
Designing and overseeing the development of the access and analysis application (e.g., reports, queries)
BI infrastructure architect
Establishing and maintaining the BI technical infrastructure (in some organizations, overseeing the nontechnical infrastructure as well); usually reports to the strategic architect on the extended team
Business representative
Participating in modeling sessions, providing data definitions, writing test cases, making business decisions, resolving disputes between business units, and improving the data quality under the control of the business unit represented by this role
Data administrator
Performing cross-organizational data analysis, creating the project-specific logical data models, and merging the logical data models into an enterprise logical data model
Data mining expert
Choosing and running the data mining tool; must have a statistical background
Data quality analyst
Assessing source data quality and preparing data-cleansing specifications for the ETL process
Database administrator
Designing, loading, monitoring, and tuning the BI target databases
ETL lead developer
Designing and overseeing the ETL process
Meta data administrator
Building or licensing (buying), enhancing, loading, and maintaining the meta data repository
Project manager
Defining, planning, coordinating, controlling, and reviewing all project activities; tracking and reporting progress; resolving technical and business issues; mentoring the team; negotiating with vendors, the business representative, and the business sponsor; has overall responsibility for the project
Subject matter expert
Providing business knowledge about data, processes, and requirements
Some roles can be combined and some are mutually exclusive. For example, one person can perform one of the following combinations of roles:
Application lead developer and ETL lead developer (assuming the person has the different skill sets required for both)
Data administrator, data quality analyst, and meta data administrator (assuming the person has the required technical skills)
Data quality analyst, subject matter expert, and business representative
Mutually exclusive roles, which should never be assigned to the same person, are listed below.
Data administrator and database administrator: The data administrator produces process-independent logical data models, while the database administrator produces process-dependent physical data models (logical database designs). It would be difficult for one person to perform these bipolar activities on the same project, even if the person had the skills to do both.
Project manager and any nonlead role: Managing a BI decision-support project is a full-time job and cannot be put in second position to any development work. One person will simply not have time to both manage the project and do the work.
The Extended Team
The extended team members also have responsibilities on the BI project, but for these members the BI project is not their main priority during the entire project schedule. These members have to schedule time to work with the full-time core team members. They can also be called into sessions when their expertise is needed to resolve a problem or to help make a decision.
Each member on the extended team can be assigned one or multiple roles and is responsible for the activities performed under each assigned role. Table 0.4 lists the extended team roles (in alphabetical order) and their major responsibilities.
As on the core team, some roles on the extended team can be combined and some are mutually exclusive. For example, one person can perform one of the following combinations of roles:
Application developer, ETL developer, and meta data repository developer (assuming the person has the different skill sets required for the three development tracks)
Web developer and Web master
Table 0.4. Extended Team Roles and Responsibilities Role
Major Responsibilities
Application developer(s)
Coding the report programs, writing query scripts, and developing the access and analysis applications
BI support (help desk staff)
Mentoring and training the business staff
Business sponsor
Championing the BI initiative and removing business-related roadblocks for the BI project team
ETL developer(s)
Coding the ETL programs and/or preparing the instructions for the ETL tool
IT auditor or QA analyst
Determining the risks and exposures of the BI project due to internal lack of controls or external forces
Meta data repository developer(s)
Coding the meta data repository migration programs to load the meta data repository database; providing meta data reports and an online help function
Network services staff
Maintaining the network environment
Operations staff
Running the batch processes for the ETL cycles, the access and analysis application, and the meta data repository
Security officer
Ensuring that security requirements are defined and that security features are tested across all tools and databases
Stakeholders (other business representatives or IT managers)
Handling limited responsibilities on the BI project, such as reviewing and ratifying the cross-organizational standards and business rules the BI project team uses or develops
Strategic architect
Managing the overall technical infrastructure for the organization, including the BI technical infrastructure
Technical services staff
Maintaining the hardware infrastructure and the operating systems
Testers
Testing programming code created by the developers from the ETL, Application, and Meta Data Repository tracks
Tool administrators
Installing and maintaining the developer tools and the access and analysis tools
Web developer(s)
Designing the Web site and creating the Web pages for displaying reports and queries on the intranet, extranet, or Internet
Web master
Setting up the Web server and Web security
Mutually exclusive roles, which should never be assigned to the same person, are:
Developer (of any type) and tester: A developer testing his or her own programs is like the fox guarding the henhouse. Even if the developer were motivated to break his or her own code, it is unlikely that he or she would think of all the possible test cases and carry out an objective test plan. However, a developer can take on the role of a tester for another developer's programs, as done in peer reviews and integration testing.
Additional Limited Roles
Other roles participate on the BI project on a limited, as-needed basis.
Data owners are the major stakeholders in any BI initiative. They are responsible for the quality of business data under their ownership and for validating the business meta data.
The facilitator is a third-party participant during post-implementation reviews. His or her responsibility is to lead the review meetings.
The scribe is also a third-party participant during post-implementation reviews. He or she is responsible for taking notes and documenting the meeting minutes and the resulting action items.
The BI Arbitration Board
The discussion on roles and responsibilities cannot end without mention of the BI arbitration board. On cross-organizational BI projects, technical as well as business disputes will arise that neither the core team nor the extended team will be able to resolve. A dispute resolution procedure should be established with guidelines for handling these types of disputes. If a resolution cannot be achieved through other prescribed means, the project team must have access to a body of executives with the authority to be the tiebreaker. This body of executives is the BI arbitration board, sometimes known as the BI steering committee.
BI arbitration boards can be organized in a variety of ways. A BI arbitration board can be a newly created group whose members include the business sponsor, the chief technology/information officer (CTO/CIO), IT managers, the chief operating officer (COO), the chief financial officer (CFO), and line-of-business managers. In some smaller organizations, even the chief executive officer (CEO) could be a member of this board.
In other organizations, the BI arbitration board can be an existing committee. Most organizations already have some official or unofficial executive committee. For example, the CTO/CIO typically meets monthly with the employees who report directly to him or her, and the CEO typically meets monthly with line-of-business executives, the CFO, and the COO. If a separate BI arbitration board cannot be established, then the BI project teams must have access to the existing executive committees.
.Justification for Using This Project Lifecycle Guide
It has been said in the industry that "a paper airplane can be constructed with little forethought, but a jet airplane cannot." Similarly, a stand-alone system that has only a handful of business people using it can get by without a set of carefully planned and executed project activities, but a cross-organizational BI initiative certainly cannot.
As the BI decision-support environment evolves over time, it is imperative that a strong foundation exists to support such expansion. To build a strong foundation, many things have to be considered and many tasks have to be performed by many people. It is irresponsible to casually "make up" who does what and when along the way. That type of ad hoc development approach would put the organization's large investment at risk and would pose an even bigger risk for losing business opportunities. There are quite a few casualties in the trenches of lost opportunities!
The question is not whether or not a set of formalized guidelines must be used but what type of guidelines to use. A waterfall methodology is not suitable for the iterative releases of BI decision-support applications, but an agile and adaptive development guide specifically geared toward BI decision-support applications is. Business Intelligence Roadmap is such a guide.
Business Intelligence Roadmap as a development guide
العرض العادي
