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

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 ...

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

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.

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

[Deleted User]

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.

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.

[Deleted User]

Good one as well!