library / lib47ae9d954ec08151
Business Intelligence Guidebook: From Data Integration to Analytics
Rick Sherman · 2014
In a sentence
A comprehensive, vendor-agnostic practitioner's guide to building a sustainable business intelligence environment from data integration through advanced analytics, with emphasis that BI success depends as much on people, process, and politics as on technology.
Business Intelligence Guidebook fills the gap between high-level BI concept books and vendor tool manuals by walking the reader through the entire lifecycle of creating a world-class BI environment: justifying the investment, defining requirements, building architectural blueprints (information, data, technology, and product), designing data models (entity-relationship and dimensional), engineering data integration processes, developing BI applications and advanced analytics, taming data shadow systems, and managing the organizational dimensions of people, process, politics, project management, and centers of excellence. Drawing on decades of consulting and implementation experience, Rick Sherman argues that BI is not a one-and-done project but an evolving program that must be built incrementally and iteratively, grounded in the recognition that raw data must be transformed into clean, consistent, conformed, current, and comprehensive information before it delivers business value. The book is deliberately product-neutral so its concepts endure beyond vendor mergers and hype cycles, and it repeatedly stresses that the hardest part of BI is not technology but the human and organizational factors that determine whether solutions are actually adopted and trusted.
The four lenses
- Science
- Statistics
- Systems
- Strategy
The model
A causal framework expressing how design levers (architectural discipline, data integration rigor, dimensional modeling, tool-based development, requirements/expectation management, training, and organizational governance) drive psychological and behavioral states (trust in information, adoption, business-IT collaboration) and contextual conditions (information quality—the five Cs—and silo proliferation), which in turn drive outcomes (business value/ROI, project success, productivity). The model is inferred from the book's repeated thesis that information quality and people/process/politics, not technology alone, determine BI outcomes.
Architectural Disciplinedesign lever
The degree to which the enterprise designs and follows an overarching four-layer architectural framework (information, data, technology, product) before selecting products, avoiding the accidental architecture and silos.
Data Integration Rigordesign lever
The extent to which data integration is engineered holistically with standards, reusable components, tools, documentation, and auditability rather than hand-coded extracts, representing the largest share of BI work.
Dimensional Modeling Qualitydesign lever
The soundness of data design using dimensional and hybrid dimensional-normalized models, conformed dimensions, surrogate keys, slowly changing dimensions, and appropriate schemas to support analytics.
Tool-Based Development Adoptiondesign lever
The degree to which BI, data integration, and database tools (selected as best fit for needs, skills, and budget) are used rather than ad hoc manual coding, enabling productivity, governance, and reuse.
Requirements and Expectation Managementdesign lever
The thoroughness of defining business, data, quality, functional, and technical requirements and the active setting and managing of realistic stakeholder expectations through justification, roadmaps, and communication.
Training Investmentdesign lever
The investment in vendor-agnostic foundational BI training, tool-specific training, and use-case-based training for both business and IT people.
Organizational Governance and Sponsorshipcontextual condition
The presence of executive sponsorship, data governance, project/program management discipline, and centers of excellence that manage people, process, and politics across the enterprise.
Information Quality (Five Cs)contextual condition
The degree to which delivered information is clean, consistent, conformed, current, and comprehensive, representing the central contextual condition that determines whether data has business value for analysis and decision-making.
Data and Application Silo Proliferationcontextual condition
The extent to which disconnected data, application, and reporting silos (including data shadow systems and spreadmarts) accumulate across the enterprise, producing inconsistent and incomplete information.
Trust in Informationpsychological state
The psychological state of business people having confidence in the data and analytics they consume, free from debate over whose numbers are correct.
BI Adoption and Usagebehavioral pattern
The behavioral pattern of business people actively using BI applications in their jobs rather than reverting to spreadsheets or data shadow systems.
Business-IT Collaborationbehavioral pattern
The behavioral pattern of business and IT groups working as partners with open communication, shared accountability, and active business participation throughout the BI lifecycle.
Business Value and ROIoutcome metric
The outcome of BI delivering tangible business benefits—revenue optimization, cost reduction, risk reduction, improved decision-making—producing return on investment.
BI Project Successoutcome metric
The outcome of BI projects being delivered on time, within budget, with quality, and—most importantly—meeting stakeholder expectations.
Business and IT Productivityoutcome metric
The outcome of business people shifting time from data gathering and reconciliation to analysis, and IT shifting from maintenance to expanding BI value.
How they connect
- architectural discipline − influences silo proliferation
- data integration rigor → predicts information quality five cs
- dimensional modeling quality → influences information quality five cs
- tool based development → influences data integration rigor
- information quality five cs → predicts trust in information
- silo proliferation − influences information quality five cs
- trust in information → predicts bi adoption
- requirements and expectation management → predicts project success
- business it collaboration → influences requirements and expectation management
- training investment → influences bi adoption
- organizational governance − influences silo proliferation
- organizational governance → influences business it collaboration
- bi adoption → predicts business value roi
- project success → predicts business value roi
- information quality five cs → influences productivity
- silo proliferation − influences productivity
- data integration rigor − influences silo proliferation
The process
The book's operating playbook outlines a comprehensive, enterprise-level approach to Business Intelligence (BI), treating it as a strategic program rather than a series of tactical projects. The journey begins with a formal justification and requirements definition process, ensuring any BI initiative is firmly grounded in business value and has clear, agreed-upon objectives. This planning phase establishes a multi-year BI road map and sets the stage for execution. The core of the playbook is a structured, phased project management framework that orchestrates the entire development lifecycle. Within this framework, the book details specific, iterative processes for designing the foundational data architecture through dimensional modeling, building robust data integration workflows to create a 'single version of the truth,' and developing user-centric BI applications for analysis and reporting. Prototyping and continuous feedback loops with business stakeholders are emphasized throughout the build phases to ensure the final solution meets evolving needs. Supporting this core lifecycle are processes for managing the human and political elements of BI, such as establishing a Center of Excellence and implementing data governance. The playbook also provides pragmatic methods for evaluating and selecting technology and for systematically addressing the common problem of 'data shadow systems' by renovating or replacing them. Together, these processes form a holistic methodology for transforming an organization's raw data into a managed, actionable information asset that drives informed decision-making.
Justify and Define a BI Initiative
To build the business and technical case for a BI project, define its scope, secure approval, and detail its requirements to ensure it addresses clear business needs and sets realistic expectations.
When to use: When an organization identifies a need for a new BI capability or wants to formalize its BI efforts.
Step 1Build the business case.
Entry: A perceived business problem or opportunity that BI could address has been identified.
Exit: A documented business case with sponsorship and stakeholder agreement is complete.
In: Organization's strategic initiatives, List of business process bottlenecks · Out: Documented business case, Committed business sponsor(s)
Step 2Build the technical case.
Entry: The business case has been established.
Exit: Agreement from key business and IT stakeholders on the proposed technical direction.
In: High-level business requirements · Out: Technology short lists, Stakeholder buy-in for the technical approach
Step 3Assess organizational readiness.
Entry: Business and technical cases are drafted.
Exit: A readiness assessment identifying key risks and mitigation strategies is complete.
In: Source system information, Organizational charts · Out: Readiness assessment document
Step 4Create a high-level BI road map.
Entry: Business needs and organizational readiness have been assessed.
Exit: A documented BI road map showing a long-term vision is created.
In: List of prioritized business initiatives · Out: BI Road Map
Step 5Develop the preliminary scope, plan, and budget.
Entry: The BI road map is established.
Exit: A preliminary project charter with scope, plan, and budget is drafted.
In: BI Road Map · Out: Preliminary project scope, plan, and budget
Step 6Define detailed requirements.
Entry: The preliminary project scope has been defined.
Exit: A consolidated requirements document is drafted.
In: Interview notes, Existing reports and spreadsheets, Source system documentation · Out: Detailed requirements documentation
Step 7Prioritize requirements and obtain approval.
Entry: Detailed requirements have been documented.
Exit: A signed-off requirements document and approved project charter are complete.
- Adjust scope, budget, or schedule if not all 'must-haves' can be met.
In: Consolidated requirements document, Preliminary plan and budget · Out: Finalized project scope, Approved project charter and budget
Execute a BI Project
To manage a BI project from inception to deployment using a structured, phased approach that orchestrates all design, development, and testing activities.
When to use: After a BI initiative has been justified and approved for execution.
Step 1Phase 1: Scope and Plan.
Entry: The BI initiative has been approved and funded.
Exit: A detailed project plan, including a Work Breakdown Structure (WBS), is complete and the team is assembled.
In: Approved project charter · Out: Detailed project plan, Assembled project team
Step 2Phase 2: Analyze and Define.
Entry: The project plan is approved.
Exit: A complete and signed-off requirements specification document is available.
In: High-level requirements from project charter · Out: Detailed requirements specification
Step 3Phase 3: Architect and Design.
Entry: Detailed requirements are defined.
Exit: Architectural blueprints and detailed data models are complete.
In: Detailed requirements specification · Out: Information Architecture, Data Architecture (including data models), Technical Architecture
Step 4Phase 4: Build, Test, and Refine.
Entry: Architectures and designs are complete.
Exit: All data integration jobs and BI applications are developed and have passed unit testing.
In: Architectural blueprints, Data models, Source data · Out: Developed data integration processes, Developed BI applications
Step 5Phase 5: Implement.
Entry: Development and unit testing are complete.
Exit: The solution has passed all testing cycles and has received UAT sign-off.
In: Developed code and applications · Out: Tested and validated BI solution, UAT sign-off
Step 6Phase 6: Deploy and Roll Out.
Entry: UAT sign-off has been received.
Exit: The BI solution is live in production and being used by the business community.
In: Validated BI solution · Out: Live production BI system, Trained user base
Design Data Architecture and Models
To create the conceptual, logical, and physical data models that define the data structures (e.g., data warehouse, data marts) for the BI system, with a strong emphasis on dimensional modeling.
When to use: During the 'Architect and Design' phase of a BI project, after business and data requirements have been defined.
Step 1Create the Conceptual Data Model.
Entry: Business and data requirements have been gathered.
Exit: A conceptual data model that communicates the overall data structure to business and IT stakeholders is complete.
In: Business requirements document · Out: Conceptual Data Model
Step 2Create the Logical Data Model using Dimensional Modeling.
Entry: The conceptual model is complete.
Exit: A logical dimensional model defining all facts, dimensions, attributes, and relationships is complete.
- Choose between a star schema (preferred) or snowflake schema for dimensions.
In: Conceptual Data Model, Detailed data requirements · Out: Logical Dimensional Model
Step 3Apply advanced dimensional modeling techniques.
Entry: A basic logical dimensional model exists.
Exit: The logical model is robust and handles historical tracking and complex relationships.
- Select the appropriate SCD type (e.g., Type 1, 2, 3, 6, 7) for each dimension based on business requirements for historical analysis.
In: Logical Dimensional Model · Out: Advanced Logical Dimensional Model
Step 4Create the Physical Data Model.
Entry: The logical model is complete and the target database technology is known.
Exit: A physical data model that can be used to generate the database schema is complete.
In: Advanced Logical Dimensional Model, Database technology specifications · Out: Physical Data Model, Database creation scripts (DDL)
Design and Develop Data Integration (DI)
To build the automated processes (ETL/ELT) that extract data from source systems, transform it according to business rules, and load it into the target BI data stores like the data warehouse and data marts.
When to use: During the 'Build, Test, and Refine' phase of a BI project, after the data models for the target data stores have been designed.
Step 1Define Data Integration Architecture and Requirements.
Entry: Target data models are designed and source systems are identified.
Exit: A complete understanding of source data and a high-level DI workflow are established.
In: Target data models, List of source systems, Data requirements document · Out: Data profiling reports, DI Architecture document
Step 2Design Data Integration Processes.
Entry: DI architecture and source data analysis are complete.
Exit: Detailed DI design specifications, including source-to-target mappings, are complete.
In: DI Architecture document, Data profiling reports, Source and target data models · Out: Source-to-target mapping documents, DI process models
Step 3Develop Data Integration Components.
Entry: DI design specifications are complete.
Exit: DI jobs are coded and have passed developer unit tests.
In: DI design specifications, Sample source data · Out: Developed DI jobs/code
Step 4Address Historical Data Loading.
Entry: Requirements for historical data are defined.
Exit: Historical data is successfully loaded and validated in the target data stores.
In: Historical source data · Out: Historical data load processes
Step 5Test Data Integration Processes.
Entry: DI jobs have been developed.
Exit: All DI processes are tested, validated, and ready for deployment.
In: Developed DI jobs, Test data sets · Out: Test results documentation, Validated DI jobs
Design and Develop BI Applications
To design and build the front-end reports, dashboards, and analytical applications that business people will use to consume and analyze information.
When to use: During the 'Build, Test, and Refine' phase of a BI project, typically after the underlying data marts or cubes are designed and populated with at least sample data.
Step 1Develop BI Content Specifications.
Entry: High-level business requirements for reporting and analysis are known.
Exit: An agreed-upon list of BI application deliverables with detailed specifications is complete.
In: Business requirements document · Out: BI Content Specifications, Finalized list of BI applications
Step 2Design the BI Application Layout and UI.
Entry: BI content specifications are complete.
Exit: A visual design or specification for each BI application is approved by business stakeholders.
- Match the type of visualization (bar chart, line chart, map, etc.) to the type of analysis required (comparison, trend, correlation).
In: BI Content Specifications · Out: UI Standards/Templates, Application wireframes or storyboards
Step 3Develop the BI Application using a Prototyping Lifecycle.
Entry: The application design is approved and underlying data is available (at least as sample data).
Exit: A working prototype of the BI application is complete and has passed developer unit tests.
In: Application design documents, BI data stores (data marts, cubes) · Out: Working BI application prototype
Step 4Gather User Feedback and Refine.
Entry: A working prototype is available.
Exit: The prototype has been validated and refined based on user feedback.
In: Working BI application prototype · Out: Refined BI application
Step 5Test the BI Application.
Entry: The refined application is complete.
Exit: The application has passed all tests and received UAT sign-off.
In: Refined BI application, Production-like test data · Out: Test results, UAT sign-off
Triage and Renovate Data Shadow Systems
To systematically identify, assess, and address unofficial, business-built reporting systems (often spreadsheets, also called 'spreadmarts') to reduce data inconsistency and business risk while retaining their business value.
When to use: When an organization recognizes that inconsistent data from multiple unofficial reporting systems is causing lost productivity and poor decision-making.
Step 1Identify existing data shadow systems.
Entry: The organization has acknowledged the problem of inconsistent, siloed reporting.
Exit: A list of known data shadow systems is compiled.
In: Anecdotal evidence of conflicting reports · Out: Inventory of data shadow systems
Step 2Triage the systems based on type and severity.
Entry: An inventory of shadow systems exists.
Exit: A prioritized list of shadow systems targeted for renovation is created.
- Decide whether to eliminate, leave in place, or target a system for renovation.
In: Inventory of data shadow systems · Out: Prioritized list for renovation
Step 3Reverse-engineer the targeted shadow system.
Entry: A shadow system has been targeted for renovation.
Exit: The business logic and data integration processes of the shadow system are documented.
In: The data shadow system itself (e.g., the spreadsheet file) · Out: Documentation of business rules and data transformations
Step 4Design and build a managed data integration process.
Entry: The shadow system's logic has been reverse-engineered.
Exit: An automated process now populates a central, consistent data store with the information the business needs.
In: Documented business rules, Source system data · Out: Managed data integration job, Governed data mart or data source
Step 5Renovate the analytical front-end.
Entry: The managed data store is available.
Exit: The business users are now using a reporting tool that connects to a consistent, managed data source.
- Decide whether to keep the spreadsheet as the front-end or replace it with a BI tool.
In: Governed data mart, Original spreadsheet/report · Out: A 'managed spreadsheet' or new BI application
Evaluate and Select Technology and Products
To systematically evaluate and select the right software and hardware (e.g., BI tools, DI tools, databases) for the BI program based on defined requirements.
When to use: During the 'Justify and Define' or 'Architect and Design' phases of a BI project when a new technology capability is required.
Step 1Gather and prioritize requirements.
Entry: A need for a new technology has been identified.
Exit: A prioritized list of requirements is documented.
In: Business and technical requirements · Out: Prioritized requirements list
Step 2Establish success and value criteria.
Entry: Requirements have been gathered.
Exit: A formal scoring or evaluation matrix is created.
In: Prioritized requirements list · Out: Evaluation criteria/matrix
Step 3Select a short list of product candidates.
Entry: Evaluation criteria are established.
Exit: A short list of 3-5 vendors is selected for detailed review.
In: Market research (e.g., analyst reports), Budgetary constraints · Out: Vendor short list
Step 4Conduct product reviews and proofs of concept (POCs).
Entry: A vendor short list has been created.
Exit: The evaluation team has hands-on experience with the shortlisted products.
In: Vendor short list, Sample data and use cases for POC · Out: Demonstration notes, POC results
Step 5Score, rank, and select the product(s).
Entry: Product reviews and POCs are complete.
Exit: A final product selection is made and documented.
In: Evaluation matrix, POC results · Out: Completed scoring matrix, Final selection recommendation
Step 6Negotiate with the vendor.
Entry: A product has been selected.
Exit: A contract is signed with the vendor.
In: Final selection recommendation · Out: Signed contract
A candidate measure
Business Intelligence Guidebook: From Data Integration to Analytics — derived measurement candidates
Architectural Discipline
Architecture maturity rating; Count of unplanned silos; Percentage of projects following the framework
self-report suitability: medium
Data Integration Rigor
Percent tool-based vs. hand-coded processes; Number of reusable components; Audit/lineage coverage
self-report suitability: medium
Dimensional Modeling Quality
Expert review score against modeling best-practice checklist; Presence of date/time dimensions; Count of anti-patterns
self-report suitability: low
Tool-Based Development Adoption
Percent of development built with tools; Presence of best-fit selection process; Documentation coverage
self-report suitability: medium
Requirements and Expectation Management
Requirements completeness checklist score; Stakeholder expectation-alignment rating; Scope change frequency and handling
self-report suitability: high
Training Investment
Training hours per person; Coverage across audiences; Pre/post competence gain
self-report suitability: high
Organizational Governance and Sponsorship
Governance structure presence index; Sponsorship/resource commitment level; Stakeholder-rated governance effectiveness
self-report suitability: high
Information Quality (Five Cs)
Per-C data quality metrics; Reconciliation/number-dispute incidence; User-reported confidence in data
self-report suitability: medium
Data and Application Silo Proliferation
Inventory count of shadow systems and silos; Number of redundant BI tools; Reconciliation effort hours
self-report suitability: medium
Trust in Information
Self-reported trust scale; Frequency of number disputes; Decision reliance rate
self-report suitability: high
BI Adoption and Usage
BI usage logs (BI on BI); Active user counts and breadth; Shadow system creation rate
self-report suitability: medium
Business-IT Collaboration
Relationship/communication-quality survey; Business participation rate in requirements/testing; Shared-accountability mechanism presence
self-report suitability: high
Business Value and ROI
ROI ratio; Cost reduction and revenue metrics; Cycle-time reductions
self-report suitability: low
BI Project Success
Schedule/budget variance; Expectation-fulfillment satisfaction score; Defect/rework rate
self-report suitability: high
Business and IT Productivity
Time-allocation percentages; Reconciliation effort reduction; IT maintenance-to-value ratio
self-report suitability: medium
The story
The reader A business or IT professional tasked with creating, renovating, or sustaining a business intelligence environment who wants to deliver trustworthy, actionable information that the business will actually use.
External problem
Their organization is drowning in inconsistent, siloed data and struggling to turn it into reliable information for decision-making; BI projects are late, over budget, and fail to meet expectations.
Internal problem
They feel overwhelmed by hype, unsure what questions to ask, and anxious about being blamed when a high-profile BI effort underdelivers.
Philosophical problem
It's just plain wrong that organizations invest heavily in data yet make decisions on incomplete, inconsistent, or untrusted information when there is a proven path to doing it right.
The plan
- Justify the BI effort and set realistic expectations with a business and technical case.
- Define requirements—business, data, quality, functional, and technical—thoroughly to avoid late surprises.
- Build an architectural framework spanning information, data, technology, and product layers.
- Design data models (ER and dimensional/hybrid) and engineer robust, tool-based data integration.
- Develop BI applications and advanced analytics matched to personas and business needs.
- Tame data shadow systems and institutionalize people, process, governance, and centers of excellence.
Success
- The reader delivers clean, consistent, conformed, current, and comprehensive information that business people trust and use.
- BI becomes pervasive and adopted, expanding incrementally with strong business ROI and a sustainable, world-class environment.
- Business and IT collaborate as partners, data shadow systems shrink, and the enterprise becomes truly data-driven.
At stake
- BI projects are late, over budget, and labeled failures due to unmet expectations.
- Data silos and data shadow systems proliferate, producing inconsistent numbers, wasted reconciliation time, and decisions based on faulty data.
- Investments deliver little ROI as the organization repeats the Twilight Zone trap of blaming and replacing tools.
Questions this book answers
- How do you justify a BI investment and set realistic expectations to avoid being labeled a failure?
- How do you define business, data, functional, and technical requirements that won't surprise the team late in the project?
- What architectural framework (information, data, technology, product) enables BI projects to complement each other rather than create silos?
- Why is data integration the bulk of BI work and how should it be designed, built, and tested?
- When should you use entity-relationship modeling versus dimensional modeling, and how do advanced dimensional constructs work?
Glossary
- Architectural Discipline
- The organization's commitment to designing and adhering to an overarching four-layer architectural framework (information, data, technology, product) that guides BI projects so they complement each other and avoid the accidental architecture.
- Data Integration Rigor
- The degree to which data integration is engineered holistically, incrementally, and iteratively with standards, reusable components, documentation, auditability, and appropriate tools rather than ad hoc hand-coded extracts.
- Dimensional Modeling Quality
- The soundness of BI data design using dimensional and hybrid dimensional-normalized models, including conformed dimensions, surrogate keys, slowly changing dimensions, and schemas appropriate to analytical needs.
- Tool-Based Development Adoption
- The extent to which appropriately selected BI, data integration, and database tools (best fit for needs, skills, and budget) are used to develop BI solutions instead of ad hoc manual coding.
- Requirements and Expectation Management
- The thoroughness of defining business, data, quality, functional, and technical requirements and the active, ongoing setting and managing of realistic stakeholder expectations via justification, roadmaps, and communication.
- Training Investment
- The organization's investment in vendor-agnostic foundational BI training, tool-specific training, and use-case-based training tailored to both business and IT audiences.
- Organizational Governance and Sponsorship
- The presence and effectiveness of executive sponsorship, data governance, disciplined project/program management, and centers of excellence that manage the people, process, and political dimensions of BI across the enterprise.
- Information Quality (Five Cs)
- The degree to which delivered information is clean, consistent, conformed, current, and comprehensive, the central condition that determines whether data can be used confidently for analysis and decisions.
Tools these methods power