Business Objects FRS Pruning

Hello Techies,

This is the continuation of Business Objects File Repository Servers Blog and we are going to see how to optimise the File Repository Servers by FRS pruning.

Have you ever got the chance to see how a Crystal or WebI document or Instance stored internally in the File System? Here it is.

The document will be saved internally in the file system with in one or more folders named based on random name generation.
What will happen if the report or the Instance deleted?

The report or the Instance alone will be deleted and leaving the temporary folders as it is. As a result of this over the period of time, there will be thousands of folders in the FRS and will be a intricacy for the Administrator when he goes for the FRS Backup. The Backup process will be very time consuming as well as occupy more space and finally the FRS will be inefficient.

How to get rid of this?

The “-Prune” command, added at the end of the command line of the File repository servers will be handy at this moment.

Working with FRS Pruning and Tracing

-Prune command, added at the end of the command line of servers triggers the server to go through the ‘Input’ or ‘Output’ folders in the internal ‘Filestore’ folder of Business Objects Enterprise to clean up all the empty directories.

-Trace command, added at the end of the command lines of the servers logs the activity of that specific server in the ‘Logging’ folder of the BOE installation directory.

We need to periodically delete the empty FRS directories to cleanup the disk but not to be manually. Instead the FRS server should be started with the -Prune command line switch. When this switch is used, the FRS servers’ status will remain ‘Starting’ till the deletion is done. Once deletion is done, the servers will stop. The -Prune switch will have to be removed manually to allow the servers to start normally.

Working with FRS Pruning and Tracing

Add -Trace and -Prune

1. Stop File Servers (both IFRS and OFRS) in CCM (XIR2) or in CMC (XI 3.x).

2. Add -Prune command at the end of the line to FRS (Input and Output) and also –Trace Command at the end of the line to check it is cleaning up files and folders that are empty.

3. Start the servers and Monitor the pruning process, you should gain more hard disk space.

Remove -Trace and -Prune

1. Stop the Servers and remove -trace and -prune commands from the command line Parameters of FRS.

2. Start the Servers again normally.

Viewing Log files

You can find the log files in the below location (for XI 3.x)

C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\Logging

Points to remember

• After the Pruning process if any empty folders still exists, they may be used by BO to keep for its housekeeping process.

• Don’t leave the prune option enabled even after the prune completed. Once pruning completed successfully the FRS will be stopped. We have to re-modify the command line by removing –Prune and -Trace and Server has to be started manually.

• Pruning process does not clean-up any CMS object that lost the FRS files that they need to point to.

Please Note -Prune is an undocumented feature in Business Objects.

We will see more about the Business Objects Servers Tuning in the upcoming blogs.

Happy Blogging!!  Keep reading!!

Business Objects File Repository Servers

Functions of File Repository server (FRS)

The BusinessObjects File Repository Servers(FRS) are responsible for listing files on the server, adding files to the repository, and removing files from the repository. FRS also responsible for the creation of file system objects such as exported reports, and imported files in non-native formats.

Default FRS location in BOE Installation?

FRS can be found on your disk at \Program Files\Business Objects\BusinessObjects Enterprise 12\FileStore (for XI 3.x) in default installation. The two main directories under this location are Input and Output. The Input directory stores the report templates and thumbnail images, while the Output directory stores the results from running those templates. Thus, the Output directory is normally many times larger than the Input directory. Each of these directories is managed by its own BO XI File Repository server.

In every BusinessObjects Enterprise implementation there is an Input and an Output File Repository Server. Both manage their respective directories and handle all aspects of file management.

Input FRS

The Input File Repository Server manages all of the report objects and program objects that have been published to the repository. It can store .RPT, .CAR, .EXE, .BAT, .JS, .XLS, .DOC, .PPT, .RTF, .TXT, .PDF, .WID files. In the case of .RPT files, they are stored as report definition files only which do not contain any data.

The Report Properties page of the CMC shows you the location of the Input report files. The RPT report template can be found at frs://Input/a_084/004/000/1108/ca067d4f1710cbc.rpt

Exploring to this location shows two files: the RPT file with the name indicated on the report properties page of the CMC and a JPEG file, which serves as the thumbnail image.

Output FRS

The Output File Repository Server manages all of the report instances (saved data copy of the report) generated by the Report Job Server or the Web Intelligence Report Server, and the program instances generated by the Program Job Server. It also manages instances generated by the Web Intelligence Report Server and the LOV Job Server. It can store the following files: .RPT, .CSV, .XLS, .DOC, .RTF, .TXT, .PDF, .WID. For .RPT and .WID files are stored as reports/documents with saved data.

