the last mile: delivering the facts – client side analysis
TRANSCRIPT
The Last Mile:Delivering the Facts – Client Side Analysis
The Last Mile:Delivering the Facts – Client Side Analysis
Agenda
Client Side AnalysisClient Side Analysis
Reporting vs. AnalysisReporting vs. Analysis
Office IntegrationOffice Integration
Pivot TablesPivot Tables
Excel Add-inExcel Add-in
SharePointSharePoint
Client Side Analysis
Make Data Available to EveryoneMake Data Available to Everyone
ForecastingForecasting
““What If” AnalysisWhat If” Analysis
Microsoft Office 2003Microsoft Office 2003
Sharepoint Portal Server 2003Sharepoint Portal Server 2003
Reporting vs. Analysis
ReportingReportingAnalysis has been doneAnalysis has been done
Data is staticData is static
AnalysisAnalysisData is dynamic and editableData is dynamic and editable
ForecastingForecasting
Microsoft Office 2003Easy to useEasy to use
Familiar Familiar
You probably already own itYou probably already own it
Users know how to use itUsers know how to use it
Powerful and FlexiblePowerful and Flexible
Microsoft BI Architecture
Man
agem
ent
Man
agem
ent
To
ols
To
ols
Dev
elo
per
To
ols
Dev
elo
per
To
ols
• PortalsPortals• DashboardsDashboards• Data AnalysisData Analysis• VisualizationVisualization
• Messaging Messaging ServicesServices
• Project Project ManagementManagement
Windows ServerWindows Server
Win
do
ws
Cli
ent
Win
do
ws
Cli
ent
Mic
roso
ft B
usi
nes
s S
olu
tio
ns
Fin
anci
al A
nal
ytic
sF
inan
cial
An
alyt
ics
Excel Add-in for SQL Excel Add-in for SQL ServerServer
BI Solution AcceleratorBI Solution Accelerator
SQL ServerRelational Engine
Reporting ServicesReporting Services
Analysis ServicesAnalysis ServicesOLAP & Data MiningOLAP & Data Mining
Office System
Business Intelligence And Reporting User Profiles
AnalystsAnalysts
Information Information ConsumersConsumers
Information Information ExplorersExplorers
5-10% of users
15-25% of users
65-80% of users
SQL Server Reporting Services
SQL Server Analysis Services
SQL Server Reporting Services
Report developers can Report developers can create reports using create reports using the Reporting the Reporting Services XML Report Services XML Report Definition Language Definition Language (RDL)(RDL)
ReportReportDefinitionDefinition
ReportReportDefinitionDefinition
AuthoringAuthoring ManagementManagement
ManagedManagedReportReport
ManagedManagedReportReport
DeliveryDeliveryChannelsChannelsDeliveryDelivery
ChannelsChannels
OnlineOnlineAccessAccessOnlineOnlineAccessAccess
DeliveryDelivery
Managed reports are Managed reports are executed either on-executed either on-demand or scheduled demand or scheduled and are cached for and are cached for performance and performance and consistencyconsistency
Users can view Users can view reports from a web-reports from a web-based front-end or based front-end or subscribe for reports subscribe for reports to be delivered in e-to be delivered in e-mailmail
Business Intelligence And Reporting User Profiles
AnalystsAnalysts
Information Information ConsumersConsumers
Information Information ExplorersExplorers
5-10% of users
15-25% of users
65-80% of users
SQL Server Reporting Services
SQL Server Analysis Services
Office Components
Excel PivotTablesExcel PivotTables
Excel PivotChartExcel PivotChart
Office Pivot Web ControlOffice Pivot Web Control
Local CubesLocal Cubes
Using Excel Pivot Tables
Defining a data sourceDefining a data source
Interacting with a pivot tableInteracting with a pivot table
Working with pivot tablesWorking with pivot tables
Formatting pivot tablesFormatting 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
FilteringFiltering
Highlighting a structured Highlighting a structured selectionselection
Displaying specific membersDisplaying specific members
Refreshing a pivot tableRefreshing a pivot table
Using Pivot Charts
Creating a Pivot TableCreating a Pivot Table
Working with Local Cubes
Defining Local CubesDefining Local Cubes
Creating Local CubesCreating Local Cubes
Defining Local Cubes
Are similar to server-based cubes, Are similar to server-based cubes, but normally contain less data (more but normally contain less data (more focused)focused)
Can be created two waysCan be created two waysExcel pivot table reportExcel pivot table report
Microsoft QueryMicrosoft Query
Office Web Controls
Saving pivot tables as web pagesSaving pivot tables as web pages
Manipulating the OLAP controlManipulating the OLAP control
Displaying the field listDisplaying the field list
Adding and removing itemsAdding and removing items
Filtering and PivotingFiltering and Pivoting
Office Web ControlsOffice Web Controls
Excel Add-In for Analysis Reporting
Provides Excel-based reporting Provides Excel-based reporting and analysisand analysis
Better than just a pivot tableBetter than just a pivot table
Free-form and semi-structured Free-form and semi-structured
reportingreporting
Excel Add-In for Analysis ReportingMultiple data sources on the same Multiple data sources on the same
reportreport
Based upon:Based upon:Microsoft Excel 2003 or Excel XPMicrosoft Excel 2003 or Excel XP
Microsoft SQL Server 2000 and BeyondMicrosoft SQL Server 2000 and Beyond
Microsoft Windows XP or Windows 2003 Microsoft Windows XP or Windows 2003 ServerServer
Excel Add-In for SQL Server: Requirements
PrerequisitesPrerequisitesServerServer
Windows Server 2000 or 2003 Windows Server 2000 or 2003
SQL Server 2000 SP3 or LaterSQL Server 2000 SP3 or Later
SQL Server Analysis Services SP3 or SQL Server Analysis Services SP3 or LaterLater
ClientClientExcel XP or Excel 2003Excel XP or Excel 2003
CubeCubeCubeCube CubeCube
Formula Formula managementmanagement
Query Query creation and creation and exportingexporting
Retrieves and Retrieves and shares cube shares cube informationinformation
Query Query consolidation consolidation and executionand execution
Report Report layout, layout, Report Report interactions interactions
Free Form Free Form and Semi and Semi Structured Structured ReportsReports
Architecture Overview
Report Report Builder UIBuilder UI Reporting Reporting
Add-inAdd-inMetadataMetadataManagerManager
ReportingReportingAdd-in Add-in
ManagerManager
CubeCubeMetadataMetadataManagerManager
QueryQueryManagerManager
Structured Reports
• 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
• 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
Capability Limitation
Customer
Geography
Order Type
Free-Form Reports
• 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
Capability Limitation
Sales Cube
Mktg Campaign Cube Excel Calculations
Excel Add-In for AnalysisExcel Add-In for Analysis
SharePoint and Business Intelligence
Build BI Portal and Dashboard Build BI Portal and Dashboard SolutionsSolutions
Web Parts Web Parts
Windows ASP.NET-based ComponentsWindows ASP.NET-based Components
BI Portal
SharePoint for BISharePoint 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.