Home > Querying Dimensional Objects > Creating Basic Queries
![]() Previous |
![]() Next |
Querying a cube is similar to querying a star schema. In a star schema, you join a fact table to a dimension table. The fact table provides the numeric business measures, and the dimension table provides descriptive attributes that give meaning to the data. Similarly, you join a cube view with either a dimension view or a hierarchy view to provide fully identified and meaningful data to your users.
For dimensions with no hierarchies, use the dimension views in your queries. For dimensions with hierarchies, use the hierarchy views, because they contain more information than the dimension views.
When querying a cube, remember these guidelines:
Apply a filter to every dimension.
The cube contains both detail level and aggregated data. A query with an unfiltered dimension typically returns more data than users need, which negatively impacts performance.
Let the cube aggregate the data.
Because the aggregations are calculated in the cube, a typical query does not need a GROUP BY
clause. Simply select the aggregations you want by using the appropriate filters on the dimension keys or attributes.
To create a level filter, you must know the names of the dimension levels. You can easily acquire them by querying the dimension or hierarchy views:
SELECT DISTINCT level_name FROM time_calendar_view; LEVEL_NAME ------------------------------ CALENDAR_YEAR CALENDAR_QUARTER MONTH TOTAL
Several data dictionary views list the names of the levels. The following example queries USER_CUBE_HIER_LEVELS
.
SELECT level_name FROM user_cube_hier_levels WHERE dimension_name = 'TIME' AND hierarchy_name ='CALENDAR'; LEVEL_NAME -------------------- TOTAL CALENDAR_YEAR CALENDAR_QUARTER MONTH
To see the importance of applying a filter to every dimension, consider the query in Example: Displaying Aggregates at All Levels of Time, which has no filter on the time dimension.
Displaying Aggregates at All Levels of Time
/* Select key descriptions and facts */ SELECT t.long_description time, ROUND(f.sales) sales /* From dimension views and cube view */ FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* No filter on Time */ WHERE p.level_name = 'TOTAL' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' /* Join dimension views to cube view */ AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY t.end_date;
Without a filter on the Time dimension, the query returns values for every level of time. This is more data than users typically want to see, and the volume of data returned can negatively impact performance.
TIME SALES ---------- ---------- JAN-98 8338545 FEB-98 7972132 Q1.98 24538588 MAR-98 8227911 APR-98 8470315 MAY-98 8160573 JUN-98 8362386 Q2.98 24993273 JUL-98 8296226 AUG-98 8377587 SEP-98 8406728 Q3.98 25080541 OCT-98 8316169 NOV-98 8984156 Q4.98 26258474 1998 100870877 . . .
Now consider the results when a filter restricts Time to yearly data.
Example: Basic Cube View Query shows a basic query. It selects the Sales measure from UNITS_CUBE_VIEW
, and joins the keys from the cube view to the hierarchy views to get descriptions of the keys.
Basic Cube View Query
/* Select key descriptions and facts */ SELECT t.long_description time, ROUND(f.sales) sales /* From dimension views and cube view */ FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Create level filters */ WHERE t.level_name = 'CALENDAR_YEAR' AND p.level_name = 'TOTAL' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' /* Join dimension views to cube view */ AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY t.end_date;
Example: Basic Cube View Query selects the following rows. For CUSTOMER
, PRODUCT
, and CHANNEL
, only one value is at the top level. TIME
has a value for each calendar year.
TIME SALES -------- ---------- 1998 100870877 1999 134109248 2000 124173522 2001 116931722 2002 92515295 2003 130276514 2004 144290686 2005 136986572 2006 140138317
Dimension attributes also provide a useful way to select the data for a query. The WHERE
clause in Example: Selecting Data with Attribute Filters uses attributes values to filter all of the dimensions.
Selecting Data with Attribute Filters
/* Select key descriptions and facts */ SELECT t.long_description time, p.long_description product, cu.long_description customer, ch.long_description channel, ROUND(f.sales) sales /* From dimension views and cube view */ FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Create attribute filters */ WHERE t.long_description in ('2005', '2006') AND p.package = 'Laptop Value Pack' AND cu.long_description LIKE '%Boston%' AND ch.long_description = 'Internet' /* Join dimension views to cube view */ AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY time, customer;
The query selects two calendar years, the products in the Laptop Value Pack, the customers in Boston, and the Internet channel.
TIME PRODUCT CUSTOMER CHANNEL SALES ------ ------------------------------ --------------------- -------- ---------- 2005 Laptop carrying case KOSH Entrpr Boston Internet 5936 2005 56Kbps V.92 Type II Fax/Modem KOSH Entrpr Boston Internet 45285 2005 Internal 48X CD-ROM KOSH Entrpr Boston Internet 2828 2005 Standard Mouse KOSH Entrpr Boston Internet 638 2005 Envoy Standard Warren Systems Boston Internet 19359 2005 Laptop carrying case Warren Systems Boston Internet 13434 2005 Standard Mouse Warren Systems Boston Internet 130 2006 Standard Mouse KOSH Entrpr Boston Internet 555 2006 Laptop carrying case KOSH Entrpr Boston Internet 6357 2006 56Kbps V.92 Type II Fax/Modem KOSH Entrpr Boston Internet 38042 2006 Internal 48X CD-ROM KOSH Entrpr Boston Internet 3343 2006 Envoy Standard Warren Systems Boston Internet 24198 2006 Laptop carrying case Warren Systems Boston Internet 13153 2006 Standard Mouse Warren Systems Boston Internet 83 14 rows selected.
A cube contains all of the aggregate data. As shown in this chapter, a query against a cube just selects the aggregate data. It does not calculate the values.
The following is a basic query against a fact table:
/* Querying a fact table */ SELECT t.calendar_year_dsc time, SUM(f.sales) sales FROM time_dim t, units_fact f WHERE t.calendar_year_dsc IN ('2005', '2006') AND t.month_id = f.month_id GROUP BY t.calendar_year_dsc;
The next query fetches the exact same results from a cube using filters:
/* Querying a cube */ SELECT t.long_description time, f.sales sales FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Apply filters to every dimension */ WHERE t.long_description IN ('2005', '2006') AND p.level_name = 'TOTAL' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' /* Join dimension views to cube view */ AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY time;
Both queries return these results:
TIME SALES ----- ---------- 2005 136986572 2006 140138317
The query against the cube does not compute the aggregate values with a SUM
operator and GROUP BY
clause. Because the aggregates exist in the cube, this would re-aggregate previously aggregated data. Instead, the query selects the aggregates directly from the cube and specifies the desired aggregates by applying the appropriate filter to each dimension.
The most efficient queries allow the OLAP engine to filter the data, so that the minimum number of rows required by the query are returned to SQL.
The following are among the WHERE
clause operations that are pushed into the OLAP engine for processing:
=
!=
>
!>
<
!<
IN
NOT IN
IS NULL
LIKE
NOT LIKE
The OLAP engine also processes nested character functions, including INSTR
, LENGTH
, NVL
, LOWER
, UPPER
, LTRIM
, RTRIM
, TRIM
, LPAD
, RPAD
, and SUBSTR
.
SQL processes other operations and functions in the WHERE
clause, and all operations in other parts of the SELECT
syntax.