InfoStore Query Builder to Check Relations

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

  1. Prasad

    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).

    Like

    Reply
    1. dmytro Post author

      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=

      Like

      Reply
  2. Sekar

    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.

    Like

    Reply
      1. dmytro Post author

        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.

        Like

  3. Gina

    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.

    Like

    Reply
    1. Michael Waller

      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)

      Like

      Reply
  4. Ash

    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

    Like

    Reply
    1. dmytro Post author

      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

      Like

      Reply
      1. Ash

        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

        Like

  5. Ash

    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

    Like

    Reply
    1. dmytro Post author

      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?

      Like

      Reply
      1. Ash

        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

        Like

  6. Chk123

    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.

    Like

    Reply
    1. Chk123

      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

      Like

      Reply
  7. Brenda

    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

    Like

    Reply
  8. sbo

    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

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s