BookmarkSubscribeRSS Feed
Sachin_bansode
Fluorite | Level 6

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.

 

 

 

5 REPLIES 5
JuanS_OCS
Amethyst | Level 16

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.

Sachin_bansode
Fluorite | Level 6

 

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.

LinusH
Tourmaline | Level 20

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
Sachin_bansode
Fluorite | Level 6

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.

LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 795 views
  • 3 likes
  • 3 in conversation