Report Performance Improvement

[pakhi]

Hi Team - I have one list report with 56 columns and it is taking around 13 minutes to extract 64000 records in excel while underline report query is completing in just 1 minutes. I tried some option to improve report performance in excel but it did not work.

Please suggest something which I can implement to improve the performance.

Cognos Version : 10.2.2
Data Base        :  SAP HANA

Package is published in DQM mode.

[dougp]

I have some users who routinely run a report that produces 185,000 rows of 208 columns and output to Excel or CSV format.  The query runs on the database for about 30-45 seconds.  The report will often fail at 30 minutes because I have set a maximum execution time.  During that time, the report will consume up to 30 GB of drive space in the Temp folder.  I think the problem is in the way Cognos compiles the output for those formats.

I have tried, to no avail, to encourage my users to use a different method to produce this type of output.  I have explained that Cognos is not the only reporting product and is certainly not the best for this use case.  The fear learning to use additional tools (like Excel).

Good luck!

[BigChris]

I'm curious...what are they doing with 185,000 rows of data? I'm guessing they're filtering it to find the subset of the data that they're actually interested in.

[hespora]

185k rows is not even that large to work with... I routinely use similar sized datasets (albeit with much fewer columns); pricing simulations are a common application. As in: customer or customer group is entering into pricing negotiations, and we need to analyze how whatever my sales guys want to do in terms of new pricing, discount, rebate schemes will affect the existing business. For that to be accurate, I run past sales for said customer or group on lowest possible granularity.

[Invisi]

The data set in itself is rather small. Just dumping this many rows with Cognos is a lot. This sounds like an intermediate result. For me, either you produce the end result in Cognos, or you don't bother with Cognos and put your analysis tool directly on your data mart, data lake or whatever data structure you have connected to Cognos.

[hespora]

Oh hell yes, but try to even find someone who will listen to that idea in a large concern where you, the guy needing the data, are removed so far from the guys managing the system, both geographically and organizationally, that you've never even met. Reality unfortunately is, it's just not worth the hassle.

[Invisi]

That's why I refuse to work for such organisations. If they want to be [^*$#], they can do it without me. There is enough work out there. I now work for a company that operates worldwide, but it's small enough for me to walk to top managers here in the office. And they usually listen.  8)

[CognosPaul]

Cognos is not an ETL tool, and shouldn't be used as one.

The 1 minute query execution time is not a valid comparison as all that's doing is taking a resultset from database A and dumping it into client B. I strongly doubt that the resultset instantly appears after 1 minute, even a client designed to handle large amounts of data still needs some processing time to draw the results into the grid.

Let's talk about the results.

56 columns * 64,000 records == 3,584,000 unique cells. Each cell has to be processed, formatted, and drawn. Cognos handles this by creating a temporary file on the server containing the query output. It then starts parsing through that file converting the results into an XML file. That file is then compressed. Unless you're talking about the old version of the Excel output, which is really a renamed uncompressed MHT file.

3.5 million cells will take a long time to process. Even at 1,000 cells a second, we're talking about an hour of processing. Considering it's being completed in 13 minutes, that means Cognos is processing 4,595 cells a second. Probably more because that's not including the time it takes to compress and deliver the report over the network.

An ETL tool doesn't need to draw the output into a specific format, it can work with the dataset as it stands, which is why it's so much faster.

[pakhi]

Thanks for the explanation. Could you please provide me any link on IBM support which explain the internal mechanism for converting the output from HTML --> XML --> XLS ? I can then explain to customer that it is taking time while parsing XML file to Excel format.

[bdbits]

Cognos is not an ETL tool, and shouldn't be used as one.

The 1 minute query execution time is not a valid comparison as all that's doing is taking a resultset from database A and dumping it into client B. I strongly doubt that the resultset instantly appears after 1 minute, even a client designed to handle large amounts of data still needs some processing time to draw the results into the grid.

Let's talk about the results.

56 columns * 64,000 records == 3,584,000 unique cells. Each cell has to be processed, formatted, and drawn. Cognos handles this by creating a temporary file on the server containing the query output. It then starts parsing through that file converting the results into an XML file. That file is then compressed. Unless you're talking about the old version of the Excel output, which is really a renamed uncompressed MHT file.

3.5 million cells will take a long time to process. Even at 1,000 cells a second, we're talking about an hour of processing. Considering it's being completed in 13 minutes, that means Cognos is processing 4,595 cells a second. Probably more because that's not including the time it takes to compress and deliver the report over the network.

An ETL tool doesn't need to draw the output into a specific format, it can work with the dataset as it stands, which is why it's so much faster.

Another succinct yet quoteable post from the master.  8)