Skip to Content
Actions

BusinessObjects Query builder queries – Part IV

June 10, 2013 | 9,579 Views |
 bo querybuilderbusinessobjects querybuilder
Follow

Dear Folks,

Again this is the continuation of my previous blogs on Query builder.

BusinessObjects Query builder queries

BusinessObjects Query builder queries – Part II

BusinessObjects Query builder queries – Part III

Have listed few more interesting queries below

To 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’

    

            Below are the 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.

To list all hosted services for each server

           SELECT SI_ID, SI_NAME, SI_HOSTED_SERVICES FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’SERVER’

To list all the metrics description

           SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’MetricDescriptions’

To list all the program objects without their instances

           SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’PROGRAM’ AND SI_INSTANCE=0

To list all the personal and corporate category details of the report

           SELECT SI_ID, SI_NAME, SI_PERSONAL_CATEGORIES, SI_CORPORATE_CATEGORIES

           FROM CI_INFOOBJECTS WHERE SI_KIND=’WEBI’ AND SI_NAME = <Name of the Report>

Relationship queries

To list the Usergroups associated with a particular User

           Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS Where PARENTS(“SI_NAME=’UserGroup-User'”,”SI_NAME=’Administrator'”)

To 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'”)

           The list of relationship objects and their relationship types are given below

              

               Relationship Objects           Relationship Types

               Profile-Principal                   ->   PARENT-CHILDREN

               DataConnection-Universe  ->  CHILD-PARENT

               Category-Document            ->  PARENT-CHILDREN

               User-Inbox                            ->  PARENT-CHILDREN

               Webi-Universe                      ->  PARENT-CHILDREN

               User-Favorites                      ->  PARENT-CHILDREN

               UserGroup-User                   ->  PARENT-CHILDREN

               Universe(Core)-Universe    ->  CHILD-PARENTS

               EnterpriseData-Flash           ->  CHILD-PARENTS

               Universe-UserGroup            ->  PARENT-CHILDREN

               CustomRole-Object              ->  CHILD-PARENTS

               User-PersonalCategory        ->  PARENT-CHILDREN

Hope you find this Interesting. Thanks for reading.

Query Builder Blog series

Basics

             BusinessObjects Query builder – Basics

               BusinessObjects Query builder – Best practices & Usability

Sample Queries

             BusinessObjects Query builder queries

               BusinessObjects Query builder queries – Part II

               BusinessObjects Query builder queries – Part III

               BusinessObjects Query builder queries – Part IV

               BusinessObjects Query builder – Exploring Visualization Objects

              BusinessObjects Query builder – Exploring Monitoring Objects

              BusinessObjects Query builder – Exploring Lumira & Design studio Objects


Use cases

               BusinessObjects Environment assessment using Query builder

               BusinessObjects Environment Cleanup using Query builder

               BusinessObjects Query builder – What’s New in BI 4.0   

To report this post you need to login first.

57 Comments