Since Output FRS stores the report instances, deleting instances would remove instances not the actual reports. However the report structure will be stored in the Input FRS.

Using Query Builder we can find the location of the Output File repository files. The following query may be handy if you already know the report name.

SELECT SI_NAME, SI_KIND, SI_FILES, SI_INSTANCE from CI_INFOOBJECTS
Where SI_NAME =’xxxx’

If the SI_INSTANCE value is false then the InfoObject is the actual report and SI_PATH will be in Input FRS. If SI_INSTANCE is true then the InfoObject would be an Instance and the SI_PATH will be in Output FRS.

Exploring to this location shows the actual instance file pertaining report data with the appropriate export format based on the scheduling parameters.

Diagnosing File Repository Servers

Repository Diagnostic Tool (RDT) is a command-line tool that scans, diagnoses, and repairs inconsistencies between your Central Management Server (CMS) system database and the File Repository Servers (FRS) filestore, or inconsistencies that can occur in the metadata of InfoObjects stored in the CMS database. This inconsistencies may occur during unexpected events such as disaster recovery, back-up restoration, or network outages. During these events, the CMS system database may be interrupted while performing a task. This can cause inconsistencies with objects in the CMS system database.

List of inconsistencies that could potentially occur in repository which RDT can identify are

Inconsistency	Description	Repair Actions
InfoObject exists, but no file	It is possible that an InfoObject exists in the CMS, but there is no file FRS	Delete the InfoObject, unless otherwise told
File exists but no InfoObject	It is possible the file exists but there is no corresponding InfoObject	User is notified to republish the object
Invalid Parent ID	An InfoObject can potentially have an invalid parent reference	The object and its children will be moved Into a folder call ‘Repair’.
Last Successful Instance	The reference to the last successful scheduled instance could be invalid	Remove the ID and let the CMS automatically recalculate it
Invalid Target ID	A shortcut could be pointing to an invalid object	The shortcut object will be deleted, unless told otherwise.
File size is wrong	There can be information discrepancies between the InfoObject and actual file	Update the InfoObject
Empty folders in the system	There may be empty folders due to old objects	Remove the empty directories, unless otherwise told.

Limitations for File Repository Servers

• The Input and Output File Repository Servers cannot share the same directories. This is because one of the File Repository Servers could then delete files and directories belonging to the other.
• In larger deployments, there may be multiple Input and Output File Repository Servers, for redundancy. In this case, all Input File Repository Servers must share the same directory. Likewise, all Output File Repository Servers must share a directory.          

Business Objects Query Builder – Part II

OM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ AND SI_NAME LIKE ‘Annual%’ AND SI_RUNNABLE_OBJECT=1

3. To extract  list of Web Intelligence documents that are scheduled in a specified period of time

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’  and SI_RUNNABLE_OBJECT=1 and

SI_NEXTRUNTIME between ‘2010.07.08.09′ and ‘2010.07.08.11′

4. To return all report folders containing a string

SELECT * FROM CI_INFOOBJECTS WHERE SI_NAME LIKE ‘%Service%’ AND SI_KIND=’Folder’

5. To returns all Universe folders containing a string

SELECT * FROM CI_APPOBJECTS WHERE SI_NAME LIKE ‘%Sales%’ AND SI_KIND=’Folder’

6. To see what type of rights you have for your BO software

SELECT SI_NAME from CI_SYSTEMOBJECTS where SI_NAMEDUSER=0 AND SI_KIND=’User’

7. To find all crystal and webi reports – not instances

Select si_id, si_name from ci_infoobjects where (si_kind = ‘CrystalReport’ or si_kind = ‘Webi’) and si_instance = 0 and si_children = 0

• To find all crystal reports – not instances or shortcuts

select si_id, SI_NAME,   si_owner,  SI_PARENT_FOLDER,  si_children, SI_PROCESSINFO.SI_FILES,  SI_PROCESSINFO.SI_LOGON_INFO, SI_PROCESSINFO.SI_RECORD_FORMULA from CI_INFOOBJECTS where (si_kind = ‘CrystalReport’) and si_instance = 0 and not si_name like ‘Shortcut to%’

• To find all the failed instances

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME>=2 and SI_SCHEDULEINFO.SI_STARTTIME>=’2011.01.01′

• To find successful instances

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO, SI_SCHEDULEINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME<2 and SI_SCHEDULEINFO.SI_STARTTIME>=’2011.01.01′

