Strategy SQL Generation Engine makes it easy to show data that exists on the lookup table and not in the fact table by the use of the 'Preserve all lookup table elements' VLDB property.
A report that shows sales for all the years and all the customer regions even if sales do not exist in the intersection of them can be achieved by the use of this setting.
In a slightly modified Strategy Tutorial demo project, the lookup tables for the customer region and year attribute contain:
January
B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345FieldIf emptyIf providedUser IDAnonymous authentication is usedPassword is requested; this user ID and password are used to authenticateData source informationDISCOVER_DATASOURCES query is issued; result should list the available data sources for the XMLA providerCatalog name is requestedCatalogDBSCHEMA_CATALOGS query is issued; result should list the available catalogs in the given data sourceMDSCHEMA_CUBES query is issued; result should list the available cubes in the given catalogcustomer regionyear
| January
B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345FieldIf emptyIf providedUser IDAnonymous authentication is usedPassword is requested; this user ID and password are used to authenticateData source informationDISCOVER_DATASOURCES query is issued; result should list the available data sources for the XMLA providerCatalog name is requestedCatalogDBSCHEMA_CATALOGS query is issued; result should list the available catalogs in the given data sourceMDSCHEMA_CUBES query is issued; result should list the available cubes in the given catalogcustomer regionyear
|
|
DataSQL
|
A report that shows the sales for each year and customer region generates the following behavior:
Depending on the desired result set, four different values can be selected for the VLDB setting:
DataSQL
|
DataSQL
|
Sales: Sum() {~+}
| Data Rows: 22
Data Columns: 1
select a14.CUST_REGION_ID CUST_REGION_ID,
max(a16.CUST_REGION_NAME)
CUST_REGION_NAME,
a15.YEAR_ID YEAR_ID,
sum(a11.Qty_Sold) SALES
from ORDER_FACT a11
join LU_ORDER a12
on (a11.Order_Id = a12.Order_Id)
join LU_CUSTOMER a13
on (a12.CUSTOMER_ID = a13.CUSTOMER_ID)
join LU_CUST_CITY a14
on (a13.CUST_CITY_ID = a14.CUST_CITY_ID)
join LU_DATE a15
on (a11.Order_Date = a15.DATE_ID)
join LU_CUST_REGION a16
on (a14.CUST_REGION_ID =
a16.CUST_REGION_ID)
group by a14.CUST_REGION_ID,
a15.YEAR_ID
The inner join between the table and the and tables cause that only the common elements between them are shown on the report.Preserve common elements of lookup and final pass result table.This is the default behavior, the Strategy Engine will perform an inner join between the lookup and the intermediate/fact tables.Preserve lookup table elements joined to final pass result table based on fact table keys.
|
Data Rows: 22
Data Columns: 1
select a14.CUST_REGION_ID CUST_REGION_ID,
max(a16.CUST_REGION_NAME)
CUST_REGION_NAME,
a15.YEAR_ID YEAR_ID,
sum(a11.Qty_Sold) SALES
from ORDER_FACT a11
join LU_ORDER a12
on (a11.Order_Id = a12.Order_Id)
join LU_CUSTOMER a13
on (a12.CUSTOMER_ID = a13.CUSTOMER_ID)
join LU_CUST_CITY a14
on (a13.CUST_CITY_ID = a14.CUST_CITY_ID)
join LU_DATE a15
on (a11.Order_Date = a15.DATE_ID)
join LU_CUST_REGION a16
on (a14.CUST_REGION_ID =
a16.CUST_REGION_ID)
group by a14.CUST_REGION_ID,
a15.YEAR_ID
The inner join between the table and the and tables cause that only the common elements between them are shown on the report.Preserve common elements of lookup and final pass result table.This is the default behavior, the Strategy Engine will perform an inner join between the lookup and the intermediate/fact tables.Preserve lookup table elements joined to final pass result table based on fact table keys.
| Data Rows: 22
Data Columns: 1
select a14.CUST_REGION_ID CUST_REGION_ID,
max(a16.CUST_REGION_NAME)
CUST_REGION_NAME,
a15.YEAR_ID YEAR_ID,
sum(a11.Qty_Sold) SALES
from ORDER_FACT a11
join LU_ORDER a12
on (a11.Order_Id = a12.Order_Id)
join LU_CUSTOMER a13
on (a12.CUSTOMER_ID = a13.CUSTOMER_ID)
join LU_CUST_CITY a14
on (a13.CUST_CITY_ID = a14.CUST_CITY_ID)
join LU_DATE a15
on (a11.Order_Date = a15.DATE_ID)
join LU_CUST_REGION a16
on (a14.CUST_REGION_ID =
a16.CUST_REGION_ID)
group by a14.CUST_REGION_ID,
a15.YEAR_ID
The inner join between the table and the and tables cause that only the common elements between them are shown on the report.Preserve common elements of lookup and final pass result table.This is the default behavior, the Strategy Engine will perform an inner join between the lookup and the intermediate/fact tables.Preserve lookup table elements joined to final pass result table based on fact table keys.
|
| Data Rows: 22
Data Columns: 1
select a13.ORDER_ID ORDER_ID,
a12.EMP_ID EMP_ID,
a11.DATE_ID DATE_ID
into #ZZTJI00CJOVOL000
from LU_DATE a11
cross join LU_EMPLOYEE a12
cross join LU_ORDER a13
A cross join is needed to retrieve all the possible combinations between years and customers' regions. In this case the lowest level in the ORDER_FACT table (where the fact comes from) is date, order and employee. The Strategy Engine performs a cartesian between all the lookup tables at that level.
select a14.CUST_REGION_ID CUST_REGION_ID,
a16.CUST_REGION_NAME CUST_REGION_NAME,
a15.YEAR_ID YEAR_ID,
sum(a11.Qty_Sold) M1
from #ZZTJI00CJOVOL000 pa1
left outer join ORDER_FACT a11
on (pa1.DATE_ID = a11.ORDER_DATE and
pa1.EMP_ID = a11.EMP_ID and
pa1.ORDER_ID = a11.ORDER_ID)
join LU_ORDER a12
on (pa1.ORDER_ID = a12.ORDER_ID
join LU_CUSTOMER a13
on (a12.CUSTOMER_ID = a13.CUSTOMER_ID)
join LU_CUST_CITY a14
on (a13.CUST_CITY_ID = a14.CUST_CITY_ID)
join LU_DATE a15
on (pa1.DATE_ID = a15.DATE_ID)
join LU_CUST_REGION a16
on (a14.CUST_REGION_ID = a16.CUST_REGION_ID)
group by a14.CUST_REGION_ID,
a16.CUST_REGION_NAME,
a15.YEAR_ID
drop table #ZZTJI00CJOVOL000
Preserve lookup table elements joined to final pass result table based on template attributes without filter. NOTE: Filter added on = 1997
|
| Data Rows: 77
Data Columns: 1
select a14.CUST_REGION_ID CUST_REGION_ID,
a15.YEAR_ID YEAR_ID,
sum(a11.Qty_Sold) SALES
into #ZZTJI00CLAFOL000
from ORDER_FACT a11
join LU_ORDER a12
on (a11.Order_Id = a12.Order_Id)
join LU_CUSTOMER a13
on (a12.CUSTOMER_ID = a13.CUSTOMER_ID)
join LU_CUST_CITY a14
on (a13.CUST_CITY_ID = a14.CUST_CITY_ID)
join LU_DATE a15
on (a11.Order_Date = a15.DATE_ID)
where a15.YEAR_ID in (1997)
group by a14.CUST_REGION_ID,
a15.YEAR_ID
The filter is applied to the metric calculation.
select distinct a11.CUST_REGION_ID CUST_REGION_ID,
a12.YEAR_ID YEAR_ID
into #ZZTJI00CLAFOL001
from LU_CUST_REGION a11
cross join LU_YEAR a12
A cross join is needed to retrieve all the possible combinations between years and customers' regions. Notice that the filter on year is not applied here.
select pa2.CUST_REGION_ID CUST_REGION_ID,
a11.CUST_REGION_NAME CUST_REGION_NAME,
pa2.YEAR_ID YEAR_ID,
pa1.SALES WJXBFS1
from #ZZTJI00CLAFOL001 pa2
left outer join #ZZTJI00CLAFOL000 pa1
on (pa2.CUST_REGION_ID = pa1.CUST_REGION_ID and
pa2.YEAR_ID = pa1.YEAR_ID)
join LU_CUST_REGION a11
on (pa2.CUST_REGION_ID = a11.CUST_REGION_ID)
In the last pass, an outer join is performed against the OL (Outer Join to Lookup) table. This will return all the elements present in the lookup table even if they don't exist in the fact table. Here the filtered attributes are lost because of the outer join and all the years are shown on the report, but the metric is only calculated for 1997.
drop table #ZZTJI00CLAFOL000
drop table #ZZTJI00CLAFOL001
Preserve lookup table elements joined to final pass result table based on template attributes with filter. NOTE: Filter added on = 1997
|
| Data Rows: 11
Data Columns: 1
select a14.CUST_REGION_ID CUST_REGION_ID,
a15.YEAR_ID YEAR_ID,
sum(a11.Qty_Sold) SALES
into #ZZTJI00CL8ZOL000
from ORDER_FACT a11
join LU_ORDER a12
on (a11.Order_Id = a12.Order_Id)
join LU_CUSTOMER a13
on (a12.CUSTOMER_ID = a13.CUSTOMER_ID)
join LU_CUST_CITY a14
on (a13.CUST_CITY_ID = a14.CUST_CITY_ID)
join LU_DATE a15
on (a11.Order_Date = a15.DATE_ID)
where a15.YEAR_ID in (1997)
group by a14.CUST_REGION_ID,
a15.YEAR_ID
The filter is applied to the metric calculation.
select distinct a11.CUST_REGION_ID CUST_REGION_ID,
a12.YEAR_ID YEAR_ID
into #ZZTJI00CL8ZOL001
from LU_CUST_REGION a11
cross join LU_YEAR a12
where a12.YEAR_ID in (1997)
A cross join is used to retrieve all the possible combinations between years and customers' regions. Notice that the filter on year is applied here as well.
select pa2.CUST_REGION_ID CUST_REGION_ID,
a11.CUST_REGION_NAME CUST_REGION_NAME,
pa2.YEAR_ID YEAR_ID,
pa1.SALES WJXBFS1
from #ZZTJI00CL8ZOL001 pa2
left outer join #ZZTJI00CL8ZOL000 pa1
on (pa2.CUST_REGION_ID = pa1.CUST_REGION_ID and
pa2.YEAR_ID = pa1.YEAR_ID)
join LU_CUST_REGION a11
on (pa2.CUST_REGION_ID = a11.CUST_REGION_ID)
where pa2.YEAR_ID in (1997)
In the last pass, an outer join is performed against the OL (Outer Join to Lookup) table. This will return all the elements present in the lookup table even if they don't exist in the fact table. The filter is applied in this pass as well to limit the results shown on the report.
drop table #ZZTJI00CL8ZOL000
drop table #ZZTJI00CL8ZOL001
|
