Query Builder in BO – Browse / Query BO Repository

/ January 27, 2014

Query Builder is one of the powerful administrative tools in Business Objects and it’s used to query your BO repository to get all kinds of information which is not even available in CMS. It is available only in BO XI R2 and adobe versions.

BO repository has set of tables to store BI information such as Universes, reports, Users, schedules, security that make up your deployment, etc. Repository tables are encrypted in such a way that it can’t query with conventional SQL tools. This is the place where Query Builder tool comes in and get those Repository Tables metadata information. Query Builder is a simple GUI tool which enables the user to run queries against the BO repository (also known as the CMS InfoStore).

The CMS repository stores metadata in the form of InfoObjects. All the contents of BO Enterprise system can be classified into two types: metadata and the actual file. For example, if there is a Crystal Report document in the BOE system, the metadata (including Report name, type, ID, CUID, path, etc.) is stored as an InfoObject in the CMS Repository. The Crystal Report document itself (i.e. the .rpt file) is stored as a file on the File Repository Server (FRS).

Note:  To access your Query Builder, use the below URL link in the Web browser.

“https://<ServerName>/AdminTools/querybuilder/ie.jsp&#8221;

CMS Repository Structure

The CMS metadata is physically stored on a database, but we browse the InfoObjects from virtual tables. To give a clear picture, let’s begin with physical structure at the database level.

Physical Database Tables
There are 6 tables on the database level to store the metadata.

CMS_VersionInfo  – Contains the current version of BO Enterprise.

CMS_InfoObjects – Important table in the repository and each row in this table stores a single InfoObject. The table contains the following columns:
ObjectID, ParentID, TypeID, OwnerID, Version, LastModifyTime, ScheduleStatus, NextRunTime, CRC, Properties, SI_GUID, SI_CUID, SIRUID, SI_INSTANCE_OBJECT, SI_PLUGIN_OBJECT, SI_TABLE, SI_HIDDEN_OBJECT, SI_NAMEDUSER, SI_RECURRING, SI_RUNNABLE_OBJECT, SI_PSS_SERVICE_ID, ObjName_TR, SI_KEYWORD, SI_KEYWORD_TR, LOV_KEY.

CMS_Aliases6 – This table maps the user alias to the corresponding user ID. A user has an alias for each security domain in which they are members. For example, a user may have both a Win AD alias and an LDAP alias. Regardless of the number of aliases a user may have, in the BI Platform each user has only one user ID. The map is stored in a separate table to enable fast logins.

CMS_IdNumbers6 – The CMS uses this table to generate unique Object IDs and Type IDs. It has only two rows: an Object ID row and a Type ID row. The CMSs in a cluster use this table when generating unique ID numbers. GUIDs, RUIDs and CUID are generated with an algorithm that does not use the database.

CMS_Relationships6 – Relationship tables are used to store the relations between InfoObjects. Each row in the table stores one edge in the relation. For example, the relation between a Web Intelligence document and a Universe would be stored in a row in the WebI – Universe Relation table. Each relationship table has these columns:
Parent Object ID,Child Object ID,Relationship InfoObject ID ( this Default InfoObject “DFO” describes the properties of the link between the two objects.), member, version, ordinal, data. Relationship tables are defined by default objects.

CMS_LOCKS6 – This is auxiliary table of CMS_RELATIONS6

Virtual Tables in BO Repository

The repository information is stored in the form of InfoObject and CMS can read repository tables from virtual tables. Which are described detailed manner as given below.

Reository Tables

Description

CI_SYSTEMOBJECTS

Contains User, User Group, Server, Server Group ,Folder, Sub Folder, Connection, Calendar, Event information

CI_INFOOBJECTS

Contains Webi, Crystal Report, Full Client, PowerPoint, Pdf, Excel, Word, Rtf, Txt, Program, Shortcut information

CI_APPOBJECTS

Contains Universe information

Note: When you log in to Query Builder, Log on as Administrator, so that you will get complete (full) access to the repository objects.

Query Builder Limitations:

