This article is a follow-up on my previous presentations located here and here. This time, we shall focus on the oracle optimizer ability to rewrite a SQL query to make use of an OLAP cube. Let’s consider the picture shown in Figure 1 below:
Figure 1
We see in Figure 1 a screen capture from an oracle tool, named Analytic Workspace Manager (AWM). We can use this tool to define dimensions, hierarchies and cubes, within an Oracle Analytic Workspace (AW). The dimensions and hierarchies defined inside an AW are different, and distinct, entities from the relational dimensions and hierarchies that are constructed inside a schema by using the create dimension syntax. In the previous two articles located here and here we used relational dimensions and hierarchies.
In this article, we will use the Oracle Graphical User Interface tool named AWM in order to define dimensions, hierarchies and an OLAP cube, so that the oracle optimizer will become aware of their existence, and so that certain SQL aggregation queries against a simple star schema will be rewritten to use the new aggregated data stored in the cube.
Let’s look at the simple star schema configuration with one fact table and two dimension tables as described in the previous articles, and shown below in Figure 2:
Figure 2
We shall define two dimensions within an OLAP Analytic Workspace, and within each dimension we shall define a hierarchy. Then, we shall create a cube and map it to the tables shown in Figure 2. After the dimensions, hierarchies and the cube have been defined and mapped inside the AW, data from tables in Figure 2 will be loaded into the dimensions, and then the cube shall be built for the purpose of enabling query rewrite.
In this case study, I have used the AW_SALES_01 Analytic Workspace inside the QRW_USER schema. We can see in Figures 3 and 4 below, a few of the relevant characteristics and entities which describe the AW_SALES_01 AW:
Figure 3
Figure 4
Inside the AW_SALES_01 AW, I have defined the following entities: two dimensions named TIME_DIM and CUST_REG_DIM, each of them having a hierarchy, and a cube named CB_SALES_2.
Figures 5 through 12 below show the relevant properties for dimension CUST_REG_DIM inside the AW_SALES_01 AW:
Figure 5
For this case study, we want to enable the materialized view refresh of the dimension, and we want to use trusted constraints, as shown in Figure 6 below:
Figure 6
We also want to use generated surrogate keys within the dimensions in the Analytic Workspace, and we need the constraints for dimension hierarchy data to be Star Consistent, as shown in Figure 7 below:
Figure 7
The dimension CUST_REG_DIM contains one hierarchy named CUST_REG_HIER which includes levels REGION and ZIP_CODE, as shown in Figure 8 below:
Figure 8
Please note that the AW dimension CUST_REG_DIM and the AW hierarchy CUST_REG_HIER map to the levels from table CUSTOMER_HIERARCHY_TK which are above the grain level of that dimension table.
The hierarchy structure within dimension CUST_REG_DIM is defined to be consistent with a fixed number of levels, as shown in Figure 9 below:
Figure 9
The underlying structure of the dimension CUST_REG_DIM is shown in Figure 10 below, with mappings to the columns of the dimension tables which contain the dimension data.
Figure 10