Defining Concat Dimensions and Variables That Use Them

A concat dimension combines two or more base dimensions into a single dimension. You can use a concat dimension instead of a hierarchical simple dimension as another means of organizing and structuring parent-child data within a dimension. You use self-relations to organize the values of the concat dimension into groups by the levels of the hierarchy.

In a relational dimension table, suppose you have one column for districts with city names as its values, and another column for regions. You can define a district dimension and a region dimension in your analytic workspace and load into them the values of the relational columns. Those dimensions have the following values.

DISTRICT
----------
BOSTON
SAN FRANCISCO
SEATTLE

REGION
-------
EAST
WEST

You can define a concat dimension named reg.dist.ccdim based on those simple flat dimensions. The concat dimension contains the values of both dimensions.

REG.DIST.CCDIM
-------------------------
<REGION: EAST>
<REGION: WEST>
<DISTRICT: BOSTON>
<DISTRICT: SAN FRANCISCO>
<DISTRICT: SEATTLE>

You can then define a self-relation that groups the values of the concat dimension into hierarchical levels. As with a hierarchical simple dimension, you can use a concat dimension to define a variable that contains different levels of aggregation.

Example: Variable with a Concat Dimension

You can define a variable dimensioned by the reg.dist.ccdim concat dimension and the division dimension from "Example: Variable with a Hierarchical Dimension" as follows.

DEFINE costs VARIABLE DECIMAL <reg.dist.ccdim division>

You can define a self-relation for the reg.dist.ccdim concat dimension that identifies the parent-to-child relationships of the district-region hierarchy as follows.

DEFINE rdccdim.rdccdim RELATION reg.dist.ccdim <reg.dist.ccdim>
limit district to 'BOSTON'
rdccdim.rdccdim(REG.DIST.CCDIM district) = reg.dist.ccdim(REGION 'EAST')
limit district to 'DENVER' 'SEATTLE'
rdccdim.rdccdim(REG.DIST.CCDIM district) = reg.dist.ccdim(REGION 'WEST')

If you enter data at the lowest level (the district dimension level), then the costs variable has the values shown below.

          ------------------------COSTS------------------------------
          --------------------REG.DIST.CCDIM-------------------------
           <REGION:   <REGION:  <DISTRICT: <DISTRICT:      <DISTRICT:
DIVISION    EAST>      WEST>     BOSTON>    SAN FRANCISCO>  SEATTLE>
--------- ---------- ---------- ---------- --------------- ----------
DIVA         NA        NA         27,600.00  10,000.00       40,000.00
DIVB         NA        NA         30,000.00  12,000.00       50,000.00

You can aggregate the data in the costs variable by creating an aggregation map and then using the AGGREGATE command. After you aggregate the data, the costs variable has values in all of its cells, including the cells for the totals for the EAST and WEST regions.

          ------------------------COSTS------------------------------
          --------------------REG.DIST.CCDIM-------------------------
                                                  
           <REGION:   <REGION:  <DISTRICT: <DISTRICT:      <DISTRICT:
DIVISION    EAST>      WEST>     BOSTON>    SAN FRANCISCO>  SEATTLE>
--------- ---------- ---------- ---------- --------------- ----------
DIVA       27,600.00  50,000.00  27,600.00  10,000.00       40,000.00
DIVB       30,000.00  62,000.00  30,000.00  12,000.00       50,000.00