Skip to main content

Relationships

Overview

When the model is created, relationships are added between pipelines in the model. These relationships come either from the source database structure or metadata, where available (for sources such as SQL Server), or from a profiling algorithm, which creates relationships based on field names and comparison of data from possibly-related fields.

Semantic Layer relationships 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 Lookup up (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.

    REF REL 1mod_pipeline_relationships_flex_mapping.png
  • They can only be viewed on the RELATIONSHIPS tab of each pipeline.

    REF REL 2 mod_pipeline_relationship_screen.png

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.

REF REL 3 mod_data_source_prop_profile.png