BusinessObjects Query builder queries 240 104 344,412 Hello Techies, Some of the Query builder queries to explore the BusinessObjects repository. Clear this option if you can want to see all the data right away. For example, you have to find a folder ID, before querying the list of objects in the folder. For more information, see Import data from external data sources. You can suggest a query to get the group, universes, and folders. 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. Click Edit Data Provider on the toolbar. The step-by-step wizard is too simplistic. There is one tool called BI clever. In new BI 4.2 SP3 release there is a new driver who allows you to query CMS db without the limitations of query builder, but with all the features of a webi report. Copyright | Dont wait, create your SAP Universal ID now! Tableau Conference Agenda: The Top Sessions to Attend, UNV to UNX conversion in just 4 steps using 360Suite solutions. This will be handy for those who searching for Query Builder Queries. WebIn this video, we will create a new Web Intelligence document based on a universe. You have to copy-paste each individual name in your list. Hi Yamini,from Query Builder, you can only extract start and end time of any instance. Dont let your users be unpleasantly surprised when. Url:https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. 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. 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. Under theDefault Query Load Settings section, do the following: Select Specify custom default load settings,and then select or clear Load to worksheet or Load to Data Model. 1733964 - How to get list of events using Query Builder? In Excel, you may want to load a query into another worksheet or Data Model. 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. BI-on-BI is a discipline that consists of analyzing BI metadata in order to take decisions and actions. Now well import the Inventory data from a file. Thanks. However, if you want to do this, select the option. Excuse me, but the following SapNote does not contain any information: 1735539 - Query to list all Universes for which a user has "Edit Objects" rights via Query Builder, Working fine for me (here the main infomation from the note), 1. Version-12.0.1100.0, Culture-neutral, si_parentid=23 is for the Public folders. It appears to be exactly what i am looking for1. 2) Install the tools to completly new PC. A Data Model typically contains several tables arranged in a relationship. Hi, I want to use the tool for BO XI R2. Im not a coder so i cannot modify your source code. The All I changed is theSI_ID of the folder and it didn't work. I can get the number of Data. What authentication are you using? > Does it run standalone? This provides the Principal and Objects(Folder) to which the access is enabled. Hello All, We have been play around with the Query Builder a bit and noticed an oddity I guess. I am really curious to know if there is a way to query object SI_DESTINATIONS because it doesnt return values when i mention this in select objects. I got the error error (could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2 . I am only able to find which Data Foundation / Business View it is connected to. hi, this looks interesting but it did not work on my laptop with windows XP SP2. I will check it. Using Query builder one can easily query the BusinessObjects repository and get the required information which cannot be found even in CMC. Please suggest me. The familiar Excel worksheet , ribbon, and grid, The Power Query Editor ribbon and data preview. I want to know the query for the query builder. Once again Thank you and Best regards, I am having the same question, Can you recommend a tool or a way to get the report's query. 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). The file should not be open in Excel, otherwise the program will not be able to write into it. I'm guessing InfoSteward stores them somewhere else in the CMS? Save the .csv file to your local The repository information is stored in the form of InfoObject and the CMS reads the InfoObjects from the virtual tables. I am getting the error: This command is just like the Data > Get Data command in the Excel ribbon. Is there a solution to this? On import some have been flipped to SI_LOGON_MODE = 0. Can this not be made level unaware? 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. Once again thank you so much for yoru efforts in providing such handy tools. its with enterprise id. Tip At the bottom of the dialog box, you can select Restore Defaults to conveniently return to the default settings. File is used by another process when Excel is not running in task manager.I am using Excel 2010, BO XI3.1. But when a NON-Administrator runs the same query in Query Builder only the Users that are designated as Administrators and the User that runs the query are returned. With no option to export the results in XLS or CSV format, you can only review the results on your screen, making it hard to leverage and manipulate the output. With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. Thanks I see all content too now! Dont let the limitations of Query Builder stop you from going further. To open the Data Model, select Power Pivot>Manage. You may want to just start from scratch. An Error occured in sending the commanf to the application. Web Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel. Text Format, Hi Dymytri The default limit for a CMS query is 1000. Thanks anyways! 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? Version-12.0.1100.0, Culture-neutral, I need to find ithe list of universes where ,a specific TABLE is being used. Under Relationships, select or clear Update relationships when refreshing queries loaded to the Data Model. Please suggest me. Query Builder is a tool available in SAP BusinessObjects since Crystal Enterprise 8.5 that allows you to understand what content exists in the CMS (Central Management Server). 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. Confirm that the correct table displays. Now its clear which tab has the data and which tab has the query. Thanks for this SO USEFUL tool ;o) If you haven't changed the default query timeout limit (of nine minutes), then that's probably the cause of your errors. Load a query from the Queries and Connections pane. For example if I created a user such as Account Name: 12345, Full Name John Doe, 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 > Any suggestions? works great for the past 20 years or so. The default behavior is to download data previews in the background. Workbook settings that only apply to the current workbook. SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS. Works fine on test server, able to see all the folders/subfolders under root folder. CrystalDecisions.Enterprise.Framework.dll Congrats! Some of the Query builder queries to explore the BusinessObjects repository. I am curious if it is possible to filter on child objects. Am getting the same error (could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2 [etc]). Extract generates configurable professional-quality report definition documentation (tables and columns used) for Crystal Report files. This can occur the first time you create a query in a workbook. If you have multiple accounts, use the Consolidation Tool to merge your content. I also have not found where I can see what objects are available. It provides advanced features like code highlighting, auto-completion, and syntax checking to help you write error-free code. Can you suggest a query to get the successful instance countwithin specific folder and for specific date. May you have 10,000 happy days for sharing. To export the results to a .csv file, complete the following: Click Query Results. SELECT TOP 10000 * FROM ci_systemobjects. Im guessing my query structure is not correct becuase the SI_LOGON_MODE is sever levels deep in the Processing Info. 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. error. 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. 1 Where do I get what different column names in these tables mean, except for the obvious ones of course? I apologies, I obviously didn't look hard enough. Query Builder is one of the essential and interesting tools in BusinessObjects. 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. If you have multiple accounts, use the Consolidation Tool to merge your content. 1. My cmc port is 8081,by default it take 8080 port and hence it is not working for me. A year ago, I was onsite to help a large French telecom company with our solutions for theirBusiness Objects migration. I try this without success, 1) Restart server Probably I had an IE cache problem. How can I get the Instances which took the maximum time to execute? This will be handy for those who searching for Query Builder Queries. Hi Amir, here: bukhantsov.org/tools/QueryBuilder_src.zip. It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. I am also interested in this tool. Neither the request nor the results can be stored. You can change the defaultbehavior for all your workbooks or just the current workbook. This is a quite tricky. And is there any way for exporting this data? Query Builder is one of the essential and interesting tools in BusinessObjects. Using Query builder one can easily query the BusinessObjects repository and get the required information which cannot be found even in CMC. To explain more in detail, BusinessObjects repository made up of set of tables to hold the information about thx:), Excellent tool mate !! Alerting is not available for unauthorized users, Right click and copy the link to share this comment. Also a query to get information on who uses a particular report. Please refer to our documentationand do not hesitate contacting us at:contact@wiiisdom.com. Let me know if you need help with this. Can you let me know in Query designer what is the query to fetch. The tool allows to restrict query tospecific object or specify parent folder. what i am asking, one of our developer had used "image from address" in Appearance option in format cell. In the Power Query Editor, select Home > Close & Load > Close & LoadTo. Assuming your query is valid and has no errors, you can load it back to a worksheet or Data Model. The query uses objects from two different levels Level 0 and Level 1. In Excel you can do this by using the Text to Columns feature of the ribbon. 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. It may take a few seconds to reclaim memory. An example of an impact analysis report identifying documents that contain specific Universe objects, SQL expressions, or variables. I am new to this product and learning as I go. I have tried to use it to export to Excel. , allows you to go further and fill the gaps that Query Builder doesnt. The tool should be recompiled to change the port. 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. 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. there isn't, the tool is not built for exporting, however CMS DB driver now allows you to build universes on top of repository and report of them. The one here should work. Furthermore, Excel cant effectively use multithreaded execution. To a worksheet. Your help in this matter will be highly appreciated. I am looking for a query to get information of most accessed reports with report folder excluding shortcuts and reports instances. In the Query Options dialog box, on the left side, under the CURRENTWORKBOOKsection, select Data Load. It is also confusing the way the levels are sorted in the layers, plus it still doesnt allow you to fetch data from the Filestore. Could you please help to find out that info using query builder or cms database. You can find a free trial available here. Thank you for your solution for so many users like me. WebNext, we need to find the names of queries (or "data providers") contained within this document: Open the WebIntelligence document and enter Design mode. Import some data. I have been using Query Builder only for a very short while. (Sort of a tutorial) PublicKeyToken-692fbea5521e1304 or one of its dependencies. Ailsa is the Content and Communications Manager at Wiiisdom. Prod environment). Is there a compiled version for 12.3.0.601? Its always good practice to change the default names of worksheet tabs to names that make more sense to you. But it is throwing errors. These query builder posts have been extremely beneficial after being thrown into the SAP BOBJ admin world. The document exists in the FRS but the link doesnt exist in the CMS. You also need to know a language that resembles SQL without it, youll be a little bit stuck! You can view results in the BO Query Builder by clicking Open in Query Builder, or export the result to Excel file. 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. 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? I tried Describe CI_INFOOJBECTS but Query builder doesn't like that. WebIf you want to export or analyze the data in Excel its not an easy task. The tool has been desupported. The above is an example of a query result. WebOn the External Data tab, in the Export group, click Excel. system cannot find the file specified. Thank you. and return a list of reports those are using this table in their query. The report works so far and I managed to add the logo. Just wondering if this has got a dependency on .Net framework. . 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. 2) Create a Data Store by making a connection to a database sat SQL SERVER 2008 in this case. Thank you! SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. Hello Dmytro, thank very much for this valuable tool, its a pleasure to manipulate Infostore data in Excel, thanks to you ! 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'). BusinessObjects Query builder Best practices & Usability, BusinessObjects Query builder queries Part II, BusinessObjects Query builder queries Part III, BusinessObjects Query builder queries Part IV, BusinessObjects Query builder Exploring Visualization Objects, BusinessObjects Query builder Exploring Monitoring Objects, BusinessObjects Query builder Exploring Lumira & Design studio Objects, BusinessObjects Environment assessment using Query builder, BusinessObjects Environment Cleanup using Query builder, BusinessObjects Query builder Whats New in BI 4.0. !:FDtF)YM,D2?o?!$C EP;sj{B%*!} 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. In the Export - Excel Spreadsheet dialog box, review the suggested file name for the Excel workbook (Access Terms of use | Query Builder doesnt have the means to detect these inconsistencies but 360Eyes can. Can you please suggest where to update the port number so as to get the desired information from cmc? Thanks so much for the tool it is very useful ( this BO ver: XI 3.1+SP3+FP3.4). Can you please let me know what might have gone wrong? Is there a way to query and return what tables/fields a report is using?? It might not work for other versions. Also it does not have installer, you just unpack the tool in a preferred location and create a shortcut. In using the product for 3.1, in using the query SELECT * Could you try to close all excel windows and maybe EXCEL processes? Ive been using this great tool for some time but ive just seen the error. 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. Query Builder allows you to query the whole CMS database but it prevents you from going any further with your metadata. Note that this information is stored in universe files in BO file repository, not in CMS. The query in the screenshot below will list the objects in the Favorites folder for all users. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SAP BusinessObjects Business Intelligence platform 4.x, download, query builder, excel, csv, cms metadata, tool, bi, cms, export, excel format, Admin tools , KBA , BI-BIP-CMC , Central Management Console (CMC) , How To. Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel. . A Data Model can have many tables. CMS Query Builder Get Free The tool allows to Access Query Tool by using the URL of the form: http://MyServer:Port/AdminTools, For an overview please refer to this SCN article. Choose the account you want to sign in with. 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. If this occurs, select Close & Load, in the new worksheet, select Data > Queries & Connections > Queries tab, right click the query, and then select Load To. Visit SAP Support Portal's SAP Notes and KBA Search. How can you make sure that your Business Objects reports show accurate information? I am running it on BI 4.0 SP4 FP20 and it works fine, but Security information is not available via CMS queries. 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) Querying the relationships between different objects of the tables requires specific Query Builder knowledge. This action causes Excel to enumerate again through the entire data set for each row. 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 Not sure if this helps: I am using Excel 2010, Window 7 and BO XI3.1. At Xoomworks BI we view the GB&Smith 360Suite of tools as administration intelligence for SAP BusinessObjects. All users that have never logged-in to the BI Platform, All users that have never logged-in to the BI Platform that are under a particular group, Objects that a given Group/User has access too. Logon cannot continue. They are in the folder: [SAP BusinessObjects]\SAP BusinessObjects Enterprise XI 4.0\win64_x64\dotnet\iPoint, The libraries: Please close Excel and try again. To avoid confusion, its important to know which environment you are currently in, Excel or Power Query, at any point in time. Check Biclever CMS Query Builder as alternative. Is it possible to automate the execution of Infostore query builder with CMS details user name,Pwd with query. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. Alternatively, you can try Skyvia Query Excel Add-in, the The source code is available, you can try to compile for R2. What do you think could be a possible reason. If you have trouble when applying Updates because the AddNode.bat/addnode.sh fails look for the SI_VIRTUAL_ID and make sure it matches the id in the _boe.install. SELECT SI_ID, SI_NAME, SI_PROMPTS.SI_USER FROM CI_INFOOBJECTS WHERE SI_KIND = 'CrystalReport', I try the above and it does not return SI_PROMPTS.SI_USER. Unable to connect to CMC server:6400. The How can I get a list of all of the fields in these tables? Hello ! If you have some suggestions about functionality let me know: dmytro.bukhantsov at gmail.com 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'. WebThe tool allows extracting Unv universe metadata to Excel spreadsheet. We provide a library of WebI documents in order to efficiently query the metadata it aggregates. 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. Do one of the following. 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. I noticed this behavior when you use this in Mac. You may find the Queries & Connections pane is more convenient to use when you have many queries in one workbook and you want to quickly find one.