If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Turn Off Auto Summarise

Started by cognostechie, 10 Dec 2010 06:16:00 PM

Previous topic - Next topic

cognostechie

When we create a new Report, there is a property of the Query 'Auto Group & Summarise' which is turned on by default. It results in using distinct and group by functions which slows down the report. Same thing applies in Query Studio. Totals are not required for each report so I was wondering if there is any place where we can change the settings so that RS and AS do not turn on this property by default. 

blom0344

That does not make much sense. Turning auto-summarize off will treat all fetched dataitems as non-facts, generally causing much larger data-sets fetched from the database. It will also prohibit building crosstabs in both RS and AS. Actually , without facts AS will not work at all.
Normally Cognos adds the distinct clause when only identifiers and attributes are fetched (standard way to remode duplicates) The distinct implies ordering data, causing performance loss.  Aggregating data actually should improve performance (smaller set to be handled) Databases are 'weak' with sorting , but aggregating should be a breeze.
I suspect that you have another issue at hand..

cognostechie

I did not mention AS at all. I am only talking about RS and QS. In practical world, when you are dealing with a table having 50 million records and you want to make a report to show the detail data, Cognos works much faster (5 seconds vs 2 min) when you set the 'Auto Group & Summarise' property to 'No'. Try that in QS and RS. Reason - It brings the first 20 records on the 1st page instead of reading the entire table and summarising. In a lot of cases, duplicates are required in the report and Crosstabs are not needed. You can also put the filters in the report before putting the first data item and summarise the facts manually if needed instead of having Cognos do that automatically. So it all depends on the requirement.

I can manually do it every time I make a report but was wondering if it can be done by default.   

blom0344

Oh, please read your original post again..   8)
Again, a fact is a fact when an aggregate is set, otherwise you are indeed trying to read a table row by row. That is not what BI and BI-tools are about.  You can easily create a section in your model that is tuned for row-by-row analysis by treating everthing as either identifier/attribute.

Even if the required 'switch' would exist, you will still need to throw the switch for 'normal' BI reporting, won't you?

cognostechie

Ok, sorry, I mistyped AS in the original post, what I meant was QS.

Yes, reading row by row is perfectly fine because BI tools are used for detalied reporting also, not just BI analysis. You yourself said in another forum that everything is not according to the best practices defined. QS in particular is for ad-hoc queyring, not BI analysis. It is not mandatory to use facts in every query. Your experiences might have been different. I have used Cognos against CRM systems where you hardly have any facts to deal with.

The point here is to answer (if somebody has it) rather than challenging the question itself.

Like I said, I can manually turn that feature off and it does work fine so Cognos does allow to turn it off. That itself proves my approach is not bad.

Thanks for your views though.

blom0344

Yes, CRM certainly would quite the exception to the rule. However, making sure that usage properties are set properly CAN make the difference. Cognos has the nasty habit of setting usage to fact for all imported numeric fields. If you accomodate your model for detail analysis (identifiers/attributes) with the proper aggregate settings (=unsupported)than you should get non-aggregated data regardless of the setting you want to change