cancel
Showing results for 
Search instead for 
Did you mean: 

BO4 - no distinct or group by in dimesnion only queries

former_member272336
Participant
0 Kudos

Hi,

We have an issue with large amount of rows being returned and would like to have the sql generated with a distinct operator or a group by to limit the result set.

e.g select distinct prod_num, parent_prod_num

  from products

or select pord_num, prod_name

   from porducts

group by  pord_num, prod_name

As I understand it the parameter DISTINCT VALUES should control this.

queris with measures are correctly getting group by clause inferred (ie.. objects haev agg function on them).

However dimension only querries have neither a group by or distinct.

We are using idt tool and have not set/changed this parameters so far.

Our example have a large dimesnion table product numbers and parent product numbers.  dimesnion table concerned has over 7 million such rows.

but only a few thousand distinct products.

Have an issue realted to a bug ADAPT01695593 saying problems over 1 million rows.

Would like to be able to haev sql generated with distinct operator.

unx universe using idt tool .

bo4 sp06.

Is this possible?

Is it a generic setting such that all queries have this or can we customise it such that obly queries not involving a mesaure have distinct operator.

Thanks

Philip

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Philip,

below are the options that you can use to get Distinct in your query, that should solve your problem

ADD DISTINCT keyword in the SQL expression of the object in IDT  ex:DISTINCT Table.CATEGORY

Use Custom Query script option on Query script viewer of Query panel and add DISTINCT explicitly in the query to get the required result

Create Derived Table or DB view with Select DISNTICT of required columns and use the objects from Derive Table/View in the report query

Thanks

Mallik

former_member272336
Participant
0 Kudos

Hi,

Thanks for advice.

Appreciate this would work but would mean multiple distincts would appear in sql - just want one distinct in the select statement as that enough if say

select distinct fld1, fld2  etc.

Would have hoped DISTINCT_VALUES with setting of DISTINCT would have caused it to be inferred.

Would ideally like to have this set at univers elevel.

Thanks

Former Member
0 Kudos

Hi Phil,

You can add and set the parameter DISTINCT_VALUES = DISTINCT in the data foundation properties>Parameters tab, instead of adding DISTINCT on all the fields that you may surely pull into the report query.

Thanks

Mallik

former_member272336
Participant
0 Kudos

Hi Malik,

This parameter works when have the option retrieve duplicate rows at query level unset.

Is its possible such that default behaviour is that this value is unchecked on reports?

Thanks

Philip

Former Member
0 Kudos

Yes Phil,

Uncheck "Retrieve Duplicate rows" at Query properties is the another option to enable DISTINCT only to the specific report query, but if you want the DISTINCT enable globally for all queries on universe earlier option suggested is the way to go.

Thanks

Mallik

former_member272336
Participant
0 Kudos

Thanks for update Malik

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Philip,

Try to create required objects with distinct in object definition as

for e.g. Product_num as select distinct (product_num) from ****************

            Product_name as select distinct (product_name) from **************** and use them in reports.