By Avi Singh [Twitter]

This article is Part II of a series.
<<Read Part I: Power Pivot to SSAS Tabular in less than 30 minutes

In Part I we explored how powerful one of the Server Options can be for Excel Power Pivot. Be it SharePoint, SSAS Tabular or Power BI – moving up to the server option lets you separate the Model from the Reports and unleash the potential of Power Pivot.

For Part II, we will continue exploring the SSAS Tabular option. Specifically once you choose to host your Model on SSAS Tabular, you are faced with two options

a) Move to Visual Studio as a Development Environment (this article has some guidelines)

b) Continue using Excel as Development Environment (read on…)

Or a combination of a) and b), with some users moving to a) Visual Studio while others stick with b) Excel, to build and update Power Pivot models.

For option b) Excel, ideally you would want some way to automatically publish your Excel workbook to SSAS Tabular. As easy as it is to publish them manually (See Part I), it would still be preferable to automate this task. Automatic upload of Excel Power Pivot Workbooks to SSAS Tabular would enable:-

  • > Uploading dozens or more workbooks automatically. One or two ad-hoc upload is fine, but to upload more and do so on a schedule begs for automation.
  • > Separation of Roles between Model Authors & SSAS Tabular Administrators. You can potentially have multiple authors using Excel and saving their workbooks on a shared location. From there the SSAS Tabular Administrator is tasked to pick up the files and upload to SSAS using one of the options below

Publish Power Pivot to Tabular using Power Update

This is by far the easier option. You can try it out using the Power Update Trial. This is fairly point and shoot: you select Source, Destination Type (SSAS Tabular) and provide Destination information; besides the other typical settings for Schedule etc.


Click to watch YouTube Video showing Publish to SSAS using Power Update/XMLA

Publish Power Pivot to Tabular using XMLA Script

Unfortunately this is harder than one would expect. Typically you can script any SQL Server action by simply clicking on the “Script” button at the top of the interactive window (kinda like Macro recording in Excel). But if you try that for “Restore from Power Pivot” action, you simply get the error<!– Restore from PowerPivot is not supported using XMLA.–>


Attempting to Script the “Restore from Power Pivot” action just yields this friendly message

Now the message is not quite accurate. It is possible using XMLA script to restore from Power Pivot. You can do so using the following set of commands, with the caveats covered below in this article.


Step 1. Delete Existing Model

The next step would fail if the Model exists, therefore as Step 1 we delete the model (if it exists) using XMLA command as below

<Delete xmlns=”https://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>AdventureWorksSQL</DatabaseID>
</Object>
</Delete>


Step 2. Load Model from XLSX File

<ImageLoad xmlns=”https://schemas.microsoft.com/analysisservices/2003/engine” xmlns:ddl100=”https://schemas.microsoft.com/analysisservices/2008/engine/100″ xmlns:ddl200_200=”https://schemas.microsoft.com/analysisservices/2010/engine/200/200″ xmlns:ddl100_100=”https://schemas.microsoft.com/analysisservices/2008/engine/100/100″>
    <ddl200_200:ImagePath>C:\TEMP\AdventureWorksSQL.xlsx</ddl200_200:ImagePath>
    <ddl200_200:ImageUniqueID/>
    <ddl200_200:ImageVersion/>
    <ddl200_200:ImageUrl/>
    <ddl100_100:DbStorageLocation/>
    <ddl100:ReadWriteMode>ReadWrite</ddl100:ReadWriteMode>
    <DatabaseName>AdventureWorksSQL</DatabaseName>
    <DatabaseID>AdventureWorksSQL</DatabaseID>
</ImageLoad>

Step 3. Change Impersonation Mode
Default mode for Power Pivot model is ImpersonateCurrentUser. This is not supported for SSAS Tabular models. Thus we would change it to ImpersonateServiceAccount.
<Alter AllowCreate=”true” ObjectExpansion=”ExpandFull” xmlns=”https://schemas.microsoft.com/analysisservices/2003/engine”> <Object> <DatabaseID>AdventureWorksSQL</DatabaseID> <DataSourceID>67df2a25-8953-404f-85fc-0e00c952fa01</DataSourceID> </Object> <ObjectDefinition> <DataSource xsi:type=”RelationalDataSource” xmlns:xsd=”https://www.w3.org/2001/XMLSchema” xmlns:xsi=”https://www.w3.org/2001/XMLSchema-instance” xmlns:ddl2=”https://schemas.microsoft.com/analysisservices/2003/engine/2″ xmlns:ddl2_2=”https://schemas.microsoft.com/analysisservices/2003/engine/2/2″ xmlns:ddl100_100=”https://schemas.microsoft.com/analysisservices/2008/engine/100/100″ xmlns:ddl200=”https://schemas.microsoft.com/analysisservices/2010/engine/200″ xmlns:ddl200_200=”https://schemas.microsoft.com/analysisservices/2010/engine/200/200″ xmlns:ddl300=”https://schemas.microsoft.com/analysisservices/2011/engine/300″ xmlns:ddl300_300=”https://schemas.microsoft.com/analysisservices/2011/engine/300/300″ xmlns:ddl400=”https://schemas.microsoft.com/analysisservices/2012/engine/400″ xmlns:ddl400_400=”https://schemas.microsoft.com/analysisservices/2012/engine/400/400″> <ID>67df2a25-8953-404f-85fc-0e00c952fa01</ID> <Name>SqlServer localhost AdventureWorksDW2012</Name> <Annotations> <Annotation> <Name>ConnectionEditUISource</Name> <Value>SqlServer</Value> </Annotation> <Annotation> <Name>MODIFIED_IN_PP</Name> <Value>MODIFIED_IN_PP</Value> </Annotation> </Annotations> <ConnectionString>Provider=SQLNCLI11;Data Source=.;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Persist Security Info=false</ConnectionString> <ImpersonationInfo> <ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode> </ImpersonationInfo> <Timeout>PT0S</Timeout> </DataSource> </ObjectDefinition> </Alter>

Considerations/Caveats for using XMLA Script

Firstly, thanks to Cathy Dumas’ excellent post explaining the steps. Here’s what you should watch out for.

  • The steps above should work if you update the blue text. These can be scheduled as a recurring job using SQL Server Agent. However, the surefire way to get the commands you need to execute is to run a trace on your SSAS server, while you run an import interactively.
    Watch the video for details.
  • Best used when your model is fed from a Database (that’s a good practice anyway). If you have copy-pasted text or linked Excel tables in your Power Pivot model, you’ll have a lot more to sort out for automation.
  • Best not have Implicit Measures in the model being imported and use only Explicit Measures (again, this is a best practice in any case)
  • Does not work with Power Query. If you were using Power Query to feed your model, that would not refresh once uploaded to SSAS Tabular
  • You obviously do not get the features offered by other Server Options – SharePoint, Power BI.
    e.g. no ability to render Excel files within a browser as in SharePoint, or ask natural language Q&A on Power BI.

      <<Part I: Power Pivot to SSAS Tabular in less than 30 minutes

      Power On!
      Avi Singh