4 Replies Latest reply on Aug 11, 2015 12:56 PM by ManishHathi

    Create a join in RPD between a DATE and DATETIME columns

    ManishHathi

      Hello,

       

      In my RPD, I have a fact table with a DATE column which also contains a time component. The physical layer shows this as a DATETIME column which I changed to DATE since I am only interested in the date part.

       

      When I join this to an  alias of my Date dimension, the physical sql generated is similar to:

       

      DIM_DATE.DATE = FACT_A.FINISH_DATE

       

      What I actually want is :

      DIM_DATE.DATE = TRUNC(FACT_A.FINISH_DATE)


      How can I achieve this at the RPD level? Yes, I can add a new column to the DW table with truncated value, but I want to know why even if I selected DATE as the datatype in the RPD, the join created does not do a trunc.


      Thanks,


      Manish

        • 1. Re: Create a join in RPD between a DATE and DATETIME columns
          rmoff

          The physical layer shows this as a DATETIME column which I changed to DATE since I am only interested in the date part.

          No. Bad idea. The physical layer of the RPD should show what is in the physical source. So if it's not a DATE, don't pretend to OBIEE that it is. For the exact reason that you're hitting now - you're expecting OBIEE to write some magic SQL to cast the datatype, without even telling it what the original type is. OBIEE won't query the metadata to check on data types in the source, it will rely on what you tell it in the physical layer.

           

          How can I achieve this at the RPD level?

          You could use an inline view in the physical layer and do your trunc there I guess. Better, as you've already identified, is to store the date as a date if that's going to be a key column on which to join.

          I want to know why even if I selected DATE as the datatype in the RPD, the join created does not do a trunc.

          See above. OBIEE does not know it's a DATETIME, so why would it do a TRUNC?

          • 2. Re: Create a join in RPD between a DATE and DATETIME columns
            ManishHathi

            Valid points Robin as always. I have already initiated action to have a truncated date field added to the table.

             

            But...I don't get your point that OBIEE doesn't know it's DATETIME....but it does...since the RPD shows it's data type as DATETIME by default AND it allows me to change it to DATE. From a users perspective (my), doesn't it mean that a data type conversion is implied by my action of changing DATETIME to DATE? That is why I was expecting a trunc.

             

            Thanks.

            • 3. Re: Create a join in RPD between a DATE and DATETIME columns
              Gianni Ceresa

              OBIEE doesn't know it's DATETIME because you changed it to DATE. The type of the column in the physical layer is not a transformation parameter (you have datetime in the DB and you want a date in OBIEE), it's a definition parameter: you tell OBIEE that the column is DATE. This parameter tell OBIEE what it can do or not on that column and what will you be allowed to do or not with the column, but it doesn't generate any implicit transformation (the expected TRUNC you didn't get) on the column.

              So when you change the type from DATETIME to DATE you are "cheating" telling OBIEE a wrong information, but you aren't asking the tool to transform your DATETIME into a DATE. You are just changing the behavior of functions and available operations on the columns and it's up to you, the user, to know what you are doing.

               

              And you see the impact of your cheat: you did a join between 2 columns defined as DATE and OBIEE didn't complained at all, but the behavior is not what you expect because for OBIEE 2 date columns are just matched using the = operator . But you gave a wrong information to OBIEE, so your match almost never works because one of the 2 columns contains time information.

              1 person found this helpful
              • 4. Re: Create a join in RPD between a DATE and DATETIME columns
                ManishHathi

                Ah... the light bulb went on!

                Now that you mention it, I realized that unlike the Oracle data dictionary, OBIEE does not remember that it's a DATETIME field. So when I changed to DATE, it said "ok date it is" and went it's merry way.

                 

                Thanks for clarifying.