1

Having Oracle dbms with ArcSDE and ArgGIS Pipeline Database Model (APDM), I would like to best describe the system with a diagram. In MS SQL each db is autonomous, I think, meaning ArcSDE(using dbo schema) has the SDE business tables in each db and further using a database model, each database has the database model implemented within??

For Oracle (oracle has a schemas instead of db's??), the Oracle instance that has the sde business tables and the database model..assigns users to participate in those tables as a sort of view (schema??) and sde manages those schemas??

My question: I would like to draw a picture showing how sde and the database model relate as well as how they sit in relation to the rdms on Oracle and on MS SQL Server. Thanks.

Improve this question
4
  • 1
    not very clear in present form. Are you wanting a diagram of the schema?
    – Brad Nesom
    Commented Apr 28, 2011 at 18:36
  • Sorry for the kind of jibberish. The first two "paragraphs" describe my understanding of SDE/Database Model/RDMS (oracle/sql server). I was hoping for corrections to any misunderstandings I have. The question is just how to best draw the picture of that. I don't need to do any design work..I just need to illustrate the system and I want to make sure I have it right in my head. Thanks.
    – Justin
    Commented Apr 29, 2011 at 16:36
  • The struggle is there are several (some can be quite extensive) ways to depict these objects. Your question of "how they relate" leaves a lot of room for interpretation. I will elaborate in my answer further.
    – Brad Nesom
    Commented Apr 29, 2011 at 16:46
  • Correct on the system. I want to show how our Oracle instance is sitting on db server A and how sde is sitting inside of that Oracle instance and how APDM is sitting inside of the Oracle instance also. Then I will show db server B which holds SQL Server instance with multiple db's which all have sde "installed" meaning sde business tables. Then I want to show how web server C has ArcGIS Server pulling data from these two db servers and pushing it to a web viewer. The oracle instance is what I am struggling to show..in terms of how sde(sde business tables) lives inside the oracle instance.
    – Justin
    Commented Apr 29, 2011 at 18:16

3 Answers 3

Reset to default
1

There are some tools that might help.
GDBtoolset

domain tools

And maybe search arcscripts for others.

There may also be an addin. Here's how to use those. And find goeprocessing addins here
Find Geodatabase addins Here
If you have Visio here is a good one called geodatabase diagrammer

The model deals with feature types, field names, types, definitions, and relationships. As well as any special cases (i.e. networks, topology, "linear referencing systems").
Which you would have with APDM.
The schema relate to these.
However when you say "system" and "autonomous". I begin to picture network and connectivity diagrams.
Model diagrams for PPDM are available on the PPDM website here.
And APDM Here.

OK that is clearer (from comment). Here is a technical link to all you need to know to draw it correctly...
esri resource center

This may be the type of graphic you are looking for.

Also here is a good section with some graphics for ideas

Improve this answer
1
  • Also, a big thanks here. The last image "section" perfectly shows the complexity to my boss without getting technical..though we are primarily on 9.3.1 so I will have to see if the docs show the same for that category..thanks.
    – Justin
    Commented Apr 29, 2011 at 21:06
0

If I understand you correctly...

  1. MSSQL Server uses the Database concept to segregate 'related' information.
  2. Oracle uses the Schema concept to segregate 'related' information

'related' here is taken as data used for a specific user/project/etc.

I think you're asking "what does this mean for ArcSDE and its implementation in Microsoft SQLServer and Oracle environments?"

Microsoft SQLServer

  • Each database instance contains the associated SDE tables (see the answer by Brad Nesom), stored procedures and permissions.

  • The default database instance is SDE (by default). Multiple instances can be created using the ArcSDE Post installation tool.

  • This tool creates the new SDE instance as well as an associated Windows Services (esri_sde by default). A Window Service is typically created for each SDE database instance.

Oracle

  • An Oracle RDBMS has one and only one Database Instance. Data is segregated using the Schema concept.
  • A Schema is analogous to a Database Instance in Microsoft SQLServer, but are strictly not the same.
  • The SDE Schema in Oracle is the custodian of the associated SDE tables and stored procedures as well as Data tables associated with this Schema
  • Additional Schemas can be given permissions to the SDE Schema tables and stored procedures, giving these additional schema's access to SDE functionality which can be read by the ArcSDE service.

This is my understanding of the High level architecture associated with these database types. PostgreSQL changes things again, almost combining the two RDBMS design concepts.

I hope this helps. Please change this answer to clarify or add corrections.

Cheers, Andrew

Improve this answer
2
  • It does help, thanks a lot. Any key concepts on the real-world differences between database models/versioning/other when on Oracle vs MSSQL Server?
    – Justin
    Commented Apr 29, 2011 at 21:04
  • good definition @OptimizePrime
    – Brad Nesom
    Commented Apr 29, 2011 at 21:36
0

There's also the ArcGIS Diagrammer.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.