Tracing tabular refresh errors

ssastabular-model

I have a tabular cube on an SQL Server 2017. The cube is refreshed with a full refresh command in a Server Agent job. The job runs on a schedule.

When I check the history of the job I see that it went through successfully, so everything is "green".

When I check the tables of the cube one of the tables is not processed well, it does not contain any data. Now with a manual processing of only this table the problem is solved. So it is not a table config/definition issue.

Where can I find more information about error message why this table is not processed in the automated full refresh command?

Best Answer

You can use Extended Events to track the processing history of a Tabular model. The ProgressReportBegin and ProgressReportEnd events report processing information, per the documentation. SSAS extended events are somewhat different than those in SQL Server. After creation, an extended event is active. To stop the event it must be deleted. An extended event can be created through an XMLA request in SSMS using the XMLA query editor. The following creates an extended event using the ProgressReportEnd event. Multiple events can be included in single event session. For instance, you could create a session with the both the ProgressReportBegin and ProgressReportEnd events. An XMLA command to delete this event is below as well. There are several ways to access the results which the target property controls. A couple options are viewing the results in SSMS (event_stream) and writing these to a file (event_file). In this example, the results are written to a local file using the event_file. The contents of this file (processing activity) can be viewed in SSMS. To do this right-click the file, select Open With then choose SSMS. To delete an event use the ID of when the event was created as the TraceID in the delete request.

Create Extended Event:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ObjectDefinition>
    <Trace>
      <ID>XE_Process</ID>
      <Name>XE_Process</Name>
      <XEvent xmlns="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
        <event_session name="XE_Process" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
          <event package="AS" name="ProgressReportEnd" />
          <target package="package0" name="event_file">
            <parameter name="filename" value="C:\Test\XE_Process.xel" />
            <parameter name="max_file_size" value="4096" />
            <parameter name="max_rollover_files" value="1" />
            <parameter name="increment" value="1024" />
          </target>
        </event_session>
      </XEvent>
    </Trace>
  </ObjectDefinition>
</Create>

Delete Extended Event:

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <TraceID>XE_Process</TraceID>
  </Object>
</Delete>
Related Question