Microsoft Business Intelligence Portal[转载]

 Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Microsoft Business Intelligence Portal

 

Tomer Ben-Moshe
Microsoft Corporation

May 2003

Applies to:
   Microsoft® Office XP

Summary: The Microsoft Business Intelligence Portal (BI Portal) is an integrated, Web-based online analytical processing (OLAP) solution that enables employees in an organization to create and share OLAP or Relational views, based on online OLAP services, offline cube files and Relational Database. (31 printed pages)

Download the BIP.exe from Microsoft Download Center.

Note:   This solution is unsupported. Security patches and bug fixes will not be provided. In the United States and Canada, you may request telephone assistance with this solution (for an hourly rate) through Microsoft Advisory Services.

Contents

Introduction
BI Portal Features
Installing BI Portal
How To Install
Using the BI Portal
BI Portal Architecture
Multi-Lingual Support
Offline Mode and Synchronization Mechanism
BI Portal Database Schema Description
BI Portal Add-ins
BI Portal Web Part API

Introduction

The Microsoft® Business Intelligence Portal (BI Portal) is an integrated, Web-based Online Analytical Processing (OLAP) solution that enables employees in an organization to create and share OLAP or Relational views, based on online OLAP services, offline cube files and Relational Database.

This article reviews BI Portal solutions, and describes its architecture.

It's assumed that the reader is familiar with:

• OLAP concepts.
• Microsoft Office Web Components (OWC) technology.
• Microsoft Windows and Microsoft .NET security.

BI Portal Features

This section describes the main functionalities of the BI Portal.

BI Portal terminology

• View—A graphical presentation of a requested query executed on a requested source of data. The definition of a presentation may include a combination of a Pivot table, a chart and a Data Analyzer display.
• Category Tree—A logic tree of subjects, which is used to store views in.
• Folder—A container of a Category Tree and Views.
• Data Source—A connector object to some type of data: OLAP server, Offline cube file or Relational Database server. A data source encapsulates all the properties of a connection such as Server name, Database name and Cube/Table name.
  When a View is created, a Data Source on which the view is based must be selected.

Features

Microsoft OLAP Client Tools

The BI Portal integrates Microsoft OLAP client tools (Viewers) into an overall OLAP solution. These tools include Microsoft PivotTable® and Chart Office Web Components (OWC), and the Microsoft Data Analyzer (DA).

These tools are used to display a View. A view defines a data source, a query made on that source and its presentation layout. For example: "Sales by country" View may define that the source of the data is the "Sales" cube on "FoodMart" database on "Analysis1" server. It also may define that the data will be displayed using the PivotTable and Chart Office Web Components.

Public and Private Folders/Categories/Views

The BI Portal includes a Category Tree, which is used by users to save views by subject. These logic trees are located inside folders, which serve as an additional hierarchy for saving Views.

Folders, categories and views may be defined as private or public. A private folder/category/view is visible only to the owner (the user who created it). A public folder/category/view is visible to all BI Portal users.

For public objects, the BI Portal also provides a Read-Only attribute, which enables a user to create a public object (such as a View) but disables all other users from changing it.

Support various data source types

The BI Portal can connect to the following data source types:

• OLAP—Connect to MS SQL Server Analysis Services (Online cube).
• Offline Cube—Connect to a cube file (.cub), located on some windows share.
• Relational—Connect to a relational database and display a requested table/view.

No matter what data source is selected, the data is displayed in the same way, using the PivotTable and Chart Office Web Components, and the Data Analyzer.

Windows-Integrated Security

Each user is authenticated when connecting to the BI Portal, using the Windows-Integrated authentication method. The BI Portal server validates the user to be a member of one of the following Windows user groups:

User group	BI Portal Permissions
BIP_USERS	BI Portal Users. Are allowed only to create private objects (Folders, Categories and Views).
BIP_PW	BI Portal Power Users. These users can create/update/delete private and public objects (Folders, Categories and Views).
BIP_ADMIN	BI Portal Administrators. These users may change system preferences which affect the overall BI Portal system behavior.

It is important to understand that all BI Portal actions are made under the current logged-in user credentials. User credentials are passed over to the systems that the BI Portal connects to: OLAP servers or Microsoft SharePoint™ Portal servers.

Data Source Encapsulation

The BI Portal uses Data Source objects to encapsulate the technical properties of a data source type. This way, an administrator may define Data Source objects to be used later by non-technical users.

Each Data Source contains the server name, database name, and cube/table name. Provided that aliases and names given to the Data Sources are logical and self-explanatory, other non-technical users may use these Data Sources to connect and display data without having to understand the technical issues.

Publish to SharePoint Portal Server

A user can publish a requested View as a SharePoint Portal Server Workspace Quick Link. In addition, the user may also categorize a View under selected SharePoint Portal Server categories. Publishing a view to SharePoint Portal Server is done using WebDav queries for getting the categories of a SharePoint Portal Server Workspace, and also for updating new Quick-Links into the SharePoint Portal Server Workspace.

Create a SharePoint Portal Server Web Part

A user can create a SharePoint Portal Server Web Part, which will display a requested BI Portal View. The user can customize the exact layout of the BI Portal Web Part by choosing values for all layout parameters (such as the OWC to display, Full/Thin display, Width/Height and so forth), and finally create a .DWP file that can be imported into the SharePoint Portal Server's workspace.

Web Part Drag and Filter

This feature enables a user to drag some value for an OLAP cube dimension level member into the BI Portal web part. When this value is dropped the displayed PivotTable Report or Chart will re-query using the filter "[Dimension].[level].[member] = [dropped value]".

The dimension and level are defined when creating the BI Portal web part (using the Create web part command on the Tools menu).

