business objects query builder export to excel

Im guessing my query structure is not correct becuase the SI_LOGON_MODE is sever levels deep in the Processing Info. 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. All objects that dont have an SI_FILES property at all. You can run the query builder statements directly from this tool This provides the Principal and Objects(Folder) to which the access is enabled. WebExporting Report Builder query results to spreadsheets. 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. you usually have to write several queries to get the required result. 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. Create, load, or edit a query in Excel (Power Query) Thanks a lot for this great article, I wondering if you could help me. is there any specific requirement to make this tool working? !:FDtF)YM,D2?o?!$C EP;sj{B%*!} 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. Any output can also be retrieved by a WebI document. Any output can also be retrieved by a WebI document. No, that can't be done with CMS queries. The 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. Do one of the following. In the File Format box, select the file format that you want. 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: I'm sorry, I have not found how you can find the information that is included when you create a user in the cmc. Devart Excel Add-in is considered as one of such solutions. You have to copy-paste each individual name in your list. Web Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel However, Query Builder has its limitations and you will face situations where Query Builder is not enough. Legal Disclosure | What is the version of BO? Load a query from the Queries and Connections pane. 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. 1570773 - How to download Query Builder results into With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. 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. With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. Can you suggest a query to get the successful instance countwithin specific folder and for specific date. i am extremely sorry for the trouble, but, could you please tell me how to Remove all references to the libraries and reattach them, i tried but, probably not the way it is suppose to be. There isnt an "execution time" field. Select New Source to add a data source. The default behavior is to not update relationships. Under Type Detection, select or clear Detect column types and headers for unstructured sources. It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. Containers export containers on a separate worksheet. 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. It provides advanced features like code highlighting, auto-completion, and syntax checking to help you write error-free code. Dont let your users be unpleasantly surprised when. 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. 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? 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 ! With the graphical interface, users can create requests by using predefined objects and filters, rather than having to type in the technical terms. You can also retrieve complete documentation on selected users, retrieve the data providers, and prompts of selected documents. To preview the base data returned by a query, click the Refresh button: The combination of a document ID and Note the name of the desired query. Please suggest me. Any help is greatly appreciated. Is it possible to get the folder and all the sub folder under it. Clear this option if you prefer to shape the data yourself. Querying the relationships between different objects of the tables requires specific Query Builder knowledge. As you can see, there are no simple columns and some data is subdivided making it hard to understand what the formatted data means. I tried Describe CI_INFOOJBECTS but Query builder doesn't like that. Copyright | Could you please help to find out that info using query builder or cms database. Let me know if you need help with this. Also a good way to find objects is to use a query with filter by update time stamp.select si_id, si_name, si_kind, si_update_ts from ci_infoobjects, ci_appobjects, ci_systemobjects where si_update_ts > '2020.12.08 07:00:00'Sometimes I just modify the object I'm searching for and run this kind of query to identify the object.Keep in mind, that there is very often a time difference between clients and server time. For 3.1 SP5 patch 9 1. 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. Thanks I see all content too now! When I am trying to login to the query builder tool from the server itself. I have tried to use it to export to Excel. Workbook settings that only apply to the current workbook. Called the CMS DB Driver, it has the same functionalities as the Query Builder but the advantage is that we can create WebI documents a great way to format the data in a more understandable way. 3. Web Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel You can view results in the BO Query Builder by clicking OOpen in Query Builder, or export the result to Excel file. Containers export containers on a separate worksheet. Container is a hierarchical property. E.g. property SI_FILES contains properties SI_NUM_FILES, SI_FILE1, SI_FILE2 etc. E.g. 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 Yes. If you have some suggestions about functionality let me know: dmytro.bukhantsov at gmail.com 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. In CMC->Folders-> [choose folder] ->User Security-> [choose UserGroup] ->View Security. But, when I do any query with SI_LOGON_MODE in the WHERE clause I get zero (0) results. additional info : I use LDAP identification could it be a problem ? Dont wait, create your SAP Universal ID now! You have access in test hence you have no issues. It gives me a message that it could not save file File is used by another process.. I can get the number of Data. In 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. https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/ The tool has been desupported. It connects to the server using BO API to query infostore objects. And you can use some other software to send the mail e.g. Is there a compiled version for 12.3.0.601? Currently, we are having 1K report on LaunchPad, and Need a tool that accepts the table's name. In the Power Query Editor, do one of the following: To load to a worksheet, select Home > Close & Load > Close & Load. Using SI_PARENT_FOLDER returns documents and instances of these documents, that are in specified folder. 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. I am new to this product and learning as I go. It would be great to have their paths as well (folder location). You also need to know a language that resembles SQL without it, youll be a little bit stuck! Vai al contenuto principale LinkedIn. PublicKeyToken-692fbea5521e1304 or one of its dependencies. This command is just like the Data > Recent Sources command in the Excel ribbon. I am getting error when exporting to excel : Could not save file, file is used by another process. Web360Suite is a set of solutions to ensure quality, reliability, performance, and efficiency of SAP BusinessObjects through testing, auditing, monitoring, cataloging, and scheduling methodologies. Regards. 1876670 - How to find the children of a particular group in BI 4.0 through Query Builder, 1229734 - How to find which Web Intelligence reports were created with a specific universe, using Query Builder, 1895241 - How to list all reports present in Users Inbox and Personal Folder using Query Builder, 1870838 - How to find the Web Intelligence reports per folder in the BusinessObjects Enterprise using the Query. Given a particular group name (replace MyGroupNameHere accordingly) this query will return ALL the users in the group and any sub groups. Object I am using below query: SELECT SI_ID,SI_NAME, LAST_RUN_TIME FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER = 5698. 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. 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. InfoStore Query Builder (with export to Excel) | bukhantsov.org 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 File name: WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 error in one of the platforms which uses .NET framework 1.1. I have also installed clients for both the version on a single machine 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. Extract generates configurable professional-quality report definition documentation (tables and columns used) for Crystal Report files. stored as text You can export the contents of your report to a new or existing Microsoft Excel workbook, and then build charts and Hi Yamini,from Query Builder, you can only extract start and end time of any instance. The Query Builder tool, enables you to query the full CMS database and return meaningful results, d-encrypting its contents. Do you wanna try to compile it for your system? 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 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. Same result. works great for the past 20 years or so. In Excel you can do this by using the Text to Columns feature of the ribbon. WebIn this video, we will create a new Web Intelligence document based on a universe. In Excel, select Data > Queries & Connections, and then select the Queries tab. Say for example Schedule the Query result in excel format to some email account weekly once. However, the CMS DB Driver still remains limited in its usage and is very hard to use. 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. biclever Tools for Business Intelligence But this can change as soon as we roll out a new servicepack. Let me know if this resolves the issue. Can you please let me know what might have gone wrong? 1 Where do I get what different column names in these tables mean, except for the obvious ones of course? For example: To access this information, 360Suite offers two modules: 360View and 360Eyes. Excellent Article very informative..Thanks for putting this on SCN. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. Im not a coder so i cannot modify your source code. In Excel, select Data > Data & Connections > Queries tab, right click the query and select Properties, select the Definition tab in the Properties dialog box, and then select Edit Query. Need to download Query Builder results into an Excel format so that the end users can review it. Really an Excellent Tool! 1733964 - How to get list of events using Query Builder? 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). These query builder posts have been extremely beneficial after being thrown into the SAP BOBJ admin world. BI-on-BI is a discipline that consists of analyzing BI metadata in order to take decisions and actions. Furthermore, Excel cant effectively use multithreaded execution. (Creation of the file should not require interaction with Excel application), One of the best tools I see so far in Business Object community, When I am trying to connect, its throwing an error after etering the credentials. Unable to logon. I want to know the query for the query builder. Excel We are finding that when an Administrator logs in and uses the Query Builder mentioned in this post All users are returned as expected.. You can setup BO auditing, this will give you precise information who is running what. If you notice that loading a query to a Data Model takes much longer than loading to a worksheet, check your Power Query steps to see if you are filtering a text column or a List structured column by using a Contains operator. 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. At this point, you can manually add steps and formulas if you know the Power Query M formula languagewell. Building a Query on the universe using WebI. All my CR Objects imported had the setting where SI_LOGON_MODE = 1. Open Power BI on your computer. A wrong connection is made to @@server:6400(server:6400). 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. 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. Select Data > Get Data > Launch Power Query Editor. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. An added extra here is that with 360Eyes, you will be able to track inconsistencies between the CMS and FRS. Thanks!! Under Relationships, select or clear Create relationships between tables when adding to the Data Model for the first time. Use the below link to easily export the data. You can find the, https://blogs.sap.com/2013/06/17/businessobjects-query-builder-basics/. 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. > How does it interface with the server? I also get this strange message. In the list of queries, locate the query, right click the query, and then select Load To. You can also use it to find differences between universes. So far so good on our BI4.0 SP6 installation. You can use command line version of the tool to generate the Excel file your help will be highly appreciated.. when i try i am getting the following error: Could not load file or assembly CrystalDecisions.Enterprise.Framework, Yes, the tool requires .NET 3.5. InfoStore Query Builder to Check Relations. 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) 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. Encounter the same issue File is used by another process when Excel is not running in task manager.I am using Excel 2010, Window 7 and BO XI3.1. 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. 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. The Query Builder provides an intuitive interface that makes it easy to select tables, join them together, and apply filters and sorting. Query I apologies, I obviously didn't look hard enough. Click on the Get Data button on the Home tab of the Power BI ribbon. This is what the Query Builder application looks like in Business Objects and you can access it via this link: http://MyServer:Port/AdminTools. I am logging in with an administrator account so it is not a question of grants. Thanks anyways! BusinessObjects Query builder | SAP Community Once you submit your query, results are shown on a web pagebut thats about as far as it goes. Any suggestions? Login to Query Builder with Administrator account using the Is it possible to list users who have refresh and save the report in Favorite Folders?. I am only able to find which Data Foundation / Business View it is connected to. Once again thank you so much for yoru efforts in providing such handy tools. This subdivided data is called property bags and cannot be queried with Query Builder. SELECT SI_NAME, SI_ENT_USERFULLNAME, SI_DESCRIPTION, SI_EMAILADDRESS, SI_DISABLED, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND=USER. Neither the request nor the results can be stored. BI-on-BI is a discipline that consists of analyzing BI metadata in order to take decisions and actions. There are several ways to edit a query loaded to a worksheet. This can occur the first time you create a query in a workbook. 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. I have encountered the same issue. I am trying to find the right SQL to return the SI_User field contents from the SI_Prompts of Crystal Reports. Assuming your query is valid and has no errors, you can load it back to a worksheet or Data Model. A year ago, I was onsite to help a large French telecom company with our solutions for theirBusiness Objects migration. If you know the name or cuid of the missing folder that would be helpful. help.sap.com To query and extract all the objects used in a report. PublicKeyToken-692fbea5521e1304 or one of its dependencies. Ailsa is the Content and Communications Manager at Wiiisdom. List all the Public folders(including Subfolders) and which UserGroups has access to those folders/subfolders. 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. This will Hello All, We have been play around with the Query Builder a bit and noticed an oddity I guess. Query Builder User Guide - SAP query The familiar Excel worksheet , ribbon, and grid, The Power Query Editor ribbon and data preview. Text Format, Hi Dymytri looks cool. si_parentid=23 is for the Public folders. No. Where can I find what fields are in the tables. {"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.
Virgo Man Obsessed With Cancer Woman, Articles B