10 Replies Latest reply: Feb 25, 2013 12:36 AM by Dhar RSS

    Using lookup tables concept with essbase data source

    sayak
      Hi,

      I am trying to achieve " display text data from Essbase Cube in OBIEE report " (opened thread on this : [nQSError: 46036] Internal Assertion: Condition pLeftOperand in obiee 11g
      I was trying with creating initialization blocks and session variables in the past . It Didn't work , so now trying with look up tables .

      Referring this :

      http://www.adivaconsulting.com/adiva-blog/item/30-lookup-tables.html

      lookup function :

      LOOKUP
      (
      SPARSE/DENSE
      #Lookup Value column from the Lookup Table#,
      #Default Value if there is no lookup value in the Lookup table# (only needed for SPARSE lookups),
      #Primary key columns from the main table
      )

      But , in my case , "primary key columns from main table " is my fact columns . So , I am creating the lookup columns under my fact table itself . After doing so , Fact table columns are getting hanged in answers (there's no consistency error's in RPD though) .

      Anything I am missing here ?
        • 1. Re: Using lookup tables concept with essbase data source
          sayak
          Hi all ,

          Waiting for your responses on this !!!
          • 2. Re: Using lookup tables concept with essbase data source
            Dhar
            Hi Sayak,

            Let me know if I got you right, that you have a Priority as an account member in Essbase with the members 0,1,2 which you are trying to report as 'LOW', 'MEDIUM' and 'HIGH' through OBIEE.

            Ok, If it is so, here are some pointers

            1. If I go by your example, I still cannot see PRIORITY as a measure, instead I would consider this as a Dimension (might well be an Attribute Dimension to be precise)

            2. For some reasons, if this is still a measure here are some steps what you could do (I could not test these though, but hope you can get it right when you run through them)

            Import your Essbase outline to OBIEE and your relational lookup table too in the physical layer..

            Convert the Essbase measure dimension to flat measures in physical layer. Yes, just because you got to have a lookup for this priority measure exclusively and I do not think we can do it, with the account still as a hierarchy in OBIEE.

            Once, you pull the Essbase cube into BMM, you could see the Fact table for the Essbase cube with this priority measure.

            Create the lookup logical table too in BMM, using this physical lookup table.

            Now for the Priority measure in Essbase's Cube Fact table, you could use the Lookup function as below

            LOOKUP
            (
            SPARSE/DENSE
            #Logical Lookup table's description column here #,
            #Essbase's Priority Measure
            )

            Just give it a try, I am sure you will find a way through. Hope this helps.

            Thank you,
            Dhar
            • 3. Re: Using lookup tables concept with essbase data source
              sayak
              Thanks for replying !

              I tried with :
              LOOKUP
              (
              SPARSE/DENSE
              #Logical Lookup table's description column here #,
              #Essbase's Priority Measure
              )

              While draging the new lookup column , its throwing below error :

              *State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 42045] Lookup functions cannot be nested with aggregations. (HY000)*
              • 4. Re: Using lookup tables concept with essbase data source
                Dhar
                Hi,

                Did you try setting the Aggregation Rule for the said measure to 'None'.

                Thank you,
                Dhar
                • 5. Re: Using lookup tables concept with essbase data source
                  sayak
                  Yes , after changing it to "none" its converting the measure into a dimension column and throwing below error :

                  Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
                  *State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 46008] Internal error: File server\Query\Optimizer\SmartScheduler\PhysicalRequestGenerator\Src\SQOSPMarkMultiTargetSupport.cpp, line 1349. (HY000)*
                  • 6. Re: Using lookup tables concept with essbase data source
                    Dhar
                    Hi,

                    Couple of questions here.

                    1. What version of OBIEE are you running on? Did you try the latest .8? If not, just try and see if this goes through. I could see an internal server error here, which I think mostly is a bug.

                    2. How about having just this measure in the report, and not combining with any other columns? Does it show up atleast then?

                    3. Did you try playing around with the column, by settting the aggregation rule in the formula section to none or something like that. I see something, these internal errors are due to such configurations.

                    4. At the last, any chance with the support guys?

                    Hope this helps.

                    Thank you,
                    Dhar
                    • 7. Re: Using lookup tables concept with essbase data source
                      sayak
                      Many thanks again for sharing your valuable thoughts !!


                      1. I'm in 11.1.1.6.2 , will try to find out , whether it's a bug .

                      2. Yes , it is showing up numeric values .

                      3. I tried , but the error remains .

                      4. Not yet . But yes , last option would be raising a SR on this .
                      • 8. Re: Using lookup tables concept with essbase data source
                        Dhar
                        Hi,

                        Probably try upgrading your system to see, if this issue is resolved in .8.

                        Also as per your second point, it is showing up numeric values. Is it what was expected? If not, can you enable level 5 log and see the queries being fired. One should be for essbase, the other should be for relational db and OBIEE does not the mapping internally.

                        Thank you,
                        Dhar
                        • 9. Re: Using lookup tables concept with essbase data source
                          sayak
                          Apologies !

                          2. While selecting 'none' in the measure aggregation rule , Its throwing the same internal error .

                          I'm keeping ' upgrading server to .8 /rasing SR ' as the last available solution , however , it would be useful if , this can be achieved by a different approach .
                          • 10. Re: Using lookup tables concept with essbase data source
                            Dhar
                            Hi,

                            Any update from Oracle Folks or on the upgrade? I am thinking, if we should be able to resolve this issue even by horizontal federation. How about giving a try with some steps at How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?

                            Hope this helps.

                            Thank you,
                            Dhar