Where can I find what fields are in the tables. This command is just like the Data > Recent Sources command in the Excel ribbon. This means that response will come faster, and no unnecessary resources will be consumed by CMS. https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/ CrystalDecisions.Enterprise.Framework.dll 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 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) However, Query Builder has its limitations and you will face situations where Query Builder is not enough. The simplest task such as a creation of list of reports might be tedious because of the structure of the result. For example these scripts you've provided, don't give an actual name. Thanks a lot. SQL Editor: it is a powerful tool for writing and executing SQL queries and scripts. Could not load file or assembly CrystalDecisions.Enterprise.Framework,Version=14.0.2000.0, Culture=Neutral, PublicKeyToken=692fbea5521e1304 or one of its dependencies. Version-12.0.1100.0, Culture-neutral, 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. Ailsa regularly creates content for the SAP BusinessObjects and Tableau community, and strengthens Wiiisdom's external communication. Hello ! All objects that dont have an SI_FILES property at all. Web Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel 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. 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. Great tool! You can change the defaultbehavior for all your workbooks or just the current workbook. 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. 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. How can I get a list of all of the fields in these tables? 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. 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. For example, you have to find a folder ID, before querying the list of objects in the folder. Create, load, or edit a query in Excel (Power Query) 360Suite empowers SAP BusinessObjects professionals by pulling metadata and offering powerful automations. Excel when I am trying to login by opening the exe, for both the versions its giving the error Unable to logon. Lire cet article en Franais window.tgpQueue.add('tgpli-644fa23dd29c1'). The Query Builder tool, enables you to query the full CMS database and return meaningful results, d-encrypting its contents. At the bottom of the Power Pivot window, select the worksheet tab of the table you want. 2) Create a Data Store by making a connection to a database sat SQL SERVER 2008 in this case. Could you try to close all excel windows and maybe EXCEL processes? 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. These query builder posts have been extremely beneficial after being thrown into the SAP BOBJ admin world. I tried your query, "To extract all the report names from specific folder". we want another reports whether used this url or not. Query Builder - Business Intelligence (BusinessObjects) Legal Disclosure |
To edit a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. Please refer to our documentationand do not hesitate contacting us at:contact@wiiisdom.com. The report object defines the underlying data model and specifies which database tables and fields to pull data from. As suggested earlier, it wold be great to walk me through troubleshooting the behavior. , allows you to go further and fill the gaps that Query Builder doesnt. Assuming your query is valid and has no errors, you can load it back to a worksheet or Data Model. Also tried on a server with platform files (BI4 SP05) and on a BI4.1 server. I do not know which tool can do that. Export Clear this option if you can want to see all the data right away. Export data to Excel - Microsoft Support BI Universe Export Business Layer object definitions to Excel Any suggestions? You might choose this command to try out the Power Query Editor independent of an external data source. In which location would the excel be saved? What I have tried so far (Image is Embedded and not from a Database or External): Login to Query Builder with Administrator account using the You can either create a query from imported data or create a blank query. 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. Click on the Get Data button on the Home tab of the Power BI ribbon. It is easy to extract SQL queries from a report using BO SDK. Export Power BI Data to Excel or CSV using Power Automate Visual #PowerBI #PowerAutomate #flow #Excel. The SAP Note/KBA you were trying to reach has been removed. I have encountered the same issue. On BO 4, I find that the following SQL runs fine. I loaded it onto my computer, as opposed to just running it from this site, and the error went away. Thank you! Working with Query Builder YouuseQueryBuildertoconstruct,submit,andtrackquerystatus.Youcansubscribetoeventsand 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. thanks for sharing. 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 I have also installed clients for both the version on a single machine Is there a place where I can change this? 2686851 - How to export query results from Query Remove all references to the libraries and reattach them. Reallt really appreciate your response on this. BusinessObjects Query builder queries | SAP Blogs Works fine on test server, able to see all the folders/subfolders under root folder. You can find the basic SQL language requests here. Just found this and unzipped onto my machine. 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. Really apreciate if you could help me figure it out how to troubleshoot and run the superbly awesome tool in my environment. SELECT SI_ID,SI_NAME,SI_PARENT_FOLDER,SI_FILES FROM CI_INFOOBJECTS hi, this looks interesting but it did not work on my laptop with windows XP SP2. Unable to connect to CMC server:6400. As an example SI_EXTENDED_PROCESSINFO.SI_PROMPT_DEFAULT_VALUES. 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. To load to a Data Model, select Home > Close & Load > Close & Load To. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. 1. Please close Excel and try again. Can you send the code or the complete solution with source code please ? With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. its with enterprise id. Using Query builder one can easily query the BusinessObjects repository and get the required information which cannot be found even in CMC. thx:), Excellent tool mate !! The purpose of the tool is to simplify building CMS metadata queries and providepossiblyto export the result to Excel. It might not work for other versions. The default connection is using port 8080 we use 9100. Basically, I need to be able to build queries that mirror what I see in the Instance Manager in CMC. From the menu, select the Export to CSV option. 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. It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. Queries Against SAP Business Objects Excellent Article very informative..Thanks for putting this on SCN. To export the results to a .csv file, complete the following: Click Query Results. 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. I try this without success, 1) Restart server For example: To access this information, 360Suite offers two modules: 360View and 360Eyes. CrystalDecisions.Enterprise.InfoStore.dll. The tool should be recompiled to change the port. its a good tool which saved me a lot of time. As you can see, there are no simple columns and some data is subdivided making it hard to understand what the formatted data means. Transposed sometimes it is convenient to see the result in the transposed form, for example when you select one object. Click Edit Data Provider on the toolbar. Privacy |
I will send you source code. Once again Thank you and Best regards, you have to remember the table names and the object kinds. Ensure SAP BusinessObjects User Adoption! All the users within and under a particular group. Is there a solution to this? It appears to be exactly what i am looking for1. 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. Highlight the entire block of text you want to be numbers Alt + A, then E, then Alt + F, you're done. Thanks a lot for this application , but when I try to extract to Excel file I got an error Windows message Could not save Excel file / File is used by another process is there any solution. 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. (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. Also a query to get information on who uses a particular report. The tool worked very well on the first day. Wiiisdom Ops supports Tableau Server and Tableau Online (now Tableau Cloud). Version-12.0.1100.0, Culture-neutral, In Excel you can do this by using the Text to Columns feature of the ribbon. 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 =