### Querying OLAP Cubes

In this tutorial, you query OLAP data that was created using Analytic Workspace Manager (AWM). 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.

NOTE: This tutorial requires either Oracle Database 11.2 or 12.1.

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 Measures to 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

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:

1. Select measures and dimension attributes.
2. Join the cube and dimension views.
3. Apply measure and dimension attribute conditions.
4. Use “All” filters to leverage summaries for excluded dimension columns.

### Scenario

The OLAP data for this tutorial was created using steps found in the Building OLAP Cubes tutorial. For information about the OLAP model used in this tutorial, and for step-by-step instructions on how to create OLAP cubes, click the link.

Before you perform this tutorial, you should:

 1 Install Oracle Database 11.2 or 12.1 with the OLAP Option. 2 Create a desktop launcher for SQL Developer. (SQL Developer is shipped free with Oracle Database.) Then, download and unzip the following files to a location on the machine that contains SQL Developer: cube_queries.zip summary_queries.zip Note: These files contain the completed olap cube and sql summary queries used in this tutorial. 3 Have access to the OLAP data model which is part of the Oracle OLAP Sample Schema package. You can either: A. Succesfully complete the Building OLAP Cubes tutorial, or B. Install the OLAPTRAIN sample schema and the SALESTRACK analytic workspace, using the instructions found in Installing the Oracle OLAP Sample Schema. 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 OLAP Cubes tutorial. It also contains some extra data elements that are referenced in other OLAP 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:

### Executing a Simple OLAP Cube Query

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.

### Using Level and Member Conditions in a Query

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 2009, and ALL REGIONS.

### Adding Calculated Measures to a Query

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, see Building OLAP Cubes.

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 2009.

#### 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 2009 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 ('CY2009', 'Q3-CY2009', 'Nov-2009').   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. The query results should look like this: Scroll down to view the remaining results.

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 CY2007, there are no data values for the YTD calculations. This is correct, because 2007 is the first year in the data model (there is no 2006).   4. Once again, place the cursor somewhere in the midst of the query and press F9. Then, in the Enter Bind Values window, enter CY2009 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 2009 (the children of CY2009).

In an extension of the Materialized View capabilities for Oracle Database, 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 OLAP 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 OLAP Cubes OBE, or installed the Sample Schema AW as described in the Prerequisites section.