SAP Profitability and Performance Management Cloud is introducing a new function called Model Join. Just like the processing function Join, this new function involves possible complex selections and possible enrichment of one or more data sources.
Yes, I am saying that this function is being modeled almost the same way as the processing function Join, so why am I excited to share with you that this Model Join Function now exists? Let me explain to you some benefits of this function…
Avoid Unnecessary Data Replication
Before I explain what I meant with data replication let me give you a recap about Query.
If you are not yet aware, Query allows projection (and simple manipulation of data) which then is being used for reporting.
Unless the input of the Query is a Model Table or Model View, processing function should be set as Executable or should be a process Activity which means it must be executed and must buffer data in its temporary function table (YTable) so that the Query on top of it can consume and read the data produced by these functions.
As SAP PaPM Cloud understands that sometimes, customers are having mass data being fed to Query, it can be that temporary data buffering is not so much efficient on some occasions.
Example occasions where you would probably want to use Model Join as an input of a query instead of using a processing function like View or Join.
- I have 2 or more raw data tables which I need to join before projecting as Input in my report. I then used the Join function to enrich the data coming from the tables then use the Join as an input of a Query.
- I have an output function which is a processing function with thousands of lines, I want to refine my content by selectively share records and fields on my Query1 but still I don’t want to make my processing function simple as I will still use it for Query2. So, for both Queries I will use View1 and View2 to manipulate and wisely choose the data I want to feed to my Query.
As you can see on both occasions mentioned above, unnecessary data replication happened just so Query can project the result of the input function. This challenging situation can then be solved by using Model Join. How and what is so special about this function?
In the background, Model Join creates SQL Views which then in the end being used by Query to project data; while other inputs such as Allocation, Join, View create SAP HANA procedures which then must be executed to populate the underlying Ytable before Query can project the datarecords. See the difference?
Now that we have swiftly discussed about data replication and Model Join’s contribution to avoid it, let us proceed with Package Parallelization….
Package Parallelization
Package Parallelization is one of the important execution functionality SAP PaPM Cloud offers in order to have faster runtime.
Now with the Model Join, you can already set up your raw data (without data replication) to be fully ready for package parallelization prior feeding it as input of View or Join.
If for an instance that your package parallelization options or setup is more complicated due to combination of fields coming from two tables, example Field A of Model Table and Field B of Model View then a Join on top of these tables must be created first, to enrich the table which then will be used in the end by the next function for parallelization. We can avoid this situation now by just using Model Join straight ontop of the raw data to avoid replication and to have a more efficient package parallelization experience or setup.
Delta between Model Join and Join
Now that you have a better overview why Model Join is being introduced in SAP PaPM Cloud, I want to get back to one of my first statements where I said Model Join is just like Join.
Aside from the technical and buffering mechanism differences, modeling these two functions in the Environment are somehow similar with slight differences.
In my next paragraphs, let me highlight to you the difference between these two functions with respect to Modeling.
In a technical note, Join is under Processing Functions while Model Join is under Model View. This means, upon successful activation of a Model Join, an SQL view is generated in the underlying database schema of the tenant and is acting more of an Information function (no procedure created, purely projecting what we have from the tables or views). Join processing function on the other hand generates SAP HANA Procedures that then is being executed to populate a temporary Ytable.
Now focusing on modeling, Model Join focuses on simplicity and doesn’t have Input nor Checks nodes. It also does not have the usual Join features intended for processing functions (e.g. Include Original Input Data, Processing Type, etc.). Most importantly, unlike Join function which can have other processing functions as Input, Model Join on the other hand can only use information functions such as Model Tables and Model Views.
Still in modeling perspective, for Auto Filling, “No” is not an option possible to be used in Model Join. Model Join is also behaving as a normal Model View, which in the end will provide all data necessary and will not filter out null values.
If those processing features are needed depending on the users’ needs, then as a suggestion, it might be logical to configure those limited options on a processing function then use Model Join as its Input function before feeding the result to a Query.
Now that we have all the limitations stated, and since Model Join is basically functioning same as Join, you may want to read the documentation regarding Join through this link.
After reading the link provided above, as a bonus, I have prepared an example scenario which will show you how the data from different tables are being combined by Model Join:
Multilevel Rule (Complex Selection) (Implicit)
This scenario shows how Model Join function combines data from three Input Model Tables with Complex Selections and using Implicit as Join Type.
Input
These are the tables that we use as input of our Join Rules:
Input Model Table 1
01: MJ - Product Table 1
| Product Code | Product | Price | Currency |
| P0001 | SHOE | 75 | EUR |
| P0002 | WATCH | 300 | EUR |
| P0003 | SHIRT | 80 | EUR |
| P0004 | SHORTS | 20 | EUR |
Input Model Table 2
01: MJ - Product / Material Table 1
| Product Code | Material Code | Request Order |
| P0001 | M1011 | 5 |
| P0001 | M1010 | 2 |
| P0002 | M1009 | 1 |
| P0002 | M1011 | 3 |
| P0005 | M1012 | 10 |
| P0006 | M1011 | 30 |
Input Model Table 3
01: MJ - Product / Material Table 2
| Product Code | Material Code | Request Order |
| P0001 | M1011 | 5 |
| P0001 | M1010 | 2 |
| P0002 | M1009 | 1 |
| P0002 | M1011 | 3 |
| P0005 | M1012 | 10 |
| P0006 | M1011 | 30 |
In the Signature tab, we don’t maintain anything since the Join Type is Implicit.
| Signature | ||
| Granularity | Selection | Action |
| empty | empty | empty |
Join Rules
| From | |
| Rule | R0001 |
| Description | Level 1 First Result |
| State | Active |
| Icon | Default |
| Scale | 1 |
| Subview | |
| empty | |
| Complex Selections | |
| WHERE (CH_MATC != 'M1001') AND (CH_PROD LIKE 'SHOE' OR CH_PROD LIKE 'WATCH') | |
| Input Function | |
| empty |
To explain the Complex Selection, it simply means we would like to get a result wherein the Fields CH_MATC is not equal to M1001 and we also wanted the result to contain SHOE and WATCH for the Field CH_PROD.
| From | |
| Rule | R0002 |
| Description | Product Table 1 |
| State | Active |
| Icon | Default |
| Scale | 1 |
| Subview | |
| empty | |
| Complex Selections | |
| empty | |
| Input Function | |
| 01: MJ - Product Table 1 (MJU01) |
| Left Outer Join | |
| Rule | R0003 |
| Description | Product |
| State | Active |
| Icon | Default |
| Scale | 1 |
| Subview | |
| empty | |
| Complex Selections | |
| empty | |
| Complex Predicate | |
| empty | |
| Join Predicate | |
| Rule | R0003 |
| Field | CH_PCODE |
| Comparison | Equal |
| Rule | R0002 |
| Join Field | CH_PCODE |
| Input Function | |
| 01: MJ - Product / Material Table 1 (MJV01) |
| Union All | |
| Rule | R0004 |
| Description | Level 1 Second Result |
| State | Active |
| Icon | Default |
| Scale | 1 |
| Subview | |
| empty | |
| Complex Selections | |
| empty | |
| Input Function | |
| empty |
| From | |
| Rule | R0005 |
| Description | Product / Material Table 2 |
| State | Active |
| Icon | Default |
| Scale | 1 |
| Subview | |
| empty | |
| Complex Selections | |
| empty | |
| Input Function | |
| 01: MJ - Product / Material Table 2 (MJW01) |
| Left Outer Join | |
| Rule | R0006 |
| Description | Product |
| State | Active |
| Icon | Default |
| Scale | 1 |
| Subview | |
| empty | |
| Complex Selections | |
| empty | |
| Complex Predicate | |
| empty | |
| Join Predicate | |
| Rule | R0006 |
| Field | CH_PCODE |
| Comparison | Equal |
| Rule | R0005 |
| Join Field | CH_PCODE |
| Input Function | |
| 01: MJ - Product Table 1 (MJU01) |
Join Rules should look like this upon configuration.
After saving and activating Model Join, the result is ready to be shown. Expected Result should be the table below:
| Product Code | Product | Price | Currency | Material Code | Request Order |
| P0001 | SHOE | 75 | EUR | M1011 | 5 |
| P0001 | SHOE | 75 | EUR | M1010 | 2 |
| P0002 | WATCH | 300 | EUR | M1009 | 1 |
| P0002 | WATCH | 300 | EUR | M1011 | 3 |
| P0001 | SHOE | 75 | EUR | M1011 | 5 |
| P0001 | SHOE | 75 | EUR | M1010 | 2 |
| P0002 | WATCH | 300 | EUR | M1009 | 1 |
| P0002 | WATCH | 300 | EUR | M1011 | 3 |
| P0005 | 0 | M1012 | 10 | ||
| P0006 | 0 | M1011 | 30 |
As for additional checking, you can actually take a look at the generated SQL view of the activated Model Join function in SAP HANA Database Explorer for SAP HANA Cockpit.
1. On the upper left corner, choose the Hamburger Menu --> Administration --> Settings
2. Choose the HANA Dashboard url
3. In the Database Overview, choose SQL Console
4. In the SAP HANA Database Explorer, ensure that the database catalog being accessed is the right database by comparing the DB properties' host vs host shown in previous step
5. Expand the Catalog folder underneath the database then choose Views
NOTE: Ensure that you are viewing objects under SAP_PAPM schema (default schema of PaPM Cloud)
6. In the Search Views search box, search for the SQL View name by searching for the function ID
NOTE: Naming convention for the SQL view generated is as follows: <CLIENT><ENV_ID><ENV_VERSION><FUNCTION_ID> e.g. Y105MJTC01MJA01
7. Double click on the SQL view. As you can see, you have two tabs there, Columns and CREATE Statement. Select CREATE Statement. After doing so, the Create Statement that was produced after Model Join activation will be shown.
You can see from below that the statement contains the created YTable, the Fields and etc.
8. Choose Open Data.
9. Contents of the SQL View (as shown in Expected results) are shown and should be exact
NOTE: If insufficient privilege is encountered. Ensure that you are using SAP_PAPM database user (owner user). You can change the database user by right clicking the database entry and choosing Add Database with Different User
Alrighty! I believe that upon reading the basic overview of Model Join function and so as its example, you should now have the idea of how to work with it and also the rationale behind its creation. This is just the beginning, who knows, maybe future blogs about Model Join and some more examples await, so I really recommend that you stay tuned!
For other SAP Profitability and Performance Management related inquiries, you can post your questions through https://answers.sap.com/questions/ask.html and use primary tag: SAP Profitability and Performance Management.
You can also read other SAP Profitability and Performance Management posts via https://community.sap.com/topics/profitability-and-performance-management
Do you have any suggestions, or do you have scenarios in mind that you would like to mention? If in case you got some related questions, please feel free to post a comment below! 😊
- SAP Managed Tags:
