Dear Accountants: PowerPivot is your friend!

May 28, 2015

Guest Post by: Mike Griffin

Intro by Avi: Power Pivot and Power BI tools can be used to transform BI for a wide array of industry verticals and vocations. But it is especially suitable to some roles; Accountants are probably at the top of that list! Our friend Mike here is a Financial Manager in the interesting vertical of Cruise Lines. And has a post for us describing just one of the ways they are using the Power BI tools, in this case to find needles in the haystack. Take it away Mike…

Accountants are NOT typically data GEEKS

Accounting related problems open doors to a different set of applications for PowerPivot and PowerQuery. Although it’s fair to say most accountants like numbers, an affinity for numbers does not always translate into a love for data – especially lots of data. This example illustrates how PowerPivot and PowerQuery can be used to help automate accounting related tasks that can be incredibly time consuming when a lot of data is involved.

The scenario I’m presenting is not sophisticated in terms of DAX formulas and is very simple from a data modeling point of view. However, it’s an incredibly useful application of the tools we use as PowerPivot enthusiasts that can save valuable time when closing the accounting period.

The Accounting Need: Remove needles from the hay stack

Use Power Pivot and Power BI to look for the proverbial needles in your data haystack
Use Power Pivot and Power BI to look for the proverbial needles in your data haystack

In this scenario, I need to reverse invoice specific journal entries that were originally posted as part of an automated process between an internal database and our accounting software. This entry is posted as a batch with thousands of other invoices (the original journal entry can’t just be reversed).

Read the rest of this entry »


Some Good Power Pivot / Power BI Computers

May 26, 2015

Post by Rob Collie

Good Computers for Power Pivot / Power BIA Lot More Work Than Expected!

Last month I posted a survey of computer performance for Power Pivot and Power BI usage.  I underestimated how much work it would be, to synthesize the results into something useful for the community.  At bare minimum, this has been five different tasks:

  1. Throwing out untrustworthy outlier results
  2. Cross-referencing with CPU benchmark sites
  3. Finding computer models that contain those CPU’s
  4. Verifying that those models have good RAM
  5. Pulling together a view different price points
  6. Repeating for Desktop vs. Laptop

So for now, I’ve only managed to pull together options from HP.
(I will add other manufacturers later, but I have always liked HP hardware, especially their laptops, so it’s a great place to start).

Desktops – High End Options

If you want a true beast of a computer that chews through DAX workloads, you might consider something like THIS monster:

 The Z840 Workstation is an Obvious Candidate.
(But the Next One Below is Better AND Cheaper.)

16GB of RAM is more than enough for Power Pivot and Power BI workloads.  Seriously, 8 GB is gonna be enough for most of your needs until/unless you start transitioning into SSAS Tabular because you have too much data for Power Pivot.

Note that this workstation above offers much faster RAM than most other machines, at 2133 GHz.

But it also carries the ultra-premium Xeon E5 2620 v3 CPU – which the test results and benchmarks indicate is a LESS effective processor (for our purposes) than many lesser models of Xeon, such as the E3 family.

So, for $1300 less, you could have the following workstation, which in all likelihood performs even BETTER for our needs: Read the rest of this entry »


Red Nose Special: 24 Distinctive Ways to Die

May 21, 2015

By Avi Singh [Twitter]

Folks, May 21st is being celebrated as Red Nose Day across USA. Goal is to have some fun and raise money for children living in poverty. We at PowerPivotPro mix a bit of black humor in today’s post and promise to donate a portion of the proceeds from our upcoming PowerPivot Online Class (Jun 8-9) to Red Nose Day.

For today’s special, we were inspired by the CDC released graph showing distinctive causes of death by state. Not content to stare at a picture, we rebuilt the model from scratch using Power Query, Power Pivot and Power Map. Which lets us slice and dice and visualize the data various ways to our heart’s content.

Our results match closely with that of CDC, slight differences may exist since we use 10 year span from 2004-2013, whereas CDC researchers reportedly used 2001-2010.


Power Map Rendering of Distinctive Cause of Death by State

Distinctive cause of death


Distinctive Cause of Death by State (Click to see full list)

Read the rest of this entry »


Grab Bag of Fun

