business objects query builder export to excel

The tool allows to restrict query tospecific object or specify parent folder. Please help. This is what the Query Builder application looks like in Business Objects and you can access it via this link: It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. Using Query builder one can easily query the BusinessObjects Does it run standalone, or is added to an existing tool? WebOver 8 years of Business Intelligency solutions with experience on all stages of software apply development life cycle.Strong experience into Developing BI reporting applications using Business Objects, Crystal Reports.Good Comprehension in Data Warehouses and Transactions database design and research, Data models, Business Intelligence In the Power Query Editor, do one of the following: To load to a worksheet, select Home > Close & Load > Close & Load. The above is an example of a query result. And you can use some other software to send the mail e.g. Edit a query from data in Excel worksheet. Some of the Query builder queries to explore the BusinessObjects repository. Queries with si_ancestor conditions can run for a long time. If you have some suggestions about functionality let me know: dmytro.bukhantsov at gmail.com I am trying to find the right SQL to return the SI_User field contents from the SI_Prompts of Crystal Reports. We provide a library of WebI documents in order to efficiently query the metadata it aggregates. Furthermore, Excel cant effectively use multithreaded execution. You can also use it to find differences between universes. Again, there is no documentation to help you and technical knowledge is still needed given mapping the InfoObjects to a universe can be challenging. SELECT TOP 10000 * FROM ci_systemobjects. SP3 on a Windows 2003 server. I want to extract the user security information of a folder or an universe to find out the parent level user rights which has rights to access it. Ihave 12.1.0 version CMC. CI_APPOBJECTS information about universes. Web Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel is there any specific requirement to make this tool working? Tableau Conference Agenda: The Top Sessions to Attend, UNV to UNX conversion in just 4 steps using 360Suite solutions. This is the place where Query Builder comes in to picture where in which this is the one and only door step through which we can query the metadata stored in the repository. 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. 1733964 - How to get list of events using Query Builder? So, with it, you can extract all instances, calculate the "execution time" creating a variable ad hoc in the report and obtain the max execution time you want. File is used by another process when Excel is not running in task manager.I am using Excel 2010, BO XI3.1. It is a technical tool, and to make queries against the SAP BusinessObjects repository, you need to have the technical knowledge because SAP doesnt provide any documentation or tutorials on how to create them in the tool, other than online blogs there is no help. 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, To extract all the report names from specific folder, SELECT SI_ID,SI_NAME,SI_PARENT_FOLDER,SI_FILES, FROM CI_INFOOBJECTS WHERE SI_KIND = WEBI AND SI_INSTANCE = 0 AND SI_ANCESTOR = [SI_ID OF THE FOLDER], To get Reports those are spanning multiple universes, SELECT SI_ID, SI_KIND, SI_NAME FROM CI_INFOOBJECTS WHERE SI_UNIVERSE.SI_TOTAL>1, To list all the events and corresponding event file location, SELECT SI_ID, SI_NAME, SI_FEATURES FROM CI_SYSTEMOBJECTS WHERE SI_KIND= Event, To list all Scheduled reports based on event, SELECT SI_NAME, SI_SCHEDULEINFO FROM CI_INFOOBJECTS, WHERE SI_RUNNABLE_OBJECT = 1 AND SI_SCHEDULEINFO.SI_DEPENDENCIES.SI_TOTAL > 0, SELECT SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER FROM CI_INFOOBJECTS WHERE SI_KIND = WEBI AND SI_CHILDREN = 0 AND SI_SCHEDULEINFO.SI_SCHED_NOW = 0, To get the list of all reports scheduled daily excluding Paused. Privacy | Creating a report consists of two primary tasks; the first task is to create the underlying data model and the next is to define the visual layout that displays the data. With InfoSteward, we use an instance of IPS_CMS and you can query it with the same tools as a BOBJ CMS. Dont let the limitations of Query Builder stop you from going further. Power Query offers several ways to create and loadPower queries into your workbook. The simplest task such as a creation of list of reports might be tedious because of the structure of the result. There isnt an "execution time" field. WebBuilding queries Feature Web Intelligence HTML Web Intelligence App let Web Intelli gence Rich Client Build queries on an Analysis View data source No Yes Yes Build queries on Excel files saved locally No No Yes Build queries on Excel files saved to the CMS * Yes Yes Yes Build queries on SAP HANA views Yes Yes In Connected mode only. I want to know the query for the query builder. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. In the Query Options dialog box, on the left side, under theGLOBALsection, select Data Load. To export the results to a .csv file, complete the following: Click Query Results. I am attempting to isolate Crystal Report objects where the SI_LOGON_MODE 1. Rename worksheet tabs Its a good idea to rename worksheet tabs in a meaningful way, especially if you have a lot of them. I, How to re-point Webi reports converted from Deski to a Freehand SQL connection In our post Taking full advantage of, When it comes to Business Objects risk management or Business Objects regulations needs it is a black box due to. Business Objects In Where can I find what fields are in the tables. You can either create a query from imported data or create a blank query. From the menu, select the Export to CSV option. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. 2) Install the tools to completly new PC. With 360Suite we can help you explore your whole content in a way that allows you to interact with it, providing you with a bigger picture of your content landscape. At the bottom of the Power Pivot window, select the worksheet tab of the table you want. There are two ways to do this. Thanks so much for this. Query Builder - Business Intelligence (BusinessObjects) Webdownload, query builder, excel, csv, cms metadata, tool, bi, cms, export, excel format, Admin tools , KBA , BI-BIP-CMC , Central Management Console (CMC) , How To About The Query Builder tool, enables you to query the full CMS database and return meaningful results, d-encrypting its contents. your help is greatly appreciated. The SAP Note/KBA you were trying to reach has been removed. Please close Excel and try again. The file should not be open in Excel, otherwise the program will not be able to write into it. This is the most common way to create a query. For example, rename Sheet1 to DataTable and Table1 to QueryTable. The default behavior is to detect them. (Sort of a tutorial) Building a Query on the universe using WebI. It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. A wrong connection is made to @@server:6400(server:6400). 1. For example, you have to find a folder ID, before querying the list of objects in the folder. The only limitation i found out was that it doesnt capture the detailed or the Sub-Items of a query output FOr example Im 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. Query Builder is one of the essential and interesting tools in BusinessObjects. SI_SCHEDULE_INTERVAL_MONTHS, WHERE SI_SCHEDULE_STATUS !=8 AND SI_RECURRING = 1, To get the list of reports scheduled by a particular user, WHERE SI_OWNER = AND SI_RECURRING = 1, 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), To Show all universes using a specific connection, SELECT SI_ID, SI_NAME, SI_OWNER FROM CI_APPOBJECTS, WHERE CHILDREN(SI_NAME=DATACONNECTION-UNIVERSE , SI_NAME=TEST'), To list all Webi reports that uses the connection (multiple universes), SELECT * FROM CI_APPOBJECTS, CI_INFOOBJECTS WHERE PARENTS(SI_NAME=WEBI-UNIVERSE', CHILDREN(SI_NAME=DATACONNECTION-UNIVERSE , SI_NAME=TEST )) AND SI_KIND=WEBI, SELECT SI_NAME,SI_GROUP_MEMBERS FROM CI_SYSTEMOBJECTS, WHERE SI_KIND = USERGROUP AND SI_NAME=ADMINISTRATORS, To extract all the users from specific user group, SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS(SI_NAME=USERGROUP-USER', SI_NAME=ADMINISTRATORS'). Please suggest. Just found this and unzipped onto my machine. . The report works so far and I managed to add the logo. Hello All, We have been play around with the Query Builder a bit and noticed an oddity I guess. This only occurs when you load the data to a worksheet orData Modelfrom Power Query. Local Machine: Win 7. Yes. If you have multiple accounts, use the Consolidation Tool to merge your content. 5 Must have tools for every BOBJ administrator You can setup BO auditing, this will give you precise information who is running what. query builder, cms, query, csv, export, tool, bi, bi platform, server, cms metadata , KBA , BI-BIP-SRV , CMS / Auditing issues (excl. The system cannot find the file specified. Thanks a lot for this great article, I wondering if you could help me. When finished making changes in the Power Query Editor, select File > Close & Load. Its always good practice to change the default names of worksheet tabs to names that make more sense to you. You can export the contents of your report to a new or existing Microsoft Excel workbook, and then build charts and Under Type Detection, select or clear Detect column types and headers for unstructured sources. Hi Yamini,from Query Builder, you can only extract start and end time of any instance. On BO 4, I find that the following SQL runs fine. You may want to just start from scratch. You can change the limit using option TOP: e.g. In the File Format box, select the file format that you want. If you have multiple accounts, use the Consolidation Tool to merge your content. Which users having access to which reports. This will be handy for those who searching for Query Builder Queries. query Step 3: Under Metadata panel you will see a data model which contains tables, columns and measures. You will require the SDK to retrieve dimensions or measures used by a report as that information won't be stored in the CMS database but inside the report. Implementing a third-party tool such as 360Suite will give you complimentary access to not only the same data as Query Builder (the System Database) but to both the Auditor and the FRS (file repository server), with the possibility to leverage this data by carrying out impact analyses or analyzing the usage and non-usage of objects within your environment. FROM CI_SYSTEMOBJECTS WHERE SI_KIND= Event, SI_SCHEDULEINFO.SI_DEPENDENCIES.SI_TOTAL > 0, SELECT SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER, SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS, SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS. I'm sorry, I have not found how you can find the information that is included when you create a user in the cmc. This might remove relationships created manually after the data was imported or introduce new relationships. system cannot find the file specified. Unable to connect to CMC server:6400. Once again Thank you and Best regards, This means that response will come faster, and no unnecessary resources will be consumed by CMS. Tip Sometimes the Load To command is dimmed or disabled. Terms of use | WHERE SI_KIND = WEBI AND SI_INSTANCE = 0 AND SI_ANCESTOR = [SI_ID OF THE FOLDER]. You should go with SI_PARENTID instead if you are interested only in documents. It appears to be exactly what i am looking for1. Login to Query Builder with Administrator account using the following link: http://servername:portnumber/AdminTools, 2. l@Mh/j. error. Do you wanna try to compile it for your system? !:FDtF)YM,D2?o?!$C EP;sj{B%*!} Check Biclever CMS Query Builder as alternative. With the graphical interface, users can create requests by using predefined objects and filters, rather than having to type in the technical terms. I even restarted my PC and tried fresh but no luck !! For more information, see Import data from external data sources. The tool is complied for BO XI 3.1 SP3 FP3.2. query The step-by-step wizard is too simplistic. The majority of the tables that make up the 'CMS Database' can not be queried using SQL directly against the database, since their content is held in a proprietary encrypted format. CrystalDecisions.Enterprise.Framework.dll Objects that do have an SI_FILES property but the number of files is zero (SI_NUM_FILES) will not be returned, All objects that have an SI_FILES property but the filesize is 0, List all plugins that have a least 2 icons associated with them, select * from CI_SYSTEMOBJECTS where SI_PLUGIN_OBJECT=1 and SI_PLUGIN_ICON_2_A is not null, Web Intelligence documents ordered by name, select SI_NAME,SI_ID from ci_infoobjects where si_kind='Webi' order by SI_NAME asc, Web Intelligence documents ordered by size (largest first), select SI_NAME,SI_ID from ci_infoobjects where si_kind='Webi' order by SI_SIZE desc, Note that even though SI_SIZE is not requested, only in the ordering, it will bring the parameter into the results set, select * FROM CI_APPOBJECTS WHERE SI_KIND='Universe' order by SI_NAME asc, selec * FROM CI_APPOBJECTS WHERE SI_KIND='DSL.Universe' order by SI_NAME asc, select * from CI_SYSTEMOBJECTS where SI_PLUGIN_OBJECT=1 and SI_PLUGIN_ICON_1_A is null, Lists information on the Install Node and EnterpriseNode (SIA). You need to have SAP BusinessObjects 3.1/4.x Client Tools on your machine. CMS Query Builder for BO 4.x (cmsquerybuilder-bo4x-1.5-20190305.zip) Under Relationships, select or clear Update relationships when refreshing queries loaded to the Data Model. Open Power BI on your computer. InfoStore Query Builder to Check Relations. thanks for sharing. its with enterprise id. After this execute the below query to get the list of all universes for which that user has "Edit" rightsselect si_name from ci_appobjects where si_kind = 'Universe' and IsAllowed("SI_ID of the User from the first query",6). As suggested earlier, it wold be great to walk me through troubleshooting the behavior. I am specifically looking for data from SI_SCHEDULEINFO.SI_DESTINATIONS area and SI_LOGON_INFO area, I do get results but just not the scheduling and processing section data such as SI_LOGON_INFO and SI_DESTINATIONS info, I was using it some time, but from today I started to get following error message: Once you submit your query, results are shown on a web pagebut thats about as far as it goes. Terms of use | For example: To access this information, 360Suite offers two modules: 360View and 360Eyes. Microsoft is aware of this problem and it is under investigation. Export 2. Thank you for your solution for so many users like me. You can use the Query Builder only to retrieve universe and connections, prompts, etc. Export Below are the BusinessObjects metadata virtual tables and their associated objects. Is it possible to automate the execution of Infostore query builder with CMS details user name,Pwd with query. At this point, you can manually add steps and formulas if you know the Power Query M formula languagewell. Yes. So far Ive only used it to export a list of users with name, email and last logon time, to Excel. Is it possible to query reports utilizing a specific element from a specific universe? The query uses objects from two different levels Level 0 and Level 1. CI_INFOOBJECTS information about all kinds of documents (Webi, Crystall Reports, Txt, Doc etc.). Great article !!! You can suggest a query to get the group, universes, and folders. Is there anything I should change. Querying the relationships between different objects of the tables requires specific Query Builder knowledge. I use the odbc connector on my pc with ms access to create queries and then publish them on a switchboard. Please help. Im not a coder so i cannot modify your source code. To extract all the report names from specific folder". Visit SAP Support Portal's SAP Notes and KBA Search. si_parentid=23 is for the Public folders. For example these scripts you've provided, don't give an actual name. You load a query to a Data Model by using the Load To command to display the Import Data dialog box, and then selecting the Add this data to the Data Model check box. how to get the Data security profile name from AdminTools query. Query Builder: How to export query results to a .csv file To preview the base data returned by a query, click the Refresh button: The combination of a document ID and Actually I am trying to get the list of report under a folder and it subfolder but this works only at one level. Could you please help to find out that info using query builder or cms database. You can find the list querying CMS: SELECT si_name FROM ci_systemobjects WHERE si_relation_table_name='RELATIONS' Now you can also build such queries and export result to to Excel in InfoStore Query Builder: BO XI 3.1 executable of InfoStore Query Builder (zip) Source code of InfoStore Query Builder I am logging in with an administrator account so it is not a question of grants. Neither the request nor the results can be stored. With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. How Can You Overcome The Limitations Of Query Builder? We have also been toying with this: https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. They are in the folder: [SAP BusinessObjects]\SAP BusinessObjects Enterprise XI 4.0\win64_x64\dotnet\iPoint, The libraries: To close the Power Pivot window, select File > Close. Edit a query from the Query Properties dialog box. To edit a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. Click Edit Data Provider on the toolbar. When refreshing queries already loaded to the Data Model, Power Query finds existing relationships between tables such as foreign keys in a relational database and updates them. Windows server 2003 Enterprise X64 edition, SPack 2. I am running it on BI 4.0 SP4 FP20 and it works fine, but Tip To tell if data ina worksheetis shaped by Power Query, select a cell of data, and if the Query context ribbon tab appears, then the data was loaded from Power Query. The default behavior is to download data previews in the background. When I search using your query, I may get the"SI_NAME" which may be equal to 12345 but what about the rest of the metadata or the John Doe? Otherwise you can try the command line one https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/ You can point it to the libraries of your BO. I need to do this, as the BO4 REST service to retrieve this information takes 2-4 minutes to run, even with "lovInfo=false" at the end of the URL: http://YOUR_SERVER_NAME:6405/biprws/raylight/v1/documents/11729/parameters?lovInfo=false. Yes. 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. Visual BI Solutions Just wondering if this has got a dependency on .Net framework. it was not working for me as well, but just corrected some formatting like single codes, semi colon, equalto sign, etc and then it worked. I apologies, I obviously didn't look hard enough. AdminTools Session should be opened with Administrator account if possible, since this account has faster security rights check than with standard account (even compared to a user part of Administrators group). There is no keyword to remove duplicate in CMS query syntax, since by design CMS cannot return an infoobject more than once for a query. Format the cells and add formulas as per your requirements and then save the file. Thanks a lot How can I figure out which Business Element is mapped to which DB Object? You also need to know a language that resembles SQL without it, youll be a little bit stuck! system cannot find the file specified. WebOn the External Data tab, in the Export group, click Excel. This provides the Principal and Objects(Folder) to which the access is enabled. The link exists but the document in the FRS no longer exists or. Exporting Report Builder query results to spreadsheets - IBM This can occur the first time you create a query in a workbook. 1735539 - Query to list all Universes for which a user has "Edit Objects" rights via Query Builder. The tool worked very well on the first day. Let me know if this resolves the issue. Before loading to the Data Model, the default behavior is to find existing relationships between tables, such as foreign keys in arelational database and import them with the data. It connects to the server using BO API to query infostore objects. when I am trying to login by opening the exe, for both the versions its giving the error Unable to logon. By the way, it would be better to specify another filter on an indexed column (like SI_NAME, SI_KIND, SI_CREATION_TIME ) to avoid unnecessary work by CMS. SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS. Using Query builder one can easily query the BusinessObjects repository and get the required information which cannot be found even in CMC. Even then appreciate your solution and sharing with all of us. For the right ID refer to the section, List of all plug-ins installed into the CMS repository, select * from ci_systemobjects where si_plugin_object=1, List of all applications installed in to the CMS repository, select * from ci_appobjects where si_parentid=99. WebOver 8 years of Business Intelligency solutions with experience on all stages of software apply development life cycle.Strong experience into Developing BI reporting applications using Business Objects, Crystal Reports.Good Comprehension in Data Warehouses and Transactions database design and research, Data models, Business Intelligence Need to download Query Builder results into an Excel format so that the end users can review it. Microsoft Access DB Format How can you make sure that your Business Objects reports show accurate information? Can you suggest a query to get the successful instance countwithin specific folder and for specific date. Assuming your query is valid and has no errors, you can load it back to a worksheet or Data Model. Object Hi Amir, here: bukhantsov.org/tools/QueryBuilder_src.zip. The familiar Excel worksheet , ribbon, and grid, The Power Query Editor ribbon and data preview. Given a particular group name (replace MyGroupNameHere accordingly) this query will return ALL the users in the group and any sub groups. It would be great to have their paths as well (folder location). Remove all references to the libraries and reattach them. (Here limited to only the Public folders), select si_name from ci_infoobjects where si_parentid=23 and IsAllowed(,), GroupID (or UserID) is accessible within the CMC properties. Is it possible to get report level data like dimensions and the report query generated for a report from the repository ?Thanks !! Ive been using this great tool for some time but ive just seen the error. Shame I can not get this running. Not sure if this helps: I am using Excel 2010, Window 7 and BO XI3.1. The default limit for a CMS query is 1000. Version-12.0.1100.0, Culture-neutral, Furthermore, the connected data that you see in an Excel worksheet, may or may not have Power Query working behind the scenes to shape the data. By adding the query filter Ancestor = 18, we get the all the objects that only belong to Users personal folder. I luv this tool, it is running fine on BO 3.1, no issues period. Copyright | By default, Power Query loads queries to a new worksheet when loading a single query, and loads multiple queries at the same time to the DataModel.

Stave 4 A Christmas Carol Annotations, Chape D'attelage Pour Micro Tracteur Iseki, When Is The Person County Sheriff Election, Articles B