Querying OLAP 11g Cubes

7/23/2019 Querying OLAP 11g Cubes

1/30


st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm 1/30

Querying OLAP 11g Cubes

Purpose

In this tutorial, you query OLAP data that was created using Analytic Workspace Manager 11g (AWM 11g). Using SQL Developer, youquery OLAP cubes directly using SQL.

You learn how to create analytic reports of cube data, including both stored and calculated meas ures, and apply techniques thatleverage unique characteristics of cubes .

Optionally, you learn how SQL summary queries agains t the relational fact table may be automatically re-written to the cube using CubeMVs.

Time to Complete:

Approximately 60 minutes

Topics

This tutorial covers the following topics:

Overview

Scenario

Prerequisites

Connecting to the OLAPTRAIN Schema in SQL Developer

Executing a Simple OLAP Cube Query

Using Level and Member Conditions in a Query

Adding Calculated Measuresto a Query

Leveraging Embedded Total Features of Cubes in a Query

Performing Drill-down in a Query

Using Parameterized Drilling

Using Cube MVs for Query Rewrite

More Information

Viewing Screenshots

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 s teps to load and view only the screenshotass ociated 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 s tarschema with the following characteristics:

- A cube view plays the role of a fact table.

- Dimens ion views and hierarchy views play the role of dimension tables.
http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t7http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t6http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t5http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t4http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t3http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t1http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#ohttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#mihttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t7http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t6http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t5ahttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t5http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t4http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t3http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t2http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t1http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#phttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#shttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#o


2/30



The star design exposed by OLAP cubes is very sim ilar to traditional table-based star models. The dimens ion views form aconstellation around one or m ore cube views. However, there are two key differences:

- Fact tables in a star schema s tore detail data (called leaves), while the cube views reveal many summary levels.

- Calculations in a cube are s imply exposed as colum ns in the cube view, and the computation for the equations occursin the OLAP engine.

These di fferences impact the way you query data. With s tar queries, you aggregate the data by combining aggregation functions (suchas sum) and the GROUP BY clause. With OLAP queries, you s imply 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 :

1. Select meas ures and dimension attributes.2. Join the cube and dimens ion views.3. Apply meas ure and dimension attribute conditions.4. Use All filters to leverage summaries for excluded dimens ion columns .

Back to Topic List

Scenario

The OLAP data for this tutorial was created us ing s teps found in the Building OLAP11g Cubestutorial. For information about the OLAPmodel used in this tutorial, and for step-by-step instructions on how to create OLAP 11g cubes , click the link.

Back to Topic List

Prerequisites

Before you perform this tutorial, you s hould:

1. Install Oracle Database 11g with the OLAP Option (Patch level 11.1.0.7 or higher).

2. Create a des ktop launcher for SQL Developer. (SQL Developer is shipped free with Oracle Database 11g.)

Then, download the following files to a location on the machine that contains SQL Developer:

cube_queries.sql

summary_queries.sql

Note: These files contain the completed olap cube and sql summary queries us ed in this tutorial.

3. Have access to the OLAP data model which is part of the Oracle OLAP 11g Sample Schema package. You caneither:
http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/Files/summary_queries.sqlhttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/Files/cube_queries.sqlhttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#thttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/BuildiCubes.htmhttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t


3/30



A. Succesfully complete the Building OLAP11g Cubestutorial, or

B. Install the OLAPTRAIN sample schema and the SALESTRACK analytic workspace, using theinstructions found in Installing the Oracle OLAP 11g Sample Schema.

Note: The SALESTRACK analytic workspace that is contained in the Sample Schema ins tallation packagecontains all of the OLAP data elements that are created in the Building OLAP11g Cubes tutorial. It also containssom e extra data elem ents that are referenced in other OLAP 11g collateral.

Back to Topic List

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 schem a, perform the following s teps:

1. Launch SQL Developer.

2. SelectView > Connectionsto display the Connections tab in the navigator pane.

3. In the Connections tab, right-click the Connections node, and select New Connectionfrom the menu.

