I want my cube to report maximum delay between subsequent orders. For example I place calendar on rows, products on columns and in data section the member that represents the maximum, let's say in days, delay between subsequent orders per product or
whatever is in the hierarchy. Is it possible to create such a calculated member purely in SSAS or is it necessary to use ETL tools to accomplish it?
So far, I have no Idea how to say in MDX: "get current date member and subtract it from the next date member where the measure of the same category exists". I don't know whether I explained my problem clearly so below I placed an SQL that does what I want
for "the entire cube". I used AdventureWorksDW2008R2.
with sales_cte as (
,RANK() over (Order by p.ProductKey,d.FullDateAlternateKey,r.SalesOrderNumber) as pRank
join dbo.DimDateas d
on r.OrderDateKey = d.DateKey
join dbo.DimProductas p
onr.ProductKey = p.ProductKey
select top 1
,datediff(DAY, c1.FullDateAlternateKey,c2.FullDateAlternateKey) as nextSaleDelay
on c1.pRank=c2.pRank-1 and c1.ProductKey=c2.ProductKey
order by nextSaleDelay desc
Edited byRafcio666Wednesday, August 22, 2012 8:40 PM