IMSTAT Procedure (Data and Server Management)

Example 5: Creating a Star Schema

Details

The following example demonstrates using the SCHEMA statement to join dimension tables with a fact table.

Program

libname example sasiola host="grid001.example.com" port=10010 tag='hps';

proc imstat;
   table example.mailorder; 1
   schema catalog  (catCode=CatCode)
          products (pcode  =pcode  )
          customers(custnum=custnum); 2
run;

   table example.&_templast_ 3
run;
   columninfo;
quit;

Program Description

  1. Table Example.MailOrder is set as the active table. This table is the fact table for the star schema.
  2. The SCHEMA statement joins the tables Catalog, Products, and Customers to the active table, MailOrder. The columns to use as keys for joining each table are enclosed in parenthesis.
  3. The result of the SCHEMA statement is a temporary table or view. Use the &_TEMPLAST_ macro variable to refer to the star schema. If you want to persist the star schema, use the PROMOTE statement.

Output

The following output shows the temporary table name and how the dimension table names are used as prefixes for the column names.
Viewing column information for a table that uses the SCHEMA statement