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.