See You in 2015!

December 16, 2014

Post by Rob Collie

image

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


Last Week’s Survey

December 15, 2014

Post by Rob Collie

image

It’s 30 Seconds – We Promise! Smile

Hi folks, if you haven’t taken last week’s survey regarding 2015 PowerPivotPro in-person classes, please take the 30 seconds and do it today.  It’s super quick and very helpful.

Thanks!


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…

Benefits

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:

image

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]:=

   DISTINCTCOUNT(Sales[CustomerKey])

Read the rest of this entry »


The Catalyst to Corporate Power Pivot Success

December 4, 2014

by Matt Allington

Many businesses (dare I say most) are yet to get started with Power Pivot and Self Service BI.  Often the first person in an organisation that discovers Power Pivot doesn’t know how to navigate the political and cross functional challenges within the organisation to make it all possible.  The tragedy of this are the missed opportunities – lost time is lost money.  There are huge benefits that can flow to businesses if they could just get this happening. There are benefits for IT and the overall business, including lower IT costs, faster speed to value realisation and indeed green field analyses that would not have even been contemplated as feasible without a Self Service approach and a tool like Power Pivot.

Start Line

How to get started?!

Read the rest of this entry »


Black/White Decisions in a Greyscale World

December 2, 2014

Post by Rob Collie

image

     JACK RYAN:  “…with them in the same place, the odds of coincidence are
     dropping fast. Still, there’s no way I can be… absolutely certain.”

     ADMIRAL GREER:  “Excuse me, Jack.  Tell me one thing in life that IS
     absolutely certain.  What I need to hear is your best guess, and I think
     I’ve heard it.  Haven’t I?”

Belatedly Reviving a Thanksgiving Tradition

In past years I occasionally took Thanksgiving Week as an opportunity to write some more “thoughtful” things, such as The Cult of the Right Thing.

This year, I would like to share something more “on topic” than any of the previous ones, while still very much fitting the theme of thoughtful/reflective.

An Exchange in “Patriot Games” Captures It

The conversation pictured above, between Jack Ryan and Admiral Greer, was pretty much lost on me the first time I saw Patriot Games in 1992.  It’s a relatively “bland” exchange.  No emotion, no drama, no action.

But in subsequent viewings, that brief little clip etched itself in my brain.  It captures something CRUCIAL about being a human being, regardless of what you do for a living.  But for those of us in data-driven professions, I think it’s even MORE relevant.

WHY is it so crucial?  Well, let’s start with Left Brain and Right Brain…

Left and Right Brain:  A Powerful Alliance

image

 

Read the rest of this entry »


Monday Mini-Post: Excel to Power Pivot Cross the Gap Webinar (Fri Dec 5th)

November 24, 2014

By Avi Singh [Twitter]

UPDATE: We ran into technical issues with the Webinar on Dec 1st using Join.me. Rescheduling to Fri Dec 5th. See event page for details. Switching to world class web conferencing this time (GoToWebinar)…um, but we are limited to 100 attendees (and a bit oversold, oops!). Join on time to make sure you get your spot – if unable to join, rest assured; you’ll get first dibs on the next one we schedule (planned in January)

Happy Monday! We’ll be going light this week for Thanksgiving, but we have big things cooking for next week. Monday Dec 1st we are hosting our Webinar Excel to Power Pivot: Cross the Gap. Webinar is targeted towards Excel users who are new to Power Pivot.

Most readers here are already across the gap, but do share this with others you know who might benefit. Like the guy in the next cubicle, who has the monthly Excel report that’s chock full of formulas across ten sheets and takes a week to update; and how he needs to schedule his vacation always around the weekly update because no one else can do it. Yeah, tell him his life is about to be transformed.



Click Here to Register for Webinar


Flat to Star Transformation using DAX Query

November 20, 2014

By Avi Singh [Twitter]

As I have started working with clients around training/consulting, I am surprised by how often I find FlatToStar_RockyIVtables that have been flattened, i.e. the data tables have been combined (joined) with lookup tables, to produce a really wide table with everything and the kitchen sink in it. Maybe that’s to be expected; after all that is the modus operandi when working in Excel. That’s the first habit that I try to break. I would nominate this for the top 5 mistakes but that already has 6 on the list :-)

Usually it is just a matter of changing how data is being pulled from the source to address this issue, and go from flat table to separate Data and Lookup tables (Star Schema). However at times, your data source itself may only have the flattened version of data. If you have little control/influence over the data source, you may be stuck with a flat table. Or not!

Flat to Star: Using DAX Query

The first thing that should come in your mind to deal with this should be Power Query, and we will cover that. But this time let’s try to use DAX as a query language for this purpose. Our sample data, is a flattened file which has sales data as well as product attributes. We would like to separate these into distinct power pivot tables.

FlatToStar
Go from a flat wide table to separate Data and Lookup tables

The hardest part is getting started; for that watch the video. I will skip to the DAX query used, with a tip of the hat to Marco and Alberto from whom I have learned so much (and continue to do so).

 

