Interview with Chris Finlan of Microsoft

March 3, 2015

Post by Rob Collie

A Kindred Spirit Revealed!

Rob Collie of PowerPivotPro and Chris Finlan of Microsoft

Me and Chris Last Week at the Microsoft Offices in D.C.
(Their Electronic Signs Are Awkwardly Truthful.)

For about a year I have been working closely with a Microsoft employee named Chris Finlan, the BI TSP for Microsoft’s Mid Atlantic Sales District.  Loosely translated, that means that when it comes to Business Intelligence, he’s the “go to” resource for all of the Enterprise sales teams in Pennsylvania, Maryland, and Virginia.

On the face of it, that may sound like “well of COURSE you two work closely together – he sells MS BI, and you teach/help people to use it.”  But there are a LOT of technologies in the MS BI stack, and we (at PowerPivotPro) are specialists in the New Wave – not just the newer technologies like Power Pivot and Power BI, but also in the way that the tools are positioned, evangelized, and sold.

Even though we’re 100% aligned with Microsoft’s direction, it takes time for habits to change – both for large companies AND the software sales teams who work with them.  Neither is particularly incented to take risks – the consequences of a failed experiment are high.  So, it’s natural that not everyone has rushed to embrace the New Wave as the total paradigm shift that it is.

The traditional Microsoft BI sales strategy can be loosely characterized as “top down” (pitch/sell the software to the people who write big checks) whereas I think Power Pivot is often better pitched bottom-up (prove its value to a single department or group of users, and the checks come later).  Neither is an “incorrect” approach of course, and they are not mutually exclusive.  In particular, I’ve long believed that “bottom-up” messaging can be an effective part of a “top-down” engagement.

But changes to the script require a LOT of confidence.  The “game” just isn’t set up to reward experimentation.  So ultimately, it often requires someone who’s wired a bit differently.

Rob's Face When he Got Chris's EmailIn my world at least, that person first “surfaced” in an email I received about a year ago.  Chris just dropped me a note and said “hey I’ve been adapting some of the messaging on your website for use with customers, and it’s been working.  Can we have a phone call at some point?”

And at that moment I scrambled for the phone.  The rest, as they say, is history.  Chris and I talk probably three times a week, cooperate on multiple customer engagements, ran classes in Philly (last year) and DC (last week), hatched Insight Center (more on this below), and generally just pester the hell out of each other all week long.

On to the Interview!

Read the rest of this entry »

Power Pivot Blitz: Webinar, Seattle Class, Online Class

February 27, 2015

By Avi Singh [Twitter]

My New Year’s resolution to help 1000 people start using Power Pivot, will not achieve itself. So we are taking the bull by the horns and announcing the training events below. Click on the links to enroll or obtain additional information.

Need help choosing? Webinar is a free event so all are welcome to join. Read this article to help you…Choose Between: Live In-Person, Live Online and other training options. 

Mar 17

Live In-Person Class
Apr 1-2

Live Online Class
Apr 6-7

PowerPivot for Excel Live Class (Seattle)

PowerPivot for Excel Online Class

More Info on Live In-Person Class

Live In-Person class includes: Thumb Drive, 50% Off Online University and other goodies!

All Students Receive a PowerPivotPro thumb drive containing the materials from the course.

Power Pivot Seattle Class: Free Thumb Drive

You also receive 50% off our online video course.  Past students have found it to be a helpful “reinforcement” to the live class (as well as covering topics that we just can’t get to in two days), so we now offer it as a bundle.

More goodies! You receive a physical copy of one of Rob Collie’s Books and digital copy of both books. Wait wait, there’s more Winking smile: you also get a laminated DAX Reference Card – which many have sworn is a life saver when solving a complex CALCULATE() puzzle.

Live In-Person Seattle Class Details & Registration

Power Pivot to SSAS Tabular: Automatically Publish Excel Workbooks

February 26, 2015

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 of this entry »

Create a Custom Calendar in Power Query

February 19, 2015

by Matt Allington

