Nebraska SharePoint User Group
Welcome to the May 5th, 2009 Session
www.SharePointNE.com
Nebraska SharePoint User Group
Agenda • • • • • • •
Introductions What is BI? The Microsoft BI Stack Dashboard Demos Third Party Tools Questions Drawings
www.SharePointNE.com
1
Nebraska SharePoint User Group
Introductions • Jeff Zakrzewski MCTS PerformancePoint – BI Practice Lead
• Scott Hanssen MCTS BI – BI Senior Engineer
• Malinda Jepsen MCSD.Net, MCITP BI, MCTS PerformancePoint, MS VTS – BI Architect
www.SharePointNE.com
Nebraska SharePoint User Group
www.SharePointNE.com
2
Nebraska SharePoint User Group Business Intelligence Technologies “Formal” BI Database-based BI
“Informal” BI File/Document-based BI
80% of the data
80% of the work • SQL • ETL • Data Mining
DBAs
• OLAP • Production Reporting • Process Integration
• Spreadsheets • Presentations • Desktop DBs
Analysts
• Management Reports • Process Diagramming • General-purpose Analysis
IWs
• Lots of different technologies • Lots of different user tools and requirements • How do you bring it all together and make sense for users? www.SharePointNE.com
Nebraska SharePoint User Group Dashboard Paradigm
www.SharePointNE.com
3
Nebraska SharePoint User Group
The “iceberg” effect 90% of the Glory!
90% of the Work!
BI Applications Dashboards, Scorecards & Reports Backend Data Source Systems Requirements by Business Process Data Lineage ETL Processing Data Warehouse / Data Marts Cubes Security Architecture
www.SharePointNE.com
Nebraska SharePoint User Group
A Performance Management Angle • • • • •
Measuring the performance of the organization Accelerate management decision making Aligning key organizational strategies to metrics Enable collaborative analysis Provide visibility into Key Performance Indicators (KPI’s)
www.SharePointNE.com
4
Nebraska SharePoint User Group
From “Me” to “Team” Richer modeling with unlimited data
Publish content with rich interactivity
Create more visual analytics with slicers
Take information offline
www.SharePointNE.com
Nebraska SharePoint User Group
Analytic Charts and Grids • Interactive • What is the story?
www.SharePointNE.com
5
Nebraska SharePoint User Group
Reports and Gauges
www.SharePointNE.com
Nebraska SharePoint User Group
Scorecards
www.SharePointNE.com
6
Nebraska SharePoint User Group A Composite Application Where Formal, Informal, Operational, and Analytic Meet Presentation Structure Data
• • •• •• •• •• •
Different Flexible Web parts user types of experience information Live data Find Databases infodata quickly Filtered Composition Documents of Lists and libraries information Web information Contextually Find and add new driven Data access information Data management
www.SharePointNE.com
Nebraska SharePoint User Group
www.SharePointNE.com
7
Nebraska SharePoint User Group
www.SharePointNE.com
Nebraska SharePoint User Group BUSINESS USER EXPERIENCE
Self-Service access & insight Data exploration & analysis Predictive analysis
BUSINESS MIDDLE TIER
Dashboards & Scorecards Excel Services Web based forms & workflow Collaboration Enterprise Search Enterprise Content Mgmt LOB data integration DATA INFRASTRUCTURE & BI PLATFORM
Analysis Services (OLAP) Reporting Services Integration Services (ETL) Data Mining Data Warehousing www.SharePointNE.com
8
Nebraska SharePoint User Group Source Data
Warehouse Tables Consolidate (ETL)
Integration Services
Users
Analysis Services Reporting Services PerformancePoint 2007
Office 2007 SharePoint 2007
Cubes
www.SharePointNE.com
Nebraska SharePoint User Group Common SharePoint Features from a BI Perspective • • • • • •
Excel Services Report Center/Report Library Dashboard Pages Filtering Data Business Data Catalog (BDC) Key Performance Indicators (KPI’s) in SharePoint
www.SharePointNE.com
9
Nebraska SharePoint User Group
Excel Services – What is it? • • • • •
New server technology in MOSS 2007 Load, calculate and display Excel Workbooks in MOSS 2007 Thin client view in browser Published workbooks can connect to external data Designed to cut down on proliferation of Excel files
www.SharePointNE.com
Nebraska SharePoint User Group
Excel Services – How does it work? • • •
Excel 2007 • • •
Publish Spreadsheets
View and Interact
Design and author BI Abilities Visualization
Web rendering Zero-footprint Interactive
Browser
Programmatic Access
MOSS 2007 Protect, Share & Reuse Spreadsheets • • •
Spreadsheets stored in document libraries Server calculation and rendering External data retrieval and caching
Custom applications • • •
Set & get spreadsheet values Perform calculations Retrieve full workbook file
www.SharePointNE.com
10
Nebraska SharePoint User Group
Publishing from Excel to Excel Services • End user creates spreadsheet in Excel 2007 • Publishes it to SharePoint Excel Services • Configures the view of the spreadsheet in SharePoint
www.SharePointNE.com
Nebraska SharePoint User Group The Report Center •
SharePoint 2007 Site Template
•
Repository for reports
•
Includes many features •
Sample KPI’s
•
Dashboard Pages
•
Excel Web Access
•
Report Library
•
Report Calendar
•
Filter web parts
www.SharePointNE.com
11
Nebraska SharePoint User Group Dashboard Pages in the Report Center • SharePoint page • Create individual dashboards
• Contains a number of different parts • Content Editor • Contact Details • Filter Actions
• Excel Web Access • KPI • Summary Link
www.SharePointNE.com
Nebraska SharePoint User Group Filtering Data in SharePoint • Used to filter data – – – – – – – – –
Multiple filter types Authored List Business Data Catalog Current User Date Query String (URL) SharePoint List SSAS Text
www.SharePointNE.com
12
Nebraska SharePoint User Group SharePoint Business Data Catalog (BDC) • • • • •
Expose Data to SharePoint Custom Line of Business Data Database or Web Services Can be used as metadata Search Data
www.SharePointNE.com
Nebraska SharePoint User Group Key Performance Indicator (KPI) List • • • •
List template Graphically displays KPI’s Use to track metrics Data from multiple sources – – – –
SharePoint Lists Excel Services SQL Analysis Services 2005 Manually entered
www.SharePointNE.com
13
Nebraska SharePoint User Group KPI Web Part • Displays KPI’s in SharePoint
List
Detail
www.SharePointNE.com
Nebraska SharePoint User Group
The PerformancePoint Server Suite Server ProClarity Analytics Server
Power User ProClarity Professional (Desktop or Web)
ProClarity Web Standard (Thin)
Dashboard Designer (DD)
SharePoint Excel 2003/2007
Migration Path??? PPS Monitoring Server
End User
PerformancePoint Services in SP 2010 PPS Planning Server
Planning Business Modeler (PBM) Excel 2003/2007
SharePoint Excel 2003/2007
SP3 This Summer then Supported for 10 Years
www.SharePointNE.com
14
Nebraska SharePoint User Group
PPS Monitoring • Dashboard Designer – The Power User tool for building Dashboards – Smart client technology (or “click-once”) – safe and secure install-ondemand, maintenance-free patching
• Dashboards – – – –
Organize scorecards and report views into dashboards Extensible dashboard layout templates Drag-and-drop placement of objects onto dashboards Dashboard filters
www.SharePointNE.com
Nebraska SharePoint User Group
PPS Monitoring • Scorecard features – Preview scorecard in Dashboard Designer – KPI data output – use the actual and/or target value for a KPI as an input value to a KPI – Import Analysis Services 2005 KPIs into scorecards
www.SharePointNE.com
15
Nebraska SharePoint User Group SQL Server Reporting Services Browser Office
Custom Application
XML Web Service Interface
Data Sources (SQL, OLE DB, XML/A, ODBC, Oracle, Custom)
Security Services (NT, Passport, Custom)
Report Server Report Processing
Data Processing
Security
Rendering
Scheduling & Delivery
Output Formats (HTML, Excel, PDF, Custom)
SQL Server Catalog
www.SharePointNE.com
Delivery Targets (E-mail, SharePoint, Custom)
Nebraska SharePoint User Group Report Builder v2.0 Microsoft SQL Server 2008 Reporting Services Report Builder 2.0 delivers an intuitive, Office-like report authoring environment enabling business and power users to leverage their experience with Microsoft Office 2007 products. Microsoft SQL Server 2008 Reporting Services Report Builder 2.0 supports the full capabilities of SQL Server 2008 Reporting Services including: • • • •
Flexible report layout capabilities of SQL Server 2008 Report Definition Language Data Visualizations including charts and gauges Richly formatted textboxes Export to Microsoft Office Word format
Features specific to Report Builder 2.0 are focused on simplifying the process of creating and editing reports and queries and include the following: • • • •
Easy to use wizards for creating table, matrix and chart data regions Support for directly opening and editing reports stored on the report server Support for using server resources such as shared data sources Query designers for multiple data sources including a Microsoft SQL Server-specific query designer www.SharePointNE.com
16
Nebraska SharePoint User Group SharePoint Features from a BI Perspective WSS 3.0
MOSS Standard
MOSS Enterprise
Reporting Services Integration
Feature
PerformancePoint Integration
Excel Services
Report Center Site Template
Data Connection Libraries
Business Data Catalog
Business Data Web Parts
Native KPIs
Filter Web Parts
For full list: http://office.microsoft.com/en-us/products/FX101758691033.aspx www.SharePointNE.com
Nebraska SharePoint User Group
BI Applications • • • •
Excel Reporting Services PerformancePoint And more…
www.SharePointNE.com
17
Nebraska SharePoint User Group SQL Server Integration Services (SSIS) • Extract Transform and Load (ETL) – More trustworthy and reliable – Development environment – Many out-of-the-box transforms
• Extensible – – – –
Custom tasks Custom enumerations Custom transformations Custom data sources
Business Perspective Data in different systems needs to be connected and related Reporting often requires looking beyond more than one system
IT Perspective Provides the glue to connect systems www.SharePointNE.com
Nebraska SharePoint User Group
SQL Server Analysis Services (SSAS) • OLAP engine / Cubes • Advanced business intelligence – KPI/Perspectives – Custom/limited aggregations and semi-additive measures
• Web services • Integrated developer tools • Business Perspective • • •
Multi-dimensional views of information Improved speed Slicing and dicing
• IT Perspective • •
Stores predetermined aggregations Reduces reporting load on transactional systems
www.SharePointNE.com
18
Nebraska SharePoint User Group
www.SharePointNE.com
Nebraska SharePoint User Group Source Data
Warehouse Tables Consolidate (ETL) Integration Services
Users
Analysis Services Reporting Services PerformancePoint 2007
Office 2007 SharePoint 2007
Cubes
www.SharePointNE.com
19
Nebraska SharePoint User Group
www.SharePointNE.com
Nebraska SharePoint User Group
How ?
www.SharePointNE.com
20
Nebraska SharePoint User Group
Break it down Filters
Scorecards Charts Grids Reports Gauges
www.SharePointNE.com
Nebraska SharePoint User Group
Demo
www.SharePointNE.com
21
Nebraska SharePoint User Group
www.SharePointNE.com
Nebraska SharePoint User Group
www.SharePointNE.com
22
Nebraska SharePoint User Group
SharePoint Integrated BI Products • Business Objects – XI SharePoint Portal Integration Kit
• IBM/Cognos – QueryVision Web Parts for IBM Cognos 8
• MicroStrategy – Portlets via Portal Integration Kit
• Others… www.SharePointNE.com
Nebraska SharePoint User Group
www.HeartlandBI.com
www.SharePointNE.com
23
Nebraska SharePoint User Group
www.SharePointNE.com
Nebraska SharePoint User Group
www.SharePointNE.com
24
Nebraska SharePoint User Group
www.SharePointNE.com
25
