Hi All, Good Morning!I am working on Cognos Analytics 11.1R5. We are working on a Data Set POC and created the data sets and loaded the data from the source data module. The main data set has around 16500000 records and took the time around 1 hour mins to load the data. Then we created a final data module with only data sets for creating the reports. The developed report has two filters. The first one is time period filter which filters the two years data and the second one is category which filters the particular category. The report is taking around 6 minutes of time for first time and it is very fast (5 seconds) in the subsequent runs. The same time (around 6 mins) the report is taking to run after cognos server restart every time. It is taking less time taking in the subsequent runs. Could you please let us know if this is expected behavior? Any suggestions?




------------------------------
Ravindra Voggu
------------------------------

#CognosAnalyticswithWatson

Yes, we also have Datasets performance issues in 11.1.6. Even Data cache property didn't help.  It's mentioned in CA 11.1.7 reelase that Dataset performance is improved in CA 11.1.7. 

You can try to make smaller datasets to use on reports or look at CPU usage when report is run first time to see there is enough memory to process requests.

------------------------------
Buddhi Gupta
------------------------------

Hi Ravindra,
Here is some additional info that will help to explain what you're observing:

• Data sets are processed on the Cognos Analytics server. 
• The underlying technology is a Parquet file.
• The queries are processed by a separate java process.
• The queries and results are cached for a duration of 12 hrs. Upto 2,000 entries will be kept, using at most 2 GBytes of disk storage.
• On restart the whole cache is cleared out.
• In 11.1.7 improvements were made for loading the data set, the performance to access/query the data sets did not change

// Henk

------------------------------
HENK CAZEMIER
------------------------------

Thanks @HENK CAZEMIER for details on changes! Does it mean if we bring all required columns in single query on report and use sub queries for each report layout components, will it be better than running individual small queries against same datasets since load performance is improved?​

------------------------------
Buddhi Gupta
------------------------------

Original Message:
Sent: Fri August 21, 2020 12:31 AM
From: HENK CAZEMIER
Subject: Data Set Report is running slow for first time

Hi Ravindra,
Here is some additional info that will help to explain what you're observing:

• Data sets are processed on the Cognos Analytics server. 
• The underlying technology is a Parquet file.
• The queries are processed by a separate java process.
• The queries and results are cached for a duration of 12 hrs. Upto 2,000 entries will be kept, using at most 2 GBytes of disk storage.
• On restart the whole cache is cleared out.
• In 11.1.7 improvements were made for loading the data set, the performance to access/query the data sets did not change

// Henk

------------------------------
HENK CAZEMIER
------------------------------

Hi Buddhi,
The loading of the data set that I'm referring to is the phase when the query is posed against its sources (databases, uploaded files, other datasets) and is used to create a resultset that is loaded into the data set. This step is initiated from the <save and load> action in the data set editor or the <refresh> action from the content explorer window.

At execution time the data set is accessed by the queries. Making multiple queries with fewer columns is likely going to perform better than making a wider query that needs to be re-iterated via a scroll cursor. I have not measured this particular aspect, but just made from observations. It will likely be sensitive to the data in the data set as well. It might be best to try out a couple of scenarios and see how it behaves in your application environment.
//Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Fri August 21, 2020 11:09 AM
From: Buddhi Gupta
Subject: Data Set Report is running slow for first time

Thanks @HENK CAZEMIER for details on changes! Does it mean if we bring all required columns in single query on report and use sub queries for each report layout components, will it be better than running individual small queries against same datasets since load performance is improved?​

------------------------------
Buddhi Gupta
------------------------------


Original Message:
Sent: Fri August 21, 2020 12:31 AM
From: HENK CAZEMIER
Subject: Data Set Report is running slow for first time

Hi Ravindra,
Here is some additional info that will help to explain what you're observing:

• Data sets are processed on the Cognos Analytics server. 
• The underlying technology is a Parquet file.
• The queries are processed by a separate java process.
• The queries and results are cached for a duration of 12 hrs. Upto 2,000 entries will be kept, using at most 2 GBytes of disk storage.
• On restart the whole cache is cleared out.
• In 11.1.7 improvements were made for loading the data set, the performance to access/query the data sets did not change

// Henk

------------------------------
HENK CAZEMIER