Skip to Content
Manikandan Elumalai
Manikandan Elumalai
June 10, 2013 4 minute read

BusinessObjects Query builder queries – Part IV

Follow RSS feed
3 Likes 24,641 Views 64 Comments

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   

Assigned tags

Related Blog Posts

    Related Questions

      64 Comments
      You must be Logged on to comment or reply to a post.
          • Former Member

            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

        • Former Member

          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

      • Former Member

        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

      • Former Member

        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

      • Former Member

        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.

      • Former Member

        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

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

      • Former Member

        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

        • Former Member

          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.

        • 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

          • Former Member

            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?

      • Former Member

        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

      • Former Member

        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.

      • Former Member

        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

      • Former Member

        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.

      • Former Member

        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

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

      • 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

      • Former Member

        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

      • Former Member

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

          • Former Member

            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

      • Former Member

        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?

      • Former Member

        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

      • 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

      • Former Member

        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

          • Former Member

            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!

      • 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

      • Former Member

        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

      • Former Member

        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

      • Former Member

        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.

         

      • Former Member

        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

         

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

      • Alexandra NUNES CLARO

        hi

        does anyone know how one could request against repository data security profile informations

        i encouter errors while trying to modify those DSP and i want to check in the repository

        already find the field SI_SL_DSPSbut it only gives the object_id Nothing more

        thank you

         

      • Former Member

        Hi Mani,

         

        I would like to get the scheduled reports list  depend on a particular event. How can we retrieve this info?

         

        Thanks

        Sandeep Chandran

      • Wright Nicholas

        hi, I hope I have repeated a previous question, great threads

        I need a way using CMS querying to identify given a measure (or cuid) and universe to find where are the locations and title of all reports that use that measure

         

        any clues?

        Nicholas Wright

      • Diwakar G

        Hi, I want to query users of all groups. How can I do this?

        I want result in below format.

         

        User_id         Group_name

         

        Thanks and Regards,

        Diwakar G