Skip Headers
Previous
Previous
 
Next
Next

Using Calculations in Queries

A DBA can create calculated measures so they are available to all applications. This not only simplifies application development, but ensures that all applications use the same name for the same calculation.

Nonetheless, you may want to develop queries that include your own calculations. In this case, you can use an inner query to select aggregate data from the cube, then perform calculations in an outer query. You can select data from cubes that use any type of aggregation operators, and you can use any functions or operators in the query. You must ensure only that you select the data from the cube at the appropriate levels for the calculation, and that the combination of operators in the cube and in the query create the calculation you want.

Example: Calculating Average Sales Across Customers shows a query that answers the question, What was the average sales of Sentinel Standard computers to Government customers for the third quarter of fiscal year 2005? UNITS_CUBE is summed over all dimensions, so that FY2005.Q3 is a total for July, August, and September. The inner query extracts the data for these months, and the outer query uses the MIN, MAX, and AVG operators and a GROUP BY clause to calculate the averages.

Calculating Average Sales Across Customers

SELECT customer, ROUND(MIN(sales)) minimum, ROUND(MAX(sales)) maximum, 
   ROUND(AVG(sales)) average
FROM
   (SELECT cu.long_description customer,
       f.sales sales
   FROM time_fiscal_view t,
     product_primary_view p,
     customer_segment_view cu,
     channel_primary_view ch,
     units_cube_view f
   WHERE t.parent = 'FY2005.Q3'
     AND p.dim_key = 'SENT STD'
     AND cu.parent = 'GOV'
     AND ch.level_name = 'TOTAL'
     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 customer
ORDER BY customer;

This is the data extracted from the cube by the inner query:

CUSTOMER                                 TIME          SALES
---------------------------------------- -------- ----------
Dept. of Labor                           JAN-05      1553.26
Dept. of Labor                           MAR-05       1555.6
Ministry of Intl Trade                   JAN-05      1553.26
Ministry of Intl Trade                   FEB-05      1554.56
Ministry of Intl Trade                   MAR-05       1555.6
Royal Air Force                          JAN-05      1553.26
Royal Air Force                          FEB-05      6218.23
UK Environmental Department              JAN-05      4659.78
UK Environmental Department              FEB-05      3109.12

The outer query calculates the minimum, maximum, and average sales for each customer:

CUSTOMER                          MINIMUM    MAXIMUM    AVERAGE
------------------------------ ---------- ---------- ----------
Dept. of Labor                       1553       1556       1554
Ministry of Intl Trade               1553       1556       1554
Royal Air Force                      1553       6218       3886
UK Environmental Department          3109       4660       3884