The Only “Requirements Doc” You Will Ever Need

January 20, 2015

Post by Rob Collie


One of Our Clients Sent This to us Before a Consult.  It is Perfection.

The Dysfunctional Myth of “Requirements Discovery”

A traditional BI project typically starts with “requirements discovery.”  This is where YOU, the business, get to spend multiple days, weeks, or even months teaching someone else (a BI consultant or internal BI pro) about your business.

There are MULTIPLE problems with this traditional methodology:

  1. YOU are the teacher (teaching about your biz), but YOU are paying.  Seems backwards yes?  Money usually flows in the opposite direction of knowledge transfer.  But not in BI.
  2. YOUR time is a MASSIVE hidden cost.  In addition to the fees you may be paying, don’t forget that YOUR time is being consumed in the process.  If we were going to put an “honest” cost on a BI project, the time consumed by Business personnel should be included.  (And that’s a lot more than your salaries – there’s opportunity cost to the business as well since you aren’t doing OTHER things).
  3. When the dust settles on “discovery,” the BI Pro STILL does not understand.  Oh, everyone PRETENDS that “discovery” is over, but really, it’s just begun.  In a few weeks, when the first “results” come in from the BI Pro, you will then, ahem, discover that there were tremendous misunderstandings.

This dysfunction is the root of BI failure.  Projects that never end.  Projects that end, but under-deliver.  And also…  projects that never even start.  If you’ve ever said “we can’t afford BI,” you were both simultaneously correct AND unknowingly reacting to this dynamic.

This dysfunction is NOT your fault, NOR is it the BI Pro’s fault.  You see, we’re terribly ineffective at communication, us humans – both on the “send” side AND the “receive” side.  No exceptions.

Requirements Discovery Works MUCH Better on Planet VulcanThe people involved are FINE.  It’s the methodology that’s broken.  I don’t have a single critical word for the PEOPLE involved in such projects.  We’d need to be Vulcans, equipped with the Mind Meld power, to make this approach work well.

So WHY did a bad methodology gain acceptance in the first place?  Because the software vendors did this TO the world.  Perpetrated it ON the world.  They built software that REQUIRED this sort of methodology.  All of the big players share historical blame here – Microsoft, IBM, Cognos, Business Objects, Microstrategy.  All of them.  Let’s shine a light on them.

Our Villain:  The Ivory Tower “Arrogance” of Past BI Software

Read the rest of this entry »

Reminder: Webinar – Excel to Power Pivot Jan 20th (mini-post)

January 19, 2015

Folks, our Webinar: Excel to Power Pivot, is coming up today Tuesday Jan 20th (click to add to calendar). We hope to see you there. Participants would also have an opportunity to sign-up for our online class at a discounted rate.

Here is what we would cover, see event page for more details:-

Walkthrough key steps in using Power Pivot and explain how Power Pivot supercharges regular Excel
Do it Yourself! We would provide you dataset and instructions to try those steps yourself at your own pace and build your first data model. Best way to learn is by doing.
Q&A: There would be opportunity to ask questions and seek guidance regarding using or deploying Power Pivot or Power BI
Resources to continue your journey

Excel Power Pivot steps we would cover in our walkthrough:-

Pull data into Power Pivot
Build a Data Model
Write new Measures (Formulas)
Write time-intelligence Measures

Subscribe to our blog to receive updates on future events.

Power On!
Avi Singh

Power Query for Excel: Combine multiple files of different file types

January 15, 2015

Guest Post by Miguel Escobar - Twitter | Youtube | Blog | Website

Power Query Magic:  The Ultimate and easiest way to consolidate multiple tables, sheets, text and/or csv files

Power Query Magic:  The Ultimate and easiest way to consolidate multiple tables, sheets, text and/or csv files
(Click for Full-Size Version)

At some point in the life of an Excel user, we have all faced a similar dillemma. How can I combine multiple sheets, tables, csv or txt files? (can I combine them all together??)

How we used to solve this scenario

Back in the day (before Power Query) we actually had some ways to do so but they were not so user-friendly and they relied heavily on coding or some tedious way of doing it. The most common ways were:

  1. Using SQL Statements to join multiple files
  2. Creating a VBA code that will do the job for me
  3. Going with the tedious way of combining the files manually (perhaps with Excel or Access)

But now we have an easier and optimized way of doing this..let’s find out how

Read the rest of this entry »

Power BI as Google Docs Antidote