Query Builder has limitations and below is the details.

  • Need to write the query manually in the Query Builder or you can use the step-by-step wizard to build the query which is too simplistic.
  • You should be aware the complete Repository table Names, objects and what kind of information it holds on.
  • Need to write several queries to get the required result. For example, If you want the objects in the folder, first you have to write a query to get the Folder ID, then use that ID and get the object in that particular folder.
  • Query Builder returns the result set by individual tables (Structure of the Result) and you have to copy past each & every tables and made your required format.
  • Cannot execute / run multiple queries in parallel.
  • The Default limit for returning objects would be 1000 objects. In order to get more than 1000 objects we need to use ‘Top N’ function before the column listing in the query. For ex.  SELECT Top 2000 * FROM CI_INFOOBJECTS
  • Query Builder won’t allow Sub queries.
  • The order of columns selected in the SELECT clause has no impact in the result returned and in its own order and order cannot be changed.

Query Builder Login:

Query Builder1Query Builder Tool:

Query Builder2Note: Query Builder can be used to query the information only stored in the CMS not in the File repository files.

Most wanted / Beneficial Query Builder BO Query

To get BO Repository Information

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4

Sample Result Set for above Query
Query Builder3Find Cluster Unique Identifiers that uniquely identify an InfoObject (CUID) for a specified report and this CUID is useful for document linking.  You can find the CUID information in the Properties of each report in CMC & Infoview.

SELECT SI_CUID FROM CI_INFOOBJECTS WHERE SI_NAME = ‘SALES REPORT’

Find the List of users fro a particular Group (ex – USA is the Group Name here)

SELECT SI_USERFULLNAME FROM CI_SYSTEMOBJECTS WHERE CHILDREN(“si_name = ‘usergroup-user'”, “si_name = ‘USA'”)

Find the report list for a particular Folder.

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER=’9565′

Find complete details about users.

SELECT SI_KIND, SI_NAME, SI_USERFULLNAME, SI_CREATION_TIME, SI_ALIASES, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘User’

Find Repository server details.

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘SERVER’ AND SI_NAME LIKE ‘%FILEREPOSITORY%’

Daily Scheduled / Recurring report Details

SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MONTHS FROM CI_INFOOBJECTS WHERE SI_SCHEDULE_STATUS !=8  AND SI_RECURRING = 1

Retrieve Public Folders other then System Folders

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=23 AND SI_NAME!=’REPORT CONVERSION TOOL’  AND SI_NAME!= ‘ADMINISTRATION TOOLS’ AND SI_NAME!= ‘AUDITOR’

Get the List of Webi reports along report details.

SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS, SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES, SI_PROCESSINFO.SI_PROMPTS  FROM CI_INFOOBJECTS WHERE  SI_KIND = ‘WEBI’

Get Full Client (Deski reports) reports details.

SELECT SI_ID, SI_NAME,SI_PARENT_FOLDER,SI_FILES FROM CI_INFOOBJECTS
WHERE SI_KIND = ‘FULLCLIENT’

Get the Report list which are using more than one universes.

SELECT SI_ID, SI_KIND, SI_NAME FROM CI_INFOOBJECTS WHERE SI_UNIVERSE.SI_TOTAL>1

Get complete status of the Events (name, location and file information)

SELECT SI_ID, SI_NAME, SI_FEATURES FROM CI_SYSTEMOBJECTS WHERE SI_KIND= ‘Event’

Complete Details about the Scheduled report which are based on Events

SELECT SI_NAME, SI_SCHEDULEINFO FROM CI_INFOOBJECTS WHERE SI_RUNNABLE_OBJECT = 1 AND SI_SCHEDULEINFO.SI_DEPENDENCIES.SI_TOTAL > 0

To Get non scheduled Deski reports.

SELECT SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER
FROM CI_INFOOBJECTS
WHERE SI_KIND = ‘FULLCLIENT’ AND SI_CHILDREN = 0 AND SI_SCHEDULEINFO.SI_SCHED_NOW = 0

Get complete details of the reports from a particular Universe

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

Get particular report information

SELECT SI_DOC_SENDER, SI_NAME,SI_DESCRIPTION, SI_FULLCLIENT_PROMPTS, SI_TOTAL, SI_FULLCLIENTDATAPROVIDERS FROM CI_INFOOBJECTS WHERE SI_NAME=’Sales Report’

