13 Replies Latest reply on Sep 8, 2018 7:12 AM by Christian Berg

    OBIEE Calculated Items

    Temoryian

      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.

        • 1. Re: OBIEE Calculated Items
          Robert Angel

          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?

          • 2. Re: OBIEE Calculated Items
            Christian Berg

            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.

            • 3. Re: OBIEE Calculated Items
              Temoryian

              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.

              • 4. Re: OBIEE Calculated Items
                Temoryian

                Hi Christian,

                 

                Thank you for your reply.  I've been trying to get everyone to look at a different solution.  So far no luck. 

                • 5. Re: OBIEE Calculated Items
                  Robert Angel

                  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?

                  • 6. Re: OBIEE Calculated Items
                    Christian Berg

                    What you are describi g is basically LISTAGG in the database.

                     

                    Can you function-ship this inside and EVALUATE?

                    • 7. Re: OBIEE Calculated Items
                      Robert Angel

                      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)

                      • 8. Re: OBIEE Calculated Items
                        Christian Berg

                        It dependa on how he actuall means that "+"-ing. SUMming? Concat/listagg? I just read it literally anf assumed *literally* what was written

                        • 9. Re: OBIEE Calculated Items
                          Robert Angel

                          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...

                          • 10. Re: OBIEE Calculated Items
                            Christian Berg

                            It's Friday afternoon - so "educated" might be pushing it X-D

                            • 12. Re: OBIEE Calculated Items
                              Temoryian

                              Hi Guys,

                               

                              Thank goodness it is Friday afternoon.  I have two separate issues here:

                               

                              1. CONCAT strings by ID

                              2. SUM displayed values from CASE statement and place that value in new column so I can reference that number in another formula.  In the first of two examples above (sorry if I wasn't clear enough about this) I need the column on the right to sum to 25 instead of 100 like it currently is doing.  Somewhere there is some aggregation happening that I don't want to happen causing 25 to aggregate to 100.  To make this even more complicated is that I need to do about five calculations each of which is dependent on the calculation that occurs before it. 

                               

                              Ignoring the two issues above - if I have to do something like this is it always better to do this in the RPD? 

                              • 13. Re: OBIEE Calculated Items
                                Christian Berg

                                Temoryian wrote:

                                 

                                Ignoring the two issues above - if I have to do something like this is it always better to do this in the RPD?

                                 

                                "Always" - well the answer is as always: it depends.

                                 

                                Normally it's better to do things in the RPD for a number of reasons but I will only name one:

                                 

                                Easy control over pre-aggregate and post-aggregate calculation logic. Will the formula get executed row-by-row in the data set before any aggregation happens? Or will the formula get executed AFTER the data set has come back and has been aggregated.