Understanding Attribute Join Types

Each attribute join type affects what attribute elements are displayed on the report. You can display only those attribute elements that appear in the related lookup and fact tables in your data warehouse; or you can display all attribute elements that exist in the lookup tables in your data warehouse, regardless of whether there is corresponding data in the related fact tables. A detailed description of each attribute join type is provided in this section.

Examples for each attribute join type are also provided. The examples are based on the following simple example data in a sample data warehouse.

Store table (lookup table)

Store ID	Store Name
1	East
2	Central
3	South
6	North

Sales table (fact table)

Store ID	Year	Dollar Sales
1	2025	1000
2	2025	2000
3	2025	5000
1	2026	4000
2	2026	6000
3	2026	7000
4	2026	3000
5	2026	1500

The Sales table has data for Store IDs 4 and 5, but the Store table does not have any entry for these two stores. Notice that the North Store (Store ID 6) does not have any entries in the Sales table. This sample data is used in the examples below to show how the different attribute join types work.

For example, your report contains the attribute Store and a Sales metric defined as Sum(Sales), which is the sum of the data in the Sales fact table. In your data warehouse, it is possible that a store does not have any sales at all. However, you want to show all store names in the final report, even the names of those stores that do not have sales. To do this, you cannot rely on the stores in the sales fact table. Instead, you must make sure that all the stores from the lookup table are included in the final report. In SQL terms, the SQL Engine must perform a left outer join from the lookup table to the fact table.

Preserve common elements of lookup and final pass result table

This attribute join type is the default setting. It causes the SQL Engine to calculate for only the attribute elements that exist in both the lookup and fact tables. This join type lets you display only those attribute elements that are common to the lookup tables and the fact tables in your data warehouse.

You cannot change the join types of individual attributes on the report by using this setting. The attributes and their join types are displayed for information only.

Preserve lookup table elements joined to final pass result table based on fact table keys

This attribute join type causes the SQL Engine to calculate for only the attribute elements that are joined to the fact table based on fact table keys.

Sometimes the level of the fact table is not the same as the level of the report. For example, a report contains the attributes Store and Month and the Sales metric which is based on the Sales fact table. The report level is Store and Month, but the fact table level is Store, Day, and Item. Different methods to keep all the store and month attribute elements so they are displayed on the report are described below:

Perform a left outer join first to keep all attribute elements at the Store, Day, and Item level, then aggregate the data to the Store and Month level.
Perform the data aggregation first, then perform a left outer join to bring in all attribute elements.

This attribute join type uses the first approach. Working with the example above, the SQL Engine makes two SQL passes:

Pass 1: LOOKUP_STORE cross join LOOKUP_DAY cross join LOOKUP_ITEM; inserted into temporary table TT1
Pass 2: TT1 left outer join Fact_Table on (store, day, item)

The advantage of this approach is that you can perform a left outer join and aggregation in the same pass (pass 2). The disadvantage is that because you perform a Cartesian join with the lookup tables at a much lower level (pass 1), the result of the Cartesian joined table (TT1) can be very large.

Preserve lookup table elements joined to final pass result table based on template attributes without filter

This attribute join type causes the SQL Engine to calculate for all attribute elements and ignores all related filtering conditions.

Perform a left outer join first to keep all attribute elements at the Store, Day, and Item level, then aggregate the data to the Store and Month level.
Perform the data aggregation first, then perform a left outer join to bring in all attribute elements.

This attribute join type uses the second approach. The SQL Engine makes three SQL passes:

Pass 1: Aggregate the Fact_Table to TT1 at Store and Month. This is actually the final pass of a normal report when this setting is not enabled.
Pass 2: LOOKUP_STORE cross join LOOKUP_MONTH; inserted into temporary table TT2
Pass 3: TT2 left outer join TT1 on (store, month)

This approach requires one more pass than the previous join type setting (Preserve lookup table elements joined to final pass result table based on fact table keys), but the cross join table (TT2) is usually smaller.

Preserve lookup table elements joined to final pass result table based on template attributes with filter

This attribute join type causes the SQL Engine to calculate for all attribute elements and applies all related filtering conditions.

This attribute join type is similar to the previous join type (Preserve lookup table elements joined to final pass result table based on template attributes without filter). The only difference is that the report filter is applied in the final pass (Pass 3). For example, a report contains Store, Month, and Sum(Sales) with a filter of Year = 2025. You want to display every store in every month in 2025, regardless of whether there are sales. However, you do not want to show any months from other years, but only the 12 months in the year 2025. This attribute join type provides this data.