Database Reference
In-Depth Information
custom SQL for data loads, Studio does not allow custom SQL for dimensional/hierar-
chical objects. As such, the SQL optimization opportunities are limited.
3.4.1.1 Adding Joins and Adding Joins by Inspection
When working with mini-schemas,
you have the option of either creating joins manually from table to table or add views by
inspection. In general, regardless of whether you are prototyping with Studio or lever-
aging the tool for a production deployment, adding joins by inspection is not a recom-
mended practice. Please note that Studio adds joins automatically when adding tables to
a mini-schema. tables with key fields are joined where the column names match.
When adding joins by inspection, Studio tends to add both too many joins and joins
in a less optimal direction. For example, Figure 3.8 shows optimal joins between dimen-
sion tables and a fact table.
notice that there is a single join between the dimension tables and the fact table in
this image. Further, the joins go from the fact table out to the dimension tables. Creating
your joins from the fact table out allows Studio to generate more optimal SQL. If you
must create a dimension from multiple tables (in more of a snowflake fashion), the joins
are more optimal moving from the fact table out. The join should move from the fact
table out to the most immediately connected dimension table and then out to the sec-
ondary dimension table (as shown in Figure 3.9).
Figure 3.10 illustrates the same relational source after adding joins by inspection.
notice in this instance that the Studio engine pattern-matched field names and cre-
ated all possible joins to ensure you can create dimensionality across tables and link
everything back to the fact table. In this simple example, there are only a few additional
joins added. however, the more complex the underlying structure, the more complex
the join population becomes. While this may result in the same dimensionality and
overall end product, the SQL generated will be significantly more complex and slower.
If you run into issues or inconsistent results during the dimension build or data load
stages, having the extended set of joins makes this much more difficult to troubleshoot.
Figure 3.8
Fact table star schema joins.
