Show Attributes That Have No Measures - SPARSE

The SPARSE function allows you to show dimension column values for which there is no fact data. SPARSE is a middle tier function which enables it to be used with OUTER JOINS and to be used at the database level.

SPARSE works with multiple dimensions in a query. The middle tier does multiple queries to ensure all sparse entries are filled with nulls.

The basic syntax for SPARSE is:

SPARSE([dimension])

Consider the following logical query:

SELECT USING OUTER JOIN [Products.ProductName] 'F8',[OrderDate: Sum: Quantity] 'F1',[Time.Year/Quarter],SPARSE([Time.Year/Quarter]) 'F5' FROM [ALL] WHERE ([Products.ProductName]=Longlife Tofu OR [Products.ProductName]=Chocolade) AND [Time.Year/Quarter]>=1993 AND [Time.Year/Quarter]<=1997 ORDER BY [Time.Year/Quarter] ASC

As shown in the report below, it is filling in the extra [Time.Year/Quarter] values. Since this is done in the logical layer, this will work with as many SPARSE columns as needed.

Under the covers, Birst is issuing two queries:

SELECT DW_DM_PRODUCTS_PRODUCTS2_.ProductName$ AS 'F8',SUM(CAST(DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS0_.Quantity$ AS BIGINT)) AS 'F1',DW_DM_TIME_DAY1_.Year_Quarter$ AS 'Year/Quarter'

FROM S_N9c6c0cea_3793_415f_8c4b_dad108aaed87.DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS0_

INNER JOIN dbo.DW_DM_TIME_DAY DW_DM_TIME_DAY1_ ON DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS0_.Time$OrderDate_Day_ID$=DW_DM_TIME_DAY1_.Day_ID$

INNER JOIN S_N9c6c0cea_3793_415f_8c4b_dad108aaed87.DW_DM_PRODUCTS_PRODUCTS DW_DM_PRODUCTS_PRODUCTS2_ ON DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS0_.Products$Products1249892458$=DW_DM_PRODUCTS_PRODUCTS2_.Products1249892458$

WHERE ((DW_DM_PRODUCTS_PRODUCTS2_.ProductName$='Longlife Tofu' OR DW_DM_PRODUCTS_PRODUCTS2_.ProductName$='Chocolade') AND DW_DM_TIME_DAY1_.Year_Quarter$>='1993' AND DW_DM_TIME_DAY1_.Year_Quarter$<='1997')

GROUP BY DW_DM_PRODUCTS_PRODUCTS2_.ProductName$,DW_DM_TIME_DAY1_.Year_Quarter$,DW_DM_TIME_DAY1_.Quarter_ID$

ORDER BY DW_DM_TIME_DAY1_.Year_Quarter$ ASC

And

SELECT DW_DM_TIME_QUARTER0_.Year_Quarter$ AS 'Year/Quarter'

FROM dbo.DW_DM_TIME_QUARTER DW_DM_TIME_QUARTER0_

WHERE (DW_DM_TIME_QUARTER0_.Year_Quarter$>='1993' AND DW_DM_TIME_QUARTER0_.Year_Quarter$<='1997')

The second query that returns the values of [Time.Year/Quarter] is filtered. This query extracts from the parent query any filters that apply only to that dimension, reducing result set sizes.