Universe Designing Important FAQS

What is a Lookup Table

A lookup (or dimension) table contains information associated with a particular entity or subject. For example, a lookup table can hold geographical information on customers such as their names, telephone numbers as well as the cities and countries in which they reside. In Designer, dimension and detail objects are typically derived from lookup tables

What is a Fact Table

A fact table contains statistical information about transactions. For example, it may contain figures such as Sales Revenue or Profit. In a universe, most but not all, measures are defined from fact tables.

Join path problems can arise from the limited way that lookup and fact tables are related in a relational database. The three major join path problems that you encounter when designing a schema are the following:

• loops

• chasm traps

• fan traps

Loops are Joins which form multiple paths between lookup tables It would return an  intersection of the results for each path, so fewer rows are returned than expected

Can be detected by Detect Aliases,Detect Contexts,Detect Loops,Check Integrity,Visual analysis of schema and can be resolved by creating aliases and contexts to break loops.

What is the loop doing to the query?

The purpose of the joins is to restrict the data that is returned by the query. In a loop, the joins apply more restrictions than what we expect, and the data returned is incorrect or few rows.

We use an alias to break a loop?

Alias breaks a loop by using the same table twice in the same query for a different purpose. but the  different name “tricks” SQL into accepting that you are using two different tables.

You can see the change in the generated SQL in applying the restrictions in where clause. Only a one time restriction is applied in the joins

What is a Chasm Trap? and Resolving Chasm Traps

A chasm trap is a type of join path between three tables when two “many-to one” joins converge on a single table, and there is no context in place that separates the converging join paths or we can define the chasm trap as. If you have two fact tables with many to one joins converging to a single lookup table, then we have a potential chasm trap.

We will get incorrect results only when:

1. A “many to one to many relationship”exists among three tables in the universe structure.

2. The query includes objects based on two tables both at the “many” end of their respective joins.

What is a Fan Trap?

A fan trap is a type of join path between three tables when a “one-to-many”

join links a table which is in turn linked by another “one-to-many” join. The

fanning out effect of “one-to-many” joins can cause incorrect results to be

returned when a query includes objects based on both tables. A fan trap is a less common problem than chasm traps. It has the same effect of returning more data as in the chasm trap.