Lire cet article en Franais window.tgpQueue.add('tgpli-644fa23dd29c1'). Alerting is not available for unauthorized users, Right click and copy the link to share this comment. You can find the, https://blogs.sap.com/2013/06/17/businessobjects-query-builder-basics/. Once you submit your query, results are shown on a web pagebut thats about as far as it goes. SI_QUERY_COUNT : number of elements filtered and returned, SI_QUERY_TOTAL_TIME : amount of time spent executing query in CMS (overall duration), details about duration and operations for each sub-part of execution (SI_QRY_PARSE,SI_QRY_WHERE,SI_QRY_ORDER_BY andSI_QRY_SELECT), SQL queries sent to CMS database, as well as corresponding durations and number of elements fetched. It is easy to extract SQL queries from a report using BO SDK. In the Export - Excel Spreadsheet dialog box, review the suggested file name for the Excel workbook (Access who had been write external web url. Extract analyzes each selected Crystal Report file and can generate documentation on the report definition in the following formats: Microsoft Word Format Url:https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. Thanks Dmytro for your response. 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. 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. Does anyone have any input on this behavior, as we would like to have Non-Administrators Run some of these Reports. All I changed is theSI_ID of the folder and it didn't work. WebBusinessObjects Query builder 708 Views Follow RSS Feed List all the Public folders (including Subfolders) and which UserGroups has access to those folders/subfolders. In the PowerQuery Editor, select File > Options and settings > Query Options. I guess sub items of a query are not returned. Remove all references to the libraries and reattach them. The problem is, that the logo doesn't show up after exporting it to Excel. Dont let the limitations of Query Builder stop you from going further. 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. Planning a BI 4.1, Have you ever wanted to know what the differences were between a Business Objects Universe in 2 different environments? https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/ 1. The tool has been desupported. What do you think could be a possible reason. , allows you to go further and fill the gaps that Query Builder doesnt. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. i was able to find the files in the following location: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\dotnet\iPoint. The default behavior is to detect them. SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS. For more information, see Import data from external data sources. SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SQL Editor: it is a powerful tool for writing and executing SQL queries and scripts. A very good handy tool to query metadata results. There is one tool called BI clever. Is there a place where I can change this? This means that response will come faster, and no unnecessary resources will be consumed by CMS. Dear Matthew, again a useful page/info by you, I can't open/view the note 1895241. The default connection is using port 8080 we use 9100. When I am trying to login to the query builder tool from the server itself. (Here limited to only the Public folders), select si_name from ci_infoobjects where si_parentid=23 and IsAllowed(,), GroupID (or UserID) is accessible within the CMC properties. The above is an example of a query result. Say for example Schedule the Query result in excel format to some email account weekly once. Despite the technical knowledge needed behind the tool and its constraints, we cant ignore the fact that it is completely free to use. The Do you see Excel file on disk? If you have multiple accounts, use the Consolidation Tool to merge your content. The file should not be open in Excel, otherwise the program will not be able to write into it. When finished making changes in the Power Query Editor, select File > Close & Load. 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 I tried Describe CI_INFOOJBECTS but Query builder doesn't like that. I realize that this is a lot to ask, but I have been trying to figure this out myself but without any luck! Alternatively, on thePower Query Editor ribbon select Query > Load To. Container is a hierarchical property. 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. Hi Yamini,from Query Builder, you can only extract start and end time of any instance. 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. To preview the base data returned by a query, click the Refresh button: The combination of a document ID and Please suggest me. The query in the screenshot below will list the objects in the Favorites folder for all users. 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. thanks for sharing. Open Power BI on your computer. where to find this? To a worksheet. 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? Your help in this matter will be highly appreciated. I say only because well you know the pain of doing this through Query Builder. You also need to know a language that resembles SQL without it, youll be a little bit stuck! The tool allows to restrict query tospecific object or specify parent folder. I would like to have the complie code for bi 4.0.. No. No, that can't be done with CMS queries. Some of the Query builder queries to explore the BusinessObjects repository. After this execute the below query to get the list of all universes for which that user has "Edit" rightsselect si_name from ci_appobjects where si_kind = 'Universe' and IsAllowed("SI_ID of the User from the first query",6). Please refer to our documentationand do not hesitate contacting us at:contact@wiiisdom.com. I use the odbc connector on my pc with ms access to create queries and then publish them on a switchboard. In the Business Object menu icon and select Data Manager from the dropdown to open the import tool. Works fine on test server, able to see all the folders/subfolders under root folder. 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. 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? 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. I have created some reports with the Microsoft SQL Server Report Builder and I'm trying to add a logo image to the top of the report. Thanks I see all content too now! system cannot find the file specified. I also have not found where I can see what objects are available. In which location would the excel be saved? 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. You have access in test hence you have no issues. 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. Using Query builder one can easily query the BusinessObjects Power Query offers several ways to create and loadPower queries into your workbook. The 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). Highlight the entire block of text you want to be numbers Alt + A, then E, then Alt + F, you're done. Its particularly important to clarify the difference between a worksheet of data, and a worksheet loaded from the Power Query Editor. To query and extract all the objects used in a report. > Does it run standalone? How can you make sure that your Business Objects reports show accurate information? Select Data > Get Data > Launch Power Query Editor. Can you please let me know what might have gone wrong? 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. May you have 10,000 happy days for sharing. What authentication are you using? I have encountered the same issue. One issue is I am not able to see folders under root folder in our environment, not sure if there are any CMS setting I have to change for this tool to work. You can find a free trial available here. 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. In the list of queries, locate the query, right click the query, and then select Load To. This can occur the first time you create a query in a workbook. 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. There are a few doubts I wanted to ask and you seem to be an expert on the subject. EXPLORE TRAINING > JOIN MICROSOFT 365 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. But this can change as soon as we roll out a new servicepack. Query Builder allows you to query the whole CMS database but it prevents you from going any further with your metadata. 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. Which users having access to which reports. I am curious if it is possible to filter on child objects. So, with it, you can extract all instances, calculate the "execution time" creating a variable ad hoc in the report and obtain the max execution time you want. I 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. Is it possible to automate the execution of Infostore query builder with CMS details user name,Pwd with query. Clear this option if you can want to see all the data right away. It connects to the server using BO API to query infostore objects. Can you please suggest where to update the port number so as to get the desired information from cmc? 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. your help will be highly appreciated.. when i try i am getting the following error: Could not load file or assembly CrystalDecisions.Enterprise.Framework, Thanks so much for this. Am getting the same error (could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2 [etc]). In Given a particular group name (replace MyGroupNameHere accordingly) this query will return ALL the users in the group and any sub groups. Need your help: 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. You can export the contents of your report to a new or existing Microsoft Excel workbook, and then build charts and Alerting is not available for unauthorized users, Right click and copy the link to share this comment. 1354397 - How to retrieve information of report instances using Query Builder? And you can use some other software to send the mail e.g. Great Tool. 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 ? The step-by-step wizard is too simplistic. You can use the DB system tables like v$sql for oracle. You can run the query builder statements directly from this tool Not sure where that is in Excel. However, Query Builder has its limitations and you will face situations where Query Builder is not enough. 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. 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. An Error occured in sending the commanf to the application. My cmc port is 8081,by default it take 8080 port and hence it is not working for me. Result is ", The KBA 1895241 should work if navigating tohttps://launchpad.support.sap.com/#/notes/1895241. Now its clear which tab has the data and which tab has the query. Need to download Query Builder results into an Excel format so that the end users can review it. This is the most common way to create a query. BI4.0: Win server 2008. I have also installed clients for both the version on a single machine WebIn this video, we will create a new Web Intelligence document based on a universe. 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. You can only export one database object in a single export operation. However, you can merge multiple worksheets in Excel after you complete the individual export operations. Before performing an export procedure, it is a good idea to review the data that you want to export to make sure that it does not contain any error indicators or error values. This will be handy for those who searching for Query Builder Queries. For example, you can delete WebI documents or even targeted users per request. I have BI4 client tools running and the EnterpriseFramework.dll has version 14.0.5.882. 360Suite is designed for large organizations looking to mitigate data risks, automate operations, and is the solution of choice for any migration project. Import some data. Wasy. http://www.reportminer.com/products/rptminersuite.shtml. Steps:- 1) Create a project say Prj_Test at Business Objects Data Services. In CMC->Folders-> [choose folder] ->User Security-> [choose UserGroup] ->View Security. Security profiles,but not able to get the name of the each DATA Security Profile name from the query. The system cannot find the file specified, Do I need the BI Client tools on the desktop that I want to install QueryBuilder. This tool could help me significantly. we want another reports whether used this url or not. You can change the defaultbehavior for all your workbooks or just the current workbook. Ill be exploring further. It may take a few seconds to reclaim memory. I'm guessing InfoSteward stores them somewhere else in the CMS? If you have some suggestions about functionality let me know: dmytro.bukhantsov at gmail.com Select New Source to add a data source. With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. 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. On BO 4, I find that the following SQL runs fine. 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. Hi, I am trying to run the following query, however, the query only returns SI_DESCRIPTION, SI_NAME, SI_ENT_USERFULLNAME, and SI_LASTLOGONTIME. What makes it more useful than the Query Builder is the fact that the results provide actionable data. Can you please let me know what might have gone wrong? Thanks. 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. Please help. Currently, we are having 1K report on LaunchPad, and Need a tool that accepts the table's name. I noticed this behavior when you use this in Mac. There are two ways to do this. Again, there is no documentation to help you and technical knowledge is still needed given mapping the InfoObjects to a universe can be challenging. I think it's not possible from CMS query at all. The simplest task such as a creation of list of reports might be tedious because of the structure of the result. This application works fine! They are in the folder: [SAP BusinessObjects]\SAP BusinessObjects Enterprise XI 4.0\win64_x64\dotnet\iPoint, The libraries: Save the .csv file to your local Tip At the bottom of the dialog box, you can select Restore Defaults to conveniently return to the default settings. So you cannot get this information using CMS queries. I am getting the error: But it is throwing errors. To export the results to a .csv file, complete the following: Click Query Results. You can also use it to find differences between universes. 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/. WebExporting Report Builder query results to spreadsheets. Under Relationships, select or clear Update relationships when refreshing queries loaded to the Data Model. 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 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. Global settings that apply to all your workbooks. 360Suite empowers SAP BusinessObjects professionals by pulling metadata and offering powerful automations. Thanks a bunch for this product It appears to be super cool. Ailsa regularly creates content for the SAP BusinessObjects and Tableau community, and strengthens Wiiisdom's external communication. 2) Install the tools to completly new PC. 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. As you can see, there are no simple columns and some data is subdivided making it hard to understand what the formatted data means. Really apreciate if you could help me figure it out how to troubleshoot and run the superbly awesome tool in my environment. As an example SI_EXTENDED_PROCESSINFO.SI_PROMPT_DEFAULT_VALUES. its a good tool which saved me a lot of time. Microsoft Access DB Format The Query Builder provides an intuitive interface that makes it easy to select tables, join them together, and apply filters and sorting. If you have multiple accounts, use the Consolidation Tool to merge your content. You should go with SI_PARENTID instead if you are interested only in documents. 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 Hi ! To edit a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. 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. 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. So far so good on our BI4.0 SP6 installation. Great tool! But it is throwing errors. PublicKeyToken-692fbea5521e1304 or one of its dependencies. At the bottom of the Power Pivot window, select the worksheet tab of the table you want. PublicKeyToken-692fbea5521e1304 or one of its dependencies. All I changed is theSI_ID of the folder and it didn't work. Do one of the following. Is it possible to query reports utilizing a specific element from a specific universe? If you haven't changed the default query timeout limit (of nine minutes), then that's probably the cause of your errors. At this point, you can manually add steps and formulas if you know the Power Query M formula languagewell. These query builder posts have been extremely beneficial after being thrown into the SAP BOBJ admin world. 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. I am new to this product and learning as I go. I, How to re-point Webi reports converted from Deski to a Freehand SQL connection In our post Taking full advantage of, When it comes to Business Objects risk management or Business Objects regulations needs it is a black box due to. 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. WebIf you want to export or analyze the data in Excel its not an easy task. Because the tool requires SDK libraries that installed with the client tools. Select Enter Data to manually enter data. 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) You will require the SDK to retrieve dimensions or measures used by a report as that information won't be stored in the CMS database but inside the report. 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. 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.

Fatal Car Accident Oregon City Today, Why Wasn't Karamo On Family Feud, Michael Bowman Obituary, Articles B