ReportPortal accesses Microsoft SQL Server Analysis Services via a XMLA (XML for Analysis) web service. The web service is implemented as an ISAPI dll. ReportPortal configures the web service during the installation. For SQL Server 2000, the web service is distributed as XML for Analysis SDK. The name of the ISAPI DLL is msxisapi.dll. The service reads datasources.xml configuration file to get the connection information for the available olap servers. For SQL Server 2005, the web service is part of Analysis Services standard installation. The name of the ISAPI DLL is msmdpump.dll. The configuration file is msmdpump.ini. Notethat unlike Pro-Clarity, Cognos' PowerPlay, Business Object's WebIntelligence and other olap client tools, XMLA provides the middle tier with the least amount of overhead. XMLA calls OLE DB Provider for Olap Services directly bypassing the ADOMD. Report Portal makes all calls to the XML/A service directly from the Internet Explorer.  All of the application state is maintained the browser and round trips to the server are minimized. Note that exposing XMLA web service to the web poses a security risk. Therefore, in the Internet environment XMLA web service has to be secured by: 1) Disabling Anonymous and enabling only Basic authentication. 2) Enabling HTTPS and requiring only secure communication.

Database Design

The information about a report is stored in the Report table. The system supports different type of reports such as OLAP and ROLAP reports.

The report type information is stored in the ReportType table.

A report resides in a folder. The folders structure information is stored in the QueryFolder table.

SQL Reports can also have textbox, listbox and checkbox filters. The filter information is stored in the QueryFilter table.

OLAP Reports can have comments. The comment information is stored in the ReportComment table.

The ReportSnopshot table holds the snapshots history for the report. A report can have many snapshots.

The application user information is stored in the AppUser table. The role information is stored in the Role table. Users can have multiple roles and roles can have multiple users. The many-to-many relationship is resolved with the UserRole table.

The Entity Relationship Diagram below shows the relationships between the tables.

Security for viewing reports

Report can be visible to multiple users and a user can see multiple reports. The many-to-many relationship is resolved with the UserPermission table. Similarly, Report can be visible to multiple user roles and a user role can see multiple reports. The many-to-many relationship is resolved with the RolePermission table.

Reports can be made visible by placing them into a folder that can be accessed by a user or a group. Folders can be made accessible to multiple users and a user can see contents of multiple folders. The many-to-many relationship is resolved with the FolderUserPermission table. Similarly, Folder can be accessible by multiple user roles and a user role can see multiple folders. The many-to-many relationship is resolved with the FolderRolePermission table.

Security for publishing reports

Reports can be made visible by placing them into a folder that can be accessed by a user or a group. Users can be restricted from publishing reports into specific folders. Folders can be made accessible to multiple users and a user can publish to multiple folders. The many-to-many relationship is resolved with the FolderUserPermission table. Similarly, Folder can be accessible by multiple user roles and a user role can publish to multiple folders. The many-to-many relationship is resolved with the FolderRolePermission table. Both tables have column PermissionType that should equal to two when tables are used for publishing access.

Some users can be restricted to publishing only some types of reports. (e.g. OLAP, SQL of KPI). Report Types can be made accessible to multiple users and a user can create multiple types of reports. The many-to-many relationship is resolved with the ReportTypeUserPermission table. Similarly, Report Types can be accessible by multiple user roles and a user role can create multiple types of reports. The many-to-many relationship is resolved with the ReportTypeRolePermission table.

Approving Reports

Published reports can be approved before they become visible to the assigned report viewers. Approvers can be assigned to a folder. Folders can require multiple approvers and approvers can approve reports in multiple folders. The many-to-many relationship is resolved with the FolderUserPermission table. The PermissionType column should equal to 3 when it is used for approving access.

Every time a user approves a report an entry is made to the ReportApprove table. Once the report is finally approved the Report.IsApproved column is set to true.

Security for SQL Connections

Some users can be restricted to using only some SQL connections when creating an SQL report. SQL connections can be made accessible to multiple users and a user can use multiple SQL connections. The many-to-many relationship is resolved with the ConnectionUserPermission table. Similarly, SQL connections can be accessible by multiple user roles and a user role can use multiple SQL connections. The many-to-many relationship is resolved with the ConnectionRolePermission table.

ROLAP Cube

Cube table stores basic information about the cube such as Fact Table and Table Alias. A cube can have many Measures (CubeMeasure), Aggregations(CubeAgg) and Dimensions (CubeDimension). A Dimension can have many Properties. A Property can be organized into many Hierarchies (CubeHierarchy). The many-to-many relationship between Property and Hierarchy is resolved by CubeHierarchyProperty table. An aggregation can be related to multiple dimensions and a dimension can be related to multiple aggregations. The many-to-many relationship is resolved by CubeHierarchyProperty table

Style

StyleClass table stores class names and css style text for the standard theme.

StyleTheme table stores theme names (Standard, Professional, Modern, etc.)

StyleThemeClass stores css style text for the non standard themes.

StyleClassComment stores class comments for a language (English, Spanish, etc)

User Filter Value

FilterDef table stores definitions for User Filter Values.

FilterReport table stores the relationship between a report and User Filter Value definition.

FilterUser table stores the relationship between a user and User Filter Value definition.

FilterRole table stores the relationship between a role and User Filter Value definition.

Other Tables

AppSettings table stores global application settings information.

CalculatedField table stores calculated measures and named sets for a cube.

UsageLog and UsageLogAction tables are used to log user activity. They allow to record and view who did what to which report and when.