Hey @vallsas! There is a way you can minimize the impact to reports by changing how you load and promote your data.
Typically when people update a report, they do the following order of operations:
- Drop the old table
- Load and promote the new table
While the old table is dropped, the report is unavailable. This is fine if the report updates in the middle of the night, but can result in a lot of questions during working hours.
An alternative is to load the updated table to a temporary caslib, drop the old table, then promote the new table. Promoting is very fast, even for large tables. What you'll do instead is:
- Load the new table to CASUSER
- Drop the old table
- Promote the new table from CASUSER to your final caslib
The downside is you'll temporarily have two copies of the table in memory at once. If you have a lot of memory or if the table is small then this shouldn't be a problem.
Your code will look like this:
cas;
caslib _ALL_ assign;
proc casutil;
load data=have casdata='have' outcaslib='casuser';
droptable casdata='have' incaslib='mycaslib';
promote casdata='have' incaslib='casuser' outcaslib='mycaslib';
save casdata='have' incaslib='mycaslib' outcaslib='mycaslib';
quit;
Let's test it out using a sample dataset with 100 million rows:
cas;
caslib _ALL_ assign;
data have;
do i = 1 to 100000000;
output;
end;
run;
data casuser.have;
set have;
run;
Checking the log, this took about 21 seconds to load to CAS:
NOTE: There were 100000000 observations read from the data set WORK.HAVE.
NOTE: The data set CASUSER.HAVE has 100000000 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 20.83 seconds
cpu time 18.45 seconds
Let's take this table that's already in CASUSER and promote it to Public:
proc casutil;
promote casdata='have' incaslib='casuser' outcaslib='public';
run;
Finally, let's check the log:
NOTE: Cloud Analytic Services promoted table HAVE in caslib CASUSER to table have in caslib public.
NOTE: The Cloud Analytic Services server processed the request in 0.160539 seconds.
It didn't even take one second to move the data between caslibs! If reducing downtime is your goal, loading to CASUSER first and then promoting to your final caslib is the way to go. Just be sure you have the memory to do it.
