The Last Non-Empty Dilemma TABULAR Style

In a previous post, I addressed the function of the last non empty aggregation in SSAS (OLAP). Specifically, I looked at what happens when there are ‘missing’ chucks of data in the fact table and how the OLAP engine natively carries forward the balance to an aggregated time member without regard for the what is the ‘true’ last date. That is, we expect the last date of stock to be applied for all aggregated date values regardless of whether data exists or not. This post looks at the LNE calculation in the tabular model.

Generally, we expect the stock schema to take the form of the fact (balances) surrounded by the dimension table. The fact table (Stock) holds the [Quantity on Hand] (on_hand) for a given date with the Dates table showing a natural hierarchy of Year, Month and Date.

Summing the column on_hand will allow the calculation of daily balances (we will be able to show correct balances when a date is selected) however, if we use any aggregated Date field, the [Stock on Hand] value will be incorrect because the formula adds the amount for all days filtered by the date restriction.

For example, if we consider a very small subset of data (shown as ‘Source Data’) and [Stock on Hand] defined as

Stock On Hand:=Sum([on_hand])

We can see that Jan 2011 for Product A (20)=15+4+1 and for product B (2011)=10+8

Source Data	[Stock On Hand] using Sum

Clearly, the addition for aggregate time members does not give us what we want.

The next thought is to change the stock filter context to the max of time. That is, return the sum of stock for the last date in the selected time period. For example;

Stock On Hand:=CALCULATE(sum([on_hand]), FILTER(ALL(Dates[date]), Dates[date]=max(Dates[date])))

The problem with this approach is that the aggregate values will only show for the last date in the month (or year). This is not a problem when we have full year worth of data in the fact and the maximum of the date filter is the last date in the period. But in this case we don’t and usually the most recent data will not be complete for the year. For Product A in the picture below, the total for Jan-2011 is correct (1) because the maximum date for Jan is 31-Jan and has data (see source). However, in the year context (ie 2011) the maximum date is 31-Dec-2011 and there is no stock for this and therefore the sum is empty.

Source Data	[Stock On Hand] using max(date.date)

To solve this problem, it is important to remember that the filter context applies a filter to the fact. So, for the filter context of Jan-2011 (say when Jan-2011 is on a row), the data in the fact is filtered for all the dates in Jan. What we are really interested in therefore is the last date in the fact for the current filter context.

Stock On Hand:=CALCULATE(sum([on_hand]), filter(Stock, Stock[date_key]=Max(Stock[date_key])))

In this formula, we recognise that the filter context has been applied to the fact table and use this to derive the maximum date for the current filter period and then use this to further filter the fact.

Source Data	[Stock On Hand] using max(Stock.date)

Alternate Approach using Transactions

In addition to using the quantity of stock as a field in the fact, we may also consider using the transactional amount. In this case the use of summing against the Date table filter context works because we are interested in summing all data less than the max date of the filter context. In this situation, our formula would be;

Stock On Hand:=CALCULATE(sum([qty_mvt]), FILTER(Stock, Stock[date_key]<=max(Dates[date])))

The Last Non Empty Dilemma

The quantity of stock on hand is a common reporting requirement for inventory systems where quantity of stock on hand at aggregate time levels is shown as the last value that was recorded. When time is aggregated, (eg the year as a parent of months), the value shown refers to the last recorded value and not the sum of months. In this post we look at the use of LastNonEmpty aggregation, and the implications that it has.

The data used in this post assumes the leaf data for the diagram above (products [a], [b], [c], [d] with moths [Oct], [Nov], [Dec]). When the November stock take is done, the year value for stock holding should display 19, and when December stock take is done the year value should show 12. Showing the year value as the sum of its children will give incorrect results because, (after Novembers stocktake) the stock holiding is not equal to October quantity plus November (45=26+19), only the last known value (Nov) 19.

Last Non Empty Aggregation

The SSAS aggregation function ‘LastNonEmpty’ can solve this issue automatically by showing the time aggregates as the last recorded value. By default aggregation is set to sum but it can be set on the properties tab for the measure (as below).

The higher levels of time now inherit the last known quantity for a given product which may seem a reasonable solution (below). However, this can cause an issue when stock values are not present for all stock items in the last period. When an stock item (sku) is excluded, the LastNonEmpty inherits the last known period value. In this situation, the LastNonEmpty can bypass the last known (or input) period and aggregate values across inconsistent periods. This is shown in the diagram below where the last recorded period is December and the yearly counts include November balances.

