topic Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO in SAS Web Report Studio

PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

prashu — Wed, 07 May 2014 09:47:59 GMT

Hello All

I am facing a performance issue while working with sas web report studio,i have developed a report in SAS WRS in that when i am drilling down a single huge count it is giving me an error,

My Report in which i want to drill through value 90,882 and my physical sas data size is 2 GB.

Now when i am clicking that value it is giving me an error as shown below.

For the alternate solution, i have tried to retrieve these drill down through linking report concept but that is also not a problem solver for me it is working fine for smaller values but for bigger value it is also giving me an error.

So someone can help me out through this and provide me any solution for drilling down data, thank you.

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

Kurt_Bremser — Wed, 07 May 2014 10:58:53 GMT

Your drill through result is just too big to display.

You might find this helpful:

SAS(R) 9.2 Intelligence Platform: Application Server Administration Guide

Look at

Set Values in the Performance Tab of the Advanced Options Window

and

Maximum number of tuples in a set

But be careful, these limits are high by default.

I recommend doing a manual test on the data to get a feel about how many tuples the drill through should yield.

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

Maheshk — Mon, 16 Jun 2014 09:53:15 GMT

Hi Prasanth,

if the physical sas data contains 2GB of data then it is huge data to open in SAS WRS. firstly take required columns and apply filters on the data what you required in the Edit mode and then try to view the data. by the way, is it summarized data or detailed data?

Thanks,

Mahesh

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

LinusH — Mon, 16 Jun 2014 12:04:59 GMT

Just for clarification: are you accessing an OLAP cube by WRS, and it's the cube drill-thru table that you try to access?

Try to formulate your drill-thru as a SQL where clause, ans issue that outside WRS (Base SAS, EG etc) - hoe many rows are returned? And just for my curiosity, what about memory consumption, CPU/real time...

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

prashu — Tue, 17 Jun 2014 09:38:46 GMT

Hi LinusH,

Yes, I am accessing an OLAP cube by WRS,and it's the cube drill-thru table.

The data contains 1,74,868 rows, i am trying to drill a data of 92329 rows and 208 columns which results in error.

I have done it in Base SAS using where clause and every time the resultant varies.

NOTE: PROCEDURE SQL used (Total process time):
      real time           3.63 seconds
      user cpu time       1.07 seconds
      system cpu time     2.04 seconds
      memory              1082.37k
      OS Memory           7608.00k
      Timestamp           06/13/2014 01:26:23 PM

NOTE: PROCEDURE SQL used (Total process time):
      real time           3.07 seconds
      user cpu time       0.88 seconds
      system cpu time     2.18 seconds
      memory              983.12k
      OS Memory           7608.00k
      Timestamp           06/13/2014 01:27:18 PM

NOTE: PROCEDURE SQL used (Total process time):

real time 3.99 seconds

user cpu time 0.92 seconds

system cpu time 1.90 seconds

memory 983.12k

OS Memory 7608.00k

Timestamp 06/13/2014 01:38:35 PM

I have done the same in SAS EG using where clause.

NOTE: PROCEDURE SQL used (Total process time):
      real time           2.80 seconds
      user cpu time       0.88 seconds
      system cpu time     1.90 seconds
      memory              985.50k
      OS Memory           18476.00k
      Timestamp           06/13/2014 01:28:18 PM

NOTE: PROCEDURE SQL used (Total process time):
      real time           2.64 seconds
      user cpu time       0.90 seconds
      system cpu time     1.73 seconds
      memory              990.59k
      OS Memory           18476.00k
      Timestamp           06/13/2014 01:28:41 PM

NOTE: PROCEDURE SQL used (Total process time):
      real time           2.79 seconds
      user cpu time       0.96 seconds
      system cpu time     1.82 seconds
      memory              983.43k
      OS Memory           18476.00k
      Timestamp           06/13/2014 01:29:02 PM

Every month data will double as to maintain historic data i cannot drill 10 rows of CE Product which can be seen in above picture from a total records of 66,00,000 lakhs data.

The same is performed in EG

NOTE: PROCEDURE SQL used (Total process time):

real time 23:41.82

user cpu time 15.91 seconds

system cpu time 36.72 seconds

memory 1004.56k

OS Memory 18476.00k

Timestamp 06/17/2014 02:31:10 PM

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

Kurt_Bremser — Tue, 17 Jun 2014 10:49:40 GMT

100,000 rows with 200 colums means that the resulting .html page would be at least 100,000*200*30 bytes (including all the HTML tags). That's 600M! Imagine transmitting that to your browser, and displaying it. There's a reason why SAS bucks before that hurdle.

If you want to have users access to such big chunks of data, create a stored process that does the subsetting and writes a file for download. The drill through capability in WRS is meant for something that can meaningfully be inspected with eyeballs Mk I.