Star joins are useful when you query information from dimensional models that are
constructed of two or more
dimension tables that surround a centralized
fact table, which is known as a
star schema. SAS SPD Server star joins are queries that validate, optimize, and execute SQL queries
in the SAS SPD Server database for performance. If the star join is not used, the
SQL is processed in the SAS SPD Server by using pair-wise joins, which require one
step for each table to complete the join. When the SAS SPD Server options are set,
the star join is enabled.
You must meet the following
requirements in order to enable a SAS SPD Server star join:
-
All dimension tables must surround a single fact table.
-
Dimension-to-fact table joins must be equal joins, and there should be one join per
dimension table.
-
You must have two or more dimension tables in the join condition.
-
The fact table must have at least one subsetting condition placed on it.
-
All subsetting and join conditions
must be specified in the WHERE clause.
-
Star join optimization must be
enabled through the setting of options on the SAS SPD Server library.
In order to enable star
join optimization, code that runs on the generated Pass SAS SPD Server
system library must have the following options added to the library:
Here is a commented
example of a WHERE clause that enables a SAS SPD Server star join
optimization:
where
/* dimension1 equi-joined on the fact */
hh_&statesimple.geosur = hh_dim_geo_&statesimple.geosur
/* dimension2 equi-joined on the fact */
and hh_&statesimple.utilsur = hh_dim_utility_&statesimple.utilsur
/* dimension3 equi-joined on the fact */
and hh_dim_family_&statesimple.famsur =
hh_dim_family_&statesimple.famsur
/* subsetting condition on the fact */
and hh_dim_family_&statesimple.PERSONS = 1
;
Note: The SAS SPD Server requires
all subsetting to be implemented on the Where tab
in the SQL Join transformation. For more information about SAS SPD
Server support for star joins, see the SAS Scalable Performance
Data Server: User's Guide. When the code is properly
configured, the following output is generated in the log: SPDS_NOTE:
STARJOIN optimization used in SQL execution.