In this post, I am going to show you how simple it is to create a custom calendar using Power Query.  If you follow the steps below, you will get a good sense of how capable Power Query is, and may spark your interest to learn more about it.

Different Types of Calendars

There are lots of different ways of creating a custom calendar for use in your Power Pivot workbooks, and plenty of discussion about them on (25 prior to this one).  Historically I have just used an Excel workbook that I created with all the relevant columns, and enough rows of dates to cover for the next year.  There is one problem with this approach however –  the date over runs when you are looking at last years’ data in a pivot.

over run

As you can see above, we have dates into the future that are showing sales for last year with no sales this year – because we haven’t had “this year” yet.  It is a pain to change the import filter on your Excel calendar each time you refresh (not practicable actually).  You can write some snappy Excel functions in your source calendar table to code around this, or you could make your DAX more complex, but better still – I think this is a great opportunity to learn some new skills – Power Query.

Building a custom calendar from scratch in Power Query is actually quite straight forward, and I am going to take you through it step by step below.

First Create a New Blank Power Query Workbook.

The Blank Query option is right at the bottom of the “From Other Sources” menu.

blank query

If you haven’t done so already, turn on your Formula bar from the view menu.  You will need this so you can easily see the Power Query Code that is generated by the UI tools, and also to allow you to edit the code by hand when needed.

turn on formula
Read the rest of this entry »

Power Pivot to SSAS Tabular in less than 30 minutes

February 17, 2015

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 of this entry »

Supporting Files from “Power Pivot Alchemy”

February 11, 2015

Post by Rob Collie


Click to Download the Workbooks and Data Sources

Apologies for the Delay

No excuses, I just fumbled this.  At one point I *did* plan to make the files available for the Alchemy book, just like I did for my first book.  But then I got lazy and thought “nah, people don’t really seem to need them.”  Which was dumb.  There are even places in the book where I say “you can grab the files and walk through this yourself.”  Ugh, so silly.  Anyway, finally they are ready to be shared:

Grab the Files Here

Don’t Have the Book?  Grab the Files Anyway!

If you want the files but haven’t purchased Alchemy, do NOT feel guilty – just grab the files anyway!  Seriously, “steal” them all you want.  Give them to your friends and family.  Re-gift them at the holidays, heh heh.  Yes, the files contain useful examples on their own, but they’re also a good “advertisement” for the actual book, so we don’t mind.

Click Here to “Steal” With Our Compliments :)

Have fun!

Tales from the Trenches: My personal experience with Power Update (by Tim Rodman)

February 10, 2015

Guest Post by Tim Rodman, currently blogging about reporting in Acumatica ERP @

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

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

Intro from Rob: I’m what you might call a “gift horse optimist” – strongly positive outlook, but when the hoped-for thing finally arrives, I find myself closely inspecting it, testing it, before I trust it enough to advocate it to others.  I went through this same process with Power Pivot itself – I “saw” its gamechanging power in 2010, but it was a full eighteen months before I finally dropped all disclaimers and just started calling it far better – period – than anything we’ve had before.”

Similarly, I’ve long known that Power Update would be a MAJOR win for us in the Power Pivot and Power BI communities.  But I am willing to advocate it now only because I’ve watched others – like Scott, and Tim below – use it successfully, in production environments, in recent months.  (Also see my post last week “introducing” Power Update in case you missed it).

Take it away, Tim…

I first found out about Power Update two months ago via a LinkedIn post by Christian Floyd.

It took me a while to realize that he wasn’t talking about a theoretical future idea, but an actual product, something that exists today. Click the picture below to see the entirety of my foolishness. It wasn’t until I talked to him directly that I realized what Power Update really was and I was immediately interested.


He got me a beta version of Power Update and I began testing it at the company I work for: a manufacturing company in Cleveland, OH called The Robbins Company.

Our Background

We started using Power Pivot at The Robbins Company back in 2013 and I wrote about our experience on this blog (click here).

Read the rest of this entry »

Visit Us at PASS BA CON! (Apr 20-22, Santa Clara)

February 5, 2015

Post by Rob Collie

Excel Speakers are a BIG Presence at This Year's PASS Business Analytics Conference

