Pats 0 Systems Management Guide

6.2 WebIntelligence

6.2.1 PATS/WebLogic – WebIntelligence/Tomcat integration notes

PATS hosts Web Intelligence via an IFrame to two web pages deployed in InfoView, openQueryPanelWithToken.jsp (an unsupported file distributed by Business Objects consultants for creating a new ad hoc report) and openDocument.jsp (opens existing ad hoc reports.)
The pros and cons of this implementation are:
Pros:

• 
  Single sign on between two distributed web applications achieved
  
• 
  Seamless integration fulfills the common look and feel that PATS and WebIntelligence (WEBI) look similar
  

Cons:

• 
  Two stateful web applications must be managed with distinct objects. PATS holds a Business Objects IEnterpriseSession and so does InfoView (which runs WEBI) in Tomcat. Additionally each application has an HttpSession. These two sessions are not synchronized. Therefore, the PATS application can time out while a user is working in WEBI. To accommodate this, the timeout in PATS web pages containing WebIntelligence links is set to one hour while the timeout on the rest of the PATS web pages is set to 20 minutes. While a user is working in WEBI, the timeout on the PATS application running in the background is one hour, so PATS is less likely to time out.
  
• 
  Additionally, the multitude of session instances (HTTP and Business Objects) can introduce scalability issues.
  

6.2.2 Universe Connection

The PATS Oracle 10g universe connection object is used to make the database connection between the PATS universe and the PATS tables that support ad hoc reporting. The BOEXI Designer application was used to create both the PATS Universe and the PATS Oracle 10g connection.
To access the Designer application:

1. 
   Select Start>All Programs>BusinessObjects 11>BusinessObjects Enterprise>Designer.
   
2. 
   Select Tools>connections.
   

When the PATS Oracle 10g database was originally designed, the following information was entered in the wizard prompts.

Wizard Prompt	Setting
Database Middleware	Navigate to Oracle>Oracle 10>Oracle Client
Type	Secured

Wizard Prompt	Setting
User name	PATSRPTS
Password	Password established for the PATSRPTS Oracle user
Keep the connection active during the whole session (local mode only)	Select this option
Array fetch size	100
Array bind size	50
Login timeout	600
Binary Slice size	32000
Hint value	null

To edit the PATS Oracle 10g Universe Connection (for example, to connect to a different database or to change the database password):

1. 
   Select PATS Oracle 10g from the list.
   
2. 
   Click Edit.
   

Result: A wizard is started that allows you to edit the parameters.

6.2.3 Universe

The universe used for ad hoc reporting, PATS Universe, was created by the PATS developer using the BOEXI Designer application. The Import installs this universe in the BOEXI repository

• 
  The table ROC_COMBO_DATA_VIEW is a single table that combines data from joins of the Report_of_contact table with all of the tables that it references, and all of the tables that reference it. All of the columns that the user sees come from this table. This table is refreshed from the PATS application table data nightly.
  
• 
  The table USER_STATION_LIST_FOR_AD_HOC contains a list of User/Station pairs, and is used to filter the data as described in the Security section below.
  
• 
  There is a many-to-many join between the two tables on the STATION_NUMBER columns.
  
• 
  All columns were transferred from the underlying ROC_COMBO_DATA_VIEW table to the universe.
  
• 
  Several new dimensions were created from underlying columns to assist the user in designing reports. For example, the issue code and the issue code name have been concatenated into a new dimension since report designers often want to show the two fields together.
  

6.2.4 Universe Data Filtering

Data is filtered so that when a user does ad hoc reporting from the PATS Universe, they are allowed to see only data for stations to which they have access. This section describes the steps taken by the PATS developer to implement the data filtering.
When a user logs on to the PATS application, the KAAJEE log on service returns a list of accessible station numbers for that user. The PATS application uses that list to build entries consisting of KAAJEE User Identifier/Station Number pairs in the USER_STATION_LIST_FOR_AD_HOC table in the database.
Adding the User/Station table to the Universe: In the BOEXI Designer application, the USER_STATION_LIST_FOR_AD_HOC table was added as a table to the PATS Universe, with a many-to-many relationship to the ROC_COMBO_DATA_TABLE. The join is between the STATION_NUMBER fields on each table. The USER_STATION_LIST_FOR_AD_HOC table was then made into a class in the PATS Universe. This class is hidden from the end users.
Forcing connection between a ROC and users(s) who have access to the station associated with the ROC: An enforced join was established between every field in the main data class, and the User Station List For Ad Hoc class. Because of this, whenever a Web Intelligence query is made involving any field on the main data table ROC_COMBO_DATA_TABLE, a join on station number will automatically be made to the USER_STATION_LIST_FOR_AD_HOC table as well. This makes the connection between every ROC, and every user who has access to the station number to which that ROC belongs.

The enforced join was made as follows:

1. 
   On the Properties for each field, click on the Tables button.
   
2. 
   Highlight both the ROC_COMBO_DATA_VIEW table and the USER_STATION_LIST_FOR_AD_HOC table.
   

Security Restriction linking the current user to their accessible divisions list: The next step in creating the filter was to set up a Security Restriction based on the current user logged on to Web Intelligence. This security restriction will create an addition to the WHERE clause for any query against the USER_STATION_LIST_FOR_AD_HOC_USER table, that restricts the selection to rows where the USER_IDENTIFIER matches the BOUSER variable. BOUSER is a system variable set by WEBI to the user identifier of the user currently logged on to WEBI. Our security restriction was set up as follows:

1. 
   Select Tools>Edit Security Restrictions>New.
   
2. 
   Select the ROWS tab.
   
3. 
   Enter the name of the security restriction RestrictUsersByStation.
   
4. 
   Under Restricted Tables, press Add.
   
5. 
   In the Table box, press >>.
   
6. 
   Select the USER_STATION_LIST_FOR_AD_HOC.
   
7. 
   In the Where clause box, press >>.
   
8. 
   Create a Where clause that says: USER_STATION_LIST_FOR_AD_HOC.USER_IDENTIFIER = @Variable(‘BOUSER’)
   

Note: The BOEXI developers warned that in future versions of the Business Objects Web Intelligence application, the maintenance developers for the PATS application may need to change this code to use a new variable DBUSER instead of BOUSER.
Apply the security restriction to all PATS Users: The final step to create the data filter by station was to apply the new Security Restriction to the PATS_REPORTS_GROUP. This was done as follows:

1. 
   Select Tools>Apply Security Restrictions.
   
2. 
   From the Manage Access Restrictions dialogue box click the Users or Groups icon.
   
3. 
   From the Select Users and Groups dialogue box highlight the group PATS_REPORTS_GROUP, press Select then close the box.
   
4. 
   The user group appears in the Manage Access Restrictions user list. Highlight the user group and select the restriction set RestrictUsersByStation from the Restriction Set drop-down list.
   
5. 
   Click Apply.
   
6. 
   Click OK.