Model Join

The Model Join function type is a data model function that, just like the Join function, involves possible complex selections and the enrichment of one or more data sources.

Aside from the differences regarding the technical and buffering mechanisms, modeling of these two functions in the environment is performed in a similar manner with only slight differences.

From a technical perspective, Join is a processing function whereas Model Join is subordinated to the information functions. This means upon successful activation of a Model Join a SQL view is generated in the underlying database schema of the tenant. Thus, the Model Join function type behaves similar to an information function: no procedure is created; the function is purely projecting the content of the tables or views. The Join processing function on the other hand generates SAP HANA procedures that are then being executed to populate a temporary Ytable.

Regarding modeling, Model Join focuses on simplicity and does not have neither Input nor Checks nodes. Partitioning is not possible. Setting the function to “Executable” and changing the event handling aside from logging is not allowed. The Model Join function also does not provide the usual join features intended for processing functions (for example, Include Original Input Data, Processing Type, and so on). Most importantly, unlike the Join function which can have any other processing functions as input, the Model Join function can only use other information functions such as Model Table and Model View.

Model Join is also behaving as a normal Model View function, which in the end provides all data necessary and does not filter out null values.

To use the processing features as mentioned, a processing function (for example View) can be used on top before feeding the result to the Query function.

Key Features

Header

In the header, you define the principal behavior of the Model Join.

You can choose between different Model Join types:

“Implicit Fields”: Fields coming from inputs are automatically considered during the join procedure. If the field is visible on multiple join rules (or inputs), you can use the Auto Filling function.
“Explicit Fields”: Fields of inputs are manually defined and maintained by the modeler. These fields will then be considered during processing.

Example

In the “Union All” join type, you need to add all fields required for the output even if they are not part of the input data. The fields must be added on each subview of the rules to be joined before activation.

You can control the Model Join results with the Auto Filling option. The following settings are possible:

Do not use the option “No” for the Auto Filling field, because Model Join also behaves as a normal Model View which in the end provides all required data and does not filter out null values.
“If Null then First to Last”: The first non-null value is taken, and if all values are null, the initial value is returned for that field.
“If Null/Initial then First to Last”: The first non-null and non-initial value is taken, and if all values are null or initial, an initial value is returned for that field.
Note

Null vs. Initial
On the SAP Profitability and Performance Management Cloud UI, both NULL and INITIAL are represented by an empty cell for the characteristic and “0” for the key figure. The modeler must take care when processing input data with these values because the response is different:
- NULL (?) does not have any value and does not occupy memory. This can be the case if a field was not assigned with values initially.
- INITIAL (' ') has a value and therefore occupies memory. In SAP Profitability and Performance Management Cloud, a cell can have an INITIAL value if it was assigned an empty record.

Rules

Each Model Join rule semantically defines the reading of a specific input.

Hierarchical Model Join rules are also supported by assigning higher levels. The hierarchy of levels is resolved starting with the highest level, feeding as input to the lower levels, and ending with level 0.

The following rule types are available:

“From”: This is always the first rule of a level.
“Left Outer Model Join”: Returns all rows from the rule above as well as the columns and rows from this rule that match the predicates.
“Inner Model Join”: Returns all rows when there is at least one predicate match in the rule above and this rule.
“Full Outer Model Join”: Returns all (matched or unmatched) rows from both the rule above and this rule.
“Cross Model Join”: Returns the cartesian product of the rule above and this rule.
“Union All”: Behaves in the same way as a union, but duplicate records are not removed.
“Lookup”: Looks up fields and fills them in the first non-lookup rule above where the predicates match. At least one field needs to be defined as a lookup field.
“Lookup Auto Predicate”: Looks up fields and fills them in the first non-lookup rule where all common fields match those of the input function which is set on the Rule tab (this means, Field 1 of Rule 1 = Field 1 of Rule 2). At least one field needs to be defined as a lookup field.

You can also configure the following Model Join rule settings, if required:

Sub View: You can define further selections, formulas, aggregations and sorting orders for each rule.
Complex Selections: You can define complex selections using formulas and SQL functions.
Join Predicates: You can define the predicate conditions for the matching for Model Join and lookup rules here.
Complex Predicates: You can enter complex on-predicates for Model Join and lookup rules here using formulas and SQL functions.

Note

Fields coming from inputs are automatically considered during the Model Join procedure. If the field is visible, these settings are only relevant for multiple Model Join rules in which either non-null or non-initial values of the same field are considered and returned for that field. If there is only one rule, the Auto Filling options are not relevant.

Using SAP HANA hints in complex selections can help in scenarios where a Model Join function execution runs into an out-of-memory exception or is very slow. For more information about which hint could help in your implementation-specific scenario (rules at the same level or multi-level rules that lead to subqueries, formula/selection being used), see the SAP HANA Help for HINT.

Procedure

Function Access

Follow the steps below to access the Model Join function:

In the client where SAP Profitability and Performance Management is installed, choose SAP Menu Profitability and Performance Management Modeling Start My Environments.
The Environment screen appears in a separate browser window. Choose an existing environment and continue. Within the environment, you can set up the newly added function.

Function Configuration

Follow the steps below to configure the Model Join function:

In edit mode, configure the required fields Join Type and Auto Filling in the header. For more information about the header, see the Key Features section above.
Define the fields to be used on the Signature tab.

Note

For more information about the Signature tab, see Signature.
On the Rules tab, you can define the rule types of the Model Join function.

Proceed as follows to configure the rules:
1. On the Rules tab, choose (Add).
2. The Add Details screen is displayed. Enter the following information:
  - Add Level
  - Rule
  - Rule Type
  - Input Description
3. Choose OK.
4. Select the created rule.
5. Define the input function to be used.
6. On the Subview tab of the created rule, choose (Add).
7. The Fields screen appears. Select the fields to be used and choose OK.
8. Follow the steps below if you want to setup or apply a formula for a specific field (optional):
  1. Select the field and choose f (Formula).
  2. On the Formula screen which is displayed next, enter the formula.
  3. Choose OK.
9. Follow the steps below if you want to setup or apply a selection condition for a specific field (optional):
  1. Select the field and choose Selection Condition.
  2. On the Select Condition screen which is displayed next, enter the condition.
  3. Choose OK.
10. On the Complex Selections tab of the created rule, you can directly define complex selections using formulas and SQL functions.
11. On the Join Predicates tab of the created rule, choose (Add).
12. The system automatically adds a new line entry. Enter the following information here:
  - Field
  - Comparison
  - Join Rule
  - Join Field
13. On the Complex Predicates tab of the created rule, you can directly enter “on” predicates for Model Join and lookup rules using formulas and SQL functions.
Choose Save and then choose Activate.

Related Information

For more information about common aspects of SAP Profitability and Performance Management functions, see Concepts for Key Users.

For more information on how to add and remove functions in SAP Profitability and Performance Management environments, see Function Hierarchy.