Juletip #16 SAS DI Studio - Promotion or Redeployment needed?

[KirstenHaubjerg]

When working with jobs in DI Studio it can be quite hard to keep track of which jobs need to be reployed and which ones might be in need of promotion.

Today's juletip is a job, that can determine what might need to be redeployed and/or promoted.

The end result is a report that looks like this:

First you need to download the attached macros and unzip them in a folder (in the example I have placed them in c:\temp\metadata.

If you place them elsewhere please change the path macro variabel in the example.

Html_report contains the location and name of the final report - can also be changed.

*Macro location;
%let path=c:\temp\metadata;

*Html report;
%let html_report=c:\temp\deployment.html;

%include "&path\get_mdjob.sas";
%include "&path\get_mdjfjobs.sas";
%include "&path\get_mdFile.sas";
%include "&path\get_mdfolders.sas";
%include "&path\get_mdTransformationStep.sas";
%include "&path\get_mdTransformationActivity.sas";

%macro get_metadata_info(envir);
%get_mdjob;
%get_mdjfjobs;
%get_mdFile;
%get_mdfolders;
%get_mdTransformationStep;
%get_mdTransformationActivity;

proc sql;
create table scheduling_&envir 
as select job_id label='Job id',
          job_name label='Job name',
          input(job_metadataupdated,datetime22.3) as di_updated label='Updated in DI Studio' format=datetime22.0,
          input(file_metadataupdated,datetime22.3) as deployed label='Deployment date' format=datetime22.0,
          case when calculated deployed=. then 'Not deployed'
               when calculated di_updated gt calculated deployed then 'Yes'
               else 'No' end as redeployment label="Reployment£needed" length = 12,
          strip(tranwrd(path,'/SAS Data Integration Studio Custom Tree/Platform','..')) as path length = 90 label='Path',
          transformationactivity_name as flow_name label='Flow name'
from 
jobs
left join jfjobs on jobs.job_id=jfjobs.associatedjob_id
left join file on jfjobs.sourcecode_id=file.file_id
left join md_folders  on jobs.tree_id=md_folders.id
left join transformationstep on jfjobs.transformationstep_id=transformationstep.transformationstep_id
left join transformationactivity on transformationstep.transformationactivity_id=transformationactivity.transformationactivity_id
order by calculated redeployment;
quit;
%mend;

 Now we need to run the macro for both TEST and PRODUCTION

Here you need to change the metadata informations to your own environments. Please be advised that the report will only contain information from the folders that the specified user has the rights to.

*Get production info;
* Set metadata options for production *;
options metaport       = 8561 
        metaserver     = "xxxxx" /*Name of server*/
        metarepository = "Foundation"
        metauser="xxxxx" /*Name of user - batchuser or similar */
        metapass='xxxxx';
%get_metadata_info(prod);

* Get Test info;
* Set metadata options for production *;
options metaport       = 8561 
        metaserver     = "yyyyyy" /*Name of server*/
        metarepository = "Foundation"
        metauser="yyyyy" /*Name of user - batchuser or similar */
        metapass='yyyyy';
%get_metadata_info(test);

Finally - the report:

* Compare information from both environments and create a report;
proc sql;
create table scheduling as 
       select a.*
              ,b.di_updated as test_di_updated label="Updated in DI Studio - Test"
              ,case when b.di_updated =. then 'Only exist in production'
                    when a.di_updated lt b.di_updated then 'Yes'
                    else 'No' end as promotion label="Promotion£needed" length = 30
       from scheduling_prod a 
  left join scheduling_test b
       on a.job_name=b.job_name and a.path=b.path;
quit;


proc format;
value $cback_redeploy
  'Not deployed' = 'yellow'
  'Only exist in production' = 'yellow'
  'Yes' = 'red'
  'No' = 'green';
run;

ods _all_ close;
ods html body="&html_report.";
proc report data = scheduling nowd split="£" style(report)={cellpadding=3 }
            style(header)={font_weight=bold font_size=2  } missing;

            column flow_name job_name redeployment promotion deployed di_updated test_di_updated path;
            define flow_name /order=internal;
            define redeployment /order=data style(column) = {background=$cback_redeploy.};
            define promotion /order=data style(column) = {background=$cback_redeploy.};
            define path /order=internal;
run;
ods html close;
ods listing;

Merry Christmas

Kirsten Haubjerg

[Clash]

Very useful, thx a lot!

[sdksda]

Super tip. 🙂

[LasseOlsen]

Very useful tip. Especially in large environments