Talk Agenda

1. Build a Browser-based OLAP Reporting SolutionUsing SQL Server 2000 Analysis Services, Microsoft Office XP Web Components, and ASP.NETSadra AbedinzadehFarzad PeyraviAshkan Zarnani

2. Talk Agenda • Overview • Introducing OLAP • Concepts • Analysis Services Manager • Office XP Web Components • Focus on the PivotTable component • Review features, user interface & API • Web-Based OLAP Report Architecture • Configure OLAP data source for HTTP • XML Web Services deliver XML data directly to the client

3. Features of a (Browser-based) OLAP Reporting Solution • Must connect with an OLAP data source • Must allow the user to drill down into the data with flexibility and ease • Must provide a user-friendly visual interface that includes charts, because reporting is most effective when it is visual. • Must allow the user to save and retrieve custom reports. • Should be browser-based: • Implemented as a server-based Web application • Accessible from a Web browser over the Internet or a public intranet

4. OLAP Glossary • Online Analytical Processing (OLAP) • Transforms relational data into multi-dimensional data structures, a.k.a., cubes, or, hypercubes • An OLAP repository may contain one or more cubes • Multi-Dimensional Data Expressions (MDX) • Specialized query language for multi-dimensional data structures • Data mining • The process of drilling down into the underlying details of a cube using MDX queries

5. Cube Aggregation • Cubes contain group-bys of all combinations of the included attributes.

6. Cube Glossary • Dimensions • A set of attributes that roughly correspond to RDBMS fields. A dimension is subdivided into levels. • Levels • A subdivision of a dimension. • Measures • The actual data value, typically numeric.

7. Demo:Connect to an OLAP Data Source from MS Office XP

8. OWC10 PivotTable

9. Demo:Web-Based OLAP Report Connect to an OLAP data source Create a new report using drag-drop Load an existing report from XML Save a report to XML

10. OLAP Report Architecture

11. PivotTable Interaction with an OLAP Data Source

12. Configure the OLAP Data Source for HTTP Access • Step 1: Install IIS on the database server where Analysis Services resides. • Step 2: Create a new Web site on the database server. Copy msolap.asp into this directory. • Location of msolap • Step 3: Assign an external domain name to the new Web site. • Step 4: Install an SSL certificate on the server, linked to the new Web site. • Step 5: Configure security credentials • Provider=MSOLAP.2;Data Source=http://localhost/msolap;Initial Catalog=Foodmart 2000;User ID=OLAPID;Password=test;

13. Security Considerations • You must enforce security credentials for an OLAP data source that is available over HTTP. • Analysis Services will assign a Cube role to a specific cube. This role restricts overall access to the cube and/or access to specific dimensions in the cube. • Create a security role as follows: • Create an NT User role • In SQL Server, create a Database role that maps to the NT User role • In Analysis Services, create a Cube role that maps to the Database role • For more information, consult “Creating Security Roles” in the Analysis Services help file at: http://msdn.microsoft.com/library/en-us/olapdmad/agsecurityroles_0t2r.asp?frame=true • Once the role has been created, add the NT User role credentials to the OLE DB connection string for the OLAP data source. HTTP requests to the OLAP data source will be authenticated using these credentials.

14. Office XP Web Components • PivotTable Component • Connects to any OLAP data source that supports the Microsoft OLE DB Provider for OLAP Services 8.0 and higher. • Connects to any data source with an OLE DB provider, including MS SQL Server, MS Analysis Services, MS Access, and MS Excel. • Allows users to analyze data by pivoting, grouping, filtering, and sorting. • Spreadsheet Component • Provides an Excel-like spreadsheet user interface, including a recalculation engine and an extensive function library. • Chart Component • Graphically displays data from a bound Data Source, PivotTable or Spreadsheet control. Refreshes automatically when data re-pivots. • Data Source Component • Manages interactive communication with the data source.

15. Benefits of OWC • Provides users with interactive access to data, from a variety of OLE DB-accessible data sources. • OWC provides a familiar Microsoft Office user interface, and a rich subset of Office functionality. • OWC is flexible: • The components may be used interactively, with their visible interfaces. • The components may be used programmatically, with their APIs.

16. Using OWC • They are COM components that provide both a visible interface and an API. • They are designed for use as both client-side and server-side components. • OWC can be hosted as ActiveX controls inside a Web browser: • They provide a visible interface. • They provide a scripting interface for any Windows scripting language, incl. JavaScript and VBScript. • OWC can be hosted on a standard Windows form, providing a visible interface. • OWC can be instanced in server-side code for access to the base services, without having to use the visible interface.

17. OWC 9 vs. OWC 10 • The OWC components were dramatically improved between versions 9 and 10. • PivotTable: more filtering and grouping options, plus calculated fields and support for custom MDX queries. • Spreadsheet: Improved calculation engine and tighter integration with MS Excel 2002, incl. Support of a common XML spreadsheet format. • Chart: Expanded charting options and improved integration with the PivotTable component. • OWC 9 (msowc.dll) and OWC 10 (owc10.dll) support side-by-side usage • Clients must have a valid Office XP license for interactive access to OWC 10 • Clients without a license have restricted access to ‘View Only’ mode

18. PivotTable Features • Used for data reporting and data analysis solutions incl. summary and cross-tabulation of raw, multi-dimensional data sets. • Filtering • Grouping (Ad-hoc and Interval) • Calculated Totals • Custom MDX • Custom Sorting • Drillthrough of hierarchical data • Export to Excel • Member Properties

19. PivotTable List Object Model • View the OWC API documentation at: C:\Program Files\Common Files\Microsoft Shared\Web Components\10\1033\OWCVBA10.CHM

20. PivotTable List API Overview

21. PivotTable API

22. PivotView API

23. PivotFieldSet API

24. PivotField API

25. PivotTable Events

28. Client Requirements • Clients must meet the following requirements to use the OWC PivotTable interactively in their browser: • Install Pivot Table Services (PTS) • OLE DB Provider 8.0 • MDAC • Note: Analysis Services SP3 contains the latest version of PTS. Do not use an earlier version. • Have a licensed copy of Office XP, or, an enterprise license agreement file • Be running IE 5.0 or greater • Note, technically you only need IE 4.01, but the Trusted Sites dialog is not available until IE 5.0 • Set up Trusted Sites to include the domain for the OLAP data source (to enable cross-domain access)

29. References • Analysis Services Overview: • MSDN Library > SQL Server 2000 > SDK Documentation > Analysis Services • Microsoft MMC/Analysis service Help • KB Article 279489: How to Connect to Analysis Server 2000 by Using HTTP Connection • Microsoft Office Solutions Articles: Pivoting Tables, Parts I and II by David Stearns • Office Solutions Development > Microsoft Office > Microsoft Office (General) > Periodicals > Microsoft Office Solutions 2002 • Introduction to Multidimensional Expressions (MDX) • MSDN Library > Data Access > OLAP Services • Newsgroup: • microsoft.public.office.developer.web.components