Metadata table registered twice

[sathya66]

All,
If a table registered twice in metadata libraray In different locations. how would it know which one to reference through libname statement? Proper DI job works fine as we drag and drop the tables.
Ex:If table registered twice, say initial one with 5 columns, new one (metadata table updated) with 7
how would it know which one to reference?


libname Test meta library=Test;
proc sql ;
select * from Test.table ;
quit;

Thanks,
SS

[Patrick]

Avoid duplicate metadata names as much as possible.

"table registered twice in metadata libraray In different locations"

Is it even possible to have the exactly same named table metadata object linked to the same metadata library object?

You can have two table metadata objects with the same metadata name linked to two different libraries but I'm not sure that SAS will let you link them to the same library. Can you please try and confirm that this is even possible?

"Proper DI job works fine"

The DI job uses metadata IDs (which are always unique) so it will pick-up the correct metadata object to generate the code. BUT: If you have same named metadata objects then make sure that you always pack "everything" when creating a .spk for moving a job to another environment as there linking to objects happens over the metadata name (if the object is not in the package). You would get a prompt asking to which table to link - but it can be cumbersome to find the correct metadata table object if there are multiple.

"select * from test.table"

It depends how the library for "test" is defined in SAS Metadata. If you're using the META engine or Metadata Bound Libraries then SAS will check the metadata table object and if there is a duplicate the way you describe it (which I believe is not possible) then yes, that could cause issues.

In any other case the query executes only against the physical table so duplicate table metadata objects (same metadata name) wouldn't have any impact. 

[sathya66]

Yes, it is possible to have the exactly same named table metadata object linked to the same metadata library object in multiple/different locations.

[Patrick]

---

@sathya66 wrote:
Yes, it is possible to have the exactly same named table metadata object linked to the same metadata library object in multiple/different locations.

---

Then better get that cleaned up. That's just asking for trouble.

I believe if using the library meta engine then SAS just checks for a matching table metadata name and will likely use the first one it finds - meaning that if that's then the one with "missing" columns that's what you'll get.

[Kurt_Bremser]

---

@sathya66 wrote:

If a table registered twice in metadata libraray In different locations.

---

DO NOT DO THAT.

Even if you tricked SAS into having two separate metadata definitions for the same physical object, this is not recommended practice at all.

Any change implemented via one metadata object will not reflect in the other one, and WILL cause trouble.

Clean that up NOW.

[sathya66]

Yes, I agree. As developers have access to create/register metadata objects(libraries, tables ,jobs,etc). They are duplicating the things.
is there any way, we can restrict to do that or can we create a report on daily basis to check for duplicate objects (I found these mdsecgo or mdsecds macros are useful or is there any easy method that I can use).
Thanks for your help.

[Kurt_Bremser]

If some people can not be trusted to handle certain tools responsibly, then you need to take away their access to these tools. SAS metadata provides ways to do this (if one of the OOTB roles does not cover it, create a custom role where these functions are disabled).

It might be sufficient to send an email to the person(s) in question, with CC to all developers, where you clearly explain that such things MUST NOT BE DONE and why. Being notified rather publicly that one did something bad can do wonders.

Welcome to the world of being a systems administrator. Some times we need to be the Bastard Operator From Hell.

[Patrick · Accepted solution]

You can only enforce standards if you define the standards so first step is to define such standards and distribute them to whoever is allowed to create metadata objects. Then you can start to control and enforce.

For the control/reporting bit: I believe you would have to write your own metadata queries for this but there must already be bits and pieces around for this in whitepapers, samples in the documentation and also here in other discussions. If you get stuck with writing such a query then eventually ask a new question for this, explain what you have and what you need.

Just as an idea: 

Pull out name and path for defined metadata objects like job, deployed job, library, table, custom transformation.

For tables: Also get information to what library and physical table it links to.

For libraries: Get some of the connection info like engine and path (not sure what "path" would be for databases and how much variation there is in metadata - schema, db server, ?). 

[sathya66]

yes, we have standards in place but we can't force some crazy users to use them.
Thanks all for your help.
I will accept this as a solution and close this.