InfoStore Query Builder (with export to Excel)

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


BusinessObjects Query Builder

With BusinessObjects Query Builder:

  • you cannot actually build a query, you have to write the query manually. The step-by-step wizard is too simplistic.
  • you have to remember the table names and the object kinds.
  • you usually have to write several queries to get the required result. For example, you have to find a folder ID, before querying the list of objects in the folder.
  • The simplest task such as a creation of list of reports might be tedious  because of the structure of the result. You have to copy-paste each individual name in your list.

How to use

You can build queries for three tables:

  • CI_INFOOBJECTS – information about all kinds of documents (Webi, Crystall Reports, Txt, Doc etc.)
  • CI_APPOBJECTS – information about universes.
  • CI_SYSTEMOBJECTS – information about servers, connections, users.

The tool allows to restrict query to specific object or specify parent folder.

You can view results in the BO Query Builder by clicking “Open in Query Builder”, or export the result to Excel file.

Export parameters

Containers – export containers on a separate worksheet. Container is a hierarchical property. E.g. property SI_FILES contains properties SI_NUM_FILES, SI_FILE1, SI_FILE2 etc.

Transposed – sometimes it is convenient to see the result in the transposed form, for example when you select one object.

Installation

The tool has been desupported. Check Biclever CMS Query Builder as alternative.

See also

InfoStore Query Builder to Check Relations

84 thoughts on “InfoStore Query Builder (with export to Excel)”

  1. jayantsb

    hi, this looks interesting but it did not work on my laptop with windows XP – SP2. is there any specific requirement to make this tool working?

    Like

    1. dmytro Post author

      The tool is complied for BO XI 3.1 SP3 FP3.2. It might not work for other versions. Do you wanna try to compile it for your system? I will send you source code.

      Like

  2. amir

    Hi,

    I luv this tool, it is running fine on BO 3.1, no issues… period.
    Can you send the code or the complete solution with source code please ?

    Thank you,
    amir

    Like

  3. terrellwsmith0747

    I am also interested in this tool. It appears to be exactly what i am looking for1. I need more information on how to use this tool. Does it run standalone, or is added to an existing tool? How does it interface with the server?

    thank so much!

    Terrell Smith

    Like

    1. dmytro Post author

      > Does it run standalone?
      Yes. Also it does not have installer, you just unpack the tool in a preferred location and create a shortcut.

      > How does it interface with the server?
      It connects to the server using BO API to query infostore objects.

      If you have some suggestions about functionality – let me know: dmytro.bukhantsov at gmail.com
      thx:)

      Like

  5. Anoop

    Excellent tool mate !! thanks for sharing. Just wondering if this has got a dependency on .Net framework. I am getting “System.IO.FileNotFoundException: Could not load file or assembly ‘WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified.
    File name: ‘WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35′” error in one of the platforms which uses .NET framework 1.1

    Like

  6. SAAM

    Great Tool. One issue is I am not able to see folders under root folder in our environment, not sure if there are any CMS setting I have to change for this tool to work. Works fine on test server, able to see all the folders/subfolders under root folder

    Thanks
    Sachin

    Like

    1. Digvijoyee

      Its because you don’t have rights to view the folders in the environment you are looikng in (viz. Prod environment). You have access in test hence you have no issues.

      Like

    1. dmytro Post author

      No. The source code is available, you can try to compile for R2. Let me know if you need help with this.

      Like

  8. Arseny

    Hi dmytro,

    Great tool! I tried to use it on a BO BI 4.0 SP02 system, however, the output Excel file contains maximum 1000 rows although there are more files in the system.
    Is there some kind of a limit on the maximum number of returned rows in the tool?

    Like

    1. dmytro Post author

      Thank you, Arseny

      The default limit for a CMS query is 1000. You can change the limit using option TOP:

      e.g. SELECT TOP 10000 * FROM ci_systemobjects

      Like

    1. wasy

      when i try i am getting the following error:

      Could not load file or assembly ‘CrystalDecisions.Enterprise.Framework,
      Version-12.0.1100.0, Culture-neutral,
      PublicKeyToken-692fbea5521e1304’ or one of its dependencies. The
      system cannot find the file specified.

      appreciate.
      wasy

      Like

      1. dmytro Post author

        Remove all references to the libraries and reattach them. They are in the folder:

        [SAP BusinessObjects]\SAP BusinessObjects Enterprise XI 4.0\win64_x64\dotnet\iPoint

        The libraries:
        CrystalDecisions.Enterprise.Framework.dll
        CrystalDecisions.Enterprise.InfoStore.dll

        Like

      2. wasy

        i am extremely sorry for the trouble, but, could you please tell me how to “Remove all references to the libraries and reattach them”, i tried but, probably not the way it is suppose to be.

        i was able to find the files in the following location: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\dotnet\iPoint

        really appreciate your help.

        Like

  10. wasy

    HI,

    Really apreciate if you could help me figure it out how to troubleshoot and run the superbly awesome tool in my environment.

    Local Machine: Win 7.
    BI4.0: Win server 2008.

    Gettinr error:

    Could not load file or assembly ‘CrystalDecisions.Enterprise.Framework,
    Version-12.0.1100.0, Culture-neutral,
    PublicKeyToken-692fbea5521e1304′ or one of its dependencies. The
    system cannot find the file specified.

    As suggested earlier, it wold be great to walk me through troubleshooting the behavior.

    Reallt really appreciate your response on this.

    Thanks,
    Wasy.

    Like

  11. wasy

    HI Dmytro,

    Tool works like a charm :), i am still in process of fixing the dll’s, currently exporting it to excel works just fine, i still need to learn a little bit more about queriying also, need to go through user manual (if any, for this tool), other than that, i am glad to have such a nice tool handy.

    Thank you so much and really really appreciate your efforts.

    I guess, i will be asking couple more questions about the operational side of this tool and some queries as well in the near future.

    Once again Thank you and Best regards,
    Wasy.

    Like

  12. PGour

    Hello Dmytro, thank very much for this valuable tool, it’s a pleasure to manipulate Infostore data in Excel, thanks to you !

    Like

  13. wasy

    HI Dmytro,

    Once again thank you so much for yoru efforts in providing such handy tools.

    Need your help:
    Is is possible to query and find a particular universe object used in any reports.

    or

    To query and extract all the objects used in a report.

    It would be great to have their paths as well (folder location).

    Your help in this matter will be highly appreciated.

    Wasy

    Like

  14. Lorena

    Hi Dmytro,

    This is a great tool ! I am running it on BI 4.0 SP4 FP20 and it works fine, but
    I noticed some of the folders under Root are not appearing. I am logging in with an administrator account so it is not a question of grants…

    Any idea what it might be ?

    Thank you,

    Lorena

    Like

    1. dmytro Post author

      Hi Lorena
      I checked the algorithm – looks correct..

      Would it be possible for you to send me (dmytro.bukhantsov at gmail.com) the result of the following query:

      select SI_NAME, SI_KIND, SI_CUID, SI_PARENT_CUID, SI_ID, SI_PARENTID 