Complete User Groups information of your BO system.

Select SI_ID, SI_ALIASES, SI_DESCRIPTION, SI_NAME, SI_USERGROUPS, SI_GROUP_MEMBERS from CI_SYSTEMOBJECTS  where si_kind = ‘UserGroup’

Get complete set of users, folders(including personal folder), Categories and Users inbox.

SELECT SI_PARENTID,SI_NAME,SI_KIND FROM CI_INFOOBJECTS

Users Inbox – SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=48
Categories – SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=45

User Inbox, Favorites and Personal Folder ID information.

SELECT SI_ID, SI_NAME, SI_INBOX, SI_FAVORITES_FOLDER, SI_PERSONALCATEGORY
FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

Get the user login count for a particular day to the system

SELECT count (SI_NAME)  FROM CI_SYSTEMOBJECTS
WHERE SI_LASTLOGONTIME> ’2013.02.14.00.00.01′ AND SI_KIND = ‘Connection’

Latest Accessed Documents and alerts for the user.

SELECT SI_ID, SI_NAME, SI_RECENT_DOCUMENTS, SI_RECEIVED_ALERTNOTIFICATIONS
FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

Get Complete Calendar details.

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND= ‘Calendar’

Complete status of each user

SELECT SI_ID, SI_NAME, SI_CREATION_TIME, SI_UPDATE_TS, SI_LASTLOGONTIME, SI_FAILEDLOGONCOUNT, SI_NAMEDUSER FROM   CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

Get Scheduled report complete details (report name, Description, scheduled frequency and etc) for a particular user.

SELECT * FROM CI_INFOOBJECTS WHERE SI_OWNER = ‘BOUSER1’ AND SI_RECURRING = 1

For more details about the each & every Objects in the repository tables, please check the SAP SDK link.

http://help.sap.com/businessobject/product_guides/boexir31/en/boesdk_net_dg_12_en.zip

Related posts

