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!

Business Objects Query Builder

Hi all,

Hope you had some time to read on my previous blog on CMS Repository tables.  As discussed, the CMS repository is a database that contains all the information about the reports, universes and security that make up your deployment.  As the data contained in this repository is stored in a binary format, you cannot query it with conventional SQL tools.  That’s where Business Objects Query Builder comes in.  Using queries that are very similar to SQL, you can tap the information hidden away there.  Since the repository is what drives the entire BusinessObjects system, there is a lot to explore.  Query Builder requires no SDK development but can return pretty much anything an SDK query might return.

Accessing Query Builder

To access the Query Builder, point your web browser to your BusinessObjects server.  Query Builder can be found at the following URL:  http://[server]:[port]/AdminTools/.

Log on as an Administrator to get full access to all the repository objects.  From here you can start writing your query.  There are three Info objects tables that you can query:

• CI_INFOOBJECTS
  Contains objects that are often used to build the user desktop, such as favorites folders and reports.
• CI_SYSTEMOBJECTS
  Contains objects that are often used to build the admin desktop and internal system objects, such as servers, connections, users, and user groups.
• CI_APPOBJECTS
  Contains objects that represent BusinessObjects Enterprise Solutions. For example, the InfoView and Desktop Intelligence objects are stored in this table.

Following columns are the frequently used from the above repository tables

Column	Description
SI_ID	Identifies each InfoObject instance uniquely in the database. But, this is not a primary key. If the instance is deleted, the value may later be reassigned to a new instance.
SI_NAME	Name of the InfoObject instance.
SI_KIND	Identifies each row by a particular InfoObject extended class type. SI_KIND for CI_INFOOBJECTS includes Webi, Pdf, Excel, Folder, FullClient, FavoritesFolder, Inbox, PersonalCategory, Shortcut, MyInfoView SI_KIND for CI_APPOBJECTS includes Universe, Universe Folder, MetaData.DataConnection,ReportConvTool, WebIntelligence, Discussions, InfoView, CMC, busobjReporter, Designer, AdHoc SI_KIND for CI_SYSTEMOBJECTS includes User, UserGroup,Connection,secWinAD, secLDAP, secWindowsNT
SI_OWNERID	User ID of the owner
SI_OWNER	User name of the owner
SI_CHILDREN	Number of children for the Infoobject
SI_CUID	CUIDs are Cluster Unique Identifiers that uniquely identify an InfoObject, within a given cluster and also identify replicas or copies of an object across multiple CMS clusters. Because CUIDs are moderately lengthy strings they are less efficient to use and slower to query for.
SI_UNIVERSE	Universes used by the document, there might be multiple universes used in one document; you may see a list of universes’ SI_ID attached to the property.
SI_PARENTID	Identifies the InfoObject instance that operates in a parent relationship to the current InfoObject. Typically, a report that is configured to be scheduled is a parent, and each report that is copied and stored when scheduled will view the source report as its parent.
SI_INSTANCE	Identifies whether the item that is stored in the database row is an InfoObject that was created through scheduling (such as a nightly report) and is therefore an ‘instance‘.

Relationship between InfoObjects

CMS InfoObjects are organized into hierarchies based on the relationship between them. The hierarchy could be based on folder based or user group.

From above diagram, the InfoObjects relate to each other not only by folder hierarchy, they may have other relationships. For example, the SI_OWNERID is the property to identify the ownership from the user to the document.

Sample Queries

SELECT * FROM CI_INFOOBJECTS

Returns the details for all the ‘InfoObjects’ (documents, folders, and other content) in your repository; you can filter this list using a WHERE clause.

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’CrystalReport’
Returns all ‘Crystal Reports’.

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’
Returns all ‘Web Intelligence documents’.

SELECT * FROM CI_APPOBJECTS WHERE SI_KIND=’Universe’

Returns all ‘Universes’ in the BOE Repository.

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’User’
Returns all Users in the BOE Repository.

Improving Query Performance in Query Builder

1. For improved performance use the below Indexed properties in query’s WHERE clause wherever required.

SI_CUID
SI_GUID
SI_HIDDEN_OBJECT
SI_ID
SI_INSTANCE_OBJECT
SI_KIND
SI_NAME
SI_NAMEDUSER
SI_NEXTRUNTIME
SI_OWNERID
SI_PARENTID
SI_PLUGIN_OBJECT
SI_RECURRING
SI_RUID
SI_RUNNABLE_OBJECT
SI_SCHEDULE_STATUS
SI_UPDATE_TS
SI_INSTANCE







2. Order of the above properties in WHERE clause also improves the Query performance as the Query Builder processes queries from top to bottom and left to right. So the selection criteria should be ordered from the most restrictive to the least restrictive.

For example, SI_NAME = ‘Test Report’ should be placed before SI_KIND = ‘WebI’ in the query.

I will discuss on few more queries in the next blog that will be followed by the File Repository Server details.

Happy blogging!  Have a good year ahead!

