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 Calculated Items
Hello everyone,
Background - I'm currently working in Oracle Business Intelligence 12.2.1.2.0. I'm working at converting reports from Hyperion as it is near it's end of life. I'm rewriting hundreds of reports into OBI analyses. I don't have access to view RPD's and am working only in the presentation layer.
I'm running into a few problems as I try to mimic what had been done previously in Hyperion. Especially when it comes to computed items/column formulas/calculated measures.
For example - I have an employee/employee ID that has string values from a column/field. In Hyperion I could use the sum function and use the ID as a break point so I could essentially string "One, Two, Three, Four" together depending on how many values were linked to the employee. OBI doesn't have that option in the Sum function and CONCAT states that it is only for combining two strings. Is there a way for me to CONCAT strings and/or numbers by unique ID?
Also I have a lot of custom formulas where I look at string values and assign them numbers. I do this via CASE statements typically. I then have a need in a new column to sum those numbers. For example below - the case statement is displaying the correct numbers in the column on the left. I am able to then sum those numbers correctly (25) using sum in the reports tab via aggregation rule (see second picture). I then have a computed item that references that same case statement on the right. However there is some aggregation that is happening causing my numbers to duplicate depending on the scenario. In this case instead of the number being 25 the record is somehow aggregating four times and shows 100. Is there a way for me to ignore the aggregation ONLY for this measure or is there a way for me to reference the sum value (25) from the "Total" row and use it again in other formulas?
I'll take any advice and best practice tips I can get as I'm learning OBI on my own without any real training from my organization. Thanks for any help you can give me or any resources that people can point me in the direction of that help users like me in converting Hyperion queries.
Answers
-
HI,
Temoryian, this is a hell of a piece of work to come to OBIEE as a newbie to!
I am curious on 'Hyperion is coming to the end of its life' - you might want to get your organisation to look into PBCS https://www.oracle.com/uk/applications/performance-management/products/planning-budgeting-cloud.html - Hyperion lives on in the cloud, and even die hard fans of on premise are coming around to the cloud based solution now having functionality that on premise does not. That and paying for the standard solution will probably cost you less in the long run than trying to use another tool, not specialised around Hierarchical data in the same way, to achieve your ends.
All of that said, now to advice on what you have asked, if you are committed to continue to hammer nails with the screwdriver....
On concat of ids / numbers, sure, cast them to char, concat them, cast them back to number; -
cast(cast (Id as char)||cast(Id2 as char) as number) (untested)
With case you will find that if you can push your case statement into the rpd and do them as physical calculation rather than logical that the number weirdness stops.
Hope this helps?
0 -
Apart from what Robert told you....
Stop what you're doing and for crying out loud take about 20 steps back and think what you're doing there! that's the worst and wrongest approach you can conceivably take!
If you're a consultant then of course it's awesome because you can pretty much wring hours over billable hours out of the client....by doing the wrong thing.
0 -
Hi Robert,
Thanks for your replies. I would love for our organization to pick a different solution but unfortunately I think we are stuck going down this path. I will continue to try to influence them in a different direction.
As far as casting and then concatenating that would work for putting two ID's together but I'm trying to concat the values of the ID's by ID and not the ID's themselves. I never know how many values will be linked to the ID.
So essentially:
I need to write something to concatenate Hockey+Football+Basketball by ID 1 and Steak+Potatoes by ID 2. ID will always be a number and value will always be stored as a string.
0 -
Hi Christian,
Thank you for your reply. I've been trying to get everyone to look at a different solution. So far no luck.
0 -
Hi,
on your requirement the only way I can see that you could achieve this is by calling a database function written for the purpose. Not good, not performant, not standard, not recommended.
Could you use SUM ( YourMeasureHere by Id ) - to achieve your ends?
0 -
What you are describi g is basically LISTAGG in the database.
Can you function-ship this inside and EVALUATE?
0 -
My thinking on sum by was that it gives a single value for all of the values that he would concatenate - like you my thought was listagg - but then I looked again and wondered if this was missing his requirement (i.e. I ignored what he asked for and tried to guess what he was actually trying to achieve)
0 -
It dependa on how he actuall means that "+"-ing. SUMming? Concat/listagg? I just read it literally anf assumed *literally* what was written
0 -
Yes - the million dollar word in my comment to you was 'guess' - without the full picture that is all we can do, make educated guesses as to the requirement...
0 -
It's Friday afternoon - so "educated" might be pushing it X-D
0