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.
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.
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.
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,$
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, $
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.
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',$