EPM Lab – ASO Time Series Reporting

November 20, 2018
Posted by Jun on

If you are familiar with planning and budgeting solutions, I believe you would know that in BSO cube, we are able to utilize the out-of-the-box dynamic time series to report on YTD, QTD, etc. But ASO cube does not provide this dynamic time series. As the current cloud solutions support data map, smart push, groovy rules to move data from data from BSO to ASO, we definitely would like to report on YTD, QTD from the ASO cubes.

 

This time, let’s check some basic ideas on how would we achieve this in ASO cubes.

 

First of all, to refresh our memories, review the BSO out-of-the-box dynamic time series. Select Period dimension first, then click on the dynamic time series button.

From the period dimension, we see the available dynamic time series are:

  • YTD Year to date
  • HTD History to date
  • STD Season to date
  • QTD Quarter to date
  • PTD Period to date
  • MTD Month to date
  • WTD Week to date
  • DTD Day to date

If we push/sync data from BSO to ASO cubes and leverage the ASO aggregation ability, we can create the time series in ASO cube directly. Generally speaking, there are two things are involved.

  • A new dimension in ASO cube
  • MDX function

Create a new dimension first. It could be called, TimeSeries, View, Measure, etc. Then under the parent member, create PTD, QTD, and YTD members.

Then the MDX functions will come into the picture.

For most cases, the planning and budgeting process is on a monthly basis, so the PTD, period to date, will not need a formula.

For YTD, we can add the ASO member formula to it.

The formula would be,

SUM(PeriodsToDate([Period].Generations(1), [Period].CurrentMember), [TimeSeries].[PTD])

Similarly, for QTD, we can add a member formula to it as well.

The formula would be,

SUM(PeriodsToDate([Period].Generations(2), [Period].CurrentMember), [TimeSeries].[PTD])

After refreshing the database and syncing data between BSO and ASO, we are able to check the data from the Smart View/web forms, reports, etc.

Using Smart View as an example, we can see when selecting YTD, the data from Jan to Jun is accrued.

Hope this post gives you some ideas on leveraging ASO cube to report QTD, YTD, etc. See you next time.

 

 

2 Comments on “EPM Lab – ASO Time Series Reporting”

  1. Hello, I liked your post but I was able to figure out a way to do it in the Period Dimension using shared members. It’s not as as clean because the ASO application requires you to keep shared members in the same order but it works for QTD and YTD but you need to create a member for each QTD(month) and YTD(month) calculation

  2. Hi, how do you map the newly created TimeSeries dimension in ASO to the BSO cube in Data Maps?

Leave a Reply