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 »


New Twist for Dynamic Segmentation: Variable Grain Control

November 6, 2014

By Avichal Singh [Twitter]

Dynamic segmentation or banding has been covered in PowerPivotPro articles in the past and in beautiful detail by the Italians – Marco Russo, Alberto Ferrari (these folks are literally “off the charts!” in Matt’s representation of Power Pivot skill levels :-) ).

It involves grouping your data in specific numeric bands or segments; for example looking at your sales data based on the price range of your products. You have a long list of price points for your products, instead of looking at each price point individually it would be nice to group them into segments say to represent the low, medium and high price items in your catalog.


Hundreds of products at different list prices…  =>  Grouped based on their List Price Range

Variable Range Selection

That is great, however it is hard to predefine segments that would work well in all scenarios. As your data changes over time, or as users slice and dice your existing data (e.g. filter to a specific region, product category or year) the segments may prove to be either too granular or not granular enough. In the case below, the predefined range does not have enough grain or detail and pretty much everything ends in one bucket ($3000-$4000).


Predefined Segment Ranges may prove too granular or not granular enough
as you work your data

What if your segments had a range of options from high to low granularity, so that you could choose the right segments based on the data and your need!


Range can be chosen to show 1000s, 100s or 10s based on dataset

Download File
Watch on YouTube or keep reading…

Read the rest of this entry »


5 common mistakes made by self taught DAX students

October 30, 2014

by Matt Allington

I am well under way in my career as a PowerPivot Consultant, Trainer and Workbook Hosting Provider.  And I have to say (now that I have delivered a number of Rob’s “PowerPivot for Excel” training classes) that I am finding delivering training to be one of the most rewarding things I do.

It occurred to me recently that people like me (and also Rob, Avi, Scott), that train users in PowerPivot are able to glean useful insights into the way people learn (and incorrectly learn) PowerPivot.  Today I am going to share with you 5 common mistakes that I have personally observed – maybe you will identify yourself in some of these things, or maybe you will confirm that you are doing just great.  Either way, it is worth a read to either discover a gap or confirm your skill.

But first: I have trained 2 general types of students

I have found there are 2 groups of students that sign up for my PowerPivot for Excel classes.  There are students that are very new, very green (see what I did there – green, get it?) and are using the class to get started.  They come in with very little knowledge about PowerPivot, but enough to know that this could be something awesome.  In the second group are students who have a reasonable amount of PowerPivot experience under their belt but realise there is more to it.  What is a “reasonable amount” of course can vary, but I would classify these people as “active” for 6-26 weeks with a total number of “invested hours” in the vicinity of 10 – 60 hours or so.  Often they are struggling to move forward, and this post covers the main reasons why.

I always ask my students to rank their knowledge on a scale of 0 to 10, and they normally rank themselves 0 (newbies) or about 3 or 4.

2 types

Read the rest of this entry »


Mini-Post: Getting Ready for the Seattle Class Nov 11-12

October 29, 2014

By Avichal Singh

Getting amped up for our first class in Seattle! Just got the box of books from Rob, which we hand out at the class. Check out all the other goodies you would get :-)

IMG_4902

The Excel to Power Pivot workshops have been a blast, but the class is going to be even more fun. Just looking forward to meeting a whole bunch of cool new people as well, who are all excited about Power Pivot (we’ll get you excited, if you aren’t yet). Everyone learns in their own way, however

  • Live class is a terrific way to jumpstart your learning
  • It is amazing how much easier it is to really absorb and cement the concepts, even if you have been using Power Pivot for a while
  • It is also a great networking opportunity. Some of these folks may be your new Excel friends for life :-)

    Speaking from my personal experience here – after attending Rob’s class even with 5 year’s worth of Power Pivot experience – and I have heard the same from Rob and Matt. See you soon!

    Click Here to View Class Details & Registration


  • Insight Center: Azure-Based SharePoint / Power Pivot Server (Joint Solution with Microsoft)

    October 28, 2014

    Post by Rob Collie

    Everyone Needs a Power Pivot Server.  The Flowchart Just Got an Exciting New Option.

    Your Server Options:  Power BI Online, On Premises / Do It Yourself,
    Third Party Cloud, and Now…  Insight Center!


    Our Continuing Mission:  A Power Pivot Server in Every, Um, Driveway

    Everyone, sing it with me:  “I’ve been wor-king on The Flow-chart, aalll the live-long daaay…”  Yes, The Flowchart.  The “yellow brick road” that helps lead your org to the Power Pivot server option best-suited for you.  (Why do you need a server?  Because it’s YouTube for Workbooks.) 

    Drawing the flowchart is, of course, the easy part.  Making sure that it “ends” in a variety of dependable options – that fit varying budgets, infrastructures, and org sizes – well, THAT is the reason why the flowchart is taking this much time.

    But said flowchart is getting closer – MUCH closer – to being ready.  We have been busy little beavers here at PowerPivotPro.  Crusaders for justice, as we oh-so-modestly think of ourselves, rarely get to rest.  The shortage of Power Pivot servers in the world is a humanitarian issue in our eyes.  (Yes, this paragraph was tongue-in-cheek.  Well, partly anyway.)

    Until now, you’ve basically had three classes of option:  1) Install and Run Your Own Servers, 2) Subscribe to Power BI Online, or 3) Lease Space From Third Party (non-Microsoft) Cloud Providers.

    All three of those options are good, viable choices, and remain so.  The Flowchart will soon help you choose between those, depending on Best Fit.  But recently it’s ALSO become clear that some organizations would benefit greatly from a Fourth Option.

    And with that realization…  Insight Center was born.

    Already Gaining Traction with Microsoft’s Enterprise Customers

    Read the rest of this entry »


    Power Pivot Job Opp in Washington DC Area

    October 27, 2014

    Post by Rob Collie

    image

    Is This You?

    Join one of Microsoft’s Most Progressive-Thinking Customers

    My friends in Microsoft’s Mid-Atlantic region have asked us to help connect one of their customers with Power Pivot talent.

    And, you know us.  We like this sort of thing Smile

    High-level details:

    1. Washington DC Area.
    2. Little or no travel required.
    3. Power Pivot skills a must.  Broader knowledge of the Power BI stack a plus (but not a must – the rest of the stack is quick to pick up).
    4. Strong communication skills and ability to understand the human/biz side of the equation.
    5. Ready to change the rules of the game & revolutionize the world of data (OK, I added this.)

    Drop us an email at careers@powerpivotpro.com with resume, CV, and/or questions.


    SUM, SUMX or CALCULATE()…Choices, choices!

    October 23, 2014

    By Avichal Singh (Avi)

    When I was working recently with a client, helping her remotely – I asked her to calculate the sum for sales amount in the table. She responded whether she should use SUM, SUMX or CALCULATE?

    Simple question, but not a simple answer. Or I can give you the classic lawyer response – “It depends!” Let’s review.

    SUM: Simple Unmitigated Magic

    The good news is that a simple SUM, would work in majority of the cases. A simple sum in the hands of Power Pivot is a powerful tool. With the magic of relationships a simple SUM can show you tricks you could never have imagined in Excel.

    Power Pivot relationships mean, that you define your measures once and use them everywhere.

    Power Pivot Measures: Define Once, Use Everywhere“Define Once, Use Everywhere”

    Your measures conform to the shape of your pivot, so you can drag and drop any fields from your model, use any slicers and the measures would still work.

    Sales:=SUM(Transactions[Amount])

    image
    Simple SUM() does magic in Power Pivot: Define Once > Use Everywhere in action

    Only when you see the results and go…”Uh…that is not exactly what I want”, should you explore other options.

    Step at a time – SUMX

    Read the rest of this entry »


    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 »