For example, if you define dimension="Customers" and level="Country", then dropping the string "Mexico" will filter the displayed view according to the "Mexico" member value.

Import and Export

The BI portal provides both import and export capabilities from or to XML files. This provides the ability to receive Views produced on other BI Portal servers, and also transfer Views to other BI Portal servers.

The export to XML file capability enables a user to work in an offline mode and display Views that were saved in a local XML file.

Offline Mode

A user who wishes to travel and display offline OLAP Views may do so, by downloading requested private/public views to his computer. This operation simply saves the requested Views to a local XML file. The BI Portal Interface layer works seamlessly with these offline XML files, in the same way as it does online, due to the BI Portal architecture.

In Offline mode, the user may not only display all downloaded Views, but additionally can produce new Views (From offline cubes or online remote OLAP/Relational servers), and then upload them to the BI Portal server.

Thin Mode

In the regular way of work, OLAP data is displayed using PivotTable and Chart Office Web Components, and also Data Analyzer. This calls for an Office XP installation on the client PC. The BI Portal also supports Thin Mode, in which data is displayed using the Office Web Components server-side image rendering, and so the user receives on-line rendered images instead of OWC controls.

The Data Analyzer viewer is not supported in thin mode.

The steps for displaying views in thin mode:

1. A user clicks a requested View in the category tree.
2. A request is submitted to the application server for the view.
3. The application server gets the definitions of the requested view from the BI Portal database. These definitions include which viewers should be displayed (PivotTable report or Chart), data schema definition for each displayed viewer, and the data source that should be used.
4. The application server creates the PivotTable or Chart objects on the server, sets their connection and data definition properties (Connection string, which data should be retrieved for the display and how to display it).
   The connection string also defines the OWC to connect to the OLAP server under the current user windows credentials.
5. The application server calls for the PivotTable or Chart image rendering method ("ExportPicture()"), which creates an image with the viewer graphical display on the server.
6. The rendered images are then returned back to the client as binary response.

As described above, in thin mode, the data for the OWC is retrieved from the OLAP/Relational sources on the server side. It is possible to connect to the data source under one of two possible user credentials:

• Windows Authenticated User—Exactly the same way as happens in Normal mode, when the OWC connect to the OLAP/Relational data source from the client.
• Fixed User—In this case the request is redirected to a different IIS virtual root, which runs in Anonymous mode (under a fixed, configurable Windows user credentials). In this case the OWC connect to the OLAP/Relational data source under this fixed user credentials, no matter which current logged-on user has requested the view. In this mode PivotTable and Chart objects are cached, so that if a view that was already displayed will have a ready to render PivotTable/Chart objects on the server, so there will be no need for re-connecting to the OLAP server to get the data for the view.

Multi-Lingual Support

All BI Portal User Interface captions are taken from a database dictionary, which may easily be enhanced to support additional languages. The interface supports both LTR and RTL languages.

BI Portal Add-ins

The BI Portal enables a user to enhance client-side functionality by adding a customized add-in file. An add-in file is an HTML file which contains both interface (HTML) and logic (JavaScript), and thus provides the means for adding client-side functionality.

Each BIP add-in file must implement a pre-defined add-in interface, which means that a fixed set of functions should be implemented. These functions are actually event handlers for BIP client-side events.

BI Portal includes ready-to-use add-ins, which manipulate the PivotTable and Chart OWC using their object model. An example of such an add-in is the "Cell Coloring" add-in, which enables a user define thresholds for coloring PivotTable cells.

Integration with Microsoft Excel 2002

A user can use the Save as Excel command from the Tool menu to save the current displayed view to an Excel file.

Installing BI Portal

Pre-requisites

Database Server

• Windows 2000 Server and SP2.
• MS SQL Server 2000 and SP2.
• SQL Server Analysis Services and SP2.

Application Server

• Windows 2000 Server.
• MS SQL Server 2000 client (BI PDMO).
• SQLXML 3.0 SP1.
• Microsoft .NET Framework V1.0.3705 / V1.1.4322.
• Microsoft Office XP (For OWC Server rendering in Thin mode).

Note   If you have previous version of BI Portal, uninstall it.

Client PC

• Windows 2000 Professional/Server/Windows XP Professional.
• MS Internet Explorer 6 and SP1.
• Microsoft Office XP (needed in order to work with Office Web Components).

How to Install

1. Unzip the file and run the BIP_WEBSetup.msi file.

Note   To use the BI Portal Standalone version, after unzipping, click on bipdefault.htm. You can connect to local cube, OLAP, or SQL, save and create views. You cannot delete or create new categories.

1. In the Welcome to the BI Portal Setup Wizard, click Next.
2. In the BI Portal Information page, read the software requirements and then click Next.
3. During the installation you'll see a new window called BIP Setup.
4. Select SQL Server.
5. Insert the database name (default = "BIP").
6. Select the security mode, Windows authentication or SQL authentication.
7. If the SQL authentication mode was chosen, insert user name and password.
8. Click OK.
9. In the Installation Complete page, click Close.

Post Installation

1. On the application server, local Admin belongs to the BIP_ADMIN windows user group, and everyone belongs to the BIP_USERS windows user group.
2. On the database server, a local BIP_USERS is also created, and includes everyone (all domain users). This group is given an EXECUTE permissions on all stored procedures in the BIP database. This means that all domain users are permitted to execute the stored procedures, but have no additional rights on the database.
3. Browse URL to "http://[BI Portal Application Server name]/BIP”.
4. If the Office Web Components or Data Analyzer is not installed on the local PC, then a page with links to the installation files should be displayed.
5. Set permissions for the BI Portal users by connecting them to one of the BI Portal Application server's local windows user-groups (BIP_USERS, BIP_PW, BIP_ADMIN).

