Universe Context design and Neutral Joins
I’ve seen some questions about the usage of the “Neutral” joins in universe’s Data Foundation. This blog is reviewing the differences between UNV and UNX with regards to type of Joins and the impact it can have.
Design change in BI 4.x with Relational UNX
In BI 4.x, the design of the contexts in relational UNX has changed to ease the maintenance. You no longer have to set a particular “State” for joins in non-ambiguous part of the schema, but leave it at “Neutral”. This is minimizing subsequent maintenance of the universe: if a new table is added, then only few context(s) would have to be updated depending on where this table and its joins are involved in the schema.
The underlying algorithm to build the SQL query from the objects used in the query has been updated according to this possibility. However this new SQL generation may be penalized by an old design where all joins are either “Excluded” or “Included”.
Note that, in the Information Design Tool (IDT), you can configure the default “State” to be used when added new joins. By default it is already at “Neutral” and should be kept as is. See below sections “Contexts”
UNV universe migration to UNX universe
With UNV universe designed in XI 3, you have to explicitly indicate for each context which joins should be “Included” and which one should be “Excluded”.
When you are converting your UNV universe in UNX with the Information Design Tool (IDT), the contexts are keeping the same definition. For example you can see below that all joins have been tagged “Excluded” or “Included” after migrating the “Beach” universe sample
However in BI4.x it is no longer mandatory in UNX to indicate for a context if a join is “Excluded” or “Included” when it is in a non-ambiguous part of the schema. For example, if in IDT you choose the detection of the context, then it will suggest the following
All the joins in the non ambiguous part of the schema have been left at “Neutral” state when you are detecting again the contexts.
The recommendation is to limit the number of “Excluded” or “Included” joins in each context as it increase the combination of joins evaluated when generating the SQL for a query. It could have a non negligible impact on the performance for loops including many tables. The more contexts and joins in each context you have in your universe, the more performance impact it would have if you don’t set correctly the joins state.
After converting a UNV universe into UNX, you should either re-detect the contexts to get optimized ones, or revisit your contexts to switch as much as possible joins in “Neutral” state.
As in some cases it is not possible to re-detect the contexts automatically or to change manually the joins state due to the number of modifications to be done, you can use the “BI Semantic Layer Java SDK” to automate this task if you can identify which exact join(s) should be set at Neutral state and for which context.
Ideally, we could imagine IDT would have additional checks in the future to propose some suggestions of joins to be set to Neutral…