0

Explaining Base Tables

Because Incorta can accommodate 3NF design, there is commonly more than one "path" by which we can link two tables together.  Take, for example, the schema above.  If we take a closer look at the relationship between the "customer" table and "salesperson" table, we can quickly see that they can be related either through "salesorderheader" or "salesterritory".

 

When building our insights against complex schemas such as this, the applicability of base tables almost always surfaces.  This need is illustrated below as we begin to build a insight using fields from "customer" and "salesperson".  Building the table below in analyzer mode, we see Incorta display "#ERROR" for my two fields from the "salesperson" table.

 

Upon inspecting the "Query Plan" of my "Salesperson ID" field, we notice that Incorta has not applied any linkage between my two participating tables.

 

This is because Incorta does not guess at how we want the two tables to relate.  This is where our "Base Table" concept comes in.  Under the "Advanced" section of my "Salesperson ID" column, I can now set my "Base Field" to a column from the "salesorderheader" table.  This tells Incorta explicitly how I wish for these tables to be linked.

  

Voila!  The "#ERROR" is now gone as Incorta has now been instructed through which table to join the data.  (Alternatively, including a column from the "salesorderheader" table would have had the same effect -- Incorta would know through which "path" I intend to relate my two tables.)

Checking the query plan for your fields is good practice.  It allows you to ensure that Incorta is applying the join paths you expect.

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like Follow
  • 7 mths agoLast active
  • 510Views
  • 1 Following