Using the BI Portal

This section reviews the BI Portal user interface, and describes the main user-scenarios.

A BI Portal is a .NET, Web-based application.

After installing the BI Portal on a server, you can load the portal from "http://[server name]/BIP URL". The user interface is shown in Figure 1.

Figure 1. BI Portal user interface

The BI Portal interface is divided into several rectangular screen areas, which resemble the SharePoint Web parts. These Web parts include:

• Menu bar—A Windows-like menu bar, which displays user-dependant menu items.
• Online/Offlineindicator.
• Toolbar—Windows XP-like toggles, which control the parts discussed below.
• Folder Part—This includes the Folder selection and category navigation tree. The category tree provides a right-click context menu, which displays the category/view related actions.
• OWC PivotTable—displays the OWC PivotTable control.
• OWC Chart—displays the OWC Chart control.
• Data Analyzer—displays the Data Analyzer control.
• Add-in Part—displays a selected add-in interface.

Each part is easily toggled using the toolbar toggle buttons, or by clicking the close (X) button in the caption bar of each part.

Menu Bar Description

This section reviews the BI Portal menu items.

File Menu

The file menu includes the following sub-menus:

Sub-menu name	Description
New	Create a new Folder/Category/View.
Save As	Save a view in a requested name, in a requested folder and category.
Save	Save the current selected view.
Publish to SharePoint	Save a Quick Link in a SharePoint Portal Server workspace, that links to the current selected view.
Import	Import views from an XML file.
Export	Export requested views to an XML file.
Print	Print the current selected view (Print the PivotTable or Chart OWC)
Send URL	Send the URL of the current view by email.
Properties	Edit the properties of the current selected view/category/folder.
Save As Excel	Save the current selected view to an Excel file.

Tools Menu

Includes the following sub-menus:

Sub-menu name	Description
Sync to PC	Downloads to the client PC the requested views and additional BI Portal files needed to display a presentation in offline mode.
Sync to Server	Uploads to the BI Portal server all local views that were created in offline mode.
Online	Go to Online mode (Work with the BI Portal server).
Offline	Go to Offline mode (Work disconnected from the BI Portal server).
Thin mode	In this mode the OWC PivotTable or Chart viewers are rendered to images on the server, so the client gets a thin HTML GUI, with no ActiveX controls.
Data Sources	Open a dialog which displays all data sources in a data grid, and enables editing/deleting and adding data sources.
Preferences	Display User Preferences dialog.
System Preferences	Display System Preferences dialog (Available only to BIP_ADMIN users).
Create web part	Create a SharePoint Portal Server Web part which displays a BI Portal view in a requested layout.

Addins Menu

Display all available BI Portal add-ins.

Help Menu

Display available help content.

User-Scenarios Description

The following sections describe the most frequent user-scenarios, while working with the BI Portal system.

Create a Data Source

This task is performed mostly by administrators and power users but may also be accomplished by members of the BIP_USERS group. A Data Source defines a connection to an OLAP/Relational source of data, which is used later to create Views.

In order to create a Data Source the following steps should be taken:

1. In the Tools menu, click Data Sources.
   A dialog with a data grid of all existing data sources will be displayed.
2. Click Add New.
   Fill-in the data source properties in the New Data Source dialog:
   • Data Source Name—Alias/Caption for the Data Source.
   • Type—OLAP (SQL Server analysis services), Offline Cube (.cub file), or Relational (SQL Server database).
   • Server—Server name (relevant to OLAP/Relational source types).
   • DB List—OLAP/Relational available databases.
   • Cube/Table—OLAP Cube/Relational DB table name to connect to.
   • Advanced…—Clicking this button enables creating a ready-to-use connection string using a data link wizard.
3. Click OK.

The new data source will be created and displayed in the data source grid.

Display available Views

Displaying available views is done by selecting a folder, and then navigating the category tree. Selecting a view in the category tree on the left pane will display the view on the right pane using the PivotTable/Chart/Data Analyzer controls.

Create a new View

In order to create a View the following steps should be taken:

1. In the File menu, point to New, and then click View or right-click a category on the category tree, and select the New View action on the context menu.
   The New View dialog allows the user to select a Data Source, on which the view is based or to create a new Data Source.
2. After the user selects a Data Source a new View is created and the existing displayed viewers (PivotTable/Chart) are reset. The user then uses the OLAP viewers (PivotTable/Chart/DA) functionality to display the requested data.
3. Save the new view by selecting File and then click Save As.

The user provides the following View properties in the Save View dialog:

• View Name—The new View name.
• Folder—The folder where the view should be saved.
• Category— The category to save the View in.
• Private—Checking this checkbox will make the view visible only to the owner user.
• Default—Checking this checkbox will make the View a default view for the folder. This means, that every time the user selects this folder for the first time, this view will be displayed at default.
• Read Only—For public View, denies all other users from changing the view.

Note   A newly created View doesn't have a name and doesn't exist in the category tree. The View receives an identification only after it is saved the first time.

Work in Offline mode

Working with the BI Portal in offline mode requires the following actions to be taken:

1. Save required Views data to the local PC disk as an XML file.
   This is accomplished by selecting Tools and then clicking Sync to PC. The user selects Folders/Views/Categories to be downloaded as XML data to the local PC in the Synchronize BI Portal dialog.
   The user can also see which offline cube files will be downloaded in order to support the requested Views.
   Clicking Synchronize starts the download process. In synchronization the interface files (html, javascript, images) are downloaded to the local machine, in addition to the Views data files (XML, .cub files).
   The application files (.htm, .js, images) are downloaded to the client PC only at the first download or when the BI Portal administrator notifies the server of a version change, which requires downloading these files to the client. Normally, clients will download only data files.
