The Strategy SQL Engine will join database tables together based on the lowest level attributes that Strategy Architect assigns to each table. If an attribute is the lowest level attribute in a hierarchy, then the Strategy SQL Engine will place a key beside this attribute in its logical table view. This means that an attribute with no children is considered to be the lowest level attribute and as a result, this attribute will have a key next to it in all of its source tables. However, even though an attribute may have children, it can still have a key next to it. This situation applies when an attribute is the lowest level attribute in that particular table.
The Strategy SQL Engine will then use the lowest level column name in each table to join two tables together. Furthermore, if there are multiple common lowest level attributes between two tables, then the Strategy SQL Engine will use all of these common keys to join the tables together.
For example, consider the situation where the “Month” attribute is the lowest level attribute in the LU_MONTH table and “Day” attribute is the lowest level attribute in the LU_DATE and the DAY_CTR_SLS tables. This means that if there exists a report with the attributes “Month” and “Day” and the metric “Revenue”, the Strategy SQL Engine will use the ‘Month’ column to join the LU_MONTH and LU_DAY tables and it will use the ‘Day’ column to join the LU_DATE and DAY_CTR_SLS tables, since these two columns are the lowest level attributes. The SQL for this report would be the following:
Sql
select a12.[MONTH_ID] AS MONTH_ID,
max(a13.[MONTH_DESC]) AS MONTH_DESC,
a11.[DAY_DATE] AS DAY_DATE,
sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from [DAY_CTR_SLS] a11,
[LU_DAY] a12,
[LU_MONTH] a13
where a11.[DAY_DATE] = a12.[DAY_DATE]
and a12.[MONTH_ID] = a13.[MONTH_ID]
group by a12.[MONTH_ID],
a11.[DAY_DATE]
