Process Tabular cube from stored procedure

Question

I have a stored procedure which populates a few tables in a db which is the data source for a tabular SSAS cube. I want to process the cube as a final step in the stored procedure when I am done loading the tables. I found this code:

DECLARE @XMLA XML = '
  <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Process>
      <Object>
        <DatabaseID>' + @Database + '</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Batch>
';

DECLARE @Command VARCHAR(MAX) = CONVERT(VARCHAR(MAX), @XMLA);
EXEC (@Command) AT SSAS;

Which accepts a database name, but what I cant figure out is how to make it run on a particular server or SSAS instance. My stored procedure and cube are on different named instanceS of Sql server. Does anyone know how to either embed the server/instance name in the xmla or run the xmla on a specified instance?

Thanks in advance.

I would create a sql agent job and then run the job from the stored proc. BTW TMSL is the way forward for Tabular cubes — Mitch Wheat

Programnik Programnik · Accepted Answer · 2020-11-17T05:28:03

I discovered that the key is using a linked server (and TMSL, thanks Mitch Wheat):

DECLARE @TMSL VARCHAR(MAX) = '{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": ' + @DataBaseName + '
      }
    ]
  }
}';
 

EXEC (@TMSL) AT SSASTABULAR; -- linked server name

SSASTABULAR is a linked server pointing to my ssas cube server instance.

Process Tabular cube from stored procedure

2 Answers