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 »


Preview of New Website Content

September 23, 2014

Post by Rob Collie

image

The Three Primary Benefits of Power Pivot vs. “Traditional” Excel
(Click for Full-Size Version, and Please Share Your Thoughts in Comments!)

Changes Afoot

Some exciting things in the pipeline here at PowerPivotPro HQ.  Most of those will remain secret until next month, but one of them is a website overhaul – with Power Pivot adoption continuing to grow, website traffic also continuing to grow (more records fell last week!), and the site’s 5-year anniversary approaching, it’s time.

So today I’d like to get your feedback on some of the new content.

It’s all pictures, so I promise it’s light Smile

Read the rest of this entry »


Using SUMX on an IF statement–Call Center Example

September 18, 2014

Guest Post by Elin Ramsey

PowerPivotPro Intro: I love real world examples. They really show how the tool is being used and the challenges you may encounter in a real world situation (like not having access to all the data you need, read how Elin tackles that).

Note how Elin slowly builds this example and keeps layering business logic in the DAX formulas. This comes naturally to the Business User who is intimately familiar with the workings and what is actually needed from the data. Throw in a third party and suddenly the process becomes much more burdensome as two parties need to communicate back and forth. In fact majority of the cost of any project (including BI projects) is Communication, read more here.

Building BI Faucets Then PlumbingI will go out on a limb and say “Business Intelligence belongs to the Business User”. The BI team is necessary in building the plumbing, but Business User should firmly be in charge of building the faucets. And build faucets before the plumbing. That is one of the stops in Rob’s amazing story (read or watch) that he recounted at PASS BA Conference. Here’s to building faucets! Elin, take it away…


Intro

My name is Elin Ramsey and I work as the Analytics Manager in a call center. Click here to see more about me. I’ll be reviewing a real world application of SUMX on an IF statement, as recently described here.

One thing I need to be able to estimate is the number of active customer support representatives we have on any given day. I can’t use HR or timecard data as I don’t have access to it. Another challenge is that some representatives will sometimes be acting as trainers most of a day and we don’t want to count them then.

I’m going to walk through what my definition of active is, how we set up the data, calculating whether a representative is active on any given day, using SUMX to calculate the number of active reps on any given day, and then a couple of examples of what we can do after we have that formula.

Read the rest of this entry »


Tuesday Grab Bag of Biz and Fun

September 16, 2014

Post by Rob Collie
 
mullet with labels

Today’s Post is Like a Mullet Haircut:  Business in the Front, Party in the Back

Let’s Go!

Series of quick updates today, mixture of pure fun and serious biz, sometimes simultaneously.  In this post:

  1. Download the Draft Index for the Online University – whether you are currently enrolled or not, this may be relevant.  Grab it here.
  2. Live Class in Philly – seats still open for Oct 21-22, more details here.
  3. “Secret Summit” Between Microsoft and Power Pivot early adopters last week.
  4. DAX:  The Power of Paper
  5. Yours Truly taken into police custody for Possession of Industrial-Grade Data Tools

 

Read the rest of this entry »


Power BI: Natural Language Q&A–reality or hype?

September 11, 2014

by Avichal Singh

Now that Rob has acquainted us all with the Power BI family, I wanted to take a closer look at one of the relatively new members: Natural Language Question & Answer or simply Q&A.

Rob pointed out in his last post how Microsoft seems to be targeting large enterprises; perhaps as a result we have seen many an impressive demo (watch Amir Netz at WPC 2013 and James Phillips at WPC 2014). But you have to pause and consider whether the features demonstrated are eye-candy or are indeed useful in practical settings.

Q&A is one such feature, which after my first look I had labeled as eye-candy and set aside (speaking with others, I find many are in the same place). All that changed for me when I attended Adam Wilson’s Q&A session at the PASS BACON. Adam not only outlined the workings of Q&A, but also shared tips and tricks and real world implementation stories. After my change of heart, I rushed back to work and cobbled together our first Q&A workbook. Q&A articles on the Power BI MSDN Blog were an immense help as well. Here is what I learnt from implementing Q&A:

  1. One Q&A workbook per subject area
  2. Eat your own dog food
  3. Optimize for Q&A (Cloud Modeling)

Read on or watch the video version.

Read the rest of this entry »


What is Power BI?

September 9, 2014

by Rob Collie

Power BI, Definition #1:  An “Umbrella” Term for all of the “Power *” Tools

Power BI, Definition #1:  An “Umbrella” Term for all of the “Power *” Tools
(Click for Full-Size Version)

An Overdue Treatment

I’ve posted before about Power Pivot vs. Power View and then later I added Power Query and other technologies to the explanation, but I’ve never just straight-on tackled the question of “What is Power BI?”  So let’s get that off the list…

…And clean up my prior visuals while we are at it.  (Can you believe that Power Query doesn’t have an icon/logo yet?  Well, boom – I just gave it one.  You’re welcome, Microsoft – although really it was just from one of the buttons already in PQ.)

Unofficial Definition = “Umbrella Term for the ‘Power *’ Tools”

#1, Power BI is an “umbrella” term that is used to describe all of the various “Power” data tools that Microsoft offers us.  This is NOT the definition that you will typically hear from Microsoft, but colloquially, when someone says “Power BI,” there’s a good chance this is what they mean.

Official Definition = “The Cloud Publish & Manage Service from MS”

But I think Microsoft would say, more officially, that Power BI is their paid cloud service for publishing, sharing, managing, and consuming the results of those tools pictured above.

