Cube Building and Modifying Examples

PROC OLAP Example for a Detail Table

In addition to building a cube in SAS OLAP Cube Studio, you can build an OLAP cube with PROC OLAP code and execute the code in a SAS session. Running PROC OLAP registers your cube and its sources in a metadata repository. It also creates the files that make up the cube. These are the possible input types for an OLAP cube that is built from a detail table:

a data table (specified in the PROC OLAP statement DATA= option)
dimension tables (specified in the DIMENSION statement DIMTBL= option)
presummarized tables (specified in the AGGREGATION statement TABLE= option)

In this example, you use data from a recent product marketing campaign. For this cube, you establish measures and summaries of various aspects of the data, such as product statistics, geographic location of potential customers, and revenue summaries.

Define the metadata profile and general information. You use the PROC OLAP and METASVR statements here. The detail table is specified in the PROC OLAP statement DATA= option. The CUBE= option is used to specify the metadata folder location of a cube and the name of the cube. The folder must already exist in the metadata. The PATH= option specifies the physical or logical path to the location of a new cube. Within the specified path, the cube is stored in a directory that uses the name of the cube in uppercase letters.

The METASRV statement is used to establish the metadata connection. It identifies the metadata repository in which existing cube metadata information exists or in which metadata about a new cube should be stored. The statement is also used to provide a user's identification and password for the identified repository. Also, the DRILLTHROUGH_TABLE= option is used here to indicate the drill-through table. Drill-through tables are optional and can be used by client applications to provide a view from processed data into the underlying data source.
```
proc olap data=olapsio.detail
   cube=/cubefolders/mycubefolder/Campaign1'
   path="c:\cubes"
   ; 
metasvr host=localhost
   port=9999
   protocol=bridge
   userid=userid
   pw=pw
   repository=Foundation
   olap_schema="OLAP Schema"
   ;
```

Define dimensions, levels, and hierarchies. Now that your basic metadata server and cube information has been entered, you can define the different dimensions and their respective levels and hierarchies. You use the DIMENSION, HIERARCHY, and LEVEL statements here.

Note: For time-specific levels in a dimension, the LEVEL statement is required. Also, there can be only one time-specific dimension. [cautionend]

dimension products
   hierarchies=(products)
   caption="Products"    
   sort_order=ascunformatted
   ; 
hierarchy products
   levels=(product_category product_group product_id 
   product_line product_name)
   ; 
dimension date
   hierarchies=(date)
   caption="Date"
   type=time
   ; 
hierarchy date
   levels=(year quarter month)
   ; 
level year
   type=year
   ; 
level quarter
   type=quarters
   ; 
level month
   type=months
   ;
dimension geography
   hierarchies=(geography)
   caption="Geography"
   ; 
hierarchy geography
   levels=(Country Region City)
   ;
dimension customer
   hierarchies=(customer)
   caption="Customer"
   ; 
hierarchy customer
   levels=(customer_age customer_group customer_type)
   ; 
dimension orders
   hierarchies=(orders)
   caption="Orders"
   ; 
hierarchy orders
   levels=(order_date total_retail_price costprice_per_unit)
   ;

Define measures. You can now define the measures for the cube. A measure is an input column and a roll-up rule (statistic). Only certain measures are physically stored. Other measures are derived from the stored measures at run time. In this example, you want measures for the product CostPrice_per_unit average and a range.

You use the MEASURE statement here.
```
measure costprice_per_unitrange
   column=costprice_per_unit
   stat=range
   format=dollar10.2
   ;
measure costprice_per_unitavg
   column=costprice_per_unit  
   stat=avg
   format=10.2
   ;
```
Define member properties. You can now define the member properties for any needed cube members. A member property is an attribute of a dimension member. A member property is also an optional cube feature that is created in a dimension to provide users with additional information about members. For this example, you can define the customer genderas a member property.

You use the PROPERTY statement here.
```
property gender
   column=customer_gender
   hierarchy=customer
   level=customer_id
   ;
```
Define aggregations. You can now define the aggregations for the cube. Aggregations are summaries of detailed data that is stored with a cube or referred by a cube. Their existence can reduce cube query time. If all aggregations are to be generated at the time of cube creation (MOLAP cube), then you can select specific aggregations that must be created in addition to the NWAY, which is the only aggregation that PROC OLAP makes by default.

You use the AGGREGATION statement here.
```
aggregation product_group product_line
   product_category country year
   name='ProductYear'
   ;
  
```

Build the cube. You can now build the cube. Execute the PROC OLAP statement within the SAS System or in batch-mode. Here is the complete PROC OLAP code.

proc olap data=olapsio.detail
   cube=/cubefolders/mycubefolder/Campaign1'
   path="c:\cubes"
   ; 
metasvr host=localhost
   port=9999
   protocol=bridge
   userid=userid
   pw=pw
   repository=Foundation
   olap_schema="OLAP Schema"
   ;
dimension products
   hierarchies=(products)
   caption="Products"    
   sort_order=ascunformatted
   ; 
hierarchy products
   levels=(product_category product_group product_id 
   product_line product_name)
   ; 
dimension date
   hierarchies=(date)
   caption="Date"
   type=time
   ; 
hierarchy date
   levels=(year quarter month)
   ; 
level year
   type=year
   ; 
level quarter
   type=quarters
   ; 
level month
   type=months
   ;
dimension geography
   hierarchies=(geography)
   caption="Geography"
   ; 
hierarchy geography
   levels=(Country Region City)
   ;
dimension customer
   hierarchies=(customer)
   caption="Customer"
   ; 
hierarchy customer
   levels=(customer_age customer_group customer_type)
   ; 
dimension orders
   hierarchies=(orders)
   caption="Orders"
   ; 
hierarchy orders
   levels=(order_date total_retail_price costprice_per_unit)
   ;
measure costprice_per_unitrange
   column=costprice_per_unit
   stat=range
   format=dollar10.2
   ;
measure costprice_per_unitavg
   column=costprice_per_unit  
   stat=avg
   format=10.2
   ;
property gender
   column=customer_gender
   hierarchy=customer
   level=customer_id
   ;
aggregation product_group product_line
   product_category country year
   name='ProductYear'
   ;
run;

Note: Any libraries must be specified before you run the PROC OLAP code. [cautionend]

Note: When a SAS OLAP cube is created, a directory for that cube is also created. This directory is assigned the same name as the cube, but in uppercase letters. For example, when you save a cube in C:\olapcubes and name the cube Campaigns, the cube is saved in the directory C:\olapcubes\CAMPAIGNS. [cautionend]

Top of Page