SSDPS Training: Business Intelligence Strategy Implementation Guide

1. SSDPS Training:Business Intelligence Strategy Implementation Guide June, 2011

2. 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 • 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.

3. Assessment Outcomes Deliverables and goals by type of assessment

4. Engagement Activities Implement assessment engagements following the project activity flow Start Pre-Kickoff Meeting or Conference call Initial Conversation with the Customer Execute the Offering Project Activity Flow Resources? Assessment and Roadmap Document with vision, architecture and plans Envision Y Kickoff Meeting Proof of Concept High-level discussion of Bi Needs and Architectures Validate N Y CoS Achieved? Condition of Satisfaction CoS Negotiation Y N CoS Feasible N Final Findings Presentations Finish

5. 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

6. 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

7. Pre-Engagement Activities Checklist: assessing the customer technical and business environment Checklist • Resource: BI Pre-Engagement Questionnaire SSDPS

8. Engagement Process Implement assessment engagements following the project activity flow 1 Engagement process begins with pre-kickoff meetings and initial conversations with the customer Start Pre-Kickoff Meeting or Conference call 1 2 Use results of pre-engagement activities to determine most appropriate resources, understand high level objectives, and to schedule interviews Initial Conversation with the Customer 4 Execute the Offering Resources? Project Activity Flow Assessment and Roadmap Document with vision, architecture and plans 2 Envision Y Kickoff Meeting 3 As engagement begins, scope and condition of satisfaction are defined Proof of Concept High-level discussion of Bi Needs and Architectures Validate 3 Y N 4 Execution includes Envision and Validation (POC). Note: POC is only applicable for 10 day assessment scenarios CoS Achieved? Condition of Satisfaction CoS Negotiation Y N CoS Feasible N Final Findings Presentations 5 5 Final findings are presented to the customer along with clear roadmap for implementing BI strategy Finish

9. Day 1- Knowledge Capture Goals Discuss business objectives, prior learning, and non-MS BI tools Objectives and Lessons Learned Non-MS BI Products

10. 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 (http://www.microsoft.com/bi/productsbi/) Non-MS BI Products

11. 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.

12. Day 1- Discuss Business Objectives & Prior Learning Clarify with client what analysis will NOT include Capture information via the Capability Workbook tool

13. Day 1-Interview Key Stakeholders Conduct Interviews with all necessary customer employees Interview Targets • Business Stakeholders • Technology Stakeholders 1 Perform Interviews 2 Document Findings 3 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

14. 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?

15. 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

16. Day 3- Map the Customer Situation Create BI Assessment and Roadmap Document Include the following 4 sections in the document: 1 Executive Summary 2 Current Environment 3 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 4 BI Vision Review deliverable with the client • Resource : BI Strategy Template SSDPS

17. 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 Solution Plan DW design optimization, including partitioning, replication, data marts and cubes Operational practices assessment and planning

18. Day 3 – Current Environment Example diagram to explain customer current environment Presentation MS Access MS Excel Ad-Hoc Custom Web App SSAS Consolidation Financial DW Services DW Source Source A Source B Source C

19. 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))

20. Day 3 – Requirements Matrix Business/Functional Requirements Sample Diagram

21. 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 DM1 DM2 DM5 DM6 EDW DM3 DM4 DM7 DM8 Staging – Information Hub Staging Information Hub Enterprise Extract-Transform-Load Platform EETL SOURCES

22. Day 3 – Logical BI Diagram Logical BI Architecture Sample Diagram BI Services Client Interfaces Back Office Services BI Application Services LOB App Supply Chain Dashboard & Scorecards Managed Reporting Operational BI Self-service BI Human Resources Office BI Platform Services Control Systems Access Services Information Services Data Management Services Web Browser Billing Presentation Reporting Master Data Management Data Storage Mobile Material Management Distribution Analysis Data Integration ETL Web Services Customer Management Financials Foundation: Infrastructure and Operations Services Firewall Load Balancing Security Database Storage Monitoring Workload Management Remote Access Clustering (High Avail) Directory Server OS Backup/ Recovery Auditing/Logging …

23. Day 4 – Methodology & Approach Clarify the approach for yourself and the customers Prioritization Approach Adoption 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)

24. 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

25. 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

26. 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

27. 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

28. 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

29. 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

30. 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