skip to Main Content

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=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>AdventureWorksSQL</DatabaseID>
</Object>
</Delete>


Step 2. Load Model from XLSX File

<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>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=”http://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=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:ddl2=”http://schemas.microsoft.com/analysisservices/2003/engine/2″ xmlns:ddl2_2=”http://schemas.microsoft.com/analysisservices/2003/engine/2/2″ xmlns:ddl100_100=”http://schemas.microsoft.com/analysisservices/2008/engine/100/100″ xmlns:ddl200=”http://schemas.microsoft.com/analysisservices/2010/engine/200″ xmlns:ddl200_200=”http://schemas.microsoft.com/analysisservices/2010/engine/200/200″ xmlns:ddl300=”http://schemas.microsoft.com/analysisservices/2011/engine/300″ xmlns:ddl300_300=”http://schemas.microsoft.com/analysisservices/2011/engine/300/300″ xmlns:ddl400=”http://schemas.microsoft.com/analysisservices/2012/engine/400″ xmlns:ddl400_400=”http://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

    • Avi Singh

      Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

      This Post Has 11 Comments
      1. Hi Avi. The links to Part I all result in a Not Found. Same when I click on the article in the search results.

      2. Great Post , Thanks Avi.

        If i would to add roles or anything else on the SSAS server after publish, Would that be ran over on the re-publish ?
        If that is the case, What would be the best practice here ?

        1. The prescribed steps in this post, do wipe away any changes made on the SSAS Side (addition of roles etc.). The options could be
          – Script all changes made after loading the Excel Model and rerun those after each upload
          – Run Automatic Excel Upload on a separate SSAS instance, and restrict access at a Server Level

      3. When I execute the XMLA code from (Step 2. Load Model from XLSX File) updated accordingly, I get the following error:

        “Executing the query …
        The following system error occurred: Access is denied.
        Execution complete”

        It does not specify the access to what exactly is denied. I got the same error if I use the “Restore From PowePivot” from SSMS.

        Any ideas?

        1. VM,
          Make sure you can manually “Restore from PowerPivot”. Once that is sorted out, the XMLA should work. Check the following
          – You are attempting this on the SSAS Server itself. You need to be logged into the SSAS Server (directly or via Remote Desktop). NOT connected remotely from another machine via SQL Server Management Studio (SSMS).
          – The file you are attempting to restore is NOT on a Network location, but somewhere on the local Hard Drive

      4. Hi Avi, So..
        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

        What are my best option to automate, if I use powerquery to feed my excel powerpivot models ? and avoid using power update

        Thank you very much!!
        regards

      5. Hi Avi, thanks for a great blog!

        I have a PowerPivot model with Power Querys as datasources. I publish the model to SharePoint 2013 PowerPivot gallery and build reports on that. While building reports I often get errors “Compressed stream”, “either the user…or the database does not exist”. I can refresh the connection and it will work but really annoying. Therefore I wanted to import into SSAS Tabular. But I need the transformation I do in Power Query.

        What are my options?
        – I have thought of loading all my Power Querys into SQL-tables with Power Update (they are all Try changing Network\Responses\EnableCompression to 0 and bounce the SSAS service

        Regards
        -Taico

      Leave a Comment or Question