May 19, 2015

Post by Rob Collie

image

Assortment of Quick and Fun Topics

I keep something called Ye Olde OneNote List of Future Blog Topics.  You know, I think of something and go “ooh, that would be a good thing to write about,” so I add it to YOOLOFBT.

Problem is, said list sometimes grows a lot faster than the rate at which I produce finished blog posts.  So…  good topics just get buried sometimes.

Today, let’s make sure a few of those topics get to see the light of day.  Like children who’ve been cooped up in the house all winter, they need to go outside and play in the yard.

#1:  DataZen Post “Wins”

Last week’s post on Datazen positively shocked me with the number of page views it got.  It set the all-time record for single-day views of a given post, AND that traffic also led to the single best day for overall PowerPivotPro.com traffic.  (A rising Datazen topic lifts all posts.  Something like that.)

And it wasn’t close, either.  Both new records eclipsed the former records by healthy margins.

The take-away, I think, is that mobile visuals SELL.  This excerpt from last week sums it up nicely:

image

So if you’re trying to increase Power Pivot buy-in at your company, Datazen is worth a look.  It’s a gateway drug.

#2:  Power Update Passes 2000 Downloads

Read the rest of this entry »


Power Pivot Online Class Jun 8-9: Join the Party

May 15, 2015

By Avi Singh [Twitter]

Last online class we hit an important milestone where we had our first attendee from South Africa. Why is that important? Per Google trends, South Africa is the hottest place for Power Pivot!


South Africa finally joined our party!

Besides the geographical distribution, really what kind of folks are attending our classes? Take a look at the word cloud below based on the business titles:-


Among class attendees, Analyst dominate but we have folks from many walks of life

Sure, ‘Analyst’ dominate but if you look carefully, you’ll find – CFO, CTO, Student, Marketing, Accountant, Finance and even a Director of Football Operations :-) That is the goal of this class and my personal goal too. To spread this revolution far and wide, touch folks from all backgrounds and all titles. All are welcome! The Online Class is specifically designed for the beginning user.

Your next opportunity to attend our Online Class is on Jun 8-9th. Use links below for information/registration. Keep in mind, Online Class attendees are invited to a Q&A (Office Hour) session each month to aid them in their Power Pivot/Power BI journey after the class.

Online Class
Jun 8-9th

PowerPivotPro Online Class


Power Query Super Charges the Internet

May 14, 2015

by Matt Allington

I guess most of the readers of PowerPivotPro.com will already have a pretty good idea that Power Query is awesome.  I spent time recently thinking about how Power Query has really opened up the data on the Internet and made it more accessible to us all.  It reminded me of something I worked on a couple of years ago when I first discovered Power Query. I want to share my journey from back then to demonstrate the point about how Power Query really “Super Charges” the Internet – and because the example is just cool.

First the background

The long story short is that I was frustrated one day when SalesForce.com released its “Spring” release of its software.  Now as someone that lives in the southern hemisphere, giving a software release a “northern hemisphere” season for a name is totally meaningless – let alone completely confusing.  Anyway in my rage (and spare time), I set about trying to find out what percentage of the world population experiences “southern hemisphere” seasons (go figure why! – I’m just like that sometimes).

Anyway, in my journey I found a link to a NASA website that provided tables of data with the total population of the world at every 1 degree of longitude and every 2 degrees of latitude.  So basically it was just a big text file 360 columns wide and 180 rows long, with each number being the population in that 2 degree square surface area of the earth – just what I needed to check the population in each hemisphere.

Read the rest of this entry »


Datazen = Beautiful Mobile Dashboards to Make Power Pivot Shine

May 12, 2015

Post by Rob Collie and Chris Finlan

Datazen:  A Beautiful, Mobile-Optimized Dashboard Delivery System That Makes Your Power Pivot Work Shine VERY Brightly

Datazen (The Latest Addition to Microsoft’s Suite of BI Tools) is a Mobile Monster
(Monster in a GOOD Way.  Yes, PowerPivotPro has its own DZ Custom Theme – You Can Too)

Datazen Q&A With Chris Finlan

***Intro from Rob:  Today I’m interviewing Chris Finlan of Microsoft about MS’s recent acquisition of Datazen, and what this means to us in the Power Pivot and Power BI community.