2. Operate the BI Portal interface in offline mode.
   Now that the local PC contains all files needed to operate in offline mode, the user can select Tools and then click Offline. The user's browser is redirected to a local html page. From this moment, the BI Portal interface is disconnected from the server.
3. In offline mode, the user can also create new Views based on the downloaded offline cube files, or based on remote OLAP/Relational servers. These views are saved locally in an XML file. After going back to Online mode, the user can upload the Views created in offline, by selecting Tools and clicking Sync to Server.

For a more detailed description of the synchronization mechanism, refer to the Offline mode and Synchronization mechanism section.

Manage user-preferences

A user preferences dialog as shown in Figure 2 is displayed by clicking Preferences in the Tools menu.

Figure 2. A user preference dialog box

The following table lists all available preferences:

Preference Name	Description
Synchronize Chart and Pivot Table controls.	PivotTable and Chart OWC are maintained synchronized to each other.
Show OWC Toolbar.	Display the PivotTable internal toolbar.
Show Chart Toolbar.	Display the Chart internal toolbar.
Show Data Analyzer Toolbar.	Display the Data Analyzer internal toolbar.
Use server formatting.	Indicate that OWC objects display server defined formatting.
Use server colors.	Indicate that OWC objects display server defined colors.
Alert on unsaved views.	Display an alert message to the user when the user tries to display a view without saving the current changed view.
Offline directory	Local path to a folder where all offline-mode data is saved by BI Portal system.
Language	User's GUI language.

Manage System Preferences

System preferences dialog is displayed when clicking System Preferences on the Tools menu. This sub menu is available only to a BIP_ADMIN user, and serves to manage the BI Portal system options.

Figure 3. A System preferences dialog box

The following table lists all available preferences:

Preference name	Description
System Default View	This view is displayed when the BI Portal is started by users who did not define default folder and view.
Default SharePoint name	SharePoint Portal Server name, which is displayed in the "Publish to SharePoint" dialog.
Default SharePoint Workspace name	SharePoint Portal Server workspace name, which is displayed in the "Publish to SharePoint" dialog.
CSS file name	The CSS file which is used by BI Portal system.
Manage cubes per user	Checking this option enables managing different offline cubes for each user. Using this option requires the BI Portal administrator to create a folder on the server for each user. The folder name should be "[username]" under the "cubes" folder.
Use system folder if user folder is unavailable	Checking this option defines BI Portal to get offline cubes for a user from the global "cubes" when the user's cubes folder was not found and the Manage cubes per user option was checked.
Use Data Analyzer object	Checking this option defines the BI Portal system to enable the use of the Data Analyzer object. When unchecked all Data Analyzer related commands are invisible.
Thin mode cache period for fixed mode authentication (min)	The number of minutes that a view will be cached in memory during Thin mode (Relevant to Fixed mode only).
Show all files during synchronize operation	When this option is checked, the Sync to PC dialog will display all downloaded files to the user, including BI Portal system file (.html, .js. image and so forth.).
Enable BIP_USER users to synchronize	Checking this option enables BIP_USER users to synchronize the BI Portal data.
Save database login password	In the case of relational database, this flag defines whether to save the database password in the data source record.

BI Portal Architecture

Application layers

The BI Portal is a .NET-based, 3-tier Web application. The application includes the following layers:

Presentation Layer

Includes the following building blocks:

• Viewers controls—Office Web Components (PivotTable/Chart) and the Data Analyzer, together providing a rich graphic display and manipulation ability of the OLAP data.
• Menu Bar—Implemented as iFrame menu bars, in order to display the menu items above the OWC ActiveX controls. This called for the use of window objects such as iFrame.
• Category Tree—HTC which pulls its data (Views and categories) using client XMLHTTP from the application XML data source. This XML data source may be a .NET ASPX page which queries the data from the database, or a local XML file containing downloaded categories and views in offline mode. No matter what is the data source, the category tree works in exactly the same way.
• Web Forms—All Folder/Category/View creation and manipulation are done through dialogs implemented as Web forms.

Business Object Layer

The .NET, C# ASPX pages and classes provide the presentation layer with the data necessary to:

• Display the category tree—The business object layer queries the BI Portal database for the categories to display depending on the current user, folder and views type (public/private), and returns the queried data in the form of XML stream.
• Gets requested view data—When a View is selected in the category tree, the system gets from the database the MDX query definition of each viewer control participating in the View layout.
• Add/Update/Delete a requested View/Category/Folder.
• The business logic layer connects to the database using SQLXML queries.

Data Layer

Consists of the following data types:

• BI Portal Database—SQL Server 2000, containing all data regarding users, folders, categories and views. Each view record contains both its data definitions (data source, MDX data fed into the viewers controls,), and the presentation definition of the view (which GUI parts are displayed, owner of the view, scope and so forth).
• OLAP/Relational sources—These are the data sources encapsulated by the data sources objects. When a view is selected by the user the displayed viewers are fed with the data source to connect to and the query to execute. Each viewer then connects directly to the server type pointed by the data source (OLAP server, offline cube file, relational database) and displays the received data.

Used Technologies

SQLXML

SQLXML is used by the application server to get XML tree data and a requested view data from the database.

Client XMLHTTP

The client category tree and the retrieval of a selected view data are done using XMLHTTP requests to the application server. This method enables working in exactly the same way with XML files in offline mode, that is, the client JavaScript code stays the same both in online and in offline mode. In both cases, the XMLHTTP object retrieves XML stream which is used to build the client display.

WEBDav Queries

The BI Portal enables saving views in a remote SharePoint Portal Server workspace, by using WebDav protocol to save a links to BI Portal views.

BI Portal connects to the SharePoint Portal Server in two cases:

