Build a Browser-based OLAP Reporting Solution Using SQL Server 2000 Analysis Services, Microsoft Office XP Web Components, and ASP.NET Sadra Abedinzadeh Farzad Peyravi Ashkan Zarnani

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
DemoConnect to an OLAP Data Source from MS
Office XP
8
OWC10 PivotTable
9
DemoWeb-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
• ProviderMSOLAP.2Data Sourcehttp//localhost/mso
  lapInitial CatalogFoodmart 2000User
  IDOLAPIDPasswordtest

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/a
  gsecurityroles_0t2r.asp?frametrue
• 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
PivotTable Represents the container for the PivotTable list.
PivotData Represents the data in a PivotTable list.
PivotResultAxis Represents the data in Column, Row, Data, Filter and Group axes.
PivotTotal Represents a total in a PivotTable list. A total is the aggregate value that is displayed for the contents of a given cell.
PivotFieldSet Represents a set of fields that together form a hierarchy.
PivotView Represents a specific view of a PivotTable list.
21
PivotTable API
ConnectionString The OLE DB connection string.
DataMember The DataMember name, equivalent to the OLAP cube name.
ActiveView Returns the PivotView object that represents the current view of the PivotTable.
XMLData XML data for the current PivotTable report, including connection information, data and formatting.
22
PivotView API
ColumnAxis Represents a column in the PivotTable.
RowAxis Represents a row in the PivotTable.
DataAxis Represents a data field in the PivotTable.
FilterAxis Represents a filter field in the PivotTable.
FieldSets A collection of PivotFieldSet objects. Fieldsets are added using the PivotAxis InsertFieldSet method.
AddFieldSet Adds a custom fieldset to the PivotView.
Totals A collection of PivotTotal objects.
23
PivotFieldSet API
Fields A collection of PivotField objects
AllIncludeExclude Enumeration for the inclusion state of all members not listed in a specific fields IncludedMembers or ExcludedMembers list.
AddCalculatedField Adds a calculated field to the PivotFieldSet. Must first create the custom field using PivotView.AddFieldSet. Then add the calculated field and update the fieldset in the PivotTable using DataAxis.InsertFieldSet.
AddCustomGroup Adds a custom group field to the PivotFieldSet.
24
PivotField API
FilterFunction Enumeration for filter type.
FilterFunctionValue The value to filter by.
FilterOn The PivotTotal to filter on.
SortOn The PivotTotal to sort on.
SortDirection The sort direction.
IncludedMembers An array of field members to include in the view.
ExcludedMembers An array of field members to exclude from the view.
25
PivotTable Events
OnConnect Occurs whenever the PivotTable list connects to a data source.
PivotTableChange Occurs whenever a PivotTable list field, field set, or total is added or deleted.
Query Occurs whenever a PivotTable list query becomes necessary.
SelectionChange Occurs whenever the user makes a new selection.
ViewChange Occurs whenever the PivotTable is redrawn. Provides a PivotViewReasonEnum to explain why the redraw occurred.
Click Occurs whenever the user clicks the PivotTable control.
26
DemoWeb-Based OLAP ReportStep Through Code

• Connect to an OLAP data source
• Create a new report using drag-drop
• Load an existing report from XML
• Save a report to XML

27
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)

28
References

• Analysis Services Overview
• MSDN Library gt SQL Server 2000 gt SDK
  Documentation gt 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 gt Microsoft Office gt
  Microsoft Office (General) gt Periodicals gt
  Microsoft Office Solutions 2002
• Introduction to Multidimensional Expressions
  (MDX)
• MSDN Library gt Data Access gt OLAP Services
• Newsgroup
• microsoft.public.office.developer.web.components