Let’s talk EPM – Part 1

Welcome to the world of Enterprise Performance Management (EPM), considered the Holy Grail of Business Intelligence. EPM and its various manifestations creatively named as Business Performance Management (BPM), Corporate Performance Management (CPM) etc. is a set of processes that help organizations optimize their business performance.

Does it sound good? – Ofcourse, Yes! Show me an organization that does not want to optimize!!
Does it sound practical? – Not really! Don’t know where to start!!

EPM means many things to many people – Optimization of business performance can mean optimization at the business processes level (local optima), can also mean optimization at the organizational level (global optima) and can also have many flavors in between.

With many BI vendors jumping into the EPM bandwagon, the problem is that EPM is immediately equated to the solutions provided by tools like Business Objects, Cognos, Hyperion etc. That view, in my opinion, is far removed from the truth.

In this series of posts, I would like to share some thoughts on making EPM a practical reality in organizations. To start with, let me enumerate the components of an EPM strategy:

1. Business Process Maps – Understand the business process
2. Metrics Identification – Get hold of the metrics
3. Metrics Profiling – Understand the metrics in depth
4. Metrics Maps – Understand the cause and effect relationships between metrics
5. Metrics Visualization – Implementation of Metric Maps on BI Tools
6. Watch and Improve – Monitor Metrics and Improve business process as required

A keen observer will immediately realize that implementing EPM has lot more of pen & paper work (substitute your favorite analysis tool here!) before technology can come into the picture. Also, in my opinion, there is no silver bullet – No single metric map can fit companies across industries or even within same industry. EPM framework for an organization has to evolve in phases based on company’s growth, its corporate vision, and the important numbers at different stages etc. or in other words ‘EPM is very personal to an organization’.

EPM, for a BI practitioner, represents a convergence of many things –

• Domain Understanding
• Quantitative Play
• BI Tool capability
• Closed-Loop BI Architecture
• Knowledge of proven methodologies like Six Sigma, Balanced Scorecard etc.

will try and explain some of the interesting aspects of an EPM strategy like Metrics Profiling, Metrics Maps etc. in the next few posts. Meanwhile, you can take a look at resources like this one (http://www.dmreview.com/issues/20050501/1026062-1.html) to understand the ‘big picture’ with respect to Enterprise Performance Management.

Thanks for reading!

Using Analytic Hierarchy Process (AHP) for BI Tool Evaluation

Enterprise wide BI architecture utilizes a multitude of tools within its landscape, each serving a specific functionality – Extract, Transform and Load (ETL), Data Cleansing, Metadata Management, Databases (both relational and multidimensional), Reporting and Analytics (OLAP), Data Mining etc. For example, just taking the OLAP area alone, there are more than 40 different products that can potentially solve a customer problem. You can imagine the number of combinations possible when all the tool options are combined across the overall landscape. This establishes the fact that one of the most challenging and vexing problems in Business Intelligence domain is Tool Evaluation.

Tool Evaluation and selection has become strategic to the implementation of enterprise wide Business Intelligence. Traditionally, tool selection involved comparing the technical features of the tools, looking at demos by product vendors, reading up industry reports, get word-of-mouth referrals and then taking a final decision. In my humble opinion – that is not sufficient any more.

Technical features, though important, cannot be the definitive criteria for selecting a particular tool. More crucial than technical features is what I term as the “Business Fitment Index”. The selected tool should fit with the characteristics of the business process prevalent in the organization and should take into account the requirements of different classes of users. The concept of Business Fitment can be classified as a Multi Criteria Decision Making (MCDM) problem and one of the powerful tools in this category is the Analytic Hierarchy Process (AHP).

AHP is a systematic procedure that helps to:

1. Represent the elements of any problem, breaking it down into smaller constituents
2. Assign weightages to each constituent by following a pairwise comparison technique
3. Leverage expert judgment and intuitive feel into a coherent framework for problem solving

Though AHP can be used in many situations, Hexaware’s BI practice has perfected the art of leveraging its power in the realm of “BI Tools Evaluation”. There are 3 steps to calculating the Business Fitment Index using AHP.

Step 1 – Pair-wise comparison of business parameters by customer stakeholders is done in this step. The parameters can be things like – Real Time Data Integration, Data Volumes, Data Quality, Business Rules Flexibility etc.

Step 2 – Relative ranking of Business Parameters based on the AHP (Analytic Hierarchy Process) technique

Step 3 – Each of the short-listed tools are evaluated against the business parameters and a final rating is arrived at taking into account the organization readiness factors

Bottom-line is that the technical features of the tools have to be taken in conjunction with the fitment level of tool to the characteristics of the business. That alone would ensure the success of the tool for enterprise wide BI initiatives.

AHP is a simple yet powerful way of arriving at a decision by consensus. There are wide ranging applications of AHP in BI and this is a great area for practitioners to get interested. If you have some thoughts on other applications of AHP in the BI world, please do share it with us. Thanks for reading!

Read More about  Analytic Hierarchy Process