Monday, October 29, 2012

Use of the "periods-to-date" function in OLAP cube

Hi all!

Today we will see one of the possible way to use the "periods-to-date" function (mdx language) in a OLAP cube...

If in AX 2009 we want to see a summarized requirements plan, perhaps with item details in row and the calendar days in column, we have two possibilities.....

one is to write al lot of code and create a new matrix-like form to be filled with data from ReqTrans table (with all the connected difficulties.....)

the second way is to create an OLAP cube using BIDS (Business Intelligence Development Studio).
I followed this way a few month ago and I have created a cube that shows the variation of the availability for each item in the future...(if anyone is interested in this cube can contact me).

The "heart" of this cube is exactly the "periods-to-date" function...
here's an example:

SUM((PERIODSTODATE([Time].[Data].[Anno],[Time].[Data].CurrentMember)),[Measures].[Quantità fabbisogno])

this function allows to calculate the variation of quantity from the first member of the time dimension to the current member of the time dimension. So we can simply change the value of the time dimension filter and we have the updated value...

In the example we have:

- [Time].[Data].[Anno]: time dimension hierarchy
- [Time].[Data].CurrentMember: identifies the current value of the time dimension filter
- [Measures].[Quantità fabbisogno]: the measure field in the cube

This is all......! see you next time!

No comments:

Post a Comment