Visit SAP Support Portal's SAP Notes and KBA Search. For custom installations, 3rd party authentication and single sign on, an initial setup may be required. Edit a query from the Query Properties dialog box. 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. Step 3: Under Metadata panel you will see a data model which contains tables, columns and measures. 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. The 360View Drill Down function is a tool that resembles the most to Query Builder in its methodology as it only requests data from the CMS. 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. where to find this? Good work. Great article !!! This command is just like the Data > Recent Sources command in the Excel ribbon. 1735539 - Query to list all Universes for which a user has "Edit Objects" rights via Query Builder. Is there a way to show all the reports that one user is running? I tried Describe CI_INFOOJBECTS but Query builder doesn't like that. Click on the Get Data button on the Home tab of the Power BI ribbon. You can suggest a query to get the group, universes, and folders. With the graphical interface, users can create requests by using predefined objects and filters, rather than having to type in the technical terms. There is one tool called BI clever. If you have multiple accounts, use the Consolidation Tool to merge your content. I have encountered the same issue. to parameterize the cms name, user name, password, business layer and data foundation path; to include data foundation export option; to have the option of This is what the Query Builder application looks like in Business Objects and you can access it via this link: http://MyServer:Port/AdminTools. 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. To close the Power Pivot window, select File > Close. This is a great tool ! By adding the query filter Ancestor = 18, we get the all the objects that only belong to Users personal folder. Under Type Detection, select or clear Detect column types and headers for unstructured sources. It appears to be exactly what i am looking for1. 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. A Data Model can have many tables. And I have checked no excel sheet or excel process running on my machine. How to obtain query processing statistics, Use flag "SHOW STATS" at the end of the query to get detailed statistics about execution in CMS example:select SI_NAME, SI_ID from ci_infoobjects where SI_KIND='Webi' order by SI_SIZE desc SHOW STATS. The system cannot find the file specified. looks cool. Not sure where that is in Excel. I think it's not possible from CMS query at all. Several fields are missing in the results. I need more information on how to use this tool. How does it interface with the server? No. For more information, see Import data from external data sources. I am having the same question, Can you recommend a tool or a way to get the report's query. It gives me a message that it could not save file File is used by another process.. I am new to this product and learning as I go. 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. Ensure SAP BusinessObjects User Adoption! Remove all references to the libraries and reattach them. 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. Know which environment you're in Power Query is well-integrated into the Excel user interface, especially when you import data, work with connections, and edit Pivot Tables, Excel tables, and named ranges. 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'). This application works fine! 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. Query is fine in Query builder, suspect its a security restrciyion in Excel preventing it being called from your app. 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. You can find the, https://blogs.sap.com/2013/06/17/businessobjects-query-builder-basics/. It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. The one here should work. 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. when I am trying to login by opening the exe, for both the versions its giving the error Unable to logon. your help will be highly appreciated.. when i try i am getting the following error: Could not load file or assembly CrystalDecisions.Enterprise.Framework, 1733964 - How to get list of events using Query Builder? A third-party tool will also give you the possibility to carry out complex requests that arent possible with Query Builder. 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. Login to Query Builder with Administrator account using the following link: http://servername:portnumber/AdminTools, 2. With InfoSteward, we use an instance of IPS_CMS and you can query it with the same tools as a BOBJ CMS. There are several ways to edit a query loaded to a worksheet. I am getting the error: Could not connect to the server. Regards. These tables are encrypted in such a way that the information stored in these tables cannot be readable using conventional SQL query tools. 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. Some of the Query builder queries to explore the BusinessObjects repository. To open the Data Model, select Power Pivot>Manage. system cannot find the file specified. property SI_FILES contains properties SI_NUM_FILES, SI_FILE1, SI_FILE2 etc. CMS Query Builder Get Free The tool allows to Please close Excel and try again. The report works so far and I managed to add the logo. How can you make sure that your Business Objects reports show accurate information? Is it possible to get report level data like dimensions and the report query generated for a report from the repository ?Thanks !! Workbook settings that only apply to the current workbook. You can also set default query load settings in the Query Options window. is there any specific requirement to make this tool working? Ive been using this great tool for some time but ive just seen the error. At this point, you can manually add steps and formulas if you know the Power Query M formula languagewell. Is there some kind of a limit on the maximum number of returned rows in the tool? 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. . Local Machine: Win 7. The default behavior is to not update relationships. Could not load file or assembly CrystalDecisions.Enterprise.Framework, 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. Extract analyzes each selected Crystal Report file and can generate documentation on the report definition in the following formats: Microsoft Word Format Once you submit your query, results are shown on a web pagebut thats about as far as it goes. , allows you to go further and fill the gaps that Query Builder doesnt. As a workaround, try using a different operator such as Equals or Begins With. 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. 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. File name: WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 error in one of the platforms which uses .NET framework 1.1. Ihave 12.1.0 version CMC. The report object defines the underlying data model and specifies which database tables and fields to pull data from. https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/ The Query Builder tool, enables you to query the full CMS database and return meaningful results, d-encrypting its contents. Edit a query from data in Excel worksheet. I am using below query: SELECT SI_ID,SI_NAME, LAST_RUN_TIME FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER = 5698. Your help in this matter will be highly appreciated. It is easy to extract SQL queries from a report using BO SDK. Using SI_PARENT_FOLDER returns documents and instances of these documents, that are in specified folder. Login to Query Builder with Administrator account using the Hi Yamini,from Query Builder, you can only extract start and end time of any instance. 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. To preview the base data returned by a query, click the Refresh button: The combination of a document ID and Does it run standalone, or is added to an existing tool? Security profiles,but not able to get the name of the each DATA Security Profile name from the query. l@Mh/j. All I changed is theSI_ID of the folder and it didn't work. In The familiar Excel worksheet , ribbon, and grid, The Power Query Editor ribbon and data preview. query builder, cms, query, csv, export, tool, bi, bi platform, server, cms metadata , KBA , BI-BIP-SRV , CMS / Auditing issues (excl. 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. You also need to know a language that resembles SQL without it, youll be a little bit stuck! I have been using Query Builder only for a very short while. 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. Can i have a link for download the verison is works fine with Product Version: 12.3.0.601 (XI 3.1 SP3)? You can change the limit using option TOP: e.g. 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. Decide how you want to import the data, and then selectOK. For more information about using this dialog box, select the question mark (?). Import some data. Microsoft is aware of this problem and it is under investigation. 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 Excel you can do this by using the Text to Columns feature of the ribbon. and includes a section listing Process Info (including the report's parameter names, etc). Select New Source to add a data source. Once again thank you so much for yoru efforts in providing such handy tools. si_parentid=23 is for the Public folders. on opening the exe its giving me error could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2, Culture = nuetral. Works fine on test server, able to see all the folders/subfolders under root folder. However, the CMS DB Driver still remains limited in its usage and is very hard to use. 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. This will be handy for those who searching for Query Builder Queries. You can run the query builder statements directly from this tool Any output can also be retrieved by a WebI document. Hi Amir, here: bukhantsov.org/tools/QueryBuilder_src.zip. But it is throwing errors. The step-by-step wizard is too simplistic. In the Power Query Editor, select Home > Close & Load > Close & LoadTo. 4. I realize that this is a lot to ask, but I have been trying to figure this out myself but without any luck! Thanks a lot for this great article, I wondering if you could help me. Wasy. In the PowerQuery Editor, select File > Options and settings > Query Options. I noticed this behavior when you use this in Mac. Need your help: Version-12.0.1100.0, Culture-neutral, 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. 360Suite is designed for large organizations looking to mitigate data risks, automate operations, and is the solution of choice for any migration project. You have access in test hence you have no issues. I try this without success, 1) Restart server May you have 10,000 happy days for sharing. You can use command line version of the tool to generate the Excel file SELECT TOP 10000 * FROM ci_systemobjects. This only occurs when you load the data to a worksheet orData Modelfrom Power Query. works great for the past 20 years or so. BI-on-BI is a discipline that consists of analyzing BI metadata in order to take decisions and actions. Dont let the limitations of Query Builder stop you from going further. You can change the defaultbehavior for all your workbooks or just the current workbook. Also it does not have installer, you just unpack the tool in a preferred location and create a shortcut. Prod environment). Please suggest me. I dont have Excel open, and double checked task mgr too. Under Background Data, select or clear Allow data previews to download in the background. Dont wait, create your SAP Universal ID now! 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. And we are finding the same issue when running a Report using the data Access driver that when an Administrator Runs a Report All users are returned as expected, and when a NON-Administrator runs the same Report only the Users that are designated as Administrators and the User runs the report are returned in the report. its a good tool which saved me a lot of time. Version: XI Which users having access to which reports. WebSAP BusinessObjects Business Intelligence platform 4.2 ; SAP Crystal Server 2016 Keywords query builder, cms, query, csv, export, tool, bi, bi platform, server, cms I am getting error when exporting to excel : Could not save file, file is used by another process. I am running it on BI 4.0 SP4 FP20 and it works fine, but Please help.. 3 And finally, I work mostly with Business Views. Web Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel. The tool should be recompiled to change the port. Devart Excel Add-in is considered as one of such solutions. Thanks so much for the tool, I have been using it since quite a while and find it very useful. Just wondering if this has got a dependency on .Net framework. Actually I am trying to get the list of report under a folder and it subfolder but this works only at one level. 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). 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. Ailsa is the Content and Communications Manager at Wiiisdom. Query Builder doesnt have the means to detect these inconsistencies but 360Eyes can. The SAP Note/KBA you were trying to reach has been removed. 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. Tip Sometimes the Load To command is dimmed or disabled. If you have multiple accounts, use the Consolidation Tool to merge your content. Tip At the bottom of the dialog box, you can select Restore Defaults to conveniently return to the default settings. Open Power BI on your computer. Query Builder can be found at the below URL http://MyServer:Port/AdminTools. It might not work for other versions. 1 Where do I get what different column names in these tables mean, except for the obvious ones of course? 1. List all the Public folders(including Subfolders) and which UserGroups has access to those folders/subfolders. BI4.0: Win server 2008. I need to do this, as the BO4 REST service to retrieve this information takes 2-4 minutes to run, even with "lovInfo=false" at the end of the URL: http://YOUR_SERVER_NAME:6405/biprws/raylight/v1/documents/11729/parameters?lovInfo=false. Web Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel Excellent Article very informative..Thanks for putting this on SCN. Note that this information is stored in universe files in BO file repository, not in CMS. 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 Queries with si_ancestor conditions can run for a long time. To query and extract all the objects used in a report. Thanks. But it is throwing errors. I guess sub items of a query are not returned. Any suggestions? I will check it. Privacy | The default limit for a CMS query is 1000. Can you please let me know what might have gone wrong? Terms of use | 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 ? At Xoomworks BI we view the GB&Smith 360Suite of tools as administration intelligence for SAP BusinessObjects. 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 To a Data Model. Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel. This provides the Principal and Objects(Folder) to which the access is enabled. I am trying to fetch list of crystal reports from specific folder however I am getting duplicate records for example there are 104 reports but query builder shows 398 records. Power Query offers several ways to create and loadPower queries into your workbook. 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. There are a few doubts I wanted to ask and you seem to be an expert on the subject. This will Im guessing my query structure is not correct becuase the SI_LOGON_MODE is sever levels deep in the Processing Info. The simplest task such as a creation of list of reports might be tedious because of the structure of the result. Could not connect to the server. However I run into the same error:Could not save file, file is used by another process. If you want, you can modify the file name. Unable to connect to CMC server:6400. 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. Thanks for this SO USEFUL tool ;o) Now well import the Inventory data from a 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. I am attempting to isolate Crystal Report objects where the SI_LOGON_MODE 1. 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. Can you suggest a query to get the successful instance countwithin specific folder and for specific date. As an example SI_EXTENDED_PROCESSINFO.SI_PROMPT_DEFAULT_VALUES. Export Query Builder output in Excel format 2944 Views Follow RSS Feed Hi Experts, Is there a way to Export Query Builder output in Excel format? All my CR Objects imported had the setting where SI_LOGON_MODE = 1. Choose the account you want to sign in with. This can occur the first time you create a query in a workbook. Thanks so much for this. 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/. 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. !:FDtF)YM,D2?o?!$C EP;sj{B%*!} Could be that the InfoSteward Objects are store in a different table.You can try a query like:select si_id, si_name, si_kind from ci_infoobjects, ci_appobjects, ci_systemobjects where si_instance != 0This will return all instances, may be a good idea to insert a condition on the name: and si_name='sample'. but this SQL (which worked fine with BO3.x)fails miserably with a "Not a valid query." Web Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel Please help. 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. You can also use it to find differences between universes. Query Builder is one of the essential and interesting tools in BusinessObjects. PublicKeyToken-692fbea5521e1304 or one of its dependencies. hi, this looks interesting but it did not work on my laptop with windows XP SP2. Yes. All objects that dont have an SI_FILES property at all. 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. Note the name of the desired query. Create, load, or edit a query in Excel (Power Query), About the integration of Power Query into Excel, Edit the query of a table in a Data Model, Loading a query to a Data Model takes unusually long, Find out which data sources are used in a workbook data model, Use multiple tables to create a PivotTable. In Excel, select Data > Queries & Connections, and then select the Queries tab. An example of an impact analysis report identifying documents that contain specific Universe objects, SQL expressions, or variables. Can you please let me know what might have gone wrong? The problem is, that the logo doesn't show up after exporting it to Excel. Yes. 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. To export the results to a .csv file, complete the following: Click Query Results. Dont wait, create your SAP Universal ID now! A wrong connection is made to @@server:6400(server:6400). 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'm guessing InfoSteward stores them somewhere else in the CMS? I have BI4 client tools running and the EnterpriseFramework.dll has version 14.0.5.882. Some of the Query builder queries to explore the BusinessObjects repository. Probably I had an IE cache problem. How can I get the Instances which took the maximum time to execute? Anyone have a query for getting a list of scheduled recurring jobs for INFOSTEWARD? The Query Builder provides an intuitive interface that makes it easy to select tables, join them together, and apply filters and sorting.