Finding Sticky Notes made in a Data Integration job in the metadata structure

[RHogenberg]

Hello,

I am trying to create an report based on walking the metadata trees in SAS. I would like to do this because we have a lot of documentation inside the SAS Data Integration Jobs that we created.

There is one object that I am unable to find in the Metadata structure. Namely "Sticky Notes".

Does anyone have any idea how to find this particular object in SAS metadata?

I am using functions like metadata_getnobj, metadata_getasn , metadata_getattr for building the report.

Best Regards

Richard

[LinusH]

Have you tried the metadata navigation tree in DMS SAS? (DOn't have access to that now, so I can't confirm that you'll find the notes this way).

But I wonder a bit whenever this kind of documentation questions arise. ETL jobs are quite technical. Sticky Notes can contain almost anything (unless your very strict local regulations what to put there).

And there is a tool for navigating metadata - DI Studio.

Who are the receiver of this documentation? 

[RHogenberg]

I used the meta data browser finding all objects with the exception of sticky notes.

So basically only missing the information that is within the sticky notes (which have an identifying start word in them so we know if the remark is functional, technical an open issue and so on).

The documentation is for project and handover purposes.

The entire architecture is setup in certain datalayers each with a specific purpose. The ETL job are also build with lots of comments and information on job level as transformation level (using names and descriptions) this in order to improve maintainability (as other documentation always seems to be ill maintained it is best to keep it together with the source). So I am trying to generate a document out of it that can be used for reading purposes and also in order to establish an opportunity to see the differences between releases and thus generating release related documentation.

[Patrick · Accepted solution]

See below:

The way I found this:

1. create a very simple job with the object you're looking for (=job and a sticky note)

2. export the job (create .spk)

3. open the .spk with a zip tool (eg. 7-zip) and extract the XML's

4. open the XML's with Notepad ++

5. search all the files for a specific text (here: the text in your sticky note)

6. open the file found and format the XML (I'm using the Notepad++ plugin XMLtools)

     -> and here you go:

    <PropertySets>
      <PropertySet Id="A5C3ED0R.AB0009I9" Name="ModifiedByProductPropertySet" SetRole="ModifiedByProductPropertySet">
        <Properties>
          <Property Id="A5C3ED0R.AC000NJ9" Name="ModifiedByProduct" DefaultValue="Version 4.901 of SAS Data Integration Studio" PropertyName="ModifiedByProduct" UseValueOnly="1"/>
        </Properties>
      </PropertySet>
      <PropertySet Id="A5C3ED0R.AB0009IA" Name="USERPROPERTIES" SetRole="USERPROPERTIES">
        <SetProperties>
          <Property Id="A5C3ED0R.AC000NJA" Name="FlowDirection" DefaultValue="Right" PropertyName="FlowDirection" PropertyRole="USER" UseValueOnly="1"/>
          <Property Id="A5C3ED0R.AC000NJB" Name="DiagramXML" DefaultValue="&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; ?&gt;&lt;ShapeList&gt;&lt;StickyNoteNode&gt;&lt;NodeId&gt;5472c979-ac1a-304f-4e37-fd8af3984de4&lt;/NodeId&gt;&lt;PositionX&gt;143&lt;/PositionX&gt;&lt;PositionY&gt;107&lt;/PositionY&gt;&lt;Expanded&gt;false&lt;/Expanded&gt;&lt;Style&gt;NoteStyleYellow&lt;/Style&gt;&lt;Text&gt;test&lt;/Text&gt;&lt;/StickyNoteNode&gt;&lt;/ShapeList&gt;" PropertyName="DiagramXML" PropertyRole="USER" UseValueOnly="1"/>
        </SetProperties>
      </PropertySet>

[RHogenberg]

Thank you for the solution.

I found it indeed.

As a note that line contains the entire diagram drawing of the DI job. The Metadata Browser does not show the entire line buts cuts the line of after x characters.

Best Regards

Richard

[FrankPoppe]

Thanks from me as well.

I was looking for the same thing, for more or less the same reason.

Slick way also, to find the information that SAS hides in XML within metadata objects!

[Resa]

Hi @FrankPoppe,

It can also be retrieved using SAS code and by making use of the functions as indicated by the OP.

Please find below some sample code to retrieve the information

data work.job_metadata
		(keep=
			job_counter job_name job_publictype job_id job_uri job_checkvalue
			pst_counter pst_name pst_id pst_uri pst_checkvalue
			stp_counter stp_name stp_pname stp_defval stp_id stp_uri stp_checkvalue
		);
	length
		job_name			$128
		job_publictype		$16
		job_id				$32
		job_uri				$256
		pst_name			$128
		pst_id				$32
		pst_uri				$256
		stp_name			$128
		stp_pname			$256
		stp_defval			$32767
		stp_id				$32
		stp_uri				$256
		
		job_checkvalue		$2
		job_counter			8
		job_retcode			8
		pst_checkvalue		$2
		pst_counter			8
		pst_retcode			8
		stp_checkvalue		$2
		stp_counter			8
		stp_retcode			8
		attribute_retcode	8
	;

	call missing (of _all_);

	job_counter = 1;
	job_retcode = metadata_getnobj("omsobj:Job?@Id contains '.'",job_counter,job_uri);
	if (job_retcode<=0) then do;
		call missing(job_name,job_publictype,job_id);
		job_checkvalue = 'NG';
		output;
	end;
	else do;
		do while (job_retcode>0);
			job_checkvalue = 'OK';
			attribute_retcode = metadata_getattr(job_uri,"Id",job_id);
			attribute_retcode = metadata_getattr(job_uri,"Name",job_name);
			attribute_retcode = metadata_getattr(job_uri,"PublicType",job_publictype);

			pst_counter = 1;
			pst_retcode = metadata_getnasn(job_uri,"PropertySets",pst_counter,pst_uri);
			if (pst_retcode<=0) then do;
				call missing(pst_name,pst_id);
				pst_checkvalue = 'NG';
				output;
			end;
			else do;
				do while (pst_retcode>0);
					pst_checkvalue = 'OK';
					attribute_retcode = metadata_getattr(pst_uri,"Id",pst_id);
					attribute_retcode = metadata_getattr(pst_uri,"Name",pst_name);

					if (pst_name='USERPROPERTIES') then do;

						stp_counter = 1;
						stp_retcode = metadata_getnasn(pst_uri,"SetProperties",stp_counter,stp_uri);
						if (stp_retcode<=0) then do;
							call missing(stp_name,stp_id);
							stp_checkvalue = 'NG';
							output;
						end;
						else do;
							do while (stp_retcode>0);
								stp_checkvalue = 'OK';
								attribute_retcode = metadata_getattr(stp_uri,"Id",stp_id);
								attribute_retcode = metadata_getattr(stp_uri,"Name",stp_name);
								attribute_retcode = metadata_getattr(stp_uri,"PropertyName",stp_pname);
								attribute_retcode = metadata_getattr(stp_uri,"DefaultValue",stp_defval);
								if (stp_pname='DiagramXML' and index(stp_defval,'StickyNoteNode')>0) then output;

								stp_counter + 1;
								stp_retcode = metadata_getnasn(pst_uri,"SetProperties",stp_counter,stp_uri);
							end;
						end;

					end;

					pst_counter + 1;
					pst_retcode = metadata_getnasn(job_uri,"PropertySets",pst_counter,pst_uri);
				end;
			end;

			job_counter + 1;
			job_retcode = metadata_getnobj("omsobj:Job?@Id contains '.'",job_counter,job_uri);
		end;
	end;
run;

From this dataset the sticky note text can be retrieved from the stp_defval column.

Please find below a simple example for this (I am sure more fancy ways can be thought of):

data work.stickynotes_tags (keep=job_id job_name stp_id stickynote_tag text_tag);
	set work.job_metadata;
	length stickynote_tag text_tag $4096;
	/* This may need to be repeated in case there is more than one sticky note */
	stickynote_tag = substr(stp_defval,index(stp_defval,'<StickyNoteNode>')+16);
	stickynote_tag = substr(stickynote_tag,1,index(stickynote_tag,'</StickyNoteNode>')-1);
	text_tag = substr(stickynote_tag,index(stickynote_tag,'<Text>')+6);
	text_tag = substr(text_tag,1,index(text_tag,'</Text>')-1);
run;

[Jay_TxOAG]

After having written several of these data steps to find different meta data...I can really appreciate you showing good code organization. One missing length statement for a returned value and it is just missing in the output data set. Thanks