• Displaying a requested Workspace's categories.
• Inserting a new Quick-Link which links to a BI Portal view and fill its profile in the SharePoint Portal Server workspace.

Application folder structure

Folder	Main Files	Description
Root
	BIPDefault.aspx	User interface page. The page includes menu, toolbar and all WebParts
	Books.aspx	Folder services (Create, update).
	Categories.aspx	Category service (Create, update).
	ChooseDB.aspx	Data source create page.
	DataSourceProperties.aspx	Display Data source properties.
	DataSources.aspx	Display a list of all data sources.
	EditItem.aspx	Page for edit selected item (Folder, Category, View)
	ExportData.aspx	Page for display tree of data and export to XML file.
	ExportWebPart.aspx	Page for create DWP file for SPS
	ImportData.aspx	Page for load tree of data from XML file and store to server.
	Preferences.aspx	Page for display and edit user preferences.
	SavePresentation.aspx	Page for save new view.
	SaveViewInSPS.aspx	Page for save quick link to selected view in SPS.
	SelectThinModeAuthentication.aspx	Page for select type of thin mode authentication: Fixed user or Windows Authentication
	Sync.aspx	Page for display tree of data and upload all needed files to client.
	SyncToServer.aspx	Page for load tree of data from XML file in local machine and store to server.
	SystemPreferences.aspx	Page for display and edit system preferences.
	ThinClient.aspx	User interface page. Page includes menu, toolbar and all Web Parts for Thin mode.
	trees.aspx	Returns XML string includes all tree data.
	trees_admin.aspx	Returns XML string containing public views.
	trees_offline.aspx	Returns XML string containing public and the current user’s private views.
	WebPartTest.aspx	Page for building and testing SPS WebParts
Add-ins		This folder contains all Add-in files.
	Actions.htm	Add and execute new action.
	CellColoring.htm	Filtering and Coloring total fields.
	DataAnalyzerSync.htm	Synchronize between Data Analyzer and PivotTable objects.
	Drillthrough.htm	PivotTable drill through displayed.
	Filter.htm	Filtering in OWC objects.
	MDX Filter.htm	Insert MDX or SQL query and execute it.
Add-ins/Actions_Files		Additional files for Action Add-in
Add-ins/ CellColoring_Files		Additional files for CellColoring Add-in
Add-ins/ DataAnalyzerSync_Files		Additional files for DataAnalyzerSync Add-in
Add-ins/ Drillthrough_Files		Additional files for Drillthrough Add-in
Add-ins/ Filter_Files		Additional files for Filter Add-in
Cubes		This folder contains all offline cube files.
ext		Folder includes additional files.(.js, .vbs, .xml…)
	AddInMenu_1.xml	XML file for language with ID = 1 (English) includes information about menu for SharePoint Portal Server Web Part with Add-in menu.
	AddInMenu_2.xml	XML file for language with ID = 2 (Hebrew) includes information about menu for Web Part with Add- in menu.
	Addins.js	Add-in engine implementation.
	BIP.js	Client action implementation.
	BIP.vbs	Implements events for OWC and Data Analyzer objects
	BIP_Logo.gif	BIP logo file.
	biportal.js	BIP Web Part manipulation.
	BIPStyleSheet.css	Style Sheet file.
	maximize.gif	Web Part maximize button.
	Menu_1.xml	XML file for language with ID = 1 (English) includes information about menu for BIP.
	Menu_1.xml	XML file for language with ID = 2 (Hebrew) includes information about menu for BIP.
	menus.js	Menu bar implementation.
	minimize.gif	Web Part minimize button.
	offline.gif	Image for offline mode.
	online.gif	Image for online mode
	ThinMenu_1.xml	XML file for language with ID = 1 (English) includes information about menu for BIP in thin mode.
	ThinMenu_2.xml	XML file for language with ID = 2 (Hebrew) includes information about menu for BIP in thin mode.
	UpdateDownload.htm	File indicates when one or more of files has changes for sync to client
BipThin		BIP application for thin mode.
	GetThinModeImages.aspx	The page retrieves binary data which includes image file for one of the OWC objects.
Help		Folder includes all files for Help
	BIP Administration.doc	This article describes Microsoft Business Intelligence Portal (BIP) general architecture and administration tasks.
	Howto write BIP add-in.doc	This article describes Microsoft Business Intelligence Portal (BIP) add-in API, and describes the steps required to implement a customized add-in.
	TemplateAddin.htm	This file contains a sample implementation of an Add-in for the BI Portal.
Sql/Template		Include XML file with SQL query for retrieve data from SQL server in XML format.
	DataSource_full.xml	Returns all information about Data sources.
	pres.xml	Returns XML data for selected view
	tree_full.xml	Returns full information about the category tree.
	trees_admin.xml	Returns XML string containing public views.
	trees_offline.xml	Returns XML string containing public and the current user’s private views.

Multi-Lingual Support

Multi-lingual is supported in the following manners:

• All strings/captions everywhere in the user interface are taken from a database repository (BIP_Language table). Each string is connected to some language. When the application starts, all captions are loaded to application-scope variables to be used in all the web forms.
• The menu bar is driven from an XML file, rendered using an XSL file. The system supports adding a menu bar xml file for every language. The supported languages appear in BIP_Languages database table, and for each supported language there should be a menu_[Language ID].xml file in the BI Portal server.

Adding a new language

In order for to BI Portal system to support a new language, the following steps should be taken by BI Portal administrator:

• Add a new language record to the "BIP_Languages" table.
• Add the relevant string records to the "BIP_Language" table. These strings are displayed in all the application's screens.
• Add "menu_[Language ID].xml", ThinMenu_[Language ID].xml" XML files for the menu bar definitions (Normal and thin mode).

