proc metadata.How to get PHYSYCAL name of source to DI studio ETL tables.

[Yura2301]

Hello,
So in general I am working on macro which has one parameter - name of ETL(DI studio Job) , and as result this macro must create table with list of source to this ETL tables.
For all this goals I use proc metadata and I've almost finished, but one issue appears - name of table in General tab of property menu can be different from real table name which can be find in "Phisical storage" tab.

Shortly about how it works- I create XML tamplate with appropritate structure and give it to proc metadata as parameter, and as result creates result XML file which consists of all needed data:

filename in "&workPath\in.xml";
filename out "&workPath\result.xml";

data _null_;
file in;
put '';
put '$METAREPOSITORY';
put 'Job';
put 'SAS';
put '';
put '404';
put '';
put "%bquote()";
put '';
...

proc metadata
header=full
in=in
out=out;
run;

After this I create XMLMap for getting source tables info,It looks like this:

filename xmlMap "&workPath\xmlMap.xml";
data _null_;
file xmlMap;
put '';
put '';
put '';
put '//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/ClassifierMap/ClassifierSources/PhysicalTable';
put '';
put '//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/ClassifierMap/ClassifierSources/PhysicalTable/@Id';
put 'character';
put 'string';
put '32';
put '';
put '';
put '//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/ClassifierMap/ClassifierSources/PhysicalTable/@Name';
put 'character';
put 'string';
put '256';
put '';
put '';
put '//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/ClassifierMap/ClassifierSources/PhysicalTable/@Desc';
put 'character';
put 'string';
put '256';
put '';
put '';
put '';
run;

And then I extract source tables:

libname xmllib xml "&workPath\ResultData.xml" xmlmap=xmlMap;

data WORK.JobSourceTables;
set xmllib.JobST1;
run;

But as I describe in begining of this post - name of table in general tab can be different from real physycal table name which can be find in "Phisical storage" tab.

The root of this question can be related with appropriate path to physical table, in my case it is:
//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/Select/ClassifierSources/PhysicalTable/@Name

But this path gives table name from general tab of property table, but I need name from "Physical storage" tab, and in documentatiuon I couldn't find how to get this.
"PhysicalTable" attribute 3 sub-attributes @id,@name and @desc sub-attributes...So may be I must check somewhere higher in objects tree...

Thanks in advanced!

[TimStearn_SASProductManagement_]

Hello,

The metadata property you're looking for is either "TableName" or "SASTableName". These are simple properties (rather than an associations), which can be retrieved right from the top level table object. Whether you want "TableName" or "SASTableName" depends on your application and these only differ for RDBMS systems that have special characters like spaces in the table name.

I generally use the metadata functions for the DATA Step for these types of tasks. The program below retrieves these properties with a "metadata query", assuming you start off with the object name:

options metaserver=""
metaport=8561
metauser=""
metapass=""
metarepository="";

data _null_;
length uri $256
id $20
uriStr $100
name $256
tableName $256
sasName $32;

uriStr=catt("omsobj:PhysicalTable?@Name = 'SALES_FACT'");
objCnt=metadata_getnobj(uriStr,1,uri);
if objCnt > 0 then do;
rc=metadata_resolve(uri,type,id);
rc=metadata_getattr(id,"TableName",tableName);
rc=metadata_getattr(id,"SASTableName",sasName);

put uriStr= uri= id= tableName= sasName=;
end;
run;

You are more likely to do this with the "id" property, in which case you would just change the "@Name" to "@Id" above.

For more information on the metadata functions in DATA Step, please see: http://support.sas.com/documentation/cdl/en/lrmeta/60739/HTML/default/viewer.htm#a003105516.htm. You should be able to translate what I've written above for use with the Metadata XML API, but if you need something more specific to XML usage, just let me know.

Lastly, I find the "Metadata Browser" tool in SAS Display Manager invaluable when writing these kinds of programs. You can find it under "Solutions>Accessories>Metadata Browser". Using the "Find" feature of this tool, you can enter queries strings like "PhysicalTable?@Name = 'SALES_FACT'" and get a graphical list of all matching objects. You can also see every available property for the returned objects and their value. Finally, you can just browse the object tree by object type if you don't want to write a query.

Thanks,

Tim Stearn

[Yura2301]

Thanks Tim!