ROLE= Data Set Option

Identifies the fact table for a star schema join.

Valid in: PROC SQL
Category: Data Set Control
Restriction: Use with input data sets only.
Syntax
Syntax Description
Details
Example
See Also

Syntax

ROLE=FACT | DIMENSION | DIM

Syntax Description

FACT

identifies the SAS data set as the fact table for a star schema.

DIMENSION | DIM

identifies the SAS data set as a dimension table for a star schema.

Details

A star schema is an arrangement of several tables in which a large fact table is joined to several dimension tables. For example, you can join SAS data sets by using SQL procedure syntax to create a star schema.
To improve the performance of the application that processes the joined tables, specify the ROLE= data set option. For example, specify ROLE=FACT to designate the specific fact table. You can also specify ROLE=DIMENSION to designate each dimension table.
Because the role a table plays can change between queries, the ROLE= specification is in effect for the current step only and is not stored with the data set.

Example: Designating the Fact Table

In this example, the ROLE= data set option improves the performance of PROC SQL. ORDERS is the fact table, and PRODUCT, PERIOD, and CUSTOMER are dimension tables. 
proc sql;
   select orders.Order_Total
   from orders (role=fact), product, period, customer
   where orders.Product_ID = product.Product_ID
      and orders.Period_ID = period.Period_ID
      and orders.Customer_ID = customer.Customer_ID
      and product.Product_Name = "camera"
      and period.Period_Name = "1997"
      and customer.Customer_Name = "Walmart";
quit;

See Also

SQL Procedure in SAS SQL Procedure User’s Guide
Copyright © SAS Institute Inc. All Rights Reserved.