Technical Articles
Report or Input Form with Many-to-many Relationship between 2 Dimensions (BPC Standard)
It’s a common question in EPM and BPC NW tags: How to create a report with many-to-many relationship between members of 2 dimensions?
For example there are 2 dimensions: ENTITY and ACCOUNT
For each ENTITY base member there is a list of valid ACCOUNT base members to be used in reports and data entry.
| ENTITY | ACCOUNT |
| BE1000 | 2020202020701,2020202020608,2020202020604,2020202020602,2020202020601, 2020202020501,2020202020405,2020202020303,2020202020301,2020202020106, 2020202020101 |
| BE1100 | 2020202020701,2020202020608,2020202020606,2020202020605,2020202020602, 2020202020405,2020202020404,2020202020403,2020202020402,2020202020303, 2020202020201,2020202020106,2020202020103,2020202020102,2020202020101 |
| … | … |
Any ACCOUNT member can be related to multiple ENTITY members.
Simple answer – not possible, there is no dedicated table to store many-to-many relationship of master data. For one-to-many relationship some property can be used, but for many-to-many property will not work!
There is a workaround explained in the following example. This workaround is not perfect (report can be slow for huge number of members) but looks like it’s the only possible solution.
Solution:
We have to create an input form with the matrix: ENTITY in Column Axis, ACCOUNT in Row Axis. Other dimensions are in Page Axis with some dummy members (unused data area). In my sample ENTITY = BE, ACCOUNT = INACCT and TIME = PERIODS.
Result:
This matrix is filled with “1” for the valid combinations of ENTITY and ACCOUNT and data is saved to the model.
Then we can create a real report or input form that will be a combination of special report to get correct list of rows and the real report that will use shared row axis from the special report.
Special report (Default Report 000) is created with ENTITY and ACCOUNT in Row Axis, some dimension in Column Axis (TIME in this sample – same member as in matrix) and Page Axis containing same dummy members as in matrix.
Report Options:
Empty lines will be removed, only valid combinations of ENTITY and ACCOUNT will be shown in the Row Axis.
Also it’s better to set User Option (will help with huge number of records):
Then the real report or input form is created. Row Axis has to be shared with Special Default report:
Page and Column Axis members are selected as required.
Result:
Report or Input Form with many-to-many relationship is created!
Hello Vadim,
Thanks, really awesome work ...!! Thanks Man ...!!
Regards,
ANDO
Hi Vadim,
this is a nice example about the different approaches of BPC Standard and BPC Embedded. In BPC Embedded one would create a characteristic relationship based on ENTITY and ACCOUNT and put the n:m relation in a DataStore-Object. In the query (report) one then would use the setting 'access mode for result values' based on characteristic relationships to create all valid combinations for ENTITY and ACCOUNT even if no posted data exist.
Regards,
Gregor
Hi Gregor,
I suspected that there was some mechanism in BPC embedded to solve it. Can you provide some sample on: "create a characteristic relationship based on ENTITY and ACCOUNT and put the n:m relation in a DataStore-Object"
B.R. Vadim
Hi Vadim,
first one creates a direct-update DataStore-Object (DSO for short), it is not relevant whether this DSO is planning enabled or not. But if it is one can maintain the needed combinations e.g. with an input-ready query. The DSO has ENTITY and ACCOUNT as key fields (to be able to maintain an n:m relation).
In transaction RSPLAN one creates a characteristic relationship based on the DSO. Then the system uses the records contained in the DSO to check the valid combinations; the relation can also be used to create combinations: in this case the system reads the DSO with the filter used in the query to create all valid combinations. The corresponding switch has to be set in the query.
Finally one has to load all the valid combinations into the DSO. To do this there exist many options, e.g. one can use WHM (if the DSO is not planning enabled) or one can use an input-ready query to maintain the valid combinations (if the DSO is planning enabled). In the latter case the input-ready query can be similar to the 'matrix' in your example. One needs an aggregation level on the DSO to be able to define the input-ready query.
In this way the planning data and the 'rules' to define the valid combinations of ENTITY, ACCOUNT are completely separated. The validity of the combinations is checked automatically in all input-ready queries and planning functions.
Regards,
Gregor
"one can use an input-ready query to maintain the valid combinations (if the DSO is planning enabled)" some sample screenshot can be useful for people using BPC embedded. Or a new blog 🙂
Thanks a lot Vadim for this magic!!!!!!! Still your blogs are helping a lot 🙂