InfoStore Query Builder to Check Relations

23 Replies

There is a nice tool in CMC that allows to check relations between objects. Something similar can be done in the query builder with some non documented features.

For instance, to find all reports that use a universe Finance, you can write:

SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
WHERE PARENTS("si_name='Webi-Universe'","si_name='Finance'")

PARENTS has two arguments. First is the relation type, second is the reference object.

If you want to find opposite – all universes used by a report, you can write

SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
WHERE CHILDREN("si_name='Webi-Universe'","si_name='Balance Sheet'")

What are the possible types of relations (first argument)? You can find the list querying CMS:

SELECT si_name FROM ci_systemobjects WHERE si_relation_table_name='RELATIONS'

Now you can also build such queries and export result to to Excel in InfoStore Query Builder:

23 thoughts on “InfoStore Query Builder to Check Relations”

Prasad August 8, 2013 at 8:50 am

Webi is developed by using the BICS connectivity and not with universe. In this scenario how to get the list of webi reports which are used by the particualr BICS connectivity(OLAP connection).

LikeLike

Reply ↓
1. dmytro Post authorAugust 8, 2013 at 9:28 pm
  
  Well I do not have a system with BICS connection and corresponding reports so it is difficult for me to answer. You can try to display all properties of a Webi document and check if there is any property linking to connection.
  
  SELECT * FROM ci_infoobjects WHERE si_kind=’Webi’ and si_id=
  
  LikeLike
  
  Reply ↓
Sekar August 8, 2013 at 10:10 am

Hi,

I am unable to execute and use this tool, it throw me Could not load file CrystalDecisions.Enterprise.Framework.

I am using BO XI R3.1 SP6

thanks in advance..

And You website is really awesome for BO solutions.

LikeLike

Reply ↓
1. dmytro Post authorAugust 8, 2013 at 9:18 pm
  
  Do you have BO client tools on your PC?
  
  LikeLike
  
  Reply ↓
  1. Sekar August 9, 2013 at 11:09 am
    
    I do have BO client TOOLS in my PC and i am using it
    
    LikeLike
  2. Sekar August 9, 2013 at 11:17 am
    
    I forgot to say to thanks for your reply
    
    LikeLike
  3. dmytro Post authorAugust 11, 2013 at 6:10 pm
    
    Maybe this is because of version incompatibility. The one here was build for 3.1 SP3 FP3.2.
    Well, I will try to look into this this week but I cannot promise anything.
    
    LikeLike
  4. Sekar August 12, 2013 at 8:28 am
    
    Thanks!!
    
    LikeLike
Gina August 14, 2013 at 6:46 pm

Could you help me to create query to find all folders for UserGroup =’Administrators’?
SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
WHERE parents(“si_name=’UserGroup-Folder'”,”si_name=’Administrators'”)
Thank you.

LikeLike

Reply ↓
1. Michael Waller May 6, 2014 at 7:40 pm
  
  It took me awhile to find the solution but there is a two step one. First find the SI_ID of the group you wish to query…
  
  SELECT SI_NAME, SI_ID FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USERGROUP’ AND SI_NAME=’User Group Name’
  
  Let’s say the SI_ID for User Group Name is 15976
  
  Run a query with 15976 in the IsAllowed parameters…
  Select SI_ID, SI_NAME, SI_CUID, SI_KIND From CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS WHere SI_KIND=’Folder’ And IsAllowed(15976, 3)
  
  LikeLike
  
  Reply ↓
  1. Antony August 1, 2014 at 4:07 pm
    
    IsAllowed(15976,3) – What does the 3 mean here? Is there any reason why u used 3?
    
    LikeLike
Sekar September 4, 2013 at 2:52 pm

Hi,

Did you had a chance to build for BO XI R3.1 SP6?

Thanks.

LikeLike

Reply ↓
Ash November 12, 2013 at 10:22 pm
Hello Dymtro

