Deploying SSAS for a multi-tennant database

Question

We have a mulch-tennant database that uses schemas in SQL Server to separate objects for different tennants, for example if we had two tennants "client_a" and "client_b" then the database structure might look a little like this

Each "client_X" schema is identical.

We want to deploy SSAS for this database - our current approach is to create a single analysis services database for the SQL database and create multiple cubes, one cube for each tennant in our database (so the cubes might be called client_a and client_b), however inspection of the data source view in SSAS indicates that the DSV is tied to specific schema.

Can we re-deploy an existing DSV / Cube definition against a different SQL Server schema (in order to support multi-tennancy against our database), and if so, how?

Answer 1

I do not think there is an out-of-the box possibility to do that.

What you could do would e. g. be to copy the project of one client to a new client, and then e. g. via XSLT change the DSV to replace the msprop:DbSchemaName attributes. I would not think that there would be references to the schema in other places of the project than the DSV - as long as you do not have partitions, where the SQL queries could again contain the schema explicitly, and if you have, you would have to adapt these as well. Of course, this is not documented anywhere, but I would assume that it would work.

A further possibility would be to use AMO to copy the definitions. This could be implemented in a way that you only have one project with multiple cubes, but if you have not worked with AMO, there will be a steep learning curve.