Sample Olap code

Sample Olap code used to create an Olap cube from SAS base without using Olap Studio.

OPTIONS VALIDVARNAME=ANY;

LIBNAME sashelp BASE "C:\Program Files\SASHome\SASFoundation\9.3\core\sashelp";

/*A metadata folder path that you have WRITE METADATA access to:*/
%let path = /User Folders/Angela Hall/My Folder/Shoes;
/*A physical path on the compute server where the physical content of the cube will be placed:*/
%let fileloc = c:\temp\shoes;

/*from BASE SAS: Server name, metadata port number, user name & password needed to estabish a connection*/
%let SERVER = ;
%let PORT = 8561;
%let USER = ;
%let PASSWORD = ;


option metaserver="&SERVER"
      metaport=&PORT
      metarepository="Foundation"
      metauser="&USER"  
      metapass="&PASSWORD";

PROC OLAP
  CUBE                   = "&path"
  MAX_RETRIES            = 3
  MAX_RETRY_WAIT         = 60
  MIN_RETRY_WAIT         = 30
  DATA                   = sashelp.SHOES
  DRILLTHROUGH_TABLE     = sashelp.SHOES
  PATH                   = "&fileloc"
  DESCRIPTION            = 'Shoes'
;


  METASVR
     HOST        = "&SERVER"
     PORT        = &PORT
     OLAP_SCHEMA = "SASApp - OLAP Schema";

  DIMENSION Products
     CAPTION          = 'Products'
     SORT_ORDER       = ASCENDING
     HIERARCHIES      = (
        Products
        ) /* HIERARCHIES */;

     HIERARCHY Products
        ALL_MEMBER = 'All Products'
        CAPTION    = 'Products'
        LEVELS     = (
           Product
           ) /* LEVELS */
        DEFAULT;

     LEVEL Product
        CAPTION        =  'Product'
        SORT_ORDER     =  ASCENDING;

  DIMENSION Location
     CAPTION          = 'Location'
     SORT_ORDER       = ASCENDING
     HIERARCHIES      = (
        Location
        ) /* HIERARCHIES */;

     HIERARCHY Location
        ALL_MEMBER = 'All Location'
        CAPTION    = 'Location'
        LEVELS     = (
           Region Subsidiary
           ) /* LEVELS */
        DEFAULT;

     LEVEL Region
        CAPTION        =  'Region'
        SORT_ORDER     =  ASCENDING;

     LEVEL Subsidiary
        CAPTION        =  'Subsidiary'
        SORT_ORDER     =  ASCENDING;

  MEASURE InventorySUM
     STAT        = SUM
     COLUMN      = Inventory
     CAPTION     = 'Sum of Inventory'
     FORMAT      = DOLLAR12.
     DEFAULT;

  MEASURE InventoryAVG
     STAT        = AVG
     COLUMN      = Inventory
     CAPTION     = 'Average Inventory'
     FORMAT      = DOLLAR12.;

  MEASURE ReturnsSUM
     STAT        = SUM
     COLUMN      = Returns
     CAPTION     = 'Sum of Returns'
     FORMAT      = DOLLAR12.;

  MEASURE ReturnsAVG
     STAT        = AVG
     COLUMN      = Returns
     CAPTION     = 'Average Returns'
     FORMAT      = DOLLAR12.;

  MEASURE SalesSUM
     STAT        = SUM
     COLUMN      = Sales
     CAPTION     = 'Sum of Sales'
     FORMAT      = DOLLAR12.;

  MEASURE SalesAVG
     STAT        = AVG
     COLUMN      = Sales
     CAPTION     = 'Average Sales'
     FORMAT      = DOLLAR12.;

  MEASURE StoresSUM
     STAT        = SUM
     COLUMN      = Stores
     CAPTION     = 'Sum of Stores';

  MEASURE StoresAVG
     STAT        = AVG
     COLUMN      = Stores
     CAPTION     = 'Average Stores';

  AGGREGATION /* Default */
     /* levels */
     Product Region Subsidiary
     / /* options */
     NAME      = 'Default';

  RUN;