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:

image

If you run a processData on the partition you will end up with the following results with just the raw fact data:

image

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:

image

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!

Technorati Tags: , ,

Feedback

# re: SSAS: Are my Aggregations processed?

Hello Darren,
I like your example of using powerssas to display all current status of the aggregations. Do you know if there are similar script in SSIS? Script to loop through all measure groups.

If powerssas is needed, is it just an add-in to visual studio? Where can I download or more whitepaper on powerssas.

Thanks!
email : caplaw@hotmail.com 7/11/2009 4:28 AM | Lawrence

# re: SSAS: Are my Aggregations processed?

PowerSSAS is not a visual studio add-in. It is an add-in to the PowerShell scripting language. (see http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx)

It would be possible to create a similar script in SSIS as PowerSSAS is just a wrapper over the AMO library (Microsoft.AnalysisServices.dll) 7/13/2009 10:27 AM | Darren Gosbell

# re: SSAS: Are my Aggregations processed?

I think it is also important to make a note about a related DMV, DISCOVER_PARTITION_DIMENSION_STAT. We've run into instances where dimensions were updated and all aggregations were processed, but performance was still very much degraded (as compared to a fully processed database). In those situations, an inquiry into DISCOVER_PARTITION_DIMENSION_STAT revealed that indexes were not being rebuilt as they should have been.
5/11/2010 5:04 AM | David Beavon

# re: SSAS: Are my Aggregations processed?

Darren have you had any issues with this working on 2008 R2? I just processed the aggs on the Internet Sales 2006 - 2008 MG and I still see 0 of 54 processed. Is there some change I need to make in the script? 8/10/2010 2:00 AM | Frank Kearney

# re: SSAS: Are my Aggregations processed?

Here are this and some other articles on SSAS Aggregations: http://ssas-wiki.com/w/Articles#Aggregations
2/24/2011 8:12 AM | Sam Kane

# re: SSAS: Are my Aggregations processed?

Hy Darren, i'm facing the same problem as Frank Kearney working with 2008R2.. 4/9/2013 7:36 PM | Jpedroalmeida

Post a comment





 

 

News

About Me
I am a consultant, based in Melbourne Australia. I primarily work in the Business Intelligence area with SQL Server, although I also dabble in Content Management Server and .Net coding

Contact Me

MVP


Legal
Any and all code, software, examples, suggestions and anything else on this web site is available for you to use at your own risk. No warranty is expressed or implied.
Views and Opinions
The views and opinions expressed on this web site are not necessarily the views or opinions of my employer.

Subscribe in Bloglines Subscribe in NewsGator Online

Locations of visitors to this page

Twitter












Tag Cloud


Article Categories

Archives

Post Categories

.Net Blogs

Aussie Bloggers

BI Blogs

CMS Blogs

Syndication: