Free text integration in BW models/reports via input enabled queries
Abstract: Maintaining comments (text/notes) in BW planning and analytic applications.
Motivation: The standard usage of manual planning is based on entering values of planning measures (key figures-based planning). However, adding texts in planning book allows business users to store meaningful information on activities during various stages of planning process execution.
Summary: In this blog, I will show step by step method to enter comments from an input enabled BW query on BW 7.x/B4HANA real time cubes and ADSOs. A preferred approach should be to use a direct update input enabled ADSOs or Standard Planning ADSOs allowing an overwrite option for comments. However, a real time info-cube or an ADSO of “Planning on Info cube Like” can be used as well for saving comments.
This feature can be used in variety of planning applications where business can enter comments during planning cycle. Additionally, this feature can be handy in logging comments by customer service team on open sales orders, delivery delays or purchasing organization or quality complaints department.
The model displayed in this blog is solely focused on text maintenance, however in a business real scenario, the direct update ADSO will consist of all reporting relevant fields and the composite provider can club multiple info-providers and combine text display in standard reports with other data sets.
Step By Step:
- Create an info-object of characteristic type w/o Alpha conversion checked
2. Create a direct update ADSO for planning
3. Add desired info-objects (in this demo, 0MATERIAL is added in ADSO) and text info-object (ZNOTES). Please make sure that ZNOTES is checked for “Use Characteristic as Key Figure”
4. Please do not add ZNOTES as a key field in ADSO. All remaining characteristics should be marked as key fields and activate ADSO.
5. Create a new composite provider and add the planning ADSO to it.
6. Map ZNOTES object as characteristic field as well as key figure to the target fields. The characteristic field can be used for reporting purpose as a standard field. The Key Figure field is used for entering text in the planning query
7. Activate the composite provider
8. Create a new aggregation level on the composite provider
9. Add all the necessary planning relevant fields in the aggregation level including the ZNOTES Key Figure. Please do not include ZNOTES characteristic field from composite provider in the aggregation level.
10. Create a planning query on the aggregation level.
11. Add all characteristics from aggregation level into the planning query. Also add required key figures including ZNOTES in the key figure column as an input enabled key figure.
12. Ensure that the planning ADSO is in plan mode
13. Open the planning query in Analysis For Office
14. Enter the values of characteristics and key figures for planning. In this example, we have not added any key figure (amount, quantity, price etc) in the planning model. Only ZNOTES is being used as a key figure for planning.
15. Users can enter free texts in the Notes column and save for record. Additionally, a comment can be deleted as well or changed and re-saved.
16. Once notes and other plan data are entered, save the results of planning.
17. As a best practice, change the query results to display (read only mode). This is not mandatory.
18. In order to validate data, execute LISTCUBE in back end and display ADSO data.
Above steps display how a characteristic info-object (or info-objects) can be used in a data model to behave as an input enabled key figure (or key figures) to writing back text into an info provider. This text write back capability of BW/IP input query can be useful in many planning applications as well as analytic (read only) data flows/models and reports for OTC, Accounting, Quality Management applications.