Managing Sparse Data and Optimizing the Workspace Cube

A composite is an object that is used to store sparse data compactly in a variable in an analytic workspace. A composite consists of a list of dimension-value combinations in which one value is taken from each of the dimensions on which the composite is based. Only the combinations for which data exists are included in the composite.

Composites are maintained automatically by the OLAP engine. With composites, you can keep your analytic workspace size to a minimum and promote good performance. For more information on composites, see the Oracle OLAP DML Reference. For information on managing sparsity and optimizing performance in your analytic workspaces, see the Oracle OLAP Application Developer's Guide

For example, you might have some products in your analytic cube that are not sold in all regions. The data cells for those combinations of PRODUCT and GEOGRAPHY would be empty. In this case, you might choose to define PRODUCT and GEOGRAPHY as a composite. The OLAP DML syntax for defining the dimensionality of the Costs measure in this cube could be as follows.

DEFINE prod_geog COMPOSITE <product geography>
DEFINE costs VARIABLE INTEGER <time channel prod_geog<product geography>>

To specify that a cube's data be loaded into an analytic workspace using this definition of the cube's dimensionality, you would define a composite specification for the cube. The composite specification would define the following expression.

<time channel prod_geog<product geography>>

Each member of a composite specification has a name, a type, and a position. Table 1-9 identifies this information for the preceding example.

Dimension order determines how the cube's data is stored and accessed in the analytic workspace. The first dimension in the dimension's definition is the fastest-varying and the last is the slowest-varying.

By default, REFRESH_AWCUBE defines a workspace cube's dimensionality with Time as the fastest varying dimension followed by a composite of all the other dimensions. The dimensions in the composite are ordered according to their size. The dimension with the most members is first and the dimension with the least members is last. For example, the default dimensionality of the ANALYTIC_CUBE in an analytic workspace would be as follows.

<time comp_name<geography, product, channel>>

You can override the default dimensionality by specifying a composite specification and including it in the cube load specification.

For information on ordering dimensions and specifying segment size for dimension storage, see the Oracle OLAP Application Developer's Guide.

The statements in Example 1-3 create a composite specification called comp1 for the ANALYTIC_CUBE.

exec dbms_awm.create_awcomp_spec 
           ('comp1', 'xademo', 'analytic_cube');
exec dbms_awm.add_awcomp_spec_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_time', 'dimension', 
           'xademo', 'time');
exec dbms_awm.add_awcomp_spec_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_channel', 'dimension', 
           'xademo', 'channel');
exec dbms_awm.add_awcomp_spec_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_prod_geog', 'composite');
exec dbms_awm.add_awcomp_spec_comp_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_prod_geog',
           'comp1_product' ,'dimension', 'xademo', 'product');
exec dbms_awm.add_awcomp_spec_comp_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_prod_geog',
           'comp1_geography' ,'dimension', 'xademo', 'geography');
exec dbms_awm.add_awcubeload_spec_comp
           ('my_cube_load', 'xademo', 'analytic_cube', 'comp1');

exec dbms_awm.set_awcomp_spec_member_pos
          ('comp1', 'xademo', 'analytic_cube', 'comp1_channel', 1);