Skip Headers
Previous
Previous
 
Next
Next

Using Attributes for Aggregation

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:

Aggregating Measures Over Attributes

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;

Aggregating Calculated Measures Over Attributes

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);