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
Posts
18 comments:
Can we do this if we have multiple facts...eg Gross Adds, Gross Deactications etc
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.
Are these calculations member formulas? I am just having troubles with ASO Time Balance and multiple time hierarchies.
Thank you
Yes, the calculations are member formulas in the ASO model.
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.
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.
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.
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.
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.
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
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.
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
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+
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 :(
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
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.
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
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.
Post a Comment