26041 - SAS® OLAP cube for use with MDX samples

Sample 26041: SAS® OLAP cube for use with MDX samples

Before submitting this code, you must create a metadata library. For instructions on creating metadata libraries, please see the SAS Management Console User's Guide available in the SAS OnlineDoc for your release. Additional documentation on the Data Library Manager can be found within the SAS Management Console help files. This library must be located in the same location as the LIBPATH macro in the code below. Once the library has been defined, modify the macro variables included at the beginning of the code and then submit to your SAS server.

/* Macro Variables for use in Samples code */
%let SERVER = server.company.com;    /* Metadata Server Name or IP Address */
%let PORT = 8561;                    /* Metadata Port Number */
%let PROTOCOL = Bridge;              /* Metadata connection protocol */
%let USER = sasdemo;                 /* User ID with ReadMetadata and WriteMetadata permissions */
%let PASSWORD = sasdemo1;            /* Password for the User Id */
%let REPOSITORY = Foundation;        /* Repository in which to build data and cube */
%let METALIB = sample library;       /* Metadata library name used in SAS Management Console */
%let LIBNAME = sample;               /* Libref which references permanent data location */
%let LIBPATH = c:\sasdata;           /* Directory in which to store data */
%let SCHEMA = SASMain - OLAP Schema; /* OLAP Schema in which to store cube */
%let CUBEPATH = .\Data;              /* Physical location for OLAP Cube files */
%let CUBE = SampleCube;              /* Cube Name */




/* Options for Metadata Server connections */

libname &libname "&libpath";
option metaserver="&SERVER"
       metaport=&PORT
       metaprotocol="&PROTOCOL"
       metarepository="&REPOSITORY"
       metauser="&USER" 
       metapass="&PASSWORD";

/* Create a data set with random missing values */
data &libname..sales (drop=val1 val2 val3);
  set sashelp.prdsale;	
  val1 = int(ranuni(1)*10);
  val2 = int(ranuni(2)*val1)+1;
  val3 = mod(val1,val2);
  if val3>2 and mod(val1,2)=0 then actual=.;
  if val3>2 and mod(val1,2)=1 then predict=.;
  fulldate = month;
run;

/* Generate the star schema data sets */
data time;
  set &libname..sales (keep=year quarter month fulldate);
run;

proc sort data=time nodupkey;
  by year quarter month;
run;

proc sort data=&libname..sales out=fact;
  by year quarter month;
run;

data &libname..time;
  set time;
  timekey = _n_;
run;

data fact (drop=year quarter month fulldate);
  merge fact &libname..time;
  by year quarter month;
run;

data products;
  set fact (keep=prodtype product);
run;

proc sort data=products nodupkey;
  by prodtype product;
run;

proc sort data=fact;
  by prodtype product;
run;

data &libname..products;
  set products;
  prodkey = _n_;
run;

data fact (drop=prodtype product);
  merge fact &libname..products;
  by prodtype product;
run;

data geo;
  set fact (keep=country region division);
run;

proc sort data= geo nodupkey;
  by country region division;
run;

proc sort data=fact;
  by country region division;
run;

data  &libname..geo;
  set geo;
  geokey = _n_;
run;

data &libname..fact (drop=country region division);
  merge fact &libname..geo;
  by country region division;
run;

/* Register tables using PROC METALIB */
proc metalib;
  omr (library="&metalib" metarepository="&repository");
  select (fact geo products time sales);
run;

/* Delete any cube that exists with the cube name */
PROC OLAP cube=&cube delete;
  METASVR repository="&repository" olap_schema="&schema";
RUN;

