topic Re: Database Views and Metadata in Data Management Questions

Database Views and Metadata

RandyKreuziger — Fri, 27 Dec 2013 19:50:13 GMT

Getting users to create metadata is always hard but they sure complain when it doesn't exist. With SDE views, created using sdetable -o create_view, metadata could be attached to the views. Now at version 10.1, creating a database view is very easy through ArcCatalog, but you can't attach metadata to the views as far as I can see. How are other sites handling this with users expecting metadata?

We are just starting to test ArcSDE 10.1 with SQL 2012.

Re: Database Views and Metadata

WilliamCraft — Fri, 27 Dec 2013 20:58:58 GMT

You can perform a variety of metadata operations on views in 10.1 if you utilize the Metadata GP tools under Conversion Tools. I'm not sure of how you are storing your source metadata, but perhaps something like the Import Metadata tool or the Metadata Importer tool (yes, these are different despite the reversed names) would help you. These are all found in ArcToolbox which can be launched from ArcCatalog or ArcMap.

Re: Database Views and Metadata

RandyKreuziger — Fri, 27 Dec 2013 21:24:20 GMT

Crafty,
I've tried both Metadata Importer and Import Metadata. Each fails with the same message
Exception from HRESULT: 0x80042601
And when I select the "Description" tab in ArcCatalog for the views it doesn't display any of the Edit, Validate, Export, Import buttons. Instead it displays
The item's XML contains errors

But you're saying views created using the "Create New View" support metadata?

Re: Database Views and Metadata

WilliamCraft — Fri, 27 Dec 2013 21:55:32 GMT

When you say ArcSDE 10.1, are you saying that you geodatabase is at the 10.1 release or that you are running the ArcSDE 10.1 application service or both? In other words, I just want to verify that your GDB itself has been created at or upgraded to the 10.1 release that matches the desktop client.

In terms of the first error, I've researched the HRESULT code and it does not appear to be documented online from Esri. You may need tech support to help troubleshoot this issue. Have you been able to create, import, and modify metadata for tables and feature classes without issue using the same user account?

In terms of the second error, I've seen this quite a bit in ArcCatalog when selecting the Description tab. I don't know if it's necessarily a symptom of or related to the first error you're seeing, though. I've seen this occur for views (and sometimes for feature classes and tables) if there is no well-formatted XML present for that object class. Once metadata is able to be added, that error disappears usually.

To answer your question, I believe the answer I can give you is 'yes' based solely on my opinion. I was able to import metadata from a table to a view using the Metadata Importer tool without getting the first error. I also can't find any information online that states that views registered with the geodatabase don't support database so I can only assume they do especially since I don't get the error you are seeing.

I did find some older, Esri-documented rules for metadata for spatial views, which are similar to regular views:

(1) The spatial view must contain an Object Id column; (2) The spatial view must be registered with the geodatabase; (3) Metadata can only exist for items registered with the geodatabase; and (4) Only the owner of a spatial view can edit the metadata for that spatial view.

I think in your case, I think you probably have 1 - 3 covered already but I am not certain about #4. Does the user account you're using to connect to SQL Server via ArcCatalog and ArcToolbox have admin rights (or assigned db_owner role, for example) on the database?

Re: Database Views and Metadata

KimPeter — Tue, 07 Jan 2014 16:47:45 GMT

ArcGIS metadata is stored in the geodatabase system tables, so you cannot store ArcGIS metadata for objects that are not registered with ArcSDE or the geodatabase. The views created through ArcGIS Desktop are database views and are not registered with ArcSDE or the geodatabase and, therefore, ArcGIS metadata cannot be stored for them.

We realize this is a limitation that needs to be addressed before the admin commands are retired. At present, though, the only way to store ArcGIS metadata for a view is to create it with the sdetable command as you described.

Re: Database Views and Metadata

RandyKreuziger — Tue, 07 Jan 2014 16:55:34 GMT

ArcGIS metadata is stored in the geodatabase system tables, so you cannot store ArcGIS metadata for objects that are not registered with ArcSDE or the geodatabase. The views created through ArcGIS Desktop are database views and are not registered with ArcSDE or the geodatabase and, therefore, ArcGIS metadata cannot be stored for them.

We realize this is a limitation that needs to be addressed before the admin commands are retired. At present, though, the only way to store ArcGIS metadata for a view is to create it with the sdetable command as you described.

Thank you kpeter for posting that. I got the same news from Aleta at ESRI a week ago but forgot to update this thread.

It was very hard getting users to create metadata but now they expect it which is a good thing. We've got a couple dozen SDE views that could be SQL views when we migrate to ArcSDE 10.1 later this month. But, how are sites handling the metadata for SQL Views and Query Layers?

Re: Database Views and Metadata

KimPeter — Thu, 09 Jan 2014 22:28:59 GMT

Randy,

Sorry for the delay in responding.

Before you upgrade the geodatabase, please export the existing metadata you have on your views to XML files. That way, you won't lose the existing metadata information.