I need to find all the reports under a folder hierarchy.
Like
```
Folder
   folder 1  reports
      folder 1.2 reports
   folder 2 reports
```
etc.

All the information is stored in CI_INFOOBJECTS
Any ideas if I can user children relationship on info objetcs.

Thanks in advance

Ash

LikeLike
Reply ↓
1. dmytro Post authorNovember 12, 2013 at 10:39 pm
  you need to know id of the Folder then you can use SI_ANCESTOR
  e.g.
```
select * from ci_infoobjects where si_kind='Webi' and si_instance=0 and si_ancestor=4
```
  gives all Webi reports in the folder with id=4 and its subfolders
  
  LikeLike
  Reply ↓
  1. Ash November 13, 2013 at 1:40 pm
    
    the si_ancestor condition I tried before but the query never comes back. I get a comment from CMS that CMS timedout after 9 mins.
    
    thank you
    
    ash
    
    LikeLike
Ash November 13, 2013 at 8:45 pm

Ok I got the query to work. si_ancestor by itself is very poor performer. If an indexed column is used along with it, like in my case i needed reports modified after 1st July 2013 then I used SI_UPDATE_TS >= ‘2013.07.01 00:00:00’ and query comes back in seconds.

Trick is to add an indexed column along with si_ancestor. List of indexed columns is as follows:

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

Link to get this list is http://devlibrary.businessobjects.com/BusinessObjectsXIR2SP2/en/en/BOE_SDK/boesdk_dotNet_doc/doc/boesdk_net_doc/html/SDKFundamentals143.html#1143954

LikeLike

Reply ↓
1. dmytro Post authorNovember 13, 2013 at 9:22 pm
  
  Thank you for followup.
  
  Strange.. Do you have that many old reports/instances? Did you add restrictions like SI_KIND=’Webi’ and SI_INSTANCE=0?
  
  LikeLike
  
  Reply ↓
  1. Ash November 14, 2013 at 2:24 pm
    
    Yes I did have those conditions but query wont come back even if I went to the lowest level folder. We have around 12K documents in boe. May be I could have tried by increasing the timeout limit in config file but that would have needed a bounce.
    
    Thanks a lot for all your help. This is a great site for sdk and I am planning to create some macros which will give this and related information and use them company wide.
    
    Ash
    
    LikeLike
Chk123 November 15, 2013 at 9:34 pm

Hey Ash,
I also got similar requirement, extract report list along with associated folder hierarchy. Can you post query which may help me.
How did you download this info to Excel as above list is required to me to compare against second new setup to ensure nothing is missed.

@Dmyto – It would be helpful if you can provide tool which supports BOXI 3.1 SP3

Appreciate your response.

LikeLike

Reply ↓
1. Chk123 November 15, 2013 at 9:51 pm
  
  with above provided tool, it gives “Transport error: communication failure (FWM 00001)”. I’m able login to the Business Objects- Query Builder.
  
  Microsoft Excel 2003, XP, Client 3.1 SP3
  
  LikeLike
  
  Reply ↓
Brenda December 16, 2013 at 3:43 pm

Hi, I am very new to Business Objects and creating queries. I need help writing a query to see what crystal report ran on which server? We have two servers they are clustered.

Thank you

LikeLike

Reply ↓
Shweta June 16, 2014 at 9:47 am

My BO port is 8081. Can you please say where to update this information so as to get the data from CMC?

LikeLike

Reply ↓
sbo June 24, 2014 at 2:35 pm

Hi Dmytro
One simple question. Is it possible to retrieve SQL query(ies) content embedded into each data provider(s) from one given webi report by using query builder or not ?

If yes could you provide us a basic query builder query example for a given webi report please ?

If not, is it possible to retrieve it with the usage of a dedicated method from BusinessObjects Java SDK (i assume getDocumentDataProviders method inherited from interface com.businessobjects.sdk.plugin.desktop.webi.IWebiBase)
Could you confirm it ?

Many thanks for your feedback.
stephan

LikeLike

Reply ↓