SSAS Tabular–per table LastProcessed dates

I saw a question yesterday on the MSDN forums ask how to go about exposing the LastProcessed datetime per table. Marco has a great post here (https://www.sqlbi.com/articles/last-process-date-in-ssas-tabular/) about a number of ways to get the last processed date at the database level. But none of these techniques work at the table level.

Having a look through the various DMVs that are available and none of them seemed to have this information apart from DISCOVER_XML_METADAT which returns a large XML document which can be tricky to query. However the information in this DMV is also used to populate the AMO object model. So it should be possible to use AMO to pull out the last processed datetime per table and then push it in using a variation of the processAdd push mode processing.

I started by creating a new table in my database called LastProcessed using the following query as a source

SELECT
CAST('' as varchar(256)) as TableName,
Getdate() as LastProcessed
WHERE 1=2

This generates an empty table with a string column and a datetime column which will be the target for our processAdd push processing.

I was not exactly sure how to construct the XMLA for the processAdd command so I create a simple SSIS package using a Dimension Processing Destination in a DataFlow task and captured the XMLA it produced using SQL Profiler. The command that Profiler captured was missing the <parameters> section that you would use for pushing a batch of data so I adapted one of Greg Galloway’s excellent XMLA processAdd examples and then wrapped that in a powershell script.

The powershell script I created can be found here https://gist.github.com/dgosbell/8f4e2a6aa022a6fdd288

And can be run with a simple command like the following:

.\update-ssaslastupdated -serverName "localhost" -databaseName "advwrksLastProcessed" -tableName "LastProcessed"

Once that succeeds you can either expose the whole table or you could hide it and build measures to just expose the dates for specific tables.

Below is an example of querying this table in my adjusted Adventure Works database. You can see that ‘Internet Sales’ and ‘Reseller Sales’ have been processed at different times to the other tables in the database.

image

Print | posted on Wednesday, May 13, 2015 9:52 PM

Comments on this post

# re: SSAS Tabular–per table LastProcessed dates

Requesting Gravatar...
Hi Darren,

I'm years late to this, do you know if your powershell script would still work with SSAS 2016 (guessing the underlying objects have changed) and do you think if I replicated your process, trace etc. I could get this working? Looks an incredibly useful functionality but I've no Powershell skills so it's a bit daunting!

Many thanks,
Alex
Left by Alex Roberts on Aug 19, 2016 6:33 PM

Your comment:

 (will show your gravatar)