Designing Your Cube Type SQL

In order to support the base cube viewer, the SQL used in your cube type data explorer zone needs to be constructed with two main components. The first component creates a view of the data and its dimensions in a form that supports a tree-like structure and allows for the dynamic use of filters. The second component queries the filtered data set to provide the values to be displayed for each dimension.

The first component should contain the following queries:

• A sub-query constructed using a ‘with’ clause that builds a tree-like view of the cube’s dimensions and optionally other columns used in the main SQL. This portion of the SQL must be enclosed within ‘/*>tree*/ ’ and ‘/*<tree*/ ‘ hints and use the table alias C_​TREE. The combination of the dimension columns should form a unique ‘key’ in the C_​TREE data. Both the ’with’ clause and the ‘tree’ portion within it may contain other supporting sub-queries.

• A sub-query which is the placeholder for filtered queries on the ‘tree’ data. It must use the table alias C_​FILTER and take the form ‘C_​FILTER as (select * from C_​TREE where 1=1 and ROWNUM <= n)’. The value of ‘n’ should be set to limit the rows returned to a number that is manageable within the cube viewer.

The second component is the main query that retrieves the measures for the cube view dimensions. This query references C_​FILTER as the subset of the C_​TREE and joins with other tables if required to retrieve the numeric values. The first date or date/time result column of the main query (as specified in the zone configuration) designates a time-series column, which forms the time-series ‘key’ with the dimensions columns.