4. In the New / Select Database Connection window, enter a Connection Name, Usernam e and Pass word. Then, inthe Oracle tab, enter or select the following options:
http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#thttp://www.oracle.com/technology/products/bi/olap/11g/samples/schemas/readme.htmlhttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/BuildiCubes.htm


4/30



Notes:

- Username is olaptrain.

- Password is the pass word 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.


5/30



6. Select olaptrain> Viewsto display the cube views for your OLAP data in the olaptrain schem a.

Note: The cube views are automatically created and maintained by Oracle OLAP when you create cubes usingAWM 11g.

Next, you will use these views to directly query the OLAP data that you created in the Building OLAP 11g Cubestutorial (or, that you created by using the Sample Schema ins tallation program).

Back to Topic List

Executing a Simple OLAP Cube Query

In this topic, you open a .sql file that contains a number of SQL queries agains t 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, opencube_queries.sql.

Result: the contents of cube_queries.sql appears in the Enter SQL Statement pane.
http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t


6/30



2. Close thelocal (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 olaptrainconnection from the list.

Result: The queries in this .sql file may now be executed against the olaptrain schem a.

Query Notes:

- Sales is s imply selected as a column. There are no SQL aggregation functions applied.

- A level within the Product dimension hierarchy -- DEPARTMENT -- is used to filter productmembers.

- All of the dimensions are qualified in the WHEREclause, even though only the Product dimens ion isselected. In OLAP cube queries, dimensions that are notselected in the query require an '"ALL"condition -- which specifies the top-level hierarchy value for each of the dimension columns -- inorder to leverage summaries that are already computed by the cube.


7/30



4. Place the cursor somewhere in the mids t of the query.

For example:

5. Select the Executetool (or press F9).

Result: The query should return three rows, and res ults should look like this:


8/30



By using the "All" filters, the aggregation is performed in the OLAP engine. Data is returned almos t instantaneous ly.

Back to Topic List

Using Level and Member Conditions in a Query

In the first query, a "Level" Condition was used for the Product dimens ion (which was the only dimension selected). You can apply levelconditions to all dimens ions in an OLAP query, as you will see in this next example.

Every OLAP hierarchy and dimension view contains a LEVEL_NAMEcolumn. The value in this colum n is the name of the OLAP hierarchyLevel object that you created when modeling the dim ension in AWM. By simply specifying a value for this column in the WHEREclause,you filter the data to include only those dimension mem bers 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 Developers Connections navigator. Simply drill on the view that you want to examine. You can also view the datavalues for any column by double-clicking the desi red 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 viewthe 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 year2007, and ALL REGIONS.


9/30



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 dimens ions, the dimension hierarchy view is used, and the LongDescription column is selected in each case. (Notice that column names are truncated at 24characters, exactly matching the dis play shown in the SQL Developer navigation pane.)

(C) Even though the Geography dimens ion is not in the SELECTs tatement, the geography hierarchyview is specified in the FROMclause. This technique enables you to specify the "ALL" condition forthe miss ing 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 leveragethe cube aggregations over geography. This is specified in the WHEREclause using the followingLevel condition: g.level_name = 'ALL_REGIONS'

(E) Level conditions are used to filter the data for the three dimens ions in the query, using theappropriate 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 typeof condition selects a specific dimens ion mem ber, 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 subs et -- all memberswhere the calendar year long description is 'CY2007') returns only those quarters in the calendaryear 2007.


10/30



2. Place the cursor som ewhere in the m idst of the query, as you did in the previous topic.

3. Select the Executetool (or press F9).

Result: the query result should look like this:

Scroll down to view the remaining results.

Back to Topic List

Adding Calculated Measures to a Query

The cube view exposes calculations as columns, which greatly simplifies the specification of analytic queries. Columns for calculatedmeas ures s how data as completely solved. However, the calculations are computed within the OLAP engine and passed through thecube view.

For example, only the SALES and QUANTITY measures in the SALES_CUBE_VIEW are stored measures. All of the other measurecolumns are calculated measures.