You must be Logged on to comment or reply to a post.

      1. Raj elan

        Hi Mani,

        Your series is Much Useful !!!

        But still i would like to have some clarifications on the below queries.

        I am doing some impact analysis and we would require to delete few objects from each universes but before doing that i would like to make sure those objects are not using in any of the REPORTS. So, I would take a report with informations Universe Name, Report Name, Object Name for each universes.

        I beleive this can be done using QUERY BUILDER.

        For Universe Name and Report Name i can use the below query from your series. BUT CAN YOU PLEASE HELP TO GET RESULTS FOR OBJECT NAMES FROM EACH UNIVERSES WHICH USED IN ALL WEBI REPORTS RELATED TO THAT UNIVERSE?

        To Show count of reports per Universe


        SELECT SI_NAME, SI_WEBI FROM CI_APPOBJECTS

        WHERE SI_KIND=’Universe’ AND SI_WEBI.SI_TOTAL > 0


        To retrieve all Web Intelligence reports connected to a Universe


        SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
        WHERE PARENTS(“SI_NAME=’WEBI-UNIVERSE'”,”SI_NAME =’EFASHION’”)


        Thanks,

        Raj

        (0) 
    2. Usha Shedge

      Mani,

      Your query builder queries are very helpful. I have very unique requirement. We are on BOXi 3.1 SP7 we have crystal reports based on universe with prompts also coming from universe but  same report has 2 or 3 prompts coming form Business View (due to cascading reason we used them from BV). I need to list these reports.

      Your earliest help is appreciated.

      Thanks

      (0) 
  3. Michael Hill

    Mani,

    Good series of blogs …. I was looking through them for an example on trying to list the Usergroups associated with a particular Folder.

    If you have an exmple tha would be really helpful? Is it possible?

    Mike

    (0) 
  4. Surendar K

    Hi

    Great Work…Keep it up…..Thanks For Sharing….


    I want to know the relationship which you have posted  as follow

    Universe-UserGroup       ->  PARENT-CHILDREN


    Can you provide me Query  for this relation…


    i tried this query


    Select SI_ID, SI_NAME From CI_APPOBJECTS,CI_SYSTEMOBJECTS Where

    PARENTS(“SI_NAME=’Universe-UserGroup'”,”SI_NAME=’sample_universe2′”)

    but its returning me Null;

    i need to list the usergroup who all are having access to the universe.

    Many Thanks,

    Surendar.K

    (0) 
  5. Max Payne

    Hello,

    I am new to Business Objects. Your Query Builder series is an amazing study material. I have learnt a lot from it.

    However, there’s one thing which is bugging me right now. I want to find out the number of users who have access to WEBI reports in CMC. I have tried with some queries in Query Builder, but all my efforts have gone in vain.

    Could you please help me? Thanks in advance.

    Regards.

    (0) 
  6. Parshuram Shankaranarayan

    Hi Mani,

    First of all thank you very much for being such a big contributor in this forum, great information and well delivered and explained with utmost simplicity

    I was wondering if any of your blogs have already covered the list of values each field can represent and the meaning of each value …for example …in this blog itself you have shared the various states of a server

                     -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

    Likewise is there any other blog or site I can refer to get the various types of scheduled reports, event based , non-event based, weekly, monthly etc..

    This need not limit to the scheduled types alone, but can also address other areas as well.

    Thanks in advance.

    Parshu

    (0) 
    1. Manikandan Elumalai Post author

      Please refer all my blogs in Query builder series and the comments section in each blog as well. You will get almost all the related information there.

      (0) 
  7. Johnny Jiang

    Hi Mani,

    Could you help me to create a query for the unx universe?

    You did have a good example for the unv universe, like:

    SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS

    WHERE PARENTS(“SI_NAME=’WEBI-UNIVERSE'”,”SI_NAME =’EFASHION’”)

    If I have the ‘efashion.unx‘, how do I retrieve all Web Intelligence reports connected to it?

    Many thanks,

    Johnny

    (0) 
    1. Max Payne

      Hi Johny,

      I have something which may help you.

      There is something called SI_FILES and SI_WEBI.

      SI_FILES – It shows you all the objects which the object of interest has relationship with. This may include connections, reports, parent folders, universes etc.

      SI_WEBI – This displays the number of WEBI reports that a universe has under it.

      In your case, you are trying to view all the WEBI reports that a particular universe has under them. You may try the following query :

      SELECT SI_NAME, SI_ID, SI_WEBI, SI_FILES

      FROM CI_APPOBJECTS

      WHERE SI_ID = ?????

      The SI_ID in the where clause is the ID of the universe that you are interested in.

      But, there is a drawback. You can’t view the names of the WEBI files. The SI_FILES will return the ID of the WEBI files only. JOINS are not supported in query builder.

      I hope this helps a bit, but, I recommend, you should wait for Mani’s reply.

      (0) 
    2. Manikandan Elumalai Post author

      Johnny,

      Can we try this

      SELECT SI_ID,SI_NAME, SI_SL_DOCUMENTS FROM CI_APPOBJECTS WHERE SI_KIND= ‘DSL.Universe’

      This should extract the list of reports for all the .unx universes.

      I am yet to check for Relationship queries. Will keep you posted

      Regards

      Mani

      (0) 
      2. satheesh kumar vadivel

        Hi Mani,

        I am referring your blogs for QB and it is really very useful. We have couple of issues and trying to find some solutions. Please let me know if you have any idea.

        1. Unfortunately Infra team migrated AD groups in the first phase migration.

        2. Now we are migrating major applications which has 75K User id’s (50K + is enterprise), in that we have 300 main groups and sub groups.

        Now we need to assign this id’s manually to each group which is really hetic process and time consuming. Else we have do delete the existing id’s and remigrate which will not work because many of the users are already active.

        Do you have any workaround idea for this?

        Coming to the QB question i tried to extract all 300+ groups and users, i can extract users but not able to find group name. for example

        user id

        xxxx

        but we are looking like below

        user id     user group name

        xxxx          groupname1

        xxxx          groupname2

        xxxyz          groupname1

        We tried with ur Queries but it is giving user names only not giving corresponding user groups.

        Can you help me with ur idea/query?

        (0) 
  9. harshavardhana palla

    Hi Mani,

    Could you please help me to create a query to identify list of all report Names and report path based on universe

    Currently we are using bleow query for list of report names but report path is missing

    SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS

    WHERE PARENTS(“SI_NAME=’WEBI-UNIVERSE'”,”SI_NAME=’Test’”)

    Thanks in advance,

    Harsha

    (0) 
  10. sandrapati sravani

    Hi Mani,

    Is it possible to run a query by recipient for the reports housed in Crystal? I received a request to remove three associates from the distribution list, however was not advised for which reports.

    If this is possible, please advice how it should be requested and i will do so.

    (0) 
  12. Usha Shedge

    Mani,

    Your query builder queries are very helpful. I have very unique requirement. We are on BOXi 3.1 SP7 we have crystal reports based on universe with prompts also coming from universe but  same report has 2 or 3 prompts coming form Business View (due to cascading reason we used them from BV). I need to list these reports.

    Your earliest help is appreciated.

    Thanks

    (0) 
  13. Harshavardhan Konathala

    We are in a need of having the list of all users who are all having access to each folder under public folders in CMC.

    for suppose, under public folders if there were two folders A and B.

    we need a list of all the users along with their rights having access to A folder and similar having access to B folder.

    Your help is appreciable.

    (0) 
  14. Robert Pollack

    Mani,

    I’d need to run a query to select distinct SI_KIND types in all 3 tables. 

    The below executes but brings back 1000 rows of the same type, I need distinct.

    We are sing BOXI3.1 SP7 FP1 and I created a excel vba that executes many queries but I can not find anything on the internet for distinct values for query builder.   

    Set Mis_Kind = IStore.Query( _

                          “SELECT distinct(SI_KIND) ” & _

                          “FROM CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS ” & _

                          “WHERE SI_KIND>” ” & _

                          “ORDER BY SI_KIND”)

    Thanks

    – Bob

    (0) 
  15. Andrew Dale

    Hi Mani

    You have this code but it only reports old style UNv universes. What is the relationship type/object for UNX universes? Their type is DSL.UNIVERSE but using that does not work.

    To 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'”)

    (1) 
      2. Andrew Dale

        I have just discovered you can use wild cards in the CHILDREN command

        this works for my connection called JIRA

        Select si_cuid ,  SI_ID, SI_NAME  , si_kind

        From CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS

        Where Children(“SI_NAME like ‘%Universe%'”,”SI_NAME like  ‘%jira%'”)

        (0) 
        1. Vinesh Pratapwar

          Hi,

          I am trying to find Universe Name (.unx format) and its Connection using Query Builder query; however no luck. šŸ™

          Your help on this will be highly appreciated.

          Regards,

          Vinesh

          (0) 
            1. Vinesh Pratapwar

              Thanks Mani for your reply!

              Below query is returning results for connection used in .unv universe

              Select SI_ID,SI_NAME,SI_KIND From CI_APPOBJECTS where CHILDREN(“SI_NAME=’DataConnection-Universe'”,”SI_NAME=’unv_conn_name'”)

              However, to get .unx universe details, below query is returning zero records..

              Select SI_ID,SI_NAME,SI_KIND From CI_APPOBJECTS where children(“SI_NAME like ‘%Connection%'”, “SI_NAME=’unx_conn_name'”)

              In children() function, provided connection extension ( .cns, .cnx ) for 2nd parameter but got no result. I am not sure on correct value to use for 1st parameter.

              Regards,

              Vinesh

              (1) 
  16. Ravikumar Periasamy

    Hi Team,

    I need an query like group wise users, Can you help me please.

    Groupname         User ID

    Administrator      a1

                             a2

                             b1

    XXgroup             a1

                             b1

    Like that.

    I have an Excel macro, that is working only in 3x not in 4x, and also tell me any idea how to make work in 4x.

    Thanks in advance

    (0) 
  17. Pradeep Prakash

    hi,

    Is there any way we can find all the reports(crystal/webi) based on BW data via universe/Bex queries.

    Any help on this would be highly appreciated

    Thanks

    (0) 
  18. Jian Qin

    Very nice article.  Can you let me know if Query Builder support embedded query.  For example, for this query, I’m not sure if there is syntax error or it’s just not supported:  Thanks!

    Select si_name from ci_infoobjects where ci_parentid in

    (select SI_ID from ci_infoobjects where SI_PARENT_FOLDER=’6818′ and SI_SPECIFIC_PROGID =’CrystalEnterprise.Folder’)

    (0) 
      1. Jian Qin

        Hi Jawahar,

        Thanks for the quick reply.  Please allow me to consult couple of more questions since finally we find an expert šŸ™‚ :

        Is there a way to query all the SI_Path to //output folder?  I mean, seems most return is about .wid files location on //input folder.  But sometimes user mistakenly deletes a report from web-i client, which deletes the instance of report on //output folder, right?  how can we recover it?  If I can collect all reports’ location on //output folder, later by recovering files from backup tape, I can easy copy it back to server, right?  (though I know I can also open .wid file and then save it back to server, but wonder how to do it more efficiently in case there is massive file corruption issue across different folders). 

        Thanks again for your help.

        Jian

        (0) 
        1. Jawahar Konduru

          It is not straight forward. You need to recover FRS and CMS DB, on different server (Sandbox server) and move the corrupted or deleted objects.

          You can SDK to retrieve the actual folder path of the objects, but not by Query Builder. QB will give only FRS path.

          (0) 
          1. Jian Qin

            Thanks, Jawahar.  Since you’ve mentioned SDK a couple of times, I wonder if you can point me to a good link of how to set up SDK?  I’m still pretty junior in this area šŸ˜³ .   Meanwhile, can you also introduce some document that is related to file recovery topic?

            Thanks very much!

            (0) 
  19. Josh thimgan

    Hello Manikandan,

    Thank you very much for the insightful posts on QB. I’m trying to retrieve folder locations of the report and the email destination of the scheduled job. Can you provide a SQL statement for those items?

    (0) 
  20. Raja Sekhar

    Hi,

    I am using BO 4.1

    Some one from the admin users group has changed the CMS port number. Can some one help me how to track the name of the user who has changed the port number.

    Thanks,

    Raj

    (0) 
  21. Andrew Dale

    I heard that a universe was going to be released to duplicate work of query builder so that reports could be run in Business Objects itself

    is there any news on when that may come out?

    regards

    Andrew

    (0) 
  23. Mengyi Yuan

    It is a wonderful series of posts on various queries. Thank you so much.

    I am trying to find something about promotion jobs, but did not find it.

    May I know how I could query a list of promotion jobs?

    Is promotion job considered an INFOOBJECT or SYSTEMOBJECT?

    What is the SI_KIND representing promotion jobs?

    Thanks a lot!!!

    Mengyi

    (0) 
      2. Mengyi Yuan

        Hello,

        I managed to get a list of promotion jobs with their status, but is there a way to go deeper into the details of each job, for example, what object(report/universe etc.) the job is promoting, job dependency…

        I did not find these attributes in the property list of a job, but only some SI_FILES in the format of properties files, are those where such details are stored?

        If so, how could I get details out of the properties files?

        Thank you very much!

        (0) 
  24. Ramanaidu Kolasani

    Hi All,

    ———————–

    Script : 

    SELECT TOP 1000000 SI_NAME from ci_systemobjects where children(“si_name = ‘usergroup-user'”, “si_name = ‘xxxx Viewers'”)

    ———————————————————–

    We are getting  SI_Name and User ID data only with the mentioned script.


    and we are not getting required data(Roles and status) for users based on the roles.


    I need data with SI Name |User Id| Role | Status.

    Do we get this kind of data with the mentioned script ?


    Kindly check the attached image FYI.

        Need data as shown in 1st table.

    Image.PNG

    (0) 
  25. Bharathi Bodapati

    Hi ,

    I need some help , I am trying to get users and groups assigned to them. Can you please let me know how can we achieve this. Below query in your blog gives user groups assigned to one particular user. there are 535 users and I want ti retrieve user groups assigned to all the users.

    Select SI_ID, SI_NAME , SI_USERGROUPNAME From CI_SYSTEMOBJECTS Where PARENTS(“SI_NAME=’UserGroup-User'”,”SI_NAME=’bbodapati'”)

    Please let me know if you have any suggestions.

    Thanks,

    BB

    (0) 
  26. Sudipta Saha

    Hi All,
    I need a help on extracting reports metadata information automatically.

    I need to know which objects are used in which reports. Ā The objects mean dimension/measures directly from the universe as well as calculated ones.
    Any pointers would be highly appreciated.

    Many thanks,
    ~Sudi

    (0) 
  27. Chakradharan Tirumala

    Hi, hope things are going alright.

    Is there a way to extract WEBI report data provider path information using Query bulider?Ā  we have various WEBI reports which are running against excel file stored in Network shared drive. We would like to extract all WEBI reports using excel and where the excel is placed.

    like this

    Report , Report Location, Report data Provider type (excel, csv, etc..), Dataprovder path ( Network shared drive )

    Thanks in advance.

     

    (0) 
  28. Lateesh Garikapati

    Hi All,

    we are using Window AD authentical, we have groupĀ hierarchy

    Example :

    Parent : Ā User Group 1

    Child Ā : User Group 2

    Child : User Group 3

    I am trying to get the group hierarchy list from the parent node.

    Please let me know, how to get the list from Query.

     

    Regards,

    Lateesh

     

    (0) 
    1. Joe Peters

      CMS queries won’t produce a hierarchical result, but you can get all descendants of a particular object. Ā To get all descendant users and groups of User Group 1, use:

       

      select si_name 
from ci_systemobjects 
where descendants("si_name='usergroup-user'","si_name='user group 1'")

       

      Although the name of theĀ relationship is “usergroup-user”, it will actually return descendantĀ groups as well as users.

      (1) 

Leave a Reply