In this tutorial, you query OLAP data that was created using Analytic
Workspace Manager 11g (AWM 11g). Using SQL Developer, you query OLAP cubes directly using SQL.
You learn how to create analytic reports of cube data, including
both stored and calculated measures, and apply techniques that leverage unique
characteristics of cubes.
Optionally, you learn how SQL summary queries against the
relational fact table may be automatically re-written to the cube using Cube
MVs.
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor
over each individual icon in the following steps to load and view only the screenshot
associated with that step.
Overview
Oracle OLAP cube data is made directly accessible to SQL by
a set of relational views. These views represent an OLAP cube as a star schema
with the following characteristics:
- A cube view plays the role of a fact table.
- Dimension views and hierarchy views play the role of
dimension tables.
The star design exposed by OLAP cubes is very similar to traditional
table-based star models. The dimension views form a constellation around one
or more cube views. However, there are two key differences:
- Fact tables in a star schema store detail data (called
leaves), while the cube views reveal many summary levels.
- Calculations in a cube are simply exposed as columns
in the cube view, and the computation for the equations occurs in the OLAP
engine.
These differences impact the way you query data. With star
queries, you aggregate the data by combining aggregation functions (such as
sum) and the GROUP BY clause. With OLAP queries, you simply select the data
you want (either stored or calculated) as a column. Typically, no aggregation
function is necessary since the data has already been summarized by the cube.
For the vast majority of cube-based queries, there are four
basic steps:
Select measures and dimension attributes.
Join the cube and dimension views.
Apply measure and dimension attribute conditions.
Use “All” filters to leverage summaries for
excluded dimension columns.
The OLAP data for this tutorial was created using steps found
in the Building OLAP11g Cubes
tutorial. For information about the OLAP model used in this tutorial, and for
step-by-step instructions on how to create OLAP 11g cubes, click the link.
Note: The SALESTRACK analytic workspace that is contained in the Sample
Schema installation package contains all of the OLAP data elements that
are created in the Building OLAP11g Cubes tutorial. It also contains
some extra data elements that are referenced in other OLAP 11g collateral.
Connecting to the OLAPTRAIN
Schema in SQL Developer
In this tutorial, SQL Developer is used to query OLAP data,
although any SQL tool may be used
To connect to the OLAPTRAIN schema, perform the following
steps:
1.
Launch SQL Developer.
2.
Select View > Connections to display
the Connections tab in the navigator pane.
3.
In the Connections tab, right-click the Connections node,
and select New Connection from the menu.
4.
In the New / Select Database Connection window, enter
a Connection Name, Username and Password. Then, in the Oracle tab, enter
or select the following options:
Notes:
- Username is olaptrain.
- Password is the password that you created when you installed the
OLAPTRAIN schema.
- Hostname is the host name of the server where Oracle Database is
installed.
5.
Click Connect. The connection appears in the navigator.
6.
Select olaptrain > Views to
display the cube views for your OLAP data in the olaptrain schema.
Note: The cube views are automatically created and maintained by Oracle
OLAP when you create cubes using AWM 11g.
Next, you will use these views to directly query the OLAP data that
you created in the Building OLAP 11g Cubes tutorial (or, that you created
by using the Sample Schema installation program).
In this topic, you open a .sql file that contains a number
of SQL queries against the OLAP data.
Then, you examine and run a very simple OLAP query that returns
total SALES for products at the DEPARTMENT level.
1.
Select File > Open, and navigate
to the folder where you download the SQL query files. Then, open cube_queries.sql.
Result: the contents of cube_queries.sql appears in the Enter SQL Statement
pane.
2.
Close the local (olaptrain) tab by
clicking the X.
3.
Vertically resize the SQL statement pane so that you
can view the first query. Then, at the top-right corner of the Enter
SQL Statement pane, select the olaptrain connection
from the list.
Result: The queries in this .sql file may now be executed against the
olaptrain schema.
Query Notes:
- Sales is simply selected as a column. There are no SQL aggregation
functions applied.
- A level within the Product dimension hierarchy -- DEPARTMENT --
is used to filter product members.
- All of the dimensions are qualified in the WHERE
clause, even though only the Product dimension is selected. In OLAP
cube queries, dimensions that are not selected in the query
require an '"ALL" condition -- which specifies the top-level
hierarchy value for each of the dimension columns -- in order to leverage
summaries that are already computed by the cube.
4.
Place the cursor somewhere in the midst of the query.
For example:
5.
Select the Execute tool (or press F9).
Result: The query should return three rows, and results should look
like this:
By using the "All" filters, the aggregation is performed
in the OLAP engine. Data is returned almost instantaneously.
In the first query, a "Level" Condition was used
for the Product dimension (which was the only dimension selected). You can apply
level conditions to all dimensions in an OLAP query, as you will see in this
next example.
Every OLAP hierarchy and dimension view contains a LEVEL_NAME
column. The value in this column is the name of the OLAP hierarchy Level object
that you created when modeling the dimension in AWM. By simply specifying a
value for this column in the WHERE
clause, you filter the data to include only those dimension members at the specified
level in the hierarchy.
As shown below, you can examine the OLAP cube, dimension,
and hierarchy views, including column names and values for your queries, by
using SQL Developer’s Connections navigator. Simply drill on the view
that you want to examine. You can also view the data values for any column by
double-clicking the desired view and then selecting the Data tab.
As shown in the Connections tab of the navigation pane, OLAP
truncates column names at 24 characters. Therefore, it is helpful to view the
names of columns before using them in your queries.
The following query returns SALES for channels at the CLASS
level, products at the DEPARTMENT level, QUARTERS in calendar year 2007, and
ALL REGIONS.
1.
In the Enter SQL Statement window, scroll down to view
the second query:
Query Notes:
(A) Three of the four dimensions are selected -- Channel, Product,
and Time.
(B) For each of the selected dimensions, the dimension hierarchy
view is used, and the Long Description column is selected in each
case. (Notice that column names are truncated at 24 characters, exactly
matching the display shown in the SQL Developer navigation pane.)
(C) Even though the Geography dimension is not in the SELECT
statement, the geography hierarchy view is specified in the FROM
clause. This technique enables you to specify the "ALL"
condition for the missing dimension using the dimension's hierarchy
view, instead of the cube view.
(D) Since the Geography dimension is not in the query, an "ALL"
condition is required to leverage the cube aggregations over geography.
This is specified in the WHERE
clause using the following Level condition: g.level_name
= 'ALL_REGIONS'
(E) Level conditions are used to filter the data for the three dimensions
in the query, using the appropriate dimension hierarchy views, and
the appropriate hierarchy level values:
c.level_name
= 'CLASS'
p.level_name
= 'DEPARTMENT'
t.level_name
= 'CALENDAR_QUARTER'
(F) The filter on the Time dimension is further narrowed by applying
a "Member" condition. This type of condition selects a specific
dimension member, rather than all members at a particular level. The
member condition is: t.calendar_year_long_descr
= 'CY2007'
(E) For the time dimension, the combination of the level condtion
(selecting all members at the 'QUARTER' level), and then the member
condition (selecting -- from this subset -- all members where the
calendar year long description is 'CY2007') returns only those quarters
in the calendar year 2007.
2.
Place the cursor somewhere in the midst of the query,
as you did in the previous topic.
The cube view exposes calculations as columns, which greatly
simplifies the specification of analytic queries. Columns for calculated measures
show data as completely solved. However, the calculations are computed within
the OLAP engine and passed through the cube view.
For example, only the SALES and QUANTITY measures in the SALES_CUBE_VIEW
are stored measures. All of the other measure columns are calculated measures.
The OLAP calculations work perfectly across all summary levels,
even when the aggregation rules are complex.
In this next query, three OLAP calculated measures are added
to the previous query.
1.
In the Enter SQL Statement window, scroll down to view
the third query:
Three calculated measures are added to the SELECT
statement.
round(s.SALES_YTD)
as ytd
round(s.SALES_YTD_PY_PCT_CHG,
2) as ytd_PY_pct_chg
how_is_sales_ytd
No other modifications are necesary. The calculated measures are computed
within the OLAP engine and simply passed through the cube view
2.
Place the cursor somewhere in the midst of the query
and press F9.
Result: The query executes just as fast as though all four measures
were stored. The query results should look like this:
Scroll down to view the remaining results.
Note: For more information on how the calculated measures were created
using AWM 11g, see Building
OLAP11g Cubes.
Leveraging
Embedded Total Features of Cubes in a Query
Because of the embedded total nature of OLAP cubes, you can easily query multiple
levels at the same time to select values across any summary level within a dimension.
Due to this feature of the OLAP model:
Any dimension member may be selected—regardless
of the summary level.
Complex aggregations rules (for example,
balances) and calculations are automatically resolved in the OLAP engine.
In this next example, the previous query is modified to return
data for different levels for time: Month, Quarter, and Year in 2007.
1.
In the Enter SQL Statement window, scroll down to view
the fourth query:
Notice how the filter for the Time dimension is modified:
- In the previous query, all quarter members in the
year 2007 are returned.
- In this query, a multiple-level "Member" condition is
applied, using the Long Description attribute. This condition selects
three time dimension members at different levels within the Calendary
Year hierarchy: t.long_description
in ('CY2007', 'Q3-CY2007', 'Nov-2007').
2.
Place the cursor somewhere in the midst of the query
and press F9.
Results: All of the calculations work perfectly and query performance
is unaffected.
Since dimension hierarchies are part of the data model, you
can specify drills as part of your queries in order to return the children of
a specified parent member. You can drill with any type of hierarchy, including
level-based, skip-level, ragged, and value-based hierarchies.
In this example, the previous query is modified to show a
drill on the Product and Geography dimensions. Specifically, the query:
Drills on the "ALL_PRODUCTS"
member in order to return its children -- the product Department members.
Drills on the "ALL_REGIONS" member
in order to return its children -- the geographical Regional members.
Selects Channel members at the top level
in the hierarchy (rather than at the Class level)
1.
In the Enter SQL Statement window, scroll down to view
the fifth query:
Query Notes:
- In the previous query, the Geography dimension was
not selected.
- In this query, Geography is included, and Channel
is left out of the SELECT
statement. Therefore, an "ALL" condition must be applied
to the Channel dimension.
- A drill on the Geography dimension is executed by specifying a
member in the PARENT
column of the geography hierarchy view's: G.PARENT
= "ALL_REGIONS". This condition returns
the children of All Regions, which are the geographic regional members.
- A drill on the Product dimension is executed by specifying a member
in the PARENT column
of the product hierarchy view's: P.PARENT
= "ALL_PRODUCTS". This condition returns
the children of All Products, which are the product Department members.
2.
Place the cursor somewhere in the midst of the query
and press F9.
The query results should look like this:
Scroll down to view the remaining results. Notice how the drills on
Product and Geography select the appropriate hierarchial children, and
all of the calculations work perfectly.
You can use parameterized drilling in OLAP cube queries as
well. Simply use a substitution parameter with the PARENT
column to enable the user to enter a valid dimension member value.
In the following query example, a substitution parameter
is used to enable the user to enter a valid Time dimension member value.
1.
In the Enter SQL Statement window, scroll down to view
the sixth query:
Query Notes:
- In this query, Geography is removed from the query, and Channel
is included. Therefore, an "ALL" condition is applied to
the Geography dimension.
- Drills on the Channel and Product dimensions are executed in the
same way as the previous query.
- In addition, a parameterized drill is performed on the Time dimension
using the hierarchy view's PARENT
column. In addition, the nvl
function is used so that if no value is provided, the "ALL_YEARS"
member is automatically used as the parent value.
2.
Place the cursor somewhere in the midst of the query
and press F9.
Result: the Enter Bind Values window appears
Notes: Since the nvl
function is used, "ALL_YEARS" is specified if no value is
entered.
3.
Do not enter anything in the Value
box, and click Apply.
The query results should look like this:
Notes:
- The query returns data for each year in the Time dimension -- these
are the children of ALL_YEARS in the Calendar Year hierarchy..
- Also notice that for CY2005, there are no data values for the YTD
calculations. This is correct, because 2005 is the first year in the
data model (there is no 2004).
4.
Once again, place the cursor somewhere in the midst of
the query and press F9. Then, in the Enter Bind Values
window, enter CY2007 in the Value box, as shown here:
5.
Click Apply.
The query results should look like this:
The data is displayed for the quarters of 2007 (the children of CY2007).
In an extension of the Materialized View capabilities
for Oracle Database 11g, OLAP cubes can be represented as a cube-organized materialized
views (Cube MVs). The query optimizer automatically recognizes when an existing
Cube MV can and should be used to satisfy a SQL query was issued against a detailed
fact table. A Cube MV represents a significant summary space, and benefits include
both ease of manageability and improved query performance.
In this topic, you run a set of summary SQL queries against
the OLAPTRAIN schema. These summary queries were captured from an Oracle BI
Answers ad-hoc query session against the olaptrain schema and are reflective
of summary queries generated by general BI tools..
First, you turn query rewrite off to see how the summary queries
perform against the fact table. Then, you turn on rewrite and run the queries
again. When you execute the queries after turning on rewrite, you will:
Observe how the database automatically
rewrites the summary queries to the OLAP Cube MVs
Compare the performance of the SQL summary
queries to the OLAP Cube MVs queries.
Notes:
- For information on how to enable Cube MVs for query rewrite,
see the Enabling Query Rewrite to Cube MVs topic in Building
OLAP11g Cubes.
- In order to perform the steps in this topic, you must
have completed the (optional) Enabling Query Rewrite to Cube MVs
topic in the Creating OLAP11g Cubes OBE, or installed the Sample Schema AW
as described in the Prerequisites section.
Follow these steps:
1.
In the Connections tab of the navigator pane, select
the Materialized Views node under the olaptrain connection.
Result: The Cube organized materialized views (CB$ tables) appear.
There is one MV each for the cube and the associated dimension hierarchies.
These cube MVs are automatically created and maintained by the OLAP
option of Oracle 11g Database.
2.
Select File > Open, and navigate
to the folder where you download the SQL query files. Then, open summary_queries.sql.
At the top-right corner of the Enter SQL Statement pane, select the
olaptrain connection from the list, then vertically
resize the SQL statement pane so that it takes up at least half of the
space in the SQL Developer window, as shown here:
3.
To ensure that Query Rewrite to the Cube MV is turned
off, click the ALTER
materialized VIEW cb$sales_cube disable query rewrite
statement, and then press F9.
4.
Scroll down to view the first summary query, shown here:
This query returns Quantity and Sales by Year and product Category.
5.
Place your cursor in the midst of the query, and press
F6 to display the Explain Plan for the query, as shown
here:
The Explain Plan shows that the query joins the fact table to the
dimension tables specified in the query, and then performs a full table
scan of the fact table in order to return data the requested data.
6.
With the cursor still in the midst of the query, and
press F9 to execute the query.
The query results should look like this:
Make a note of the query performance, which is shown value appears
at the top of the SQLStatement pane, as shown above. In this example,
the query took approximately 3.05 seconds to run. The performance that
you observe will depend on a number of factors related to the configuration
of your database server.
7.
Click inside the second summary query, which is designed
to return Sales by Year, Department, Class and Country.
a. Press F6 to show the Explain Plan. As is the
case with all the summary queries, a full table scan of the fact table
will be performed in order to return data the requested data.
b. Press F9 to execute the query, as shown here.
Again, record your query time.
8.
Using the same techniques shown above, execute the third
and fourth queries. Record each of the query times.
In our example:
Summary query number 3 performed like this:
Summary query number 4 performed like this:
9.
Scroll back up in the SQL file and enable query rewrite
for the session, and to the OLAP cube MV, by performing the following:
a. Click the ALTER
SESSION SET query_rewrite_integrity=stale_tolerated
statement, and then press F9.
b. Click the ALTER
SESSION SET query_rewrite_enabled = force statement,
and then press F9.
c. Click the ALTER
materialized VIEW cb$sales_cube enable query rewrite
statement, and then press F9.
10.
To confirm that the queries will rewrite, click within
the first query and press F6 to display the Explain
Plan, as shown in the following screenshot:
Now, the summary queries will be rewritten to the cube MVs.
11.
Press F9 to execute the query, and
make note of the improved performance.
12
Scroll down and click inside the second summary query.
Press F6 to confirm the rewrite, and then press F9
to execute the query.
13.
Execute the remaining queries. Record each of the query
times.
Note: Query times for a Cube MV rewrite commonly are 10 to 50 times
faster than summary queries against relational fact tables.
14.
When you are done working with the query files, close them
and exit SQL Developer.
Bookmark
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, response time may be slow depending on your Internet connection.)
