SQL Power Methodology: Summary

SQL Power Data Warehousing Methodology has evolved with our Data Warehousing Consulting practice and has been validated and confirmed by our Clients and Consultants on many successful BI Solution implementations.

Our Methodology contains activities grouped in seven major phases:

1. Business Intelligence Vision

BI Strategic Planning activities focus on the development of a Business Case for a BI Solution; Organization of a BI Steering Committee; Definition of the Required BI Infrastructure and creation of the BI Evolution Roadmap.

2. Scope

Scoping activities relate to the initiation of a BI project and include the development of the Project Charter, Business Assessment, Requirement Gathering, Project Planning and creation of the Project Team.

3. Architect

This phase includes Analysis and Design activities to create the DW Architecture, conduct Technology and Data Quality Assessments, design Data Models, Metadata Repository, ETL Application and BI Reports.

4. Build

This is the code construction phase during which all physical Data Models, ETL Code and BI Reports are constructed.

5. Test

Testing activities focus on Unit testing, System Integration Testing, User Training and User Acceptance Testing. Testing includes quality checking of all ETL code and BI reports constructed in the Build phase.

6. Deploy

The solution deployment planning rollout and Production Rollout execution activities include User Training and Production deployment of BI databases, Metadata Repository, ETL code and BI Report.

7. Support

Post-production implementation activities focus on BI application Monitoring and Support, including monitoring of Data Load Logs, Data Volumes, Data Auditing, Report Usage frequencies and Change Tracking.

The effective execution of a successful BI project requires that a proper project management approach is used. Thus there is an additional Project Management phase that applies equally to any activities on a BI project.

Top 10 Ways to Ensure a Successful BI Implementation

  1. Secure a strong Business Sponsor
  2. Under-commit and over-deliver on BI projects (especially in the early phases)
  3. Supplement your team with seasoned Business Intelligence experts
  4. Design your Data Warehouse for scalability and constant change
  5. Invest in good BI productivity tools (including Design Tool, ETL Tool, and Data Cleansing Tool)
  6. Implement Data Auditing processes: Data Integrity = End User Confidence = BI Program credibility
  7. Strive for end-user self-sufficiency: Choose easy and intuitive BI Reporting Tools
  8. Decommission obsolete and lesser-used subject areas
  9. Continuously look for ways to reduce on-going BI Costs
  10. Promote Data Warehouse growth - a static Data Warehouse is a dead Data Warehouse

 

The shortest time to delivery of the BI Solution is guaranteed by high quality deliverables produced in each development phase, as shown on the diagram.

 

 

Methodology - Vision

1. BI Vision

1.1. Description

The BI Vision phase defines the BI strategy for the company. It addresses the business needs and defines the business case for a Business Intelligence solution. Before the BI Steering Committee is organized and the required infrastructure is put in place, a study must be conducted to determine the readiness of the business for a BI solution, so that business executives are in a position to sponsor the BI vision. A very important outcome of this phase is the BI Evolution Roadmap.

1.2. Objectives

The objectives of this phase include:

  • Establishing business goals that will be met by implementing the BI solution
  • Obtaining buy-in from the business executives that sponsor the BI vision
  • Assessing readiness of the organization for the BI solution
  • Developing the BI business case
  • Organizing the BI Steering Committee
  • Creating the BI evolution roadmap

1.3. Inputs

The inputs for this phase include:

  • Business strategy and business objectives that will be met by a BI solution
  • Business executives criteria for sponsoring a BI vision
  • Existing and future documented information reporting requirements
  • Results of previous business assessments
  • Business case criteria
  • Existing infrastructure
  • Current state of information reporting

1.4. Activities/Tasks

The activities of this phase include:

  1. Conducting BI Readiness Assessment
  2. Develop Business Case
  3. Document Infrastructure Requirements
  4. Conduct BI Architecture Review
  5. Establish BI Steering Committee
  6. Develop BI Evolution Roadmap

1.5. Deliverables

The deliverables of this phase include:

  • BI Readiness Assessment
  • Business Case justification
  • Required Infrastructure including:
    • People
    • Processes
    • Systems and Technology
    • Architecture
  • BI Evolution Roadmap including:
    • BI Matrix
    • High Level Project Scope
    • High Level Timelines and Estimates

BI Vision deliverables are delivered in the BI Strategic Plan, a document that outlines the business need for a BI Solution. This strategic document must be signed by the business executive who will be the sponsor for the BI initiative.

 

 

Methodology - Scope

2. Scope

2.1. Description

The second phase of the SQL Power Methodology defines the scope and the plan of each of the Business Intelligence projects outlined in the BI Evolution Roadmap developed in the BI Vision phase. The project?s scope, plan, quality plan, required resources, etc. are documented in the Project Charter that must be signed off by the BI Steering Committee.

2.2. Objectives

The objectives of this phase include:

  • Define scope and plan for each identified BI project
  • Develop Project Charter
  • Gather and document business requirements
  • Assess technical state of required infrastructure
  • Organize project team
  • Compile and publish project schedule
  • Develop and obtain sign off on the Project Charter

2.3. Inputs

