KB239141: How to determine the first lookup table that will be used in the report SQL, for a report that only contains attributes from different hierarchies?

The Strategy SQL Engine follows the logic explained below to find the join path for a report that only contains attributes from different hierarchies:
 

1. The SQL Engine will first resolve the template attributes to the lowest level set and order the set by name  - the attribute order is number first, then followed by alphabet
2. From the first attribute in the resolved set, SQL Engine will search down or up to find the join path to other attributes and the relationship table information is recorded (find this information in the “Tables Accessed” section of the SQL view).
3. Lastly, the engine does some basic optimization to eliminate unnecessary tables and remove them from the join

Refer to Technical Note below for information about the logic used in determining the overall join path.
KB19866: What are the criteria used by the Strategy SQL Engine to build the join tree of a report’s SQL in Strategy Desktop 9.x?
There are occasions where users need a specific lookup table to be accessed first. The workaround in such cases is to change the attribute name and ensure that the required attribute (who's lookup table needs to be accessed first) is ordered before the other.
Create a report with attribute Country and Year; LU_COUNTRY is used as the first lookup table.

select    distinct a11.[COUNTRY_ID] AS COUNTRY_ID,
    a11.[COUNTRY_NAME] AS CUST_COUNTRY_DESC0,
    a12.[YEAR_ID] AS YEAR_ID
from    ([LU_COUNTRY]    a11
    cross join    [LU_YEAR]    a12)

Change the attribute Year to 0Year; LU_YEAR is used as the first lookup table.

select    distinct a12.[COUNTRY_ID] AS COUNTRY_ID,
    a12.[COUNTRY_NAME] AS CUST_COUNTRY_DESC0,
    a11.[YEAR_ID] AS YEAR_ID
from    ([LU_YEAR]    a11
    cross join    [LU_COUNTRY]    a12)