Beware of Rules with Circular References
Posted by Alan on August 15, 2008
So we found out something fun the other day. We had a model that was up and running. Everything was working fine, we were creating input sheets and submitting data. Life was good. Then stuff stopped working. We created a new input form and tried to save it and got errors. We tried to browse the cube in SQL Management Studio and it froze everything. We tried to browse the data in the cube with Excel and it hung Excel. We got error messages in Event Viewer. We opened the model in the Planning Business Modeler (which we still could) and checked it out and it all looked fine so we deleted the cubes and redeployed and then found that this process just would not finish.
So we did the logical thing and started to freak out. In the back of our minds was the total number of late nights and weekends it would take to get us back to where we were if we had to do a complete rebuild from scratch. Lesser men would have cried.
Then we calmed down and started to backtrack and look at what changes were made between when things last worked and when they stopped working. It turns out that three new rules had been created and deployed and then things stopped working. So we opened up PBM, deleted the rules, went into Analysis Services and deleted the cube and then redeployed — it all worked again!
So what had happened? Well one of the business rules that was written referenced itself. In simplest terms, we had a formula like “A = A + B”. So every time “A” changed, then it would take that value and add “B” to it which would mean that the system saw that “A” had changed so it had to add “B” again. This would go on forever and ever. The funny thing is that we did validate the rules using that function in the PBM and later found out that the validation routine only checks if your syntax is correct, not it your formula makes sense or not. I am guessing that my cube was actually processing just fine, but it was literally going to take forever to finish.
I hope that the PPS development team will add the ability to check for circular references when rules validate in v2 of PPS. Until then — watch yourselves.