The “Modern Excel” Vibe is Taking Over This Year’s Speaker Lineup.
Come Hang Out With Us…  And Oh Yeah, Attend Our Sessions Smile

Getting the Band Back Together!

Holy cow folks, am I stoked!  Look at that lineup!  I’d go to this event (PASS Business Analytics Conference) just to HANG OUT with these people.  Other than the Excel MVP Summit at Microsoft HQ, you don’t ever really find this many “EXCELebrities” in one place.  (See what I did there?  I made a new word!  Let’s make it stick.)

But unlike the MVP Summit, this event is open to the public.  Come hang out with us.  And oh yeah, attend our sessions too!

Registration Discount Code

Register using the official PowerPivotPro discount code BASPROB and save $150.

Register Here and Use Code BASPROB

Full Day Pre-Conference Sessions by Rob/Avi… and Chandoo!

Read the rest of this entry »

Introducing Power Update!

February 3, 2015

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 of this entry »

Upcoming Webinar at PASS BA Marathon Tue Feb 3rd

February 2, 2015

By Avi Singh [Twitter]

Thanks to all those who attended our past Excel to Power Pivot webinar on January 20th. Some of the attendees also joined us later for our first ever online class. For those who missed out, keep reading to learn about our upcoming options.

First of all, it is a joy and a privilege to speak to all of you about my favorite topic – Power Pivot/Power BI. So thank you for giving me that opportunity. My favorite part was the Q&A session at the end where I got a chance to interact with all of you. At the bottom of this post you will see some of the key questions, that were asked and answers. I figured that might help all the readers of this blog.

We plan to be running these webinars every month; best way to stay informed of our upcoming events is to subscribe to our blog. We are locked onto the Citrix/GoToMeeting platform, which has performed flawlessly for our recent events. But we are looking to increase the maximum capacity, to accommodate more attendees.

There is a special opportunity, to attend our webinar on February 3, as part of the BA marathon, being conducted by the SQL PASS folks. Details as below:-

SQL PASS Business Analytics BA Marathon

Productivity Revolution in Excel By Avi Singh, Chandoo
Tue Feb 3rd 11AM PST (click to change Time Zone)
Register at PASS BA Marathon

Yes, I am collaborating with the “awesome” Chandoo for this Webinar. Gosh!

…now as promised here are the popular questions from our past webinar.

Questions from past Webinar

Some questions were asked over audio and others via the chat window. Some of these topics are meaty enough that we will follow up with full blog post and/or YouTube Video.

Read the rest of this entry »

Dynamic Age Calculation Using Measures

January 29, 2015

by Matt Allington

I was recently helping a forum member at with a problem about how to dynamically calculate an employee’s age.  I thought a worked through example would make a good blog post as it demonstrates a further use of disconnected tables vs the more common “disconnected slicers” tables that Rob loves so much.

First let me explain the scenario.

Number of Employees Under the Age of 35

The requirement is to be able to calculate the total number of employees under age 35 years of age at any point in time.  The DAX formulae therefore need to take into account new employees starting at the company, employees leaving the company, as well as the fact that all employees get older every year.  Here is the solution I created. Read the rest of this entry »

Power BI, YouTube for Workbooks: More Affordable!

January 27, 2015

Post by Rob Collie

Power BI = YouTube for Workbooks, But Also Now Something Else

$9.99 Per User Per Month Starting Feb 1!

Wow, this is awesome.  What I have been calling “Power BI Online” – which is one of the easiest ways to get a Power Pivot / Excel server, aka YouTube for Workbooks, is dropping in price from $40 per month per user to $9.99 – us math folks call that a 75% price cut.

So if you’ve been “priced out” of the server market up until now, next month may be your opening.  Read the Microsoft Announcement.

And in arguably bigger news… MS announces a FREE version?

My Power BI V2 Preview Site - 100% Free Forever

This is the Preview Site That I Signed Up Today.  Took Two Minutes.
And it Will Continue to be 100% Free.

Even more interesting (perhaps) is that today MS launched a “Power BI v2” – in Preview mode.

