Last 'something was processed in cube' date

Topics: Developer Forum
Aug 31, 2012 at 11:31 AM

Hello,

I would like to give our Excel users a way to check when the data in cube was changed.

A calculated measure as in http://asstoredprocedures.codeplex.com/wikipage?title=CubeInfo&referringTitle=Home would be a pretty good solution, but it is unfortunately not applicable for many of our cubes, because in our incremental processing we only process the last partition (for the same reason we also cannot use GetMeasureGroupLastProcessedDate(), it only returns a datetime when the whole measuregroup was processed, not individual partitions).

My current plan is to write a method GetLastProcessedDateOverPartitions() which iterates over partitions and gets the max of the last processed dates (need to test what exactly happens when ProcessUpdate is run on a dimension).

Am I actually on the right track that I need to implement this feature or can I get the same information available to Excel users with the existing functionality (I thought about using Discover/DiscoverXmlMetaData for this purpose, but if I'm not missing something, they won't work in my case)-

If I'm on the right track, I'll be glad to provide the code for the ASSP-Project.

Sincerely

Andrej Kuklin

Coordinator
Aug 31, 2012 at 2:37 PM

I was a bit surprised to see that if you just process one partition, it doesn't update the processed date on the measure group. So it seems like you're on the right track.

I would probably create a version of the function that accepts the cube name and scans all measure groups in the cube... and another version that accepts a measure group name as a parameter and scans partitions in that measure group only.

That'd be great if you could post the code you come up with back to this discussion so we can consider including.

I don't know off the top of my head if Discover/DiscoverXmlMetaData will get you what you need. Even if it does, it'd be nice to wrap this common need into a friendly function name with an easy function signature. So I think you're going down the right path.

Sep 13, 2012 at 3:06 PM

Hello Greg,

I was a little bit distracted by other issues, but now the methods for finding the last processed dates on per partition level are ready. I've uploaded the patch; you can find it under http://asstoredprocedures.codeplex.com/SourceControl/list/patches . It would be great if you could review the code and integrate it into ASSP project.

The code should be pretty self-explanatory, just a couple of notes:

1. I've made several methods, so that one can find the last processed date in (all measure groups and all partitions) or (one specific measure group and all partitions) or (one specific partition). There is also a possibility to specify the cube explicitly (otherwise the current one will be used).

2. If a dimension is processed with ProcessUpdate and no aggregations or indices are dropped, then the "last processed"-timestamps of partitions actually do not change, but the timestamps of the measure groups do. I've added a parameter whether the timestamps of measure groups should be taken into account (default - true). So, the simplest version with no parameters will check all partitions in all measure groups in the current cube with "last processed"-timestamps of the measure groups taken into account.

3. I've thoroughly tested the code for SSAS 2008 R2 (all overloads, invalid measure group name, invalid partition name etc.), but didn't test it for SSAS 2012 (though I do not expect anything to be different).

Sincerely

Andrej Kuklin

Coordinator
Sep 13, 2012 at 7:42 PM

Fantastic job! I checked in your code as is and tested it on SSAS 2012.

See if you think my wiki description of the functions is clear:
http://asstoredprocedures.codeplex.com/wikipage?title=CubeInfo

Sep 14, 2012 at 7:28 AM

Hello Greg,

the wiki description is complete and correct. Thank you very much for quick response.

Sincerely

Andrej Kuklin

Jan 3, 2013 at 2:16 PM

Hello Greg,

are you planning to release the 1.3.7 version of ASSP in next 4-5 weeks? I know, I can compile it myself, but I'd like to replace our custom assembly with the new version of ASSP that we use anyway and want to stay on the official release path :)

Happy New Year and best wishes

Andrej Kuklin