Where does your inflation rate come from? Do you expect to enter it by hand every time on the screen when running a report or is it stored in a table in your DB ?
If it is in your DB the best solution is to import that table in your RPD and model into your current model.
You can get the inflation rate next to your facts by using a LTS or also model it as a lookup (as it's a fixed rate per year).
In that way you can create 2 versions of your measures:
- normal measures
- measures adjusted with inflation rate
As you can see it looks like a simple things to do in words, so now it depends on how does your model looks like ...
Thank you Gianni, inflation rates are in a HFM application as members in a hierarchy in the accounts dimension. Also I'm using Essbase Analytics Link to use an Essbase cube for the integration with OBIEE.
So when I import metada into the RPD, EAL generates only one measure and the accounts dimension is a "normal" dimension. So in the RPD I can't transform the accounts to a measure dimension.
I'm thinking to create a transparent partition of the EAL cube and create a member in the currency dimension calculating the inflation of the accounts I need, but don't know if it's gonna work.
Ah! Now you make it interesting: HFM and Essbase
That unique and single measure your see for your cube is a "fake" measure and and you say your accounts are 1 standard dimension in OBIEE.
The good news is that you can make it "flat": every single member of the dimension will be a real measure in OBIEE.
An interesting point to keep in mind: it's not mandatory to make the accounts dimension flat, sometimes it can be more interesting to use the "scenario" dimension or any other.
This is an example of how will your cube looks like in the physical layer with a "flat" hierarchy.
Have a look at this blog for some details about "flatten" the accounts dimension: Rittman Mead Consulting » Incremental Essbase Metadata Imports Now Possible with OBIEE 11g
Thank you very much Gianni for your help, but how can I dynamically calculate this value for every account? That's where I'm lost.
For example: I have the Account A and we are in March 2015. I want to compare it with data to March 2014 and March 2013. The way it occurs to me is to create a rate from January to March for 2014 and other for 2013. But doing that means that I will have to create 12 rates per year and that is not a viable solution, because I will have to create manually the rates of every month for each year.
If you have a method in mind it will help a lot.
1 person found this helpful
Can you get your inflation rate into Essbase as well? (I guess you can ....)
Can you "customise" the Essbase cube by hand? In Essbase (I don't know if you have a ASO or BSO but it's possible for both) you can have a measure like inflation rate at the year level and then add another measure with a formula taking the yearly inflation and applying that one to your monthly measures. So you can solve your problem just with Essbase by using a formula there.
Or I would also say you can do it in OBIEE, using content level you can have measures at different granularity and use them together (so in OBIEE you can define that a measure exist only at the year level and you ask the tool to simply replicate it for any level under the year (so quarter or month).
So up to you to see how to do it.
I guess that, if possible, doing it in Essbase would be better so can have the same data if you link directly Essbase to Excel via SmartView (or use it with another reporting tool).
Doing it in OBIEE is possible as well, because the business layer (where you would do the calculation in my mind) is independent from physical database it must work for Essbase as well (but it require to be tested to be 100% sure and I don't have a working Essbase right now that I can try).
Yes, I will try in essbase (BSO), the thing is that I have to do a transparent partition, because it's not recommended to modify the EAL cube. But I think it's possible so I will test this ideas now and let you know how it goes.
For the other hand, I dont' know how to do the calculation when the year aren't consecutive, for example if I want to compare 2014 with 2012 or 2010 with 2006.
The question about the calculation on how to compare years is related to OBIEE?
You have many ways to do it, you can select both years (2014 and 2012) and just filter your data on these 2 years and use a pivot to display 2014 next to 2012 so you can see what changed. You can also use AGO function (need to define a dimension as time dimension in OBIEE) to get 2012 is a column next to the 2014 data.
Yes I'm using AGO function to compare years in OBIEE, but I have to apply the ratio to the past year. That's where I'm stucked
It depends how your ratio is in OBIEE. If you added it in Essbase and create a measure where you directly apply it you just need to use AGO on that measure.
If you model it in the BMM layer you can create a logical column there being already the result of the measure after applying the inflation ratio, in that way AGO will automatically retrieve the correct value.
If you just have the inflation rate by itself in BMM you can define a neutral aggregation rule (like AVG) and in your formula you do something like: AGO(measure) * AGO(inflation) [of course that one isn't the correct syntax of AGO].
As you many IF, it really depend on your BI model / Essbase app.