Consolidations and SQL

The calculations associated with a consolidation are done by the Analytical Engine component of Intelligence Server. The SQL Engine writes the SQL query that gets the required data from the warehouse, and then passes it to the Analytical Engine to do any mathematical operation that is needed to create the report.

For example, the following SQL is for the dollar sales by season report discussed in Create a Virtual Attribute.

Notice that the seasons are not mentioned in the SQL. The query retrieves the data for the Months of Year, and then the Analytical Engine performs the necessary calculations to present the data in terms of seasons.

select a12.[MONTH_OF_YEAR] AS MONTH_OF_YEAR, max(a13.[MONTH_OF_YEAR_NAME]) AS MONTH_OF_YEAR_NAME, a12.[YEAR_ID] AS YEAR_ID, sum(a11.[TOT_DOLLAR_SALES]) as DOLLARSALES

from [MNTH_CATEGORY_SLS] a11, [LU_MONTH] a12, [LU_MONTH_OF_YEAR] a13

where a11.[MONTH_ID] = a12.[MONTH_ID] AND a12.[MONTH_OF_YEAR] = a13.[MONTH_OF_YEAR] AND a12.[MONTH_OF_YEAR] in (3, 4, 5, 6, 7, 9, 10, 11, 12, 1, 2)

group by a12.[MONTH_OF_YEAR], a12.[YEAR_ID]