A look inside Oracle OLAP cubes and Analytic Workspaces – Page 1

The purpose of this article is to explore the mechanisms used by the oracle database engine to store and access data inside Oracle OLAP cubes and Analytic Workspaces (AW).

A previous article located here describes an implementation of “Summary Management” by using relational materialized views and relational SQL dimensions.

Another previous article located here describes an implementation of “Summary Management” by using OLAP cubes, dimensions and cube-organized materialized views.

Oracle OLAP cubes are used within the context of “Summary Management” to aggregate and store fact data along one or more dimensions. In addition to the aggregated measures obtained from fact table data, the Oracle OLAP cubes can also contain “calculated measures”, which can be defined by using OLAP expression syntax, OLAP DML expressions, or OLAP DML functions.

Because Oracle OLAP cubes contain data typically aggregated along several dimensions, they can be thought of as “hyper-cubes”, or multi-dimensional cubes. Implementation-wise, an oracle OLAP cube is an oracle object, whose definition exists in the oracle data dictionary.

Conceptually, an Analytic Workspace (AW) can be thought of as a container within the Oracle Database, that is used by Oracle OLAP to perform OLAP analysis.

An oracle OLAP cube is stored inside an oracle Analytic Workspace (AW).

Let’s consider the diagram shown in Figure 1 below (click on it to enlarge):

 Figure 1 shown above lists various objects from schema GLOBAL, which is one of the oracle schemas that are provided with sample data and objects.

 The area of Figure 1 is divided into three regions – top, middle and bottom.

1. The top region shows information about tables, segments, tablespaces, extents and blocks, along with relevant information about indexes and lobs. The object sizes have been aggregated from extent sizes.
2. The middle region shows information about tables and segments, along with relevant information about indexes and lobs. The object sizes have been aggregated from segment, index and lob sizes.
3. The bottom region shows total table sizes by schema, including index and lob storage. The object sizes have been aggregated from segment, index and lob sizes.

Figure 2 below shows some details about the GLOBAL Analytic Workspace (AW), which is stored inside schema GLOBAL, as displayed by the Analytic Workspace Manager (AWM) tool.

The schema GLOBAL is an oracle schema which contains sample data and OLAP objects such as dimensions, hierarchies and cubes.

Now, let’s take a step back, and see how we got to the information shown in Figure 1 above.

First, let’s look at what information related to Analytic Workspaces (AW’s) is available to user GLOBAL:

SELECT * FROM USER_AWS ORDER BY AW_NAME;  --(Q1)


We can see the returned result shown in Figure 3 below:

So, the oracle data dictionary query listed above confirmed what the AWM tool is showing us – namely, that the user GLOBAL has access to an Analytic Workspace (AW) named GLOBAL. Furthermore, the AW named GLOBAL is contained within schema GLOBAL.

Now, let’s see what objects exist in the oracle data dictionary, owned by the schema GLOBAL and having the name like '%GLOBAL%':

SELECT * FROM ALL_OBJECTS
WHERE UPPER(OBJECT_NAME) LIKE '%GLOBAL%'
AND OWNER = 'GLOBAL'
ORDER BY OWNER, OBJECT_NAME, OBJECT_TYPE, SUBOBJECT_NAME;


We see the result of the above query, shown in Figure 4 below:

 In the result set shown above, we have the following distinct types of objects: INDEX, SEQUENCE, TABLE, TABLE PARTITION, TABLE SUBPARTITION.
Three of those types of objects (TABLE, TABLE PARTITION, TABLE SUBPARTITION) are associated with object name AW$GLOBAL.

According to the oracle documentation, the naming convention for an Analytic Workspace (AW) is AW$workspacename. Looking back at query (Q1) above and its’ result, in our case, the name of the AW from the data dictionary is GLOBAL, as shown also by the AWM tool in Figure 2. If we prepend the prefix AW$ we get AW$GLOBAL. This means that in Figure 4 above, the Analytic Workspace object AW$GLOBAL consists of various types of objects: TABLE, TABLE PARTITION, TABLE SUBPARTITION, all of which are stored in schema GLOBAL.

 According to the oracle documentation, an Analytic Workspace is physically stored in the Database as LOBs in a table named AW$workspacename. This is confirmed by our findings as described in the paragraph above: the database object AW$GLOBAL is in fact an Analytic Workspace consisting of objects of type TABLE, TABLE PARTITION, TABLE SUBPARTITION. We will look further into the structure of table AW$GLOBAL below.

Let’s consider the following SQL query below:

SELECT
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE ,
DATA_TYPE_MOD ,
DATA_TYPE_OWNER,
DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'GLOBAL'
AND TABLE_NAME = 'AW$GLOBAL';


 Figure 5 below shows the result set:

We see that one of the columns in table AW$GLOBAL is of type BLOB, which confirms what the oracle documentation states about AW’s being physically stored as LOBs inside tables.

Now, let’s calculate the total size of the BLOB column values in table AW$GLOBAL:

select
sum(dbms_lob.getlength(awlob)) SIZE_IN_BYTES
from GLOBAL.AW$GLOBAL;


The result value is 107987880 Bytes (almost 108 MB). Now, let’s look at the bottom area in Figure 1 above; the total size of table AW$GLOBAL, as aggregated from segment, index and lob sizes, is 135528448 Bytes, or about 136 MB. The difference from 108 MB to 136 MB represents the additional overhead required to store the entire structure of table AW$GLOBAL on disk.

To summarize: the database table AW$GLOBAL in schema GLOBAL acts as a container which stores the content of the AW called GLOBAL (no relation to the schema with the same name). The content of the AW is stored as BLOB columns in table AW$GLOBAL.

The Blog is under construction, so please be patient and check daily for new content.

 Continued on next page…

---

<< Prev Article | Next Page >>