ssdps training business intelligence strategy implementation guide l.
Skip this Video
Loading SlideShow in 5 Seconds..
SSDPS Training: Business Intelligence Strategy Implementation Guide PowerPoint Presentation
Download Presentation
SSDPS Training: Business Intelligence Strategy Implementation Guide

Loading in 2 Seconds...

play fullscreen
1 / 30

SSDPS Training: Business Intelligence Strategy Implementation Guide - PowerPoint PPT Presentation

  • Uploaded on

SSDPS Training: Business Intelligence Strategy Implementation Guide. June, 2011. Guidance Deck Overview. Best practices for implementing BI Strategy Assessment engagements. Three types of Business Intelligence Strategy Assessments. 1. Core. 2. Advanced. 3. Complete. 3 day assessment

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'SSDPS Training: Business Intelligence Strategy Implementation Guide' - paul2

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
guidance deck overview
Guidance Deck Overview

Best practices for implementing BI Strategy Assessment engagements

Three types of Business Intelligence Strategy Assessments







  • 3 day assessment
  • Analysis of current environment and BI vision
  • Capabilities assessment

5 day assessment

Methodology/ approach; standards and governance

Business value modeling

10 day assessment

Sections, milestones/tasks, Proof of Concept (POC)

Deployment roadmap

Target Customers: SQL Server customers ready to create a BI solution.

assessment outcomes
Assessment Outcomes

Deliverables and goals by type of assessment

engagement activities
Engagement Activities

Implement assessment engagements following the project activity flow


Pre-Kickoff Meeting or Conference call

Initial Conversation with the Customer

Execute the Offering





Assessment and Roadmap Document with vision, architecture and plans



Kickoff Meeting

Proof of Concept

High-level discussion of Bi Needs and Architectures




CoS Achieved?

Condition of Satisfaction

CoS Negotiation



CoS Feasible


Final Findings Presentations


large customer considerations
Large Customer Considerations

Guidance points for large customer assessments

Engagement Type & Focus

POC Guidance

  • 10 day engagements recommended for large customers
  • 3 and 5 day engagements not recommended- customer environment too complex
  • Focus engagement on a specific line of business or organization
  • Position POC to address business objectives of a single line of business
  • Focus POC methodologies toward enterprise-wide best practices, templates, and patterns
  • Pre-POC implementation best practices:
  • Prior to engagement start, secure access to systems and data sources including user account for consultants with appropriate privileges)
  • Assure scope of POC is manageable for a 3 day implementation (i.e. one subject area)
  • Assure server development infrastructure is available
  • Assure sources for facts, dimensions, and basic business/transformation rules are identified and available
  • If no POC in engagement, increase breadth and depth of analysis and assessments
consultant resources considerations
Consultant Resources Considerations

Requirements for consulting resources product expertise and experience

Ensure that all project resources meet the expertise and experience requirements

Required Consultant

Product Expertise

Required Consultant

Experience & Skills

  • SQL Server Relational Engine (2005 , 2008, 2008 R2)
  • SQL Server Integration Services
  • SQL Server Analysis Services
  • SQL Server Reporting Services
  • SharePoint (2007, 2010)
  • Gold Competency in Business Intelligence and/or Data Platform
  • At least 2 end-to-end BI implementations completed
  • Excellent communication and client facing skills
  • Problem solving and negotiation skills

Consultant must have expertise in all products

pre engagement activities
Pre-Engagement Activities

Checklist: assessing the customer technical and business environment


  • Resource: BI Pre-Engagement Questionnaire SSDPS
engagement process
Engagement Process

Implement assessment engagements following the project activity flow


Engagement process begins with pre-kickoff meetings and initial conversations with the customer


Pre-Kickoff Meeting or Conference call



Use results of pre-engagement activities to determine most appropriate resources, understand high level objectives, and to schedule interviews

Initial Conversation with the Customer


Execute the Offering





Assessment and Roadmap Document with vision, architecture and plans




Kickoff Meeting


As engagement begins, scope and condition of satisfaction are defined

Proof of Concept

High-level discussion of Bi Needs and Architectures






Execution includes Envision and Validation (POC). Note: POC is only applicable for 10 day assessment scenarios

CoS Achieved?

Condition of Satisfaction

