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
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″>
|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.
Watch the video for details.
e.g. no ability to render Excel files within a browser as in SharePoint, or ask natural language Q&A on Power BI.