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
– Mitch Wheat, Commented Nov 17, 2020 at 4:42

Programnik · Accepted Answer · 2020-11-17 05:28:03Z

1

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.

answered Nov 17, 2020 at 5:28

Programnik

1,5591 gold badge10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mitch Wheat Over a year ago

rather than use a linked server, I think a cleaner approach is to create an agent job, and then run that job from SP

Kupokev · Accepted Answer · 2020-11-17 05:51:06Z

-2

You can create a job in SQL Agent to process the cube. You can then call the SQL Agent job from your stored procedure using sp_start_job.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-start-job-transact-sql?view=sql-server-ver15

Doing it this way allows you to process the cube from different stored process without having to store the processing code in each one of them. It also makes it easier to update incase your cubes are moved to a different server. You only have to update the SQL Agent Job to point to the new server.

edited Nov 17, 2020 at 5:51

answered Nov 17, 2020 at 5:36

Kupokev

1891 silver badge8 bronze badges

Collectives™ on Stack Overflow

Process Tabular cube from stored procedure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related