asked
How to implement multi tenant architecture in SSAS
We have a **multi tenant** data warehouse (SQL Server 2012 Standard) and I want find out how we should **implement that in SQL Server Analysis Services**. Currently the source data in the data warehouse is in a separate schema for each client. Like this: SELECT * FROM Datawarehouse.Client1.FactSales. There is a separate SSAS database for each client and 3 cubes in each SSAS database. When we have a new client, we (1) script an existing SSAS database, (2) find and replace the client name, and then (3) run the script. That builds a new SSAS database for the new client. We currently have 3 clients using our SSAS cubes and plan to have about 8 cubes with about 20 to 30 clients using them. Although this works, **is this the best way to implement multi tenant architecture in SSAS?** I want to confirm this before we starting add more clients and cubes. And our solution need to be **secure**: client1 cannot see Client2's data
Looking forward to seeing something on this. Could probably extend the dynamic security model from this tutorial, replacing "Sales Territory" with "Customer" -
http://msdn.microsoft.com/en-us/library/hh479759.aspx That would at least allow all customers to share the same SSAS database and cubes
SSAS roles is where you can do this
15 People are following this question.
