agile bi u sing microsoft sql server 2012

© Copyright GNet Group 2012

Agile BI using Microsoft SQL Server 2012

Neelesh RahejaVP Consulting Services, GNet [email protected] @PracticalBI


Agenda• Level set on components of a BI solution• The Microwave Society – Speed• Evolution & Change• Approaches to BI

• Classic• Agile• Blend of both approaches

• Agility with Microsoft SQL Server 2012• 2 Scenario’s

• In Summary• Q&A


Level Set – food for thought• Components of a BI solution?

1. ETL (Extract, Transform, Cleanse, Load)2. Data Warehouse (includes ODS, Staging, Dim Model)3. Presentation (OLAP, Reports, Dashboards etc)

• Which of the above 3 can we get rid off and still deliver a long term & robust Ent. BI solution?

• None! Enterprise Data Integration & Architecture is key to a successful Enterprise BI Solution


The Microwave Society• Pace of business and short attention span• Exponential growth in data, sources and

reporting needs• Reduced cycle times and pressure to react

faster • Need to deliver on reporting needs today• The next business question is already here!

Can we deliver an Enterprise BI Solution….tomorrow? Next Week? Next Month?


BI solutions must and will evolveTransparenc

y: Report what

happened? (DW and Reports)

Discover & Question what’s

happening?(Dashboards and Ad-Hoc Analysis)

Understand Why? Cause & Effect?

(root cause & predictive analytics)

ChangesWhat

should happen?(Goal and process)

Improvements

Measure change impact

(Evolve DW, Dsh, Rpts)

Processes & Data

Changes in DW Challenges

A. Ent Data ArchitectureB. Time


Classic approach to BI

Project Planning

Business Requirements

Definition

Technical Architecture

Design

Dimensional Modeling

BI Application Design

Technology & Infrastructure

Install and Configuration

Physical Design

ETL Design & Development Deployment

Program / Project Management

BI Application Development

May 12 November 12Jun-12 Jul-12 Aug-12 Sep-12 Oct-12

• How often do requirements evolve during design/development?• How often do requirements change and impact

design/development?• How often are the requirements and data in sync?• How does the above impact design/development?• How fast can we change a DW / ETL to align with changing

requirements?

80%-Backend workTakes too long

20%-Touch and feel at project end

Ent Data Integration & Architecture X Time


Agile approach to BI

• What about data integration i.e. Ent Data Warehouse? One EDW vs. siloed Data Marts

• What about multiple perspectives of data (e.g. sales hierarchy, marketing hierarchy)?

• What about single source of truth? (e.g. Multiple Data marts – sales, marketing)

• Can we rebuild / change data marts without impacting reports?• How can we build a single, consistent enterprise data warehouse over

time?

X Ent. Data Integration & Architecture√ Time


Blend the two?

• End Goal is a comprehensive BI solution (ETL, EDW and Presentation)• Agile BI Solutions in the interim while Data Architecture is built• Ent. Data Architecture: ODS, EDW based on Exploration, Evaluation and

Evolution.• Accelerate robust EDW build based on solid requirements and feedback

3. Project Planning

4. Business Questions and Requirements

5. Data Arch & Int

6. Prototype (BI App) Model Design Dev

7. Deploy Self Service

8. Feedback

9. Backlog

2. Assessment, Roadmap and Release Plan

1. Business Vision and Strategy

For Each Roadmap Phase7A. Physical

Design

6A. Build ODS

/Dimensional Model Based on Personal / Team

8A. Physical Design

Time: Early Results. Buy Time for Data Architecture

Ent Data Architecture

9A. Development

10A. Deployment

Data Warehouse build In parallel


How Does SQL 2012 fit in?

• Time : Agile, self service personal (PowerPivot) and/or team BI (PowerPivot for SharePoint)

• Promote PowerPivot to SSAS (Org BI)• Some applications will remain in PowerPivot (Personal / Team)

3. Project Planning

4. Business Questions and Requirements

5. Data Arch & Int

6. Prototype (BI App) Model Design Dev

7. Deploy Self Service

8. Feedback

9. Backlog

2. Assessment, Roadmap and Release Plan

1. Business Vision and Strategy

For Each Roadmap Phase7A. Physical

Design

6A. Build ODS

/Dimensional Model Based on Personal / Team

8A. Physical Design

Time: Early Results. Buy Time for Data Architecture

Ent Data Architecture

9A. Development

10A. Deployment

1st Iteration - PowrerPivot

2nd Iteration Team BI with SharePoint & PowerPivot or

Org BI - SSASData Warehouse build In parallel


New in SQL 2012 Analysis Services:BI Semantic ModelOne Model for all End User Experiences

Client Tools Analytics, Reports, Scorecards,

Dashboards, Custom Apps

Data SourcesDatabases, LOB Applications, Data Feeds,

Spreadsheets, Text Files

BI Semantic ModelData model

Business logic and queriesData access

Team BIPowerPivot for

SharePoint

Personal BIPowerPivot for

Excel

Organizational BIAnalysis

Services


Analysis Services

BI Semantic Model

POS

CRM ERP POS Other

CRM

ERP

Other

ODS

SOURCE DBs

Team BI

Power Pivot for

SharePointPromote to IT Managed

Environment – Process & Security

Personal BI

XLS

Power Pivot for

Excel

Organizational BI

Performance Point

Excel and Excel

ServicesSSRSPowerPivot

EDW

Agility and long term data architecture!

Scenario 1: First DW (end goal is EDW) 2

31


Analysis Services

BI Semantic Model

POS

CRM ERP POS Other

CRM

ERP

Other

ODS

SOURCE DBs

Team BI

Power Pivot for

SharePointPromote to IT Managed

Environment – Process & Security

Personal BI

XLS

Power Pivot for

Excel

Organizational BI

Performance Point

Excel and Excel

ServicesSSRSPowerPivot

EDW

Agility and long term data architecture!

Scenario 2: Additions to existing DW

1

2


Evidence of the blended approach

• Pentair Technical Products – Price Realization Project


BenefitsBlended approach addresses both Data Architecture and the Need for SpeedPrototyping 1. Visualizing, touching, feeling the data – facilitates discovery and exploration2. Enables data profiling, validate requirements 3. Keeps customers engaged and involved in the process4. Focus is on business discussion vs technical specifications5. Facilitates ownership and adoption

Classic Approach based on feedback6. Accelerates Data Architecture (EDW) build in parallel based on solid

requirement7. Perception of speed, they see activity, no big gaps in time, “pressure relief

valve”8. Removes the unknown which results in uncertainty and a sense of risk9. Reduces cost of Data Architecture build Leadership can see the value in action


In Summary• Need to blend Agile and Classic approach• Why PowerPivot?

• Modeling and building the solution at the same time• Can promote model/solution to enterprise class BI Solution

• This approach does not eliminate • EDW. Changes how we approach data architecture and time• Biz/IT engagement. Partnership is still key• Roadmap and periodic (90 days) refactoring / re-prioritization

• Must• Enable self service to accelerate results and adoption• Incorporate prototyping & deploy to production–Touch & Feel asap!• Gather feedback for long term data architecture• Execute on it too! EDW is a must

• Technical Architecture for SQL Server 2012 in memory technologies

• Yes! Training and hands-on experience with 2012


Thank You

For more information contact Neelesh RahejaVP Consulting Services, Gnet [email protected] @PracticalBI

agile bi u sing microsoft sql server 2012

Documents