Looking for a validation rule to validate data after transformation and before loading to the cube.
I have Import/validate/Load workflow.
After the validation step , I want to run a business rule that can do the following -
1. Sum up the amount for all records with the same Product code in the transformed table.
2. Check if SUM = 0 for individual product code.
3. If step2 is validated for all product code , then proceed for LOAD step, else error out.
PS- I know this can be achieved via a confirmation rule but i want to validate before loading to the cube.
One idea that i thought is to execute a validation in the transformation rule but that validation limits to record by record whereas I am looking for validation post all transformation.
Thanks!
Could you not use a Target Derivate Rule for this?
A target Derivative Rule allows you to run logic on the post-transformed data. You could sum your records and verify they are equal to zero using the Check Rule Derivative Type. If it fails you won't be able to load to the Cube.
If you have access to the Golfstream App, there is an example of how to construct the Derivative Rule and the Complex Expression to evaluate what to do when the amount = 0 and what to do when it does not = 0
