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. ReportPortal makes all calls to the XMLA service directly from the browser using AJAX. 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 might pose 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, list box 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 Connections

Some users can be restricted to using only some connections when creating a report. Connections can be made accessible to multiple users and a user can use multiple connections. The many-to-many relationship is resolved with the ConnectionUserPermission table. Similarly, connections can be accessible by multiple user roles and a user role can use multiple 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.

Report Scheduler

Schedule table stores definitions for a public or private subscription Schedule

ScheduleDay table stores the days for a Calendar schedule type.

ScheduleMonth table stores the days for a Calendar and Week Number schedule type.

ScheduleWeek table stores the days for a Week Number schedule type.

Job table stores definitions for a subscription

JobFilter table stores job Filter information

JobRole table stores list of Roles that will receive the subscription

JobUser table stores list of Users that will receive the subscription

JobHistory table stores run history for a subscription

Ratings Tables

ReportRating table stores ratings (1-5 stars) for each report

ReportFav table stores list of favorite reports for each user

ReportNote table stores report comments

ReportNoteRating table stores ratings (Like/Dislike) for each report comment

Wiki Tables

WikiVersion table stores definitions for each Wiki Version including the latest one

WikiRating table stores data for each Wiki rating definition

WikiRatingItem table stores ratings (1-5 stars) each Wiki rating

Blog Tables

BlogEntry table stores data for each Blog Entry

OLAP Comment Tables

ReportComment table stores cell comment data for OLAP and OWC reports

ColorPalette Tables

ColorPalette table stores definitions for a Color Palette

Colors table stores colors for each Color Palette

Language Tables

Language table stores definitions for each language

LanguagePhrase table stores phrases for localization.

SharedFilter Tables

SharedFilter table stores definitions for shared filters. A shared filter can be used my multiple report filters (QueryFilter) .

Logging Tables

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

AppUserPasswordHist table stores old passwords for each user in encrypted format when this option is enabled: Admin > Settings > Password > Enforce password history

LoginAttempt table stores information about successful and unsuccessful login attempts

Other Tables

AppSettings table stores global application settings information.

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

EmailTemplate table stores data for Email Templates.

AppUserDrillThrough table stores user settings for drill-through dialog.

SecurityToken table stores tokens for Single Sign-on.