It occurred to me the other day that it might be interesting to manage aggregates in an SQL Server database by actually moving the aggregate values to Analysis Services and having the Stored Procedures from SQL Server access the Analysis Services cube by using straight T-SQL. I see that it is theoretically possible to do this by creating a Linked Server to Analysis Services and passing the MDX query as a string to the Analysis Services by using the OPENQUERY syntax. Would this be the best way to do it? What are the shortcomings? What is performance like? I've had bad experiences with OPENQUERY before in terms of performance, but I've never used it on Analysis Services.
Keep in mind that this is an existing application that already accesses these aggregates inside the SQL Server database itself, so I would have to move these aggregates to Analysis Services and then create a view or some similar structure which gets these aggregates and makes them available to T-SQL database objects.
Has anyone out there tried this? Does it actually work? Are there better ways to go about this? Any suggestions?
