End-users will generally utilize business intelligence tools to submit report requests. In Oracle terms, each request may generate a series of SELECT statements necessary to construct the complete report. For example, your users' business intelligence tool may have a server process of its own that processes the returned SQL SELECT results using advanced OLAP functions and logic not yet found within Oracle. So as the DBA, you'll only be able to see the intermediate queries by scanning the SGA. Not to worry, though, for generally if we can make the intermediate queries faster, the overall report execution will improve as well.
A typical user query might be something like the following: How much beer and coffee did we sell in our Dallas stores during December 1998? Using our example dimensional model in Figure 4-1, a typical ad-hoc business intelligence intermediate query submitted to Oracle might look something like:
SELECT prod.category_name, sum (fact.sales_unit) Units, sum (fact.sales_retail) Retail FROM pos_day fact, period per, location loc, product prod WHERE fact.period_id = per.period_id AND fact.location_id = loc.location_id AND fact.product_id = prod.product_id AND per.levelx = 'DAY' AND per.period_month = 12 AND per.period_year = 1998 AND loc.levelx = 'STORE' AND loc.city = 'DALLAS' AND loc.state = 'TX' AND prod.levelx = 'ITEM' AND prod.category_name in ('BEER','COFFEE') GROUP BY prod.category_name;
with results of:
CATEGORY_NAME UNITS RETAIL ---------------------- ------------ -------------- BEER 11,613 64,490.81 COFFEE 22,808 20,462.92
Even this example yields key insights into what the DBA can expect in the way of queries against star schema warehouses. In general, a star schema SELECT will:
Use GROUP functions and therefore GROUP BY.
Contain a JOIN of a fact with one or more dimensions.
Possess lots of WHERE restrictions using dimension columns.
Scan lots of rows to return relatively few rows of results.
Conversely, business intelligence tool-generated SQL may target the wrong level of table summarization (i.e., example query should have used the month table instead of the day table, and therefore offers opportunities for tuning or utilizing query rewrites).
That's it. Star schema queries are just that simple. In fact, they are nothing more than table searches via some lookup tables. Oracle has been able to handle queries of this nature for years, but the sheer size of data warehouses make this something altogether different.