BI Portal Database Schema Description

This section describes the BI Portal database structure: tables and relations. On top of the data model, there are views and stored procedures, which are not described in this article.

Database Tables

BIP_Tree

This table contains the existing folders in the system.

Column name	Key	Data type	Description
Tree_id	Primary	int	Folder identifier.
Tree_name		nvarchar(50)	Folder name.
Username		varchar(250)	Creator user domain name ([Domain]\[User])
Is_Private		bit	1=the folder is private to the user.
Is_ReadOnly		bit	1=the folder is read-only (for public folder).

BIP_Category

This table contains categories existing in all folders. Each category belongs to some folder.

Column name	Key	Data type	Description
Category_id	Primary	int	Category identifier.
Category_name		nvarchar(50)	Folder name.
Tree_Ref		int	Reference to a BIP_Tree row.
Username		varchar(250)	Creator user domain name ([Domain]\[User])
Is_Private		bit	1=the category is private to the user.
DenyGroup		bit	For future use.
GroupID		int	For future use.
Is_ReadOnly		bit	1=the category is read-only (for public category).
Is_Offline		bit	n/a

BIP_UserData

This table contains views. Each view is connected to a category and holds additional data regarding its data source, owner, scope and so forth.

Column name	Key	Data type	Description
PresentationCode	Primary	int	View identifier.
PresentationName		nvarchar(500)	Folder name.
Username		varchar(50)	Creator user domain name ([Domain]\[User])
CategoryRef		int	Reference to a category record.
IsPrivate		bit	1=the view is private to the user.
XMLDataOWC		ntext	XMLData from PivotTable object.
XMLDataDA		ntext	XMLData from Data Analyzer object.
XMLDataChart		ntext	XMLData from Chart object.
OWCMinimized		bit	Indicates that the top "web part" (PivotTable object) is minimized.
DAMinimized		bit	Indicates that the bottom "web part" (Chart or Data Analyzer object) is minimized.
TreeMinimized		bit	Indicates that the left "web part" (Tree) is minimized.
IsChart		bit	Indicates if Chart or Data Analyzer object is active (displayed in bottom web part).
IsVertical		bit	Indicates that the viewers "Web Parts" are in vertical layout.
DataSourceRef		bit	Reference to a DataSource record.
Is_ReadOnly		bit	Indicates that the view is read-only (relevant to public views only).
MDXQuery		ntext	MDX or SQL query string.
IsUseMDXByDefault		bit	Indicates that the MDX or SQL query will be executed immediately when the view is loaded.

BIP_UserPref

This table contains preferences of the BI portal users.

Column name	Key	Data type	Description
Username	Primary	varchar(50)	User domain name, in the format "[Domain]\[User]".
ChartSyncPivot		bit	Folder name.
OWC_panel_grow		bit
SHOW_OWC_TOOLBAR		bit	1=display Pivot OWC toolbar.
SHOW_CHART_TOOLBAR		bit	1=display Chart OWC toolbar.
SHOW_DA_TOOLBAR		bit	1=display Data Analyzer toolbar.
OfflineDir		nvarchar(250)	Local full path, where all offline files will be saved on the user's PC (for Offline mode).
[language]		int	User's BI Portal interface language id.
UseProviderFormatting		bit	Indicates that OWC objects display server defined formatting.
UseProviderColors		bit	Indicates that OWC objects display server defined colors.
AutoChangesCheck		bit	Indicates to display alert message on unsaved views.
DefaultBookID		int	Reference to a BIP_Tree row. Default displayed folder for the user (only if has a defined default view).
SCROLL_TREE		bit	n/a
nl		char(10)	n/a

BIP_Languages

This table is a dictionary of languages.

Column name	Key	Data type	Description
ID	Primary	int	Language identifier.
Language		nvarchar(50)	Language name.
RTL		bit	Language direction: 1=Right-to-left, o=left-to-right.

BIP_Language

This table is a repository of all the strings in all available languages, used in the BI Portal user interface.

Column name	Key	Data type	Description
FieldID	Primary(1)	int	String identifier.
LangCode	Primary(2)	nvarchar(50)	Language identifier.
LocalString		bit	String translation.

BIP_SystemPref

This table includes key-value pairs, used to hold the BI Portal system preferences.

Column name	Key	Data type	Description
Name	Primary	varchar(200)	Key (preference) name.
Value		varchar(1000)	Preference value.
Description		varchar(1000)	n/a

BIP_AccessLog

This table contains log records, of views being displayed by users.

Column name	Key	Data type	Description
ID	Primary	int	Record identifier.
PresentationIDRef		int	Reference to a view record.
UsernameRef		nvarchar(50)	Reference to a user.
AccessTime		datetime	Date and time log.

BI Portal Add-Ins

BI Portal add-in architecture description

The Microsoft BIP system displays Microsoft's OLAP components (OWC, Data Analyzer) in a single integrated user interface, which enables Graphic display and easy manipulation of OLAP and Relational data. Moreover, a user can enhance client-side functionality by adding a customized add-in file.

An add-in file is an HTML file which contains both interface (HTML) and logic (JavaScript), and thus provides the means for adding client-side functionality.

Using/Displaying an add-in is simply done by:

1. Writing an add-in file.
2. Saving the add-in file inside a pre-defined BIP server folder ("Add-ins" folder).
3. Selecting the add-in from the "add-in" BIP interface menu item.

An add-in can also use additional files (images, HTC files and so on) similar to such items produced when developing an ordinary HTML page. By convention, these add-in's additional files should be located in the [add-in name] sub-folder on the BIP server's "add-ins" folder.

Add-in Application Programming Interface (API)

