Agile BI u sing Microsoft SQL Server 2012
description
Transcript of 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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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?
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
Evidence of the blended approach
• Pentair Technical Products – Price Realization Project
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
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
© Copyright GNet Group 2012
Thank You
For more information contact Neelesh RahejaVP Consulting Services, Gnet [email protected] @PracticalBI