SSAS Tabular - Deploy to multiple models

0
votes

We are trying to create a SSAS tabular model for 60-100 customers.

In regards to creating a single model and process all customer's data is time consuming (until the data refresh is finished,each and every customer need to wait for the latest data - we update every 15 min).

However creating multiple tabular models is easy to re process and trouble shoot but difficult to maintain or deploy changes. If I need to add new measures or tables,I would like to apply to all the models.

I was wondering if anyone can suggest best way to deploy changes/additions across different tabular models.

1
deploymentssasssas-tabular
Refreshing every 15 minutes sounds like overkill. Have you tried DirectQuery mode? docs.microsoft.com/en-us/sql/analysis-services/tabular-models/… – vercelli
Maybe you can partition by customer and refresh partitions instead of the whole model – vercelli

1 Answers

1
votes

If you've worked with SSIS this can be used to deploy across multiple sites. An overview of this is below. What this will do is take a list a server names that you supply, iterate through them, and execute the DDL for the updated Tabular model to each one. This same method can also be used for cube processing, with the create DDL replaced with a processing script. If the model is deployed to a server for the first time ensure that it's processed before it's queried or used by any client tools, and make sure the processing of changed objects is handled accordingly as well.

  • When connected to SSAS in SSMS, right-click the model, select Script > Script Database As > Create or Replace To > then choose where to output the Script. Note that this will not include the password for security purposes and this will need to be handled accordingly.
  • Create an SSIS package. In the package create an Analysis Server Connection Manager. This can be set to a server where this Tabular database currently exists.
  • Create a String variable and leave in blank. This can be called DeployServerName. Also create an object variable, which can be called ServerList. On the SSAS Connection Manager, go to the properties window (press F4), then select the Expressions ellipsis. On the window that comes up, choose the ServerName property and set the DeployServerName variable as the expression. This will allow the server name to change to multiple servers for deployment.
  • Add an Execute SQL Task in the data flow. This is where you will get the server names to deploy to. If they're stored in a master/lookup table just select the column holding the server name as the SQL statement. You can also add the destination server names individually with UNIONs selecting plain text.

Example

SELECT 'Server1' AS DestServer
UNION
SELECT 'Server2' AS DestServer
  • On the Execute SQL Task, set the ResultSet property to Full Result Set. Then on the Result Set pane, enter 0 for the Result Name and the object variable created earlier (ServerList) for the Variable Name field.
  • Next create a Foreach Loop after the Execute SQL Task and connect this to it. Use the Foreach ADO Enumerator Enumerator type and select the object variable (ServerList) as the ADO Object Source Variable. On the Variable Mappings pane, place the string variable (DeployServerName) at Index 0.
  • Inside the Foreach loop add an Analysis Services Execute DDL Task. Use the SSAS Connection manager you created as the connection, Direct Input as the SourceType, and enter the script generated in SSMS as the SourceDirect statement.