Traditional web-based reporting with SAS BI tools

Need to Reduce Processing time while open linking SAS Reports in WRS

Reply
Occasional Contributor
Posts: 13

Need to Reduce Processing time while open linking SAS Reports in WRS

Hello Team,

 

 

Background Detail:

Currently we are using cube based summary report which is linked with detail report.

For this we use report linking approach instead of drill thorough approach. because we have requirement like hyperlink for total column and display all columns at time.

 

Issue:

While we click on Summary Reports hyperlink column it take too much time to open detail report. Still we are facing processing time issue.

Can we have another options in (Info map/Cube/Report linking)  which will help to reduce processing time to open detail link report.

 

Currently Table is having around 1.2 million records (DB2 Tables), In future it will grow further.

 

Can you please help us to resolve this issue. Let us know if you require more details.

 

 

 

Super User
Posts: 981

Re: Need to Reduce Processing time while open linking SAS Reports in WRS

[ Edited ]

Hello @Sachin_bansode,

 

Since you cannot change the code on how WRS queries the tables, I would strongly recommend you to have an updated copy of your DB2 table on a local SAS table.

 

Here is the reason why: every time you query a SAS table with an external database table, SAS will download your external table to a SAS table, do the join and sort the table in your SASWORK/UTILLOC and then answer the request. But you don;t want SAS to do this everytime your users query the table, since it is a complete wate of time, I/O and network. 

 

Therefore, try to keep those kind of tables within the SAS server itself and make periodic updates/appends. Also, if you don;t need the full table for your report, try to reduce the size by removing the columns/variables you won't need.

 

Besides this, which I would consider the best and most general suggestion, there are always ways to improve the performance such as:

 

- optimize the libname statement

- change the memory settings for your Workspace Server, Pooled Workspace Server and OLAP server

- etc.

 

But again, if you understand the basic behaviour and optimize your queroes, it already solves more than half of the problems you might face.

Occasional Contributor
Posts: 13

Re: Need to Reduce Processing time while open linking SAS Reports in WRS

 

Hello @JuanS_OCS

 

 -optimize the libname statement

      1.What exactly we need to optimize, I mean add some options in libname.

      2.Can you please suggest.

- change the memory settings for your Workspace Server, Pooled Workspace Server and OLAP server

  1.Can you have any documents on this steps.If So then please share with me it will be vary helpfull for us.

Thanking you again.

Esteemed Advisor
Posts: 5,086

Re: Need to Reduce Processing time while open linking SAS Reports in WRS

Assuming that the detail report contains subsets - try to figure out what the usual queries look like, and index your DB2 accordingly.

In your Pooled workspace server, add to the autoexec:

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

Then you should be able to audit how ACCESS engine and DB2 is managing your WRS queries.

 

Data never sleeps
Occasional Contributor
Posts: 13

Re: Need to Reduce Processing time while open linking SAS Reports in WRS

Hello @LinusH,

Thanking you very much for your suggestion.

Can you please help me to understand on below points.

1.Are you talikng about Log files??

2.Is it like ARM log files.

3.As you mentiones can we need to add 

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

in autoexec file??

Awating for you suggestion.

Esteemed Advisor
Posts: 5,086

Re: Need to Reduce Processing time while open linking SAS Reports in WRS

1. Yes. Workspace Server I think by default doesn't keep logs, at least not detailed ones. You may need to change the logging level (temporarily or in dev/test env).

2. No. SAS support ARM logging for SAS processing. This is for auditing logging on the RDBMS side. But if you run into performance issues on the SAS side, ARM logging might be a way to look into performance details on the SAS side.

3. Yes.... appserver_autoexec_usermods.sas in the PooledWorkspaceServer directory if remember correctly.

Data never sleeps
Post a Question
Discussion Stats
  • 5 replies
  • 90 views
  • 3 likes
  • 3 in conversation