Query less Reporting in BPC embedded
In the last blog on inprovement of the Planning Engine BPC Embedded Model with BW 7.50 SP4 we mentioned the possiblities to create local query in following use cases.
- Build new queries
- . On existing central provider
- aDSO, Cube, DSO
- central composite provider
- On local provider
- . On existing central provider
- Extend central IT query
- Reporting Query
- Plan Query
However, the local query also modelled in the BPC web admin is still a query which needs to be modeled. In principle it is aquivalent as the central to the report builder in the EPM client for the standard model.
But there is also a way to build those reports in AO by starting from the default query of any InfoProvider like cube or aDSO. You can choose then in AO the drill down, select the relevant members you want to see and add local formulas or restricitions.
For central InfoProvider this is shown in following video .
Often I hear that you still need a central modeled object and cannot start with excel files directly. Already with BPC10.1 based on BW 7.4 we offer the local provider. A user can upload local csv files and create a local provider and then start the reporting on top of this from AO. This is very pragmatic approach for fast prototyping.
It is shown in following video as example.
In case of planning even an aggregation level on lowest level can be generated and used for input ready reports
Is this enough to build good reports. I honestly do not know yet but you might provide me some feedback on how far once can get. Just try it out. Best is to use one of the latest build of AO to also use newest features like local created restrictions.
Hello Frank,
like in your other Blog entry "Planning Engine Improvements for BPC Embedded Model, PAK and BW-IP with BW 7.50 SP4" the videos are not accessible from our end. It is linked to http://go.sap.com/index.html instead of a video. Perhaps you can check it.
On the other side it is very interesting, to see what is happening with the planning functionality. 🙂
Greetings
Jens
Hi Jens,
as we figured out the links to video.sap.com are only accessible within SAP network. I know uploaded the to you tube and exchanged the link. So it should now be accessible
Regards
Frank
Hi Frank,
Documentation says that local restrictions can be used for reports only. Is using them for planning templates on the roadmap?
Thank you,
Gersh
Hi Gersch,
yes this is on the roadmap.
We alrady implemented to create non input ready key figures on plan enabled queries.
We are just about to evalutate the possibilities to extend and make the restrictions input ready. What will be working is to create input enable restrictions on top on input ready base key figures. But we also think to create them on not input ready key figures. However we can have problematic situation with respect to locking and authorizdation.
Do you have any Ideas on the following
==Limitations on Input-ability due to Authorizations and Locking==
In general one could think about creating an inputable selection based on a read-only selection (so to say choosing the input-ability freely based on user wishes). The following combinations are possible:
base | dynamic | base | newly needed |
selection | selection | authorization | authorization | comment
-----------+-----------+---------------+----------------------------------------------------------
read-only | read-only | read | - | OK, dynamic selection is subset of base
read-only | inputable | read | change | -> problematic
inputable | read-only | read, change | - | OK, OLAP also checks read authorization for plan queries
inputable | inputable | read, change | - | OK, dynamic selection is subset of base
As currently the dynamic selection is only a subset of the base selection (it further restricts the base selection), no new data becomes accessed via the dynamic selection, that is not already (aggregated) thre in the base selection. Therefore we do not expect any problems with authorizations nor locking, as long as the inputability of the dynamic selection stays the same as that of the base selection.
In addition creating a read-only selection based on an inputable selection is also fine, because the analytic engine checks read authorizations for the complete query, and additionally change authorization for the inputable query part. Therefore no additional authoizations are needed.
The problematic case is, when an inputable selection is created from a read-only selection. Here two problems are possible:
#Insufficient authorizations: Change authorization is needed in addition. If this check fails, the complete query becomes read-only because of missing change authorization
#Additional locking: An additional part needs to be locked. If this check fails, then the complete query becomes read-only.
In both cases adding a selection might massively impact the status of existing selections
==Possible Solutions==
===Authorization Check and Lock check during add_selection===
Authorizations and locking are normally checked during query runtime. In the add-selection step the authorization check is quite difficult but may be possible in the one structure case. It is literally impossible in the 2 structure case.
The locking is more difficult. Maybe locking is also possible in the one structure case by adding "preliminary" locks for the new selection, and later (with the new generated query) replacing them by "final" locks.
In the 2 structure case we could also only check the current selection, as it is a superset of all cells that are created by combining the 2 structures, and therefore if authoizations and locking work for the dynamic selection, they will also work for all cells created from that dynamic selection. But it might be that we forbid adding an inputable dynamic selection because of an authorization or locking problem that does NOT occur in the finally generated query, because this check might be too rough.
===Problems during end-of-definition===
During the end-of-definition step we do not expect any errors to happen. If errors happen here, RSBOLUP/frontend already have incorporated the newly added cells into their state, and it is not possible to recover from an error by rolling back to a prior state without newly the added selections. Therefore errors during end-of-definition either can be handled with keeping the newly added cells, or the query has to be aborted.
If we are not able to perform all the checks during add_selection in a way that we can guarantee authorizations and locking for the added cells, one possible strategy would be:
#Generate the new query
#perform authorization check for planning
#perform locking
#In an error occurs during authorization check or locking, change ALL newly added cells to read-only and reinstantiate the new query. Now it has to work! This way the user does not get what he wants, but all the existing cells are not influenced by the newly added cells, and the state of analytic engine and RSBOLUP/frontend are still in sync with respect to the new structure elements.
Ideas?