As part of this v2, they are introducing a two-tier pricing plan: Free and Pro.  The plans can be viewed by clicking the image below:

Power BI V2 - Pricing Card

Click the Image to View the New, V2 Pricing Plans

Comparing the three versions:  V1, V2 Free, and V2 Pro

***UPDATED as of Jan 29

I think it’s fair I’ve quickly thrown together the following table based on what I understand so far:


Power BI V1

V2 – Free

V2 – Pro

Available Today?

Yes – Has Been for Awhile

In Preview (Preview is somewhat a hybrid between Free & Pro)

In Preview (Preview is somewhat a hybrid between Free & Pro)

Can You Use Excel as Your Report Surface?


No – But Maybe in the Future?  (This is a BIG question for me of course)

No – But Maybe in the Future?  (This is a BIG question for me of course)


$9.99 per user per month (but that’s on top of SharePoint Online Pricing, so actually it’s more than $9.99)

(and will remain
free even after
the Preview ends)

$9.99 per user per month (and so far, it seems like that’s an all-inclusive price, but we will wait and see)

Can you use their new Power BI Designer to build dashboards?




Does it Support Automated Refresh of Models and Reports?

Yes – Through the Data Management Gateway

Yes – But Only via SaaS data sources like OR via “grabbing” workbooks from OneDrive (there is no connectivity to on-premises data sources)

Yes – everything that Free can do, PLUS connectivity to on-premises data sources via the Data Mgmt Gateway

Data Refresh Limits

1x Daily Refresh, Unclear if there is a “rows per hour” limit, but likely this becomes equivalent to Pro in the future (1x Hourly and 1M Rows per Hour)

1x Daily Refresh, maximum of 10K rows per hour.

1x Hourly Refresh, maximum of 1 Million rows per hour.

Data Capacity

250 MB per workbook, but you can seemingly upload an unlimited number of those today. Might be Reduced to Pro Level in the Future (10 GB per user – which is still A LOT.)

1 GB per User (which is quite a bit considering how much you can fit into a compressed Power Pivot model, but I think still capped at 250 MB per workbook?)

10 GB per User (but I think still capped at 250 MB per workbook?)

Can it “live connect” to on-premises SSAS servers, thus serving as a UX layer for data that remains safely inside the corporate firewall?

I Think This is Planned but Not Yet Ready




  1. It sounds like the Preview is a “mish-mash” of what Free and Pro will look like once they move from Preview to General Availability.  I’m not 100% on the details yet, but it sounds like maybe they don’t have the data refresh limits in place yet for instance, AND you can connect to your SSAS on-premises servers.  So Preview is kinda… Pro.
  2. OneDrive-based workbooks as refreshable data sources – for the time being this means “Power Pivot Data Model Workbooks” as opposed to “sheets of raw data in Excel.”  Also, it’s up to YOU to get that OneDrive workbook updated – those don’t refresh themselves as part of this service.

I will continue to update this as info becomes clearer.  And please feel free to let me know if YOU know how to clarify it in any way.

Last Note:  Never “Sleep” on Mount Redmond

Mount Redmond :)

In JRR Tolkien’s “The Silmarillion,” Mordor unveils a secret weapon – the world’s first dragon – and unleashes it on Middle Earth.

This dragon – named Glaurung – engages the forces of light, immediately gets shot full of arrows, and then retreats back into Mordor, never to be seen again.  Everyone regards Glaurung the Novelty as a joke, and forgets about this failed little experiment.

Centuries later though, Glaurung re-emerges, now MUCH older, bigger, and equipped with incredibly thick scales.  Glaurung v2 is an unstoppable juggernaut, lays waste to entire kingdoms, and takes one of those kingdoms as its lair.  Glaurung basically says “how you like me NOW?”

I see Microsoft, and honestly myself, through this lens.  Never look at their first effort and think that’s the end of it.  They are hampered by politics and Big Company inertia, for sure, but they never stop chugging.  Rust Never Sleeps, said Neil Young.  Neither does Microsoft, and neither should your perception of them.