January 13, 2015


“Help ME (Power BI)…  Help YOU (Office).”

Reviving the “Open Letter to MS” Tradition

In the past, I’ve written some things aimed at my former Microsoft colleagues.  Places where I think their strategy could benefit from adjustment.  Generally speaking, those posts have been about how the “BI teams” at MS should better leverage their Excel advantage.  Given recent developments, I think those messages are more important than ever.

But today, I am reversing that lens, and talking instead about how the Power BI suite of tools is a tremendous gift to the Office team.  Aha!  Bet you didn’t expect THIS dramatic turning of the tables from Rob “Excel is Everything” Collie, DID YOU??  Gotta stay on your toes around here.

You Don’t Have “Users.”  You Have “Producers” and “Consumers!”


I think the word “user” is responsible for a lot of strategic damage.  It lumps everyone into one big, convenient bucket – hiding some crucial, underlying dynamics.

Read the rest of this entry »

PowerPivot Live Online Class in January and New Year Resolutions

January 12, 2015

By Avi Singh [Twitter]

We wish all the readers of PowerPivotPro a very happy new year. We wish that you get your groove power on this year :-). I have never been big on New Year’s resolutions. For good reason; reportedly only 8% of people achieve their New Year’s resolution. Sharing your resolution socially and publicly, is one factor that could help you get there though (check out other factors).

So here I am, going out on a limb and saying:

I will help a thousand people to start using Power Pivot

That is my BHAG (Big Hairy Audacious Goal) for the year. I had a late start in 2014, but here is how many I helped last year (via training, webinar, workshops, consulting).

Here is a geographical representation. Yes that’s an embedded Power View (view full screen); more on that in a later post.