from CI_INFOOBJECTS where SI_KIND like '%Folder'

      (SI_NAME is optional)
      If you know the name or cuid of the missing folder – that would be helpful.

      Thank you

      Like

    1. dmytro Post author

      The file should not be open in Excel, otherwise the program will not be able to write into it. Please close Excel and try again.

      Like

      1. Jenny

        It is great tool, love it. However I run into the same error:Could not save file, file is used by another process. I don’t have Excel open, and double checked task mgr too. Not sure if this helps: I am using Excel 2010, Window 7 and BO XI3.1.

        Like

      2. tilak

        Hi Dmytro,

        The tool worked very well on the first day. But now when i tried to extract the information into Excel, even though there is not Excel file opened it gives an error message “File is used by another process”.

        What do you think could be a possible reason. I even restarted my PC and tried fresh but no luck !! Please help..

        Like

      3. Maggie

        Encounter the same issue “File is used by another process” when Excel is not running in task manager.I am using Excel 2010, Window 7 and BO XI3.1.
        Please suggest.

        Like

      4. FX

        Is there a solution to this?
        I have encountered the same issue.
        “File is used by another process” when Excel is not running in task manager.I am using Excel 2010, BO XI3.1. SP3 on a Windows 2003 server.

        In which location would the excel be saved?

        Please advice.

        Like

    1. dmytro Post author

      This information is not stored in CMS.

      You can setup BO auditing, this will give you precise information who is running what.

      Like

  18. Jay

    Please let me know the code for getting folders, Objects, Objects properties like definition, sql etsc.. of a universe

    Thanks

    Like

  19. Ankur

    Hi,

    Thanks so much for the tool, I have been using it since quite a while and find it very useful. The only limitation i found out was that it doesnt capture the detailed or the Sub-Items of a query output… FOr example I’m creating list of failed reports with error message and Name… The code captures the SI_ID, SI_NAME however it fails to capture the error message that is the “SI_STATUSINFO.SI_SUBST_STRINGS.2”

    I’m not a coder so i cannot modify your source code. If you could include the same in this query builder, it would be of great great help!!

    Cheers!
    Ankur

    Like

  20. Lee

    Hi,

    Thanks so much for the tool it is very useful ( this BO ver: XI 3.1+SP3+FP3.4). Can i have a link for download the verison is works fine with Product Version: 12.3.0.601 (XI 3.1 SP3)?

    Many Thanks for you.

    Lee

    Like

  21. Kim

    The default connection is using port 8080 we use 9100. Is there a place where I can change this? Thanks a bunch for this product It appears to be super cool.

    Like

    1. dmytro Post author

      This is a quite tricky. I do not know which tool can do that.

      It is easy to extract SQL queries from a report using BO SDK. But then it needs to be parsed to extract names of the tables and fields and this is not easy since a full SQL syntax analysis is required.

      Like

      1. Jachin

        Check this out:

        http://www.reportminer.com/products/rptminersuite.shtml

        Extract generates configurable professional-quality report definition documentation (tables and columns used) for Crystal Report files. Extract analyzes each selected Crystal Report file and can generate documentation on the report definition in the following formats:

        Microsoft Word Format
        Microsoft Access DB Format
        Text Format

        Like

  23. Darren

    Hi Dymytri
    I’ve been using this great tool for some time but i’ve just seen the error

    An Error occured in sending the commanf to the application.

    Query is fine in Query builder, suspect its a security restrciyion in Excel preventing it being called from your app. Not sure where that is in Excel.

    Anay help greatly appreciated.

    Thanks

    Like

    1. dmytro Post author

      Do you see Excel file on disk? (Creation of the file should not require interaction with Excel application)

      Like

  26. Masood

    Hi,

    When I am trying to connect, its throwing an error after etering the credentials.” Unable to logon. Could not connect to the server”.
    Please help

    Like

      1. Masood

        Hi Dmytro..

        For 3.1 SP5 patch 9
        When I am trying to login to the query builder tool from the server itself. After entering the query and clicking on export to excel its giving a message that ” no application is associated with the specified file for this operation but when I am trying to open it from a diferent server its allowing to login and get the result of the previous server where its giving error.

        For 4.0 SP4 Patch 6
        on opening the exe its giving me error ” could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2, Culture = nuetral…

        I have also installed clients for both the version on a single machine…
        when I am trying to login by opening the exe, for both the versions its giving the error “Unable to logon. Could not connect to the server. its with enterprise id.

        Please advise.

        Regards
        Masood

        Like

  27. DBourlet

    Hello !
    Thanks for this SO USEFUL tool ;o)
    but, I have an issue, using with BO XI R2 SP5… while trying to login I have the message box “could not login” and nothing more… My credential are OK and tested on the CMC webpage… thanks for your help !
    Regards.

    Like

  28. Berend

    Am getting the same error (could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2 [etc]).

    I have BI4 client tools running and the EnterpriseFramework.dll has version 14.0.5.882. But this can change as soon as we roll out a new servicepack. Can this not be made level unaware?

    Also tried on a server with platform files (BI4 SP05) and on a BI4.1 server. Same result.

    Shame I can not get this running. looks cool.

    Like

  29. Faisal

    Thanks a lot for this great article, I wondering if you could help me.

    I am looking for a query to get information of most accessed reports with report folder excluding shortcuts and reports instances.

    Also a query to get information on who uses a particular report.

    Thanks in advance for your effort.

    Like

  30. Haytham

    Thanks a lot for this application , but when I try to extract to Excel file I got an error Windows message “Could not save Excel file” / “File is used by another process” is there any solution

    Like

      1. Himanshu

        I am also getting the same error “File is in used by another process”. And I have checked no excel sheet or excel process running on my machine. Please help. Thanks!!

        Like

    2. patrick delage

      I also get this strange message. I try this without success

      1) Restart server
      2) Install the tools to completly new PC

      It like something in the CMS or BO server ?

      Strange

      Like

  31. BrigParadise

    Could not install querybuilder:

    I am getting the error:
    Could not load file or assembly ‘CrystalDecisions.Enterprise.Framework,Version=14.0.2000.0, Culture=Neutral, PublicKeyToken=692fbea5521e1304 or one of its dependencies. The system cannot find the file specified

    Do I need the BI Client tools on the desktop that I want to install QueryBuilder

    Like

  33. Eric

    I am attempting to isolate Crystal Report objects where the SI_LOGON_MODE 1. All my CR Objects imported had the setting where SI_LOGON_MODE = 1. On import some have been “flipped” to SI_LOGON_MODE = 0. I would like to quicly identify any others. When I run SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’ I am able to find the SI_LOGON_MODE under Processing Info>SI_LOGON_INFO>SI_LOGON1. But, when I do any query with SI_LOGON_MODE in the WHERE clause I get zero (0) results. Even when I search SI_LOGON_MODE=1, where I know I should have hundreds of matches. I’m guessing my query structure is not correct becuase the SI_LOGON_MODE is sever “levels” deep in the Processing Info. Any suggestions? Thank you.

    Like

    1. dmytro Post author

      Hi Eric. Try something like:

      select * from CI_INFOOBJECTS 
