Tag: Business Objects

How to fix Business Objects “Exception: CS, Job already in use”

Error Message: “Exception: CS, Job already in use”

The error message was received when data analyst ran a “Yearly” report

bo cs job already in use

 

The explanation I found. The BusinessObjects Desktop Intelligence client is connecting to the server using an HTTP protocol which uses the network settings in the Internet Explorer.

Internet Explorer IE6 by default set to 60 min time for send/receive timeout

Internet explorer IE7+ by default send/receive timeout set to 30 sec. Running “Yearly” report exceeded the threshold. To fix we can modify the registry manually, ReceiveTimeout DWORD value with a data value of (<number of seconds>)*1000.

Reference http://support.microsoft.com/kb/181050

 

Resolving DA0003 error

 

  1. Download registry patch fix IEFix.reg here
  2. Double click on file and “Run” to install the patch
  3. You will get a security notice about installing the update, click the required buttons to allow changes to the computer
  4. After installation complete you get a message indicating that your registry has been updated. Click the OK button to finish
  5. Restart the PC
  6. Also double check your universe’s connection parameters, and the configuration parameters (screen 3/4). Make sure you have at least the following: Pool timeout of 10 minutes, array fetch size: 10; Array bind size: 5; Login Timeout: 60 minutes.

 

Business Objects Query Builder

Query Builder is a web application to query Business Objects repository. It provides information which can’t be found in Central Management Console (CMC).

BusinessObjects deployment repository made up of set of tables to hold the information about the BI content such as universes, reports, users, schedules, etc. These tables are encrypted in such a way that the information stored in these tables cannot be readable using conventional SQL query tools.

Query Builder gives the opportunity for the Admin/developer to query the metadata stored in the repository.

Why you may ask, because users, auditors may ask some questions with data not ready available. Like what reports were updated since start of the year, categorise reports by kind, number and names of recurring reports etc

How to access Query Builder

To access the Query Builder, in the Internet browser of your choice type the following URL:

http://[BO SERVER]:[PORT]/AdminTools/querybuilder/ie.jsp

Access Query Builder screen

 

Log on as an Administrator to get full access to all the repository objects.

 

Query Business Object repository

 

Query Builder Objects

 There are 3 main objects:

  1. CI_INFOOBJECTS: Covers folders, reports and other content consumed by the end user
  2. CI_SYSTEMOBJECTS: Covers servers, connections, users, and user groups.
  3. CI_APPOBJECTS: Covers BusinessObjects Enterprise applications including InfoView, Desktop Intelligence etc and also objects used by documents such as Universes

 

Query Bulider Functions and operators

  • COUNT – Retrieves the number of distinct values of a property
  • TOP – specifies the maximum number of object to be returned

Operators inlcude: =,!=, >, >=, <, <= , IN, LIKE, BETWEEN, ALL

 

Simple queries Object type

1. CI_INFOOBJECTS

BO Repository System Information

 SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4

Full Client and webi reports in the repository starting with the word “Audit” which are scheduled

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND IN ('WEBI' ,'FULLCLIENT') AND SI_NAME LIKE ‘Audit%’ AND SI_RUNNABLE_OBJECT=1

Reports updated since 1th July 2013

SELECT SI_NAME, SI_UPDATE_TS FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi' AND SI_UPDATE_TS > '2013.07.01'

All Full Client reports that use a specific universe

SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where PARENTS ("SI_NAME = 'Webi-Universe'", "SI_NAME = 'Universe Name'")

List of all the public folders (Non System Folders)

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=23 AND SI_NAME!='REPORT CONVERSION TOOL'  AND SI_NAME!= 'ADMINISTRATION TOOLS' AND SI_NAME!= 'AUDITOR'

List all the WebI reports with prompts

SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS, SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES, SI_PROCESSINFO.SI_PROMPTS FROM CI_INFOOBJECTS WHERE  SI_KIND = 'WEBI' and SI_INSTANCE = 0 and SI_PROCESSINFO.SI_HAS_PROMPTS=1

Which Reports Use my_Universe

SELECT SI_ID, SI_NAME, SI_UNIVERSE FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where PARENTS("SI_NAME='Webi-Universe'","SI_NAME ='Universe Name'")
and SI_KIND = 'Webi' and si_instance = 0

List of available categories

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=45

 

2. CI_APPOBJECTS

Total Count of univeses in the deployemnt

SELECT COUNT (SI_ID) FROM CI_APPOBJECTS WHERE SI_KIND = 'UNIVERSE'

List the Universes associated with a particular Data connection

Select SI_ID,SI_NAME,SI_KIND From CI_APPOBJECTS where CHILDREN("SI_NAME='DataConnection-Universe'","SI_NAME='efashion-webi'")

List of all your universes updated since some date

