Defining a Dimension: WITHIN

How to:

The OLAP model organizes data structures by predefining dimensions in the Master File, using the field name attribute WITHIN. A dimension is a group or list of related fields called elements.

The WITHIN attribute enables drill up and drill down functionality on hierarchical dimensions. You can manipulate a report by selecting an OLAP-enabled field and drilling down to view other levels of a dimension hierarchy.

For example, a hierarchy of sales regions can be defined in the Master File as the GEOGRAPHY dimension and can include the following fields (elements): Region, State, and City in descending order. Region, the highest element in the hierarchy, would contain a list of all of the Regions within the GEOGRAPHY dimension. State, the second highest element in the hierarchy, would contain a list of all available States within Region, and so on. Dimensions can be defined in the Master File for any supported data source.

The combination, or matrix, of two or more dimensional hierarchies in an OLAP-enabled data source is called multi-dimensional. For example, although products are sold within states they need not be grouped in the same dimension as states. Instead, the elements Product Category and Product Name likely would be grouped in a dimension called PRODUCT. State would be a member of the GEOGRAPHY dimension that also can include Region and City. These dimensions are combined in a matrix so that the intersections of their criteria provide specific values, for example, sales of coffee in the Northeast region.

You can specify a list of acceptable values for each dimension element (field) using the ACCEPT attribute. This is done using either a hard coded list in the Master File or a lookup file. For more information on the ACCEPT attribute, see Validating Data: ACCEPT.

Syntax: How to Define a Dimension 

WITHIN='*dimensionname'
WITHIN=field

where:

'*dimensionname'

Is the name of the dimension. The dimension is defined in the field declaration for the field that is at the top of the hierarchy. The name must be preceded by an asterisk and enclosed within single quotation marks. The name must start with a letter and can consist of any combination of letters, digits, underscores, or periods. Avoid using special characters and embedded blanks.

field

Is used to define the hierarchical relationship among additional elements to be included in a given dimension. After the dimension name is defined at the top of the hierarchy, each element (field) uses the WITHIN attribute to link to the field directly above it in the hierarchy. The WITHIN attribute can refer to a field either by its field name or its alias. Note that a given field may participate in only one dimension, and two fields cannot reference the same higher level field.

Example: Defining a Dimension

The following example shows how to define the PRODUCT dimension in the OSALES Master File. 

PRODUCT Dimension
           ===> Product Category
               ===> Product Name
FILENAME=OSALES,  SUFFIX=FOC
  SEGNAME=SALES01,  SEGTYPE=S1
     FIELD=PRODCAT,    ALIAS=PCAT,     FORMAT=A11,
       WITHIN='*PRODUCT',$
     FIELD=PRODNAME,   ALIAS=PNAME,    FORMAT=A16,
       WITHIN=PRODCAT,$

Example: Defining Multiple Dimensions

The following annotated example shows how to define the dimensions, PRODUCT, GEOGRAPHY, and TIME in the OSALES Master File. 

PRODUCT Dimension
           ===> Product Category
               ===> Product Name
GEOGRAPHY Dimension
           ===> Region
               ===> State
                   ===> City 
                         ===> Store Name (from the OSTORES Master File)
TIME Dimension
           ===> Year
               ===> Quarter
                   ===> Month
                       ===> Date

OSALES Master File

   FILENAME=OSALES, SUFFIX=FOC
    SEGNAME=SALES01, SEGTYPE=S1
    FIELD=SEQ_NO,    ALIAS=SEQ,   FORMAT=I5,   TITLE='Sequence#',
     DESC='Sequence number in database',$
    FIELD=PRODCAT,   ALIAS=PCAT,  FORMAT=A11,INDEX=I, TITLE='Category',
     DESC='Product category',
     ACCEPT='Coffee' OR 'Food' OR 'Gifts',
1.      WITHIN='*PRODUCT',$
    FIELD=PRODCODE, ALIAS=PCODE, FORMAT=A4, INDEX=I, TITLE='Product ID',
     DESC='Product Identification code (for sale)',$
    FIELD=PRODNAME, ALIAS=PNAME, FORMAT=A16,         TITLE='Product',
     DESC='Product name',
2.   ACCEPT='Espresso' OR 'Latte' OR 'Cappuccino' OR 'Scone' OR
            'Biscotti' OR 'Croissant' OR 'Mug' OR 'Thermos' OR
            'Coffee Grinder' OR 'Coffee Pot',
        WITHIN=PRODCAT,$
    FIELD=REGION,   ALIAS=REG,   FORMAT=A11, INDEX=I,TITLE='Region',
     DESC='Region code',
     ACCEPT='Midwest' OR 'Northeast' OR 'Southwest' OR 'West',
3.      WITHIN='*GEOGRAPHY',$
    FIELD=STATE,    ALIAS=ST,    FORMAT=A2, INDEX=I,TITLE='State',
     DESC='State',
4.   ACCEPT=(OSTATE),
        WITHIN=REGION,$
    FIELD=CITY,      ALIAS=CTY,    FORMAT=A20,        TITLE='City',
     DESC='City',
        WITHIN=STATE,$
    FIELD=STORE_CODE, ALIAS=STCD,  FORMAT=A5, INDEX=I, TITLE='Store ID',
     DESC='Store identification code (for sale)',$
    FIELD=DATE,       ALIAS=DT,    FORMAT=I8YYMD,      TITLE='Date',
     DESC='Date of sales report',
        WITHIN=MO,$
