Creating and Editing Replica Tables and Replica Views
Go Up to Creating and Editing Entities and Tables
Contents
ER/Studio Data Architect supports cross-database references of Database.Schema.Table (Replica Table) and Database.Schema.View (Replica View) to allow users to work with Replica Tables and Replica Views and supports reverse engineering of external dependencies for Views.
This feature is available for the following database platforms:
- Azure Synapse Analytics
- Snowflake
- SQL Server 2019 and 2022
Using Replica Tables and Replica Views
Insert Replica Tables and Replica Views into your Diagrams from the Insert > Objects > Replica Table or Replica View menu. Once a Replica Table or Replica View is in your diagram, notice that these objects include a think, dashed border with a different-colored background.
Users also can switch an Object between a Replica Table and a normal Table, as well as the same with Replica Views. Simply click to highlight the Table or View you want to switch, and then click Switch Object Class from the Insert ribbon menu. You can also use the context menu by right clicking on the Object in the Data Explorer tree, and then selecting Switch Object Class > Switch to Regular Table/Regular View or Switch to Replica Table/Replica View. Use Undo/Redo to return to the previous state.
Note:
- Switching from Regular Table/Regular View to Replica Table/Replica View causes a loss of all data associated with hidden properties, and regular relationships except for view relationships also are lost.
- Switching from Replica Table/Replica View to Regular Table/Regular View returns hidden properties but with the default values as if it is a new Table/View.
Both Replica Tables and Replica Views are available in the Automation Interface. For more information about this interface, see Automating ER/Studio Data Architect.
Understanding Replica Tables
Replica Tables include the following properties:
- offers only a limited number of tabs in the Replica Table Editor
- users can change Definition, Notes, and Glossary properties only
- Owner property of table is populated with databaseName.schemaName by default
Use reverse engineering to add a Replica Table when representing a Table outside of the selected database or use the Compare/Merge feature to add a Replica Table from another Physical Model.
Replica Tables cannot be a parent or child of a relationship. However, Data Architect allows Replica Table view relationships from non-replica views in the model if those views reference this Replica Table.
Understanding Replica Views
Replica Views include the following properties:
- offers only a limited number of tabs in the Replica Table Editor
- Owner property of table is populated with databaseName.schemaName by default
If the DDL is correct, a Replica View is validated no matter whether the referenced Objects are in the current Model.
Note that you cannot edit within the Columns tab. It is read-only and populated with the Columns parsed from the DDL. You can make changes to the DDL directly in the DDL tab.
View relationships are allowed from non-replica views in the model if those views reference this Replica View.
Using reverse engineering for Replica Tables and Replica Views
Users with the following permissions can reverse engineer Replica Tables and Replica Views:
- On the current database:
- SELECT and VIEW DEFINITION on the sys schema
- SELECT and VIEW DEFINITION on the selected schema
- On the referenced databases, i.e. where the View's dependencies reside:
- SELECT and VIEW DEFINITION on both the referenced schema and the sys schema
Note: This functionality supports only external databases within the same SQL Server instance. Cross-referenced database references are no supported.
Generating a database for Replica Objects
When Generating a Script File or Database with Replica Tables or Replica Views, note that the Replica Objects do not appear on Page 3 of the Wizard in the tree view for Tables and Views, and therefore no SQL preview is available.
Note: DDL is available for Replica Views only. Replica Tables display n/a.
Using the Compare and Merge Utility with Replica Objects
When Using the Compare and Merge Utility with Replica Tables or Replica Views, you can see the graphical differences between Models having and not having Replica Objects while viewing Page 5 of the Wizard. The Model that has a Replica Table or Replica View appears with the replica icon 
.
Click SQL Difference and you notice that you see only those properties and folders associated with Replica Objects no matter what type of Object is on the other side.
You cannot merge Replica Objects into Regular Objects. For example, if the source contains Replica Objects and the target is a Regular Table or Regular View, then using Merge into Target from the dropdown list automatically switches the Resolution to Ignore. Conversely, if the source contains Regular Objects and the target is a Replica Table or Replica View, then using Merge into Current from the dropdown list also automatically switches the Resolution to Ignore.
Note: You cannot generate alter script for Replica Objects even if you select the option to generate SQL.
Understanding Replica Object data lineage
If you compare a Model in another dm1 using the Import Source Wizard (Other Sources > Import New Source > From a model in another dm1), no Replica Objects appear beginning on Page 4 of the Wizard.
Saving only Replica Matches
In the Save Matches Options dialog, accessible from Page 5 of the Compare and Merge Utility by checking the Save Matches box, and then clicking the ellipses button in the bottom-right corner, you can create mappings only for Replica Objects by selecting the Save only replica matches option.
When this option is selected, the system save only those matches between Replica Objects and Regular Objects. You can identify Replica Mappings by the following icon on the Where Used tab of the Table/View Editor: INSERT ICON HERE
Note: If a universal mapping already exists between a Replica Table or Replica View and a Regular Table or Regular View and you select the Save only replica matches option, the mapping converts to a Replica Mapping.
A Replica Object can have only one Replica Mapping with a Regular Object. If a Replica Object already has a Replica Mapping, and you attempt to map it to another Regular Object, the existing Replica Mapping converts to a Universal Mapping and a new Replica Mapping is created for the new match.