In order to get to this goal, we will continue existing offerings but also add some new ones:-

  • Live In-Person classes
  • Online University
  • Consulting
  • Webinars
  • *New* Live Online Class

      New! Live Online Class in January

      I am excited to offer our first live online class. Click below for details and registration.
      There are limited seats for first online class as we ramp this up.

      Read the rest of this entry »

    • Cleveland and D.C. In-Person Classes in February!

      January 8, 2015

      Post by Rob Collie

      How recent students have described the class


      One of Our 2014 Classes

      Washington D.C. Feb 24-25 and Cleveland Feb 17-18

      Washington DC Class Info

      Register for Washington DC Class!

      Cleveland OH Class Info

      Register for Cleveland OH Class!

      Optional Gathering/Dinner on “Middle” Night

      Read the rest of this entry »

      Would You Like to Join Our Team in 2015?

      January 6, 2015

      Post by Rob Collie


      Do You Have the “Data Gene?”  Know Your Way Around Power Pivot?
      We’re Looking for You in 2015!

      Hi folks, we’ve done this a number of times on behalf of our clients, but never before have we solicited resumes for PowerPivotPro itself.

      Well, I think that changes in 2015.

      Now, to be perfectly clear, we’re not hiring immediately, nor are we 100% certain yet on what kinds of positions we will be filling.  (Nor how many team members we will add).

      But I’m confident that we WILL be growing the team this year, and I’d like to start getting a sense of who is out there that would be interested.

      So, if you’re even curious about what this might look like, click below and drop us a quick note:

      Me?  Yeah, I Like Changing the World!  :)

      Mini-Post: Excel to Power Pivot, Webinar Jan 2015

      January 5, 2015

      by Avi Singh [Twitter]

      Pop quiz: For 2015, do you
      a) change nothing this year?
      b) Embark on a brand new path of empowered BI?

      Calling out to folks who are sitting on the Excel fence and have not dipped their toe in Power Pivot.
      Or business groups that want to explore a whole new way of doing BI, that is low cost, agile and delivers results.

      Join us for our next Webinar: Excel to Power Pivot: Cross the Gap, Tuesday Jan 20th 2015

      Click here for Webinar Details

      For many readers who have already crossed the gap, please forward this to those who need help.
      We did face some technical difficulties in our first attempt on Dec 1st, but since then have switched to using GoToMeeting – world leader in web conferencing – and it has performed superbly. Thanks for bearing with us as we experiment with this new format.

      See You in 2015!

      December 16, 2014

      Post by Rob Collie


      On Hiatus (from Blog Only) Until 2015

      After 120+ published posts in 2014, we’re gonna give our blogging energies some much-needed recuperation time from now until the end of the year.

      But that doesn’t mean we are resting!  Far from it.  Lots in the pipeline for early 2015, including a full “reboot” of this website in terms of its design and capabilities.

      The rest of the “biz” stuff is also continuing without interruption.  Work with clients, the Online University, everything like that basically continues unabated.  Secret projects as well – we always like to have a few of those simmering at all times.

      See you in January. Smile

      SQL Date Tables in Power Pivot

      December 11, 2014

      Guest post by Thomas Allan

      SQL to Power Pivot

      Intro by Avi: As in Excel, in Power Pivot there are often many ways to accomplish the same thing. That is usually a sign of strength of the tool.  Although it also makes it more challenging/fun to be able to weigh the options and decide which one works best for you given your situation. Thomas, shows us a cool way to pull Date table from SQL.

      Stepping back a bit, there is some good interplay between Power Pivot and SQL. In terms of feeding Power Pivot using SQL – see Why PowerPivot is Better Fed From a Database Part 1 and Part 2. And also Power Pivot being a great addition for SQL savvy folks – see I Know SQL Queries, So Why Do I Need Power Pivot?. Goodness all around, I say :-)
      Take it away Thomas…


      In addition to often mentioned benefits of using SQL servers as data stores (flexibility, reliability, scalability and security), the benefit of linking to a centralized source that delivers results quickly, consistently to practically any client, anywhere, adds a powerful dimension of “portability“ from the outside of the Excel workbook as Power Pivot and DAX formulas offer on the inside.

      Date Table

      Four types of resources are often used to create date tables within Power Pivot: 1) Excel itself, using formulas or VBA, 2) data feeds, which you can find an example of following this hyperlink, 3) Power Query, which you can find an example of following this hyperlink, and 4) relational databases.

      The example that follows was developed using the relational database SQL Server 2012 and uses only table-valued functions. Although the code was developed on SQL Server 2012, it was also tested on a 2008 release of Microsoft’s flagship database product.

      For demonstration purposes, the solution offered here is based on a calendar fiscal year (quarters start January 1, April 1, July 1 and October 1). For other types of calendars, such as 4-4-5 or school semesters, the code can modified by a SQL developer (also, for other types of calendars, some architectural issues may also apply inside Power Pivot, which are fully explained in Rob Collie’s comprehensive Power Pivot course).

      This post assumes that the reader has basic familiarity with SQL Server Management Studio, sufficient to install table-valued functions, or has access to someone who knows how to install table-valued functions. This post also assumes familiarity with connecting to a SQL Server database from within Power Pivot (similar to connecting to an Access database).

      Download SQL code below.

      Read the rest of this entry »

      Help Us Design Our 2015 In-Person Class Schedule!

      December 10, 2014

      Post by Rob Collie

      Seattle?  Cleveland?  Elsewhere?

      Hi Folks, we’re already starting to plan our 2015 class schedule and we could really use your quick input.

      By default, we plan to offer classes in Cleveland and Seattle.  Cleveland classes will be taught by Rob, and Seattle taught by Avi (who has already worked with double-digit clients, including five onsite trips, and just overall been blowing people’s doors off).

      But we don’t know whether, for instance, we should be scheduling CLE and SEA for the same month.  Nor do we know what 2-3 “hot spots” we should try to visit outside of those two obvious “home” cities.

      Only the first question is “required” – the other 4 are purely optional.  Many thanks!

      Repeat Customers in DAX: Three Flavors

      December 9, 2014

      Post by Rob Collie

      Repeat Customers in Power Pivot / DAX:  By Number of Transaction Lines, By Number of Distinct Orders, and Allowing for Cross-Year Return Customers

      In 2004, There Were 2,561 Customers Who Bought Something in the Southwest.
      But How Many of Those Were Repeat Customers?  Depending on How We
      Define “Repeat,” We Can Get at Least Three Different Answers.

      A Right Turn at Albuquerque…

      I sat down today to write about “Disconnected Cube Formulas” – yes, you heard that right.  A brand new technique that I think has some pretty exciting (yet admittedly narrow) applications.

      But along the way, like Bugs Bunny, I ended up doing something at least as interesting.  So let’s do that one first.

      Setting Up the Problem

      I have four relevant tables:  Territories, Customers, Calendar, and Sales:


      The first three are Lookup (aka Dimension) tables, and Sales is a Data (aka Fact) table.

      Active Customers is a pretty easy formula:

        [Active Customers]:=


      Read the rest of this entry »