The query in the screenshot below will list the objects in the Favorites folder for all users. I am only able to find which Data Foundation / Business View it is connected to. Any suggestions? Export data to Excel - Microsoft Support If using a 3rd Party Web Application Server, or you are manually deploying Web Applications, then you will need to deploy the Web Application 'AdminTools'. its with enterprise id. Anyone have a query for getting a list of scheduled recurring jobs for INFOSTEWARD? Very nice blog and you have explained everything on query builder. This provides the Principal and Objects(Folder) to which the access is enabled. export data in Power Automate to an Excel File InfoStore Query Builder (with export to Excel) | bukhantsov.org I have tried to use it to export to Excel. Web Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel Excel Terms of use | List all the Public folders(including Subfolders) and which UserGroups has access to those folders/subfolders. Query is fine in Query builder, suspect its a security restrciyion in Excel preventing it being called from your app. select * from CI_APPOBJECTS where SI_ID =<<-->>, https://blogs.sap.com/2013/07/26/businessobjects-query-builder-whats-new-in-bi-40/. As a workaround, try using a different operator such as Equals or Begins With. You can also use it to find differences between universes. Thanks I see all content too now! I tried your query, "To extract all the report names from specific folder". Building a Query on the universe using WebI. we want another reports whether used this url or not. 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. On BO 4, I find that the following SQL runs fine. Step 1: Open your Power BI file first, then open Dax studio and click on Connect tab. 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. The simplest task such as a creation of list of reports might be tedious because of the structure of the result. File name: WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 error in one of the platforms which uses .NET framework 1.1. 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. I have BI4 client tools running and the EnterpriseFramework.dll has version 14.0.5.882. We are finding that when an Administrator logs in and uses the Query Builder mentioned in this post All users are returned as expected.. Could you try to close all excel windows and maybe EXCEL processes? Is it possible to get report level data like dimensions and the report query generated for a report from the repository ?Thanks !! {"serverDuration": 297, "requestCorrelationId": "12b290e7937bf855"}, SAP BusinessObjects Business Intelligence Platform (Enterprise), https://launchpad.support.sap.com/#/notes/1895241, Unlock the CMS database with new data access driver for BI 4.2, Number of Webi documents (excluding instances), select top 100000 si_id from ci_infoobjects where si_kind='Webi' and si_instance=0, Number of Webi instances only (not the master doc), select top 100000 si_id from ci_infoobjects where si_kind='Webi' and si_instance=1, Number of Crystal Reports (excluding instances), select top 100000 si_id from ci_infoobjects where si_kind='CrystalReports' and si_instance=0, Number of Deski documents (excluding instances), select top 100000 si_id from ci_infoobjects where si_kind='FullClient' and si_instance=0, Number of Deski instances only (not the master doc), select top 100000 si_id from ci_infoobjects where si_kind='FullClient' and si_instance=1, Number of Publications (excluding instances), select top 100000 si_id from ci_infoobjects where si_kind='Publication' and si_instance=0, Number of Publication instances only (not the master doc), select top 100000 si_id from ci_infoobjects where si_kind='Publication' and si_instance=1, select top 100000 * from ci_infoobjects where si_schedule_status=9, select * from ci_systemobjects where si_kind='Server', select si_name from ci_systemobjects where si_nameduser = 1, select si_name from ci_systemobjects where si_nameduser = 0. For example if I created a user such as Account Name: 12345, Full Name John Doe, etc. Could not connect to the server. Click Edit Data Provider on the toolbar. I use the odbc connector on my pc with ms access to create queries and then publish them on a switchboard. 2. Connect Power BI to SQL Server: 3 Easy Ways - Devart Blog There is no limit to the scope of the queries, you can query all the content, including content not normally accessible through the CMC or BI LaunchPad. I need to find ithe list of universes where ,a specific TABLE is being used. I luv this tool, it is running fine on BO 3.1, no issues period. Thank you for your solution for so many users like me. Please let me know the code for getting folders, Objects, Objects properties like definition, sql etsc.. of a universe, You can start from this post: https://bukhantsov.org/2011/08/getting-started-with-designer-sdk/. CMS Query Builder Get Free The tool allows to Exporting Report Builder query results to spreadsheets - IBM Trademark, SAP BusinessObjects Business Intelligence platform 4.0 ; SAP BusinessObjects Business Intelligence platform 4.1 ; SAP BusinessObjects Business Intelligence platform 4.2 ; SAP BusinessObjects Business Intelligence platform 4.3. Any output can also be retrieved by a WebI document. Query Builder allows you to query the whole CMS database but it prevents you from going any further with your metadata. 3. On a more functional level, we are able to document all objects in your environment, for example, the permissions, users and groups, universes, user connections, and are able to run impact analysis and understand what is used and not used. I am also getting the same error File is in used by another process. 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) Please close Excel and try again. With the graphical interface, users can create requests by using predefined objects and filters, rather than having to type in the technical terms. Is it possible to get the folder and all the sub folder under it. Load a query from the Queries and Connections pane. To load to a Data Model, select Home > Close & Load > Close & Load To. Firstly, great piece of code!! It is great tool, love it. Let me know if you need help with this. Result is ", The KBA 1895241 should work if navigating tohttps://launchpad.support.sap.com/#/notes/1895241. I try this without success, 1) Restart server When finished making changes in the Power Query Editor, select File > Close & Load. 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 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. List universes that reference a database column. It is commonly used by SAP BusinessObjects administrators and developers looking for information about their users, reports, and universes. You can also dynamically override the default settings for a query by using the Import dialog box which displays after you selectClose & LoadTo. you cannot actually build a query, you have to write the query manually. For custom installations, 3rd party authentication and single sign on, an initial setup may be required. SELECT SI_ID,SI_NAME,SI_PARENT_FOLDER,SI_FILES FROM CI_INFOOBJECTS , allows you to go further and fill the gaps that Query Builder doesnt. How can you make sure that your Business Objects reports show accurate information? Windows server 2003 Enterprise X64 edition, SPack 2. 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. A Data Model can have many tables. So far so good on our BI4.0 SP6 installation. 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 SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4, To get BO File Repository Server Information, WHERE SI_KIND = SERVER AND SI_NAME LIKE %FILEREPOSITORY%, To get the all the public folders (Non System Folders), WHERE SI_PARENTID=23 AND SI_NAME!=REPORT CONVERSION TOOL AND, SI_NAME!= ADMINISTRATION TOOLS AND SI_NAME!= AUDITOR, To list all the WebI reports with prompts. The default behavior is to download data previews in the background. 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 Remove all references to the libraries and reattach them. What I have tried so far (Image is Embedded and not from a Database or External): In CMC->Folders-> [choose folder] ->User Security-> [choose UserGroup] ->View Security. WebSAP BusinessObjects Business Intelligence platform 4.2 ; SAP Crystal Server 2016 Keywords query builder, cms, query, csv, export, tool, bi, bi platform, server, cms 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. How does it interface with the server? Object Please help. Containers export containers on a separate worksheet. The Query Builder tool, enables you to query the full CMS database and return meaningful results, d-encrypting its contents. BI Universe Export Business Layer object definitions to Excel However, if you want to do this, select the option. Click to share on Twitter (Opens in new window), Click to share on Facebook (Opens in new window), InfoStore Query Builder (with export toExcel), https://bukhantsov.org/2011/08/getting-started-with-designer-sdk/, https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/, http://www.howtogeek.com/125045/how-to-easily-send-emails-from-the-windows-task-scheduler/. Url:https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. Is it possible to query reports utilizing a specific element from a specific universe? Version: XI An added extra here is that with 360Eyes, you will be able to track inconsistencies between the CMS and FRS. select * from ci_systemobjects where si_kind='Event', select SI_FILES from CI_INFOOBJECTS where SI_FILES.SI_NUM_FILES=0, Plug-ins where that was no icons associated with them, SELECT top 200000 SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='MyGroupNameHere'") AND SI_KIND='USER', SELECT TOP 20000 SI_NAME, SI_LASTLOGONTIME FROMCI_SYSTEMOBJECTS WHERE SI_NAME NOT IN ('Administrator','Guest') AND SI_KIND='USER' AND SI_LASTLOGONTIME IS NULL ORDER BY SI_NAME, SELECT top 200000 SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='MyGroupNameHere'") AND SI_KIND='USER' AND SI_NAME NOT IN ('Administrator','Guest') AND SI_KIND='USER' AND SI_LASTLOGONTIME IS NULL, 1201157 - Sample administrator queries for Query Builder, 1542511 - How to access query builder on Web Application Container Service (WACS) deployment, 1854982 - Using query builder to find number of reports for certain schedule statuses.