Traditional web-based reporting with SAS BI tools

PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

Reply
Occasional Contributor
Posts: 6

PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

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.

pissue1.png

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

pissue3.png

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.

pissue2.png

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

Esteemed Advisor
Posts: 5,979

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Learner
Posts: 1

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

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

Esteemed Advisor
Posts: 5,086

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

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...

Data never sleeps
Occasional Contributor
Posts: 6

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

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


Esteemed Advisor
Posts: 5,979

Re: PERFORMANCE ISSUE IN SAS WEB REPORT STUDIO

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Post a Question
Discussion Stats
  • 5 replies
  • 3509 views
  • 0 likes
  • 4 in conversation