• To find successful instances of a particular report after a specific date

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO, SI_SCHEDULEINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME<2 and SI_SCHEDULEINFO.SI_STARTTIME>=’2011.01.01′ and SI_NAME = ‘Test.rpt’

• To find scheduled instances for a specific time range

select SI_NAME, SI_SCHEDULEINFO.SI_submitter, SI_SCHEDULEINFO.SI_STARTTIME from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_STARTTIME>=’2011.01.01.16.00.00′ and SI_SCHEDULEINFO.SI_STARTTIME<’2011.01.02.13.00.00′ order by SI_SCHEDULEINFO.SI_STARTTIME

• To find successfully scheduled reports (not instances) scheduled after a certain date

select si_id, SI_NAME, si_owner, SI_PARENT_FOLDER, si_children, SI_PROCESSINFO.SI_FILES, SI_PROCESSINFO.SI_LOGON_INFO, SI_PROCESSINFO.SI_RECORD_FORMULA,  SI_SCHEDULEINFO.SI_STARTTIME, SI_SCHEDULEINFO.SI_SUBMITTER,  SI_SCHEDULEINFO.SI_DESTINATION, SI_SCHEDULEINFO.SI_UISTATUS from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME = 1 and si_instance = 0 and SI_SCHEDULEINFO.SI_STARTTIME>=’2008.11.01′

• To find recurring instances

select si_id, SI_NAME, si_owner, SI_PARENT_FOLDER, si_children, si_recurring, SI_PROCESSINFO.SI_FILES, SI_PROCESSINFO.SI_LOGON_INFO, SI_PROCESSINFO.SI_RECORD_FORMULA, SI_SCHEDULEINFO.SI_STARTTIME, SI_SCHEDULEINFO.SI_SUBMITTER, SI_SCHEDULEINFO.SI_DESTINATION, SI_SCHEDULEINFO.SI_UISTATUS from CI_INFOOBJECTS where not si_name like ‘Shortcut to%’ and si_recurring=1 and SI_SCHEDULEINFO.SI_STARTTIME>=’2008.11.01′

• To find users who have logged in since a specified date or whose userid was created after a specified date, but may not have logged in

select si_name, SI_CREATION_TIME, si_lastlogontime from ci_systemobjects where si_kind = ‘user’ and (si_lastlogontime > ’2008.11.01.04.59.59′ or SI_CREATION_TIME > ’2009.04.01.04.59.59′ )

• To find reports that have not been scheduled

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER from CI_INFOOBJECTS where (si_kind = ‘CrystalReport’ or si_kind = ‘Webi’) and si_instance = 0 and si_children = 0 and SI_SCHEDULEINFO.SI_SCHED_NOW = 0

• To find users are all logged in to Business Objects at a given Point of time

SELECT TOP 1000 * FROM CI_SystemObjects WHERE si_kind = 'Connection' AND si_parent_folder = 41 AND si_authen_method != 'server-token' ORDER BY si_name

• To get list of Crystal reports by data connection from BO Enterprise

SELECT SI_NAME FROM CI_APPOBJECTS WHERE SI_KIND=’MetaData.DataConnection’

• To find universe used by the report

SELECT SI_ID, SI_NAME, SI_WEBI , SI_OWNER
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
Where PARENTS(“SI_NAME=’Webi-Universe’”,”SI_NAME =’Your Universe Name’”)

• To get all recurring reports from Specific folder

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ’3711′ and

SI_recurring = 1

• To get all recurring reports from Specific folder NOT Paused

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ’3711′ and

SI_recurring = 1 and SI_SCHEDULEINFO.SI_SCHEDULE_FLAGS = ’0′

• To get all recurring reports from Specific Folder, All Recurring PAUSED:

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ’3711′ and

SI_recurring = 1 and SI_SCHEDULEINFO.SI_SCHEDULE_FLAGS = ’1′

• To get list of users who is logged in to your Business Objects XI at a given Point of time

SELECT TOP 3000 * FROM CI_SystemObjects WHERE si_kind = 'Connection' AND si_parent_folder = 41 AND si_authen_method != 'server-token'

ORDER BY si_name

• To get Get All Webi reports from the repository

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Webi’ And SI_INSTANCE=0

• To get Full Client Reports from the repository

SELECT SI_ID, SI_NAME,SI_FILES FROM CI_INFOOBJECTS WHERE SI_KIND in( ‘webi’ ,’FullClient’)

1. To get all reports from the repository
2. 
   Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Report’ And SI_INSTANCE=0
3. 
   
