Over the past several weeks readers have posted questions concerning the career paths of Excel and Power BI professionals. In general, these questions take the form of “I’m good with core Power BI technologies (DAX, M), is this sufficient to get into ‘official’ BI, as a consultant or on a corporate BI team?”
On page 303 of Power Pivot and Power BI, the guidance from the PowerPivotPro team is “With very little effort, an established Power Pivot pro can “change hats” and label herself a Business Intelligence Pro, a Tabular Modeler, even if she were “just” an Excel Pro a couple years ago.”
When you think about that statement carefully you see that a lot is left up to interpretation. What defines an ‘established Power Pivot Pro’? More importantly, just because you label yourself an SSAS Tabular pro, does that mean you’re going to be successful in your transition from Excel? (Will clients or other BI professionals take you seriously?)
As Jennifer Lawrence says above, what an Excel or PowerPivot Pro thinks is amazing is often, for good reason, considered a mess by a BI Pro. (A mess that someone in BI/IT will have to support and/or re-design).
My answer to the question is that in this instance, I have to disagree with Power Pivot Pro – I believe a great deal of effort is required to successfully transition from Excel/Power Pivot to become a MSBI professional. This doesn’t mean that you can’t deliver great value or find success by specializing in DAX and Tabular modeling, the Power Pivot Pro team is just one example of this, but I think that A) it’s important to distinguish the point solutions and prototypes that Power Pivot professionals can produce from the much more demanding requirements that BI professionals have to deliver and B) in many organizations, the DBAs and/or ETL team are simply not available (or won’t be made available) to support the data quality and shaping needs of a Power Pivot Pro and even advanced Power Query/M skills are not a sufficient substitute for this.
In my view and experience the Power Pivot Pro needs to become very fluent in SQL, at least competent with designing SSIS/ETL packages, and knowledgeable of data architecture, data warehouse design, and several other administrative topics (e.g. monitoring, availability, processing patterns/options, etc) as well. Many of the organizations that use Power BI and SSAS likely also use the SQL Server relational database so knowledge of it’s BI-relevant features such as the Columnstore index and table partitioning is important. Additionally, the Power Pivot Pro needs go further with SSAS to include performance optimization, infrastructure configurations, on-premises data gateways, partitioning, Direct Query vs Import decisions, and more.
BI professionals are able to have productive conversations with DBAs, Networking, System Admins, Security, ETL and Application Development teams, Infrastructure/Cloud and other IT personnel. The more successful BI pros are able to see the broader enterprise architecture and longer term issues – they plan and effectively persuade the right parties to help design their BI solutions. BI pros look for frameworks to leverage and advise against ‘technical debts’ to ensure long term ROI – they don’t get overly impressed by POC/Pilot demonstrations. You might consider my past post on The Cool Teenager and the Old Man – in some ways a Power Pivot Pro is like the cool teenager.
Maybe 2-3 years ago DAX was such a rare skill that it was relatively easy for a DAX/Power Pivot pro to obtain good SSAS Tabular work but today, and certainly over the next 2-3 years, I find it hard to believe that the other skills previously mentioned won’t also be essential. One alternative route, in terms of skill and career development, is for the Power Pivot Pro to also become an R and stats pro. These are the main two paths to my mind for the Power Pivot Pro – either the modern BI Architect or the Data Scientist. An easier though less financially rewarding option is to add DAX as a query language for use in SSRS and other applications as a core skill and to build an expertise in data visualization.
In summary, there are no magic pills or shortcuts. BI professionals have to constantly study and deliver across tools and domains outside the scope of analytical models. Power Pivot and Power BI doesn’t change this reality.
Insight Quest 
Hi Brett,
Your thoughts on DAX/M are enlightening… it has me thinking. To pursue the Data Science (R and stats pro) path, what do you think about the Microsoft Data Science curriculum?
LikeLike
Hi John,
Thanks for your comment and I hope I didn’t offend anyone. As mentioned in the post, Excel and Power Pivot/BI pros that don’t venture outside those tools clearly create value and DAX and M have a central role in the MSBI roadmap. The main thought of my post, and maybe I didn’t title it correctly, was just that you’re limiting your value severely if you can’t go upstream (ETL, DW) or downstream (visualization, statistics) from the analytical model. What you build with DAX and M may seem (and actually be) great in the short term but BI pros have to see the bigger picture too.
The MS Data Curriculum looks promising (a well integrated set of forward looking courses) and a good value overall. To obtain the data science track credit you have to spend $525, about $50 per course, but you also have the option to just attend the courses you need for free but not obtain the track credit. For example introductions to T-SQL and visualization in Excel or Power BI might be a waste of time and money. It’s hard to say how much help the curriculum would be in landing a data scientist or advanced analytics job. Generally field experience and provable skill/knowledge is more important than certificates and there’s many good sources of learning some of these topics (R, machine learning)for free. In general, if you go the BI architect route you want to be very close to the data storage and processing technologies (e.g. cloud service data warehouses) and processes but if you go the analytics route you want to be deep into the root math and stats – the equations and theory. With each path you can build around these fundamentals with a deeper knowledge of a particular implementation or tool but be valuable across platforms.
LikeLike