When using the Visual Query main screen to compose a new query request, the user clicks on the data object(s) on the left-hand Data Object tree to select and/or set filters on the entities desired in the report or graph.
Visual Query assumes no knowledge of the entity relationships or database design on the part of the user (definition of ‘ad hoc user query tool’ perhaps?), so it is possible that no ‘certifiable’ (aka verifiably correct) answer is possible for the requested answer. So Query Certifier determines whether it is possible to generate a verifiably correct SQL command (resulting in the ‘correct’ answer). If there is some reason why a correct answer cannot be certified, the user is shown a dialog with this fact, along with one or more reasons. Where possible, suggested corrections to the user’s request are shown as well.
An example of a certifiably correct query and one that is not certifiable is described below, using the famous ‘Northwind’ public database for the example queries.
Query 1: ‘total sales revenue by employee’. This answer can be certified as correct, because there is no ambiguity involved in the navigation paths: Employee -> Order -> Item; Sales Revenue = sum(Item.quantity * Item.unit price) BY employee.
Query 2: ‘total sales revenue by territory’. This answer cannot be certified as correct, because there is ambiguity involved in the navigation paths: Employee -> Order -> Item; Sales Revenue = sum(Item.quantity * Item.unit price); Employee > territory). Since there are many Territories per Employee, and the query can only SUM by Employee, not by Territory, the database ‘granulary’ doesn’t render the desired facts (’sales revenue by territory’). So a message to that effect is shown to the user, along with the suggestion to change the request to .. ‘by Employee’.
Posted by Marvin Elder