83 thoughts on “Query Builder in BO – Browse / Query BO Repository”

  2. Nice Blog Overall. I have an other query, currently we are using BO 4.1 in that we need to see all the BO sers who ran/executed a particular BO report in recent past. Is there any way to get the list using query builder?

    Reply

    • Hi Akash,

      Query for the Input File Repository Server (iFRS).

      SELECT TOP 1 SI_NAME, SI_SERVER_DESCRIPTOR, SI_SERVER_IS_ALIVE, SI_STATUS_CHECK_TS
      FROM CI_SYSTEMOBJECTS
      WHERE SI_PROGID=’CrystalEnterprise.Server’
      AND SI_SERVER_KIND=’fileserver’
      AND SI_SERVER_IS_ALIVE=1
      AND SI_NAME LIKE ‘%Input%’
      ORDER BY SI_SERVER_DESCRIPTOR

      you can modify the query to SI_NAME LIKE ‘%Output%’ to examine the Output File Repository servers (oFRS) instead.

      Also you can try the below to get server information.

      SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4

      SELECT * FROM CI_SYSTEMOBJECTS
      WHERE SI_KIND = ‘SERVER’ AND SI_NAME LIKE ‘%FILEREPOSITORY%’

      Thanks & Regards
      MadhesDWBI

      Reply

      • Hello MadhesDWBI,
        Can you please let me know the query which will give me all the report name along with the folder path?? really appreciate your help

        Thanks
        Krish

        Reply

        • Hi Krish,

          To list Universe that doesn’t associated with any WebI reports

          SELECT si_id,si_name,si_webi,si_cuid FROM CI_AppObjects
          WHERE si_kind = ‘Universe’ and SI_WEBI.SI_TOTAL=0

          To list Universes with more than one connections (multi source universe)

          SELECT si_id,si_name,si_webi,si_cuid FROM CI_AppObjects
          WHERE si_kind = ‘Universe’ and SI_DATACONNECTION.SI_TOTAL>1

          To list WebI reports that doesn’t associated with any universe

          SELECT TOP 50000 si_id,SI_NAME FROM CI_Infoobjects
          WHERE si_kind = ‘WebI’ AND SI_INSTANCE=0 and SI_UNIVERSE.SI_TOTAL=0

          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

          Find all the WebI reports that use a specific universe

          SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
          Where PARENTS (“SI_NAME = ‘Webi-Universe'”, “SI_NAME = ‘Universe Name'”)

          To List of all Groups with Subgroups

          Select SI_ID, SI_ALIASES, SI_DESCRIPTION, SI_NAME, SI_USERGROUPS, SI_GROUP_MEMBERS from CI_SYSTEMOBJECTS where si_kind = ‘UserGroup’

          To get a list of Full Client reports

          SELECT SI_ID, SI_NAME, SI_KIND FROM CI_INFOOBJECTS WHERE SI_KIND = ‘FullClient’

          To get a list of available Calendars

          SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_PARENTID=22

          To get a list of Users along with their personal folder

          SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=18

          To get a list of Users along with their inbox

          SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=48

          To get a list of available categories

          SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=45

          To count total number of connection on a particular day (Today)

          SELECT count (SI_CREATION_TIME) FROM CI_SYSTEMOBJECTS
          WHERE SI_LASTLOGONTIME> ‘2013.02.14.00.00.01’ AND SI_KIND = ‘Connection’

          To get total number of unique users logged in to the system on a particular day (Today)

          SELECT count (SI_NAME) FROM CI_SYSTEMOBJECTS
          WHERE SI_LASTLOGONTIME> ‘2013.02.14.00.00.01’ AND SI_KIND = ‘Connection’

          Points to consider while querying
          the default limit for returning objects would be 1000 objects normally. In order to get more than 1000 objects we need to use ‘Top N’ function before the column listing in the query. For ex. SELECT Top 2000 * FROM CI_INFOOBJECTS where Si_KIND=’WebI’
          You are not allowed to use Sub queries.
          The order of columns in the SELECT clause has no impact as the results will be rendered in its own order

          Thanks & Regards
          MadhesDWBI

          Reply

      • Hello MadhesDWBI,
        How to get a list of Webi report for all user Inbox & Personal folder.
        Any idea please.
        Thanks

        Reply

    • We cannot get WEBI report SQL using query builder because reports SQL generated on the runtime. You can try using Audit DB or SAP information Steward or try some BO SDK.

      Reply

  4. Hi Madesh,

    Great information about the query builder. I was trying to schedule a monthly BO report which can take the output of query ran on Query Builder as a source, is there any way I can create output in a excel format automatically or in a scheduled way?

    Thanks,
    SG

    Reply

    • Thanks!

      You can get the Query Builder Result into Excel using third party tool (InfoStore Query Builder)

      The purpose of the InfoStore Query Builder tool is to simplify building CMS metadata queries and provide possibly to export the result to Excel.

      BO XI 3.1: Download

      BO BI 4.0: Download

      Thanks
      MadhesDWBI

      Reply

      • Hey Madhes

        Another way they can save to excel is save the output page as txt from (Page Settings) Save as txt, then open in Excel.

        Reply

  5. We have scheduled reports running on daily/monthly basis. I need to develop a portal which will display the list of reports completed today. Could you please help

    Reply

    • HI, You Can achieve your requirement using Query builder or some third party tools like Apos, Infoobjects, etc.,

      You can try with below Query in Query builder To get the list of all reports scheduled and below are some samples and you have to tune to get your needs.

      SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,
      SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MONTHS
      FROM CI_INFOOBJECTS
      WHERE SI_SCHEDULE_STATUS !=8 AND SI_RECURRING = 1

      select SI_NAME, SI_SCHEDULEINFO.SI_SUBMITTER, SI_SCHEDULEINFO.SI_STARTTIME from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_STARTTIME>=’2015.07.10.20.00.00′ and SI_SCHEDULEINFO.SI_STARTTIME<'2015.07.11.13.00.00' order by SI_SCHEDULEINFO.SI_STARTTIME 00

      Thanks
      MadhesDWBI

      Reply

  6. Great blog entry.
    I’ve just begun using Query Builder and am having some difficulty pulling some data. I’m looking to pull the report name, when it is scheduled to run (daily,weekly,etc.) and the email address the report is being mailed out to.

    I found this query but it’s not pulling back the destination email addresses. Could you assist?

    SELECT
    SI_DESTINATIONS.SI_DEST_SCHEDULEOPTIONS.SI_MAIL_ADDRESSES,
    SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,

    SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MONTHS

    FROM CI_INFOOBJECTS

    WHERE SI_SCHEDULE_STATUS !=8 AND SI_RECURRING = 1

    Reply

    • Hi Mik,

      Use the below query to get your destination email address.

      SELECT SI_SCHEDULEINFO.SI_DESTINATION,
      SI_SCHEDULEINFO.SI_MAIL_ADDRESSES,
      SI_SCHEDULEINFO.SI_NAME
      FROM CI_INFOOBJECTS WHERE SI_KIND = ‘your report details’

      Thanks
      MadhesDWBI

      Reply

      • Hi, Could you please help me out with a query to identify the reports scheduled in a specific folder and all the schedule information, especially the email addresses to those the instances are sent. I tried your query above but i don’t get the fields SI_SCHEDULEINFO.SI_DESTINATION,
        SI_SCHEDULEINFO.SI_MAIL_ADDRESSES,
        SI_SCHEDULEINFO. delivered from query builder.
        I would have to create an excel list of all Reports with recipient addresses and scheduling intervals.
        Thanks in advance for you help!

        Reply

  7. I was wondering if it’s possible to see what non-scheduled Crystalreport is running and who’s running it. I mean the ones that are stared manualy. What I’m looking for is a way to detect what report might cause a high CPU usage. Hopefully figure out what the PID is from that report and kill it?

    Reply

      • Hi,

        it works with the following statement:

        SELECT SI_ID, SI_NAME, SI_DOCUMENT FROM CI_APPOBJECTS WHERE SI_KIND = ‘CommonConnection’ AND SI_ID = ‘SI_ID of my OLAP connection’

        Reply

  10. Hi,
    Is it possible to count the number of reports for each WebI document in the Repository (1 WebI doc can have 1 or many reports inside) ?
    How ?
    Thanks !
    Sreve

    Reply

  11. hi
    can you please help me to find the below query.
    I want the query to find out what type of connection is associate with universe ?
    thanks,
    Sanju

    Reply

  12. Hi Madhes,
    This is very helpful,,,
    we are on 4.1 SP7
    kindly help me with how to find the
    number of users who uses Cystal Report
    number of users who uses Web i

    Thank You !!!

    Reply

    • Hi Roan,

      Thanks!

      User details are present in CI_SYSTEMOBJECTS table and webi, Crystal reports details are present in INFOOBJECTS TABLE And we have to combine both the tables and can get the result which you are requesting.

      Reply

  13. Hi Madhes,
    How can i know the number of users using Crystal Reports
    and how many users are using Web I.

    Thank you !!!

    Reply

    • Hi Roan, you can try with below but this will give you number of users logged in a particular day.

      SELECT count(SI_NAME) FROM CI_SYSTEMOBJECTS WHERE SI_LASTLOGONTIME >’2016.03.16.00.00.01′ and SI_KIND=’Connection’

      Reply

      • Hi Madhes,
        Thank you for the response,
        i am getting the output as “SI_AGGREGATE_COUNT SI_NAME 1”
        can you please help me with these.

        Reply

      • Hello Sir,

        Good evening:)

        I’ve one requirement how to findout BEx query name for Analysis for Office reports.

        Kindly help me.

        Reply

  14. Hi Madness,
    I would like your help. I want to find the query which can give me the list of all BO users but also everybody who can log in due to active directory and what data sources (universes) they can access.
    Can you please help me?

    Reply

    • Hi,

      Please check the below query and update according to your requirement.

      To list all the Users – SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

      or Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS Where SI_PROGID = ‘CrystalEnterprise.USER’ Order By SI_NAME or Select SI_NAME, SI_USERFULLNAME, SI_ID From CI_SYSTEMOBJECTS Where SI_KIND = ‘User’ And SI_NAME != ‘Administrator’ And SI_NAME != ‘Guest’ Order By SI_NAME

      To list all the Universes – SELECT * FROM CI_APPOBJECTS WHERE SI_KIND=’UNIVERSE’

      Thanks
      Madhes DWBI

      Reply

      • Hi Madhes ,

        Thanks for sharing Query Builder Information . Could you please suggest me for the below scenario.

        I would like to get the list of Folder Names and Rights information for the given user group name using query builder .

        Example : For the example The UserGroup Name “Report Developers” having access for the different project folders.

        I need to get list of Folder Names , Rights information for the above usergroup “Report Developers”.

        And also the Query Builder out put is not excel friendly . Is there any approaches to get the out put as excel .

        Thanks & Regards

        Reply

        • To get all folders from the repository
          Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Folder’

          User rights
          SELECT SI_NAME from CI_SYSTEMOBJECTS where SI_NAMEDUSER=0 AND SI_KIND=’User’

          Reply

  15. Hello Madhes,

    is there a query to get users by their groups along with their principal for security purposes?

    i need 2 reports for auditing.

    users/user groups and prinicipal
    folder/ user groups and their acces rights

    Reply

  16. Hi Madhes,

    Thanks for sharing Query Builder Information . Could you please suggest me for the below scenario.

    I would like to get the list of Folder Names and Rights information for the given user group name using query builder .

    Example : For the example The UserGroup Name “Report Developers” having access for the different project folders.

    I need to get list of Folder Names , Rights information for the above usergroup “Report Developers”.

    And also the Query Builder out put is not excel friendly . Is there any approaches to get the out put as excel .

    Thanks & Regards

    Reply

  17. It came to our notice that few of the BO reports( that were present in the specified folder previously) are missing from the Repository. Through Query builder is there a way to investigate what has happened those missing report ?

    Reply

    • Hi Dinesh, I don’t think those missing reports information available in query builder tables unless until you have BO AUDIT repo setup in your system.

      Thanks
      MadhesDWBI

      Reply

  18. Hello Friends , i need query which retrieve all mail id information on which we send reports everyday , need to check successfully delivered and not delivered

    Reply

    • Hi,
      To find email Address for scheduled reports, you need to tweak the query little bit based on your requirement.
      SELECT TOP 4500 SI_NAME,SI_USERFULLNAME,SI_EMAIL_ADDRESS,SI_USERGROUPS FROM CI_SYSTEMOBJECTS
      WHERE SI_NAME IN (Sales,’Finance’)”)AND SI_KIND = ‘User’ORDER BY SI_NAME

      Successful instances
      select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO, SI_SCHEDULEINFO,SI_EMAIL_ADDRESS from CI_INFOOBJECTS where
      SI_SCHEDULEINFO.SI_OUTCOME=’2016.08.01′

      Failed instances
      select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME>=2 and SI_SCHEDULEINFO.SI_STARTTIME>=’2016.08.01′

      Reply

  19. Dear Madhes,

    Thanks a lot for this wonderful article.
    I need your help to know the query about finding the list of BO reports which have details about a specific company code. Can you please help?

    Regards
    Rishi

    Reply

  20. Hi Madhes,

    Please provide a query to find all the report names built on particular universe (UNX).

    I have tried the below query to acieve that, but no luck.

    SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
    Where PARENTS (“SI_NAME = ‘Webi-Universe’”, “SI_NAME = ‘Universe Name’”)

    Actually we use the below query to get the universe names of type .UNX, but I’m unable to get the names of the reports built on top of that.

    select * from ci_appobjects where si_kind=’DSL.Universe’ and SI_NAME = ‘Universename.unx’

    Thanks in advance.

    Reply

    • Hi Sivasai, please use the below query to get the list of reports from UNX universe

      Select si_name, si_sl_documnents from co_appobjects where si_kind=’DSL.MetaDataFile’

      Thanks
      MadhesDWBI

      Reply

  21. Hi,

    Can any body tell me the query for list of webi reports which are having propmts from public folder.

    Thanks,
    Sankar

    Reply

    • Here you go.. To find the list of Webi reports which are using prompts.

      SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS,

      SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES,

      SI_PROCESSINFO.SI_PROMPTS  FROM CI_INFOOBJECTS
      WHERE  SI_KIND = ‘WEBI’ and SI_INSTANCE = 0 and SI_PROCESSINFO.SI_HAS_PROMPTS=1

      Reply

  22. How do you export the results to something like a csv file? Or more importantly is there a way to query CI_ information externally?

    Reply

  23. Hi Madhes,

    i need to find when was the last time the report got refresh.. i had used the si_update_ts but that not correct,, can you give the query to find the last refresh date for the report.

    Reply

  24. Hi Madhes,

    i want to know what is the query for the last refresh date for the report. i had try with si_update_ts but that is not correct.

    Reply

  25. Hi Mahesh,
    1. How to get server details of BO 6.5 SP4?
    2. How to query system tables – CI_SYSTEMOBJECTS and CI_INFOOBJECTS in BO 6.5 SP4?
    3. Will Query Builder of SAP BO 3.1 work in BO 6.5 SP4?

    Thanks and Regards,
    Amit

    Reply

    • Hi Amit,

      First BO 6.5 and BO XI has different architecture and you cannot use same query builder in BO 6.5.

      Below are the list BO 6.5 repository tables.

      Security Domain tables 25

      1. OBJ_M_ACTOR
      2. OBJ_M_USRATTR
      3. OBJ_M_DOCCST
      4. OBJ_M_GENPAR
      5. OBJ_M_MAGICID
      6. OBJ_M_RESLINK
      7. OBJ_M_UNIVCST
      8. OBJ_M_UNIVSLC
      9. OBJ_M_ACTORDOC
      10. OBJ_M_DOCUMENTS
      11. OBJ_M_UNIVERSES
      12. OBJ_M_ACTORLINK
      13. OBJ_M_TIMESTAMP
      14.OBJ_M_UNIVDBCST
      15.OBJ_M_CONNECTION
      16.DS_USER_LIST
      17.OBJ_M_REPOSITORY
      18.OBJ_M_CATEG
      19.OBJ_M_CONNECTDATA
      20.OBJ_M_OBJSLICE
      21.OBJ_M_RESERVATION
      22.DS_PENDING_JOB
      23.OBJ_M_DOCCATEG
      24.OBJ_M_DOCAT
      25.OBJ_M_DOCATVAR

      Document Domain have 1 tables

      1.OBJ_X_DOCUMENTS

      Universe Domain have 31 tables

      1. UNV_JOIN
      2. UNV_AUDIT
      3. UNV_CLASS
      4. UNV_RELATIONS
      5. UNV_DIM_OBJ
      6. UNV_OBJECT
      7. UNV_OBJ_TAB
      8. UNV_TAB_OBJ
      9. UNV_CONTEXT
      10. UNV_CTX_JOIN
      11. UNV_TAB_PROP
      12. UNV_JOIN_DATA
      13. UNV_PROP_DATA
      14. UNV_PROPERTY
      15. UNV_UNIVERSE
      16. UNV_CLASS_DATA
      17. UNV_DIMENSION
      18. UNV_PROP_TAB
      19. UNV_OBJECT_DATA
      20. UNV_CONTEXT_DATA
      21. UNV_JOINCONTENT
      22. UNV_UNIVERSE_DATA
      23. UNV_OBJCONTENT
      24. UNV_TABLE
      25. UNV_TABLE_DATA
      26. UNV_OBJECT_KEY
      27. UNV_JOIN_OBJECT
      28. UNV_COLUMNS
      29. UNV_COLUMN_DATA
      30. UNV_OBJ_COLUMN
      31. UNV_X_UNIVERSES

      Reply

  26. Dear,
    Can you help me please. I tried to purge the reports from personal folder (User Folders) with the modification date greater then x days.

    share-cleaner.xml

    “O:\Program Files\BI4\SAP BusinessObjects Enterprise XI 4.0\win32_x86\jre\bin\java” -Xms64m -Xmx1024m -jar O:\BOXI4.0Config\MovePurge\BOXI_script_MovePurge\share-cleaner.jar -f O:\BOXI4.0Config\MovePurge\BOXI_script_MovePurge\share-cleaner.xml -p cleaner-user-folders

    Can someone please shed some light on this issue.

    System : SAP BusinessObjects BI Platform 4.1 Support Pack 5 Patch 8

    Kind regards,
    Fethi

    Reply

  29. Hi Madhes

    I need to know list of crystal reports that used specific field in the report. Is it possible to find in the query builder? or else to find list of crystal reports based on the connection.

    Reply

  30. Pingback: Query Builder in BO – Additional Queries | DWBI castle

  31. Hi Madhes, how to get the information from sub fields. For e.g. I wanted to retrieve SI_LOGON_INFO.SI_LOGON1.SI_CUSTOM_USER information for a specific crystal report which is coming when I enter the query as below
    SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’ AND SI_ID = 519765

    However, i want to retrieve only SI_NAME and SI_CUSTOM_USER for above report. Whatever the combination I try, 1 row is retrieved but nothing is displayed. Can you help?

    Reply

  32. Hi,

    I trying to query users of all groups in business objects.

    SELECT *
    FROM ci_systemobjects
    WHERE descendants(“si_name=’Usergroup-User'”, “si_name=””)
    The above query will return users of one group. But, I want users of all groups with group name in the below format.

    User_id group_name

    Thanks and Regards,

    Diwakar G

    Reply

  33. Hi,
    Could anyone help me on my request.
    I need to know Webi report Count and its name(report name) that been refreshed from January 1st,2019 to till now.
    Regards,
    Suprith

    Reply

  34. Hi , Madhes .
    Could you please help me how to get ” Number of reports in each user inbox level ” by using Query.

    Reply

    • Hi Narendra,

      Use below query to find Number of reports in each user inbox.

      SELECT SI_OWNER,SI_CHILDREN FROM CI_INFOOBJECTS WHERE SI_PARENTID=48

      Thanks
      Madhes

      Reply

      • Hi All,

        Could you please help me with the query for report name, report scheduled frequency, delivery method(email, ftp), delivery destination (email list, directory) for a particular folder.

        Thanks

        Reply

        • HI Puja,

          Get Scheduled report complete details (report name, Description, scheduled frequency and etc) for a particular user.

          SELECT * FROM CI_INFOOBJECTS WHERE SI_OWNER = ‘BOUSER1’ AND SI_RECURRING = 1

          Also please go through my post, you will get answer for your question.

          Reply

      • can you tell me the query for user list ,personnel folder reports list with size, I am trying with
        SELECT TOP 10000 SI_ID,SI_NAME, SI_PATH,SI_AUTHOR,SI_SIZE,SI_FILES FROM CI_INFOOBJECTS WHERE SI_KIND = ‘Webi’ – group by SI_NAME is not working and also I need to export the results to excel, I am running on BO4.3SP2 patch9

        Reply

  35. Hi Madhes,

    One question.. If we run some query in a query builder will that be executed in CMS database in backend or will it just fetch result from the InfoStore.

    Reply

  36. Hi Madhes,
    You seem to be very open in helping here.
    May I know how to query all user with their status:
    si_name | Si_enabled
    I tried below queries but seem to returning 0 result.
    SELECT TOP 5000 SI_NAME, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS
    WHERE SI_NAME NOT IN (‘ADMINISTRATOR’,’GUEST’) AND SI_KIND=’USER’
    AND SI_ALIASES.SI_ENABLED = ‘True’
    SELECT SI_NAME, SI_ALIASES.SI_ENABLED FROM CI_SYSTEMOBJECTS
    WHERE SI_NAME NOT IN (‘ADMINISTRATOR’,’GUEST’) AND SI_KIND=’USER’
    Your kind help is much appreciated.

    Reply

  38. can you tell me the query for user list ,personnel folder reports list with size, I am trying with
    SELECT TOP 10000 SI_ID,SI_NAME, SI_PATH,SI_AUTHOR,SI_SIZE,SI_FILES FROM CI_INFOOBJECTS WHERE SI_KIND = ‘Webi’ – group by SI_NAME is not working and also I need to export the results to excel, I am running on BO4.3SP2 patch9

    Reply

Leave a reply to Yusuf Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.