1 / 19

BI Features of Excel 2007 - Agenda

BI Features of Excel 2007 - Agenda. Role of Excel in Microsoft BI stack New BI (and BI-related) features Compatibility experience and issues Missing cube / OLAP features vs. Proclarity MOSS 2007 and Excel Services Demos using Adventure Works OLAP cube. End To End BI Offering.

catrin
Download Presentation

BI Features of Excel 2007 - Agenda

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BI Features of Excel 2007 - Agenda • Role of Excel in Microsoft BI stack • New BI (and BI-related) features • Compatibility experience and issues • Missing cube / OLAP features vs. Proclarity • MOSS 2007 and Excel Services • Demos using Adventure Works OLAP cube

  2. End To End BI Offering Data Warehouse Infrastructure Reporting and Analysis Performance Management

  3. Excel 2007Worlds most popular BI front end client just got better Knowledge, Discovery & Insight Excel 2007 offers complete support for SQL Server 2005 Analysis Services Easily find trends in data Excel Conditional Formatting Strong SQL Server 2005 Integration

  4. Excel 2007 Big BI Bets Excel 2007 was redesigned to boost individual productivity, calculation performance, business insight and server integration

  5. BI-related benefits of Excel 2007 • Much larger data sets: 1 million rows, 16K columns • Dynamic preview of format and style changes • Redesigned charting engine, used throughout Office • More powerful and flexible conditional formatting • MOSS and Excel Services provide browser access • Support for most Analysis Services 2005 features • Can convert cube pivot tables into cell-based reports

  6. New features to support AS 2005 OLAP cubes • Redesigned Pivot Table field list - easier navigation • Data Connection properties editing (for maintenance) • Can apply server-defined formatting rules to cells • Conditional formatting adapts to pivot table changes • Supports AS 2005 Key Performance Indicators (KPIs) • Compact Pivot Table Layout for nested hierarchies • Support for drill through and other cube actions • Label and Value pivot filters work with cube data • Show member properties in tooltip text or as column

  7. Pivot table demos with AS 2005 OLAP cube • Pivot Table field list – display folders, KPI’s, sets … • Data Connection properties editing (for maintenance) • Server-defined formatting rules: format, color .. • AS 2005 Key Performance Indicators (KPIs): icons • Pivot Table Layout: compact for nested hierarchies • Double-click todrill through; cube report action • Label and Value pivot filters: names, Top 10 products • Member properties: Adventure Works Customers

  8. Convert cube pivot table to cell-based report • Some scenarios not suited to Pivot Tables • "Asymmetric reports” - rows or columns don’t follow fixed pattern • Reports that mix data from multiple data sources • Reports with custom spacing, precise grouping and ordering • Convert PivotTable cells to worksheet formulas • 7 new “cube” functions available • Places OLAP cube data directly into specific spreadsheet cells • Power of multi-dimensional data store with Excel formula engine • CUBEMEMBER (connection, member_expression,[caption]) • CUBEVALUE (connection, [member_expression_1], .. • CUBESET (connection, set_expression, [caption], .. • CUBESETCOUNT (set) • CUBERANKEDMEMBER (connection, set_expression, .. • CUBEMEMBERPROPERTY (connection, member_expression, .. • CUBEKPIMEMBER (connection, kpi_name, kpi_property, ..

  9. Demos of OLAP cube cell formulas • Convert pivot table with filters to cell formulas • Inspect and explain the different cell functions used • Examples of asymmetric sets

  10. Excel 2007 versus Proclarity - feature gap • No UI to create MDX Named Sets, Calc. Members • Can’t edit MDX query submitted to OLAP cube server • No equivalent for Proclarity “decomposition tree” • Other visualizations like “heat map” aren’t supported

  11. Excel 2007 compatibility considerations • Read / write Excel 2003 (.XLS) files, less functionality • Support for older versions of Pivot Table (vs. file) • Pivot table data refresh could affect multiple tabs • Calculated member in page filter can cause problems • Visual Totals can’t be disabled (due to subquery) • Whitepaper – “Migrating to Excel 2007” • Excel Team Blog – Backwards Compatibility topics

  12. Extending Excel with Excel Services Browser Excel 2007 High quality Web rendering Design and author View and Interact Zero-footprint Save to SharePoint Interactive: Set parameters, sort, filter, explore Save Spreadsheets Excel 2007 Office SharePoint Server 2007 Export/Snapshot into Excel Open in Excel for rich exploration and analysis Spreadsheets stored in document libraries Open snapshots Spreadsheet calculation & rendering Custom applications Set values, perform calculations, get updated values via Web services Programmatic Access External data retrieval and caching Retrieve full workbook file 100% calculation fidelity

  13. Key usage scenarios for Excel Services • Sharing spreadsheets through a browser • Author saves spreadsheet to a SharePoint document library • Pure DHTML viewer – no ActiveX, no client copy of Excel 2007 • Author can limit access to specific sheets or even cell ranges • Building business intelligence (BI) dashboards • Build dashboard and reporting portals quickly and without code • Ability to re-use Excel spreadsheets in dashboards • Use Parameters and SharePoint Filters to centralize “slicing” • Can add non-Excel SharePoint Web Parts like KPI List • Reuse spreadsheet business logic in custom apps • Provides a web-service-based interface to custom apps • Calling app can change values, recalculate the spreadsheet ..

  14. Dashboard page based on Excel 2007 (from Excel Team Blog)

  15. Key to areas of sample Dashboard page • Built on SharePoint – consistent look and feel • Standard portal functionality such as search, personalization .. • Web Parts added as needed to customize functionality of page • Excel Services Web Parts - 1 Table and 1 Chart • Configure Excel Services to show only portions of a workbook • SharePoint Key Performance Indicator (KPI) List • Visualization of key status and trends (sourced from OLAP cube) • SharePoint SQL Server Filters • Sourced from dimension data in OLAP cube • Connected to other parts on the page, so users can “slice” data

  16. Demos of Excel Services used in MOSS pages • Publish Excel pivot table which uses cube data • Configure parameters and visible part of worksheet • Drop Excel Services Web Parts on dashboard page • Add SQL Filter and KPI List to page, and connect

  17. Summary • Office Excel 2007 and Analysis Services 2005 create exciting new user experiences with data • Interactive: lightning fast performance on business queries • Friendly: business names, folders, hierarchies, formatting • Personalized: perspectives, translations • Rich: calculations, named sets, exception highlighting, actions, KPIs • Powerful: KPIs, OLAP formulas, visuals, interactive charts

  18. Excel 2007Resources • Microsoft archived webcasts on Excel 2007 BI • Building Business Intelligence Solutions Using Office Excel 2007 and SQL Server 2005 Analysis Services • Microsoft Office Excel 2007 and SQL Server 2005 Analysis Services Integration Explained • Creating Effective Conditional Formats Using Excel 2007 • Microsoft Office links of interest: • What's new in Microsoft Office Excel 2007 • Introduction to Excel Services and Excel Web Access • Excel 2007-related blog: • Excel and Excel Services Team Blog • Blog entry: CUBE Functions Part 1

More Related