11/30



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:


12/30



Three calculated meas ures are added to the SELECTstatement.

round(s.SALES_YTD) as ytdround(s.SALES_YTD_PY_PCT_CHG, 2) as ytd_PY_pct_chghow_is_sales_ytd

No other modifications are neces ary. The calculated meas ures are computed within the OLAP engine and s imply passedthrough the cube view

2. Place the cursor som ewhere in the mids t of the query and press F9.

Result: The query executes just as fast as though all four measures were stored. The query results s hould look like this:



13/30



Note: For more information on how the calculated measures were created using AWM 11g, see Building OLAP11g Cubes.

Back to Topic List

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 anysum mary level within a dimens ion. Due to this feature of the OLAP model:

Any dimens ion member may be selectedregardless of the sum mary 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:
http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#thttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/BuildiCubes.htm


14/30



Notice how the filter for the Time dimension is m odified:

- In the previous query, all quarter members in the year 2007 are returned.

- In this query, a multiple-level "Member" condition is applied, us ing the Long Des cription attribute. Thiscondition selects three time dimension mem bers at different levels within the Calendary Year hierarchy:t.long_description in ('CY2007', 'Q3-CY2007', 'Nov-2007').


Results: All of the calculations work perfectly and query performance is unaffected.

The query results s hould look like this:



15/30



Back to Topic List

Performing Drill-down in a Query

Since dimens ion hierarchies are part of the data model, you can specify drills as part of your queries in order to return the children of aspecified 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 s how 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 mem bers.

Drills on the "ALL_REGIONS" member in order to return its children -- the geographical Regional m embers.

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 dimens ion was not selected.

- In this query, Geography is included, and Channel is left out of the SELECTstatement. Therefore, an "ALL"condition must be applied to the Channel dimension.

- A drill on the Geography dimension is executed by specifying a m ember in the PARENTcolumn of the geographyhierarchy view's : G.PARENT = "ALL_REGIONS". This condition returns the children of All Regions, which are thegeographic regional mem bers.

- A drill on the Product dimens ion is executed by specifying a m ember in the PARENTcolumn of the producthierarchy view's : P.PARENT = "ALL_PRODUCTS". This condition returns the children of All Products, which arethe product Department members.


16/30





Scroll down to view the remaining results. Notice how the drills on Product and Geography select the appropriate hierarchialchildren, and all of the calculations work perfectly.

Back to Topic List

Using Parameterize d Drilling

You can us e parameterized drilling in OLAP cube queries as well. Simply use a s ubstitution parameter with the PARENTcolumn toenable the user to enter a valid dimension member value.

In the following query example, a subs titution parameter is used to enable the user to enter a valid Time dimension mem ber value.

1. In the Enter SQL Statement window, scroll down to view the sixth query:


17/30



Query Notes:

- In this query, Geography is removed from the query, and Channel is included. Therefore, an "ALL" condition isapplied 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 PARENTcolumn. In addition, the nvlfunction is used so that if no value is provided, the "ALL_YEARS" member is

automatically used as the parent value.


Result: the Enter Bind Values window appears


18/30



Notes: Since the nvlfunction is used, "ALL_YEARS" is s pecified if no value is entered.

3. Do not enter anything in the Value box, and click Apply.


Notes:

- The query returns data for each year in the Time dim ension -- these are the children of ALL_YEARS in theCalendar Year hierarchy..

- Also notice that for CY2005, there are no data values for the YTD calculations. This is correct, because 2005 isthe first year in the data model (there is no 2004).


19/30



4. Once again, place the cursor somewhere in the mids t of the query and press F9. Then, in the Enter Bind Values window, enterCY2007in the Value box, as s hown here:

5. Click Apply.


The data is d isplayed for the quarters of 2007 (the children of CY2007).


20/30



Back to Topic List

Using Cube MVs for Query Rewrite

In an extens ion of the Materialized View capabilities for Oracle Database 11g, OLAP cubes can be represented as a cube-organizedmaterialized views (Cube MVs). The query optimizer automatically recognizes when an existing Cube MV can and s hould be us ed to

