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 product marketing campaign. For this cube, you establish measures and summaries of product statistics, geographic location of potential customers, and revenue.
  1. 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"
       ;
  2. 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.
    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)
       ;
  3. 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
       ;
  4. 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 gender as a member property.
    You use the PROPERTY statement here.
    property gender
       column=customer_gender
       hierarchy=customer
       level=customer_id
       ;
  5. 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 aggregations in addition to the NWAY. The NWAY aggregation is the only aggregation that PROC OLAP creates by default.
    You use the AGGREGATION statement here.
    aggregation product_group product_line
       product_category country year
       name='ProductYear'
       ;
      
  6. 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.
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.