Join Now
Wiiisdom | Analytics Governance SolutionsWiiisdom | Analytics Governance Solutions
  • Solutions
    • By Initiatives
      • analyticsops-solution AnalyticsOps
      • technology-solution Embedded Analytics
      • icon-scale-analytics Scale Analytics
      • icon-regulatory-compliance Regulatory Compliance
      • icon-enable-rpa Enable RPA
      • icon-cloud-analytics Move to the Cloud
    • By Industries
      • financial-solution Financial
      • government-solution Government
      • healthcare-solution Healthcare
      • insurance-solution Insurance
    • By Technologies
      • sap-bo SAP BusinessObjects
      • sap-data-services SAP Data Services
      • tableau-software Tableau
      • power-bi Power BI
  • Products
    • wiiisdom-ops Wiiisdom Ops
      • tableau-software Wiiisdom Ops for Tableau
      • sap-bo Wiiisdom Ops for SAP BusinessObjects
      • power-bi Wiiisdom Ops for Power BI
    • 360suite 360Suite for SAP BusinessObjects
      • favicon-eyes 360Eyes
        Metadata & Impact Analysis
      • favicon-live 360Live
        Live Monitoring
      • favicon-bind 360Bind
        Automated Regression Testing
      • favicon-univ 360Univ
        Universe Management Tool
      • favicon-cast 360Cast
        Report Bursting & Scheduling
    • 360suite 360Suite
      • favicon-view 360View
        Administration & Maintenance
      • favicon-plus 360Plus
        Disaster Recovery, Backup & Promotion
      • favicon-vers 360Vers
        Versioning & Lifecycle Management
      • favicon-safe 360Safe
        License Compliance
      • favicon-init 360Init
        Project Creation
  • Resources
    • Resource Center
    • Webinars
    • Events
  • About Us
    • Our Mission
    • Careers
    • Partner Program
    • News
  • Contact Us
0 0   
By Ailsa Cartledge SAP BusinessObjects 360Suite BI Administration April 9, 2020

Lire cet article en Français france-drapeau

Go The Extra Mile:
How Can You Overcome The Limitations Of Query Builder?

query-builder-limitations

Back to the basics

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). It is commonly used by SAP BusinessObjects administrators and developers looking for information about their users, reports, and universes. 

However, Query Builder has its limitations and you will face situations where Query Builder is not enough. It is a technical tool, and to make queries against the SAP BusinessObjects repository, you need to have the technical knowledge because SAP doesn’t provide any documentation or tutorials on how to create them in the tool, other than online blogs there is no help. You also need to know a language that resembles SQL — without it, you’ll be a little bit stuck! You can find the basic SQL language requests here. 

To create proper queries, you need to equally familiarize yourself with these three tables and the content in their rows:

query-builder-basics

Source: https://blogs.sap.com/2013/06/17/businessobjects-query-builder-basics/ by Manikandan Elumalai

Querying the relationships between different objects of the tables requires specific Query Builder knowledge. However, there are only three tables and there are vast amounts of information stored in each one, making retrieving the data very difficult. 

This is what the Query Builder application looks like in Business Objects and you can access it via this link: http://MyServer:Port/AdminTools

query-builder-platform

The limitation game 

Once you submit your query, results are shown on a web page…but that’s about as far as it goes. 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. Neither the request nor the results can be stored. It’s a one-shot process. 

query-builder-platform

The above is an example of a query result. As you can see, there are no simple columns and some data is subdivided making it hard to understand what the formatted data means. This subdivided data is called “property bags” and cannot be queried with Query Builder.

Benefit of the doubt

Despite the technical knowledge needed behind the tool and its constraints, we can’t ignore the fact that it is completely free to use. 

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. 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. 

However, the CMS DB Driver still remains limited in its usage and is very hard to use. Again, there is no documentation to help you and technical knowledge is still needed given mapping the InfoObjects to a universe can be challenging. It is also confusing the way the levels are sorted in the layers, plus it still doesn’t allow you to fetch data from the Filestore. 

bi-platform-cms

Get more with 360Suite

It is clear that Query Builder by itself isn’t enough to be able to really take advantage of your SAP BusinessObjects metadata. 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. 

A third-party tool will also give you the possibility to carry out complex requests that aren’t possible with Query Builder. For example:

  • List reports that use a database column,
  • List universes that reference a database column,
  • List reports that use a universe object. 

To access this information, 360Suite offers two modules: 360View and 360Eyes. 

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. This tool is also aimed at less technical users because it doesn’t require you to learn the SQL language that is needed with Query Builder. With the graphical interface, users can create requests by using predefined objects and filters, rather than having to type in the technical terms. What makes it more useful than the Query Builder is the fact that the results provide actionable data. For example, you can delete WebI documents or even targeted users per request. You can also retrieve complete documentation on selected users, retrieve the data providers, and prompts of selected documents.

The second module, 360Eyes, allows you to go further and fill the gaps that Query Builder doesn’t. With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. 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. Any output can also be retrieved by a WebI document. We provide a library of WebI documents in order to efficiently query the metadata it aggregates. 

