Power Pivot to SSAS Tabular: Automatically Publish Excel Workbooks

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

Read the Rest

Power Pivot to SSAS Tabular in less than 30 minutes

By Avi Singh [Twitter]

Part I in Series. Part II >>Power Pivot to SSAS Tabular: Automatically Publish Excel Workbooks

A key milestone in my Power Pivot journey (read or watch) was when I let my baby go. I uploaded my beautiful Power Pivot model on our server and now it was no longer just mine. Many others could connect to it and create reports that they wanted, using the tools that they liked (Excel, Power View, SSRS etc.). I hadn’t quite realized the power of what I had unleashed. Read on or watch the video below for a fresh take on this, but also check out my earlier post – Migrating From Power Pivot to Analysis Services Tabular Model.

YouTube Video

Server Option: Separate Model and Reports

The best advantage, in my opinion, of going the server route is the ability to separate the model from the reports (many other advantages, but I’ll focus on this one for now). Excel Power Pivot models, typically start around 10 MB and go up from there – 50MB, 100MB, 1GB…

Without the server option, your Excel file would include – The Power Pivot model + Any and every report that you (ever) need. This ends up fairly chaotic very soon, with lots and lots of sheets with different pivot tables, Power View sheets etc. The file becomes hard to share, use or update.

Excel Power Pivot file gets bloated as the model grows and you keep adding more “Reports” within the same fileExcel Power Pivot file gets bloated as the model grows and you keep adding more “Reports” within the same file

The way out (or up!) is easy. You have a few server options available

· SharePoint

· SSAS (SQL Server Analysis Services) Tabular

· Power BI*

With the server option it would be possible to upload your model and build separate connected reports. The Core and Thin framework introduced earlier.

 Server option lets you separate the model (Core) from the Reports, which can now be really Thin!
Server option lets you separate the Model from the Reports
(Reports can now be really thin and created using your tool of choice
– Excel, Power View, SSRS etc.)

* Power BI v1 and v2 (in Preview) offer different functionality than SharePoint or SSAS.  V1 is limited but V2 offers reports and dashboards connected to your model. At this point, neither lets you create thin Excel workbooks connected to a Power BI model in cloud.

The freedom you get from this will be dizzying at first. Now you, or really anyone in your team can create the needed reports connected to the single model, using the tools they like – Excel, Power View, SSRS etc. So you still retain the single source of truth but allow for flexibility and customization of reports per user needs.

SSAS Tabular – no programming needed!

Read the Rest

Introducing Power Update!

Post by Rob Collie

***Update:  check out Scott Senkeresty’s review of Power Update over on Tiny Lizard.

***Update #2:  a Free Version of Power Update is now available.  More info here.

***Update #3:  There is now a forum for Power Update questions, located here.

Power Update: Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (SharePoint or Otherwise)

A brand-new software utility designed from the ground up as
a “Companion” to  Power Pivot, Power Query, and the entire Power BI stack.

Definitely Click on the Image for Larger Version – Surprises Lurk Therein

Do Any of These Sound Familiar?

Common Problems with Power Pivot and Power BI Scheduled Refresh

Power Update Helps With ALL of These (And a Few More, Too)

“What IS It?”

OK, a few things:

Read the Rest

Guest Post: Migrating From Power Pivot to Analysis Services Tabular Model

Intro from Rob

At a conference three years ago, I saw my former colleagues at Microsoft announce something very significant:  Power Pivot was getting a bigger brother.  Power Pivot’s formula language (DAX) and the overall “tables/relationships/calc columns/measures” modeling experience was going to be “ported” into Microsoft’s flagship, industrial-strength BI platform – SQL Server Analysis Services (SSAS).

At the time, that led me to post the following diagram:


Power Pivot Broke the First Wall – and We’re Still Getting Accustomed to Our New Powers.
But Power Pivot’s Bigger Brother (SSAS Tabular) Breaks a Future Wall Too.

Years have passed and that has already become the new normal.  Microsoft rolled out Power View and had it only work against Power Pivot / Tabular (and not against traditional SSAS) – a decision they later had to dilute, but still a clear signal of their future intent.  BI luminaries like Chris Webb and the Italians wrote a book on Tabular.  As revolutions go, this one was quite non-eventful.

Not that Excel Pros MUST move upstream and start using SSAS.  Quite the contrary – I still personally have not!  But it is VERY good to know that it’s there, for several reasons:

  1. Microsoft is betting heavily on “the Power Pivot way.”  You don’t “infect” your flagship product with something new unless that new thing is awesome.  Power Pivot – that thing running on your desktop – is good enough for the heavyweight BI pros.  Digest that thought.
  2. There’s an “upgrade path” for important Power Pivot models.  This is a great selling point for IT if they are nervous about Power Pivot.  Unlike regular Excel workbooks, a Power Pivot workbook that becomes business critical CAN be “taken over” by IT, and made into something centralized and blessed, without having to rewrite it.
  3. There’s an “upgrade path” for Excel Pros.  With very little effort, an established Power Pivot pro can “change hats” and label herself a Business Intelligence Pro, a Tabular Modeler – even if she were “just” an Excel Pro a couple years ago.  Again, not that she has to, because Power Pivot itself offers practically limitless power.  She just can.  Exciting huh?

Avichal Singh is one such Excel Pro who has been making that transition.  He’s been so kind as to write up his experiences so far, and MAKE US A VIDEO SHOWING US WHAT THAT LOOKS LIKE!  (Holy cow I had not realized there was a video in this post until just this moment.  I am Jack’s Awestruck Amazement.)

Take it Away, Avichal…

By Avichal Singh www.linkedin.com/in/avichalsingh

This is a follow up to my original post – My Power Pivot Journey. In this post I would like to detail the migration to Analysis Services and its implications we experienced. For general comparison of Analysis Services versus PowerPivot read Comparing Analysis Services and PowerPivot (look for table under Analysis Services Offerings). Our main reason for switching to Analysis Services was scalability. We were reaching 100MB with Excel+PowerPivot and after migration to SSAS Tabular our database has grown to 700+MB with 25+ Million rows overall.

My (Non) Background as Developer

If you are like me, you might have opened up Visual Studio in the past in a misguided attempt to develop something or opened up someone else’s project and felt somewhat lost. Do not let that fear stop you.

Developing SSAS Tabular Model in Visual Studio is not that much different than doing it in Excel PowerPivot. Authoring your model will feel surprisingly similar, but you will find more changes around deploying and using your model.

In the video below and the rest of the article I will show you, exactly – how to migrate, what changes to expect and how to use the new interface.

PowerPivot to SSAS Tabular: Migration and other tips

Read the Rest