Currently, metadata for views would be done by maintaining the metadata information in separate, stand-alone XML files. You could also look into metadata options in your database...

This is probably not the news you wanted, and I apologize for that. As mentioned, we are looking into ways to handle this in future.

-Kim

Re: Database Views and Metadata

MiguelParedes — Mon, 20 Jan 2014 14:27:57 GMT

Kimberly,

Thank you for your posting. I have just raised this issue with Esri Support, since I have a user the same issue as reported by kreuzrsk. The analyst who dealt with the issue did say that it was possible to have access to the metadata for a spatial view, but only after creating the view using the SDE Command tools to:
1) Creating the view with (sdetable -o create_view), and then
2) registering the view with (sdelayer -o register).

However, it seems the above will work only if the spatial type is SDEBINARY, and not if the spatial type is GEOGRAPHY or GEOMETRY. Would you be able to confirm if this is the case? If so, is this Esri would consider as part of whatever solution is implemented to deal with this issue?

Thanks in advance.

Re: Database Views and Metadata

TimLangner — Mon, 20 Jan 2014 14:32:18 GMT

Hi there

I started another thread on this, unaware this one existed. The problem exists in 10.2/10.2.1

Can anyone tell me if it is not good practice to have metadata on database views and if so please explain why?

We reported a bug to ESRI UK and this eventually resulted in an American ESRI support analyst logging a feature enhancement for the error message displayed to be more helpful. This isn't the solution we were looking for. Personally I'd rather the problem was fixed. As it is a feature enhancement it isn't possible to search for those online. However the bug number is: NIM098090

The support analyst�??s comments were that it does not make much sense to create meta data for a view when one would have the metadata for the original data source.

If that is the case what is the best practice when you need to publish a subset of data, complete with its own metadata and you�??d rather not duplicate your original database tables, to avoid redundancy in your database.

We are currently in the process of publishing data until our obligations to do use under the EU INSPIRE Directive. However some of our database tables cover multiple INSPIRE Themes so we need to create views of the data. Clearly the metadata has to reflect what we are publishing, rather than what the original database contains. Besides, if we publish a view, how can we publish the metadata if the view doesn't contain any?

Th other thread can be found at:
http://forums.arcgis.com/threads/101034-Bug-NIM098090-when-creating-spaital-views-in-ArcGIS-10.2-using-ArcCatalog?p=359361&posted=1#post359361

Re: Database Views and Metadata

MarcoBoeringa — Mon, 20 Jan 2014 15:53:59 GMT

Can anyone tell me if it is not good practice to have metadata on database views and if so please explain why?

Tim,

Good you managed to dig up this thread yourself, I just looked over it...

Of course it is good practice to add metadata to (subsets of) your data. The things I wrote in the other thread are merely to explain to you that there is a technical issue here, that is not a bug, but requires a potential enhancement to allow storage of metadata with spatial database views.

The support analyst�??s comments were that it does not make much sense to create meta data for a view when one would have the metadata for the original data source

I don't agree, I think you have a legit desire, but that doesn't make it a fault of ESRI or bug in the ArcGIS for Desktop application. It is a matter of choice in where to put development effort in, and if there is a need, than ESRI is likely to full fill it at some point, see Kimberly Peter's remark.

The functionality for creating database views from within ArcGIS for Desktop is relatively new, keep that in mind also.

If that is the case what is the best practice when you need to publish a subset of data, complete with its own metadata and you�??d rather not duplicate your original database tables, to avoid redundancy in your database.

We are currently in the process of publishing data until our obligations to do use under the EU INSPIRE Directive. However some of our database tables cover multiple INSPIRE Themes so we need to create views of the data. Clearly the metadata has to reflect what we are publishing, rather than what the original database contains. Besides, if we publish a view, how can we publish the metadata if the view doesn't contain any?[/URL]

I don't know if this is a real solution to your issue, but you might consider setting Definition Queries on your layers in ArcMap that need to be published, and save them out as stand alone layer files (*.lyr) outside the ArcMap document. You can than use ArcCatalog to create metadata for this specific layer file, which will be stored as a separate XML file next to your layer file in the folder where you stored it. Additionally, you can limit the number of visible fields in the layer properties.

Re: Database Views and Metadata

TimLangner — Wed, 22 Jan 2014 07:45:25 GMT

Of course it is good practice to add metadata to (subsets of) your data. The things I wrote in the other thread are merely to explain to you that there is a technical issue here, that is not a bug, but requires a potential enhancement to allow storage of metadata with spatial database views.

I don't agree, I think you have a legit desire, but that doesn't make it a fault of ESRI or bug in the ArcGIS for Desktop application. It is a matter of choice in where to put development effort in, and if there is a need, than ESRI is likely to full fill it at some point, see Kimberly Peter's remark.

The functionality for creating database views from within ArcGIS for Desktop is relatively new, keep that in mind also.

