The SAP Note/KBA you were trying to reach has been removed. 360Suite empowers SAP BusinessObjects professionals by pulling metadata and, leverage this data by carrying out impact analyses, How BEC Better Serves Its Customers with Improved SAP BusinessObjects Promotion Management, Automate your SAP BusinessObjects archiving, How To Implement and Use Tags in Business Objects, 360Suite Your new 3 best friends for SAP BusinessObjects migration, How Fannie Mae Maintains an Efficient SAP BusinessObjects Environment, Taking full advantage of 360Suite #2 Compare Universes, Repoint Your WebI Reports With Ease Thanks To 360Suite, Solving SAP BusinessObjects Challenges with 360Suite Solutions at Johnson Financial Group, Harley-Davidson Saved More Than $1 Million with 360Suite, How Orange Prepared For Its SAP BusinessObjects Migration, Repointing converted Webi reports to Freehand SQL connections, Business Objects Risk Management and Regulatory Needs, Leveraging 360Eyes data for Business Objects migration, How A Fortune 500 Company Prepared for a Business Objects Upgrade. 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 ! Hello ! You can also dynamically override the default settings for a query by using the Import dialog box which displays after you selectClose & LoadTo. 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. WebIn this video, we will create a new Web Intelligence document based on a universe. 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. but this SQL (which worked fine with BO3.x)fails miserably with a "Not a valid query." You can use command line version of the tool to generate the Excel file 360Suite is designed for large organizations looking to mitigate data risks, automate operations, and is the solution of choice for any migration project. Alerting is not available for unauthorized users, Right click and copy the link to share this comment. 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. With InfoSteward, we use an instance of IPS_CMS and you can query it with the same tools as a BOBJ CMS. additional info : I use LDAP identification could it be a problem ? With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. I think it's not possible from CMS query at all. From the menu, select the Export to CSV option. Dear Matthew, again a useful page/info by you, I can't open/view the note 1895241. This tool is also aimed at less technical users because it doesnt require you to learn the SQL language that is needed with Query Builder. You can also set default query load settings in the Query Options window. The file should not be open in Excel, otherwise the program will not be able to write into it. Working with Query Builder YouuseQueryBuildertoconstruct,submit,andtrackquerystatus.Youcansubscribetoeventsand Im not sure if you want an excel or vba way to do this, or do you want ms-access to do something like this before it goes into Excel? Using SI_PARENT_FOLDER returns documents and instances of these documents, that are in specified folder. Same result. You have to copy-paste each individual name in your list. The query in the worksheet and the table in the Data Model are updated. Does it run standalone, or is added to an existing tool? The tool has been desupported. To open the Data Model, select Power Pivot>Manage. 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. Use the below link to easily export the data. This can occur the first time you create a query in a workbook. In the Manage Application Data select the Import Business Objects. 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. Microsoft is aware of this problem and it is under investigation. I am getting error when exporting to excel : Could not save file, file is used by another process. However, Query Builder has its limitations and you will face situations where Query Builder is not enough. I am logging in with an administrator account so it is not a question of grants. How can you make sure that your Business Objects reports show accurate information? If you have multiple accounts, use the Consolidation Tool to merge your content. Given a particular group name (replace MyGroupNameHere accordingly) this query will return ALL the users in the group and any sub groups. 1733964 - How to get list of events using Query Builder? 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/. The one here should work. And I have checked no excel sheet or excel process running on my machine. If you have some suggestions about functionality let me know: dmytro.bukhantsov at gmail.com Any output can also be retrieved by a WebI document. All I changed is theSI_ID of the folder and it didn't work. error. It gives me a message that it could not save file File is used by another process.. Is is possible to query and find a particular universe object used in any reports. A very good handy tool to query metadata results. In your help is greatly appreciated. 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? Its because you dont have rights to view the folders in the environment you are looikng in (viz. The system cannot find the file specified, Do I need the BI Client tools on the desktop that I want to install QueryBuilder. There is one tool called BI clever. The default behavior is to detect them. There are several ways to edit a query loaded to a worksheet. Decide how you want to import the data, and then selectOK. For more information about using this dialog box, select the question mark (?). 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. The default connection is using port 8080 we use 9100. Can i have a link for download the verison is works fine with Product Version: 12.3.0.601 (XI 3.1 SP3)? 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. I do not know which tool can do that. Select Enter Data to manually enter data. 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. On the contrary, if the report exists and not the link, you cannot consult the report and therefore it is taking up valuable space in the machine. Text Format, Hi Dymytri How do I get a list of Parameter Names, and their Types (string or numeric) using SQL ? SP3 on a Windows 2003 server. 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. This is the most common way to create a query. 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. 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). With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. Open Power BI on your computer. 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. Great Tool. This is a quite tricky. we want another reports whether used this url or not. works great for the past 20 years or so. Execute the below query to get the SI_ID of the user for whom we need to check the rightselect si_id,si_name from ci_systemobjects where si_kind = 'User' and si_name = 'Username', 3. This is a great tool ! The problem is, that the logo doesn't show up after exporting it to Excel. Login to Query Builder with Administrator account using the following link: http://servername:portnumber/AdminTools, 2. In Excel, select Data > Queries & Connections, and then select the Queries tab. https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/ There are a few doubts I wanted to ask and you seem to be an expert on the subject. If you have multiple accounts, use the Consolidation Tool to merge your content. This command is just like the Data > Recent Sources command in the Excel ribbon. Tool works like a charm :), i am still in process of fixing the dlls, 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. 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. There isnt an "execution time" field. Check Biclever CMS Query Builder as alternative. Access Query Tool by using the URL of the form: http://MyServer:Port/AdminTools, For an overview please refer to this SCN article. I even restarted my PC and tried fresh but no luck !! Select Data > Get Data > Launch Power Query Editor. I also have not found where I can see what objects are available. system cannot find the file specified. File is used by another process when Excel is not running in task manager.I am using Excel 2010, BO XI3.1. PublicKeyToken-692fbea5521e1304 or one of its dependencies. Please suggest. This tool could help me significantly. 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'. Thanks a lot I am attempting to isolate Crystal Report objects where the SI_LOGON_MODE 1. Highlight the entire block of text you want to be numbers Alt + A, then E, then Alt + F, you're done. This is usually the result when content is outdated. 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. To preview the base data returned by a query, click the Refresh button: The combination of a document ID and Again, there is no documentation to help you and technical knowledge is still needed given mapping the InfoObjects to a universe can be challenging. You may want to just start from scratch. Thanks Dmytro for your response. Please refer to our documentationand do not hesitate contacting us at:contact@wiiisdom.com. Under Background Data, select or clear Allow data previews to download in the background. you usually have to write several queries to get the required result. Global settings that apply to all your workbooks. However, there are only three tables and there are vast amounts of information stored in each one, making retrieving the data very difficult. Yes. Dont let your users be unpleasantly surprised when. 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) Choose the account you want to sign in with. 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. Its always good practice to change the default names of worksheet tabs to names that make more sense to you. thanks for sharing. 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. 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 Is there a BO4 version of these SQL examples ? Its particularly important to clarify the difference between a worksheet of data, and a worksheet loaded from the Power Query Editor. No, that can't be done with CMS queries. who had been write external web url. It is commonly used by SAP BusinessObjects administrators and developers looking for information about their users, reports, and universes. Please help. Tip At the bottom of the dialog box, you can select Restore Defaults to conveniently return to the default settings. I noticed some of the folders under Root are not appearing. thx:), Excellent tool mate !! I would like to quicly identify any others. Please help.. Congrats! Now its clear which tab has the data and which tab has the query. It connects to the server using BO API to query infostore objects. 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. Is it possible to get report level data like dimensions and the report query generated for a report from the repository ?Thanks !! Is there a way to show all the reports that one user is running? 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. All objects that dont have an SI_FILES property at all. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. > Does it run standalone? 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. Also tried on a server with platform files (BI4 SP05) and on a BI4.1 server. Select a cell in the data and then select Query > Edit. and includes a section listing Process Info (including the report's parameter names, etc). 3rd Party Authentication) , How To. Is there a way to easily export data from the Query Builder? I am using below query: SELECT SI_ID,SI_NAME, LAST_RUN_TIME FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER = 5698. In the Power Query Editor, select Home > Close & Load > Close & Load. I also get this strange message. The Yes. SQL Editor: it is a powerful tool for writing and executing SQL queries and scripts. The default behavior is to download data previews in the background. 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. Several fields are missing in the results. Hello Dmytro, thank very much for this valuable tool, its a pleasure to manipulate Infostore data in Excel, thanks to you ! For custom installations, 3rd party authentication and single sign on, an initial setup may be required. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. Do you see Excel file on disk? 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. WebBusinessObjects Query builder 708 Views Follow RSS Feed List all the Public folders (including Subfolders) and which UserGroups has access to those folders/subfolders. 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'). I will send you source code. When I run a query likeselect si_name, si_cuid from ci_infoobjectsagainst my CMS database using the query tool,I don't see any objects that look like scheduled jobs / tasks. However I run into the same error:Could not save file, file is used by another process. {"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. Terms of use | Step 1: Open your Power BI file first, then open Dax studio and click on Connect tab. Furthermore, Excel cant effectively use multithreaded execution. Probably I had an IE cache problem. How can I get the Instances which took the maximum time to execute? 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. BI4.0: Win server 2008. 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. Hope the above queries might be useful to those who start digging in to Business Objects repository. Where can I find what fields are in the tables. BusinessObjects Query builder queries 240 104 344,412 Hello Techies, Some of the Query builder queries to explore the BusinessObjects repository. Simple queries to use against the repository, SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=USER, SELECT * FROM CI_APPOBJECTS WHERE SI_KIND=UNIVERSE, SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=WEBI, 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. Thanks I see all content too now! In the PowerQuery Editor, select File > Options and settings > Query Options. Containers export containers on a separate worksheet. Dont let the limitations of Query Builder stop you from going further. https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. Save the .csv file to your local I guess sub items of a query are not returned. You can find a free trial available here. You might choose this command to try out the Power Query Editor independent of an external data source. In the list of queries, locate the query, right click the query, and then select Load To. 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. Web360Suite is a set of solutions to ensure quality, reliability, performance, and efficiency of SAP BusinessObjects through testing, auditing, monitoring, cataloging, and scheduling methodologies. The familiar Excel worksheet , ribbon, and grid, The Power Query Editor ribbon and data preview. For example, you can delete WebI documents or even targeted users per request. Thanks. For example, the CMS can contain links toward FRS documents, and this could create various inconsistencies such as: These inconsistencies can be extremely frustrating because if the link exists but not the report, it works exactly like on a website when you click on a link and get the 404 error Page Not Found. 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. This means that response will come faster, and no unnecessary resources will be consumed by CMS. SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. If you have multiple accounts, use the Consolidation Tool to merge your content. Always make sure Single & Double quotes involved in the queries as expected by the standard format. The tool should be recompiled to change the port.

Ecgs Bushing Tacoma, Sysco Frozen Nutritional Treat Nutritional Information, Vinegar Shortage 2021, Articles B