Ad hoc query and analysis is the style of business intelligence (BI) designed for information explorers and power users who need full analytic power against all enterprise data. These users require the ability to see any possible combination of data. If it were feasible to pre-design reports that covered every possible combination of data, then ad hoc query and analysis would not be needed. Practically speaking, this is impossible.
Pre-defining reports with all possible permutations would require the design of tens of thousands and even millions of reports depending on the extent of the database. It would also require the addition of hundreds or thousands of new reports each time a new attribute is added to the database.
The most basic way to support ad hoc query and analysis is to give users the ability to create brand new reports, allowing them to assemble any possible combination of data into a report. In that way, companies don`t have to pre-design all possible report combinations. This is the basic definition of ad hoc query and analysis, and it is the method employed by most BI vendors. However, it is important to note that cube-based BI vendors only allow users to create ad hoc reports against the small subsets of data available in their proprietary cube databases. While this technically is an ad hoc report, it does not fulfil the requirements for full analysis of the entire relational database.
The requirement today is for an architecture designed from its very roots to provide robust ad hoc query and analysis capability. Key here is relational online analytical processing (OLAP) technology, which allows users to perform full OLAP analysis against the entire relational database.
This architecture distinguishes itself from all other BI architectures in five key areas:
1. Parameter-driven reporting with guided analysis: Allows users to create radically different reports simply by answering a series of questions (or prompts) just prior to running the report.
Parameter-driven reporting functionality allows people to customise the content and layout of any given report within a range of variations defined by certain factors or parameters. This architecture provides the richest range of parameterisation available, allowing one report design to manifest itself in a multitude of variations.
Novice users can create sophisticated custom reports on the fly, defining report content by selecting metrics and business criteria at run time - simply by answering prompted questions. This allows organisations to translate complex database query parameters into a set of simple questions that guide users.
What makes parameterised reporting truly useful is when the users have the ability to iterate quickly through many parameter sequences and to save their sequenced answers for future use.
2. Drill anywhere: Allows users to surf to any place in the database by using OLAP functionality and following the business model of the data warehouse, enabling the dynamic creation of new ad hoc reports.
The most basic way to support ad hoc query and analysis is to give users the ability to create brand new reports, allowing them to assemble any possible combination of data into a report.
Charl Barnard, GM, MicroStrategy
Where other BI software limits users to drill up and down the hierarchies within their limited cube databases, ROLAP modelling of relational databases allows users to drill up, drill down, and drill across hierarchies anywhere in the entire database. Each drilling action creates a new report dynamically for the user.
In essence, drill anywhere in the database gives users the ability to surf to the set of data that gives them the answers they are looking for, without the need to manually create ad hoc reports.
3. OLAP analysis against the entire database: Allows users to conduct report manipulations on the fly.
Users have the flexibility to modify reports on the fly by adding powerful subtotals, sorting on multiple values, paging by report attributes, pivoting the report axes, building new calculations or new derived metrics, applying sophisticated filtering criteria, using thresholds and exporting data.
Subtotals are data that is rolled up or aggregated according to some formulae for logical grouping levels that show up in a report. For example, a regional report would have a total for the entire region and would have subtotals for all of the districts within the region. Subtotals can be applied on columns, rows, and across specific levels of business attributes in a report. With this feature, a person viewing a report with initial totals set at the region level for each quarter might then modify the report to calculate the total of all quarters for each region.
4. Sophisticated filtering with power of sets: Allows users to segment data according to different business criteria in order to refine the data set.
Set analysis, also known as segmentation analysis, is effective across many business disciplines. It is particularly popular in marketing, where it generally takes the form of customer segmentation, through which an organisation will segment the customer base according to different business criteria in order to find the right customers for certain products. Set analysis is the process in which data sets are manipulated and combined to obtain a refined data set for further analysis.
5. User-defined data grouping: Allows users to refine the business model without causing any changes to the database or the overall business model.
A data warehouse is organised in a structure that reflects the business model of an organisation, but the structure does not always reflect the business needs of individual departments, teams and decision-makers. As a result, users can have difficulty consolidating data into one report that meets their specific business needs.
Custom groups are made of business elements that make up the rows of a report. Users can define each report element separately from the other elements - each custom group element can act as a separate report with its own set of criteria, such as business attribute qualifications, metric qualifications and banding qualifications.
Ultimately, users do not need to understand databases, table structures, or query languages - they only need to know how to point and click. They need to drill into their data to access detailed information guided by their business model, not their database model. Giving them complete control over their data minimises the administrative workload and reduces costs. Furthermore, with this type of architecture administrators can build once and deploy throughout the entire organisation, enabling users to then build exactly what they want when they need it.
Share