Problem

I need to build a report that compares total sales amount of top 10 Product Sub Categories from January to February, 2013 and the same period in last year. It means I need to build the report that is named as Year-over-Year of Calendar Year To Date ( CYTD YoY)

Solution

In MDX language, Microsoft introduced the function which is call YTD. I use this function to build my report as expectation. For understanding how YTD function work, please click here

MDX Query

WITH MEMBER Measures.[CYTD Current]
AS
SUM(YTD([Date].[Calendar].CURRENTMEMBER)
,[Measures].[Reseller Sales Amount])
,FORMAT_STRING =”Currency”
MEMBER Measures.[CYTD Last Year]
AS
SUM(
YTD(
ParallelPeriod([Date].[Calendar].[Calendar Year]
,1
,[Date].[Calendar].CURRENTMEMBER
)
)
,[Measures].[Reseller Sales Amount]
)
,FORMAT_STRING =”Currency”
MEMBER Measures.[% CYTD Growth]
AS
IIF(Measures.[CYTD Last Year] = 0
, null
,(Measures.[CYTD Current] – Measures.[CYTD Last Year]) / Measures.[CYTD Last Year])
,FORMAT_STRING = “Percent”
SELECT NON EMPTY({
Measures.[CYTD Current]
,Measures.[CYTD Last Year]
,Measures.[% CYTD Growth]
}) ON COLUMNS,
TOPCOUNT(NONEMPTY(ORDER(([Product].[SubCategory].CHILDREN), Measures.[CYTD Current],DESC))
,10) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Month].[February 2013]

In the MDX query above, I built three new measures

  • Measures.[CYTD Current] = total sales amount from Jan to Feb in 2013
  • Measures.[CYTD Last Year] = total sales amount from Jan to Feb in 2012. In this measure, I combined YTD and ParallelPeriod functions, the combination returns total sales amount of the same period with current member that we used in WHERE statement
  • Measures.[% CYTD Growth] = % growth when comparing two measures above
  • I also used TOPCOUNT function to return top 10 Product Sub Categories which have the larges sales amount from Jan to Feb in 2013. This function likes SELECT TOP and ORDER BY in T-SQL

Testing

Now, we execute the MDX query and query back to Datawarehouse database to compare the result of MDX and T-SQL. Be aware of TSQL, Microsoft does not support an analysis function for this purpose directly.

I need to query back to the datawarehouse database and building the report with T-SQL language. To be simple, I do not try to use some functions such LEAD or LAG to build. Instead, I use hard code values.

USE AdventureWorksDW2014
GO
WITH CYTD_Current
AS
(
SELECT dpsc.EnglishProductSubcategoryName, SUM(f.SalesAmount) AS [CYTD Current]
FROM [dbo].[FactResellerSales] f
INNER JOIN DimProduct dp ON f.ProductKey = dp.ProductKey
INNER JOIN DimProductSubcategory dpsc ON dp.ProductSubcategoryKey = dpsc.ProductSubcategoryKey
WHERE f.OrderDate >=’2013-01-01′ AND f.OrderDate < ‘2013-03-01′
GROUP BY dpsc.EnglishProductSubcategoryName
)
,CYTD_LastYear
AS
(
SELECT dpsc.EnglishProductSubcategoryName, SUM(f.SalesAmount) AS [CYTD Last Year]
FROM [dbo].[FactResellerSales] f
INNER JOIN DimProduct dp ON f.ProductKey = dp.ProductKey
INNER JOIN DimProductSubcategory dpsc ON dp.ProductSubcategoryKey = dpsc.ProductSubcategoryKey
WHERE f.OrderDate >=’2012-01-01’ AND f.OrderDate < ‘2012-03-01’
GROUP BY dpsc.EnglishProductSubcategoryName
)
SELECT TOP 10 c.EnglishProductSubcategoryName AS [Product Sub Category],c.[CYTD Current],l.[CYTD Last Year]
,CASE WHEN l.[CYTD Last Year] IS NOT NULL OR l.[CYTD Last Year] <> 0
THEN ROUND((c.[CYTD Current]-l.[CYTD Last Year]) / l.[CYTD Last Year] * 100,2 )
ELSE null END AS [% CYTD Growth]
FROM CYTD_Current c
LEFT JOIN CYTD_LastYear l ON c.EnglishProductSubcategoryName = l.EnglishProductSubcategoryName
ORDER BY c.[CYTD Current] DESC

From the comparison table, we can see that both ways are returning the same result.

Conclusion

Obviously, we definitely build the TSQL for Year Over Year of Calendar Year To Date report from relational database and it is the datawarehouse database in my case. However, there is no function to help us build us directly instead we must use other functions for our purpose. One of important things, performance is the key factor when we aggregate data on the fly in a relational database. With YTD function in MDX, we can query data quickly for binding data into our report.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s