Aggregation Export?

Topics: Resolved
Mar 11, 2008 at 1:06 AM
Can the Agg Mgr piece of BIDS Hhelper export a cube/partition's aggregations to an external file? And then re-import them into another? Or does the XML just have to be hacked?

Thanks.
Coordinator
Mar 11, 2008 at 3:42 PM
Anything is possible. Can you explain why you would want to do that?
Coordinator
Mar 11, 2008 at 11:58 PM
You can already export aggregation designs for a MeasureGroup to an XMLA create or alter script from SSMS. You could run such a script against a different cube/database by change the DatabaseID and CubeID. But as furmangg says, anything is possible and if you have a particular scenario that you would like to see addressed beyond that we would be interested to hear about it.
Mar 14, 2008 at 7:02 PM
Thanks for getting back, guys.

Darin, I didn't realize you could export the aggregations directly in Management Studio. Thanks, that will help us with what we need. I wish I had realized that.

One of the things we used to do with Partition Manager (remember that old thing?) back in AS 2000 is get our aggregations tuned just right, and then we'd paste them into a "master partition" where a Clone method in DSO would automatically pick them up when building or re-building the partitions at process-time. After testing in our development environment, if they were right, we'd just paste those new aggs from a CSV directly into the master partitions in production.

Our aggs need a lot of tuning because the cubes are quite big (based upon billions of rows) with quite a few dimensions (some with about 28!). It was nice to have those aggregations be portable between development and production, when we knew our dimensionality was exactly the same. It's cool that with BIDS helper you can just move the partitions you have into the scheme you'd like to use, I was just thinking that if you want to move them into another environment altogether (totally different database, cube, etc...) that has the same dimensionality as the source, having just a file with those designs be importable with a push of a button would be a big help.

Just a thought.

(BTW, the visualize attribute lattice and MDX deployment tools are two of the coolest and highly utilized and popular options here! )

Thanks again.

- Phil : > )
Coordinator
Mar 14, 2008 at 8:23 PM
You might also look at the Deploy Aggregation Designs feature. If you design your aggs in BIDS, all you need to do is pull up project properties and change your target server to your production server, then run the Deploy Aggregation Designs feature, then run Process Indexes on the cube in production.
Coordinator
Mar 15, 2008 at 8:39 AM
Edited Mar 15, 2008 at 8:53 AM
Hi Phil,

Another possibility might be to write a script in something like PowerShell. I had an existing script that did something like this so I modified it and ended up with the script below which outputs all the aggregations for a given cube to an xmla file.

Cheers
Darren

# Scripting out all the aggregation designs for a given cube
# -----------------------------------------
# Author: Darren Gosbell 
#         http://geekswithblogs.net/darrengosbell
# Date  : 15 Mar 2008
# ----------------------------------------- 
 
$svrName = "localhost"
$sourceDB = "Adventure Works DW"
$sourceCube = "Adventure Works"
 
### load the AMO library (redirect to null to 'eat' the output from assembly loading process)
[System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices") > $null
# connect to the AS Server
$svr = New-Object Microsoft.AnalysisServices.Server
$svr.Connect($svrName)
 
# get a reference to the database
$db= $svr.Databases.Item($sourceDB)
# get a reference to the cube
$cub = $db.Cubes.FindByName($sourceCube)
 
# we need to create an xmlwriter, so we base it on a
# stringBuilder and StringWriter so that we can output
# the string to a text file
$sb = new-Object System.Text.StringBuilder
$sw = new-Object System.IO.StringWriter($sb)
$xmlOut = New-Object System.Xml.XmlTextWriter($sw) 
$xmlOut.Formatting = [System.Xml.Formatting]::Indented
 
# create an array of MajorObjects to pass to the scripter
$aggDes = [Microsoft.AnalysisServices.MajorObject[]] $cub.MeasureGroups | % {$_.AggregationDesigns}
 
# This line just outputs all the aggregations we are about to script
$aggDes|Format-Table Parent, ParentCube, EstimatedRows
 
# Start the batch
[Microsoft.AnalysisServices.Scripter]::WriteStartBatch($xmlOut,$false)
#Pipe the aggDes array into a foreach loop and call the WriteAlter method
$aggDes | % { [Microsoft.AnalysisServices.Scripter]::WriteAlter($xmlOut,$_,$false,$true)}
# Write the EndBatch
[Microsoft.AnalysisServices.Scripter]::WriteEndBatch($xmlOut)
 
#output the script to a file in the current Directory
$sb.ToString() > AggDes.xmla
 
# clean up any disposeable objects
$sw.Close()
$svr.Disconnect()
$svr.Dispose()
 
Mar 27, 2008 at 3:05 PM
Darin, thanks for that script, I'd like to try it out and add some tweaks to it. I'm writing some AMO to work with aggregations as well.

For the record, I didn't mean to sound like I was speaking disparagingly about the Partition Manager tool used in AS 2000 by refering to it as "that old thing." It's just because it seems like it's been a while since I've used it on a regular basis. From 2003 - 2005 it was a very useful and slick (and invaluable) asset when automatically creating partitions and setting their slices accordingly, as well its ability to let people customize their own aggregations and move them via cut and paste. My apologies if I ruffled any feathers, it wasn't my intention at all.

Thanks again, guys for all your help.

- Phil : > )
Coordinator
Mar 27, 2008 at 8:47 PM
Glad to be able to help. There are no feathers ruffled here : )

We love getting feedback and feature ideas, we just have to try and prioritize them with other features, other projects and our day jobs.

The more I think about it, the more I think that a script is probably the better solution, for the reason that you have just highlighted - you can take it, try it out and add your own tweaks. And it can get you up and running fast.

If find that you are using your updated script all them time, but you feel that it would work better if it was integrated into BIDS - feel free to post the script back here and we can log it as a feature request.

Coordinator
Jun 29, 2008 at 11:36 AM
We just checked in a feature in Agg Manager that allows you to right-click on an agg design and export the aggs to a SQL table as subcube vectors (1's and 0's). This is somewhat related to this thread so I thought I would mention it here.