ROB:  Last month, Microsoft purchased a company named Datazen.  Most people had never heard of Datazen before, but you had pointed them out to me last summer I think.  You were already a big believer in them at that point, as were your customers.

CHRIS:  Yeah, I love Datazen.  I’m as passionate about it as you are about Power Pivot. I think it’s an extraordinary product, and have felt this way for quite some time Don’t believe me? Check out the date of my review in the Windows Store.  (Spoiler alert:  it was April of 2013 – that’s before I even applied for a job at Microsoft).

ROB:  You’re truly a trendsetter in tech and clothing.  I think one of the natural first reactions/questions from the community is, “wait, did MS just buy one of Power BI’s competitors, and if so, when do I use it versus, say, Power Pivot?”

CHRIS:  No, DZ was designed from the beginning to “only” be a visualization layer on top of the Microsoft Data Platform.  In your post on Visualizations Layers in Perspective: The Last Mile, you pointed out three key reasons at the end on why you’d buy a visualization tool.  Datazen checks all three boxes (and oh by the way, there’s no longer anything to buy – it’s simply a benefit you receive when you license SQL Server Enterprise with Software Assurance).

ROB:  Which means it’s free for many existing MS customers.  More on that later.  But I also want to talk about how DZ can be used to “light up” the great work being done by Power Pivot practitioners, because hey, that’s what we do around here.

Any Flat Table in Excel Can “Power” a Datazen Dashboard

image

EX:  Power Pivot Produces a Flat Pivot (or DAX Query Table),
and DZ Can Use That Excel File as a First-Class Data Source.
(The ONLY Server Required Here is a DZ Server – No SharePoint, No SQL, No SSAS)

Read the rest of this entry »


Free Webinar: How to Build BI Solution Using Power BI

May 11, 2015

By Avi Singh [Twitter]

Friends,

