SYMPTOM:
Training reports require outer joins for the metrics and for the attributes. Training metrics can only have input that is based off of other metrics, so facts must be created for any attribute that is to be used as an input.
When having all attributes on an outer join some of them are still not displaying all of the attributes that are on the table. This causes the training metric to miss data that is required.
When only having the missing attributes on outer join and the others on inner join, the attributes are displaying properly but the fact that hits the same column is returning nulls. This also causes the training metric to miss data that is required.
CAUSE:
Scenario 1:
Having all attributes on outer joins causes the fact table that is referenced as the lookup table to be joined to the other full lookup tables. It is being inner joined on attributes in common which causes the final pass to not see the data from the rows that do not pair with the other attributes. This is done since lookup tables with columns in common are supposed to be exhaustive.
Example: A lookup table for month that is also a fact table contains a region column. If the region column is not exhaustive (there are rows with months that have region as null), then the final pass will not contain an exhaustive set of months.
Scenario 2:
Having only the missing attributes to outer causes the fact table to be the only one used in the final preserve attribute pass. The attribute values display as expected but the fact values are null. This is caused by the facts being fetched before the final pass in which the joined fact table is used and not the fact table alone. When the fact table is joined to the other tables the rows that do not contain all necessary data are not included and thus the data is not visible. This is happening since the attribute joins are set to inner for all but the one that in Scenario 1 had missing data.
Example: A fact table has data for only one year. Other years are included on the lookup table. Having data as the only outer joined metric causes all dates to be seen but does not result in a Cartesian join to create a list of regions for each date. Only those that are on the fact table will be seen as it was joined during the fact fetching pass. All dates are seen but not all regions for all dates.
ACTION:
The fact and lookup table must not be the same table if it is not exhaustive for all attributes on it. Without an exhaustive fact table, a proper lookup table must be used or table aliased so that the same table is not referenced in both passes and the aliased table is only referenced by the one attribute which it is exhaustive for. With the fact table and lookup table as treated differently and lookup not having attributes in common with other lookup tables, all attributes can be set to an outer join and thus the fact table will not need to be joined in the manner that it will reduce the visible data for the training metric.
