Calling MDX from a SQL Server Stored Procedure

I work for an online media company which allows our customers to login to a website and check their numbers. For the last several years all their activity reports have been pointed at SQL Server aggregated tables. While this worked, it wasn't the ideal solution. Reports often timed out and the database server would have a high load throughout the day, specifically early month while everyone was checking month end numbers.

 

Eventually my boss approached me about speeding up the reports and possibly pointing them at a cube. However, there could be no changes to the report files or how the data was being called (no front end changes). All the modifications had to be done in the stored procedure.