topic Re: SAS Metadata / DI Studio problem in SAS Data Management

SAS Metadata / DI Studio problem

ErikLund_Jensen — Sat, 19 Oct 2019 14:28:06 GMT

Hi experts out there...

I have a problem with Autopropagate or Automap in DI Studio job transformations. Autopropagation. It is a sure way to get superflous columns propagated through work tables and permanent tables in a whole data warehouse, and our company policy requires these options to be disabled.

In teory they can be disabled in the options settings for Job Editor, but this has no effect on new transformations in a job (SAS DI Studio 4.903 on Windows 10 clients). They still have the settings enabled, and they must be disabled manually in every transformation.

This is often forgotten or neglected by ETL developers, so it is part of our central quality control. But in a setup with more than 4000 jobs /30.000 transformations and dozens of new or changed jobs every week it is a tedious task, and besides it obscures job responsibility, because the Metadata Modified-by-registration no longer points to the developer.

We have automated many tasks in our job quality control, e.g. enforcing standardization of formats, but I have not been able to disable occurrences of Autopropagate or Automap with a program. It is not difficult to locate them with a program, but all my efforts to disable them has failed so far.

The only way I can figure out is to use the metadata_setprop function like this example.

data _null_;
	length inuri outuri $200;
	inuri = "omsobj:PropertySet/A580FNM8.AB00AL75";
	outuri = '';
	rc = metadata_setprop(inuri,"IncludedInPropagation","false",outuri);
	put rc= outuri=;
run;

What happens is explained in the following cut from the metadata browser. The In-URI points to the OPTIONS object (blue background).

Instead of changing the value of the existing property IncludedInPropagation associated with SetProperties (lower blue box), it adds a new property IncludedInPropagation associated with Properties (upper blue box), which of course has no effect on the transformation.

It might be expected from the name that the metadata_setprop function would do exactly that, i.e. add a new property or change an existing Property associated with Properties and not with SetProperties, but that does not solve my problem. How do I write a function call to change the existing property? - any help would be greatly appreciated.

Re: SAS Metadata / DI Studio problem

Patrick — Sun, 20 Oct 2019 06:51:33 GMT

Looking into the XML it appears that you need to set DefaultValue="false" for any Property with PropertyName either being IncludedInPropagation or IncludedInMapping

<Property Id="A5LSNX1U.AC006O2R" Name="IncludedInPropagation" DefaultValue="true" PropertyName="IncludedInPropagation" PropertyRole="OPTION" SQLType="16" UseValueOnly="1"/>
<Property Id="A5LSNX1U.AC006O2S" Name="IncludedInMapping" DefaultValue="true" PropertyName="IncludedInMapping" PropertyRole="OPTION" SQLType="16" UseValueOnly="1"/>

Below code returns a table with all the object id's matching above cases. You then could use this information to either generate and execute an UpdateMetadata XML or you use SAS function metadata_setattr()

filename myinput temp lrecl=256;
filename myoutput temp recfm=N;

/***
data _null_;
   file myinput;
   input;
   put _infile_ ' ';
   datalines;
<GetMetadataObjects>
  <Reposid>$METAREPOSITORY</Reposid>
  <Type>Property</Type>
  <Objects/>
  <NS>SAS</NS>
  <!-- OMI_XMLSELECT(128) + OMI_GET_METADATA(256) + OMI_TEMPLATE (4) -->
  <Flags>388</Flags>    
  <Options>
    <XMLSELECT search="*[@PropertyName = 'IncludedInPropagation' and @DefaultValue='true' or @PropertyName = 'IncludedInMapping' and @DefaultValue='true']"/>
    <Templates>
      <Property Id="" Name="" DefaultValue=""/>
    </Templates>
  </Options>
</GetMetadataObjects>
;
run;
***/


data _null_;
   file myinput;
   input;
   put _infile_ ' ';
   datalines;
<GetMetadataObjects>
  <Reposid>$METAREPOSITORY</Reposid>
  <Type>Property</Type>
  <Objects/>
  <NS>SAS</NS>
  <!-- OMI_XMLSELECT(128) -->
  <Flags>128</Flags>    
  <Options>
    <XMLSELECT search="*[@PropertyName = 'IncludedInPropagation' and @DefaultValue='true' or @PropertyName = 'IncludedInMapping' and @DefaultValue='true']"/>
  </Options>
</GetMetadataObjects>
;
run;

proc metadata method=DoRequest
   in=myinput
   out=myoutput
  ;
run;

filename map temp;
libname myoutput xmlv2 automap=replace xmlmap=map;

options ps=max ls=max;
proc print data=myoutput.property;
run;

If using a SAS data step approach then I believe using table myoutput.property as created above the code to change the value to false could look as simple as below (not tested).

data _null_;
  set myoutput.property;
  rc=metadata_setattr(Property_ID, "DefaultValue",  "false");
run;

Re: SAS Metadata / DI Studio problem

Patrick — Sun, 20 Oct 2019 21:55:36 GMT

Or here another coding option using data step only (not tested). I believe the main difference to what you're doing: There is no attempt to create a new property but it's updating the attribute DefaultValue from true to false.

data test;
  length uri $256;
  nobj=0;
  nobj=metadata_getnobj("omsobj:Property?(@Name='IncludedInPropagation' or @Name ='IncludedInMapping') AND (@DefaultValue='true')",1,uri);
  do i=1 to nobj;
    rc=metadata_getnobj("omsobj:Property?(@Name='IncludedInPropagation' or @Name ='IncludedInMapping') AND (@DefaultValue='true')",i,uri);
    /* write to metadata - set attribute DefaultValut to false */
/*    rc = metadata_setattr(uri, "DefaultValue", "false");*/

    /* read from metadata - allows to list objects before changing them */
    length DefaultValue $5;
    rc=metadata_getattr(uri,"DefaultValue",DefaultValue);
    output;
  end;
  stop;
run;
proc print;
run;

Added later:

I've just realized that in the code initially posted the 2nd metadata_getnobj() call was missing. Above code amended accordingly.

Re: SAS Metadata / DI Studio problem

ErikLund_Jensen — Sun, 20 Oct 2019 08:58:17 GMT

@Patrick

Thank you!

I saw Properties as something special and didn't realize that - according to the metadata model - a Property is just an Object like all other, and the DefaultValue just an attribute, so it could be set with metadata_setattr.

It took less than one minute to find and correct 776 properties in our test environment, so you just saved me from days of tedious work.