SELECT SI_NAME, SI_REVISIONNUM, SI_UPDATE_TS   FROM CI_APPOBJECTS WHERE SI_KIND='UNIVERSE' AND SI_UPDATE_TS > '2013.07.01'

Count of reports per Universe

SELECT SI_NAME, SI_WEBI FROM CI_APPOBJECTS WHERE SI_KIND='Universe' AND SI_WEBI.SI_TOTAL > 0

 

3. CI_SYSTEMOBJECTS

Business Objects File Repository Server Information

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'SERVER' AND SI_NAME LIKE '%FILEREPOSITORY%'

List all the servers and their status with server category

SELECT SI_NAME, SI_SERVER_IS_ALIVE, SI_DISABLED, SI_SERVER_KIND, SI_FRIENDLY_NAME, SI_EXPECTED_RUN_STATE FROM CI_SYSTEMOBJECTS WHERE SI_KIND='SERVER'

         Possible values for SI_EXPECTED_RUN_STATE object

  • -1:  Server is currently in an invalid state due to a configuration error.
  • 0:  The expected state of the server is stopped.
  • 1:  The expected state of the server is running.
  • 2:  The expected state of the server is restarting.
  • 3:  The server is not being managed by the Server Intelligence Agent.
  • 4:  The expected state of the server is immediate shutdown.

Users in specific user group

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")

List all the Users created between a date ranges

SELECT SI_ID,SI_NAME,  SI_CREATION_TIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User' AND SI_CREATION_TIME BETWEEN '2013.11.01' AND '2013.11.30'

 Users member of more number of groups

SELECT SI_ID, SI_NAME, SI_USERGROUPS FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'USER' AND SI_USERGROUPS >= ALL SI_USERGROUPS

List of available Calendars 

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_PARENTID=22

Count total number of connection on a particular day

SELECT COUNT (SI_CREATION_TIME) FROM CI_SYSTEMOBJECTS WHERE SI_LASTLOGONTIME > '2013.10.15.00.00.01' AND SI_KIND = 'Connection'

Total number of unique users logged in to the system on a particular day

SELECT COUNT(SI_NAME)  FROM CI_SYSTEMOBJECTS WHERE SI_LASTLOGONTIME> '2013.10.15.00.00.01' AND SI_KIND = 'Connection'

 

NOTE:

  • Default limit for returning objects is 1000 objects. In order to get more than 1000 objects use ‘Top N’ function before the column listing in the query.
  • No sub queries allowed
  • The order of columns in the SELECT clause has no impact as the results will be rendered in its own order

BOXI3 Desktop Intelligence Connection Error Troubleshooting

Problem: One of the users was unable to refresh an existing DESKI report and was getting the following error

Connection or SQL sentence error: (DA0005)

DESKi Connection Error D0005

Followed by connection unavailable error (DA004)

 DESKi connection required D0004

Possible Causes

 

  • Cause 1: You may get a connection or SQL sentence error after changing the date format in the sbo file.

 

Action: Verify that the DATE_FORMAT parameter is set to the date format that you have set in the sbo file. 

 

  • Cause 2: Your universe connection has been moved, deleted, renamed, or account accessing data changed access rights.

 

Action: Check connection and its parameters. Data Access driver, connection and login parameter and connection type

 

  • Cause 3: Your drivers have been moved, deleted, renamed, or damaged. 

Action: Install the Microsoft drivers again. 

After checking all of the above I found out that universe was using OLEDB connection and also user has moved his machine.

Solution:

I installed SQL Native Client for the appropriate version of the SQL Server universe tried to access on his machine and it worked.

That led me to the questions:

What is the difference between OLE Db and ODBC data access?

What is preferred method connecting to databases today?

Comparing OLE DB and ODBC

OLE DB and ODBC are both specifications created by Microsoft to address universal data access.

ODBC is an industry standard and a component of Microsoft® Windows®  Open Services Architecture (WOSA). The ODBC interface makes it possible for applications to access data from a variety of database management systems (DBMSs)  through a single interface. The application in this case is independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which create an interface between an application and a specific DBMS.

Microsoft’s definition of OLE DB is a strategic system-level programming interface to data across the organization. OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data.

 

OLE DB

ODBC

Component based specification Procedural based specification
Supports all forms of data stores: relational,

hierarchical, e-mail and file system stores; text, graphical, and geographical data; custom business objects; and more

Constrained to relational data stores
Richer and more flexible interface for data access Tightly bound to a command syntax
Better support Internet integration and multi-threading More established interface proven drivers

and applications available in the market place

Easy to deploy Difficult to deploy due since many software pieces must be synchronized (server, driver, OS, etc)
Accomodates several locking models that allow multiple concurrent updates No concept of record locking
Worked only in Windows work. Mostly for Microsoft data systems Access data from multiple platform

 

We have been told many times by Microsoft to start migrating applications to OLE DB because it is the industry direction to access all kinds of data and it’s fully integrated in Microsoft’s desktop environment.

