BusinessObjects-How to find which report is running from database session

Sometimes you see very large SQL expressions which are killing your database systems. With audit reports you can find which reports have run after they finished. But with this little code you can directly find the user, the report and the universe from your database directly on demand.

http://www.dallasmarks.org/blog/2011/10/tips-and-tricks-identifying-business-objects-queries-using-end_sql/

Just put this lines to your universe parameters "END_SQL" line:
/* @Variable('UNVNAME') - @Variable('BOUSER') - @Variable('DOCNAME') */

Thats it. Your query will look like this:

Your dba will appreciate you.

BusinessObjects-How to use Oracle hints in Universe

If you use an Oracle connection in your universe and put the tables from the table browser, you can't use hints with your table. You can use derived tables for use of Oracle hints.

But there is another way put hints to your queries. Just go to your universe. Create an object with your rule. For example:

There is a "1" at the end. Because BO puts a comma sign after each object by generating the sql. Therefore we put a dummy value.

Ok. Lets get a look after we export our universe and use the hint object. (Don't forget to use this object as the FIRST object in Webi)

BusinessObjects-Webi Ignores Prompt

Today i faced a new property(!) of BI4. I create a query. Put my dimensions and my prompts. After running the query i saw that my query returns result which schould filter after my prompt.

I checked the SQL script and there it is. The query stays without the prompt. See the picture below.

As you seen on the screenshot, i have two prompts but the script shows me only one of them. After a little search i found that the properties are different of the dimensions.

Aftermath: Webi prompt doesn't support "Hierarchical Display" dimensions.

Tweet

BusinessObjects-Query Builder Examples

Main tables: CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS

Some scenarios:

Which BusinessObjects Active Directory Groups don't have any member:
• Select SI_NAME from CI_SYSTEMOBJECTS WHERE SI_KIND = 'UserGroup' and SI_GROUP_MEMBERS is null

Which Universes use the Connection 'con1'?
• Select * FROM CI_APPOBJECTS WHERE CHILDREN("SI_NAME = 'dataconnection-universe'", "SI_NAME = 'con1'")

List of all Universes
• Select SI_NAME, SI_ID FROM CI_APPOBJECTS WHERE SI_KIND='Universe'

List of all Universe Connections
• Select SI_ID, SI_NAME FROM CI_APPOBJECTS WHERE SI_KIND = 'metadata.dataconnection'

List of Active Connections
• Select * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='Connection'

Which Reports Use my Univ1 Universe
• SELECT si_id, si_name,si_universe,si_cuid
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
Where PARENTS("SI_NAME='Webi-Universe'","SI_NAME ='Univ1'")
and SI_KIND = 'Webi' and si_instance = 0

List of Named Or Concurrent Users
• SELECT si_name FROM CI_SYSTEMOBJECTS where SI_NAMEDUSER=0 AND SI_KIND='User'
SI_NAMEDUSER=0 is for Concurrent users. Use SI_NAMEDUSER=1 for Nameduser

To bring back all folders and objects in the ‘Report Samples’ folder:
• select * from ci_infoobjects, ci_appobjects, ci_systemobjects where DESCENDENTS("si_name='Folder Hierarchy'","si_name='Report Samples' and si_kind='Folder'")

To bring back only direct children (objects and folders) in the ‘Report Samples’ folder:
• select * from ci_infoobjects, ci_appobjects, ci_systemobjects where CHILDREN("si_name='Folder Hierarchy'","si_name='Report Samples' and si_kind='Folder'")

To list reports and documents those are in public folders including Sub folders. (Excluding instances, personal documents and inbox documents):
• SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Txt', 'Excel', 'Webi', 'Analysis', 'Pdf', 'Word', 'Rtf', 'CrystalReport', 'Agnostic') AND SI_RUNNABLE_OBJECT = 0 AND SI_INSTANCE_OBJECT = 0 AND SI_ANCESTOR = 23

Tweet

BusinessObjects-Universe not Found WIS 00501

Let's try to explain the situation what we faced. You have two BusinessObjects environments. Our goal is export webi reports and universes to a biar file and import them to the target system.

Everything looks fine? Here comes the nightmare. You refresh the reports and



There are many possible reasons. Our root cause was security.

Under Central Management Console look to the "Universes" folder. On the "Rights" tab be sure there is a group which has access to this folder (Universes) and this group belongs to a group that want to view the webi reports.

For the second be sure that the same group has access to the Universe Connections. You can check this in Central Management Console->Universe Connections->Rights.

More about this article can be found here :
http://www.businessobjectstips.com/tips/web-intelligence/what-is-an-unbound-report-and-why-should-i-care/

Tweet