This will http://www.reportminer.com/products/rptminersuite.shtml. Tip If you are in a worksheet with a query, select Data > Properties, select theDefinition tabin the Properties dialog box, and then select Edit Query. What do you think could be a possible reason. To explain more in detail, BusinessObjects repository made up of set of tables to hold the information about the BI content such as Universes, reports, Users, schedules, etc. 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) Also tried on a server with platform files (BI4 SP05) and on a BI4.1 server. SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. select SI_NAME,SI_KIND from CI_INFOOBJECTS where SI_FILES is null, Note that this will only return objects which dont have an SI_FILES property at all. Thanks a lot. Export Is it possible to list users who have refresh and save the report in Favorite Folders?. Login to Query Builder with Administrator account using the It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. Under Background Data, select or clear Allow data previews to download in the background. This application works fine! In the Query Options dialog box, on the left side, under the CURRENTWORKBOOKsection, select Data Load. its a good tool which saved me a lot of time. Need your help: error. CMS Query builder will helps you do that. Format the cells and add formulas as per your requirements and then save the file. Please suggest me. Wasy. All objects that dont have an SI_FILES property at all. 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. Create an Excel Export Template The first step in this process is to create an Excel that contains a table for your exported data to be inserted into. I am logging in with an administrator account so it is not a question of grants. For 4.0 SP4 Patch 6 So far Ive only used it to export a list of users with name, email and last logon time, to Excel. The tool allows to restrict query tospecific object or specify parent folder. Can you let me know in Query designer what is the query to fetch. 3) Now open an excel file and save the required data at Excel file in the local drive. Export Measures and Calculated columns This only occurs when you load the data to a worksheet orData Modelfrom Power Query. The Query Builder tool, enables you to query the full CMS database and return meaningful results, d-encrypting its contents. Privacy | Do you wanna try to compile it for your system? Local Machine: Win 7. biclever Tools for Business Intelligence What is the variable to use to select users whose accounts have been disabled (Disabled option checked) ? However, if you want to do this, select the option. Could not connect to the server. Flag is only leveraged when session is opened with a user member of Administrators group, due to security concerns with such detailed statistics and SQL information. It is great tool, love it. BI Universe Export Business Layer object definitions to Excel WebIf you want to export or analyze the data in Excel its not an easy task. SELECT SI_NAME, SI_ENT_USERFULLNAME, SI_DESCRIPTION, SI_EMAILADDRESS, SI_DISABLED, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND=USER. Thanks a lot for this great article, I wondering if you could help me. You might choose this command to try out the Power Query Editor independent of an external data source. Works fine on test server, able to see all the folders/subfolders under root folder. Or you can select Home and then select a command in the New Query group. Thank you for your solution for so many users like me. It may take a few seconds to reclaim memory. I can get the number of Data. This means that response will come faster, and no unnecessary resources will be consumed by CMS. Hello Manikandan, I work in Dallas and I thank you for writing this blog post. 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. looks cool. You should go with SI_PARENTID instead if you are interested only in documents. This tool could help me significantly. Can you send the code or the complete solution with source code please ? 2686851 - How to export query results from Query How do I get a list of Parameter Names, and their Types (string or numeric) using SQL ? Privacy | Select Python script from the Get Data window and click Connect. To preview the base data returned by a query, click the Refresh button: The combination of a document ID and Hi, I am trying to run the following query, however, the query only returns SI_DESCRIPTION, SI_NAME, SI_ENT_USERFULLNAME, and SI_LASTLOGONTIME. Url:https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. In the Query Options dialog box, on the left side, under theGLOBALsection, select Data Load. 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. Any help is greatly appreciated. The default connection is using port 8080 we use 9100. Is there a way to easily export data from the Query Builder? WebSAP BusinessObjects Business Intelligence platform 4.2 ; SAP Crystal Server 2016 Keywords query builder, cms, query, csv, export, tool, bi, bi platform, server, cms It will return a table at the end of standard results, with information such as: This can be helpful in analyzing performance and optimizing custom CMS queries, and may be requested by SAP Support when working on incidents related to performance. Using Query builder one can easily query the BusinessObjects On BO 4, I find that the following SQL runs fine. 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? You can find the, https://blogs.sap.com/2013/06/17/businessobjects-query-builder-basics/. Does it run standalone, or is added to an existing tool? WebAbout the integration of Power Query into Excel Create a query Load a query Edit a query from a worksheet Edit the query of a table in a Data Model Loading a query to a Data Model takes unusually long Set query load options See Also Power Query for Excel Help Manage queries in Excel Need more help? I'm guessing InfoSteward stores them somewhere else in the CMS?I've tried to copy/paste the list from Instance Manager in the CMC, but no luck.Thanks in advance for any guidance folks can offer!PS:I wish they'd put an "export" function on CMC list-based screens. However, Query Builder has its limitations and you will face situations where Query Builder is not enough. So you cannot get this information using CMS queries. Thanks for this SO USEFUL tool ;o) 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. Reallt really appreciate your response on this. you cannot actually build a query, you have to write the query manually. Yes, the tool requires .NET 3.5. The source code is available, you can try to compile for R2. Im not a coder so i cannot modify your source code. Business Objects I have been using Query Builder only for a very short while. Object 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 Can you suggest a query to get the successful instance countwithin specific folder and for specific date. To a Data Model. The query uses objects from two different levels Level 0 and Level 1. In the Import Data dialog box, select Add this data to the Data Model. how to get the Data security profile name from AdminTools query. Need to download Query Builder results into an Excel format so that the end users can review it. Could you please help to find out that info using query builder or cms database. Step 3: Under Metadata panel you will see a data model which contains tables, columns and measures. you have to remember the table names and the object kinds. Hello Dmytro, thank very much for this valuable tool, its a pleasure to manipulate Infostore data in Excel, thanks to you ! 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. How to obtain query processing statistics, Use flag "SHOW STATS" at the end of the query to get detailed statistics about execution in CMS example:select SI_NAME, SI_ID from ci_infoobjects where SI_KIND='Webi' order by SI_SIZE desc SHOW STATS. CrystalDecisions.Enterprise.Framework.dll This action causes Excel to enumerate again through the entire data set for each row. property SI_FILES contains properties SI_NUM_FILES, SI_FILE1, SI_FILE2 etc. Select New Source to add a data source. The tool worked very well on the first day. 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. Any suggestions? Is there anything I should change. You can change the limit using option TOP: e.g. Yes. CI_INFOOBJECTS information about all kinds of documents (Webi, Crystall Reports, Txt, Doc etc.). On the other hand: Depending on the delimiter char used, the method of opening the file in Excel might be the problem: Restart Excel and choose "File > Open > 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. 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. Using Query builder one can easily query the BusinessObjects repository and get the required information which cannot be found even in CMC. IsAllowed takes the GroupID (or UserID) and the right ID. Hi Lorena Rename worksheet tabs Its a good idea to rename worksheet tabs in a meaningful way, especially if you have a lot of them. How does it interface with the server? CrystalDecisions.Enterprise.InfoStore.dll. what i am asking, one of our developer had used "image from address" in Appearance option in format cell. Currently, we are having 1K report on LaunchPad, and Need a tool that accepts the table's name. Extract generates configurable professional-quality report definition documentation (tables and columns used) for Crystal Report files. Thanks so much for this. Create, load, or edit a query in Excel (Power Query) When prompted upload this file to create a new business object by dragging and dropping the file in the blue You can change the defaultbehavior for all your workbooks or just the current workbook. To create proper queries, you need to equally familiarize yourself with these three tables and the content in their rows: Source: https://blogs.sap.com/2013/06/17/businessobjects-query-builder-basics/ by Manikandan Elumalai. Click on the Get Data button on the Home tab of the Power BI ribbon. Unable to connect to CMC server:6400. As a workaround, try using a different operator such as Equals or Begins With. . Clear this option if you can want to see all the data right away. 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: (SI_NAME is optional) Is there a way to show all the reports that one user is running? The step-by-step wizard is too simplistic. 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 You may want to just start from scratch. Query Builder doesnt have the means to detect these inconsistencies but 360Eyes can. Under Relationships, select or clear Create relationships between tables when adding to the Data Model for the first time. is it possible to query the comments solution table to get the context which was using for each front end tool to apply the comment ? Am getting the same error (could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2 [etc]). Hi ! hi, this looks interesting but it did not work on my laptop with windows XP SP2. You can find the basic SQL language requests here. It gives me a message that it could not save file File is used by another process.. Thank you! In a few months, SAP Community will switch to SAP Universal ID as the only option to login. To export the results to a .csv file, complete the following: Click Query Results. Workbook settings that only apply to the current workbook. I am using below query: SELECT SI_ID,SI_NAME, LAST_RUN_TIME FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER = 5698. May you have 10,000 happy days for sharing. How can I get a list of all of the fields in these tables? Open Power BI on your computer. From the menu, select the Export to CSV option. Great article !!! BI-on-BI is a discipline that consists of analyzing BI metadata in order to take decisions and actions. This might remove relationships created manually after the data was imported or introduce new relationships. Please help. SQL Editor: it is a powerful tool for writing and executing SQL queries and scripts. When finished making changes in the Power Query Editor, select File > Close & Load. Very nice blog and you have explained everything on query builder. Query Builder is one of the essential and interesting tools in BusinessObjects. We provide a library of WebI documents in order to efficiently query the metadata it aggregates. CMS Query Builder Get Free The tool allows to 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. To edit a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. SELECT TOP 10000 * FROM ci_systemobjects. Container is a hierarchical property. Query Builder User Guide - SAP query 1 Where do I get what different column names in these tables mean, except for the obvious ones of course? Decide how you want to import the data, and then selectOK. For more information about using this dialog box, select the question mark (?). For example: To access this information, 360Suite offers two modules: 360View and 360Eyes. Once you submit your query, results are shown on a web pagebut thats about as far as it goes. WebBusinessObjects Query builder 708 Views Follow RSS Feed List all the Public folders (including Subfolders) and which UserGroups has access to those folders/subfolders. If you have multiple accounts, use the Consolidation Tool to merge your content. I am new to this product and learning as I go. CI_SYSTEMOBJECTS information about servers, connections, users. Just wondering if this has got a dependency on .Net framework. There is one tool called BI clever. My cmc port is 8081,by default it take 8080 port and hence it is not working for me. WebExporting Report Builder query results to spreadsheets. A year ago, I was onsite to help a large French telecom company with our solutions for theirBusiness Objects migration. But it is throwing errors. Thanks I see all content too now! PublicKeyToken-692fbea5521e1304 or one of its dependencies. Ailsa regularly creates content for the SAP BusinessObjects and Tableau community, and strengthens Wiiisdom's external communication. Hello All, We have been play around with the Query Builder a bit and noticed an oddity I guess. Ailsa is the Content and Communications Manager at Wiiisdom. 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. For more information about Data Models, see Find out which data sources are used in a workbook data model, Create a Data Model in Excel, and Use multiple tables to create a PivotTable. As I said above, you need to know the language that resembles SQL in order to carry out queries but fortunately, since the BI 4.2 SP03 release, SAP has put one universe to access the CMS data, which allows you to pass by having to know this technical query-language, be able to see the data in WebI and export the data in Excel if you want. Really apreciate if you could help me figure it out how to troubleshoot and run the superbly awesome tool in my environment. Not sure where that is in Excel. (Sort of a tutorial) 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) 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. The simplest task such as a creation of list of reports might be tedious because of the structure of the result. The default behavior is to detect them. Note that this information is stored in universe files in BO file repository, not in CMS. No. SELECT * FROM CI_SYSTEMOBJECTS, CI_APPOBJECTS, CI_INFOOBJECTS WHERE SI PARENTID IN (53,59), https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. A very good handy tool to query metadata results. Excel I apologies, I obviously didn't look hard enough. Excel 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/. In the Power Query Editor, select Home > Close & Load > Close & Load. Great tool! > Does it run standalone? I am trying to find the right SQL to return the SI_User field contents from the SI_Prompts of Crystal Reports. 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. to parameterize the cms name, user name, password, business layer and data foundation path; to include data foundation export option; to have the option of !:FDtF)YM,D2?o?!$C EP;sj{B%*!} When I am trying to login to the query builder tool from the server itself. There are a few doubts I wanted to ask and you seem to be an expert on the subject. Thanks so much for the tool it is very useful ( this BO ver: XI 3.1+SP3+FP3.4). Terms of use | But this can change as soon as we roll out a new servicepack. BusinessObjects Query builder Basics | SAP Blogs 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. 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. For example if I created a user such as Account Name: 12345, Full Name John Doe, etc. You can also dynamically override the default settings for a query by using the Import dialog box which displays after you selectClose & LoadTo. As suggested earlier, it wold be great to walk me through troubleshooting the behavior. They are in the folder: [SAP BusinessObjects]\SAP BusinessObjects Enterprise XI 4.0\win64_x64\dotnet\iPoint, The libraries: 360Suite is designed for large organizations looking to mitigate data risks, automate operations, and is the solution of choice for any migration project. If you have multiple accounts, use the Consolidation Tool to merge your content. 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 You have access in test hence you have no issues. Windows server 2003 Enterprise X64 edition, SPack 2. Querying the relationships between different objects of the tables requires specific Query Builder knowledge. An Error occured in sending the commanf to the application. Please suggest. BI4.0: Win server 2008. 2) Create a Data Store by making a connection to a database sat SQL SERVER 2008 in this case. WebSAP BusinessObjects Business Intelligence Platform 4.x Web Intelligence (WebI) AdminTools (Query Builder) Product SAP BusinessObjects Business Intelligence platform 4.2 ; SAP BusinessObjects Business Intelligence platform 4.3 Keywords Create, load, or edit a query in Excel (Power Query), About the integration of Power Query into Excel, Edit the query of a table in a Data Model, Loading a query to a Data Model takes unusually long, Find out which data sources are used in a workbook data model, Use multiple tables to create a PivotTable. 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. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. Contains InfoObjects that the BI Platform uses, User, User Group, Server, Server Group,Folder, Connection, Calendar,Event, Holds InfoObjects that are used by documents, Contains InfoObjects that are consumed by the end user, WebI, Crystal Report, FullClient, PowerPoint, Pdf, Excel, Word, Rtf, Txt,Program, Shortcut, Query Builder can be found at the below URL. you usually have to write several queries to get the required result. Hello ! I also have not found where I can see what objects are available. Know which environment you're in Power Query is well-integrated into the Excel user interface, especially when you import data, work with connections, and edit Pivot Tables, Excel tables, and named ranges. 3rd Party Authentication) , How To. (Here limited to only the Public folders), select si_name from ci_infoobjects where si_parentid=23 and IsAllowed(