7 Replies Latest reply on May 31, 2017 10:00 AM by Christian Berg

    Distinct Clause

    Selva Pandiyan

      Hi All

       

      OBIEE reports by default generates the physical query with DiSTINCT CLAUSE.

      Could we suppress teh Distinct clause?

       

      Kindly throw some light on the implications on the reports by suppression of distinct clause.

       

      Please help

       

      Thanks

      Selva

        • 1. Re: Distinct Clause
          Gianni Ceresa

          Hi,

          What do you mean by:

          Selva Pandiyan wrote:

           

          OBIEE reports by default generates the physical query with DiSTINCT CLAUSE.

          You mean when you make a query on attributes only?

          What's your use case? Because you can definitely avoid the effects of a "DISTINCT", depends on what you try to do exactly ...

          • 2. Re: Distinct Clause
            Selva Pandiyan

            Hi Gianni,

             

            Yes, When we select some set of Attributes only Report for our Legal Compliance.

            We lose some valuable information. so We want to suppress the Distinct clause.

             

            Thanks

            Selva

            • 3. Re: Distinct Clause
              Gianni Ceresa

              As the idea of the attribute is to be unique (based on the logical keys you defined), if you add the logical key to your analysis you will get the correct level of granularity. The distinct will still be there as that's how things are supposed to work (when using measures there is a group by, when only attributes it use the distinct) but you will get all your things.

              No need to show the column if you don't want it, you can hide or exclude it.

              • 4. Re: Distinct Clause
                Selva Pandiyan

                Hi Gianni,

                 

                We- OBIEE guys well understood the need of Distinct Clause.

                But how do I explain to the business users as they dont understand well.

                 

                Can we suppress the Distinct Clause or not?

                if yes, how do we do.

                 

                Thanks

                Selva

                • 5. Re: Distinct Clause
                  Christian Berg

                  Selva Pandiyan wrote:

                  But how do I explain to the business users as they dont understand well.

                  Precision and data quality. Analytics IS about these. Plus ganularities. Plus uniqueness. Not "semi-random data assembly".

                   

                  Selva Pandiyan wrote:

                   

                  Can we suppress the Distinct Clause or not?

                  if yes, how do we do.

                   

                  You're asking the wrong question: "SHOULD" you even do so and "WHY" should you or shouldn't you do so.

                   

                  As Gianni said granularity is there for a reason. If your data and/or models aren't well thought through and/or the usage then you have a bigger issue.

                  • 6. Re: Distinct Clause
                    Robert Angel

                    Hi,

                     

                     

                    I concur with my much more learned colleagues on the advisability of what you are trying to achieve, but on any subject area you can deem that any fact measure is implicitly (Implicit Fact Column) written into any query on that subject area that has no fact content. If you have a PK in the fact folder that will guarantee the granularity remains then create a non-aggregated measure based on that and use that measure as your implicit fact content.

                     

                    Hope this helps,

                     

                    Robert.

                    • 7. Re: Distinct Clause
                      Christian Berg

                      Good one as well!