4. To get all universes from the repository
5. 
   Select SI_ID, SI_NAME, SI_WEBI, SI_KIND From CI_APPOBJECTS where SI_KIND =’Universe’
6. 
   
7. To get all Users from the repository
8. 
   SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID=’CrystalEnterprise.USER’
9. 
   
10. To get all groups from the repository
11. 
    Select * from CI_SYSTEMOBJECTS Where SI_KIND=’UserGroup’
12. 
    
13. To get all folders from the repository
14. 
    Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Folder’
15. 
    
16. To get all categories from the repository
17. 
    SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND=’Category’
18. 
    
19. To get all personal categories from the repository
20. 
    Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND=’PersonalCategory’

Hope all these could be useful to you when it comes in to handy. In the forthcoming post, I will discuss on Business Objects file repository servers in detail.

You can Read it more Business Objects Query Builder

Xcelsius Dashboards – Integration with SQL Server Reporting Services

Pre – requisite

• Xcelsius Reporting Services (XRS) gateway needs to be installed on a web server where IIS, .NET framework and SQL Server are installed and configured.

• SSRS (SQL Server Reporting Services) reports need to be deployed in the SQL server, so that it can be accessible in the dashboard.

Xcelsius Connector to be used

Reporting Services Button can be used to get data from the SQL Server report which is deployed in the SQL server.   The deployed report can be accessed through the below URL

“http://servername/xrs/xrs.asmx/GetReports”

Here the servername is the SQL Server Name.

Building the Dashboard

• Create a report using the SQL Server reporting services and deploy it to the server.
• While creating the dashboard, use the Reporting Services Button to connect to the SSRS data source as below:
• In the URL box, enter the path where the reports are deployed.   On clicking the submit button, the reports in the server are listed.

• Select the target report and do the necessary data mapping in the underlying excel.
• If the report contains prompts, they would be listed in the report parameters that can be used by users at run time to pass values.
• Load the data into the dashboard based on the any one of the options available given below as per the requirement:
• Refresh on Load: Loads the data to the dashboard as soon as it is opened.
• Refresh on Interval: Loads the dashboard in periodic intervals.
• Trigger Behavior: Loads the dashboard based on an action in the dashboard.

• Generate the flash file (.swf) from the dashboard and deploy it portal for users to view the dashboard.

Hope you will be able to leverage your SQL Server environment effectively for integrating with Xcelsius dashboards.  Please get back to me for any queries.  Have an enjoyable 2011!  Happy year ahead friends!

The Central Management Server (CMS) Repository

The content of the Business Objects Enterprise (BOE) system consists of the physical files and the metadata information about the physical files.
For a Crystal Report, the physical file as well as the metadata about the file should exist in the BOE system. The Crystal report is stored as a file on File Repository Server (FRS) with an extension of .rpt . The Metadata information such as report name, type, report ID, path, etc is stored as an InfoObject in the CMS Repository.
I will discuss about the CMS repository in this article.
The CMS Repository Database Tables
The CMS metadata is physically stored on a database as InfoObjects.   There are six tables, the purpose of which is given below.

Sno	Physical Table Name	Purpose
1	CMS_VersionInfo	Contains the current version of BOE.
2	CMS_InfoObjects6	Each row in this table stores a single InfoObject.  This is the main table in the repository.
3	CMS_Aliases6	Maps the user alias(es) to the corresponding user ID. For example, a user may have both a Win NT alias and an LDAP alias. Regardless of the number of aliases a user may have, in the Business Intellengence .Platform each user has only one user ID. The map is stored in a separate table to enable fast logins.
4	CMS_IdNumbers6	The CMS uses this table to generate unique Object IDs and Type IDs. It has only two rows: an Object ID row and a Type ID row. The CMSs in a cluster use this table when generating unique ID numbers.
5	CMS_Relationships6	Relationship tables are used to store the relations between InfoObjects. Each row in the table stores one edge in the relation. For example, the relation between a Web Intelligence document and a Universe would be stored in a row in the WebI – Universe Relation table. Each relationship table has these columns: Parent Object ID, Child Object ID, Relationship InfoObject ID, member, version, ordinal, data.
6	CMS_LOCKS6	This is an auxiliary table of CMS_RELATIONS6.

The Central Management Server(CMS) repository tables cannot be queried directly. Query builder is the tool to be used to retrieve Business Objects metadata information using the virtual tables such as ci_systemobjects, ci_infoobjects and ci_appobjects.
In the forthcoming posts, I will discuss on query builder and file repository server.