Home > Querying Dimensional Objects > Using Calculations in Queries
![]() Previous |
![]() Next |
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