Home > Querying Dimensional Objects > Using Attributes for Aggreg...
![]() Previous |
![]() Next |
An OLAP cube aggregates the data within its hierarchies, using the parent-child relationships revealed in the hierarchy views. The OLAP engine does not calculate aggregates over dimension attribute values.
Nonetheless, you may want to aggregate products over color or size, or customers by age, zip code, or population density. This is the situation when you can use a GROUP BY
clause when querying a cube. Your query can extract data from the cube, then use SQL to aggregate by attribute value.
The cube must use the same aggregation operator for all dimensions, and the aggregation operator in the SELECT
list of the query must match the aggregation operator of the cube. You can use a GROUP BY
clause to query cubes that use these operators:
First Non-NA Value
Last Non-NA Value
Maximum
Minimum
Sum
Example: Aggregating Over an Attribute shows a query that aggregates over an attribute named Package. It returns these results:
TIME PACKAGE SALES ------ ------------------ ---------- 2005 All 1809157.64 2005 Multimedia 18083256.3 2005 Executive 19836977 2005 Laptop Value Pack 9547494.81
Units Cube uses the SUM
operator for all dimensions, and the query uses the SUM
operator to aggregate over Sales. The Package attribute applies only to the Item level of the Product dimension, so the query selects the Item level of Product. It also eliminates nulls for Package, so that only products that belong to a package are included in the calculation. The GROUP BY
clause breaks out Total Sales by Time and Package.
Aggregating Over an Attribute
SELECT t.long_description time, p.package package, SUM(f.sales) sales FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Select Product by level and attribute */ WHERE p.level_name = 'ITEM' AND p.package IS NOT NULL AND t.long_description = '2005' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' /* Join dimensions and cube */ AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel GROUP BY t.long_description, p.package;
Before using the technique described in "Aggregating Measures Over Attributes", ensure that the calculation is meaningful. For example, the common calculation Percent Change might be defined as a calculated measure in a cube. Summing over Percent Change would produce unexpected results, because the calculation for Percent Change ((a-b)/b
,) is not additive.
Consider the following rows of data. The correct Total Percent Change is .33
, whereas the sum of the percent change for the first two rows is .75
.
Row | Sales | Sales Prior Period | Percent Change |
---|---|---|---|
1 | 15 |
10 |
.50 |
2 | 25 |
20 |
.25 |
Total | 40 |
30 |
.33 |
Example: Querying Over Attributes Using Calculated Measures shows a query that aggregates over the Package attribute and calculates Percent Change From Prior Period. The inner query aggregates Sales and Sales Prior Period over the attributes, and the outer query uses the results to compute the percent change. These are the results of the query, which show the expected results for PCT_CHG_PP
:
TIME PACKAGE SALES PRIOR_PERIOD PCT_CHG_PP ------ ------------------ ---------- ------------ ---------- 2005 All 1809157.64 1853928.06 -.02414895 2006 All 1720399.03 1809157.64 -.04906074 2005 Executive 19836977 20603879.8 -.03722128 2006 Executive 19580638.4 19836977 -.01292226 2005 Laptop Value Pack 9547494.81 10047298.6 -.04974509 2006 Laptop Value Pack 9091450.58 9547494.81 -.04776585 2005 Multimedia 18083256.3 19607675.5 -.07774604 2006 Multimedia 18328678.7 18083256.3 .013571806 8 rows selected.
Querying Over Attributes Using Calculated Measures
/* Calculate Percent Change */ SELECT TIME, package, sales, prior_period, ((sales - prior_period) / prior_period) pct_chg_pp FROM /* Fetch data from the cube and aggregate over Package */ (SELECT t.long_description time, p.package package, SUM(f.sales) sales, SUM(f.sales_pp) prior_period FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Create filters */ WHERE p.level_name = 'ITEM' AND p.package IS NOT NULL AND t.long_description IN ('2005', '2006') 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 GROUP BY t.long_description, p.package ORDER BY p.package);