However recently, Microsoft made an announcement that sounded like a cruel joke. After years of telling users that Open Database Connectivity (ODBC) was an old technology that should no longer be used and would be phased out in favour of Object Linking and Embedding Database (OLE DB), they declared ODBC the new king of data access and deprecated OLE DB.

http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx

 

Why ODBC is a winner for the data access?

The explanation is obvious — ODBC has survived and prevailed mainly because there is a strong need to access database systems from multiple platforms. OLE DB worked only in the Windows world and an OLE DB provider was usually a Microsoft product.

In the last few years, the need for cross-platform and cross-database communication greatly increased. There are lots of new database products, many of them based on NoSQL technologies. Heterogeneous database systems on multiple platforms including Windows, Linux, iOS, Android etc need to communicate with each other to satisfy the needs of business users. Structured data systems based on relational databases often need to connect to unstructured data stores.

Microsoft realized, based on surveys and customer feedback, that ODBC is the most established cross-platform data access technology.

Will this affect your existing applications?

OLE DB for SQL Server will continue to be supported for several years. Mainstream support for SQL Server 2012 will last until July 2017.

Many components of SQL Server use OLE DB, including linked servers, distributed queries, SQLCMD utility or OLE DB for OLAP. So Microsoft will now need to re-architect these components and figure out how to make them work either with ODBC or some other way of communicating at the SQL Server level.

How to Show a friendly message when “No Data to Fetch” in Business Objects reports

Receiving “No data to retrieve in <query>” or “There is no data corresponding to this query…” pop-up boxes is very confusing for end users. In addition your Web Intelligence (viewers and Java Report Panels) and Desktop Intelligence document may look not as intended. It will typically show column headers without any content, zero totals, and some formulas may even display awkward error messages.

Business Objects "No data to fetch"

Causes for the “No data to retrieve in” message

There could be lots of different factors. I list the most common issues

  1. Parameters and/or report conditions are too restrictive or negating each other.
  2. The data is missing in the table you query from the report
  3. Row-level restrictions for the current user
  4. Incorrect universe connections

However there are times when you may expect that report will not have any data for any or all queries. So how can we suppress this pop-up window

Suppressing the “No data to retrieve” message?

To overturn this message some Business Objects forum threads proposing the use of VB macro or some other SDK integration. Yet there are more simple ways to do this.

1. Use conditions in the report objects. Create a variable that will hold number of rows returned by data provider eg variable “RowCount”, formula =NumberOfRows(DataProvider(<MyQuery1>))

The next step is to assign condition to your table/chart to be visible when datasourse returns some data and hide it otherwise. Create “No data” block to display when datasource contains no data. Position both objects on top of each other

2. Create a union in your datasource query such that always one of the queries returns a value. I usually use my dummy empty objects for the second query. The only catch is that number of objects and objects types in the “Result Objects” pane must be the same. To keep you report clean you may need to add report level filter. Note dummy objects must reference dummy table or view in order to parse in Designer. 

Business Objects Enterprise XI R3.1

What is BusinessObjects Enterprise?

BusinessObjects Enterprise is a flexible, scalable, and reliable solution for delivering powerful, interactive reports to end users via any web application – intranet, extranet, internet or corporate portal. It’s an integrated suite for reporting, analysis, and information delivery.

BO Enterprise provides a solution for increasing end-user productivity and reducing administrative efforts.

What is BusinessObjects Enterprise XI?

BusinessObjects Enterprise XI is the business intelligence platform based on industry-standard, proven architecture and platform support for semantic layers, data integration, and security.

It supports the entire range of performance management, reporting, querying, and analysis applications. It also provides

BusinessObjects Enterprise XI provides full web-based  administration and configuration of the entire system.

The architecture is designed to meet the needs of virtually any BI deployment, and is flexible enough to grow from several users with a single tool, to tens of thousands of users with multiple tools and interfaces.

BusinessObjects Enterprise components can be installed on one or many machines to provide flexibility, reliability, and scalability.

 

BO Enterprise Infrastructure

The Enterprise Infrastructure is a series of services that are designed to communicate via CORBA (Common Object Request Broker Architecture), which runs over TCP/IP.

The Enterprise Infrastructure provides the framework for establishing connections between clients and servers.

 

Business Objects Enterprise Architecture

 

BusinessObjects Enterprise Infrastructure is:

  • The centerpiece of BusinessObjects Enterprise technology
  • Facilitates communication between servers
  • Provide means of a client object make requests to server objects and server object in serving requests to client objects.

BusinessObjects Enterprise components

  1. BusinessObjects Enterprise web services
  2. BusinessObjects Enterprise management services
  3. BusinessObjects Enterprise storage services
  4. BusinessObjects Enterprise processing services
  5. Server Intelligence
  6. Life-Cycle Management
  7. Client Applications