The Conditional Sub-Queries component enables you to sort through numerous detailed transactions, highlighting low-level exceptions and identifying the small number that fail to meet the threshold.
Sifting through a sales database with numerous daily consumer transactions to find exceptions is inefficient. To discover these exceptions, data must be sorted at a detailed level—transaction by transaction. Sorting involves reading all transactions and applying exception criteria. Tools that undertake this task, must load the transactions into memory and take time to read the data. If the data is cached in a document; such as, an Interactive Reporting Studio document, time is required for the document to open and exceptions to be viewed. Users do not want to wait for their documents to open, and system managers do not want their resources to be used inefficiently, in this case; reading, saving, and shipping large information volumes over the network.
The Conditional Sub-Queries component provides a solution by dividing the process into two queries—the primary query and the sub-query.
The primary query (multiple queries can exist in an Interactive Reporting Studio document) is scheduled and runs in the scheduler. It reads only the columns needed to identify exceptional transactions, and produces a table of key values that identify those transactions. The table is the Limit Lookup Table. Unlike most scheduled queries, the primary query results are not saved, because the data is mostly of little interest and increases the Interactive Reporting Studio document size.
The sub-query (multiple sub-queries can exist in an Interactive Reporting Studio document) is not scheduled, but is executed programmatically by the component after the scheduler runs the primary query. The Conditional Sub-Queries component transfers key values from the Limit Lookup Table and applies them as filters to the sub-query, processed by the component. The sub-query retrieves exceptional transaction details, and saves the results.
Applying filters to the sub-query, enables the sub-query to generate only exception details that require action. The end result is the slimmest Interactive Reporting Studio document with the full detail required to manage and analyze exceptions.
The Conditional Sub-Queries component also enables dashboards to activate sub-queries based on a user event; for example, when a user selects an item from a drop-down list. In this case, a custom event, such as <Dropdown Selection> as defined by the dashboard developer, replaces the primary query. When the component receives the custom event, it activates the sub-query associated with the event. The Limit Lookup Table associated with the custom event defines the sub-query filters.
Other regular queries may be available in the Interactive Reporting Studio document.