impact-analysis-report

An example of an impact analysis report identifying documents that contain specific Universe objects, SQL expressions, or variables. 

An added extra here is that with 360Eyes, you will be able to track inconsistencies between the CMS and FRS. For example, the CMS can contain links toward FRS documents, and this could create various inconsistencies such as: 

  • The link exists but the document in the FRS no longer exists or,
  • The document exists in the FRS but the link doesn’t exist in the CMS. 

These inconsistencies can be extremely frustrating because if the link exists but not the report, it works exactly like on a website when you click on a link and get the “404 error Page Not Found”. 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. Query Builder doesn’t have the means to detect these inconsistencies but 360Eyes can. 

Take it a step further

Query Builder allows you to query the whole CMS database but it prevents you from going any further with your metadata. 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. 

BI-on-BI is a discipline that consists of analyzing BI metadata in order to take decisions and actions. 360Suite empowers SAP BusinessObjects professionals by pulling metadata and offering powerful automations.

Don’t let the limitations of Query Builder stop you from going further. Contact us today and one of our experts will help you.

query-builder-documentation
reports-business-objects-administrators
READ MORE

Related posts

bec-improve-sap-businessobjects
November 30, 2020

How BEC Better Serves Its Customers with Improved SAP BusinessObjects Promotion Management

READ MORE
archiving-clean-up-sap-businessobjects-cover
October 29, 2020

Automate your SAP BusinessObjects archiving

READ MORE
cover-data-lineage-sap-bods
October 15, 2020

End-to-End Data Lineage For SAP BODS

READ MORE
360EyesBusiness Objects MetadataBusiness Objects Bulk Update
Ailsa Cartledge / About Author
Ailsa is the Marketing and Communications Specialist at Wiiisdom. Ailsa regularly creates content for the SAP BusinessObjects and Tableau community, and strengthens Wiiisdom's external communication.
More posts by Ailsa Cartledge

Related Posts

fannie-mae-maintains-an-efficient-sap-businessobjects-environment
How Fannie Mae Maintains an Efficient SAP BusinessObjects Environment
0 3
20 Mar 2018
Calculation Engine changes
Find Calculation Engine Changes in SAP BusinessObjects

In the past, we discussed the value of 360Bind in how to mitigate risks with automated regression testing. 360Bind is an…

0 3
03 Oct 2016
preparation-migration-business-objects-orange
How Orange Prepared For Its SAP BusinessObjects Migration
0 0
29 Jul 2020
best-practices-webintelligence-reports
Top 10 Best Practices for Web Intelligence Reports
9
16 Aug 2018
Business Objects regulations
Business Objects Risk Management and Regulatory Needs

When it comes to Business Objects risk management or Business Objects regulations needs it is a black box due to…

5
19 Oct 2017
airbus-helicopters-bi-self-service
3 Steps Towards A Self-Service BI: Airbus Helicopters
0 0
28 Apr 2020
Business Objects cross-charging
Inter-company billing and cross-charging made easy with 360Eyes for SAP BusinessObjects

Keys to implement inter-company billing and cross-charging using 360Eyes Full BI-on-BI tool Without using a specialized software solution, how can…

2
01 Aug 2017
reduce-cloud-hosting
Reduce On-Premise & Cloud hosting costs through monitoring and automated actions
0 0
27 Nov 2019
archiving-clean-up-sap-businessobjects-cover
Automate your SAP BusinessObjects archiving
4
29 Oct 2020
performance-testing
Business Objects Performance Testing
0 3
01 Jun 2018
businessobjects-tags
How To Implement and Use Tags in Business Objects
0 0
23 Dec 2019
epa-use-case
Reducing Costs and Maximizing SAP BusinessObjects Investments at EPA
0 0
19 Mar 2019
bi-on-bi-360eyes
How City of Chicago uses BI on BI to improve metadata management, increase security, and save money
0 5
08 Feb 2018
bec-improve-sap-businessobjects
How BEC Better Serves Its Customers with Improved SAP BusinessObjects Promotion Management
5
30 Nov 2020
Webi reports
Repointing converted Webi reports to Freehand SQL connections

How to re-point Webi reports converted from Deski to a Freehand SQL connection In our post Taking full advantage of…

2
11 Jul 2017

Leave a comment Cancel reply

Information

  • Careers
  • Press
  • Terms & Privacy Policy
  • Legal
  • Supplier Code of Ethics

Support

  • Helpdesk
  • Downloads
  • Documentation
  • Knowledge Base
  • Release Notes

Latest news

  • How SAP Ariba Improved Its SAP Data Services Documentation Capabilities
    March 16, 2023
  • What is BI Testing?
    March 8, 2023

Connect With Us

  • Making trusted decisions together
  • Français
2023 © Copyrights Wiiisdom.
All Rights Reserved.

Request your trial for Wiiisdom Ops

Wiiisdom Ops supports Tableau Server and Tableau Online (now Tableau Cloud).
For custom installations, 3rd party authentication and single sign on, an initial setup may be required.
Please refer to our documentation and do not hesitate contacting us at: contact@wiiisdom.com.