Relationships
Overview
When a model is created, some relationships are generated automatically, while others are added manually. This design approach achieves a balance between efficiently establishing relationships while maintaining acceptable performance.
Relationships that are created automatically are mostly semantic layer relationships. These are relationships that are present in the data warehouse, and are also created between the appropriate facts and dimensions in the Semantic model, so they can be used to create reports. They are discussed in relationships in semantic modeling
For the purpose of this article, we look at relationships that support the creation of transformations in the model. They are called Lookup up relationships (previously Flexible relationships).
Lookup (flexible) relationships
Lookup relationships uses complex conditions and limited validation to create lookups. These relationships:
exist only in the data warehouse.
are used to create lookup (denormalized) columns in pipelines in the data warehouse.
define how the pipelines in the model are connected to one another by specifying the columns they have in common.
They can only be viewed on the RELATIONSHIPS tab of each pipeline.
Note
Be aware that it is not uncommon for lookup relationships to generate duplicates. When the table that you link to for your lookup values have duplicates, they can be replicated across.
Relationship profiling
Profiling settings
The Profiling Settings area contains properties that allow you to refine the behavior of the profiles created for each column.
The following settings are available in this area:
Rows to Profile - This value determines the number of rows in each table of the data source that are examined to build up a profile of each column.
The column profiles are displayed as tool tips when hovering over the column in the pipeline schema. Column profiles are also used to set column widths and to build relationships for data sources that do not have relationship metadata available. The value is a tradeoff between accuracy and speed and varies between data sources.
Internet-based data sources may be too slow to allow profiling large numbers of rows. Increase the value if the profiling information seem inaccurate. Decrease it if profiling is taking too long.
Detect Relationships from Data - Specify whether or not any existing relationship information will be detected and included for all pipelines in the data source. Skipping this detection step (clearing the corresponding check box) can speed up the process of adding pipelines and the relationships can be defined at a later time.
 |