Copyright | Say for example Schedule the Query result in excel format to some email account weekly once. WebIn 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 2. 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. Microsoft Access DB Format Some of the Query builder queries to explore the BusinessObjects repository. 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'. For 4.0 SP4 Patch 6 Text Format, Hi Dymytri 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. Select Recent Sources to select from a data source you have been working with. 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/. All I changed is theSI_ID of the folder and it didn't work. query builder, cms, query, csv, export, tool, bi, bi platform, server, cms metadata , KBA , BI-BIP-SRV , CMS / Auditing issues (excl. Version-12.0.1100.0, Culture-neutral, I also have not found where I can see what objects are available. Its because you dont have rights to view the folders in the environment you are looikng in (viz. The document exists in the FRS but the link doesnt exist in the CMS. Is there a BO4 version of these SQL examples ? 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. The purpose of the tool is to simplify building CMS metadata queries and providepossiblyto export the result to Excel. I dont have Excel open, and double checked task mgr too. Im not a coder so i cannot modify your source code. Am getting the same error (could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2 [etc]). Please help. However, Query Builder has its limitations and you will face situations where Query Builder is not enough. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. Several fields are missing in the results. With the graphical interface, users can create requests by using predefined objects and filters, rather than having to type in the technical terms. Hello Manikandan, I work in Dallas and I thank you for writing this blog post. 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. The second module, 360Eyes, allows you to go further and fill the gaps that Query Builder doesnt. Make sure you are using a user credentials that is part of Administrator user group in order to gain access to all the repository objects. If you want, you can modify the file name. I think it's not possible from CMS query at all. 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. I would like to quicly identify any others. Dont wait, create your SAP Universal ID now! 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. error. This tool could help me significantly. 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. The tool has been desupported. Wasy. An Error occured in sending the commanf to the application. SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. FROM CI_SYSTEMOBJECTS WHERE SI_KIND= Event, SI_SCHEDULEINFO.SI_DEPENDENCIES.SI_TOTAL > 0, SELECT SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER, SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS, SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS. > How does it interface with the server? additional info : I use LDAP identification could it be a problem ? 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. You can use command line version of the tool to generate the Excel file 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. Alerting is not available for unauthorized users, Right click and copy the link to share this comment. Thanks for this SO USEFUL tool ;o) The report works so far and I managed to add the logo. Select Data > Connections & Properties > Queries tab, right click the query, and then select Edit. The problem is, that the logo doesn't show up after exporting it to Excel. 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. The source code is available, you can try to compile for R2. 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) Note that this information is stored in universe files in BO file repository, not in CMS. Is is possible to query and find a particular universe object used in any reports. However, there are only three tables and there are vast amounts of information stored in each one, making retrieving the data very difficult. Unable to connect to CMC server:6400. InfoStore Query Builder (with export to Excel) The purpose of the tool is to simplify building CMS metadata queries and provide possibly to export the result to 1354397 - How to retrieve information of report instances using Query Builder? I use the odbc connector on my pc with ms access to create queries and then publish them on a switchboard. Need your help: Is there some kind of a limit on the maximum number of returned rows in the tool? https://bukhantsov.org/tools/QueryBuilder4.zip. But now when i tried to extract the information into Excel, even though there is not Excel file opened it gives an error message File is used by another process. Now well import the Inventory data from a file. A very good handy tool to query metadata results. I am running it on BI 4.0 SP4 FP20 and it works fine, but To edit a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. I am getting error when exporting to excel : Could not save file, file is used by another process. To avoid confusion, its important to know which environment you are currently in, Excel or Power Query, at any point in time. Ill be exploring further. You can change the limit using option TOP: e.g. Again, there is no documentation to help you and technical knowledge is still needed given mapping the InfoObjects to a universe can be challenging. But it is throwing errors. 2 Is there absolutely no way to get which DB objects are used in a Crystal Report (I know this question has already been answered before, but just want to confirm) 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. IsAllowed takes the GroupID (or UserID) and the right ID. 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. So far so good on our BI4.0 SP6 installation. Neither the request nor the results can be stored. 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. 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. It is commonly used by SAP BusinessObjects administrators and developers looking for information about their users, reports, and universes. WebBuilding queries Feature Web Intelligence HTML Web Intelligence App let Web Intelli gence Rich Client Build queries on an Analysis View data source No Yes Yes Build queries on Excel files saved locally No No Yes Build queries on Excel files saved to the CMS * Yes Yes Yes Build queries on SAP HANA views Yes Yes In Connected mode only. PublicKeyToken-692fbea5521e1304 or one of its dependencies. Dear Matthew, again a useful page/info by you, I can't open/view the note 1895241. Edit a query from the Queries & Connections pane. Any output can also be retrieved by a WebI document. Is there a compiled version for 12.3.0.601? http://www.howtogeek.com/125045/how-to-easily-send-emails-from-the-windows-task-scheduler/. You should go with SI_PARENTID instead if you are interested only in documents. You can export the contents of your report to a new or existing Microsoft Excel workbook, and then build charts and Is there a way to query and return what tables/fields a report is using?? 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. I am using below query: SELECT SI_ID,SI_NAME, LAST_RUN_TIME FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER = 5698. You may want to just start from scratch. Login to Query Builder with Administrator account using the How can I get a list of all of the fields in these tables? 3 And finally, I work mostly with Business Views. https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. Or you can select Home and then select a command in the New Query group. All the users within and under a particular group. With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. Could not load file or assembly CrystalDecisions.Enterprise.Framework,Version=14.0.2000.0, Culture=Neutral, PublicKeyToken=692fbea5521e1304 or one of its dependencies. Planning a BI 4.1, Have you ever wanted to know what the differences were between a Business Objects Universe in 2 different environments? You can use the DB system tables like v$sql for oracle. 2) Create a Data Store by making a connection to a database sat SQL SERVER 2008 in this case. These tables are encrypted in such a way that the information stored in these tables cannot be readable using conventional SQL query tools. Select New Source to add a data source. So far Ive only used it to export a list of users with name, email and last logon time, to Excel. I am curious if it is possible to filter on child objects. I'm guessing InfoSteward stores them somewhere else in the CMS? You have to copy-paste each individual name in your list. This is the most common way to create a query. If you have multiple accounts, use the Consolidation Tool to merge your content. You can run the query builder statements directly from this tool Yes, the tool requires .NET 3.5. The query uses objects from two different levels Level 0 and Level 1. Security profiles,but not able to get the name of the each DATA Security Profile name from the query. Let me know if this resolves the issue. And is there any way for exporting this data? It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. I also get this strange message. You can setup BO auditing, this will give you precise information who is running what. In Excel you can do this by using the Text to Columns feature of the ribbon. 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. Thank you so much and really really appreciate your efforts. Container is a hierarchical property. In the Query Options dialog box, on the left side, under the CURRENTWORKBOOKsection, select Data Load. 1. 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. These query builder posts have been extremely beneficial after being thrown into the SAP BOBJ admin world. Yes. This will I am getting the error: I apologies, I obviously didn't look hard enough. Do one of the following. Decide how you want to import the data, and then selectOK. For more information about using this dialog box, select the question mark (?). An added extra here is that with 360Eyes, you will be able to track inconsistencies between the CMS and FRS. Edit a query from the Query Properties dialog box. 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. CrystalDecisions.Enterprise.Framework.dll I am looking for a query to get information of most accessed reports with report folder excluding shortcuts and reports instances. You might choose this command to try out the Power Query Editor independent of an external data source. Privacy | Create an Excel Export Template The first step in this process is to create an Excel that contains a table for your exported data to be inserted into. Its particularly important to clarify the difference between a worksheet of data, and a worksheet loaded from the Power Query Editor. The default behavior is to not update relationships. Please suggest me. This command is just like the Data > Recent Sources command in the Excel ribbon.
