Removing Distinct SQL from framework manager

[mohnish45]

Hey guys,

I am trying to remove the distinct from the sql generated in the query studio. Do you know what setting I should change in the framework relational package to remove distinct from SQL?
I want to change this inorder to improve the performance of the sql.


Any help will be appreciated!!!

Thanks
Patel

[blom0344]

Add just 1 - fake or not - aggregated item to the query and the resulting group by will take care of duplicates..

[mohnish45]

Thanks for replying Blom,

Can you please explain in detail?  i want to make changes in the framework manager itself.  i dont see any option in the query properties in framework manager which shows option for aggregate.

thanks

[blom0344]

You can do this in framework manager , set the usage to fact and the regular aggregate to , say count. However, you can add a dataitem to the query itself using say value  1 for the definition and the aggregate to say count.

If you can't find the aggregate setting then use the help in FM or RS

[mohnish45]

It didnt work for me!!! Do you want forces distinct query in cognos framework and query studio?

[tjohnson3050]

You can just turn off auto group and sum inside query studio, this will remove the distinct from your generated sql:

http://pic.dhe.ibm.com/infocenter/cbi/v10r1m1/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.ug_cr_qstd.10.1.1.doc%2Ft_turn_off_automatic_summarization.html

[blom0344]

There is a little note in the article referenced which may make this rather tricky as the setting is linked to personal preferences. I do not understand why the addition of an aggregate would not solve your issue, though  :o

[velniaszs]

in framework manager go to needed data items and check in both database and business namespacess what type is the data itema (fact or attribute). Set it as attribute ( as i susspect it is a text), and set regular agregate, semi-agregate to unsupported. Hope that will help

[blom0344]

in framework manager go to needed data items and check in both database and business namespacess what type is the data itema (fact or attribute). Set it as attribute ( as i susspect it is a text), and set regular agregate, semi-agregate to unsupported. Hope that will help

No, I think it has little relevance.  A query WITHOUT an aggregate will add a distinct clause.  At report level you can deactivate autogroup and summarize which effectively removes the distinct. However, this is a setting.  Introducing an aggregate will also remove the distinct as an aggregate automatically returns unique rows (otherwise it would not be an aggregate) So, there is no FM setting that invokes the desired behavior

[mohnish45]

thank you guys, this is really helpful and all your help is highly appreciated

thank you once again

-mohnish