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

Details

Example

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 in order to create a star schema.

To improve the performance of the application that processes the joined tables, you can specify the ROLE= data set option. For example, you can specify ROLE=FACT to designate the specific fact table, or you can specify ROLE=DIMENSION to designate each dimension table, meaning that the table not designated is the fact 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 the following example, the ROLE= data set option improves the performance for 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;

ROLE= Data Set Option

Syntax

Syntax Description

Details

Example: Designating the Fact Table

See Also