Many thanks for your valuable inputs. The final solution we are trying to take to address the current reporting solution is to consider the current backup copies as the ODS and build a data Warehouse. By taking this approach the current reporting system is still intact and a new DWH in parallel will be developed. Going forward, new reports to be build on the data warehouse.Slowly decommission the existing reports accessing the backup copies. Interesting thing which I found recently was there is MI reporting on live source systems. Hence, the data warehouse approach. Hopefully, I am on right track.

Building Cubes solution is in consideration but not immediately but may take 6 months later. I feel personally cubes are way to go forward. Having Cubes built for individual departments for MI reporting is a good solution.

Regards,

Kamal