In-Person Power Pivot Class in Seattle, Nov 11-12 Signups Open

October 22, 2014

Intro by Rob:  You Will See What I Mean

Last week I explained why Avi is the Right Guy For the Job, and mentioned that he might be a better “me” than me.

Everyone in the Seattle area now gets a chance to see what I mean, as he will be teaching my official class there in November.  (He joins Matt Allington as the only two human beings I have certified to teach this class, other than myself, and it’s well-deserved company for both gentlemen).

Enough from me though, I’ll let Avi take it from here.

PowerPivotPro Comes Full Circle, Returns to Seattle

image

Click for Info on the Seattle Class

Rob started his Power Pivot journey here in Seattle as one of the founding engineers for Power Pivot. I feel honored to bring that full circle and offer the next  Power Pivot class right here in Microsoft’s backyard. I have spent past several years at Microsoft, building large scale BI systems based on Power Pivot; and training internal and external users alike. Read or watch my Power Pivot journey or read all my posts.

Read the rest of this entry »


Excel to Power Pivot: Cross the Gap, Change the World

October 21, 2014

by Avichal Singh

Oh boy! What an intro…thanks Rob. “A better me than me”…now I would need to live up to that. Let’s get to work.

Change the World

What happened to ‘Cross the Gap’ you say. Well, that’s covered later. This is more important, plus we are never about doing things in the expected order (e.g. we like building BI faucets before plumbing Smile).

#1 challenge with Power Pivot is awareness. You can generalize that for Power BI, although each component of Power BI also has it’s unique challenges (e.g. Power Query).