The inputs for this phase include:

  • BI Evolution Roadmap
  • BI Matrix
  • Business readiness assessment
  • Technical assessment
  • Resource availability

2.4. Activities/Tasks

The activities of this phase include:

  1. Review BI Evolution Roadmap and high level project scope
  2. Develop Project Charter
  3. Gather Business Requirements
  4. Develop Quality Plan
  5. Conduct Technology Assessment
  6. Develop Project Schedule (timeline, resources)
  7. Obtain Sign-off of the Project Charter

2.5. Deliverables

The deliverables of this phase include:

  • Approved Project Charter and Project Schedule
  • Business Requirements Document
  • Quality Plan
  • Technology Assessment
  • Change Management Plan
  • Review of the required infrastructure
  • Recommendations for new technology infrastructure
  • Allocated project team
  • Allocated resources

The Scope phase will define in detail each of the BI projects that have been identified. It defines the scope, timelines and resources for the new project.

 

 

Methodology - Architect

3. Architect

3.1. Description

The third phase of the SQL Power Methodology designs the intended Business Intelligence solution. During this phase the architecture of the solution is finalized and all the necessary logical pieces (such as: Logical Data Models, Metadata Repository, ETL Application Architecture, and Design Document) are put in place. The plan for system integration testing is developed, and Capacity and Support Plans are also drafted in this phase. Originating systems and source data quality are analyzed and detailed, and ETL interfaces (including source-to-target mapping) are designed and documented.

In some cases, a reporting prototype can be created to verify business requirements with the users.

3.2. Objectives

The objectives of this phase include:

  • Perform detailed analysis of the source data
  • Define technical architecture requirements
  • Assess data quality
  • Design target data warehouse architecture
  • Define and design ETL interfaces
  • Define test plan
  • Define infrastructure capacity plan
  • Define data warehouse support plan

3.3. Inputs

The inputs for this phase include:

  • Project Charter
  • Technology assessment
  • Source data
  • Business requirements
  • Required resources

3.4. Activities/Tasks

The activities of this phase include:

  1. Review Technical Architecture
  2. Perform Detailed Analysis
  3. Conduct Data Quality Assessment
  4. Perform Detailed Design
    1. 4.1. Data Modeling (logical, physical)
    2. 4.2. ETL Interface Design
    3. 4.3. BI Reporting Design
  5. Build BI Prototype (optional)
  6. Review and refine project plan
  7. Negotiate Interface Agreements
  8. Formulate Test Plan
  9. Prepare Capacity Plan
  10. Create Support Plan

3.5. Deliverables

The deliverables of this phase include:

  • Logical and physical data models for:
    • Data Audit Repository
    • Metadata Repository
  • Design Document of the ETL/Interface Application design, including:
    • Source-To-Target mappings
    • ETL Modules/Procedures design
    • ETL Process Dependencies and Schedule design
  • Test Plan
  • Capacity Plan
  • Support Plan

The Architect phase will produce the design of the intended BI solution, including all the necessary specifications to start building the actual system.

 

 

Methodology - Build

4. Build

4.1. Description

The fourth phase of the SQL Power Methodology develops the intended Business Intelligence solution. During this phase the physical database and the ETL code are developed and unit tested. Data migration and conversion plans are developed. User Acceptance testing is planned in this phase. In cases where a reporting prototype was created, it can be reviewed and refined based on user feedback.

4.2. Objectives

The objectives of this phase include:

  • Develop physical database
  • Develop ETL applications
  • Unit test ETL processes
  • Develop and review BI prototype
  • Plan data migration and conversion to production

4.3. Inputs

The inputs for this phase include:

  • Project Charter
  • Logical and physical data models for:
    • Data Audit Repository
    • Metadata Repository
  • Design Document of the ETL/Interface Application design, including:
    • Source-To-Target mappings
    • ETL Modules/Procedures design
    • ETL Process Dependencies and Schedule design
  • Test Plan
  • Required resources

4.4. Activities/Tasks

The activities of this phase include:

  1. Review Design Documents
  2. Create Physical Database
  3. Develop ETL module
  4. Conduct code reviews
  5. Develop Production Migration Plan
  6. Develop User Acceptance Plan

4.5. Deliverables

The deliverables of this phase include:

  • DDL Scripts for Physical Database Implementation
  • Constructed and unit tested ETL modules
  • Constructed and unit tested BI Reports
  • Constructed and unit tested ETL load scheduling procedures
  • User Acceptance Test pan

The Build phase will build the BI solution and will prepare it for the User Acceptance testing.

 

 

Methodology - Test

5. Test

5.1. Description

The fifth phase of the SQL Power Methodology tests the developed Business Intelligence solution. During this phase the ETL code is tested for performance. The whole system is tested for integration and data flow. Users test the system for acceptance based on the User Acceptance Test Plan developed in the previous phase. System administrators may be required to maintain the system. Users are trained in the use of the new system.

5.2. Objectives

The objectives of this phase include:

  • Verify that the BI solution functions as expected
  • Conduct Integration (System) Testing
  • Conduct Performance Testing
  • Conduct Regression Testing
  • Perform User Acceptance Testing
  • Obtain user approval
  • Train administrators
  • Train users