where SI_PROCESSINFO.SI_LOGON_INFO.SI_LOGON1.SI_LOGON_MODE=0

      Like

  35. Alberto Rico

    Ihave 12.1.0 version CMC. Windows server 2003 Enterprise X64 edition, SPack 2. I got the error error (could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2 ….

    What Can I do to solve the problem?

    Like

  36. Antony Pallupetta

    Hi,

    Firstly, great piece of code!! Congrats! 🙂

    I have been using Query Builder only for a very short while. There are a few doubts I wanted to ask and you seem to be an expert on the subject.

    1 • Where do I get what different column names in these tables mean, except for the obvious ones of course? (Sort of a tutorial)
    2 • Is there absolutely no way to get which DB objects are used in a Crystal Report (I know this question has already been answered before, but just want to confirm)
    3 • And finally, I work mostly with Business Views. How can I figure out which Business Element is mapped to which DB Object? I am only able to find which Data Foundation / Business View it is connected to.

    I realize that this is a lot to ask, but I have been trying to figure this out myself but without any luck! Any help is greatly appreciated.

    Thanks In Advance,
    Antony

    Like

  37. Venkkav

    Really an Excellent Tool! Is it possible to get the folder and all the sub folder under it. Actually I am trying to get the list of report under a folder and it subfolder but this works only at one level. Is there anything I should change.

    Like

  39. Kumar

    Hi Dmytro,

    Great Work.

    Is it possible to automate the execution of Infostore query builder with CMS details user name,Pwd with query.

    Say for example Schedule the Query result in excel format to some email account weekly once.

    UK

    Like

  40. Erika

    A very good handy tool to query metadata results. Thank you for your solution for so many users like me. I am really curious to know if there is a way to query object SI_DESTINATIONS because it doesn’t return values when i mention this in select objects. I guess sub items of a query are not returned. Even then appreciate your solution and sharing with all of us.

    Like

  41. Kenneth

    Hi, I am trying to run the following query, however, the query only returns SI_DESCRIPTION, SI_NAME, SI_ENT_USERFULLNAME, and SI_LASTLOGONTIME. Several fields are missing in the results. Any suggestions?

    SELECT SI_NAME, SI_ENT_USERFULLNAME, SI_DESCRIPTION, SI_EMAILADDRESS, SI_DISABLED, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

    Best Regards,
    Ken

    Like

  42. Michelle

    I am new to this product and learning as I go. This tool could help me significantly.

    Is there a compiled version for 12.3.0.601?

    Thank you!!

    Like

  43. Shweta

    My cmc port is 8081,by default it take 8080 port and hence it is not working for me. Can you please suggest where to update the port number so as to get the desired information from cmc?

    Like

  44. Dan Coffman

    In using the product for 3.1, in using the query SELECT *
    FROM ci_infoobjects, ci_appobjects, ci_systemobjects where si_recurring=1 and si_runnable_object=1 but the results for scheduling and processing do not show up. I am specifically looking for data from SI_SCHEDULEINFO.SI_DESTINATIONS area and SI_LOGON_INFO area

    Like

    1. Dan Coffman

      I do get results but just not the scheduling and processing section data such as SI_LOGON_INFO and SI_DESTINATIONS info

      Like

  45. Butchi

    Hello Dmytro,

    Thank you very much for this great tool!

    I was using it some time, but from today I started to get following error message:
    “Unable to connect to CMC server:6400. A wrong connection is made to @@server:6400(server:6400). Logon cannot continue. ”

    version of BO is 4.1 SP2.

    Thank you for response.
    Butchi

    Like

  46. Hic Schaff

    Just found this and unzipped onto my machine. Thank you! So far so good on our BI4.0 SP6 installation. Thanks so much for this. So far I’ve ‘only’ used it to export a list of users with name, email and last logon time, to Excel. I say ‘only’ because… well you know the pain of doing this through Query Builder. I’ll be exploring further.

    May you have 10,000 happy days for sharing.

    Like

  47. Robert

    I have tried to use it to export to Excel. It gives me a message that it could not save file – “File is used by another process.”

    Like

    1. Robert

      I loaded it onto my computer, as opposed to just running it from this site, and the error went away. This application works fine!

      Like

  48. Arpit

    Hi,

    On daily basis I monitor Web intelligence reports whose status is ‘success’ in CMC Instance manager and then I have to calculate the time taken by each report from a column giving information about report ‘refresh time’ and then I have to apply some maths in it manually(using calculator). As the number of reports are increasing day by day this task is becoming hectic for me, so i was thinking if I can view this data from Query builder but the data comes from any folder in Business Objects. And is there any way for exporting this data?

    Any suggestion will be appreciated.

    Thanks & Regards
    Arpit

    Like

Comments are closed.