Post reply

Error processing SSAS cube using TMSL via SQL Server Agent Job

  • August 3, 2020 at 4:04 pm

    #3776928

    Hi,

    I am trying to automate SSAS cube processing by having an SQL Server Agent job run a TMSL script.

    I have 2 servers (SSAS where all the cubes live and ETL where all the jobs live).  Both are 2017 version.

    Below is what I tried and the error I am getting:

    1. Created TMSL script that processes a cube
    2. Created a job on the ETL Server with the following specifications:

      1. Type: SQL Server Analysis Services Command
      2. Run as "ETL Proxy" (this proxy is active to the "SQL Server Analysis Services Command" subsystem)
      3. Server: SSAS Server
      4. Command: {"refresh": {"type": "full","objects": [{"database": "SSAS_DATABASE_TO_REFRESH"}]}}

    Running the above command directly in SSMS on the SSAS Server, worked with no problems, refreshing the cube.

    Running the job, however, gave the following error (which I suspect has something to do with permissions, but I can't figure out what additional permissions are needed):

    "Message

    Executed as user: AD\Admin_User_Used_By_Proxy. Microsoft.AnalysisServices.Xmla.XmlaException: The JSON DDL request failed with the following error: Cannot execute the Refresh command: database 'SSAS_DATABASE_TO_REFRESH' cannot be found.. at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.ExecuteStatement(String statement, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalog) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.SendCommand(String command, Boolean withResults, String properties) at OlapEvent(SCH_STEP* pStep, SUBSYSTEM* pSubSystem, SUBSYSTEMPARAMS* pSubSystemParams, Boolean fQueryFlag). The step failed."

    Please advise.

    Thank you!

  • August 4, 2020 at 4:10 pm

    #3777253

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • December 8, 2021 at 1:45 pm

    #3961660

    Hi,

    You must change all services logon account to a user account (that must have access on Analysis Services). The services are: SQL Server, SQL Server Agent and SQL Server Analysis Services.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply