During the development and on going maintenance of a SSAS database, there are times when you’ll want to have a backup copy of an existing aggregation design. The primary use case to consider is when you are creating customized aggregations (either manually or via the Usage-Based Optimization Wizard). By having a backup copy of the original aggregation design, you can safely merge in the new customized aggregations, test the results, and roll back if you don’t get enough bang for the buck.
Below are step by step instructions for two methods you can use to create copies of existing aggregation designs…
Method 1: SSMS via Script Aggregation Design as…
- In SSMS, connect to the SSAS instance, expand database tree down to the aggregation design for which you want to make a copy, right click and choose Script Aggregation Design | CREATE To | New Query Editor Window :
click to zoom
- In the query editor window that opens, change the name and ID of the script.
click to zoom
- Now just execute the script and refresh the object to see the new Aggregation design that was just created:
click to zoom
Method 2: SSDT via Aggregations Tab Advanced View
This my personal preference because it keeps everything in the project.
- In SSDT, open the cube object and select the Aggregations tab
- Click Advanced View:
- Click the New Aggregation Design button:
click to zoom
- On the final screen, choose the “Create a copy of an aggregation design”, select the aggregation design for which you want to make a copy, provide a name for the copy, and click OK.
click to zoom
- Now, after flipping back over from the advanced view, you should see the original aggregation design and the backup you just created:
click to zoom
