Wednesday, July 16, 2008

Time Functionality

Something I've worked on a number of times and even spoke about at the ODTUG Kaleidoscope conference is MDX and specifically how to use it to do time related calcs like period-to-date functions and Time Balancing. I've seen quite a few posts on the subject and figured it was worth putting up some examples here.

More recent releases of Essbase will do time balancing natively, but period-to-date is something you still have to work out. My preferred method of doing this is to add an analytic dimension to the database and set the formula in one of the calculated members. For those of you who aren't familiar with the term, an analytic dimension is one that you add to your database that has a single default load member, all other members are calculated. In the case of time functionality I usually call this dimension [View] and have a default member called [Periodic]. I will then have some other members as needed, like [Time Balance], [QTD], [YTD], etc.

For Example:


The period-to-date functionality is pretty easy using the PeriodsToDate MDX function, the key is remembering to wrap it in a SUM function because the PTD function returns a member set, not a value.

YTD:
SUM(PeriodsToDate([Time].Generations(1), [Time].CurrentMember), [View].[Per])

QTD is almost identical with a modification to the generation reference:
SUM(PeriodsToDate([Time].Generations(2), [Time].CurrentMember), [View].[Per])



Time Balancing is little more involved. First you need to flag the members you want time balanced with a User Defined Attribute (UDA). Once you have that set you have to decide what kind of time balancing you wan to do. If you just want to grab the last child member, that's fairly easy.

CASE WHEN IsUDA([Measures].CurrentMember, "TB") THEN IIF(IsLeaf([Time].CurrentMember), [View].[Per],
(ClosingPeriod ([Time].Levels(0), [Time].CurrentMember), [View].[Per]))
ELSE [View].[Per] END

If you want to show the last value with data, known as Time Balance Skip missing, then you would use something like this:

CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(Leaves([Time].CurrentMember),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE [View].[Per] END

Now you'll want to modify your QTD and YTD functions to work with your Time balancing

QTD
CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(Leaves([Time].CurrentMember),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE SUM(PeriodsToDate([Time].Generations(2), [Time].CurrentMember), [View].[Per]) END

YTD
CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(Leaves([Time].CurrentMember),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE SUM(PeriodsToDate([Time].Generations(1), [Time].CurrentMember), [View].[Per]) END


28 comments:

Jitenk said...

Can we do this if we have multiple facts...eg Gross Adds, Gross Deactications etc

Gary Crisci, Oracle Ace said...

Yes you can. The purpose of an Analytic dimension is that it crosses all members of other dimensions by using the default load member. So any fact you are on will be properly calculated for the PTD. This is why I feel this method is more efficient than having individual formulas to calculate time functionality for each member.

lubos said...

Are these calculations member formulas? I am just having troubles with ASO Time Balance and multiple time hierarchies.

Thank you

Gary Crisci, Oracle Ace said...

Yes, the calculations are member formulas in the ASO model.

suresh said...

Hi Gary,

The Time Balance functionality like TB Last functionality is very interesting.
It would be really helpful for all if you could give once example by considering Sample Basic database.

Regards,
Suresh.

Anonymous said...

Hi Gary,

Regarding your YTD calc with Time Balancing, I guess that is for UDA with TB Last. How do I calculate for TB First? Awesome article.

Thank you.

Gary Crisci, Oracle Ace said...

With regard to TB First, you would basically do the same thing. Add a UDA for "TB First", then in the MDX code, replace the Tail() function with the Head() function.

Anonymous said...

Gary,
did you ever do anything recreating the Time Balance/YTD functionality in an ASO cube where Years and Periods were combined?
we're struggling with the logic and I'd be interested if anyone has it.

Gary Crisci, Oracle Ace said...

I have done this, but it would be lengthy to write up. POst an email address and I will send you a copy of an ASO outline to review.

Dhaval said...

Hi Gary

I am using your YTD formula but it has slow performance , i was trying to use NONEMPYTUPLE, if you can spread some light how should i write NONEMPTYTUPLE in your mentioned formula will be greatly appreciated,

Thank you!
Don

MPL6161 said...

Gary,

I am using your YTD calculation and it works well on upper level members, however when I drill down on a customer dimention (100,000 + members)it gets hung up. I have used the NONEMPTYMEMBER and the retreive is much quicker however the YTD balances are not accurate for the dynamic hierarchies. For instance "NetSales" is comprised of "GrossSales" minus "Returns" - the YTD for "GrossSales" is calculating correctly, but since its skipping empty members and there are not returns everyday the "NetSales" for YTD is not factoring in the returns from prior days.

I changed the solve order to make YTD come after Account, however as I expected this did not change the result either.

Thanks.

Anonymous said...

Hi Gary, I would like to ask you a question about what you have mentionned above about the YTD function.
In facet, I would like to calculate a Year to date in a member of a demension which is not Dynamic or Accounts in a ASO cube so is this possible ?
Thank you for your reply

Gary Crisci, Oracle Ace said...

In facet, I would like to calculate a Year to date in a member of a demension which is not Dynamic or Accounts in a ASO cube so is this possible ?

It is not possible to have calculated members in stored hierarchies, so the only other way to calc is to leverage the outline.

So you can have alternate stored hierarchies in your time dim that define your YTD.

For example create an alternate hierarchy called JuneYTD with shared children members
Jan+
Feb+
Mar+
Apr+
May+
Jun+

Anonymous said...

Hi gary again,
Thank you for your reply yesterday.
I have only one little problem with one of your scripts:
I have a dimension Time Periods with this hierarchy :

Time Periods
--YearTotal(+)
----Total_Year(+)
------Jan(+)
------Feb(+)
------Mar(+)
------Apr(+)
------May(+)
------Jun(+)
------Jul(+)
------Aug(+)
------Sep(+)
------Oct(+)
------Nov(+)
------Dec(+)
----Quarters(+)
------Q1(+)
------Q2(+)
------Q3(+)
------Q4(+)
----Semesters(+)
------Sem1(+)
------Sem2(+)

And i would like to use your sum script to only calculate sum only from January to december so i wrote this : Sum(PeriodsToDate([Time Periods].Generations(4), [Dec]))
My problem is about generation tag Generations(4) because it will take i think all the elements in generation 4 of time periods including Q1 Q2 Q3 Q4 and Sem1 Sem2 so can you help me with this because i want only to have a sum of total year.
Thank you for your reply

ps: i thaught about using CHILDREN function 'http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_techref/techref.htm) but it doesen't work :(

Anonymous said...

Hi Gary,
I would love it if you could also share with me an example of how you can to QTD functionality in an ASO outline where Year and Time Periods are combined in one dimension. I really don't want to have to achieve this by manually creating shared members because that will be a maintenance nightmare. You can email your example to me at tmp1144@gmail.com
Thanks in advance!
Tamara

Drew said...

When I create the view dimension, I get a dataload error about loading to a derived cell. Are there additional settings beyond what I see in the screenshot that need to be made, e.g., sparse/dense, multiple hierarchies, etc.

Ija said...

Hi, Gary,

Maybe you can help me out. I need to be able in Financial Reports to select Yesterday as my POV and run the batches automatically. To achieve this I believe I need to create Yesterday dynamic Calculation member in Essbase . My YearTotal dimension looks like:

TotalYear
Q1+
Jan+(parent; below are level 0 members)
Jan1+
Jan 2+
Jan 3+
Jan 4+

How do I write a formula to get Yesterday?

If today is January 5, then I want my report would run for Jan 4.
I would appreciate any ideas or recommendations.

Thank you,
Ija

|Gary said...

Hi Gary,

We have scenario were we are trying to implement transparent partition with BSO & ASO Cube.

As BSO supports DTS, so we have activated DTS & for ASO we used MDX scripts for DTS.

The requirement is to map BSO & ASO using transparent partition & we should see the impact of DTS.

Please suggest the best approach.

Anonymous said...

Hi Gary,

Great post, this helped us out tremendously. Do you happen to know what you do for Inception-to-Date?

Thanks again!
Mike

AlanD said...

Hi Gary,

Thanks for all the useful information you post on this blog... you've reduced the rate at which I pull my hair out.

I would like to second the question above on Inception to Date...

- we have separate Year & Period dimensions;

Year dimension
- 2010
- 2011
- 2012

Period dimension
- YearTotal
- Q1
- Jan
- Feb
- Mar
- Q2
- Apr
- etc

I have also taken your advice and set up a View dimension, with 'Month' as the single stored member, and dynamic members for YTD, QTD, etc.

The ASO cube in question is designed to report on capital projects which can span more than one year....
I've set up an ITD (Inception to Date) View member, but cannot get the member formula to span across years.

Do you have any thoughts on how to approach this?

Cheers,
Alan

AlanD said...

Hi Gary,

No need to respond to the above, I think I have a solution...

1) create one PeriodsToDate statement on the Year dimension for prior years

2)create a second one on the Period dimension for the periods in the current year.

3)add them together.

Not sure if it's the most efficient solution, but it works.

I have the code if anyone wants it, but be warned that this is my first day using MDX.....

Cheers,
Alan

Gary Crisci, Oracle Ace said...

A simple way to achieve the HTD calc is to use the TupleRange() function.
It is easiest if you hard code the starting points. So if your database starts with Fiscal year 2010 and you run a typical Jan - Dec Time dim, your HTD formula would look like this
SUM(TupleRange(([FY2010], [Jan]), ([Year].CurrentMember, [Time].CurrentMember)), [View].[Per])

I will say that like anything else with MDX the size of your query is going to have a huge impact on your retrieval time. If HTD is a requirement for your database, I would argue that it would make the most sense to merge Periods and years into one dimension. Nonetheless, the above will work.
Regards,
Gary

Guillermuco said...

Hi Gary

I notice that using the sum function the performance is very bad if you compare agaist the MTD value where it is store the data. Instead using IFF and checking the month. I write it not as MDX language
If (Jan) then Jan
If (Feb) then (Feb+Jan)
.....

Do you notice a bad performance using sum when you start to use more members in your query and not the parent members?

Anonymous said...

Hello Gary,
I have been looking for a version of your YTD formulas for an ASO cube with Years and Periods combined with no luck. do you have an example you could send to me?

Tahnks,
Kenneth
kmsmisc@gmail.com

Raju said...

Gary,
did you ever do anything recreating the Time Balance/YTD/QTD functionality in an ASO cube where Years and Periods were combined?
We have a the Time dim as
FY2012
->FY2012-Q1
->FY2012-Q1-Jan
->FY2012-Jan-W1
->FY2012-Q1-Day1
->2012-01-01
we're struggling with the logic and I'd be interested if anyone has it.

Raju said...

Gary,
did you ever do anything recreating the Time Balance/YTD functionality in an ASO cube where Years and Periods were combined?
We have the Time dimension as
FY2012
->FY2012-Q1
->FY2012-Jan
->FY2012-Jan-W1
->FY2012-Q1-Day1
->2012-01-01

we're struggling with the logic and I'd be interested if anyone has it.

Nicolaas Willemse said...

Excellent post. Thanks so much!

Marco said...

Hi Gary.

I am trying to make the TB_Last calculation to work but I am hitting a snag. Your code uses a tuple:

(
[View].[Periodic],
Tail(
Filter(
Leaves([Time].CurrentMember),
Not IsEmpty([View].[Periodic])
)
).Item(0).Item(0)
)

This is not working on our environment. when we try to use this part of the code, the whole formula breaks (does not return anything.)

If you could reply to my email: Marco.Rossodivita@perficient.com and I'll give you more details of the issue.

thanks in advance.

Marco A. Rossodivita