question

craighb avatar image
craighb 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
ssasolapcubes
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·

1 Answer

·
craighb avatar image
craighb answered
SSAS roles is where you can do this
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.