We have a couple very large datasets that need publishing out of one SQL ArcGIS Server database into a 'Publishing' database (20 million records).
The data needs to be joined to 3 tables for additional attribution, plus a few aliases etc etc. Overall, they are simple 1-M joins based on traditional pk/fk - no spatial work required, except the input geometry from the large point dataset (basically joining to owner names, addresses in one table and a 'hierarchy' table for System Names and a couple other attributes)
Typically, we prefer to 'pre-process' as much as possible to make our nightly ETL processes more simple and perform better (join + export = slooooow)
Any best practice suggestions for creating views (SDE.Default) while keeping geometry?
You can create a database view with geometry or spatial view and then publish to ArcGIS Server. Here is the web help provided different methods to create database view:
We need more information to provide better suggestions:
1. What is your holistic requirement. How you want to use this publish service.
2. Do you have Geodatabase. Are these tables participated in creating views are part of one single geodatabase or coming from multiple geodatabase or multiple databases.
3. What is the version of Geodatabase and ArcGIS Server.
4. Who is publishing view.