Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE 12c - how to calculate (and keep!) the value for the whole last year
Answers
-
Yes, no luck - of course 53 million euros looks nicer than 600 000. I tried this also so that I created a new view to db that calculates the year totals and joined that as another fact to my subject area. It works but it would cause major changes to my BMM if I finalized it properly (needs additional dimensions that I don't have now as the data is only "info" level). Then I tried to create a new column to my original fact and loaded data to it from the view, only to January - and then created cumulative calculation on top of that in OBI. This seems to be working and it's probably the easiest way. I'm loading balance sheets anyway with a procedure, couple of update clauses won't make it much slower.
0 -
You don't need a new view for this to work, the modelling I describe purely uses the existing measure in the business model layer, as I said just duplicate your existing measure, rename the copy in the BM and pin it to the dimension level - OBIEE does the rest!
0 -
Yes and I did that duplication/rename/pin - unfortunately it multiplies the data as I described. No idea why, duplication works perfectly in HR models and they are built exactly the same way as this one.
0 -
And if you access the formula for this measure is it just a physical table column or is there a formula in there?
If the latter can you post the formula please?
0 -
No matter if I use base measure (physical table column) or formula, result is wrong (tried both). Formula is normal todate(measure, level) aka todate(mymeasure, year) in this case.
0 -
In your position I would be; -
1. Going over the business model with a fine tooth comb, this is standard functionality, there must be something amiss in there - you checked all of the fact / dimension Logical Table Source levels - yes, and you checked all keys - yes, and you checked all joins - yes, that all dimensions in the query are joined to the fact and do have detail
1b. Constructing basic queries in answers and running them to ascertain if there are any other breaking points that give me a clue where the issue lies
2. Running the functionality I described (pinned at year) with logging ramped up so you can view the physical sql to see if it gives you any clues
3. Querying the physical data if possible to ascertain that all is right there when joined to your time dimension (and whichever other dimensions are present in your query)
0 -
That's the method I use too, put the process in pieces and test piece by piece. Usually people just get blind to their own work; I'm pretty sure that this is some tiny minor thing somewhere but I just can't find it! DB is ok, I'm 100% sure of it. Physical layer is ok, I get everything I expect to get. Base BMM is ok because all base metrics and analytics work just as they should. And then there is that one monster... I'll start to check the generated sql if it could tell me something new.
0 -
Have you checked the consistency of the keys on the time dimension with this only manifesting there.
Is your year dimension key as transparent as 2018 or similar?
Are all your chronological keys also set correctly?
If you are sure that your DB and physical are both 100% then I go back to use the physical sql generated to give you a good pointer on where the BM layer is wrong.
Do let me know how you get on / when you find the answer - I am heavily 'invested' in this now!
0 -
Solved this by calculating everything in the database. Basically I created the year sum on January on a new table column, imported the column to RPD, changed default aggregate to sum and then created a new OBI column on top of that. The new OBI column is normal cumulative calculation; todate(myNewDBColumn, year) and that shows the yearly sum correctly on the analysis.
0