Each BIP add-in file must implement a pre-defined add-in interface, which means that a fixed set of functions should be implemented. These functions are actually event handlers for the BIP client-side events.

The following table lists the BIP add-in related events, and the add-in event handlers that should be implemented to handle these events.

Event	Add-in event handler	Remarks
Page Load	RegisterAddin()	Registers the add-in to the add-in engine. The add-in engine then calls the add-in’' OnInitAddin() function.
Page Load	OnInitAddin()	This function is called when the BIP interface is loaded. The Addin author should use this hook to initialize any private variables of the add-in. It is called only once for each add-in during the page load.
Add-in sub-menu selected	ShowAddinUI()	This function is called just before the add-in's UI is displayed (after a user selects the add-in in the menu-bar).
Add-in is deselected in the add-in menu bar.	OnAddinUnload()	This function is called just before the add-in's UI is hidden.
User changes displayed view.	OnUIViewChange()	This API function is used to notify the active add-in that the view on the screen has changed. It is called first when the add-in is activated. Then it is called whenever the active View is changed. It is also called if the visible control in the top or bottom pane is changed. It is not called when a Web Part of the Top or Bottom pane is minimized.
Loading View process started.	OnStartLoadView()	This function is called just when the system starts to load a user-requested view.
Loading View process finished.	OnFinishLoadView()	This function is called when a view was successfully loaded.

An add-in file is loaded and displayed according to the following sequence:

1. During page load in the client, the RegisterAddin() function is called. This function creates a custom JavaScript object for the add-in and saves a reference to it. The function, also initializes the function pointers in the object to the add-in interface implementation functions.
2. RegisterAddin() calls back to the OnInitAddin() implementation. This allows the add-in to further expand the object model of the custom object. The add-in author should use this hook to put any private variables and methods in the custom object.
3. When an add-in is selected from the menu the following sequence occurs:
   • if an add-in is already showing, its OnAddinUnload() is called.
   • if an add-in is already showing, its containing SPAN is given the style.display="none".
   • The selected add-in's OnUIViewChange() is called. During this call the add-in's UI hasn't been presented yet.
   • The selected add-in's ShowAddinUI() is called.
   • The add-in's SPAN display="none" is removed to show the UI.
4. When an add-in is deselected from the menu the following sequence occurs:
   • The add-in's OnAddinUnload() is called.
   • The add-in's SPAN is given the style.display="none".

Add-in functions description

Function:	RegisterAddin()
Use:	Register the add-in to the BIP add-in engine.
Parameters: Name	Description
p_Caption	Unique add-in string identifier.
p_OnInitAddin	An add-in supplied function pointer. The function pointer is used by the system and is called on Page load.
p_OnUIViewChange	An add-in supplied function pointer to an event handler for the View change event. The function is called by the engine whenever the add-in needs to be notified of a change of screen content. This includes the following: Before the add-in is activated. This method is called before the ShowAddinUI method is called. Whenever the user changes the selected presentation. Whenever the visible controls on the page are changed. For example, changing from Chart to DA view.
p_ShowAddinUI	An add-in supplied function pointer. The function is called by the BIP add-in engine to notify the add-in that it is about to be shown.
p_OnAddinUnload	An add-in supplied function pointer. The function is called by the BIP add-in engine to notify the add-in that it is about to be hidden.
Returns:	None.

Function:	OnInitAddin()
Use:	This function is used by the add-in to initialize local variables and private function pointers. It is also a place where the add-in can change its interface to the Locale required. It is called only once for each add-in during the page load.
Parameters: Name	Description
p_LanguageCode	Integer code of the current system’s language. (0=English, 1=Hebrew)
Returns:	None

Function:	ShowAddinUI()
Use:	This function is called just before the add-in's UI is shown. This function, and its counterpart, OnAddinUnload(), are used to maintain the private visibility state of the add-in.
Parameters:	None.
Returns:	None.

Function:	OnUIViewChange()
Use:	This function is used to notify the active (The displayed) add-in of a change in the displayed view.
Parameters: Name	Description
p_TopPaneControl	The active control in the Top Pane of the BI Portal. The add-in should save a reference to this object for further manipulation.
pstr_TopControlType	A string specifying the type of the Top Pane control. This can be one of: "PivotTable", "Chart" or "DA".
p_BottomPaneControl	The active control in the Bottom Pane of the BI Portal. The add-in should save a reference to this object for further manipulation.
pstr_BottomControlType	A string specifying the type of the Top Pane control. This can be one of: "PivotTable", "Chart" or "DA".
pb_IsRelational	A Boolean value specifying whether the Connection is to a Relational Database or OLAP.
pb_IsOffline	A Boolean value specifying whether we are working in Offline or Online mode.
Returns:	None

Function:	OnAddinUnload()
Use:	This function is called just before the add-in's UI is hidden. This function, and its counterpart, ShowAddinUI(), are used to maintain the private visibility state of the add-in.
Parameters:	None.
Returns:	None.

Function:	OnStartLoadView()
Use:	This function is called just before the view loading starts.
Parameters:	None.
Returns:	None.

Function:	OnFinishLoadView()
Use:	This function is called right after the view loading is complete.
Parameters:	None.
Returns:	None.

Offline mode and Synchronization Mechanism

This section describes how the BI Portal synchronization and offline modes work.

As described in the introduction, the BI Portal enables a user to download requested views to the local PC (Sync to PC), and then work disconnected from BI Portal server. Moreover, a user can add new views in offline mode and then upload them back to the server (Sync to server).

Synchronization mechanism

When a user chooses to synchronize his PC, the system offers him to select the views he wants to download to the local PC. Each view is connected to some kind of data source (OLAP services, relational database or an offline cube), through the DataSource object.

