Know How to Handle Oracle Ad Hoc Reporting Tool with Ease Using These Tips

Posted by Mike Shah on January 29th, 2018

Ad Hoc queries are a type of query that can’t be determined prior to conception or issuance. They are used in order to get information as the need arises and they typically consist of dynamically constructed SQL, made using desktop resident query tools. This is in direct contrast to any other queries that are predefined and routinely performed. Ad Hoc, from the Latin phrase meaning “for the purpose,” generally refers to queries that have been designed to provide answer to very specific problems. An Ad Hoc created for example, is committed to dealing with a very specific undertaking, after which the committee is disbanded. In the same way, ad hoc queries don’t reside within the computer or database manager, but is dynamically created based on the data user’s needs. 

In the past, multiple query sets are constructed to help users analyze different kinds of data. These queries are typically predefined under database management or a system administrator. A barrier there exists between what users needs and the predefined, canned information. As a result, end users are bombarded with lots of unrelated data within the query results. This also takes a heavy toll on IT resources as users will often have to execute multiple queries at a given period in order to get to the information they actually need. Fortunately today, Ad Hoc reporting tools and active data warehouses exist to accelerate the retrieval of relevant information and provide answers to interactive queries in mission-critical applications. 

Most data users are non-technical people. Ad Hoc Query tools such as Oracle’s Ad Hoc Reporting tool exist so that the very same non-technical users can perform complex queries without having to know what happens behind the curtain. These tools include features that support different kinds of query relationships such as one-to-many, many-to-many, and many-to-one relationships. Oracle’s Ad Hoc Reporting Tool makes use of Hyperion Smart View to retrieve, analyze, and report data and below are simple tips to help you handle its use: 

• There are three main view types in Smart View, including ALL, PREDEFINED, and FAVORITE. ALL view provides access to all data sources, while PREDEFINED offers a filtered set. FAVORITE lists pre-defined data sources, similar to a browser’s favorites list. 

• Use the Hyperion menu to view, navigate, and/or analyze Essbase data, connect to a data base, change POV, drill down into the data, and calculate. 

• Use the Smart View toolbar to access commands. The toolbar can be customized to contain buttons that are relevant to Essbase. 

About the Author: 

This article is written by the CTO of Datasparc Inc. They offer DBHawk™ – a web-based database management & self-service business intelligence software designed for Oracle, SQL Server and other databases.