Scripting a Cube in XMLA

Downloads
95163.zip

Virtually any object in SQL Server Analysis Services (SSAS)—including cubes, dimensions, data sources, and data source views (DSVs)—can be scripted in XMLA. The easiest way to begin a script is to right-click the object that you want to create in SQL Server Management Services (SSMS) and select the Script menu item. You can use this capability to selectively script some migrated objects. The following high-level checklist shows how to implement this method in our FoodMart 2000 scenario:

1. Perform a straight database migration from SSAS 2000 by using the Migration Wizard. The new database is probably named "FoodMart 2000."
2. Create a new FoodMart database, named something like "FoodMart 2005."
3. Script and run the data source from the migrated database (change the database in the script).
4. Script and run the DSV from the migrated database (change the database in the script).
5. Add the appropriate relationships and primary keys to the DSV in FoodMart 2005.
6. Rename named calculations in the FoodMart 2005 DSV as appropriate. The Migration Wizard added some named calculations to some of the tables in the DSV and named them "Column1." These named calculations reflect SQL statements that were entered into the Source Column for cube measures or Member Column for dimension-level names in FoodMart 2000. It's a good idea to rename these to better represent the underlying calculation.
7. Create a new cube in FoodMart 2005 by using the Auto Build feature.
8. Review and revise the resulting dimensions (e.g., attribute properties, user hierarchies, attribute relationships) in FoodMart 2005.
9. Script and run the XMLA to create other objects, such as partitions, MDX calculations, and actions to copy them from the migrated database to FoodMart 2005. You can script these objects independently or extract them from a cube or database creation script.
10. Add designations for parent/child, custom member formulas/rollups, and unary operators to FoodMart 2005. You can determine these designations by reviewing the migrated database.
11. Add drillthrough actions to duplicate drillthrough settings from SSAS 2000.

It might be easiest to cut out subsets of a cube or database creation script when creating objects such as partitions, actions, and MDX calculations. You can run the example script in Listing A in SSMS to create calculated members from the source Sales cube in the newly generated database.

Learn more from Derek Comingore's "Building Your First Cube."