Minimized SQL versus preventing join elimination

When a table is designed in the modeling tools, it can comprise multiple columns from multiple tables. When you query one column from this modeled table, you might expect to see SQL that omits the tables from which columns are not referenced. This concept is referred to as join elimination or minimized SQL.

Depending on the model design, minimized SQL might not occur, and the query acts like a view. In this case, all the joins of the underlying tables are enforced in a subquery before the parent query selects the single column in the final projection list. In some cases, this behavior might be desired because you want to enforce a join structure that controls the amount and type of data returned. This view behavior is also known as preventing join elimination.

For example, take the case of a Product dimension which includes four underlying tables, Product dim and three lookup tables that have a relationship to Product dim: Product line lookup, Product type lookup, and Product lookup. When you query Product line from Product line lookup, you might want only product lines returned, where there are also product types and products. In this case, you must ensure that the underlying join between these tables is enforced, provided inner joins are defined in the model. However, in some cases when you query Product line, you want to see all of the tables, regardless if they have product types and products associated. In this case, there are two ways to accomplish this requirement. The first is to configure a left outer join to Product type lookup from Product line lookup, with all the underlying joins still enforced in a subquery. The second option is to ensure that minimized SQL is generated by configuring the model to do so.

Let’s look at the difference in the SQL for the Product line query. The following example shows non-minimized SQL (no join elimination).

WITH 
"PRODUCT_LOOKUP0" AS 
    (
    SELECT
        "PRODUCT_LOOKUP01"."PRODUCT_NUMBER" AS "PRODUCT_NUMBER", 
        "PRODUCT_LOOKUP01"."PRODUCT_LANGUAGE" AS "PRODUCT_LANGUAGE", 
        "PRODUCT_LOOKUP01"."PRODUCT_NAME" AS "PRODUCT_NAME", 
        "PRODUCT_LOOKUP01"."PRODUCT_DESCRIPTION" AS "PRODUCT_DESCRIPTION"
    FROM
        "PRODUCT_LOOKUP" "PRODUCT_LOOKUP01" 
    WHERE 
        "PRODUCT_LOOKUP01"."PRODUCT_LANGUAGE" IN ( 
            'EN' )
    ), 
         "Product_Line_Lookup_View_1" AS 
    (
    SELECT
        "PRODUCT_LINE_LOOKUP0"."PRODUCT_LINE_EN" AS "PRODUCT_LINE_EN"
    FROM
        "PRODUCT_LINE_LOOKUP" "PRODUCT_LINE_LOOKUP0"
            INNER JOIN "PRODUCT_DIM" "PRODUCT_DIM0"
            ON "PRODUCT_LINE_LOOKUP0"."PRODUCT_LINE_CODE" = "PRODUCT_DIM0"."PRODUCT_LINE_CODE"
                INNER JOIN "PRODUCT_LOOKUP0"
                ON "PRODUCT_LOOKUP0"."PRODUCT_NUMBER" = "PRODUCT_DIM0"."PRODUCT_NUMBER"
                    INNER JOIN "PRODUCT_TYPE_LOOKUP" "PRODUCT_TYPE_LOOKUP0"
                    ON "PRODUCT_TYPE_LOOKUP0"."PRODUCT_TYPE_CODE" = "PRODUCT_DIM0"."PRODUCT_TYPE_CODE"
    )
SELECT
    "Product_Line_Lookup_View_1"."PRODUCT_LINE_EN" AS "Product_Line"
FROM
    "Product_Line_Lookup_View_1" 
GROUP BY 
    "Product_Line_Lookup_View_1"."PRODUCT_LINE_EN"

Notice all the inner joins in the sub query for Product_Line_Lookup_View_1. The joins for all four tables are enforced.

Compare this SQL with the following example of minimized SQL (joins are eliminated) for the Product line query:

SELECT
    "Product_Line_Lookup_View_1"."PRODUCT_LINE_EN" AS "Product_Line"  
FROM
    "PRODUCT_LINE_LOOKUP" "Product_Line_Lookup_View_1" 
GROUP BY 
    "Product_Line_Lookup_View_1"."PRODUCT_LINE_EN"

In this case, it's a simple column selection from a table.

If you then added an item from Sales fact to the query, the appropriate underlying joins from Product line lookup to Product dim to Sales fact would be used to aggregate the values from the Sales fact table.

Minimized SQL in Framework Manager

In Framework Manager, you can configure a query subject to use minimized SQL, which is the default setting. For more information, see Changing how the SQL is generated.

However, even if minimized SQL is configured in Framework Manager, if you create a model query subject, and then attach a relationship join to it, the model query subject always acts as a view. The joins are enforced as shown in the following model design scenario, which results in non-minimized SQL:

To generate minimized SQL, as shown in the following scenario, you wouldn’t join from Products to Sales fact. Instead, you would join Product dim from the underlying product tables to the Sales fact table. When querying a single column, such as Product line, from Products, the SQL would be minimized as expected.

If you then added an item from Sales fact to the query, the appropriate underlying joins from Product line lookup to Product dim to Sales fact would be used to aggregate the values from the Sales fact table.

Minimized SQL in data modules

In data modules, if you want the minimized SQL or view behavior, use the table property Item list. A consolidated table with a relationship join attached to it uses this property to generate minimized SQL, or enforce the underlying joins.

For more information, see Generating the query SQL.