Another way to get LastNonEmpty over all date period via ETL in SSAS

There are many ways to calculate LastNonEmpty over all date period — with a standart function in SSAS Enterprise edition, calculated measures in MDX and in a storage via filling values on every day. Recently I have got a task to calculate this measure on a big fact table (millions of rows) and a big dimension (nearly one million). I’ve tried to use a standart function, but it was too slow. Let me explain the another approach which also has its pluses (better speed) and minuses (no multiple selection on date dimension).

As a storage we take AdventureWorksDW2014. Main steps are:

  1. Add the new column in the data source view table (FactInternetSales) or replace it by the new view with that column.
  2. Place the difference between a current sale sum and a sum of previous sale (or 0 if it doesn’t exists)
  3. Create a new measure in cube with SUM on this column
  4. Create a calculated measure with SUM from the first to the current date

Create a view with new column and replace a table by it in «Adventure Works DW.dsv»

CREATE VIEW [dbo].[v_FactInternetSales]
AS
SELECT c.* -- don't use * in production )
    ,CASE WHEN c.[SalesOrderLineNumber] <> 1 THEN NULL
        ELSE CurrentOrder.TotalSalesAmount - COALESCE(PrevOrder.TotalSalesAmount, 0)
    END AS SalesAmountDelta
FROM [dbo].[FactInternetSales] AS c
OUTER APPLY
(
	SELECT SUM(t.[SalesAmount]) TotalSalesAmount
	FROM [dbo].[FactInternetSales] t
	WHERE t.[SalesOrderNumber] = c.[SalesOrderNumber]
) CurrentOrder
OUTER APPLY
(
	SELECT TOP 1 t.SalesOrderNumber
	FROM [dbo].[FactInternetSales] t
	WHERE t.[CustomerKey] = c.[CustomerKey]
		AND t.OrderDateIndex < c.OrderDateIndex
	ORDER BY t.OrderDateIndex DESC
) PrevOrderNumber
OUTER APPLY
(
	SELECT SUM(t.[SalesAmount]) TotalSalesAmount
	FROM [dbo].[FactInternetSales] t
	WHERE t.SalesOrderNumber = PrevOrderNumber.SalesOrderNumber
) PrevOrder

This is a main trick. We need to create the value, which can be summarized over the period from the first date to the current and as the result we will get the value of the last element.

Create the measure

measure

and finally test in MDX

WITH
MEMBER [Measures].[LASTSALE_DELTA] AS
	SUM(NULL:[Date].[Date].CURRENTMEMBER, [Measures].[Sales Amount Delta])
SELECT
	HEAD([Customer].[Customer].[Customer].MEMBERS, 10) *
	{
		[Measures].[Internet Sales Amount],
		[Measures].[LASTSALE_DELTA]
	} ON 0,
	[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works]

There are some speed tests on my notebook:

  1. With the standart function, 72 sec
  2. The variant by Chris, 12 sec
  3. This approach, 8 sec

Hope it helps!

Реклама
Another way to get LastNonEmpty over all date period via ETL in SSAS