You have designed Aggregations for your cube, but how do you know that they are currently processed?
Hopefully you have your processing routines setup in production so that your indexes are always kept processed. But maybe you are working in a development environment or you are performance tuning that you want to double check that your aggregations are currently processed.
It is not immediately obvious how you can figure if the indexes for a partition or a set of partitions are processed as this information is not exposed in SSMS anywhere.
One way in which you can find it out with an XMLA discover call to the DISCOVER_PARTITION_STAT rowset, but that returns the results in big lump of XML which is not as easy to read as a tabular result set.
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_PARTITION_STAT</RequestType>
<Restrictions>
<RestrictionList>
<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>
<CUBE_NAME>Adventure Works</CUBE_NAME>
<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>
<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
</PropertyList>
</Properties>
</Discover>
If you have SSAS 2008, you can use the new DMV feature to query this same rowset and return a tabular result.
SELECT *
FROM SystemRestrictSchema($system.discover_partition_stat
,DATABASE_NAME = 'Adventure Works DW 2008'
,CUBE_NAME = 'Adventure Works'
,MEASURE_GROUP_NAME = 'Internet Sales'
,PARTITION_NAME = 'Internet_Sales_2003')
This is much easier to read. If you look at the results for a partition that has processed aggregations you will see a row for each aggregation and a row without an aggregation name which represents the fact data.
If you are using SSAS 2005 you are not stuck with just the XML results, you can download the assembly from the Analysis Services Stored Procedure project and use the DMV() function from there with what I think is a much more intuitive syntax - but then I'm biased as I wrote it :)
CALL assp.dmv("
SELECT *
FROM $system.discover_partition_stat
WHERE DATABASE_NAME = 'Adventure Works DW 2008'
and CUBE_NAME = 'Adventure Works'
and MEASURE_GROUP_NAME = 'Internet Sales'
and PARTITION_NAME = 'Internet_Sales_2003'
")
Unfortunately the documentation on this function is pretty sparse, but with a few experiments we can figure out what it is telling us. As you can see this lists each aggregation along with it's size:
If you run a processData on the partition you will end up with the following results with just the raw fact data:
And after you do a processUpdate on one of your dimensions with flexible relationships you will see the following sort of result where any flexible aggregations now have a size of 0:
Now this is all fine if you are only interested in one partition, but if you are like me you will be thinking that you don't want to have to run this query manually for every partition. I want to run it for all partitions. And ideally I would like to check against the metadata of the cube to find out if all of the designed aggregations have been processed. The problem here is that the XMLA Discover call, which is also underlying the two DMV approaches, has mandatory restrictions so you can only query one partition at a time. If you leave out any of the restrictions you will just get an error about a missing mandatory restriction.
So what we really need is some way of running a series of these queries.
Enter PowerShell. The following script loops through all of the partitions in a cube and executes the discover query, then it does a check to see if the total count of processed aggregations is the same as the count of designed aggregations. This script is using PowerSSAS, but I have put some comments in the top of the script for the equivalent "raw" powershell if you are not able to install PowerSSAS.
############################################################
# Get a reference to the Adventure Works cube
add-PSSnapin powerssas
$cub = get-ascube localhost "adventure works dw" "Adventure works"
############################################################
## if you don't have powerSSAS you can uncomment the 5 lines below
## and use them instead of the 2 lines above.
############################################################
#[System.Reflection.Assembly]::LoadWithPartialName("microsoft.AnalysisServices")
#$svr = new-Object Microsoft.Analysisservices.Server
#$svr.Connect("localhost")
#$db = $svr.Databases.GetByName("Adventure Works DW")
#$cub = $db.Cubes.GetByName("Adventure Works")
# Create a connection use the Microsoft.AnalysisServices.Xmla assembly so
# that we can issue a DISCOVER command
[System.Reflection.Assembly]::LoadWithPartialName("microsoft.AnalysisServices.Xmla") > null
[Microsoft.AnalysisServices.xmla.xmlaclient]$xmlac = new-object Microsoft.AnalysisServices.Xmla.XmlaClient
$xmlac.Connect("localhost")
$cub.Refresh()
# loop through each Measure Group and Partition in the cube
foreach ($mg in $cub.MeasureGroups)
{
foreach ($p in $mg.Partitions)
{
$XmlResult = "" # Initialise the variable so that it can be passed by [ref]
# the $restrict variable is a multi-line string
$restrict = "
<RestrictionList>
<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>
<CUBE_NAME>$($cub.Name)</CUBE_NAME>
<MEASURE_GROUP_NAME>$($mg.Name)</MEASURE_GROUP_NAME>
<PARTITION_NAME>$($p.Name)</PARTITION_NAME>
</RestrictionList>"
$xmlac.Discover("DISCOVER_PARTITION_STAT", $restrict, "", [ref] $XMLResult, 0, 1, 1)
# the result comes back as a string, so the result is cast to an XML variable
# to make it easier to work with
[xml]$x = $XmlResult
# get the cound of processed aggregations from the XML result
[int]$processedAggCnt = $($x.return.Root.row |Where-Object {$_.AGGREGATION_SIZE -gt 0} ).Count
if ($processedAggCnt -gt 0) {$processedAggCnt -= 1}
# count how many aggregations we have in the design
[int]$totalAggCnt = $p.AggregationDesign.Aggregations.Count
# print out the details of aggregations where the count of designed
# aggregations does not match the count of processed aggregations
if ($processedAggCnt -ne $totalAggCnt)
{
"$($cub.Name) - $($mg.Name) - $($p.Name) cnt: $processedAggCnt of $totalAggCnt processed ($($p.EstimatedRows) Estimated Rows)"
}
}
}
$xmlac.Disconnect()
After doing a process update on one of the dimensions in the Adventure Works DW database I got the following output from the above script.
Adventure Works - Internet Sales - Internet_Sales_2001 cnt: 0 of 22 processed (1013 Estimated Rows)
Adventure Works - Internet Sales - Internet_Sales_2002 cnt: 0 of 22 processed (2677 Estimated Rows)
Adventure Works - Internet Sales - Internet_Sales_2003 cnt: 6 of 22 processed (32265 Estimated Rows)
Adventure Works - Internet Sales - Internet_Sales_2004 cnt: 6 of 22 processed (32265 Estimated Rows)
Adventure Works - Reseller Sales - Reseller_Sales_2001 cnt: 16 of 66 processed (4138 Estimated Rows)
Adventure Works - Reseller Sales - Reseller_Sales_2002 cnt: 16 of 66 processed (16676 Estimated Rows)
Adventure Works - Reseller Sales - Reseller_Sales_2003 cnt: 16 of 66 processed (26758 Estimated Rows)
Adventure Works - Reseller Sales - Reseller_Sales_2004 cnt: 16 of 66 processed (13283 Estimated Rows)
Adventure Works - Sales Summary - Total_Sales_2001 cnt: 0 of 63 processed (5151 Estimated Rows)
Adventure Works - Sales Summary - Total_Sales_2002 cnt: 24 of 63 processed (19353 Estimated Rows)
Adventure Works - Sales Summary - Total_Sales_2003 cnt: 24 of 63 processed (51201 Estimated Rows)
Adventure Works - Sales Summary - Total_Sales_2004 cnt: 24 of 63 processed (51201 Estimated Rows)
PS: >_
If you want to take this script to the next level, rather than just providing information, you would probably want to do a processIndex on the listed partitions. Possibly using the CaptureXml property and the ExecuteCaptureLog method of the server object to run this processing in parallel.
But I leave that as an exercise for the reader :)
Enjoy!