Read the rest of this entry »


Visualization Layers in Perspective: The Last Mile

November 18, 2014

Post by Rob Collie

image

The Models We Build in Power Pivot are the Prime Movers.  Visualization is “Just” Where the Information (Output of the Model) Meets the Humans.

A Comment Plucked Straight from My Brain!

Avi’s post last week was deliberately thought-provoking (and to some, perhaps outright provoking, heh heh).  It drew a lot of views, shares, tweets, and comments.

My favorite comment, by far, was this one by Andrew.  Here’s a slightly condensed version of it:

“Five years from now, I envision a time when awesome visualization tools and incredible and beautiful charts are common and very cheap. Everyone will have those and they will be easy to make. What will still be rare is what Power Pivot does and the role that it plays along with Power Query. The real action is in prepping data and turning it into information that can be visualized… not the actual visualizations. Unfortunately, so many get lost amid a sea of pretty bars on maps and dynamic spider webs…

It’s the model stupid! It’s the ease of crunching numbers and aggregating millions of rows on the fly! It’s the simplicity of turning trash data into sparkling clean information and not having to go through red tape clogged and extremely expensive departments to do it – do it yourself!…

Hear hear, Andrew!  Salute!  We park our cars in the same garage, as the movie producer said to Christian Slater in True Romance.  As I’ve said before, even the phrase “let’s look at the data” sets us up for failure.

Visualization:  NOT Unimportant.  Just Easier to Replace!

image

Just Like Light Bulbs:  Crucial, But MUCH Easier to Replace than the Wiring.

In short, my observations today come down to these three things:

  1. RVOE:  Replacement Value Over Excel.  Excel is essentially free, and is incredibly under-rated as a viz tool.  If you measure any Viz tool’s true value through this lens, it’s much harder to justify the price of most of them.
  2. It’s Relatively Easy for Software Firms to Build a Viz Tool.  Compared to modeling and calc engines like Power Pivot, at least.  And Power Pivot is the best such engine on the market.  So, I think it’s sensible to start with Power Pivot as your core “commitment,” and then pick your viz layer – there are many available, and many more to come as time goes on.
  3. Don’t buy a “full stack” Analytics Tool Just Because of its Viz capabilities.  This is kinda the corrolary to #2, but it also helps us understand why certain “Viz” tools are so stinking expensive.
  4. My parting thoughts on the ways in which Viz tools ARE important. 

Read the rest of this entry »


Power BI and Tableau: Best Friends Forever

November 13, 2014

By Avichal Singh [Twitter]

Yes, you heard me right. I hear a lot of talk about “Power BI versus Tableau” – some discussion even makes it sound like the next fight airing on pay per view. But I believe they go great together.
image

A dream you have, will come true

(courtesy Fortune Cookie MessageSmile).

Sooner or later there are going to be legions of Power Pivot developers – yes I call them developers; these are our Excel Superheroes charged up on Power Pivot. No longer creating workbooks, these are BI applications in all respects. The pace is hard to predict, but revolution is coming. Already in my short stint at PowerPivotPro I have been heartened to work with folks from California to Germany who are discovering Power Pivot and it’s amazing powers (Can we make this a drinking game, every time I use the word ‘power’, everyone downs a shotSmile). If you are still sitting on the Excel fence, sign up for our next Webinar and Cross the Gap from Excel to Power Pivot.

Back to Tableau…

Power BI can power Tableau

Tableau is a great tool. I can only claim a 101 level familiarity, but I have seen enough to know that it is a very promising tool. At it’s heart Tableau is and will be a data visualization company. Which is terrific, it is better to do one thing and do it well (Sigh! MicrosoftDisappointed smile)

Although at one point Tableau had filled gaps in data collection, modeling and shaping; by now Power BI toolset (Power Pivot and Power Query specifically) have done that and more! The biggest overlap still, is with Power View (and it’s upcoming cousin Power BI Dashboard).

But I say, if Office can play well with Dropbox, in spite of Onedrive…
Power Pivot and Power Query can play great with Tableau, in spite of Power View.

Read the rest of this entry »


Displaying Top N, Bottom N, and “All Others”

November 11, 2014

Post by Rob Collie

image

If We Use Excel’s Built-In Top N Filter to See Our Top 1,000 Customers, It Hides the Other Customers Completely.  But Using DAX, We Can Just “Split” the Audience into Two Groups.

This Came Up Recently…

Hey, I absolutely ADORE the TOP N filter capability offered by all Excel Pivots.  It kicks major booty and we use it all the time:

image

The Top 10 / Top N Value Filter in Pivots:  Get to Know It, It Does Amazing Things

But If we set that to show us the top 10 customers, it shows us JUST those 10 customers:

image

OK cool, we see those top ten customers, and that they collectively purchased $132,026 of stuff from us.

But we want to ALSO see how much the OTHER (non top 10) customers are worth too.

Read the rest of this entry »