Sourcing candidates and training a team on the Microsoft Data Platform

The Microsoft Data Platform (and probably any BI tool) and FP&A seem to have at least one thing in common: it’s hard to find qualified people. I hear it all the time. I don’t think I consciously concluded that I could and should focus on training people up on both, but at some point, I did, and it’s worked out well for me. Looking back, I don’t think I’d have it any other way—I’ve seen the sheer pain other hiring managers have gone through trying to check wish-list boxes on their job descriptions. I’ve been lauded for my patience, but I have none for checking boxes. I’d much rather, and get more satisfaction from, invest in training someone—distill everything I’ve learned and spent hours trying to figure out into a focused progression that is not only useful to me but will be useful to the recipient in the future. I haven’t lost any of the people I’ve trained up yet, and I’m sure that, when I do, it’ll be heartbreaking. But I will be satisfied knowing that I’ve been a meaningful part of their professional development and that maybe they’ll boomerang.

Some Quick Thoughts on Sourcing

For a long time now, I’ve been hiring people based on aptitude. For Excel, DAX, and other Power BI skills, I try to craft a set of interview questions that bring out the candidate’s real interests and innate sense of curiosity, which I believe drives not only technical success in both FP&A and BI, but also longevity. When I prepare interview questions, I create a matrix that contains:

Columns 1-3 are thoughtfully completed beforehand; 4-5 are during the interview. If other members of my team are interviewing with me, I ask them to do the same, and we spend time before the interview merging questions and expectations. After the interview, we compare. Not only do I think this practice helps remove bias from interviews and is therefore fair to applications, but it also helps me to standardize interviews across candidates and makes *me* think about what it is I’m hiring for. If you want to go interview-fairness gangbusters, you could have a third-party read and score the interview questions. I’ve done this a couple of times and was amazed at the results, and the perspectives received.

Find the Data Genes and Nab Them!

If you have the data gene, you can probably recognize the data gene in someone else within a few minutes of talking to them. Rob talked about this in the first class I took from him. I’m pretty sure he said that you either have the data gene, or you don’t. If you find your candidate is more interested in analytics through pretty pictures, I’ve found it’s a good sign they don’t have the data gene. Don’t get me wrong: good presentation is essential, and I have my people read and take classes from data presentation experts, but in my opinion, it’s the understanding of the flow and business logic applied to data that makes a true business intelligence analyst.

Some Observations on Training

Once they’re on board, bringing them up to speed takes investment. Here are some tenants and progressions I feel have worked in creating Power BI (namely DAX and Power Query) experts:

Talk to people about the real nature of data. No matter how advanced the analyst, one of the first lessons I teach people is that data is either pivoted or unpivoted, and you need to recognize the difference. This is not difficult but spotting it straight out of the gate is vital. Once they can recognize pivoted/unpivoted, then I teach that rows and columns are almost always dimensions (exception: Power BI allows for more than one value column). I teach them to recognize the difference between a dimension member and an attribute of that dimension. If you talk to people using expert terminology, well, they become experts faster.

Explain your outcome in plain English (or any preferred “plain” language). I often find that people jump right to the architecture of an outcome. “So, I need to calculate A times B, then divide, then join these two tables, and multiply the whole thing by X.” I used to do this—I think it’s a normal station on the analytics learning curve. When someone comes to me frantic with this type of approach, I ask them to take a deep breath and explain to me, in plain English, what they’re trying to accomplish. Most of the time, the “architecture” turns out much more straightforward than they initially expected, especially with analytic powerhouses like DAX and Power Query.

Keep organized. Visual organization of just about everything in analytics is critical. Label and order measures appropriately (I highly recommend a calculation table). Organize your data models well. Delete tables and measures from your data model that are no longer needed. Label your presentations well. Note assumptions and filters. At some point, I realized just how much time I saved by knowing exactly where something is and that it’s valid, not to mention the time spent on damage control if someone else picks up your analysis and “looks under the covers.” When teaching people about DAX and Power Query, I’m often the person asking “why is this there,” etc. and we spend time deleting, reorganizing, re-labeling and it pays off big-time.

The One Table Method. One word: RELATED. I’m sure we’ve all abused RELATED to one extent or another. If super-powered DAX and Power Query, combined with intimate knowledge of SSAS Tabular’s bells and whistles is an analytic blackbelt, then RELATED is my white belt. It’s a rite of passage. As a stepping stone to understanding data modeling, I will teach the grasshopper to recognize 1:many relationships and all the pitfalls just getting to that point, especially if your data is messy, contains blanks, etc. Then, I will have them bring [what they will later come to know as because of point #1 above] dimensional data into the fact table. This usually starts with one to three dimension tables and one fact table. I’ll instruct them to create a pivot using only the fact table. At this point, they think PowerPivot is the bee’s knees, and the sold sign appears on the front lawn. Getting to the sold sign is the sole purpose of this exercise in many respects, but it also gives the grasshopper something tangible they can use AND replicate on their own.

The progression usually comes in this form: “Matt, now I have two fact tables that I need in my analysis—how do I do that.” This is when I then true power of the Microsoft Data Platform is unlocked, and their mind is blown. I now explain the proper construction and use of dimensions vs. facts, which is easily understood from going through the One Table Method. They’ve waxed on and waxed off and are now ready to fight (and win)!

Filter First, Then Calculate – teach people to effectively query. I believe Rob taught this as a best practice in the Foundations class. I use this EVERYWHERE now, whether writing efficient DAX or Excel measures and it’s amazing the outcome. In a field where everyone has their approach, this adage lends some consistency to it. If they become advanced enough, then they’ll recognize the real value of this mantra (i.e., so their analysis doesn’t crash, and they lose faith).

Time Intelligence. I treat time intelligence as it’s own (but very essential) beast. If time intelligence is involved, my trainee has worked their way through most other points above and then layer on the time intelligence aspect. To help people get their heads around it, I created a calendar table with calculated columns that demonstrate fundamentals of time intelligence. For example, below is the relative year calculation—the current year is equal to 0. One year back is equal to 1 and so on.

Relative Year =
YEAR ( TODAY () ) – Calendar[Year]

Ultimately, I teach people to work as much as they can into a measure and ditch calculated columns. But, don’t underestimate the power of calculated columns as a learning tool. I shunned them when I learned they were “bad,” but now embrace them for a different reason.

Where to go from here

I have a couple of people on my team who are moving past these steps and are creating analytics I know I can be confident in. They’ve messed up. They’ve overused RELATED. They’ve realized that pivoted data tables are hard to work with without unpivoting them. They’ve overused calculated columns. I’ve found that if you enable people in an almost-Montessori way, they usually ask me the next right question, which is a signal they’re ready for the next step. Just recently, someone asked me if there was a way not to repeat elements (scalars, filters, hard-coded values) of a calculation, so we had the VAR discussion. Marco and Alberto have lots of great information on variables that is kept up to date with the latest thinking, but Matt Allington wrote my favorite beginner article on the subject. DAX, Power Query, and other elements of Power BI are nuclear powered, so the learning never stops.

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.