Power Pivot and SSAS (Tabular) Restore


One really cool feature of SSAS in SQL 2012 SSAS (tabular mode) is the ability to restore a SSAS database from a powerpivot workbook. This can separate the design and administration functions of SSAS.

The restore can be done through the object explorer (right click on the Database node as select ‘Restore from PowerPivot’), which opens the restore GUI (see below). Simply select the workbook and the name of the SSAS database (which will import the powerpivot book). If the SSAS database exists, and select ‘Allow database overwrite’, if not, the ‘Allow database overwrite’ option can be left unchecked.

When the restore is run from the UI the standard script generation () will not produce the XMLA to restore the database from file (an XMLA restore command cannot be used (see below)).

There is no need to process the model after the restore however, I have noticed that the browse UI did not recognise the database until the 1st server reconnect was hit (that is, button [1] in the screen shot below). The query reconnect (button [1] did not refresh the schema metadata).

Alternatively, changing from design to text query modes ([3]) refreshed the metadata.

XMLA

The default script for restoring a SSAS database specifies the file and database (as below);

<Restore  xmlns=http://schemas.microsoft.com/analysisservices/2003/engine>
<File>some_file.abf</File>
   <DatabaseName>Target Database</DatabaseName>
   <AllowOverwrite>true</AllowOverwrite>
</Restore>

The restore of a powerpivot workbook cannot be done by using the excel workbook as the file name. The <File> tag of the command must be an .abf file
and substituting an excel extension (for the file) will throw an error.

In order to restore the database from XMLA, an image load command must be used (with both database id and name specified). However, in this case an AllowOverwrite tag cannot be used (the database must not exist);

<ImageLoad xmlns=http://schemas.microsoft.com/analysisservices/2003/engine
xmlns:ddl100=http://schemas.microsoft.com/analysisservices/2008/engine/100
xmlns:ddl200_200=http://schemas.microsoft.com/analysisservices/2010/engine/200/200
xmlns:ddl100_100=http://schemas.microsoft.com/analysisservices/2008/engine/100/100>
<ddl200_200:ImagePath>some_file.xlsx</ddl200_200:ImagePath>
<ddl100:ReadWriteMode>ReadWrite</ddl100:ReadWriteMode>
<DatabaseName>Target Database</DatabaseName>
<DatabaseID>Target Database</DatabaseID>
</ImageLoad>

One of the biggest drawbacks of SSAS (multidimensional) has been the thin line between development and administration (especially for small-mid enterprises). The ability to separate the design of tabular models (leaving this in the hands of business) and server administration is a powerful feature.

Advertisements

2 thoughts on “Power Pivot and SSAS (Tabular) Restore

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s