/* Create the OLAP cube */
PROC OLAP
  Fact=&libname..FACT
  DrillThrough_Table=&libname..sales
  cube=&cube
  Path="&cubepath"
  Description="Sales Cube for OLAP Samples"
  ;
 
  METASVR repository="&repository" olap_schema="&schema";

  DIMENSION Geography hierarchies=(Geography ) 
    CAPTION='Geography Dimension'
    SORT_ORDER=ASCENDING DIMTBL=&libname..GEO DIMKEY=geokey FACTKEY=geokey;

    HIERARCHY Geography ALL_MEMBER='All Geography' 
      levels=( COUNTRY REGION DIVISION ) 
      CAPTION='Geography Hierarchy'
      DEFAULT
      ;
 
      LEVEL COUNTRY
        CAPTION='Country' 
        SORT_ORDER=ASCENDING
        ;    

      LEVEL REGION
        CAPTION='Region'
        SORT_ORDER=ASCENDING
        ;  
  
      LEVEL DIVISION
        CAPTION='Division'
        SORT_ORDER=ASCENDING
        ; 

  DIMENSION Products hierarchies=(Products ) 
    CAPTION='Products Dimension'
    SORT_ORDER=ASCENDING DIMTBL=&libname..PRODUCTS DIMKEY=prodkey FACTKEY=prodkey;

    HIERARCHY Products ALL_MEMBER='All Products' 
      levels=( PRODTYPE PRODUCT ) 
      CAPTION='Products Hierarchy'
      DEFAULT
      ;

      LEVEL PRODTYPE 
        CAPTION='Product type' 
        SORT_ORDER=ASCENDING
        ;    

      LEVEL PRODUCT
        CAPTION='Product'
        SORT_ORDER=ASCENDING
        ;      

  DIMENSION Time hierarchies=(Time ) 
    CAPTION='Time Dimension'
    TYPE=TIME SORT_ORDER=ASCENDING DIMTBL=&libname..TIME DIMKEY=timekey FACTKEY=timekey;

    HIERARCHY Time ALL_MEMBER='All Time' 
      levels=( YEAR QUARTER MONTH ) 
      CAPTION='Time Hierarchy'
      DEFAULT
      ;

      LEVEL YEAR TYPE=YEAR 
        CAPTION='Year' 
        SORT_ORDER=ASCENDING
        ;

      LEVEL QUARTER TYPE=QUARTERS 
        CAPTION='Quarter' 
        SORT_ORDER=ASCENDING
        ;

      LEVEL MONTH TYPE=MONTHS 
        CAPTION='Month' 
        SORT_ORDER=ASCENDING
        ;

  MEASURE ACTUALSUM
          STAT=SUM
          COLUMN=ACTUAL
          CAPTION='Sum of ACTUAL'
          FORMAT=DOLLAR12.2
          DEFAULT
          ;

  MEASURE PREDICTSUM
          STAT=SUM
          COLUMN=PREDICT
          CAPTION='Sum of PREDICT'
          FORMAT=DOLLAR12.2
          ;

  MEASURE PREDICTAVG
          STAT=AVG
          COLUMN=PREDICT
          CAPTION='Average PREDICT'
          FORMAT=DOLLAR12.2
          ;

  MEASURE ACTUALAVG
          STAT=AVG
          COLUMN=ACTUAL
          CAPTION='Average ACTUAL'
          FORMAT=DOLLAR12.2
          ;

  AGGREGATION COUNTRY
              REGION
              DIVISION
              PRODTYPE
              PRODUCT
              YEAR
              QUARTER
              MONTH
              / NAME='Default'
              ;

  AGGREGATION COUNTRY
              PRODTYPE
              YEAR
              / NAME='Top Level'
              ;

  PROPERTY Full_Date
           LEVEL=MONTH
           HIERARCHY= (Time )
           COLUMN=FullDate
           CAPTION='Full_Date'
           ;
RUN;

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Type:	Sample
Topic:	Third Party ==> OLAP (Online Analytical Processing)

Date Modified:	2007-10-15 07:08:22
Date Created:	2006-02-27 12:27:54

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	SAS OLAP Server	z/OS	9.1 TS1M0	n/a
		64-bit Enabled AIX	9.1 TS1M0	n/a
		64-bit Enabled Solaris	9.1 TS1M0	n/a
		64-bit Enabled HP-UX	9.1 TS1M0	n/a

Support

Sample 26041: SAS® OLAP cube for use with MDX samples

Operating System and Release Information