5.  FIELD=UNITS,     ALIAS=UN,    FORMAT=I8,     TITLE='Unit Sales',
     DESC='Number of units sold',$
    FIELD=DOLLARS,    ALIAS=DOL,   FORMAT=I8,     TITLE='Dollar Sales',
     DESC='Total dollar amount of reported sales',$
    FIELD=BUDUNITS,   ALIAS=BUNIITS, FORMAT=I8,   TITLE='Budget Units',
     DESC='Number of units budgeted',$
    FIELD=BUDDOLLARS, ALIAS=BDOLLARS,FORMAT=I8,   TITLE='Budget Dollars',
     DESC='Total sales quota in dollars',$
6. DEFINE ADATE/A8 = EDIT(DATE);$
   DEFINE YR/I4 = EDIT (EDIT(ADATE,'9999$$$$')); WITHIN='*TIME',$
   DEFINE MO/I2 = EDIT (EDIT(ADATE,'$$$$99$$')); WITHIN=QTR,$
   DEFINE QTR/I1 = IF MO GE 1 AND MO LE 3
     THEN 1 ELSE IF MO GE 4 AND MO LE 6
     THEN 2 ELSE IF MO GE 7 AND MO LE 9
     THEN 3 ELSE IF MO GE 10 AND MO LE 12 
     THEN 4 ELSE 0;
     WITHIN=YR,$
7. SEGNAME = STORES01, SEGTYPE = KU, PARENT = SALES01,
     CRFILE = OSTORES, CRKEY = STORE_CODE, $
  1. Declares the PRODUCT dimension. The name must be preceded by an asterisk and enclosed within single quotation marks.
  2. A list of acceptable values may be defined for each dimension element (field), if you wish. You specify the ACCEPT attribute using either a hard coded list in the Master File or an external flat file. The list of acceptable values is presented to you as possible selection criteria values. In this example, the value for the product name must be Espresso, Latte, Cappuccino, Scone, Biscotti, Croissant, Mug, Thermos, Coffee Grinder, or Coffee Pot. For more information on the ACCEPT attribute, see Specifying Acceptable Values for a Dimension.
  3. Declares the GEOGRAPHY dimension and defines the dimension hierarchy for GEOGRAPHY: Region within GEOGRAPHY (top of the hierarchy), State within Region, and City within State.
  4. In this example, the ACCEPT attribute is using an external flat file (OSTATE) to determine all of the possible data values for state (field ST).
  5. The four fields, UNITS, DOLLARS, BUDUNITS, and BUDDOLLARS, are examples of measure fields. A measure field is used for analysis that typically defines how much or how many. For example, Units, Dollars, Budget Units, and Budget Dollars are measures that specify how many units were sold, the total dollar amount of reported sales, how many units were budgeted, and total sales quota in dollars, respectively.
  6. Shows the following:

    Virtual fields may be included at any level in a dimension. In this example, the fields YR, MO, and QTR are defined within the TIME dimension. The WITHIN attribute for a virtual field must be placed on the same line as the semicolon that ends the expression.

    How to define the dimension hierarchy for the TIME dimension: Year within Time, Quarter within Year, Month within Quarter, Date within Month.

    Fields in a hierarchy can occur in any order in the Master File.

  7. Dimensions may span a dynamic or static JOIN structure using a qualified name. In this example, the OSALES data source is statically cross-referenced to the OSTORES data source using the common field STORE_CODE. Through this linkage, the OLAP application can retrieve the value for store name from the OSTORES data source. Note that STORE_NAME in the OSTORES Master File is an element of the GEOGRAPHY dimension that was defined in the OSALES Master File. The following is the OSTORES Master File:
    FILENAME=OSTORES, SUFFIX=FOC
SEGNAME=STORES01, SEGTYPE=S1
 FIELD=STORE_CODE, ALIAS=STCD,  FORMAT=A5, INDEX=I,TITLE='Store ID',
   DESC='Franchisee ID Code',$
 FIELD=STORE_NAME, ALIAS=SNAME, FORMAT=A23,  TITLE='Store Name',
   DESC='Store Name', WITHIN=SALES01.CITY,$ 
 FIELD=ADDRESS1,   ALIAS=ADDR1, FORMAT=A19,  TITLE='Contact',
   DESC='Franchisee Owner',$
 FIELD=ADDRESS2,   ALIAS=ADDR2, FORMAT=A31,  TITLE='Address',
   DESC='Street Address',$
 FIELD=CITY,       ALIAS=CTY,   FORMAT=A22,  TITLE='City',
   DESC='City',$
 FIELD=STATE,      ALIAS=ST,    FORMAT=A2,   TITLE='State',
   DESC='State',$
 FIELD=ZIP,        ALIAS=ZIP,   FORMAT=A6,   TITLE='Zip Code',
   DESC='Postal Code',$
  • Virtual fields may be included at any level in a dimension. In this example, the fields YR, MO, and QTR are defined within the TIME dimension. The WITHIN attribute for a virtual field must be placed on the same line as the semicolon that ends the expression.
  • How to define the dimension hierarchy for the TIME dimension: Year within Time, Quarter within Year, Month within Quarter, Date within Month.
  • Fields in a hierarchy can occur in any order in the Master File.