Don't Always Blame The Data

Just a quick post on this error I received yesterday. 

While I was working at a client, the application manager made some changes to a Slowly Changing Dimension of type 2 with effective dates, as mentioned here. This dimension contains the organisational units from the client.

A little while later a part of the financial report I made, was broken. After executing the report (or actually the procedure of the dataset in SSMS) it gave me the following error message:

The maximum recursion 100 has been exhausted before statement completion.

Although this answer on SO mentions the MAXRECURSION option at the end of the CTE, that doesn't solve my problem. The poster mentioned he had a circular reference in his data, what could cause my report to break also.

After investigation of the dimension I couldn't find any problems so I was back at the report and looked at the procedure of the dataset. I started executing parts of the dataset and it turned out to be a code issue instead of a data issue:
The anchor part of the recursive CTE that creates a hierarchical tree of the organisation didn't take into account the effective dates of the SCD and therefore creating a circular reference between two versions of an organisational unit. It kept on trying to build the tree before failing until the 100th recursion, as mentioned in the error message.
My solution was to add the effective dates of the SCD to the CTE and after that the procedure and the report ran fine again. Everyone happy :)