Furthermore, total balances are calcualted as the total for the LastNonEmpty period and so the total for the time aggregate doesnot equal the sum of its children. We can see that;

1. [CY 2010] values at the product level inherits the last known value.
2. [CY 2010] [Grand Total] inherits the [Grand Total] for [CY 2010 Dec] (so that 12=12) and
3. The [Grand Total] for [CY 2010] does not equal the sum of each product ([a],[b],[c],[d]) for [CY 2010] (ie 12 is not the sum of 8+3+4+1). If we are to assume that the values for [CY 2010] for each product will aggregate to the [Grand Total] for the product, we should see 16 as the [Grand Total] for [CY 2010].

Clearly, the use of the LastNonEmpty as an aggregate function comes with some caveats.

Solution 1 (Fact Based)

One way to solve this issue is to include zero balances for inventory items where the value is zero (or would otherwise be null). This can be done by appending the a zero quantity products that are not counted (have zero balances or are null). In this case, a view which automatically derives missing data based on the theroitical space of (date x prodcut less non zero values).

As expected, the values for all products are known for a month and the time aggregate inherits the last materialised period amount.

Solution 2 (Script Based)

A different approach is to scipt a measure that satisifys the aggregation function and answers the LastNonEmpty based on the last non empty date period for all products. Leaving the existing LastNonEmpty measure inplace and using a calcualte member [Stock On Hand] to calculate time aggregates based on the [Prod Count] value. Essentially, we are looking for the [Prod Count] values on the last [date] member where any product had a value. In this context, the last date member refers to the last (maximum) date for any product that had stock.

The solution follows the following logic;

1. What was the last date that products had value
2. What was the value of product on this date?

The last date (that any product was sold) can be determined by the snippet below

TAIL(FILTER([Date].[Calander Years].[Cal Month], ([Measures].[Prod Count], ROOT([Product]))<>0), 1)

And the sum aggregates these values (for the last time period)

SUM(  TAIL(FILTER([Date].[Calander Years].[Cal Month], ([Measures].[Prod Count], ROOT([Product]))<>0), 1) 
 , [Measures].[Prod Count] 
 )

Other Calculation Considerations

Introducing the [Stock On Hand] calcualted member solves the aggregate time issues and [CY 2010] values will show correct (as below). The [Stock On Hand] value for [CY 2010] will show the stock as at the last recorded period ([CY 2010 Dec]).

One side-effect for this calcualtion (asis) is that preceeding periods will show values for post dated periods. For example (below), [CY 2010 Sep] values will be displayed on the basis of [CY 2010 Dec]. Clearly this is also not correct as the values for [Sep] should be null.

This behaviour can be corrected by only displaying the calcualted value when a LastNonEmpty value would have otherwise been shown. The calcuation becomes;

IIF([Measures].[Prod Count]=0  , NULL  , SUM(TAIL(FILTER([Date].[Calander Years].[Cal Month], ([Measures].[Prod Count], ROOT([Product]))<>0), 1)  , [Measures].[Prod Count]  ) 

And this satisfies all requirements

Conculsion

The use of LastNonEmpty aggregation offers a convenient way to aggregate measures such as product count across time hierarchies. However, there are some assumptions included with the use of the function that may cause output not to show as expected. This output can be controlled by either by including zero values on for products that are not counted or by scripting a calculated measure to manually aggregate across time.

 

Improving the performance of the sum:

As Chris has pointed out, the sum function will perform poorly in a large environment.  This can be greatly improved by defining the LNE over the tuple for the last date that has values.  That is, we derive the last date member (as below)

TAIL(Filter([Date].[Calander
Years].[Cal Month], (ROOT([Product]),[Measures].[Prod
Count] )> 0)

,1).ITEM(0)

Other methods of defining the last posting date may use attributes of the date dimension (see http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?List=83c71d76-a4d8-4197-9257-38d6b857634f&ID=16)
per Boyans post.

The stock on hand value is then defined as the tuple of the
member and our materialised value;

MEMBER
[Measures].[Stock Count] as

(

[Measures].[Prod Count]

,[Date].[Calander Years].LAST_MONTH_VALUE

)