7 Replies Latest reply on Nov 13, 2015 11:56 AM by Christian Berg

    OBIEE 12c. Incorrect conversion of Metadata (INT)

    Frog Toad

      Hi!

       

      I used OLAP-cube darasource in repository.

       

      In my dimension exists many columns with type "INT" - it's ID-values. They contain only integer values. Example: column "EventID"

       

      In repository, in Physical-level, this column exported to type of "VARCHAR".

      I change type of the column on Physical-level on "INT", create dimension with this column on Business-level and Presentation-level

      Next i publish a repository and check this column in web-analytics

      In analytics, i get error:

      Odbc driver returned an error (SQLExecDirectW).

      Error codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

      ...

      State: HY000. Code: 64206. [nQSError: 64206] Column "KEY0#154Anc": The value "2.2E1"of type "xsd:double" from the XMLA backend cannot be converted. (HY000)

      And so with any integer column

       

      I checked this column - column contains only integer values.

      I change type of the column on Physical-level on "DOUBLE" and checked - error disappeared.

      I change type of the column on Physical-level on "VARCHAR" and checked - column contains only integer values: 1, 2.. 10, 100.. (yes, now this values is text)

       

      Why the columns incorrectly converted to "INT"?

      How can I fix this?

        • 1. Re: OBIEE 12c. Incorrect conversion of Metadata (INT)
          Christian Berg

          That's because they're DOUBLEs and not INTEGERs in terms of the OBIEE RPD. An INT is explicitly a 32bit signed integer.


          Ref. https://en.wikipedia.org/wiki/2147483647_%28number%29

          Ref. Gangnam Style overflows INT_MAX, forces YouTube to go 64-bit | Ars Technica

          • 2. Re: OBIEE 12c. Incorrect conversion of Metadata (INT)
            Felipe_Idalgo

            HI,

             

            This may be ocurring due OBIEE cannot convert your values cause you're out of data range supported.

             

            INT - The INTEGER type is a signed binary integer data type occupying four bytes. The maximum value that can be represented is 2,147,483,647, and the minimum value is -2,147,483,648.

             

            DOUBLE -

            The DOUBLE type is the same as the IEEE 754 64-bit double-precision binary floating-point data type. The internal storage is eight bytes. The significand occupies 53 bits (including the sign bit). Therefore, the precision is limited to approximately 16 decimal digits. The exponent occupies 11 bits. The range of the exponent is approximately ±307 as a base 10 decimal value.

            http://docs.oracle.com/cd/E28280_01/bi.1111/e10540/data_types.htm#BIEMG4605

            Please verify your data range

            Felipe Idalgo

            • 4. Re: OBIEE 12c. Incorrect conversion of Metadata (INT)
              Frog Toad
              Please verify your data range

              I checked it every time:

              In original source (ms sql) values of column INT-type - values in column only integer: 1, 2 ... 10000 (max value).

              10000 << 2147483647

              All values in columns > 0 and all integer-type. Not null


              The same problem with a different column - MonthNum. There have 12 values: 1, 2, 3 .. 12. Here also there is the same error - BI thinks that  column MonthNum have DOUBLE-values


              Why BI can't convert INTEGER-values to INT and thinks that is DOUBLE... i don't understand

               

              UPD. But, that's funny, column "Year", values: 2010-2020, convert to INT without problems. How it works...

              • 5. Re: OBIEE 12c. Incorrect conversion of Metadata (INT)
                Christian Berg

                From your own initial post

                 

                3043957 wrote:

                 

                Column "KEY0#154Anc": The value "2.2E1"of type "xsd:double" from the XMLA backend cannot be converted.

                 

                It's about how the data is queried and received from the XMLA source. Sometimes you just have to live with the peculiarities of one source or another.

                 

                Also: What's the big difference for you whether you declare them as INT or DOUBLE in the RPD?

                 

                As to this:

                3043957 wrote:


                Why BI can't convert INTEGER-values to INT and thinks that is DOUBLE... i don't understand

                Because OBI is source agnostic and the definitions in the RPD physical layer are built to serve the most common denomiator.

                • 6. Re: OBIEE 12c. Incorrect conversion of Metadata (INT)
                  Frog Toad

                  Christian Berg

                   

                  It's about how the data is queried and received from the XMLA source. Sometimes you just have to live with the peculiarities of one source or another.

                  Initially, AdministrationTool converts all data of my "INT"-columns to VARCHAR. AdministrationTool can make mistakes when determining the type of column, it is normal. I have to tell him what type need to use

                   

                  Also: What's the big difference for you whether you declare them as INT or DOUBLE in the RPD?

                  Usually - no difference, i have many of ID-columns, which are hidden from the ordinary user and are used for correct sorting of other columns. In this case, i do not care what it contains - "1.0, 2.0 .. 10.0" or "1, 2 .. 10"

                  But there is a columns that is displayed to the ordinary user, where the need is integer-numbers, for example: "MonthNum"

                  And I think that all columns should be strictly the type, as they were conceived, because each type of data has its own purpose.

                  Otherwise, can always use the LONGVARCHAR, but it's wrong and bad

                   

                  I see at least one method of solving this problem - create the new logical column in the Business Model and, using CAST, convert original VARCHAR-column to INT. But I think it "kludge" - incorrect solution

                  • 7. Re: OBIEE 12c. Incorrect conversion of Metadata (INT)
                    Christian Berg

                    I hear you but I do not see why DOUBLE wouldn't be perfectly ok. You can always format your "PresTable"."PresColumn" definition is OBIPS afterwards and display it without precision after the comma and retain a numeric field type for arithmetic operations.

                     

                    And yes, every datatype has its own purpose but inside OBI that purpose and definition may differ. Ex.: INT being a 32bit SIGNED integer. Other example: INTERVAL which is unknown as a functional data type to the OBI engine.