Maintaining OLAP Groups and OLAP Summary Data Stores

Example: Using DATE/TIME Stored Expression to Split Date Values

For an OLAP summary data store, you might need to store parts of date values from an input column. For example, suppose an input column named drop_dat contains SAS date values that correspond to a value 05/05/95. For the data store, you could add columns to represent different parts of that value (for example, yearonly and monthonly) and split the value among the two output columns. To produce the appropriate data:

Add the columns yearonly and monthonly to the OLAP data store.
Assign the added columns as class columns.
For each added column, in the Mapping process, define a derived mapping to return a value using a specified format. The SAS/Warehouse Administrator Expression Builder window provides stored expressions from the DATE/TIME standard component so that you can easily define the derived mappings.

Add Columns to OLAP Summary Data Store

You add columns from the Columns tab in the object's properties window, which for this example is the OLAP Table Properties window. This example assumes that other columns have been imported from the input source and only illustrates adding columns to the data store.

Columns Tab

specifies the columns to be included in the OLAP data store. To add a column that does not exist in the input data, define the column's attributes (name, type, length, description, format, and informat), and then click Add. The result of adding columns to store the date values is as follows:

[untitled graphic]

Assign OLAP Summary Roles to the Columns

On the Columns tab, click OLAP to open the OLAP Column Roles window from which you assign OLAP summary roles. This example assumes that the summary roles for the imported columns have been assigned.

For the added columns, assign yearonly and monthonly as class columns.

[untitled graphic]

Define Mapping Process in Process Editor Job

Mappings for the imported columns can be defined simply as one-to-one mappings. However, derived mappings must be defined for the columns yearonly and monthonly. You can easily do this by using stored expressions available in the Expression Builder window from the DATE/TIME standard component.

Navigate to the Column Mapping tab on the Mapping Process Properties window as follows:

[untitled graphic]

To define derived mappings:

Select yearonly, and then click Derive Mapping, which opens the Expression Builder window.
In the Expression Builder window, select the Standard Expressions component, and then DATE/TIME to display a list of available stored expressions.
Select the stored expression Date to 4 digit year. The resulting expression uses the PUT and YEAR functions.
You must replace the argument %date% with the input column name, which is drop_dat.
Click OK on the Expression Builder window. The expression is assigned to the column, and you are returned to the Column Mapping tab in the Mapping Process Properties window. Note that SAS/Warehouse Administrator interprets the expression as:
```
yearonly=put(year(drop_dat),4.)
```
Repeat the steps for the monthonly column and select the stored expression Date to Month name, which results in the following expression:

The following Column Mapping tab in the Mapping Process Properties window displays the two columns with derived mapping defined:

[untitled graphic]

Once the Job is executed and the table loaded, here is the result:

[untitled graphic]

Top of Page