The dimension CUST_REG_DIM and the hierarchy CUST_REG_HIER inside the Analytic Workspace are accessible to relational SQL queries via views which are created by the AWM tool. Figures 11 and 12 below show relevant details about these views:
Figure 11
The view CUST_REG_DIM_VIEW shown above returns one row for each dimension member.
Figure 12
The view CUST_REG_DIM_CUST_REG_HIE_VIEW shown above returns data from dimension CUST_REG_DIM according to the structure of hierarchy CUST_REG_HIER.
The views CUST_REG_DIM_VIEW and CUST_REG_DIM_CUST_REG_HIE_VIEW have been created by the AWM tool inside the schema QRW_USER, where the Analytic Workspace AW_SALES_01 also is located.
Now, let’s have a look at the characteristics of the other dimension defined inside this analytic workspace.
Figures 13 through 20 below show the relevant properties for dimension TIME_DIM inside the AW_SALES_01 AW:
Figure 13
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 14 below:
Figure 14
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 15 below:
Figure 15
The dimension TIME_DIM contains one hierarchy named TIME_HIER which includes levels YYYY, QTR_YYYY and MMYYYY, as shown in Figure 16 below:
Figure 16
Please note that the AW dimension TIME_DIM and the AW hierarchy TIME_HIER map to the levels from table TIME_HIERARCHY_TK which are above the grain level of that dimension table.
The hierarchy structure within dimension TIME_DIM is defined to be consistent with a fixed number of levels, as shown in Figure 17 below:
Figure 17
The underlying structure of the dimension TIME_DIM is shown in Figure 18 below, with mappings to the columns of the dimension tables which contain the dimension data.
Figure 18
The dimension TIME_DIM and the hierarchy TIME_HIER inside the Analytic Workspace are accessible to relational SQL queries via views which are created by the AWM tool. Figures 19 and 20 below show relevant details about these views:
Figure 19
The view TIME_DIM_VIEW shown above returns one row for each dimension member.
Figure 20
The view TIME_DIM_TIME_HIER_VIEW shown above returns data from dimension TIME_DIM_VIEW according to the structure of hierarchy TIME_HIER.
The views TIME_DIM_VIEW and TIME_DIM_TIME_HIER_VIEW have been created by the AWM tool inside the schema QRW_USER, where the Analytic Workspace AW_SALES_01 also is located.
For the sake of staying on topic, I won’t go here into the details of how to create the two dimensions and the cube. The mappings and other relevant details for the dimensions, hierarchies and the cube have been provided in the screen captures throughout this article.
The cube mapping details are shown in Figure 1 on the previous page.
However, I will go briefly over how to populate the dimensions with data, and over how to build and populate the cube.