#1 road block in spreading awareness is Power Pivot availability.

  • Excel 2010: Power Pivot is a free add-in
  • Excel 2013/Office 365: you need to be a maze runner Smile (see illustration below)

    Either the whole world should downgrade back to Excel 2010 or we can beseech Microsoft to do something about Excel 2013/Office 365. I tossed a coin and the latter won out. So here it is folks:

    Change.org Free Power Pivot!

    CALL TO ACTION: Sign yourself and recruit others to support the Change.org Petition to Microsoft to “Free Power Pivot!”. Make Power Pivot freely available in Office 2013, Office 365 (and beyond)

    Check it out, sign the petition, ask your friends, neighbors, heck even call your Grandparents.

    One clarification though, the request to Microsoft is
    - To make it “freely available
    - and not necessarily “free”, as in a free handout
     

    If you need more inspiration, read

  • Bill Jelen (Mr Excel)’s lament

  • Comments on the original post: “Hey, Who Moved My (PowerPivot 2013) Cheese?”

  • Scott’s comment on the petition says it all “Amazing product, amazingly hard to getRead the rest of this entry »

  • Reinventing an Industry: Welcome Avi Singh!

    October 16, 2014

    Post by Rob Collie

    Our Merry Band Has Found Its Luke Skywalker

    GENERAL MADINE:  “General Solo, is your Strike Team Ready?”
    SOLO:  “We’re making huge strides.  The Death Star is Toast.”

    Avi Singh Joins PowerPivotPro Full-Time!

    Avi SinghFile under “Coup, Major.”  Folks, this is a CRAZY cool moment for us at PowerPivotPro.  We like to think of ourselves as the Rebel Alliance of Data, and seriously, we feel like we’ve found our Luke Skywalker.

    That’s Right:  Avichal (“Avi”) Singh has left Microsoft to become a full-time member of the team here at PowerPivotPro.  Microsoft’s loss is our gain, which is…  ALSO Microsoft’s gain, ultimately.  Because in this role, Avi is going to “light up” people and organizations all over the world.  As opposed to performing data miracles strictly within the internal Microsoft org, he’s going to be helping MANY OTHERS perform their OWN miracles.  With MS’s platform, of course, which is great for them.

    So hey Microsoft, please don’t be TOO upset with us for poaching him.  But really, we did nothing of the sort.  He um, kinda stalked US.  Let me explain.

    To Build A Different Kind of Firm, You Need a Different Kind of Person

    Avi Singh With Hand on Heart

    I had just finished a presentation at BA CON ‘14 in San Jose, California – a presentation I had opened with the theme of “I have the best job in the world – I work in data, and I get to spread HAPPY wherever I go.”  (Which is the truth, btw).

    Out in the hallway after the talk, I met Avi in person for the first time.  Like me at the time, you probably know Avi primarily through the excellent guest posts that he has penned from time to time. 

    The written word does not do justice to the “in person” version of this human.  He is one of the absolutely most sincere, genuine, and positive people I have ever met, and that was clear in the first two minutes.  (If I say he’s in the Donald Farmer Zone, I mean it as a compliment to both gentlemen).

    What followed was LITERALLY a “hand on heart” moment.  The very first thing Avi said to me, in reaction to the talk I’d just given, was to zero in on that “spreading HAPPY” theme.  He said something like “That’s so true, I’ve seen it!  And I feel it…  in my heart.”  And to emphasize that last part, he lightly bumped his fist to his heart.  That moment was so incredibly genuine, and conveyed several years’ worth of information in an instant:  this guy was special.

    You kinda had to be there to see why that was so impactful, so convincing to me, but if you get to meet Avi in person, you will see what I mean.  And I think you will ALSO see what I mean in the coming months, as you hear from him more often on this site and in other media.

    “Different” Kind of Firm?  “Reinventing” an Industry?

    Read the rest of this entry »


    Power Query Adds SalesForce Connectivity: Totally Awesome, But Trouble Looms Ahead

    October 14, 2014

    Post by Rob Collie

    SalesForce Data Into Power Pivot / Power BI?  YES!

    This is what it looks like when Microsoft Does Something Epically Awesome…
    …But Dark Clouds Loom – Read on to Learn Why

    We LOVE This!

    Seriously, we do.  It’s AMAZING.  Multiple of our clients are going to jump all over this.  It’s going to change their culture – AGAIN.

    If Power Query can connect to it, that lets you pull SalesForce data directly into Power Pivot.  No more export, save, import/paste/etc.

    SalesForce Data Into Power Pivot / Power View? YES!

    Power Query Can Import SalesForce Data Directly Into Power Pivot, Which Means You Can Then Visualize in Power View, For Instance

    So, the primary point of today’s post is to make you aware of this new capability, and tell you how to get it.

    Read the rest of this entry »


    Reminder: Live Class in Philly in < 2 Weeks

    October 10, 2014

    Post by Rob Collie

    PowerPivot University - Philadelphia

    Final Reminder:  I Will Be in Philadelphia Oct 21-22, Teaching a Two-Day Class
    (at the Microsoft Facility in Malvern, specifically)

    1. More Details
    2. Blog Post on Why You Should Attend
    3. Register

    See you there!


    How about a FIRSTDATE()?

    October 9, 2014

    Guest Post by Willem van Dijk

    Intro

    I am, like most (if not all) visitors to this website, an addict for information –> data –> truth –> Εμπειρία <-hope that sounds like Greek to you…

    My journey into Power Pivot has been one of uphill struggles and downhill thrills. It’s been brief moments of ego feeding, immediately followed by lengthy bouts of meekness. One moment I am Indiana Jones going for his gun in The Raiders of the Lost Ark directly followed by the same scene in The Temple of Doom


    Where did my DAX go?

    Read the rest of this entry »


    Blending “Time of Day” Analysis with Calendar/Date Analysis

    October 7, 2014

    Post by Rob Collie
     
    Blending “Time of Day” Analysis with Calendar/Date Analysis in Power Pivot and Power BI

    Our “Morning” Website Traffic is Down 21.5% in Jan 2014 vs. Jan 2013, But “Evening”
    Traffic is Up by a Similar Amount, and Full-Day Traffic is “Flat” at +0.9%

    (Fake Data, But Real Analysis)

    Two Different Flavors of “Time”

    Usually, when we talk about “time” in Power Pivot, we’re talking about the Calendar/Date flavor:  How much have things changed from yesterday to today.  What are our Month to Date numbers, and how do those compare against the same period last year?  Let’s call this “macro-trending.”

    But time of day is also often interesting:  what are the trends WITHIN a day?  Let’s call this “micro-trending.”

    And then, the hybrid of the two:  how are our “micro” trends changing over the course of the year, month, quarter, etc?

    I don’t think the techniques here are terribly complicated, but they might be a little difficult to conjure up on your own.  So, it’s time for a post – and a downloadable workbook! Smile

    The Key:  Separate the Date and Time Components!

    Read the rest of this entry »


    Winston Wolf Has a Power Pivot Job Opp For You

    October 3, 2014

    Post by Rob Collie

    image

    That’s a Pretty Badass Quote, Prominently Displayed When You Walk Into KAMAN HQ

    Wanna Work for One of Our Coolest Clients?

    imageMike Miskell is Winston Wolf.  That’s basically his job description.  When the executives at KAMAN identify a problem, they send The Wolf to fix it.  And The Wolf, is Mike Miskell.

    Last year, The Wolf found Power Pivot.  Nine minutes and thirty-seven seconds later, KAMAN had revolutionized their entire culture around profitability.  (You get the 9:37 reference, Pulp Fiction fans?)

    (For the record, I don’t think Mike has even SEEN Pulp Fiction.  He’s just The Wolf naturally.  And he promised me he’d watch the movie if I posted this.)

    We even got on stage together, The Wolf and I, at PASS BA 2014 in San Jose to talk about the success of this project.

    We then co-presented on a VERY different stage, last month, at the Secret Microsoft/PowerPivotPro Summit.

     

    Read the rest of this entry »


    5 Interactive Chart Techniques Come Together

    October 2, 2014

    by Matt Allington

    Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.com, from searching the Web, as well as some of my own ideas.  While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated.  I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user.  There is a link to this workbook at the end of this post.

    The techniques I have used are:

    1. Disconnected slicers used to create interactive chart series
    2. Cube formulae and standard Excel to make an interactive chart title

    I love these 2 tips I learnt from Rob – so user friendly.  However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.

    1. Cube formulae and standard Excel to make an interactive legend
    2. VBA and “link to source” for interactive axis formatting
    3. Excel VBA to change which Axis the series appears on.

    I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below.  I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.

    Now let me call out the key techniques I have used to make this workbook rock.
    Read the rest of this entry »


    “I Know SQL Queries, So Why Do I Need Power Pivot?”

    September 30, 2014

    Post by Rob Collie

    because I am very familiar with databases and the ability to create custom SQL queries and data models using SQL Management Studio,  I struggle to see why I would need PowerPivot if I can do much of this heavy lifting using SMS.

    Got This Question the Other Day, and it is LONG Overdue That I Answer It

    Some of You Are Confused, Some Are Nodding

    Generally speaking, I think the people reading this fall into one of a few camps:

    1. People who are early in their Power Pivot journeys, and who also do NOT know SQL (most Excel Pros fall into this camp at some point, before hopefully moving into group #2 below).
    2. People who are pretty good at Power Pivot, but do NOT know SQL (I fall into this group).
    3. People who are good at BOTH Power Pivot and SQL (this is a blessed group).
    4. People who are good at SQL but still early in their Power Pivot awareness/knowledge.

    Group #4 is the “target audience” for today’s post, but it’s still relevant for groups 1-3, because we WILL get asked this same question from time to time, and it’s good for us to be able to answer.

    “I Started Out as a DBA…”

    SQL Queries as BI - More Common Than Really Any Other Method of "Official" BI

    For Many People and Organizations, THIS is Business Intelligence
    (And to a Certain Extent, This is Effective, So it Persists as a Workflow)

    Let’s say you began life a a DBA.  Which means you know SQL, of course, but writing SQL is not the only thing you do as a DBA.  You’re maintaining indexes, watching for bottlenecks, talking about I/O, number of spindles, TempDB…  all that good DBA stuff that I understand at a conceptual level but have never learned to actually DO.

    But one day, someone from the Business has a question.  They figure all the data required to answer it is “owned” by you, so they come to you with said question. 

    And hey, it turns out that you CAN write some SQL and answer the question!  Which is pretty damn helpful and makes everyone involved feel pretty good.  (Hey, we are all still fundamentally wired for cooperation after all).  It also makes you more relevant to the front-line business, and no longer “just a cost center” from the perspective of the company’s leaders, which is VERY good for your career.

     

    Read the rest of this entry »


    Aussie Aussie Aussie! Pivot Pivot Pivot!

    September 26, 2014

    Post by Rob Collie

    Just a quick reminder for our friends Down Under, make sure to catch the one and only Matt Allington at one of his upcoming classes in Adelaide, Melbourne, Brisbane, and Sydney. 

    I wouldn’t let just anyone teach my materials – Matt is one fantastic dude.  It will be fun, productive, and life changing.  I swear on the CALCULATE function.

    2-day live Power PIvot training in Australia!


    Leverage SharePoint for Rich User Interaction

    September 25, 2014

    Guest Post by Scott at Tiny Lizard

    Hold onto your hats, my friends.  We have some pretty advanced stuff for you today!

    If you host your workbooks on SharePoint, you are about to read some powerful techniques, and hopefully give you some “brain-fodder” for related ideas.  Even if you aren’t using SharePoint today… it’s worth reading to see the types of things possible with SharePoint, then you can refer back when SharePoint enters your life.

    I am going to show two techniques to allow end-users to have some level of interaction outside the bounds of the workbook… say, to drive data into the underlying data sources.  The first technique is not nearly as fancy as the second…Smile

    Read the rest of this entry »