Query Tools and Applications
1. What is a Query Tool?
A query / reporting tool is the Tier-3 front-end software by which business users interrogate the data warehouse without writing low-level SQL themselves. It reads metadata to know which tables, columns, hierarchies and measures exist.
2. Categories of Query Tools
| Category | Nature of query | Typical output | Example tools |
|---|---|---|---|
| Production / canned reporting | Pre-defined, parameterised | Standard formatted reports at fixed schedule | Crystal Reports, SSRS, Cognos Report Studio |
| Managed query environment (ad hoc) | GUI drag-drop, tool generates SQL | Ad hoc lists, cross-tabs, exception lists | BusinessObjects WebI, Impromptu, Tableau |
| OLAP / multidimensional | Slice, dice, drill, pivot on cubes | Cross-tab matrix, charts | Essbase, MS Analysis Services, Power BI |
| Data mining / EIS | Model building, pattern discovery, KPI dashboards | Rules, clusters, scorecards | SAS EM, IBM SPSS Modeler, RapidMiner |
| Application-specific | Embedded analytics inside an app | Operational reports, alerts | Billing systems, ERP BW |
3. Features Expected of a Good Query Tool
- Metadata-driven — no knowledge of physical schema needed; business names used.
- Ad hoc query generation — converts GUI selections into optimised SQL/MDX.
- Drill-down / roll-up along hierarchies; drill-through to detail rows below the warehouse aggregate.
- Slice, dice, pivot views and multi-dimensional cross-tabs.
- Aggregates and calculations — average, growth %, top-N ranking, running totals, share of total.
- Exception reporting — highlight rows violating thresholds (e.g.
sales < 80% of target). - Graphical output — bars, pies, maps, trend lines; export to Excel/PDF.
- Scheduling & distribution — automatic refresh and email broadcast.
- Security — row/column level access control, single sign-on, user profiles.
- Performance help — result caching, query cost warning, aggregating navigation.
4. Query Tool Architecture
USER (GUI / browser / mobile)
|
v
+--------------------------+ metadata (tables, columns, hierarchies, joins)
| Semantic / metadata layer |<-------------------- METADATA REPOSITORY
+--------------------------+ ^
| generates SQL / MDX |
v |
+--------------------------+ |
| Query engine (SQL rewrite,| |
| aggregation navigation) |-------------------------------+
+--------------------------+
|
v
+--------------------------+
| DATA WAREHOUSE / MART / |
| OLAP CUBE |
+--------------------------+
Aggregation navigation: if a query asks for yearly totals but only monthly aggregates exist, the tool rewrites it.
5. Typical Analytic Applications
| Domain | Application |
|---|---|
| Sales & marketing | market basket analysis, campaign response, customer segmentation, RFM analysis |
| Finance | profitability analysis, budgeting vs actual, receivables ageing |
| Supply chain | inventory turns, supplier scorecards, demand forecasting |
| CRM | churn prediction, lifetime value, call-centre analytics |
| Web | clickstream / path analysis, funnel conversion |
| Fraud & risk | outlier detection in card transactions, insurance claim screening |
| HR | attrition analysis, workforce planning |
| Retail | assortment planning, price elasticity, store performance ranking |
6. Common Reports
- Production report: monthly sales register, invoiced dispatch note.
- Ad hoc query: 'show top 20 products by margin in North zone for Q3'.
- Exception report: customers whose payments are overdue by more than 60 days.
- Ranking report: top 10 dealers by value.
- Dashboard / balanced scorecard: KPIs — revenue, margin, on-time delivery, NPS.
7. Exam Points
- Classify query tools and explain each category with examples — 7-10 marks.
- Explain how metadata drives a query/reporting tool (ad hoc SQL generation, drill-down) — 5 marks.
- Short note on dashboards / balanced scorecard applications — 3 marks.