Semantic Layer Modeling - Loop issue

About

Loop is a join path problem which is a set of joins that defines a closed path through a set of tables

_

How Does a Loop Affect Queries?

If you model based on the above structure, any query run against the tables in the loop would return only results where the country values for resorts and the country values for customer origin are equivalent. This double restriction on the shared lookup Country table returns fewer rows than expected.

Result Expected Result received
_ _

Country is serving two purposes:

  • Lookup for the resort country.
  • Lookup for the customer country of origin.

This creates a restriction so that data is returned only when the resort country is the same as the customer country. The resulting report shows only the number of visitors from the US who visited resorts in the US.

Resolution

You can:

  • break the loop by using an alias
  • create two models

Example: with an alias You create an alias for Country and rename it Country_Region. The two “one” ended joins are now separated as follows:

  • Country keeps a join to the Resort table.
  • Country_Region is joined to the Region table.

The model now appears as shown below:

_


Powered by ComboStrap