Transcript The Last Mile - Client Side Analysis

The Last Mile:
Delivering the Facts – Client Side Analysis
Agenda
Client Side Analysis
Reporting vs. Analysis
Office Integration
Pivot Tables
Excel Add-in
SharePoint
Client Side Analysis
Make Data Available to Everyone
Forecasting
“What If” Analysis
Microsoft Office 2003
Sharepoint Portal Server 2003
Reporting vs. Analysis
Reporting
Analysis has been done
Data is static
Analysis
Data is dynamic and editable
Forecasting
Microsoft Office 2003
Easy to use
Familiar
You probably already own it
Users know how to use it
Powerful and Flexible
Microsoft BI Architecture
•
•
•
•
Excel Add-in for SQL Server
Portals
Dashboards
Data Analysis
Visualization
• Messaging
Services
• Project
Management
SQL Server
Relational Engine
Reporting Services
Analysis Services
OLAP & Data Mining
Windows Server
Management
Tools

Windows Client
BI Solution Accelerator
Developer Tools
Microsoft Business Solutions
Financial Analytics
Office System
Business Intelligence And Reporting
User Profiles
SQL Server
Analysis Services
5-10% of users
Analysts
Information 15-25% of users
Explorers
SQL Server
Reporting Services
Information
Consumers
65-80% of users
SQL Server Reporting Services
Report developers
can create reports
using the Reporting
Services XML Report
Definition Language
(RDL)
Authoring
Managed reports are
executed either ondemand or scheduled
and are cached for
performance and
consistency
Management
Users can view
reports from a webbased front-end or
subscribe for reports
to be delivered in email
Delivery
Online
Access
Report
Definition
Managed
Report
Delivery
Channels
Business Intelligence And Reporting
User Profiles
SQL Server
Analysis Services
5-10% of users
Analysts
Information 15-25% of users
Explorers
SQL Server
Reporting Services
Information
Consumers
65-80% of users
Office Components
Excel PivotTables
Excel PivotChart
Office Pivot Web Control
Local Cubes
Using Excel Pivot Tables
Defining a data source
Interacting with a pivot table
Working with pivot tables
Formatting pivot tables
Defining a Data Source
Defining a Data Source
Defining a Data Source
Defining a Data Source
Interacting with a PivotTable
Working with PivotTables
Filtering
Highlighting a structured selection
Displaying specific members
Refreshing a pivot table
Using Pivot Charts
Creating a Pivot Table
Working with Local Cubes
Defining Local Cubes
Creating Local Cubes
Defining Local Cubes
Are similar to server-based cubes, but
normally contain less data (more focused)
Can be created two ways
Excel pivot table report
Microsoft Query
Office Web Controls
Saving pivot tables as web pages
Manipulating the OLAP control
Displaying the field list
Adding and removing items
Filtering and Pivoting
Office Web Controls
Excel Add-In for Analysis Reporting
Provides Excel-based reporting and
analysis
Better than just a pivot table
Free-form and semi-structured
reporting
Excel Add-In for Analysis Reporting
Multiple data sources on the same report
Based upon:
Microsoft Excel 2003 or Excel XP
Microsoft SQL Server 2000 and Beyond
Microsoft Windows XP or Windows 2003
Server
Excel Add-In for SQL Server:
Requirements
Prerequisites
Server
Windows Server 2000 or 2003
SQL Server 2000 SP3 or Later
SQL Server Analysis Services SP3 or Later
Client
Excel XP or Excel 2003
Architecture Overview
Report
Builder UI
Cube
Metadata
Manager
Free Form
and Semi
Structured
Reports
Report
layout,
Report
interactions
Reporting
Add-in
Metadata
Manager
Retrieves and
shares cube
information
Cube
Cube
Reporting
Add-in
Manager
Query
Manager
Cube


Formula
management
Query
creation and
exporting
Query
consolidation
and execution
Structured Reports
Geography
Order Type
Customer
Capability
• Using dynamic set expressions on an
axis
• Drill Up, Drill Down, Expand, Collapse,
Focus and Eliminate, Pivoting
• Create reports with nested axis
• Analysis Services’ Actions, drill through,
write back and server side formatting
• Refresh reflects member changes
• Create custom filter expressions
Limitation
• Create Reports from single OLAP
source only
• Cannot Insert row or column
• Cannot add external data within the
report
• Cannot add member properties to the
report
• Cannot sort members
Free-Form Reports
Sales Cube
Mktg Campaign Cube
•
•
•
•
•
•
•
Excel Calculations
Capability
Limitation
Access multiple OLAP sources
Intersperse OLAP and Excel data
Insert rows and columns
Excel’s native formatting and sorting
Member properties in rows and columns
Create nested axis – cross-join
Analysis Services’ Actions, drill through,
write back and server side formatting
• Cannot Use refresh report or a page
member change, to bring new members
for row or column headers.
• No drill up or down
Excel Add-In for Analysis
SharePoint and Business
Intelligence
Build BI Portal and Dashboard Solutions
Web Parts
Windows ASP.NET-based Components
BI Portal
SharePoint for BI
© 2004 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.