Let us look at one more aspect of validation which is validation of business logic. Most of the time mismatches in the data are due to the wrong business logic.  Business logic may go wrong when consultant misinterprets it or he/she wrongly implemented it. It may happen business users themselves are not sure about the logic. Each one of these reasons may lead to incorrect output of the reports. For example, different users may use different formula to calculate the net profit.

It is always important for a consultant to go through the requirement document created in early stage of the project before starting to code the logic. It may happen that over the course of time the formula changed due to the business policies, in such cases the document should be updated and should get reviewed by the business user and it should be signed off by the user.

To validate the business logic, consultant should divide the logic in smaller parts and start validating each one of them separately. This testing can be done while developing the data model or it can be done after the data model is finished. It is because there will be few logic which will get implemented to generate result at run time.

The method which I use and would recommend to do is that you should load the raw data required by the section of business logic. Once this data is loaded, start putting the required fields on the front end using the filters. Make appropriate filter criteria by selecting relevant values. Use the chart aggregation functions along with KPI object or the best pivot table and see the values and validate the same against the available validation report.

The best visualization which helps in data validation is Pivot table. It is because the calculation written on charts can easily debugged using this chart. If you do not give label to the expression it shows the evaluated values of the search string when dollar sign expression is used. Following screenshot shows you the sample evaluation:

You can see that the expression written uses the dollar sign expression to evaluate the year start date for the maximum date. The pivot table shows the evaluated value, which helps to identify the anomalies in the logic applied.