CoS Negotiation



CoS Feasible


Final Findings Presentations



Final findings are presented to the customer along with clear roadmap for implementing BI strategy


day 1 knowledge capture goals
Day 1- Knowledge Capture Goals

Discuss business objectives, prior learning, and non-MS BI tools

Objectives and Lessons Learned

Non-MS BI Products

day 1 knowledge capture goals10
Day 1- Knowledge Capture Goals

Discuss business objectives, prior learning, and non-MS BI tools

Key Discussion Points and Knowledge Capture Targets

  • Discuss business objectives, challenges, and problems that are being targeted for resolution
  • Take notes of all points discussed and collect all relevant documentation available from the client. Also capture all relevant diagrams (i.e. data flow)
  • Discuss efforts that are being done or that had been attempted to address business objectives and challenges
  • Identify all lessons learned from current/previous BI initiatives, if any
  • Are there BI tools/platforms that had been already selected or procured for addressing BI ?
  • Are there expectations to formulate BI strategy around existing tools/platforms?

Objectives and Lessons Learned

  • If there are existing non-Microsoft BI tools/platforms (i.e. MicroStrategy) procured by the client and expected to be used as a part of global BI strategy, identify the maturity state of current implementation
  • If applicable, incorporate in recommendations, strategy for using existing tools in initial phases of BI Strategy Deployment for applicable components of BI system (i.e. use Panorama for dashboards and analytical reports).
  • However, define strategy for replacing non-Microsoft BI tools in following stages of BI Strategy Deployment with comparable components of Microsoft BI toolset (

Non-MS BI Products

day 1 discuss business objectives prior learning
Day 1- Discuss Business Objectives & Prior Learning

Capture information via the workbook tool

Use the Capability Workbook to assist with defining

  • Process Checklist
  • Business Infrastructure Capability Definitions
  • Strategic Objectives
  • Business Infrastructure Capability Map and Assessment
  • IT Capabilities and Assessment
  • Project List and Roadmap

Resource: BI Capability Workbook SSDPS

All sections of this template cover more than with is in scope for a 3-day assessment. Use only sections appropriate to the scope of assessment.

day 1 discuss business objectives prior learning12
Day 1- Discuss Business Objectives & Prior Learning

Clarify with client what analysis will NOT include

Capture information via the Capability Workbook tool

day 1 interview key stakeholders
Day 1-Interview Key Stakeholders

Conduct Interviews with all necessary customer employees

Interview Targets

  • Business Stakeholders
  • Technology Stakeholders


Perform Interviews


Document Findings


Identify Common Response Patterns

  • Use consistent interview template
  • Capture all the information
  • Example patterns:
  • Requirements to have drill-into and drill-across access to data
  • Requirements to have consolidated reporting and analysis
  • Requirements to have unified repository of master data entities
day 2 consolidate findings
Day 2- Consolidate Findings

Compile all pertinent information from day 1

Consolidate all findings from day 1

  • Business objectives, challenges, and problems that are being targeted for resolution
  • Are there non-Microsoft products/tools that are considered to be in scope?
  • Strategy for introducing Microsoft full BI stack
  • What is the scope for the engagement?
day 2 begin design
Day 2- Begin Design

Create logical design

Finalize gathering details of existing data sources, and information management and delivery systems

Begin Shaping BI Vision

Create Logical BI Architecture

  • Logical BI Architecture suggested considerations:
  • Logical Data Architecture
  • Logical Metadata and Master Data Management
  • Logical Infrastructure Architecture
  • Logical Technology Architecture
  • Logical Information Delivery Architecture
  • Logical Security Architecture
  • Logical BI Semantics Architecture
day 3 map the customer situation
Day 3- Map the Customer Situation

Create BI Assessment and Roadmap Document

Include the following 4 sections in the document:


Executive Summary


Current Environment


Data Delivery Challenges

  • Include the following in the BI Vision section:
  • Logical Data Architecture
  • Logical BI Architecture
  • Metadata and Master Data Mgmt
  • Logical Infrastructure Architecture
  • Logical Technology Architecture


BI Vision

Review deliverable with the client

  • Resource : BI Strategy Template SSDPS
day 3 executive summary
Day 3 – Executive Summary

Ensure that your executive summary include the example sections listed here

Problem Statement

  • Enterprise Customer System initiative, Utility of the future, demand greater need for Business Intelligence Services, such as portal, reporting and analysis capabilities, where and when needed.
  • These demands will create ever increasing data volume while shrinking window of information processing
  • We must make sure the BI Services are architected and appropriated supported for growth, availability and manageability

Various business processes and functions rely on similar capabilities, such as authoritative source of information, information digitization, timely access to information, reporting and analysis and workflow

Maturing BI capabilities will directly improve the maturity of many horizontal business capabilities. These capabilities must be improved from current level of 1 or 2 to level 3 or 4

BI and DBA/Operations teams have unclear roles and responsibilities and conflicting objectives

Eleven subject areas organized in three segments provide flexibility, yet design and implementation has created additional dependencies and latency

Key Observations

Use service-based approach to planning and managing BI Capabilities

BI team is granted expanded authority in driving and executing its plans. Other teams\, such as DBA and operations provide necessary support, not control

Ensure BI services are architect for growth and manageability i.e. additional capacity and resources can be added at an acceptable cost and effort to meet future needs

Use replication to avoid cross-segment data retrieval and dependency in ODS

Use data marts and OLAP cubes for reporting and analysis. Allow only drill-down within a DW segment

Implement horizontal partitioning for large tables, and vertical partitioning for dominant data items in a table



DW design optimization, including partitioning, replication, data marts and cubes

Operational practices assessment and planning

day 3 current environment
Day 3 – Current Environment

Example diagram to explain customer current environment


MS Access

MS Excel


Custom Web App



Financial DW

Services DW


Source A

Source B

Source C

day 3 data delivery bi vision
Day 3 – Data Delivery & BI Vision

Include the following information in each of these sections

Data Delivery Challenges

Details on current data delivery challenges

BI Vision

Detailed proposed future state of Business Intelligence Strategy

Correlation between requirements and comparable solutions from Microsoft BI stack

Address importance of considering metadata and master data management

Outline association between adequate infrastructure architecture and BI solution components

Identify, at a logical level severs for proposed solution (i.e. separate server for SSAS, SQL&SSIS, SSRS, SPS web application, and SPS service applications)

Specify how technology architecture consists of Microsoft BI stack (i.e. OLAP structures are being deployed on SQL Server Analysis Services (SSAS))

day 3 requirements matrix
Day 3 – Requirements Matrix

Business/Functional Requirements Sample Diagram

day 3 logical data architecture sample
Day 3 – Logical Data Architecture Sample

Logical Data Architecture Sample Diagram

Information Delivery

BI Semantics - Collaboration

OLAP - Cubes

Conformed Dimensions

Conformed Dimension

Dimensional Data Warehouse – Data Marts










Staging – Information Hub

Staging Information Hub

Enterprise Extract-Transform-Load Platform



day 3 logical bi diagram
Day 3 – Logical BI Diagram

Logical BI Architecture Sample Diagram

BI Services

Client Interfaces

Back Office Services

BI Application Services


Supply Chain

Dashboard & Scorecards

Managed Reporting

Operational BI

Self-service BI

Human Resources


BI Platform Services

Control Systems

Access Services

Information Services

Data Management Services

Web Browser




Master Data Management

Data Storage


Material Management



Data Integration


Web Services

Customer Management


Foundation: Infrastructure and Operations Services


Load Balancing





Workload Management

Remote Access

Clustering (High Avail)


Server OS

Backup/ Recovery


day 4 methodology approach
Day 4 – Methodology & Approach

Clarify the approach for yourself and the customers




Detail which aspects of proposed BI Strategy should be implemented first

Identify dependencies and which processes can be carried out in parallel

Strategy for adoption of BI Strategy by development and end-user community

Training considerations

Explain where the application of business rules tales place (i.e. in ETL layer)

How will dimension history changes be handled (i.e. Type II changing dimensions)?

Changes to fact data (i.e. introducing offset transactions)

day 5 standards governance
Day 5 – Standards & Governance

Assure key process, security, architecture, and naming requirements are identified

Process & Security

Architecture & Naming Conventions

  • Integration
  • Discuss benefits of adopting ETL framework approach and how SSIS is used in that regard
  • Security
  • What are security requirements for BI systems and how will authentication and authorization be managed (data sources, SharePoint, custom applications)?
  • Change Request Management
  • What are existing procedures?
  • How will changes be managed as a part of BI Strategy?
  • Source Version Control
  • Identify/discuss existing and planned
  • Data Architecture
  • Database naming conventions and data type standards
  • Database recovery models, data, log, and index filegroups
  • Data partitioning (relational and OLAP)
  • Presentation
  • Why SSRS, Excel, and PerformancePoint content should be standardized
  • Ad-Hoc reporting
  • Portal
  • Detail how SharePoint 2010 provides for a collaboration layer in BI Strategy
day 5 two key deliverables
Day 5 – Two Key Deliverables

Update documents with approach, standards, and governance information

Deliverable 1

Deliverable 2

  • Add relevant sections to BI Strategy and Roadmap Document from 3 Day Assessment:
  • Methodology/Approach
  • Standards and Governance
  • Create supplemental PowerPoint deliverable for the BI Assessment
  • Use BI Final Deliverable Template SSDPS & BI Sample Deliverable Healthcare SSDPS
  • Resources: BI Strategy Template SSDPS BI Sample Deliverable Healthcare SSDPS
day 6 sections milestones and tasks
Day 6 – Sections, Milestones, and Tasks

Discuss at the logical level

Foundation Planning

Iteration Planning

  • Infrastructure
  • Data and Integration Architecture Framework
  • Information Delivery Architecture Framework
  • Data Warehouse Design Planning
  • Iteration Analysis Definitions
  • Data Warehouse Design
  • SSAS Cube Design
  • Integration
  • Information Delivery
  • Testing
  • Deployment
day 7 proof of concept
Day 7 – Proof of Concept

Steps to complete a POC

  • Define scope – recommended one subject area within one business unit (e.g., manufacturing, sales, etc.)
  • If client has SharePoint 2010 Enterprise include PerformancePoint dashboards in POC
    • Alternative solution – use VM image or consultants laptop for this component
  • If client has Excel 2010 include PowerPivot as a part of POC
    • Alternative solution demo PowerPivot from consultants laptop
  • Secure development/deployment environment
    • Server, software (if not available consider using consultant resources – laptops)
  • Identify sources
  • Identify outputs – reports (what types – analytical, transactional, excel)
  • Assist client in understanding technologies outside the scope of what they are accustomed to
    • Example: How OLAP would this be beneficial for them
  • Create logical system design
  • Create logical database design
  • Create logical data models
day 8 table creation
Day 8 – Table Creation

Steps to design and create tables

  • Establish naming standards (or adhere to existing standards if existent)
  • Relevant to reporting requirements:
  • Design staging tables – operational data layer
  • Design dimensions with appropriate attributes
    • Discuss how data changes can be tracked
    • Introduce change tracking attributes (start date, end date, current indicator, inferred member indicator)
  • Design fact table to include all measures relevant to reporting requirements
    • It is expected that one fact table as POC should address one subject area of a business unit so POC’s scope can be managed and delivered in 4 days
  • Load data into staging
    • Use the fastest method for loading
  • Create SSIS packages for dimension and fact loads
  • Load data into dimensions and fact
day 9 olap creation
Day 9 – OLAP Creation

Create key reports and dashboard

  • Create OLAP- SSAS
  • Create reports (SSRS, Excel, PowerPivot)
  • Create PerformancePoint Dashboard with analytical views
    • Include three analytical reports of different type and one scorecard (with KPI)
  • Integrate SSRS, Excel, and PowerPivot into the dashboard
day 10 three final deliverables
Day 10 – Three Final Deliverables

Finalize all documents and close the engagement

Deliverable 1

Deliverable 2

Deliverable 3

  • Add relevant sections to BI Assessment and Roadmap Document from 5 Day Assessment
  • Finalize the supplemental PowerPoint deliverable
  • Use BI Sample Deliverable Healthcare SSDPS
  • POC codebase and sample data

Do POC demo for business sponsors, technology sponsors, and leadership/executives

Include high-level overview of strategy in demo

Discuss planning for follow-on work

  • Resources: BI Strategy Template SSDPS
  • BI Capability Workbook SSDPS
  • BI Sample Deliverable Healthcare SSDPS