Transcript Self Service BI:

21st November 2009
Gemini
Self Service BI: Project
PowerPivot
Bob Duffy
Database Architect
Prodata SQL Centre of Excellence
Speaker Profile – Bob Duffy
• SQL Server MVP
• MCA for SQL Server (aka SQL Ranger)
• MCM on SQL 2005 and 2008
• 18 years in database sector, 250+ projects
• Senior Consultant with Microsoft 2005-2008
• Regular speaker for TechNet, MSDN, Users Groups, Irish
and UK Technology Conferences
• On MCM 2008 exam working group
• Database Architect at Prodata SQL Centre Excellence,
Dublin
Agenda
•
•
•
•
Why Self Service BI
What is PowerPivot?
PowerPivot for Excel
PowerPivot for SharePoint
The BI road so far 2005/8
“Trad” 2005/8 BI Architecture
Problems with Traditional BI ?
#1 The Business User
Can we analyze this data by tomorrow ?
Can we compare the data to competitors data ?
“It has to stay in Excel. We just won’t use another tool.”
“We don’t know what is OLAP and dimensional modeling and we don’
to know. Even relational modeling is a bit too hard.”
“Don’t ask us to design or model anything in advance. We should just be able to
our data in and work. Exactly as do in Excel.”
#2 The IT Perspective on using Excel
“Spreadmarts”
“Unsanctioned Information”
“Stale Data”
“Unsecure”
“Ban Excel!”
“Rouge Applications”
“Data locked in Excel sheets”
“Multiple Versions of the Truth”
Arrrggg… #%^$%
Gemini Goals – Match Conflicting Twins…
Business
Simple and Smart
Tool
IT
Scalable and robust
Infrastructure
PowerPivot
What is PowerPivot?
Excel-hosted client & reporting
In-memory BI engine (VertiPaq)
PowerPivot for SharePoint
Operational Monitoring
PowerPivot for Excel…
Loading
Filtering
Mashups
Introducing DAX
•
•
•
•
Expression Language based on Excel Syntax
Essential for Calculated Columns and Measures
Converted to MDX by PowerPivot AddIn
Example Functions:
• RELATED(), RELATEDTABLE()
• Filter(Table, Condition)
• Distinct(Column)
• Concatenation “&”
• Notes:
• Works on Columns NOT Rows
• Consider “home” sheet carefully
Using DAX:
Profit Analysis
Temperature Mashup
Wheres the data?
Excel-hosted client & reporting
In-memory BI engine (VertiPaq)
PowerPivot for SharePoint
Operational Monitoring
PowerPivot for SharePoint
• Sits under Excel Web Access (EWA)
• Installation Gotchas
•
•
•
•
•
•
Read the guides (on blogs)
Needs Domain Accounts
Easier with “new” farm
Use Windows 2008 x64sp2 (patch for R2 released)
Uses lots of RAM
Limit of 2GB size (blob)
• Needs Enterprise Edition
PowerPivot for SharePoint
What is PowerPivot?
Excel-hosted client & reporting
In-memory BI engine (VertiPaq)
PowerPivot for SharePoint
Operational Monitoring
Step 1 Monitoring Sandboxes
Step 2 Monitoring Servers
Step 3 Monitoring a Solution
Summary
Excel is user tool of choice
Balance between
User Power
IT Management
Bridges the Organizational Divide
Gemini is a part of the MS BI stack
Enables the Self-Service scenario
Enhances existing investments
CTP3 Available now
Ship next year
References/Blogs
Official Blog/Site
http://blogs.msdn.com/powerpivot/
http://www.powerpivot.com/
Analysis Services Team Blogs
http://powerpivotgeek.com
http://powerpivottwins.com/
http://powerpivotpro.com/
General Site
http://www.powerpivot-info.com/
Online PowerPivot Labs
https://cmg.vlabcenter.com/default.aspx?moduleid=ad3bd3e9-8d2b498d-94fa-e41e1b09730d
Thank You!