The setting for Derived table for the VLDB property "Intermediate table type" has certain limitations. Some of these limitations are described in the following article:
KB30257 - "Limitations of "derived table" intermediate table type VLDB setting in Strategy SQL Engine"
Another limitation of this setting is using multisource SQL. This is due to the SQL language and how it performs joins on data between sources. When using Multisource reporting, the datasource is always going to need to select the data from the first source, and insert it into the secondary datasource by a means of creating a temporary table. The derived table setting cannot apply in this case because nested SQL statements cannot be used between different data sources.
Here is an example of how the multisource SQL will be implemented using temp tables.
SQL Statements:
[DB Instance: Tutorial Data]
create table ZZMD00 (
Revenue DOUBLE)
[DB Instance: Tutorial Data]
insert into ZZMD00
select max(a11.[TOT_DOLLAR_SALES]) AS Revenue
from [ITEM_MNTH_SLS] a11,
[LU_MONTH] a12
where a11.[MONTH_ID] = a12.[MONTH_ID]
and a12.[Year_ID] in (2010)
[DB Instance: Operational Datamart]
select a11.[REGION_ID] AS REGION_ID,
a11.[MONTH_ID] AS MONTH_ID,
sum(a11.[REVENUE_TARGET]) AS WJXBFS1,
(sum(a11.[PROFIT_TARGET]) / IIF(sum(a11.[REVENUE_TARGET]) = 0, NULL, sum(a11.[REVENUE_TARGET]))) AS WJXBFS2
from [F_TUTORIAL_TARGETS] a11
where a11.[Year_ID] in (2010)
group by a11.[REGION_ID],
a11.[MONTH_ID]
[DB Instance: Tutorial Data]
create table ZZOP01 (
MONTH_ID LONG,
REGION_ID SHORT,
WJXBFS1 DOUBLE,
WJXBFS2 DOUBLE)
[DB Instance: Tutorial Data]
insert into ZZOP01 values ([Analytical Engine Results: MONTH_ID, REGION_ID, WJXBFS1, WJXBFS2])
[DB Instance: Tutorial Data]
select pa11.[MONTH_ID] AS MONTH_ID,
a13.[MONTH_DESC] AS MONTH_DESC0,
pa11.[REGION_ID] AS REGION_ID,
a14.[REGION_NAME] AS REGION_NAME0,
pa12.[Revenue] AS Revenue,
pa11.[WJXBFS1] AS WJXBFS1,
pa11.[WJXBFS2] AS WJXBFS2,
IIF(ISNULL(((IIF(ISNULL(pa12.[Revenue]), 0, pa12.[Revenue]) - IIF(ISNULL(pa11.[WJXBFS1]), 0, pa11.[WJXBFS1])) / IIF(pa11.[WJXBFS1] = 0, NULL, pa11.[WJXBFS1]))), 0, ((IIF(ISNULL(pa12.[Revenue]), 0, pa12.[Revenue]) - IIF(ISNULL(pa11.[WJXBFS1]), 0, pa11.[WJXBFS1])) / IIF(pa11.[WJXBFS1] = 0, NULL, pa11.[WJXBFS1]))) AS WJXBFS3
from [ZZOP01] pa11,
[ZZMD00] pa12,
[LU_MONTH] a13,
[LU_REGION] a14
where pa11.[MONTH_ID] = a13.[MONTH_ID] and
pa11.[REGION_ID] = a14.[REGION_ID]