For instance, Microsoft sales reps today are measured, in part, by their customers’ adoption of precisely that online subscription service.  They are NOT measured by how many of their customers are simply using the “free” desktop tools included in the Power BI “umbrella” term.

Read the rest of this entry »


CALCULATE ( ) Lessons from Looney Tunes

September 4, 2014

By Matt Allington

In my last post I talked about a mistake I made early on in my DAX learning journey.  In that post I showed a formula that used CALCULATE ( ) to turn a row context into a filter context (AKA context transition).  Here is a quick refresh of the relevant part of that post.

calculate post

Since this post, I have been thinking about CALCULATE ( ) and wondering how to explain “WHY” CALCULATE ( ) creates context transition – this is the topic of today’s post.

I am sure there is a wide age profile of readers of this blog, and at least some of you would remember the Merrie Melodies cartoon “Cheese Chasers” where the dog (Marc) is sitting and using an ACME adding machine to make sense of what he is observing. [I know I said Looney Tunes in the title, but there is not a lot of difference and I thought more people would know what Loony Tunes was]. Before you watch this brief 40 second clip from the cartoon, let me first set the scene.  Everything is backward; the mice don’t eat cheese, the cat WANTS to be chased by the dog – you get the idea.   Spoiler alert – I will refer to the punch line below, so don’t read on until you watch the video if you want the full 1951 immersive experience.

Read the rest of this entry »


The Benevolent Virus Spreads to Australia :)

September 3, 2014

Post by Rob Collie

PowerPivot Pro Training Australia

“Scaling Out”

Despite me being arguably the loudest advocate of our favorite technology, it remains true that this Power Pivot thing is much bigger than any one person, and everyone needs help.

I’ve already reached the limits of my own personal travel calendar.  As much as I would LOVE to jetset around the world and teach classes everywhere, well, my family would never see me except for occasional burned-out stopovers at home.

The fortunate position I find myself in is that I’ve met AMAZING people over the years.  When I need help, I have the luxury of consulting the Rolodex of Awesome.

So when the time comes to help spread our most benevolent of viruses to the other side of the world, I’m ridiculously happy to be able to call up people like Matt Allington.

(If you haven’t heard Matt’s story, you should quickly click on his name above, right now, because it will blow your mind – I am still putting mine back together actually.)

 

 

Certified in My Methods

PowerPivot Pro Training Australia

As of Today, Matt Allington is the ONLY Person
I’ve EVER Certified in My Teaching Methods

In July, Matt made the voyage from Down Under to the shores of Lake Erie for an intensive week at PowerPivotPro HQ.  I deprived him of sleep.  I fed him nothing but Ohio food, like 3-pound grilled cheese sandwiches.  I subjected him to relentless DAX torture.  He bent spoons with his mind.

Read the rest of this entry »


In-Person Class in Philly, Oct 21-22!

September 2, 2014

Post by Rob Collie

How recent students have described the class

image

Our July Class in Cleveland – 90% of these Fine Folks Came from Out of State!
(And One From Australia!)

Taking it to the East Coast – and Beyond

After running three consecutive classes in Cleveland (April, July, and August), and seeing a packed classroom for the last two, we’ve decided that I should start occasionally venturing out of the Heartland and teaching the class elsewhere too.

This time we’re going to be partnering with Microsoft itself, and teaching in their MTC (Microsoft Technology Center) just outside of Philadelphia.  An exciting step for sure, uniting me (former ‘Softie and Power Pivot’s most passionate advocate), with, you know, the company that makes the product.  Probably should have figured out some way to cooperate sooner, but such is the independent nature of current and former ‘Softies.

Click Here to View Class Details & Registration

Optional Gathering/Dinner on “Middle” Night

image

Some Folks from the August Class Throws the “Excel Gang Sign” at Dinner
(Can You Spot MrExcel Himself, Bill Jelen?)

On the night of the first classroom day, we’ve been running an informal gathering at a local restaurant, and many people reported that it was one of their favorite social outings ever.  Just being surrounded by people with similar backgrounds, mindsets, and problems is a unique experience, especially for the Excel crowd.

So of course we will organize such an outing in Philly as well.  Venue TBD (if you have suggestions, send them our way).

Included:  Thumb Drive and 50% Off Online University

All Students Receive a PowerPivotPro thumbdrive containing the materials from the course:

thumbdrive

And also a 50% off discount code to the online video course.  Past students have found it to be a helpful “reinforcement” to the live class (as well as covering topics that we don’t have time for in two days), so we have decided to offer it as a bundle.

More Info and Registration…

Click Here to View Class Details & Registration


Dynamic Charting In Power Pivot

August 28, 2014

Guest Post by Idan Cohen From Excelando

Rob already blogged about charts with dynamic measures -Using Named Sets for “Asymmetric” pivots, where you can choose the measures to be displayed with a slicer.

But what about dynamic axis?

This clever technique was found by one of my analysts , Gal Vekselman, when a client challenged us.
What is it useful for you ask ??!

For example, I want a chart to display sales by quarter and then change it to sales by month with a press of a button?
Or another useful scenario where I want to see sales by category,  but when choosing a category on the slicer,  the sub categories for this category will be displayed in the chart,  and when choosing a sub category the chart will display the underlying products.  Sounds cool, huh? Well,  it is even cooler.

And the way to do it? Named sets!

Dynamic Monthly Chart Dynamic QuarterlyChart

Use the period selection slicer to quickly change the chart axis, from Month to Quarter

Read the rest of this entry »