![Page 1: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/1.jpg)
The Last Mile:Delivering the Facts – Client Side Analysis
The Last Mile:Delivering the Facts – Client Side Analysis
![Page 2: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/2.jpg)
Agenda
Client Side AnalysisClient Side Analysis
Reporting vs. AnalysisReporting vs. Analysis
Office IntegrationOffice Integration
Pivot TablesPivot Tables
Excel Add-inExcel Add-in
SharePointSharePoint
![Page 3: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/3.jpg)
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
![Page 4: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/4.jpg)
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
![Page 5: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/5.jpg)
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
![Page 6: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/6.jpg)
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
![Page 7: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/7.jpg)
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
![Page 8: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/8.jpg)
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
![Page 9: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/9.jpg)
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
![Page 10: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/10.jpg)
Office Components
Excel PivotTablesExcel PivotTables
Excel PivotChartExcel PivotChart
Office Pivot Web ControlOffice Pivot Web Control
Local CubesLocal Cubes
![Page 11: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/11.jpg)
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
![Page 12: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/12.jpg)
Defining a Data Source
![Page 13: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/13.jpg)
Defining a Data Source
![Page 14: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/14.jpg)
Defining a Data Source
![Page 15: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/15.jpg)
Defining a Data Source
![Page 16: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/16.jpg)
Interacting with a PivotTable
![Page 17: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/17.jpg)
Working with PivotTables
FilteringFiltering
Highlighting a structured Highlighting a structured selectionselection
Displaying specific membersDisplaying specific members
Refreshing a pivot tableRefreshing a pivot table
![Page 18: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/18.jpg)
Using Pivot Charts
![Page 19: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/19.jpg)
Creating a Pivot TableCreating a Pivot Table
![Page 20: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/20.jpg)
Working with Local Cubes
Defining Local CubesDefining Local Cubes
Creating Local CubesCreating Local Cubes
![Page 21: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/21.jpg)
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
![Page 22: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/22.jpg)
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
![Page 23: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/23.jpg)
Office Web ControlsOffice Web Controls
![Page 24: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/24.jpg)
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
![Page 25: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/25.jpg)
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
![Page 26: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/26.jpg)
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
![Page 27: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/27.jpg)
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
![Page 28: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/28.jpg)
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
![Page 29: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/29.jpg)
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
![Page 30: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/30.jpg)
Excel Add-In for AnalysisExcel Add-In for Analysis
![Page 31: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/31.jpg)
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
![Page 32: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/32.jpg)
BI Portal
![Page 33: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/33.jpg)
SharePoint for BISharePoint for BI
![Page 34: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/34.jpg)
![Page 35: The Last Mile: Delivering the Facts – Client Side Analysis](https://reader036.vdocuments.us/reader036/viewer/2022062518/56649eb35503460f94bba695/html5/thumbnails/35.jpg)
© 2004 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.