business objects query builder export to excel

Objects that do have an SI_FILES property but the number of files is zero (SI_NUM_FILES) will not be returned, All objects that have an SI_FILES property but the filesize is 0, List all plugins that have a least 2 icons associated with them, select * from CI_SYSTEMOBJECTS where SI_PLUGIN_OBJECT=1 and SI_PLUGIN_ICON_2_A is not null, Web Intelligence documents ordered by name, select SI_NAME,SI_ID from ci_infoobjects where si_kind='Webi' order by SI_NAME asc, Web Intelligence documents ordered by size (largest first), select SI_NAME,SI_ID from ci_infoobjects where si_kind='Webi' order by SI_SIZE desc, Note that even though SI_SIZE is not requested, only in the ordering, it will bring the parameter into the results set, select * FROM CI_APPOBJECTS WHERE SI_KIND='Universe' order by SI_NAME asc, selec * FROM CI_APPOBJECTS WHERE SI_KIND='DSL.Universe' order by SI_NAME asc, select * from CI_SYSTEMOBJECTS where SI_PLUGIN_OBJECT=1 and SI_PLUGIN_ICON_1_A is null, Lists information on the Install Node and EnterpriseNode (SIA). 3rd Party Authentication) , How To. 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. It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. As you can see, there are no simple columns and some data is subdivided making it hard to understand what the formatted data means. Dear Matthew, again a useful page/info by you, I can't open/view the note 1895241. To a Data Model. With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. Dont wait, create your SAP Universal ID now! I am having the same question, Can you recommend a tool or a way to get the report's query. 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. Probably I had an IE cache problem. How can I get the Instances which took the maximum time to execute? You can suggest a query to get the group, universes, and folders. 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. Hello Manikandan, I work in Dallas and I thank you for writing this blog post. To preview the base data returned by a query, click the Refresh button: The combination of a document ID and Steps:- 1) Create a project say Prj_Test at Business Objects Data Services. Save the .csv file to your local It provides advanced features like code highlighting, auto-completion, and syntax checking to help you write error-free code. https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/ Edit a query from the Queries & Connections pane. Great tool! Logon cannot continue. is there any specific requirement to make this tool working? CMS Query Builder Get Free The tool allows to Is there a way to query and return what tables/fields a report is using?? 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. It like something in the CMS or BO server ? You can run the query builder statements directly from this tool WHERE SI_KIND = WEBI AND SI_INSTANCE = 0 AND SI_ANCESTOR = [SI_ID OF THE FOLDER]. You should go with SI_PARENTID instead if you are interested only in documents. Any suggestions? In the Manage Application Data select the Import Business Objects. However, there are only three tables and there are vast amounts of information stored in each one, making retrieving the data very difficult. For 4.0 SP4 Patch 6 For example, you have to find a folder ID, before querying the list of objects in the folder. Several fields are missing in the results. This means that response will come faster, and no unnecessary resources will be consumed by CMS. FROM ci_infoobjects, ci_appobjects, ci_systemobjects where si_recurring=1 and si_runnable_object=1 but the results for scheduling and processing do not show up. The one here should work. Windows server 2003 Enterprise X64 edition, SPack 2. A third-party tool will also give you the possibility to carry out complex requests that arent possible with Query Builder. When I am trying to login to the query builder tool from the server itself. In using the product for 3.1, in using the query SELECT * Thanks a lot As suggested earlier, it wold be great to walk me through troubleshooting the behavior. I dont have Excel open, and double checked task mgr too. Once you submit your query, results are shown on a web pagebut thats about as far as it goes. Your help in this matter will be highly appreciated. its a good tool which saved me a lot of time. Terms of use | you usually have to write several queries to get the required result. 2. 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. So you cannot get this information using CMS queries. This only occurs when you load the data to a worksheet orData Modelfrom Power Query. Furthermore, Excel cant effectively use multithreaded execution. How do I get a list of Parameter Names, and their Types (string or numeric) using SQL ? https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. Format the cells and add formulas as per your requirements and then save the file. Is it possible to automate the execution of Infostore query builder with CMS details user name,Pwd with query. Thanks a lot for this great article, I wondering if you could help me. I want to know the query for the query builder. I am running it on BI 4.0 SP4 FP20 and it works fine, but 1735539 - Query to list all Universes for which a user has "Edit Objects" rights via Query Builder. 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. 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. You can setup BO auditing, this will give you precise information who is running what. I am looking for a query to get information of most accessed reports with report folder excluding shortcuts and reports instances. Thanks a lot. Step 3: Under Metadata panel you will see a data model which contains tables, columns and measures. Really an Excellent Tool! error. Neither the request nor the results can be stored. Even when I search SI_LOGON_MODE=1, where I know I should have hundreds of matches. 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. 2) Install the tools to completly new PC. So far so good on our BI4.0 SP6 installation. We are finding that when an Administrator logs in and uses the Query Builder mentioned in this post All users are returned as expected.. 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/. 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. However, the CMS DB Driver still remains limited in its usage and is very hard to use. thx:), Excellent tool mate !! The second module, 360Eyes, allows you to go further and fill the gaps that Query Builder doesnt. Its because you dont have rights to view the folders in the environment you are looikng in (viz. The problem is, that the logo doesn't show up after exporting it to Excel. 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. Result is ", The KBA 1895241 should work if navigating tohttps://launchpad.support.sap.com/#/notes/1895241. If you have multiple accounts, use the Consolidation Tool to merge your content. 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. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. All my CR Objects imported had the setting where SI_LOGON_MODE = 1. For example, manipulating data in an Excel worksheet is fundamentally different than Power Query. 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. The repository information is stored in the form of InfoObject and the CMS reads the InfoObjects from the virtual tables. Is there a solution to this? Microsoft is aware of this problem and it is under investigation. To load to a Data Model, select Home > Close & Load > Close & Load To. In the Export - Excel Spreadsheet dialog box, review the suggested file name for the Excel workbook (Access But it is throwing errors. system cannot find the file specified. Check Biclever CMS Query Builder as alternative. Its always good practice to change the default names of worksheet tabs to names that make more sense to you. You can also use it to find differences between universes. You can also dynamically override the default settings for a query by using the Import dialog box which displays after you selectClose & LoadTo. 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. With the graphical interface, users can create requests by using predefined objects and filters, rather than having to type in the technical terms. This provides the Principal and Objects(Folder) to which the access is enabled. Hello All, We have been play around with the Query Builder a bit and noticed an oddity I guess. where to find this? 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. What I have tried so far (Image is Embedded and not from a Database or External): 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. I need more information on how to use this tool. 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. 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. By the way, it would be better to specify another filter on an indexed column (like SI_NAME, SI_KIND, SI_CREATION_TIME ) to avoid unnecessary work by CMS. For custom installations, 3rd party authentication and single sign on, an initial setup may be required. Web360Suite is a set of solutions to ensure quality, reliability, performance, and efficiency of SAP BusinessObjects through testing, auditing, monitoring, cataloging, and scheduling methodologies. It is great tool, love it. 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 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 Excel, select Data > Queries & Connections, and then select the Queries tab. Select New Source to add a data source. 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. 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. My cmc port is 8081,by default it take 8080 port and hence it is not working for me. 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. In a few months, SAP Community will switch to SAP Universal ID as the only option to login. On import some have been flipped to SI_LOGON_MODE = 0. how to get the Data security profile name from AdminTools query. what i am asking, one of our developer had used "image from address" in Appearance option in format cell. Select Enter Data to manually enter data. Login to Query Builder with Administrator account using the following link: http://servername:portnumber/AdminTools, 2. If you have multiple accounts, use the Consolidation Tool to merge your content. Given a particular group name (replace MyGroupNameHere accordingly) this query will return ALL the users in the group and any sub groups. 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? What makes it more useful than the Query Builder is the fact that the results provide actionable data. Clear this option if you prefer to do this on your own. Url:https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. Need to download Query Builder results into an Excel format so that the end users can review it. Using Query builder one can easily query the BusinessObjects Is there a way to show all the reports that one user is running? The familiar Excel worksheet , ribbon, and grid, The Power Query Editor ribbon and data preview. A very good handy tool to query metadata results. Hi Amir, here: bukhantsov.org/tools/QueryBuilder_src.zip. This is a quite tricky. I am really curious to know if there is a way to query object SI_DESTINATIONS because it doesnt return values when i mention this in select objects. In the Power Query Editor, select Home > Close & Load > Close & Load. Let me know if this resolves the issue. . Ensure SAP BusinessObjects User Adoption! It may take a few seconds to reclaim memory. 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. The report object defines the underlying data model and specifies which database tables and fields to pull data from. For more information, see Import data from external data sources. select * from CI_APPOBJECTS where SI_ID =<<-->>, https://blogs.sap.com/2013/07/26/businessobjects-query-builder-whats-new-in-bi-40/. You can use the DB system tables like v$sql for oracle. Is there some kind of a limit on the maximum number of returned rows in the tool? To open the Data Model, select Power Pivot>Manage. Microsoft Access DB Format All I changed is theSI_ID of the folder and it didn't work. What authentication are you using? Working with Query Builder YouuseQueryBuildertoconstruct,submit,andtrackquerystatus.Youcansubscribetoeventsand 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. An example of an impact analysis report identifying documents that contain specific Universe objects, SQL expressions, or variables. I'm sorry, I have not found how you can find the information that is included when you create a user in the cmc. setup the odbc connector on the users pc, rename and database to .accdr, put it on a file share and install the ms access runtime (free) on their pcs. You can use command line version of the tool to generate the Excel file An added extra here is that with 360Eyes, you will be able to track inconsistencies between the CMS and FRS. Good work. If this occurs, select Close & Load, in the new worksheet, select Data > Queries & Connections > Queries tab, right click the query, and then select Load To. CrystalDecisions.Enterprise.Framework.dll The step-by-step wizard is too simplistic. Thanks so much for the tool it is very useful ( this BO ver: XI 3.1+SP3+FP3.4). The report works so far and I managed to add the logo. The majority of the tables that make up the 'CMS Database' can not be queried using SQL directly against the database, since their content is held in a proprietary encrypted format. In the list of queries, locate the query, right click the query, and then select Load To. SELECT SI_ID,SI_NAME,SI_PARENT_FOLDER,SI_FILES FROM CI_INFOOBJECTS Hello ! Yes, the tool requires .NET 3.5. 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. Is there anything I should change. Dont wait, create your SAP Universal ID now! SELECT TOP 10000 * FROM ci_systemobjects. I realize that this is a lot to ask, but I have been trying to figure this out myself but without any luck! Queries with si_ancestor conditions can run for a long time. 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. Regards. Is there a compiled version for 12.3.0.601? Can i have a link for download the verison is works fine with Product Version: 12.3.0.601 (XI 3.1 SP3)? Because the tool requires SDK libraries that installed with the client tools. Is it possible to get report level data like dimensions and the report query generated for a report from the repository ?Thanks !! This action causes Excel to enumerate again through the entire data set for each row. This is what the Query Builder application looks like in Business Objects and you can access it via this link: It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. 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. Always make sure Single & Double quotes involved in the queries as expected by the standard format. I have BI4 client tools running and the EnterpriseFramework.dll has version 14.0.5.882. On the other hand: Depending on the delimiter char used, the method of opening the file in Excel might be the problem: Restart Excel and choose "File > Open > 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. To close the Power Pivot window, select File > Close. Planning a BI 4.1, Have you ever wanted to know what the differences were between a Business Objects Universe in 2 different environments?
Obie Bennett Family, Hicks Family Genealogy, Articles B