How to use Dynamic Dimension YTD, QTD, MTD, WTD in Pentaho

you can make accumulated measurement using Dynamic Dimension. instead of periodical measurement, you can make an “as per date” measurement. current stock is an perfect example of accumulated measurement of inventory transaction, that accumulated from the begining to current date.

as you may know, pentaho mondrian OLAP cube build from a flat table, consist of several dimensions and measurement. when you drill down a dimension, mondrian group measurement based on exploded dimension.

screenshot above show inventory cube. the magenta box, indicated measurment value for all movement type, in 2012, and limited to alcoholic product group. the measurement amount, 4.890.922 is not current stock of Dec 31th 2012. mondrian just sum all movement quantity that applicable for drilled-down dimensions. it’s mean, 4.890.922 come from all inventory transaction that happen in 2012 and limited to alcoholic product group. if you dig deeper to date dimension, you will find measurement amount for each month. but, once again, it’s not current stock, for example, of Jan 31th 2012. it’s sum all movement that happen in Jan 2012 and limited to alcoholic product group.

now we want to make a more advanced measurement, that accumulated the measurement amount instead of grouping it. mondrian have several function to do this: YTD, QTD, MTD, WTD. all of them related to time dimension (that’s why you need to declare dimension type to time dimenson in mondrian schema. see pentaho mondrian dimension design blog post for further technical details). in this post, I would like to use YTD as an example, of course you can change syntax that I show, if you want to try another function.

here an example of mdx query to use YTD function:

WITH MEMBER [Measures].[Stock On Hand] AS 'SUM(YTD([Date].CurrentMember), [Measures].[Quantity])'
SELECT {[Measures].[Quantity], [Measures].[Transaction Count]} ON COLUMNS,
{[Date].[All Year]} ON ROWS FROM [Inventory]
WHERE [Product Group].[Alcoholic]

mdx query above tell mondrian to accumute measurement value of Quantity, based on Date time dimension. and look at screenshot below:

I know, still nothing special, but, try to drill down year 2012

and change mdx to put [Measures].[Stock On Hand] to your selected measurement

WITH MEMBER [Measures].[Stock On Hand] AS 'SUM(YTD([Date].CurrentMember), [Measures].[Quantity])'
SELECT {[Measures].[Quantity], [Measures].[Transaction Count], [Measures].[Stock On Hand]} ON COLUMNS,
Hierarchize(Union(Union({[Date].[All Year]}, [Date].[All Year].Children), [Date].[2012].Children)) ON ROWS 
FROM [Inventory] WHERE [Product Group].[Alcoholic]

and screenshot below show the result:

look at measurement stock on hand, mondrian accumulate quantity for each month in 2012 and put the result into stock on hand. why this is correct? let imagine that stock balance as per date Jan 31 2011 is zero, and based on screenshot above, qty on jan 2012 is 24,750. it’s mean in January 2012 alone, all incoming (positive qty transction), subtracted by all outgoing (negative qty transaction), all of them summarize and get figure 24,750. it’s mean current stock on Jan 2012 is 24,750. this is ending balance of Jan 2012, and also used as beginning balance of Feb 2012.

in Feb 2012, all incoming (positive qty transction), subtracted by all outgoing (negative qty transaction), all of them summarize and get figure 857,000. this is not the current stock, since we have beginning balance of Feb 2012 about 24,750. instead of current stock, 857,000 is net-of transaction for Feb 2012. then we can add it to beginning balance and get 881,750 as current stock. that’s why we can say YTD of sum of transaction quantity equal to current stock.

can you imagine another usage of YTD?

or, futhermore YTD aplicable only for month within a year. it’s not applicable to accumulate measurement year on year. so, what about beginning balance of Jan 2013? it’s not start from zero, it’s use ending balance as per date Dec 31 2012 as it’s beginning balance. how we can achieve this?


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s