I guess the issue we found was that we were trying to achieve something that was possible in the past, via a different way of working. ESRI then decide they were prefer us not to use the existing way of working and stated they were going to remove it. So we switch our way of working and find we can't do what we previously were able to do. We have an ObjectID with our spatial views.

Then a support analyst decides the solution is to simply provide a better error message. We need a solution to the problem rather than just a more helpful error message. To their credit, it was actually one of the ESRI UK support analysts who found this thread and forwarded the link onto us.

Tim,I don't know if this is a real solution to your issue, but you might consider setting Definition Queries on your layers in ArcMap that need to be published, and save them out as stand alone layer files (*.lyr) outside the ArcMap document. You can than use ArcCatalog to create metadata for this specific layer file, which will be stored as a separate XML file next to your layer file in the folder where you stored it. Additionally, you can limit the number of visible fields in the layer properties.

That could be really helpful. I wasn't aware metadata could be created for layer files.

Re: Database Views and Metadata

MarcoBoeringa — Wed, 22 Jan 2014 10:49:54 GMT

I guess the issue we found was that we were trying to achieve something that was possible in the past, via a different way of working.

Very true, and the bold and underlined text is significant in this context.

I guess the issue we found was that we were trying to achieve something that was possible in the past, via a different way of working. ESRI then decide they were prefer us not to use the existing way of working and stated they were going to remove it. So we switch our way of working and find we can't do what we previously were able to do. We have an ObjectID with our spatial views.

First of: The ArcSDE Command Line tools aren't retired yet. You can still use them at 10.2(.1) if you need them, but you are encouraged and adviced to look for other options by ESRI. They will, however, be retired soon, but hopefully, ESRI will come up with a solution for the specific "metadata" issue before they do (as Kimberly hinted on).

Anyway, while you may feel ESRI is limiting you, ESRI actually opened up a whole new world to us: being able to easily create both native, and persisted, database views in an RDBMS that aren't entangled with the ArcSDE / Geodatabase repository (of course, if the database view is based on an geodatabase Feature Class, there still is some dependency), and the ability to add layers based on standard SQL via Query Layers, all straight from the ArcGIS for Desktop user interface.

That is a big bonus to the new 10.x versions of ArcGIS. Many people have wanted this functionality for a long time.

This bonus comes at a price though: you are no longer working within the scope of the geodatabase. This means several limitations, since the RDMBS database is not "geodatabase aware", even if you use layers from a geodatabase as the basis for the database views! This is because you are accessing the database not through ArcObjects classes designed for geodatabase access, which incorporate the intelligence to use the full geodatabase functionality in their code, but through other classes that deal with standard ODBC / database client connections.

The limitations are therefore (not a complete list):

- No awareness of complex datasets or geodatabase features like "Feature Datasets", "Geometric Networks", "Topologies", "Parcel Fabrics" and so on.

- No standard support for metadata on data layers

- No automatic translation of domain "codes" to human readable domain "descriptions"

- No knowledge of feature type (point, line, polygon) until dataset is accessed, because this is not registered in the ArcSDE / Geodatabase Repository, hence dataset must be selected in ArcCatalog before it shows its proper icon.

Re: Database Views and Metadata

KimPeter — Wed, 22 Jan 2014 16:21:42 GMT

However, it seems the above will work only if the spatial type is SDEBINARY, and not if the spatial type is GEOGRAPHY or GEOMETRY. Would you be able to confirm if this is the case? If so, is this Esri would consider as part of whatever solution is implemented to deal with this issue?

Hi Miguel,

The steps you describe are not limited to feature classes that use SDEBINARY, and should work with the SQL Server Geometry and Geography types. Did you get an error when you ran them on a Geometry feature class?

Re: Database Views and Metadata

KimPeter — Mon, 27 Jan 2014 17:53:45 GMT

As an aside, Tim requested this on the Ideas page.

http://ideas.arcgis.com/ideaView?id=087E000000059hjIAA

Those of you who need this functionality, please promote his idea.

Thanks 🙂

Re: Database Views and Metadata

RandyKreuziger — Wed, 03 Sep 2014 00:09:03 GMT

ESRI is moving away from the SDE command line to create views to the new "Create New View" functionality in ArcGIS Desktop, but don't consider it a bug when the replacement function loses functionality? Not uncommon for ESRI since they did that when going from ArcInfo Workstation to ArcGIS Desktop taking around 10 years to regain automated map production.

Losing the ability to attache metadata to database views is a serious loss. There is no way for the user to know what the base feature is or what definition queries are placed on the data. Layer files are pointers to data (and symbolozation) but once you've added the layer to your MXD metadata in the .lyr.xml file is no longer associated with the MXD entry!

Re: Database Views and Metadata

RandyKreuziger — Fri, 18 Dec 2015 16:21:35 GMT

Kim, we recently upgraded to 10.2.2 so we're still able to create SDE views. You mentioned two years ago that the problem of metadata for views was something that ESRI planned to address before the admin commands were retired. Has it been addressed at 10.3, or if not, at 10.4?

Thanks