| Oracle® OLAP Application Developer's Guide, 10g Release 2 (10.2) Part Number B14349-05 |
|
|
PDF · Mobi · ePub |
| Oracle® OLAP Application Developer's Guide, 10g Release 2 (10.2) Part Number B14349-05 |
|
|
PDF · Mobi · ePub |
You can query the rich data stored in dimensional objects using SQL. This chapter explains the basics of querying relational views of cubes and dimensions.
This chapter includes the following topics:
See Also:
Oracle OLAP adds power to your SQL applications by providing extensive analytic content and fast query response times. A SQL query interface enables any application to query cubes and dimensions without any knowledge of OLAP.
You can generate relational views of cubes and dimensions. SQL applications query these views to display the information-rich contents of dimensional objects to analysts and decision makers.
The SQL OLAP_TABLE function provides the basic technology for querying OLAP dimensional objects in SQL. You can use it for querying the objects directly or for creating views that can be queried with standard SQL SELECT statements. There are also some example programs for generating views available on the Oracle Technology Network.
See Also:
Oracle OLAP Reference for more information about OLAP_TABLE
Oracle Technology Network for a sample view generator at
http://www.oracle.com/technetwork/database/options/olap/index.html
You can create views of OLAP cubes and dimensions. For querying the data, you only need cube views. Cube views are denormalized views of all the measures, dimensions, levels, and attributes. They contain all of the data found in the tables of a star schema.
If you are developing an application, you can use dimension views as a convenient way to populate choice lists. Dimension views are equivalent to the dimension tables of a star schema.
Like a fact table, a cube view contains a column for each measure, calculated measure, and dimension of the cube. A cube view also contains columns for the parents, levels, and attributes of all the dimensions, so that the view is fully denormalized. These are the types of columns that should be included in a cube view:
Dimensions: The dimension columns contain all the dimension keys at all levels of the dimension. Example 4-1 describes the columns of a view of the Global Units Cube. There are columns for TIME, CUSTOMER, PRODUCT, and CHANNEL. The TIME column contains dimension keys for months, quarters, and years.
Parents: The parent columns define the parent-child relationships in a particular hierarchy. Example 4-1 shows a column name TIME_CALENDAR_YEA_PRNT that identifies the parent in the Calendar Year hierarchy of the dimension key in the TIME column. In this hierarchy, every month has a quarter for a parent, and every quarter has a year.
Hierarchy: The hierarchy columns provide the ancestor at each level of a particular dimension, using the description instead of the dimension key. Example 4-1 shows hierarchy columns named TIME_YEAR_LVLDSC, TIME_QUARTER_LVLDSC, and TIME_MONTH_LVLDSC. A TIME value at the quarter level has values at the year and quarter levels, but not at the month level.
Levels: The level columns identify the level of the dimension key. The TIME_LEVEL column in Example 4-1 has values of MONTH, QUARTER, and YEAR.
Attributes: The attribute columns contain the attribute values for the dimensions. Example 4-1 has attribute columns named TIME_END_DATE, TIME_TIME_SPAN, TIME_LDSC, TIME_SDSC, TIME_QUARTER_OF_YEAR, TIME_MONTH_OF_QUARTER, and TIME_MONTH_OF_YEAR.
Measures: The measure columns contain the facts for all combinations of dimension keys at all levels of the hierarchy. Thus, a cube view returns data at all levels of aggregation, from the detail level to the topmost level of consolidation. Example 4-1 has columns for the UNITS and SALES measures.
Calculated Measures: These columns contain the calculated measures that have been defined for the cube. Like the measure columns, they contain business facts at all levels of aggregation. Example 4-1 shows that the Units Cube has calculated measures named SALES_PCT_CHG_PP, SALES_CHG_PP, PROD_SALES_SHARE_WITHIN_TOTAL, PROD_SALES_SHARE_WITHIN_PARENT, AND THREE_PERIOD_MOVING_AVG_SALES.
The DSO and OLAP_CALC columns shown in Example 4-1 are not queried by SQL.
Example 4-1 Cube View of the Global Units Cube
SQL> DESCRIBE units_cube_cubeview
Name Null? Type
----------------------------------------- -------- ----------------------------
TIME VARCHAR2(4000)
CUSTOMER VARCHAR2(4000)
PRODUCT VARCHAR2(4000)
CHANNEL VARCHAR2(4000)
TIME_CALENDAR_YEA_PRNT VARCHAR2(4000)
TIME_YEAR_LVLDSC VARCHAR2(4000)
TIME_QUARTER_LVLDSC VARCHAR2(4000)
TIME_MONTH_LVLDSC VARCHAR2(4000)
TIME_END_DATE DATE
TIME_TIME_SPAN NUMBER
TIME_LDSC VARCHAR2(4000)
TIME_SDSC VARCHAR2(4000)
TIME_QUARTER_OF_YEAR VARCHAR2(4000)
TIME_MONTH_OF_QUARTER VARCHAR2(4000)
TIME_MONTH_OF_YEAR VARCHAR2(4000)
TIME_TIME_DSO_1 NUMBER
TIME_LEVEL VARCHAR2(4000)
.
.
.
UNITS NUMBER
SALES NUMBER
SALES_PCT_CHG_PP NUMBER
SALES_CHG_PP NUMBER
PROD_SALES_SHARE_WITHIN_TOTAL NUMBER
PROD_SALES_SHARE_WITHIN_PARENT NUMBER
THREE_PERIOD_MOVING_AVG_SALES NUMBER
OLAP_CALC RAW(16)
You can display the facts in a cube view quickly with a query like the one shown in Example 4-2.
Example 4-2 Querying the Facts in a Cube View
SQL> SELECT time, customer, product, channel, units, sales
FROM units_cube_cubeview WHERE rownum < 15;
TIME CUSTOMER PRODUCT CHANNEL UNITS SALES
---------------- ---------------- ---------------- ---------------- ---------- ----------
YEAR_145 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1
YEAR_4 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 415392 116931479
YEAR_2 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 330425 134109248
YEAR_3 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 364233 124173522
YEAR_1 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 253816 100870877
YEAR_85 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 364965 92515295
YEAR_119 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 339831 80846147.8
YEAR_102 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 534069 130276515
QUARTER_12 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 87521 33761936.8
QUARTER_13 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 88484 31522409.5
QUARTER_81 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 84100 21499269.6
QUARTER_141 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1
QUARTER_143 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1
QUARTER_6 TOTAL_CUSTOMER_1 TOTAL_PRODUCT_1 TOTAL_CHANNEL_1 61320 24993273.3
14 rows selected.
A query like the one in Example 4-3 displays the level, ancestry, and attributes of each dimension key.
Example 4-3 Querying a Dimension in a Cube View
SQL> SELECT time, time_level, time_calendar_yea_prnt parent, time_year_lvldsc year,
time_quarter_lvldsc quarter, time_month_lvldsc month, time_ldsc description,
time_end_date end_date, time_time_span time_span
FROM units_cube_cubeview WHERE rownum < 15;
TIME TIME_LEVEL PARENT YEAR QUARTER MONTH DESCRIPTION END_DATE TIME_SPAN
------------ ---------- ---------- -------- -------- -------- ------------ --------- ----------
YEAR_145 YEAR 2005 2005 31-DEC-05 365
YEAR_4 YEAR 2001 2001 31-DEC-01 365
YEAR_2 YEAR 1999 1999 31-DEC-99 365
YEAR_3 YEAR 2000 2000 31-DEC-00 366
YEAR_1 YEAR 1998 1998 31-DEC-98 365
YEAR_85 YEAR 2002 2002 31-DEC-02 365
YEAR_119 YEAR 2004 2004 31-DEC-04 366
YEAR_102 YEAR 2003 2003 31-DEC-03 365
QUARTER_12 QUARTER YEAR_2 1999 Q4-99 Q4-99 31-DEC-99 92
QUARTER_13 QUARTER YEAR_3 2000 Q1-00 Q1-00 31-MAR-00 91
QUARTER_81 QUARTER YEAR_85 2002 Q1-02 Q1-02 31-MAR-02 90
QUARTER_141 QUARTER YEAR_145 2005 Q1-05 Q1-05 31-MAR-05 90
QUARTER_143 QUARTER YEAR_145 2005 Q3-05 Q3-05 30-SEP-05 92
QUARTER_6 QUARTER YEAR_1 1998 Q2-98 Q2-98 30-JUN-98 91
14 rows selected.
A dimension view contains all the information typically found in a dimension table of a star schema. The view contains a column for the dimension keys, parents, levels, hierarchies, and attributes. All of these columns are also found in the cube views, so there is no need to join a cube view to the dimension views. However, you may want to create dimension views to support user interface components such as choice lists.
Example 4-4 shows the columns of a dimension view for the Global Time dimension. For descriptions of these columns, refer to "Cube Views".
Example 4-4 Dimension View for the Global Time Dimension
SQL> DESCRIBE time_dimview Name Null? Type ----------------------------------------- -------- ---------------------------- TIME VARCHAR2(4000) TIME_LEVEL VARCHAR2(4000) TIME_TIME_DSO_1 NUMBER TIME_MONTH_OF_YEAR VARCHAR2(4000) TIME_MONTH_OF_QUARTER VARCHAR2(4000) TIME_QUARTER_OF_YEAR VARCHAR2(4000) TIME_SDSC VARCHAR2(4000) TIME_LDSC VARCHAR2(4000) TIME_TIME_SPAN NUMBER TIME_END_DATE DATE TIME_MONTH_LVLDSC VARCHAR2(4000) TIME_QUARTER_LVLDSC VARCHAR2(4000) TIME_YEAR_LVLDSC VARCHAR2(4000) TIME_CALENDAR_YEA_PRNT VARCHAR2(4000)
You can display the hierarchical information provided in a dimension view with a query like the one shown in Example 4-5.
Example 4-5 Querying the Global Time Calendar Hierarchy
SQL> SELECT time, time_level, time_calendar_yea_prnt parent,
time_year_lvldsc year,time_quarter_lvldsc quarter, time_month_lvldsc month
FROM time_dimview WHERE rownum < 15;
TIME TIME_LEVEL PARENT YEAR QUARTER MONTH
------------ ---------- ---------- -------- -------- --------
YEAR_145 YEAR 2005
YEAR_4 YEAR 2001
YEAR_2 YEAR 1999
YEAR_3 YEAR 2000
YEAR_1 YEAR 1998
YEAR_85 YEAR 2002
YEAR_119 YEAR 2004
YEAR_102 YEAR 2003
QUARTER_12 QUARTER YEAR_2 1999 Q4-99
QUARTER_13 QUARTER YEAR_3 2000 Q1-00
QUARTER_81 QUARTER YEAR_85 2002 Q1-02
QUARTER_141 QUARTER YEAR_145 2005 Q1-05
QUARTER_143 QUARTER YEAR_145 2005 Q3-05
QUARTER_6 QUARTER YEAR_1 1998 Q2-98
14 rows selected.
A query like the one shown in Example 4-6 displays the attributes of the dimension.
Example 4-6 Querying the Global Time Attributes
SQL> SELECT time, time_ldsc description, time_month_of_year mo_of_yr,
time_month_of_quarter mo_of_qtr, time_end_date end_date,
time_time_span time_span
FROM time_dimview
WHERE time_month_of_year IS NOT NULL AND rownum < 15
ORDER BY end_date;
TIME DESCRIPTION MO_OF_YR MO_OF_QTR END_DATE TIME_SPAN
------------ ------------ ---------- ---------- --------- ----------
MONTH_29 Nov-98 11 2 30-NOV-98 30
MONTH_35 May-99 5 2 31-MAY-99 31
MONTH_36 Jun-99 6 3 30-JUN-99 30
MONTH_40 Oct-99 10 1 31-OCT-99 31
MONTH_41 Nov-99 11 2 30-NOV-99 30
MONTH_52 Oct-00 10 1 31-OCT-00 31
MONTH_55 Jan-01 1 1 31-JAN-01 31
MONTH_66 Dec-01 12 3 31-DEC-01 31
MONTH_75 Jul-02 7 1 31-JUL-02 31
MONTH_120 Jun-04 6 3 30-JUN-04 30
MONTH_123 Sep-04 9 3 30-SEP-04 30
MONTH_126 Dec-04 12 3 31-DEC-04 31
MONTH_127 Jan-05 1 1 31-JAN-05 31
MONTH_135 Sep-05 9 3 30-SEP-05 30
14 rows selected.
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 already 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 cube or dimension views:
SQL> SELECT DISTINCT time_level FROM units_cube_cubeview; TIME_LEVEL ---------- QUARTER MONTH YEAR
To see the importance of applying a filter to every dimension, consider the query in Example 4-7, which has no filter on the time dimension.
Example 4-7 Displaying Aggregates at All Levels of Time
/* Select key descriptions and facts */
SELECT time_ldsc time,
ROUND(sales) sales
/* From cube view */
FROM units_cube_cubeview
/* No filter on Time */
WHERE product_level = 'TOTAL_PRODUCT'
AND customer_level = 'TOTAL_CUSTOMER'
AND channel_level = 'TOTAL_CHANNEL'
ORDER BY time_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 degrade performance.
TIME SALES
------------ ----------
Jan-98 8338545
Feb-98 7972132
Q1-98 24538588
Mar-98 8227911
Apr-98 8470315
May-98 8160573
Q2-98 24993273
Jun-98 8362386
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 4-8 shows a basic query. It selects the long description attributes of Time and 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.
Example 4-8 Basic Cube View Query
/* Select key descriptions and facts */
SELECT time_ldsc time,
ROUND(sales) sales
/* From cube view */
FROM units_cube_cubeview
/* Filters on all dimensions */
WHERE time_level = 'YEAR'
AND product_level = 'TOTAL_PRODUCT'
AND customer_level = 'TOTAL_CUSTOMER'
AND channel_level = 'TOTAL_CHANNEL'
ORDER BY time_end_date;
Example 4-8 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 116931479 2002 92515295 2003 130276515 2004 80846148 2005 8 rows selected.
Dimension attributes also provide a useful way to select the data for a query. The WHERE clause in Example 4-9 uses attributes values to filter all of the dimensions.
Example 4-9 Selecting Data With Attribute Filters
/* Select key descriptions and facts */
SELECT time_ldsc time,
product_ldsc product,
customer_ldsc customer,
ROUND(sales) sales
/* From dimension views and cube view */
FROM units_cube_cubeview
/* Create attribute filters */
WHERE time_ldsc IN ('2001', '2002')
AND product_package = 'Laptop Value Pack'
AND customer_ldsc LIKE '%Boston%'
AND channel_ldsc = 'Internet'
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 SALES ---------- ------------------------------ ---------------------- ---------- 2001 Laptop carrying case KOSH Entrpr Boston 4995 2001 56Kbps V.92 Type II Fax/Modem KOSH Entrpr Boston 9683 2001 Internal 48X CD-ROM KOSH Entrpr Boston 2122 2001 Envoy Standard KOSH Entrpr Boston 24335 2001 Standard Mouse KOSH Entrpr Boston 419 2001 Laptop carrying case Warren Systems Boston 747 2001 Standard Mouse Warren Systems Boston 107 2001 56Kbps V.92 Type II Fax/Modem Warren Systems Boston 1743 2001 Envoy Standard Warren Systems Boston 14438 2001 Internal 48X CD-ROM Warren Systems Boston 129 2002 Internal 48X CD-ROM KOSH Entrpr Boston 2161 2002 56Kbps V.92 Type II Fax/Modem KOSH Entrpr Boston 17573 2002 Envoy Standard KOSH Entrpr Boston 2002 Standard Mouse KOSH Entrpr Boston 487 2002 Laptop carrying case KOSH Entrpr Boston 5584 2002 Laptop carrying case Warren Systems Boston 3357 2002 Envoy Standard Warren Systems Boston 24511 2002 56Kbps V.92 Type II Fax/Modem Warren Systems Boston 1249 2002 Standard Mouse Warren Systems Boston 142 2002 Internal 48X CD-ROM Warren Systems Boston 20 rows selected.
A cube contains all of the aggregate data. As shown in this chapter, a query against a cube just needs to select the aggregate data, not calculate the values.
The following is a basic query against a fact table:
/* Querying a fact table */
SELECT t.year_dsc time,
SUM(sales) sales
FROM time_dim t, units_history_fact f
WHERE t.year_dsc IN ('2001', '2002')
AND t.month_id = f.month_id
GROUP BY t.year_dsc;
The next query fetches the exact same results from a cube using filters:
/* Querying a cube */
SELECT time_ldsc time, sales
FROM units_cube_cubeview
/* Apply filters to every dimension */
WHERE time_ldsc IN ('2001', '2002')
AND product_level = 'TOTAL_PRODUCT'
AND customer_level = 'TOTAL_CUSTOMER'
AND channel_level = 'TOTAL_CHANNEL'
ORDER BY time;
Both queries return these results:
TIME SALES ---------- ---------- 2001 116931479 2002 92515295
The query against the cube does not compute the aggregate values with a SUM operator and GROUP BY clause. Because the aggregates exist already 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.
Drilling is an important capability in business analysis. In a dashboard or an application, users click a dimension key to change the selection of data. Decision makers frequently want to drill down to see the contributors to a data value, or drill up to see how a particular data value contributes to the whole. For example, the Boston regional sales manager might start at total Boston sales, drill down to see the contributions of each sales representative, then drill up to see how the Boston region contributes to the New England sales total.
The views include a parent column that identifies the parent of every dimension key. This column encapsulates all of the hierarchical information of the dimension. If you know the parent of every key, then you can derive the ancestors, the children, and the descendants.
For level-based hierarchies, the level column supplements this information by providing a convenient way to identify all the keys at the same depth in the hierarchy, from the top to the base. For value-based hierarchies, the parent column provides all the information about the hierarchy.
See Also:
Chapter 10, "Developing Reports and Dashboards," about using bind variables to support drillingYou can use the parent column of a view to select only the children of a particular value. The following WHERE clause selects the children of calendar year 2001.
/* Select children of calendar year 2001 */
WHERE time_calendar_yea_prnt = 'YEAR_4'
AND product = 'TOTAL_PRODUCT_1'
AND customer = 'TOTAL_CUSTOMER_1'
AND channel = 'TOTAL_CHANNEL_1'
The query drills down from Year to Quarter. The four quarters Q1-05 to Q4-05 are the children of year CY2005 in the Calendar hierarchy.
TIME SALES ---------- ---------- Q1-01 27595330 Q2-01 27798427 Q3-01 29691668 Q4-01 3184605
The parent column of a hierarchy view identifies the parent of each dimension key. The following WHERE clause selects the parent of a Time key based on its long description attribute.
WHERE time = (SELECT DISTINCT time_calendar_yea_prnt FROM units_cube_cubeview WHERE time_ldsc='Sep-01') AND product = 'TOTAL_PRODUCT_1' AND customer = 'TOTAL_CUSTOMER_1' AND channel = 'TOTAL_CHANNEL_1'
The query drills up from Month to Quarter. The parent of month Sep-01 is the quarter Q3-01 in the Calendar Year hierarchy.
TIME SALES ---------- ---------- Q3-01 29691668
The following WHERE clause selects the descendants of calendar year 2001 by selecting the rows with a Time level of MONTH and a year of 2001.
/* Select Time level and ancestor */
WHERE time_level = 'MONTH'
AND time_year_lvldsc = '2001'
AND product = 'TOTAL_PRODUCT_1'
AND customer = 'TOTAL_CUSTOMER_1'
AND channel = 'TOTAL_CHANNEL_1'
The query drills down two levels, from year to quarter to month. The 12 months Jan-01 to Dec-01 are the descendants of year 2001 in the Calendar Year hierarchy.
TIME SALES ---------- ---------- Jan-01 9377798 Feb-01 9080969 Mar-01 9136563 Apr-01 9145284 May-01 9028805 Jun-01 9624338 Jul-01 9789531 Aug-01 9581753 Sep-01 10320384 Oct-01 10117410 Nov-01 10866341 Dec-01 10862303 12 rows selected.
The hierarchy views provide the full ancestry of each dimension key. The following WHERE clause uses the year level key column to identify the ancestor of a MONTH dimension key.
/* Select the ancestor of a Time key based on its Long Description attribute */
WHERE time_ldsc =
(SELECT distinct time_year_lvldsc
FROM units_cube_cubeview
WHERE time_ldsc = 'Sep-01')
AND product = 'TOTAL_PRODUCT_1'
AND customer = 'TOTAL_CUSTOMER_1'
AND channel = 'TOTAL_CHANNEL_1'
The query drills up two levels from month to quarter to year. The ancestor of month Sep-01 is the year 2001 in the Calendar hierarchy.
TIME SALES ---------- ---------- 2001 116931479
A DBA can create calculated measures in Analytic Workspace Manager, 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 only need to make sure 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 4-10 shows a query that answers the question, What was the average sales of Sentinel Standard computers to Government customers for the third quarter of calendar year 2001. UNITS_CUBE is summed over all dimensions, so that QUARTER_67 (Q3-01) 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.
Example 4-10 Calculating Average Sales Across Customers
SELECT customer, ROUND(MIN(sales)) minimum, ROUND(MAX(sales)) maximum,
ROUND(AVG(sales)) average
FROM
(SELECT customer_ldsc customer, time_ldsc time, sales
FROM units_cube_cubeview
WHERE time_calendar_yea_prnt = 'QUARTER_67'
AND product_ldsc = 'Sentinel Standard'
AND customer_market_segme_prnt = 'MARKET_SEGMENT_4'
AND channel_level = 'TOTAL_CHANNEL'
)
GROUP BY customer
ORDER BY customer;
This is the data extracted from the cube by the inner query:
CUSTOMER TIME SALES ------------------------------ ---------- ---------- Dept. of Communication Aug-01 1752.06 Dept. of Communication Jul-01 5344.2 Dept. of Communication Sep-01 3507.06 Dept. of Labor Aug-01 Dept. of Labor Sep-01 1753.53 Dept. of Labor Jul-01 3562.8 Ministry of Finance Jul-01 1781.4 Ministry of Finance Aug-01 3504.12 Ministry of Finance Sep-01 7014.12 Ministry of Intl Trade Jul-01 5344.2 Ministry of Intl Trade Sep-01 5260.59 Ministry of Intl Trade Aug-01 5256.18 Royal Air Force Jul-01 3562.8 Royal Air Force Sep-01 3507.06 Royal Air Force Aug-01 8760.3 UK Environmental Department Aug-01 UK Environmental Department Sep-01 UK Environmental Department Jul-01 3562.8 US Dept. of Research Jul-01 1781.4 US Dept. of Research Aug-01 1752.06 US Dept. of Research Sep-01 1753.53 US Marine Services Sep-01 US Marine Services Aug-01 US Marine Services Jul-01
The outer query calculates the minimum, maximum, and average sales for each customer:
CUSTOMER MINIMUM MAXIMUM AVERAGE ------------------------------ ---------- ---------- ---------- Dept. of Communication 1752 5344 3534 Dept. of Labor 1754 3563 2658 Ministry of Finance 1781 7014 4100 Ministry of Intl Trade 5256 5344 5287 Royal Air Force 3507 8760 5277 UK Environmental Department 3563 3563 3563 US Dept. of Research 1752 1781 1762 US Marine Services
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 4-11 shows a query that aggregates over an attribute named Package. It returns these results:
TIME PACKAGE SALES ---------- -------------------- ---------- 2001 All 2176753.8 2001 Executive 25793371.5 2001 Laptop Value Pack 16118203.4 2001 Multimedia 19887248.8
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
Example 4-11 Aggregating Over an Attribute
SELECT time_ldsc time,
product_package package,
SUM(sales) sales
/* From cube view */
FROM units_cube_cubeview
/* Filters on all dimensions */
WHERE time_ldsc = '2001'
AND product_level = 'ITEM'
AND product_package IS NOT NULL
AND customer_level = 'TOTAL_CUSTOMER'
AND channel_level = 'TOTAL_CHANNEL'
GROUP BY time_ldsc, product_package
ORDER BY product_package;
Before using the technique described in "Aggregating Measures Over Attributes", be sure 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 4-12 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:
TIME PACKAGE SALES PRIOR_PERIOD PCT_CHG ---------- -------------------- ---------- ------------ ---------- 2001 All 2176753.8 2048166.74 .06 2002 All 1840963.8 2176753.8 -.15 2001 Executive 25793371.5 26391852.4 -.02 2002 Executive 18717348.1 25793371.5 -.27 2001 Laptop Value Pack 16118203.4 18884919 -.15 2002 Laptop Value Pack 11085266.8 16118203.4 -.31 2001 Multimedia 19887248.8 21262926.7 -.06 2002 Multimedia 16218667.2 19887248.8 -.18 8 rows selected.
Example 4-12 Querying Over Attributes Using Calculated Measures
/* Calculate Percent Change */
SELECT time, package, sales, prior_period,
round((sales - prior_period) / prior_period, 2) pct_chg
FROM
(SELECT time_ldsc time, product_package package,
sum(sales) sales, sum(sales_pp) prior_period
FROM units_cube_cubeview
WHERE product_level = 'ITEM'
AND product_package IS NOT NULL
AND time_ldsc IN ('2001', '2002')
AND customer_level = 'TOTAL_CUSTOMER'
AND channel_level = 'TOTAL_CHANNEL'
GROUP BY time_ldsc, product_package
ORDER BY package);
If you are developing a generic application -- that is, one where the names of the dimensional objects are not known -- then your application can retrieve this information from the Active Catalog.
The Active Catalog is a set of views that display metadata for dimensional objects. These views always reflect the current state of the analytic workspace. You can query the views using standard SQL.
Active Catalog views provide information about dimensional objects in all analytic workspaces accessible to the current user. The public synonyms for these views are named with the ALL_OLAP2_AW prefix.
Table 4-1 provides brief descriptions of the Active Catalog views.
Table 4-1 Active Catalog Views
| PUBLIC Synonym | Description |
|---|---|
|
|
List of dimension attributes in analytic workspaces |
|
|
Lists the measures in the measure folders |
|
|
Lists the measure folders in analytic workspaces |
|
|
List of levels in aggregation plans in analytic workspaces |
|
|
List of measures in aggregation plans in analytic workspaces |
|
|
List of aggregation operators in aggregation plans in analytic workspaces |
|
|
List of aggregation plans in analytic workspaces |
|
|
List of cubes with their associated dimensions in analytic workspaces |
|
|
List of cubes with their associated measures in analytic workspaces |
|
|
List of cubes in analytic workspaces |
|
|
List of hierarchical levels in analytic workspaces |
|
|
List of levels in analytic workspaces |
|
|
List of dimensions in analytic workspaces |
|
|
List of properties associated with standard form objects in analytic workspaces |
|
|
List of standard form objects in analytic workspaces |
|
|
Lists the analytic workspaces |
See Also:
Oracle OLAP Reference for more information about the Active Catalog
|
 Copyright © 2003, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|