5.3. Inputs

The inputs for this phase include:

  • Project Charter
  • Business Requirements
  • Design Document
  • Developed system
  • System Integration Test Plan
  • User Acceptance Test Plan
  • Required resources

5.4. Activities/Tasks

The activities of this phase include:

  1. Review results of unit testing
  2. Conduct System Integration Testing
  3. Conduct Performance Tests
  4. Train Users
  5. Train Administrators
  6. Conduct User Acceptance Testing

5.5. Deliverables

The deliverables of this phase include:

  • Test Results of:
    • Integration Testing
    • Performance Testing
    • Regression Testing
  • Tested ETL programs & ETL application
  • Test results & recommendations
  • User Acceptance Approval
  • Trained Administrators
  • Trained Users

The Test phase will confirm that the developed BI solution conforms to the specified business requirements and will prepare the system for migration to production environment.

 

 

Methodology - Deploy

6. Deploy

6.1. Description

The sixth phase of the SQL Power Methodology rolls out the developed Business Intelligence solution to the production environment. During this phase, the tested system is migrated to production so that the users can start using it.

6.2. Objectives

The objectives of this phase include:

  • Determine organization's deployment readiness
  • Develop user documentation
  • Train business users
  • Rollout the solution into the production environment

6.3. Inputs

The inputs for this phase include:

  • Tested System
  • Trained Administrators
  • Trained Users
  • Migration Plan

6.4. Activities/Tasks

The activities of this phase include:

  1. Prepare system for migration
  2. Rollout system to production
  3. Publish user documentation
  4. Develop system documentation
  5. Monitor system migration

6.5. Deliverables

The deliverables of this phase include:

  • User Documentation
  • Operations Manuals (Runbooks)
  • Implementation Approval Document
  • Packaged Project Documentation
  • Production system

The Deploy phase will roll out the developed BI solution to the production environment and will make it available for everyday use.

 

 

Methodology - Support

7. Support

7.1. Description

The seventh phase of the SQL Power Methodology consists of the post-production implementation activities. The main activity here is the monitoring of the production Business Intelligence system, including Data Auditing, Data Volume and Report Usage monitoring, as well as Data Load Logs (Journal) review. This phase also defines the Change Management processes that are required to incorporate any changes to the system.

7.2. Objectives

The objectives of this phase include:

  • Ensure smooth running of the system
  • Monitor system performance and take corrective action, as required
  • Establish orderly process to enable seamless changes to the system

7.3. Inputs

The inputs for this phase include:

  • Fully functional production system
  • Data Load Logs
  • Data Volumes reports
  • Report Usage reports
  • System documentation

7.4. Activities/Tasks

The activities of this phase include:

  1. Monitor system operations and performance
  2. Conduct data auditing
  3. Support production system
  4. Define and implement change management

7.5. Deliverables

The deliverables of this phase include:

  • Fully functioning Production system
  • Data Volumes and Report Usage reports
  • System status reports
  • Change Management process
  • Implemented changes

 

The Support phase is the ongoing monitoring of the BI system. It is intended to ensure that the system is functioning as expected. The duration of this phase will be determined by the needs of the users for the system.

 

 

Methodology - Project Management

8. Project Management

8.1. Description

Project Management is not a separate phase of the SQL Power Methodology, but rather an activity that takes place throughout any Business Intelligence project, starting from scoping and planning to roll-out and production support. Project Management activities include the necessary steps to ensure that the project is properly scheduled, the required resources and infrastructure are available and that the project is implemented on time and on budget.

8.2. Objectives

The objectives of this phase include:

  • Ensure proper project planning
  • Oversee project execution at every phase
  • Ensure project controls are adhered to
  • Provide required project management reporting
  • Identify and control project risks
  • Ensure the project execution is on time and within budget
  • Monitor and manage user expectations
  • Conduct project closing at the end of the project

8.3. Inputs

The inputs for this phase include:

  • Business requirements
  • Approved Statement of Work (SOW)
  • Infrastructure resources
  • Human Resources
  • Project Charter
  • Timesheets
  • Minutes of Project Status meetings

8.4. Activities/Tasks

The activities of this phase include:

  1. Analyze user requirements
  2. Develop Project Charter (project schedule, risks and expected outcomes)
  3. Assign project team
  4. Monitor project execution and quality of deliverables
  5. Prepare and submit Project Status reports
  6. Address project issues and manage user expectations
  7. Manage project scope, initiate and manage changes
  8. Obtain approval for project deliverables
  9. Conduct project closing

8.5. Deliverables

The deliverables of this phase include:

  • Properly planned and executed project
  • Satisfied users
  • Satisfied project team
  • High quality deliverables

 

The Project Management phase applies to all other phases of the BI project and is intended to properly plan, execute and control the project. This phase closely resembles the Project Management approach developed by PMI (Project Management Institute), as documented in the PMI PMBOK (Project Management Book of Knowledge - see www.pmi.org.) The use of productivity tools for project management, e.g. Microsoft Project 2007 is strongly encouraged.