The synchronization mechanism enables both downloading views and system files in order to enable activating the BI Portal interface on the local PC, and uploading new views created while in offline mode back to the BI Portal server.

Sync to PC

This option enables the downloading of data and system files to the local user's PC.

In order to enable a user to display views in offline, the following issues are handled:

1. Display user interface: In order to display a user interface in offline mode, the BI Portal generates HTML-based user interface. This generation process is done by producing a series of HTTP requests to server-side pages, which produce the interface file, menu XML string and also returns .js and image files. All these are saved by the client as files on the local PC's file system.
   The interface and menu bar XML file are produced according to the user's permissions and language definitions.
   The system interface files (.html, .js, images) are not downloaded on each "Sync to PC" operation. The client requests these files only if the file "UpdateDownload.htm" on the local PC is older then the file on the BI Portal server.
   When an administrator wants to impose the full download on the clients, this file should be changed on the server.
2. Download selected views: As mentioned before, the interface layer works in online and offline modes in exactly the same way; the category tree is built from an XMLDOM object and a user-selected view is displayed by feeding the XML data of each viewer that was brought using XMLHTTP request.
   In order for offline mode to work in the same way as in online mode, the sync process produces an xml file on the local PC, which includes the requested views data, so instead of performing a XMLHTTP request to the server, the request is performed to a local XML file, which returns data in exactly the same format.
3. Download cube files: Each view is connected to some data source through a DataSource object. In order to be able to work in offline, for each online source (OLAP online cube) a matching offline cube (.cub file) is downloaded instead. The generation of these cube files must be performed by an administrator BEFORE users attempt to download views. Additionally, the naming convention for these cube files is as follows: For an online OLAP cube named "XXX", the matching offline cube should be named "[OLAP server name]_[OLAP database name]_[OLAP cube name]". These cube files should be located under the "cubes" BI Portal folder.
   This automatic data source conversion and cube download is supported only for online cubes. Views that are based on offline cubes are written to the local xml file, but their cubes are not automatically downloaded, so the user has to save them in the needed cubes in the correct folder manually.
   The synchronization process does not support relational data sources.

Synching to the local PC is performed according to the following order:

1. The user selects the views he wishes to download to the local PC.
2. The user may see what cube files are needed to be downloaded for the selected views, and may also choose to download additional files (offline cube files, other files).
3. When the user starts the synchronization process, the client interface starts downloading the selected cube files.
4. The client checks if the system interface files should also be downloaded. If so, then these files are downloaded by requesting them one after another (using XMLHTTP object).

Sync to server

In offline mode, new created views are saved in "trees_offline.xml" local xml file. These views may be based on the downloaded cubes, on other offline cubes, or on online OLAP/relational servers (A user may work disconnected from the BI Portal server, but still create views based on online sources).

After a user returns to online mode, he may upload and save the views created in offline mode in BI Portal server.

Synching to the BI Portal server is performed according to the following order:

1. The user selects the views he wishes to save in the BI Portal server, from the views created locally while being in offline mode.
2. For each view, the client saves it to the BI Portal server by posting a XMLHTTP request to the server.
3. In case of naming collisions the system displays a dialog message to the user, offering him to change view name in order to resolve the collision.

BI Portal Web Part API

The BI Portal interface was designed to be controlled via query string parameters. These parameters can alter many aspects of the displayed GUI such as: Which viewer is displayed, arrangements of the interface parts, window dimensions and so forth.

In addition, the BI Portal enables a user to create a Web part (.dwp file), which includes a link with all the query string parameter values, according to a requested BIP interface layout.

Creating a Web part is accomplished through selecting Tools and then clicking Create web part. This menu opens a dialog with a rich set of parameters that control the exact created Web part layout and behavior. The dialog enables the user to create a URL with the parameter’s values, display how a Web part will look like, and create a Web part (.dwp file).

The following table lists all the BI Portal Web Part available parameters:

Parameter Name	Parameter Value	Description
Display OWC		Display OWC or Data Analyzer Objects
	Pivot Table	Display PivotTable object
	Chart	Display Chart object
	Data Analyzer	Display Data Analyzer object
Thin mode	Yes/No	Display thin mode (images instead of the OWC controls).
Thin User		User authentication mode
	Fixed	The fixed user mode
	Window	Windows authentication mode
Display toolbar		Display toolbar mode
	Full	Show entire BIP portal without top image, and without the Folder/View combo boxes.
	Medium	Show mini toolbar (Web parts toggles only, without the menu bar and Folder/View combo boxes.
	None	Don’t show toolbar at all.
Display Add-ins	Yes/No	Display Menu and button fro add-ins. Enable in Medium toolbar type only.
Display Web Parts border	Yes/No	Show web part caption and border or not.Show web part caption and border = Show web part caption and border= Show web part caption and border
Display OWC toolbar	Yes/No	Display OWC internal tool bar
Display Data Analyzer toolbar	Yes/No	Display Data Analyzer internal tool bar
Display OWC Drop area	Yes/No	Show Drop area for filtering OWC components.
WebPart size	Large, Medium, Small, Custom
WebPart Width	Width in pixels.	Relevant to “Custom” size.
WebPart Height	Height in pixels.	Relevant to “Custom” size.
WebPart Layout	Horizontal/Vertical
Show Folder & View combos	Yes/No	Show Folder and View combo boxes in the tool bar. (Instead of the tree web part).
Show Folder & View WebPart	Yes/No	Show the folder combo and category tree web part.
Show drop area	Yes/No	Display a Drop area for filtering OWC displayed data according to dropped dimension level member value.
Filter Dimension	String	Name of the filtered dimension.
Filter Level	String	Name of a dimension’s level.
Filter Member	String	Name of the filtered level’s member.