3 Replies Latest reply on Sep 13, 2018 7:49 PM by Christian Berg

    RPD join - results are multiplied

    aPsikus

      Hi,

       

      Question related to RPD join setup.

       

      We have table with Employees Performance reviews per employee per year.

      It is joined with Dim_date: EXTRACT (YEAR from DimDate.ACTUALDATE)-1 = PerfReview.Rewiew Year.

       

      Now when we try to create simple analysis:

      Employee ID, Rating, Review Year

       

      each Rating is multiplied 360 times.

      If I will add Date filter, then I will receive single entry.

       

      What I have did wrong that it is multiplied? Or based on join that is expected behavior and it will be working correctly only with Date filter?

        • 1. Re: RPD join - results are multiplied
          Gianni Ceresa

          Multiplied by 360? Sounds like it's multiplied by every single day ...

          You are doing a join at a different level than the lowest granularity, so you have to model things right in the RPD telling the system how things are supposed to work.

           

          So the join and OBIEE are just doing exactly what you asked! And it's quite impossible to tell you what you wanted to do instead based on a single join description (not enough info provided).

          1 person found this helpful
          • 2. Re: RPD join - results are multiplied
            Thomas Dodds

            joining on EXTRACT (YEAR from DimDate.ACTUALDATE)-1 will still give you a row with a year number FOR EVERY detail row you have ... this is not the way to aggregate.

             

            You need a proper calendar dimension with proper logical hierarchies set and (as been said) tell the system how those work by setting the appropriate content levels in both the logical table sources of dimensions and facts.

            1 person found this helpful
            • 3. Re: RPD join - results are multiplied
              Christian Berg

              Amen to everything said before. Maybe have a look at what you are doing, how you are doing it and why you are doing it.