Rob made a joke in one of his classes, that “Microsoft gives you the parts to the Porsche”. It’s a great product, but some assembly required :-) The biggest roadblocks we see to Power BI adoption (the toolset, not necessarily the powerbi.com service) are

  • Lack of awareness
  • Lack of clarity on how to get started

    For our past webinars, we have attempted to focus on Excel users who are unaware of the benefits of Power Pivot. For our next one, we will attempt to target folks, who are aware, but unsure as to how to proceed. We will try to provide you the missing manual to put together your Porsche!

    The webinar is targeted towards the audience below. For more information/registration, click the links below.

    Free Webinar
    Jun 2

    PowerPivotPro Free Webinar Series

    Managers/Decision Makers: Who are looking to select a toolset to build their BI solution. Or those who have selected Power BI toolset but looking for a roadmap to implement that solution

    Technology Professionals/Excel Users: With Power BI, Excel users can go toe-to-toe with top BI professionals and roll out complete BI solutions and not just ad-hoc reports/analysis. Learn the roadmap to transform your career.


  • Harness Power Query to Gain Competitive Analysis Insights from LinkedIn

    May 6, 2015

    Guest Post by Gil Raviv

    Intro by Avi: Many experts had proclaimed LongForm Journalism was headed towards extinction in the digital age. In fact it has found a new resurgence and a new audience in the recent years. Thanks to that we can still get articles like This Old Man (featured on NPR’s best Longform Journalism list). This blog post is in the same spirit. This is not a bite-sized learn a cool new trick. We do have tons of those on our site and they’re great. But sometimes you want to sit-down and eat a seven-course meal. Enjoy the feast! Take it away Gil…

    In this blog post we will show you how to use Power Query in Excel to import data from LinkedIn and gain amazing competitive analysis insights based on company search. To get your attention right at the start, we will conduct this tutorial and analyze a domain that we all know so well and love – Power Pivot. We will analyze companies who specialize in Power Pivot.

    We will show you step by step how to utilize Power Query to extract information from LinkedIn including company size, founding year, location, specialties, and more.

    Build your own customized Competitive Analysis Dashboard

    When we are done, you can download the workbook, read below how to get LinkedIn access token in this tutorial (Step 1-6), and start using the workbook as your dashboard for competitive analysis. You can use its parameterized queries to search for companies in any domain, refresh the workbook and get a tailor-made dashboard for the competitive posture of your interest.

    Before we start, here are few screenshots of what you can get at the end.

    Screenshot below shows the world distribution of the 70 companies who specialize in PowerPivot (Created with Power Query and Power Map).
    image

    Next screenshot shows the distribution of companies by founding year. It’s interesting to see a gradual incline of younger companies who specialize in PowerPivot from 2009 to 2013, and to see a decline in 2014. It seems that last year fewer companies were founded with PowerPivot as a specialty.
    image

    Next screenshot shows company distribution by Specialty. You can see the most common specialties for companies who specialize in PowerPivot (e.g. Business Intelligence, SQL Server and Excel).
    image

    I am sure that by now, we got your attention :-) Read the rest of this entry »


    Our Road Ahead

    May 5, 2015

    Post by Rob Collie

    image

    If you believe in redemption
    I’m calling to you from another dimension

    -The Flobots

    Turn the Corner, Press the Accelerator

    This post will have a definite “personal” flavor to it, but also a strong professional flavor – of what you should expect from us over the coming year.  Personal and Professional are ALWAYS closely intertwined, and I’m less squeamish about blurring that line in 2015 than in 2009, when we started this journey.  Running my own business has taught me this, but really, it was always true.  I was just less aware of it when I worked at Microsoft.  Now matter how you slice it, it’s our lives.

    A few days ago, I resolved a long-running personal matter.  It’s… done.  Just looking at those two words – “it’s done’’ – wow, I’m still letting the new reality settle in.  It’s going to take awhile perhaps.  But we’re going to move forward aggressively now.  A new lease on everything.  And we’re not going to waste it.

    Lemony Snicket’s Series of UnFortunate Events

    steering wheel

    And I can’t help but ask myself
    How much I let the fear
    Take the wheel and steer

    -Incubus

     

    Read the rest of this entry »


    Secret Pot Roast Recipe: Power Query vs. VBA Macros

    April 30, 2015

    Guest Post by Willem van Dijk

    Intro by Avi: I have never met Willem van Dijk in person, but in my mind he has a persona of a scrappy boxer/standup comedian. A priceless combination. He would have you rolling on the floor no matter what. He brings home the reality of a business user trying to get some business intelligence. Your day job is something else – Finance, Marketing, Accounts, Sales, Product planning etc. But you do what it takes to get the data and analysis that you need. Here’s to the fighting spirit! Take it away Willem…

    I love to hate secrets just about as much as I hate to love secrets…

    Why? Because knowing a secret is great for the ego, yet too much ego is not great for the soul.
    Which is why I love reading posts on PowerPivotPro as it is all about sharing secrets which, in essence, are no longer such.
    Ironically, at times what one believes to be a secret often results to be common-knowledge, or worse, common-sense.

    In my inaugural post I promised that I would share a Pot-roast recipe (of the secret kind) so here we go…

     TopSecretppp

    Read the rest of this entry »


    Where’s Rob?

    April 27, 2015

    Post by Rob Collie

    image

    Can You Spot the Power Pivot Blogger In this Picture?

    A Little Scarce…

    Last week at the PASS BA Conference, Matt Allington was giving me grief about having not written enough blog posts lately.

    He is right of course.  It hit me pretty hard, to hear that, even though I already knew it.

    Doubly so since writing turns out to be one of the most rewarding things I do.

    Well, I have a reason.  There’s a long-running personal matter that has been consuming an ever-increasing share of time over the past year.

    No, I’m not sick, nor is anyone else.  Everyone is (and is going to be) fine, but that doesn’t make it any easier.

    It’s all finally coming to a conclusion this week and next.  Wish me luck.  If it comes out correctly, I will be “back” in a big way.  If not…  well, then I’ll still redouble my efforts here.  This site, and you folks, are “home” for me in an important sense.

    Thanks everyone for being here, for reading this, and for being so great to me (us) over the years. 

    Stay tuned for our normal guest post on Thursday, and watch this space for news late next week if all goes well.

    I also need to circle back with the results of the hardware/perf survey.  If you haven’t run that test yet, please consider it so that it makes the results more valid.

    Thanks,

    -Rob