Perhaps the most important decisions business intelligence professionals make is determining which technologies to learn and to what extent or depth should they grow their knowledge. For example, should you dedicate yourself to becoming a true expert in Power BI datasets and Analysis Services tabular models or should you develop competencies (but not expertise) across a variety of tools such as Power Apps, Power Automate, and Azure Synapse Analytics? Although these decisions are unique to the objectives and circumstances of each individual, today’s post shares seven principles I’ve learned over the years and adopted in my work.
Consistency
The process of learning new skills and tools and enhancing or refreshing existing skills needs to be highly prioritized and a part of one’s daily schedule or routine. Maybe there are other professions where you only need to ramp up when starting the job or where change is limited but this isn’t the case for BI professionals. Very rarely do I find someone that’s reached a high level in anything ranging from technical knowledge to physical fitness to spiritual development without consistent, focused effort.
Even if you’re not sure what would be the best or most helpful topic to learn, just begin to regularly learn and assess the benefits and applications of this knowledge and adjust as necessary. The learning process can take many forms – books, certification materials and exams, MS Doc articles, blogs, YouTube videos, online courses, etc. I find that writing down notes is helpful in retaining information so I use several OneNote notebooks with different sections and pages dedicated to different BI tools and data languages. In many real-world project scenarios I don’t have the full answer or code pattern off the top of my head but I’m able to run a quick search in OneNote or navigate back to the relevant sections or page of notes from years ago to find what I need.
One technique I’ve found particularly helpful is creating your own BI solution, preferably with publicly available data that you personally find interesting. In my case, over the years I’ve built up a solution that retrieves a large array of economic and financial data that I use to ultimately help drive investment decisions such as whether to buy or sell certain assets. Building and enhancing this solution has involved the use of Azure Automation, Azure Storage, SQL Server, and of course multiple tools of Power BI. You certainly can learn a great deal via standard training data such as AdventureWorks but it might not be as rewarding to you and thus could impact your consistency.
Languages First
Data and analytics languages should be prioritized far beyond graphical interface tools/software/services and should form a solid foundation of a skillset. Unlike software applications and various user interface controls which change frequently, the essential concepts and semantics of data languages such as SQL and DAX don’t change nearly as frequently and thus languages offer a much greater return on the time invested to learn them. For example, the fundamental PowerShell scripting knowledge I built up years ago using the Windows PowerShell ISE can still be applied today in many different tools, apps, and services that weren’t around back then such as Azure Function Apps and Visual Studio Code.
In almost every BI project I can remember, even projects that were explicitly intended to use low-code or no-code tools, it was the combination of different languages such as SQL, DAX, Kusto (KQL), Power Fx, and others that delivered the most value or which made the difference between project success and failure. Similarly, even in projects in which my role was intended to exclusively focus on the data model layer with DAX, I’ve almost always found myself also writing SQL, Power Query (M) and using other languages and code either in the data warehouse or on the reporting layer.
This doesn’t mean rich and powerful GUIs should be ignored, it’s just that at every level of solutions you can rarely get away from custom logic or business rules that graphical tools generally don’t support well, if at all. Moreover, I find that even if a robust graphical tool such as Power Query can technically implement the intended logic, it’s often beneficial or necessary to replace this tool-generated code with more efficient, custom code such as a SQL query.
Although they’re technically not ‘languages’, the expressions you write and functions you use in paginated or SSRS reports, SSIS packages, and Azure Data Factory pipelines are often the most critical, value-added elements when using these tools. Likewise, in a recent project with a finance and accounting team, the use of cube formulas and to an extent certain MDX concepts and functions was essential in delivering their cell-specific Excel reports on top of a Power BI dataset. Ultimately it’s the ability to write the code to closely align the solution to the specific needs or requirements of the users or business that adds the most value.
Breadth over Depth
It’s preferable to be competent with many related BI tools and skills than to be an expert exclusive to one or two. There certainly are scenarios in which a deep expertise in a particular technology or language is necessary such as performance tuning DAX measures over large scale models but I find that it’s much more common to need to just have a solid knowledge of several tools.
The best analogy I can think of to express this view is that it wouldn’t be prudent to invest all your money in one financial asset such as Bitcoin even if you strongly believed in the value and future of this asset. Your investment portfolio would be much more resilient (less risky) if you owned a broad mix of stocks, bonds, real estate, gold, etc. Similar to investment portfolios, in the BI world you don’t know with certainty which tools will grow in popularity/value versus which technologies will fade away. Additionally, you don’t always get to choose to use your preferred technology on every project so it makes sense to maintain a broad portfolio of different tools and skills you can call on when needed.
Semantic models and SQL have stood the test of time so I’m fairly confident that various forms of dimensional modeling and SQL (e.g. Azure Databricks SQL) will continue to be valuable. However, increasingly it’s not ‘just’ the data warehouse, data model, and reports and dashboards that’s in scope of project deliverables. It’s also the need for an app (PowerApps) for business users to write and store data, an automation scenario (Power Automate, Azure Automation, etc) to support, and the need to retrieve data from APIs, for example.
Again, in these project scenarios it’s typically not a deep expertise that’s needed but rather it’s the absence of a material knowledge gap such that the same developer can effectively build different components at different layers of the solution and integrate them together. The attitude that ‘that’s somebody else’s job’ may be appropriate in certain circumstances but tool-specific specialists adds significant delays, costs, and communication issues to projects creating a strong incentive for organizations to replace specialists (or one-trick ponies) with more broadly skilled BI engineers.
Up the Stack over Down the Stack
Technologies involved with the acquisition, transformation, cleansing, standardization, and modeling of data (up the stack) should generally be prioritized over downstream technologies that rely on these processes such as data visualization and advanced analytics or data science. It’s generally accepted that 70-80% of BI/DW project time is spent in the ETL/ELT (extract-transform-load) layers of the solution and naturally any failure in these processes can bring the entire solution to a halt. This doesn’t mean that semantic modeling and report development or enabling self-service analysis is ‘easy’ (as some data engineers sometimes erroneously conclude), it just suggests that a BI pro that can also, when necessary, go up the stack and effectively contribute in these critical data processing areas will be much more valuable.
An example of this principle at work could be someone whose primarily a Power BI and Analysis Services data model developer choosing to learn Azure Data Factory rather than the Pandas Python library or how to create custom R visuals for Power BI. Obviously there are many great use cases for data science and roles in which these skills will be highly valued but my experience is that there’s a much larger market or demand for relatively simple reports and analyses which can reliably deliver accuracy, performance/scalability, and the required security.
Moreover, I find that business stakeholders are often willing to compromise on certain aesthetics and front end user experiences such as using a Power BI report instead of paginated report or vice versa but there’s little to no flexibility in the back end. In the back end, you either load and process the data correctly and with resilience or you don’t and the project fails. Thus, skills and experience with the tools that produce the structures needed for reporting, typically dimension and fact tables with surrogate keys, tend to be more valuable than tools that help to analyze and visualize the data.
Dev and Ops
As much as BI development skill is needed, the operations and management aspects of BI solutions such as managing access, deploying changes (without breaking changes), and monitoring usage should not be understated. The work associated with a given BI project typically doesn’t end after the solution has been deployed to production and users have adopted the new solution. In many cases, despite best efforts to capture requirements and test all realistic scenarios, various changes are needed at different layers of the solution and additional questions are raised as more users access the solution.
BI developers need to be familiar with all common tools and techniques for managing and monitoring their solutions such as deployment pipelines, Azure DevOps, Git and repos, ALM Toolkit, Log Analytics and PBI Premium capacity monitoring app. In terms of access controls, a knowledge of Azure Active Directory (AAD), preferably with an ability to work with AAD via PowerShell, can be an invaluable skill as well. A few common operations examples include the need to re-process only certain partitions in a Power BI dataset to reflect the updated state of the source system, a need to asses why a refresh failed and make necessary changes, and the need to revise which security groups have which permissions to a dataset and in which RLS roles.
Ultimately nobody wants a silo solution that only one developer understands and which is maintained outside the context of standard team testing and deployment processes. Development skills get the solution/project off the ground but a mix of development and operations skills are generally needed to ensure the solution remains a valuable asset.
Target Synergies and Gaps
After a foundation of BI skills has been constructed, preferably around data languages, complementary skills and tools can be added. For example, a Power BI dataset or Analysis Services model developer could build competencies with paginated reports and cube formula-based Excel reports which utilize the Power BI dataset or Analysis Services model as its source. Likewise, a broadly skilled Power BI developer could become proficient in the development of simple but effective Power Apps and Power Automate flows that supplement or enhance Power BI solutions. The Power Apps and Power Automate visuals in Power BI make it very easy to integrate functionality from these services into Power BI.
A BI developer that’s confident with SQL and other data transformation tools may consider Power Query (M) unnecessary or a lower priority but since A) it’s a language and B) it’s focused exclusively on data connectivity and transformation (up the stack) this could be an important knowledge gap to close. In some project scenarios, due to policies around access to a source system or the nature of the data sources, Power Query (M) is essential and thus not closing this gap would represent a significant risk.
In some projects a Power BI architect or senior developer is also called on to perform the functions of a Power BI administrator. If the developer or solution architect has a material gap in administrative topics such as Power BI licenses and resource usage, service principals, tenant settings and premium capacity management this gap would also represent a significant risk.
The main idea is to actively close knowledge gaps closely related to one’s core skills and tools and to invest in learning tools with deep integrations around one’s core.
Know Yourself
The great investor Peter Lynch said “You have to know what you own and why you own it.” I think this line also applies to BI skills. You need to be honest with yourself about how skilled or deep your knowledge is in certain areas and be able to clearly apply those skills to practical situations. If you can’t do this, you run the risk of being put in a situation in which you can’t deliver what’s expected or you won’t be able to put yourself in a situation to succeed.
This isn’t complex but it does require discipline to regularly assess yourself and determine whether you are where you want to be relative to your expectations and objectives. For example, are you strong enough with DAX that you can move on to other tools or, given the value of DAX to your role, should you keep studying DAX concepts (e.g. context transition, filter context) and various DAX functions and patterns.
Wrapping Up
I’m sorry for the long delay since my last post. My project work and the Second Edition of Mastering Power BI has consumed most of my time the past few months. I expect to begin posting more regularly again going forward.
Insight Quest 
One comment