STARJOIN Examples :: SAS® Scalable Performance Data Server 5.3: User's Guide

Example 1: Valid SQL STARJOIN Candidate

The following code is an example of an SQL submission that the server can use as a star schema. The submission is a valid candidate for the following reasons:

A single central fact table, Sales, exists.
The dimension tables Time, Products, Location, and Supplier all join with the fact table Sales.
Each dimension table appears in only one join condition.
All dimension tables link to the fact table using equally joined operators.

proc sql;
  create table Sales_Report as
  select a.STORE_NUMBER,
         b.quarter
         c.month,
         d.state,
         e.SUPPLIER_ID

  sum(a.total_sold) as tot_qtr_mth_sales
  from   Sales a,
         Time b,
         Products c,
         Location d,
         Supplier e

  where a.sales_date   = b.sales_date
    and a.STORE_NUMBER = d.store_number
    and a.PRODUCT_CODE = c.product_code
    and a.SUPPLIER_ID  = d.supplier_id
    and b.quarter in (3, 4)
    and c.PRODUCT_CODE in (23, 100)

  group by b.quarter,
           a.STORE_NUMBER,
           b.month;
  quit;

Example 2: Invalid SQL STARJOIN Candidate

The following code is an example of an SQL submission that the server cannot use as a star schema because no single central fact table can be identified.

  proc sql;
  create table Sales_Report as
  select a.STORE_NUMBER,
         b.quarter
         c.month,
         d.state,
         e.SUPPLIER_ID

  sum(a.total_sold) as tot_qtr_mth_sales
  from   Sales a,
         Time b,
         Products c,
         Location d,
         Supplier e

  where a.sales_date   = b.sales_date
    and a.STORE_NUMBER = d.store_number
    and a.PRODUCT_CODE = c.product_code
    and c.SUPPLIER_ID  = d.supplier_id
    and b.quarter in (3, 4)
    and c.PRODUCT_CODE in (23, 100)

  group by b.quarter,
           a.STORE_NUMBER,
           b.month;
  quit;

The server cannot use the SQL submission in this example as a star schema This code joins the dimension tables for Time, Products, and Location to the Sales table, but the table for Supplier is joined to the Sales table through the Products table. As a result, the topology does not define a single central fact table.

Example 3: STARJOIN Candidate with Created or Calculated Columns

The STARJOIN facility in the server supports calculated or created columns. The following code is an example of an SQL submission that creates columns. This code still uses STARJOIN optimization if the central fact table and the dimension tables contain indexes on the join columns for the STARJOIN.

  proc sql;
  create table &Regional_Report as
  select case d.state
    when 1 then 'NC'
    when 2 then 'SC'
    when 3 then 'GA'
    when 4 then 'VA'
    else '  '

    end as state_abv,
    b.quarter,
    sum (a.tot_amt) as total_amt

  from wk_str_upd_t a,
       week_t b,
       location_t d,

  where a.we_dt       = b.we_dt
    and a.chn_str_nbr = d.chn_str_nbr
    and b.quarter     = 2

  group by d.state,
           b.quarter
    having d.state in (1,2,3,4);
  quit;

The code creates a column called State_Abv. The server STARJOIN facility supports created columns if the appropriate indexes on the join columns exist in the fact table and dimension tables.