Advanced Joins

The v2017.2 release of Exago BI features several Join Enhancements, including the ability to join on inequalities, nested join expressions, and subqueries. Additionally, data objects can now be cloned, which allows for multiple different joins between objects, as well as the ability to join objects to themselves.

These enhancements allow developers to programmatically define complex joins “on-the-fly” in the application API, without needing to write SQL directly.

Note

To modify advanced joins in the Exago UI, see Joins – Advanced Joins. Advanced joins are not supported in the Admin Console. Advanced joins cannot be applied across multiple data sources.

Join Expressions

The left and right side of application joins now support complex expressions with the following logic:

Operators: EQ (=), NE (<>), LT (<), GT (>), LE (<=), GE (>=), IN

Conjunctions: AND, OR

Expression types: Column, Constant, SubQuery, Expression

Note

For a list of all constants, please see the Constants and Enumerators article.

Example

The JoinColumn and KeyColumn API objects have been extended with support for the additional logic. The following example demonstrates how to write a complex join statement in the API:

// INNER JOIN Products ON Categories.CategoryId > Products.CategoryId
// OR (Categories.CategoryId = Products.CategoryId
// AND Categories.CategoryId = Products.ProductId)

var join = new Join(api.PageInfo)
{
  EntityFromName = "Categories",
  EntityToName = "Products",
  Type = (int)JoinType.Inner,
};

join.JoinColumns.Add(new JoinColumn(
  new KeyColumn(join.EntityFromName, "CategoryId", JoinExpressionType.Column),
  JoinComparison.GT,
  new KeyColumn(join.EntityToName, "ProductId", JoinExpressionType.Column),
  JoinConjunction.OR,
  0 // nesting level - number of parens surrounding the expression
  ));

join.JoinColumns.Add(new JoinColumn(
  new KeyColumn(join.EntityFromName, "CategoryId", JoinExpressionType.Column),
  JoinComparison.EQ,
  new KeyColumn(join.EntityToName, "CategoryId", JoinExpressionType.Column),
  JoinConjunction.AND,
  1
  ));

join.JoinColumns.Add(new JoinColumn(
  new KeyColumn(join.EntityFromName, "CategoryId", JoinExpressionType.Column),
  JoinComparison.EQ,
  new KeyColumn(join.EntityToName, "ProductId", JoinExpressionType.Column),
  JoinConjunction.OR,
  1
  ));

// api.Joins.Add(join);    // add at the application level
// report.Joins.Add(join); // or at the report level

Join XML Schema

The join XML schema has changed to support these enhancements. The <joincol> attribute is deprecated and replaced with <clause>:

<clause>
  <left_entity /> <!-- added in v2017.3 -->
  <left_side />
  <right_side />
  <conjunction />
  <comparison />
  <left_side_type />
  <right_side_type />
  <level />
</clause>

For details, see Config File XML Reference – Joins.

Reports created in older versions will be automatically converted to the new schema when they are saved in the Advanced Report Designer. The Exago BI configuration file will not be converted automatically.

Example

<join>
 <affinity>Global</affinity> 
 <entity_from_name>Categories</entity_from_name>
 <entity_to_name>Products</entity_to_name>
 <join_type>inner</join_type>
 <relation_type>1M</relation_type>
 <weight>0</weight>
 <clause>
 <left_side>CategoryID</left_side>
 <left_side_type>Column</left_side_type>
 <comparison>GT</comparison>
 <right_side>CategoryID</right_side>
 <right_side_type>Column</right_side_type>
 <conjunction>OR</conjunction>
 <level>0</level>
 </clause>
 <clause>
<!-- <left_entity>New Left Entity Name would go here if needed -->
 <left_side>CategoryID</left_side>
 <left_side_type>Column</left_side_type>
 <comparison>EQ</comparison>
<!-- <right_entity>New Right Entity Name would go here if needed -->
 <right_side>CategoryID</right_side>
 <right_side_type>Column</right_side_type>
 <conjunction>AND</conjunction>
 <level>1</level>
 </clause>
 <clause>
 <left_side>CategoryID</left_side>
 <left_side_type>Column</left_side_type>
 <comparison>EQ</comparison>
 <right_side>ProductID</right_side>
 <right_side_type>Column</right_side_type>
 <conjunction>AND</conjunction>
 <level>1</level>
 </clause>
</join>

Entity Cloning

Data Objects (also referred to as entities) can be cloned on a per-report basis, to allow objects to be joined in multiple ways.

The following example demonstrates how to add a cloned entity to a report:

Example

report.Entities.Add(api.Entities.GetEntityClone(
  "Categories",    // original entity
  "Category_Clone" // cloned entity name
));

The clonedFrom property indicates the original entity that a clone was created from.

Entity Report XML Schema

The entity report XML schema was updated to support an optional <clone> attribute, indicating that an entity is a clone of an existing one:

<entity>
  <entity_name>Categories_Clone</entity_name>
  <clone>Categories</clone>
</entity>

Cloned entities can be added to reports by editing the XML directly, if desired.

Configuration Settings

A hidden flag was added to the configuration file in support of entity cloning.

• <aliasallentities>

As entity names may no longer be unique, the entity Id property (which must always be unique) can be used as a table alias in any application generated SQL. This can be enabled by setting the <aliasallentities> attribute in the configuration file to True. The default value is False, meaning all entities will not be aliased with the ID property. Setting to True will enable the aliasing behavior.