satis fy a SQL query was iss ued against a detailed fact table. A Cube MV represents a significant summ ary space, and benefits includeboth 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 anOracle BI Answers ad-hoc query sess ion agains t the olaptrain schema and are reflective of summary queries generated by general BItools..

First, you turn query rewrite off to see how the s ummary queries perform against the fact table. Then, you turn on rewrite and run thequeries again. When you execute the queries after turning on rewrite, you will:

Observe how the database automatically rewrites the summ ary queries to the OLAP Cube MVs

Compare the performance of the SQL summ ary 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 MVstopic inBuilding OLAP11g Cubes.

- In order to perform the s teps in this topic, you must have completed the (optional) Enabling Query Rewrite to Cube MVstopic in the Creating OLAP11g Cubes OBE, or installed the Sample Schema AW as described in the Prerequisitessection.

Follow these s teps:

1. In the Connections tab of the navigator pane, select the Materialized Viewsnode under the olaptrain connection.

Resul t: The Cube organized materialized views (CB$ tables) appear. There is one MV each for the cube and the as sociateddimens ion hierarchies. These cube MVs are automatically created and maintained by the OLAP option of Oracle 11g Database.
http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#phttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/BuildiCubes.htmhttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t


21/30



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 olaptrainconnection from the list, then vertically resize the SQLstatement pane s o 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 theALTER materialized VIEW cb$sales_cube disable queryrewritestatement, and then press F9.


22/30



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 m idst of the query, and press F6to display the Explain Plan for the query, as shown here:

The Explain Plan s hows that the query joins the fact table to the dimens ion tables specified in the query, and then performs a full tablescan of the fact table in order to return data the requested data.


23/30



6. With the cursor s till in the mids t of the query, and press F9to execute the query.


Make a note of the query performance, which is shown value appears at the top of the SQLStatement pane, as shown above. In thisexample, the query took approximately 3.05 seconds to run. The performance that you obs erve will depend on a number of factorsrelated to the configuration of your database server.


24/30



7. Click inside the second summary query, which is designed to return Sales by Year, Department, Class and Country.

a. Press F6to show the Explain Plan. As is the case with all the summary queries, a full table s can of the fact table willbe performed in order to return data the requested data.

b. Press F9to execute the query, as shown here. Again, record your query time.


25/30



8. Using the same techniques s hown 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:


26/30



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 theALTER SESSION SET query_rewrite_integrity=stale_toleratedstatement, and then press F9.

b. Click theALTER SESSION SET query_rewrite_enabled = forcestatement, and then press F9.


27/30



c. Click theALTER materialized VIEW cb$sales_cube enable query rewritestatement, and then press F9.

10. To confirm that the queries will rewrite, click within the first query and press F6to display the Explain Plan, as shown in the followingscreenshot:

Now, the summary queries will be rewritten to the cube MVs.


28/30



11. Press F9to execute the query, and make note of the improved performance.

12 Scroll down and click inside the second sum mary query. Press F6to confirm the rewrite, and then press F9to execute the query.


29/30




30/30


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.

Back to Topic List

More Information

For more information on OLAP-related education, use the following l inks:

Oracle Databas e 11g: OLAP Essentials(Oracle University inClass)

Building OLAP11g Cubes(OBE)

Using Oracle OLAP 11g With Oracle BI Enterprise Edition(OBE)

Creating Interactive APEX Reports Over OLAP 11g Cubes(OBE)

Back to Topic List

Place the cursor over this icon to hide all screenshots.
http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#thttp://www.oracle.com/technology/obe/olap-apex/usingapex4olap.htmhttp://www.oracle.com/technology/obe/olap_biee/index.htmlhttp://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/BuildiCubes.htmhttp://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D70039GC10&p_org_id=1001&lang=US&source_call=http://st-curriculum.oracle.com/obe/db/11g/r1/olap/cube/querycubes.htm#t

Documents

Querying OLAP 11g Cubes