Database Reference
In-Depth Information
When you first create a new DSV, the easiest thing to do is to go through all of the
steps of the wizard, but not to select any tables yet. You can then set some useful
properties on the DSV, which will make the process of adding new tables and
relationships much easier. In order to find them, right-click on some blank space in
the diagram pane and click on
Properties
. They are:
•
RetrieveRelationships
: By default, this is set to
True
, which means
that SSDT will add relationships between tables based on various criteria.
It will always look for foreign key relationships between tables and add
those. Depending on the value of the
NameMatchingCriteria
property,
it may also use other criteria as well.
•
SchemaRestriction
: This property allows you to enter a comma-delimited list
of schema names to restrict the list of tables that appear in the
Add/Remove
Tables
dialog. This is very useful if your data warehouse contains a large
number of tables and you use schemas to separate them into logical groups.
•
NameMatchingCriteria
: If the
RetrieveRelationships
property is set to
True
, then SSDT can also try to guess relationships between tables by looking
at column names. By default, the
NameMatchingCriteria
property is set
to
None
, which means this won't happen, but there are three other possible
settings for this property that translate to three different ways it can perform
this matching:
° By looking for identical column names in the source and
destination tables (for example,
FactTable.CustomerID
to
Customer.CustomerID
)
° By matching column names to table names (for example,
FactTable.Customer
to
Customer.CustomerID
)
° By matching column names to a combination of column and table
names (for example,
FactTable.CustomerID
to
Customer.ID
)
This is extremely useful if the tables you're using don't actually contain foreign key
relationships, or if you've used views on top of your dimension and fact tables in the
way we suggested in the previous chapter. You'll also see an extra step in the
New
Data Source View
wizard allowing you to set these options if no foreign keys are
found in the Data Source you're using.
Search WWH ::
Custom Search