In every planning application it is of utmost importance to make sure that the plan data that is saved to the system is correct. Let us have a look at some examples of incorrect data and discuss how we can make sure with BPC for NW 10.1 Embedded that incorrect data will be identified.
Example 1: A user enters a new data record and enters a dimension member that does not exist in the master data table
This check is handled automatically by the system. If you are using Analysis for Office and the user enter the wrong value in an input enabled query then the system will inform the user as soon as he has stopped typing and moved the cursor from the cell. When entering a wrong value for a variable or when using other front ends then the system will send an error information after the user has pressed ‘transfer data’/check.
The system also checks automatically whether the data entered for a measure has the correct format (check against the definition of the measure). It will detect if the user enters a text instead of a number, if the number of decimals is incorrect or if the number exceeds the technical limits.
Example 2: The user enters new records for calendar day January 2nd 2017 and calendar week 2.
It is easy to see that this must be wrong. Luckily again you do not have to set up anything in the system to detect this error. The system has built in check for the relationship between time dimensions. As we have multiple time characteristics in BW we have more than 40 of these automatic checks. For some of these checks two time characteristic are enough (as in the example), for some three time characteristics are connected (say two digit period 01, calendar year 2017, and 6 digit period 01.2017) and for some cases no check is possible – such as calendar week and month (a calendar week can have days in two different months).
Example 3: The user enters a new data record for the sales region ‘Europe’ and the country ‘China’.
Obviously this is a wrong combination. But what about sales region ‘Europe’ and country ‘Israel’ or ‘South Africa’. Both countries are not part of Europe, but in some companies they are handled by the sales organization for Europe. Obviously here business logic comes into play and the BW system cannot predefine this special logic. This is why we have introduced the characteristic relationships. You use them in order to define relationships between dimensions/characteristics. Those relationships mimic your business rules and they are used to define which combinations of dimension members are allowed. Characteristic relationships cannot be used to define checks on the values of measures. Characteristic relationships provide three features:
The characteristic relationships might change for some (business-) reasons – say we have a re-organization in the company – and forbidden combinations might exist on the data base. You can either leave them as they are – in order to keep the correct history of the data – or use some predefined planning functions either to delete incorrect combinations or to repost them so that they comply with the new business rules.
Characteristic relationships are now also checked in Analysis for Office in the value help for creating new records.
Example 4: The user enters some data that is not inside an allowed value range
In this example we have a defined range in which the value for a certain measure has to be contained. The check depends only on the value of the measure, for example the user should not be able to enter any negative values for the sales revenue. As the check just depends on the measure itself we can use so-called exceptions in the BW query for such checks. When the user enters a number that is not allowed then the system can change the color of the data cell.
Setting for which structutre member the exception should be defined:
Definition of the exception:
Result in Analysis for Office:
Instead of using query exceptions we can use conditional formatting in Analysis for Office as well.
Definition of the conditional formatting:
Result in the data grid:
It has to be kept in mind that in both cases the user still can save the data. Query exceptions/conditional formatting cannot stop the user from writing the incorrect data to the data base. If you want to make sure that no incorrect data can be written into the data provider you have to use the same technique as in our following example. Also query exceptions work only within queries and cannot be used to check data that is generated by a planning function.
Example 5: The user enters some revenue data without maintaining the corresponding price
In this example we have the case that the records might be correct in itself (correct combination of dimension members, measure within the defined limits) but not in the business context. Thus for our validation we need information from further data records, not just the ones available in the query.
We can achieve this by creating a planning function (formula, ABAP-function, or sql-script function) that reads all the necessary data, implements the check and send an error message (type ‘E’) if an inconsistent situation is found.
Example for a formula:
Now how can you make sure that the planning function is executed any time the user wants to save the data? First of all, put the planning function into a planning sequence. When using Analysis for Office you could use a macro that first calls the planning sequence and then saves the data if everything is fine. But you would have to make sure that in any workbook having write access to the set of data in question implements this check. This is why it is possible in BPC Embedded to register a planning sequence centrally on the InfoProvider. Whenever a planning application writes data to this InfoProvider the planning sequence is executed automatically. If the sequence fails (sends an error message) the system will not save the data and the user will have to adapt the data in such a way that it matches the business rules. If the user should be able to save the data anyway and just should be informed about the problems with the data you can simply send warning messages from your planning function.
As you want to make sure that only necessary checks are done you should use the option to run the planning sequence only on changed records as unchanged records do not need to be checked anymore.
Definition of automatic planning sequence in the InfoProvider maintenance (with delta mode set active):
If a planning function sends a message type 'E' then the system will display several messages - first of all the message that informs the user that the execution of the planning function failed. This might be misleading for the user. If you want to adapt which messages are displayed you can use the techniques explained in the blog How To Suppress Messages Generated by BW Queries. In the comments it is also explained hoew to handle messages from planning functions.
Example 6: The user should be forced to enter a comment for any change of a value of a measure
In some applications it is crucial that the user enters a comment as soon as he changes a value in the system. We see this requirement for example in public sector projects. In BPC Embedded we can enter short comments by using a DataStore Object with a characteristic as key figure. With the help of a sql-script based planning function it can be checked whether there is such a comment for every measure value within a given selection. The approach is quite similar to the one for the previous example – again you would probably run this planning function in a planning sequence that is triggered automatically when saving the data. But as in this example some sql-script programming is necessary we will dig deeper into this topic in the following blog: BPC 10.1 Embedded: Mandatory Short Comments.
Example 1: A user enters a new data record and enters a dimension member that does not exist in the master data table
This check is handled automatically by the system. If you are using Analysis for Office and the user enter the wrong value in an input enabled query then the system will inform the user as soon as he has stopped typing and moved the cursor from the cell. When entering a wrong value for a variable or when using other front ends then the system will send an error information after the user has pressed ‘transfer data’/check.
The system also checks automatically whether the data entered for a measure has the correct format (check against the definition of the measure). It will detect if the user enters a text instead of a number, if the number of decimals is incorrect or if the number exceeds the technical limits.
Example 2: The user enters new records for calendar day January 2nd 2017 and calendar week 2.
It is easy to see that this must be wrong. Luckily again you do not have to set up anything in the system to detect this error. The system has built in check for the relationship between time dimensions. As we have multiple time characteristics in BW we have more than 40 of these automatic checks. For some of these checks two time characteristic are enough (as in the example), for some three time characteristics are connected (say two digit period 01, calendar year 2017, and 6 digit period 01.2017) and for some cases no check is possible – such as calendar week and month (a calendar week can have days in two different months).
Example 3: The user enters a new data record for the sales region ‘Europe’ and the country ‘China’.
Obviously this is a wrong combination. But what about sales region ‘Europe’ and country ‘Israel’ or ‘South Africa’. Both countries are not part of Europe, but in some companies they are handled by the sales organization for Europe. Obviously here business logic comes into play and the BW system cannot predefine this special logic. This is why we have introduced the characteristic relationships. You use them in order to define relationships between dimensions/characteristics. Those relationships mimic your business rules and they are used to define which combinations of dimension members are allowed. Characteristic relationships cannot be used to define checks on the values of measures. Characteristic relationships provide three features:
- Check in the records (existing on the data base, entered by the user manually or generated by a planning function) whether the combination of dimension members comply with the defined rules. If ‘forbidden’ combinations exist in the data base the system will display them but they will not be input enabled.
- Create all records (within a given selection) that comply to those rules
- Automatically fill in dimension member that have not been used in the current aggregation level (say enter only the country, the system will fill in the sales region automatically)
The characteristic relationships might change for some (business-) reasons – say we have a re-organization in the company – and forbidden combinations might exist on the data base. You can either leave them as they are – in order to keep the correct history of the data – or use some predefined planning functions either to delete incorrect combinations or to repost them so that they comply with the new business rules.
Characteristic relationships are now also checked in Analysis for Office in the value help for creating new records.
Example 4: The user enters some data that is not inside an allowed value range
In this example we have a defined range in which the value for a certain measure has to be contained. The check depends only on the value of the measure, for example the user should not be able to enter any negative values for the sales revenue. As the check just depends on the measure itself we can use so-called exceptions in the BW query for such checks. When the user enters a number that is not allowed then the system can change the color of the data cell.
Setting for which structutre member the exception should be defined:
Definition of the exception:
Result in Analysis for Office:
Instead of using query exceptions we can use conditional formatting in Analysis for Office as well.
Definition of the conditional formatting:
Result in the data grid:
It has to be kept in mind that in both cases the user still can save the data. Query exceptions/conditional formatting cannot stop the user from writing the incorrect data to the data base. If you want to make sure that no incorrect data can be written into the data provider you have to use the same technique as in our following example. Also query exceptions work only within queries and cannot be used to check data that is generated by a planning function.
Example 5: The user enters some revenue data without maintaining the corresponding price
In this example we have the case that the records might be correct in itself (correct combination of dimension members, measure within the defined limits) but not in the business context. Thus for our validation we need information from further data records, not just the ones available in the query.
We can achieve this by creating a planning function (formula, ABAP-function, or sql-script function) that reads all the necessary data, implements the check and send an error message (type ‘E’) if an inconsistent situation is found.
Example for a formula:
Now how can you make sure that the planning function is executed any time the user wants to save the data? First of all, put the planning function into a planning sequence. When using Analysis for Office you could use a macro that first calls the planning sequence and then saves the data if everything is fine. But you would have to make sure that in any workbook having write access to the set of data in question implements this check. This is why it is possible in BPC Embedded to register a planning sequence centrally on the InfoProvider. Whenever a planning application writes data to this InfoProvider the planning sequence is executed automatically. If the sequence fails (sends an error message) the system will not save the data and the user will have to adapt the data in such a way that it matches the business rules. If the user should be able to save the data anyway and just should be informed about the problems with the data you can simply send warning messages from your planning function.
As you want to make sure that only necessary checks are done you should use the option to run the planning sequence only on changed records as unchanged records do not need to be checked anymore.
Definition of automatic planning sequence in the InfoProvider maintenance (with delta mode set active):
If a planning function sends a message type 'E' then the system will display several messages - first of all the message that informs the user that the execution of the planning function failed. This might be misleading for the user. If you want to adapt which messages are displayed you can use the techniques explained in the blog How To Suppress Messages Generated by BW Queries. In the comments it is also explained hoew to handle messages from planning functions.
Example 6: The user should be forced to enter a comment for any change of a value of a measure
In some applications it is crucial that the user enters a comment as soon as he changes a value in the system. We see this requirement for example in public sector projects. In BPC Embedded we can enter short comments by using a DataStore Object with a characteristic as key figure. With the help of a sql-script based planning function it can be checked whether there is such a comment for every measure value within a given selection. The approach is quite similar to the one for the previous example – again you would probably run this planning function in a planning sequence that is triggered automatically when saving the data. But as in this example some sql-script programming is necessary we will dig deeper into this topic in the following blog: BPC 10.1 Embedded: Mandatory Short Comments.
- SAP Managed Tags:
3 Comments
| User | Count |
|---|---|
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
