BI Developer Technical and Soft Skills for Success

By: Koen Verbeeck | Updated: 2021-10-18 | Comments | Related: More > Professional Development Skills Development

Problem

In this tip, we'll present you with a list of traits – technical skills, soft skills, apps, problem-solving, etc. – that are typically found in successful Business Intelligence (BI) developers. It's of course by no means an exhaustive list and as it usually goes with such lists, number 9 is arbitrarily chosen.

Solution

1 – Have some affinity with your business (processes)

Let's kick of the list with a non-technical item. BI is short for "business intelligence" and the first word matters: business. If you want to create BI solutions (BI reports and dashboards) that are actually going to be used by the people in the company, you'll need to figure out what those people like to see in a report. This means conducting interviews relying on your communication skills for business requirements, but also trying to understand how those people do their daily jobs and how a report or dashboard – and the insights that come from them – help them to do their jobs better with more efficient decision making.

Teamwork, Cooperation, Brainstorming, Business, Finance

It will certainly help if you understand the business process that you're trying to model, be it sales, manufacturing, purchasing or something else. A good BI developer asks the end-users questions and isn't afraid to push back a little when requirements are made. If someone asks "give me a report that shows sales", you'll need to ask "which sales? What are sales? How do we define them?" If you go around the company and ask different people the definition of "profit", you might get a dozen different answers. A good BI project tries to consolidate everything and sets the standard for the rest of the company. Typically, BI projects are accompanied with data governance and master data projects. Or, in some cases, a BI project uncovers the need for such projects (when it turns out a lot of the data is in bad shape).

2 – Business Intelligence Developers Need to Learn SQL

I cannot understate how important SQL is. It's the de facto language of databases. It has been around for decades and is still used widely. Even in some "NoSQL" databases, some sort of SQL dialect was eventually supported. Azure Databricks supports SQL and most big data systems as well.

A BI developer will write SQL as a large part of their daily jobs. When creating ETL/ELT scripts in SQL Server Integration Services (SSIS) or Azure Data Factory, when modelling the data warehouse in SQL Server, when writing the source query for a report in SSRS and so on. Preferably, you write efficient and fast SQL. Here are some resources to get you started:

After a while, your SQL becomes more complex as you are troubleshooting harder business problems. You'll need to use constructs such as common table expressions, tally tables or window functions:

The SQL language is the cornerstone of working with data and is well worth investing your time in.

3 – Understand Data Modelling

No matter how good your SQL skills are, a badly modelled database can still results in performance issues. As a BI developer, you'll need a good foundation of data modelling skills as the foundation for software development. The most important modelling technique is the "star schema", also known as "the Kimball methodology". You use this to model your data warehouse in facts and dimensions. Here, data is denormalized so it is optimized for read performance. Another advantage is that facts and dimensions are intuitive to understand by non-technical people.

The star schema is not only important in the data warehouse itself, but also in SQL Server Analysis Services (SSAS), especially in the Multidimensional model (hence it's name), but also in the Tabular model. Even though you can model a Tabular model how you would like, it is optimized to work with facts and dimensions. The same goes for Microsoft Power BI models (which use the same technology as Tabular models behind the scenes). With a star schema model, the model performance faster and typically the DAX expressions are less complicated. A solid understanding of star schema modelling is definitely an advantage.

An example star schema, with a sales fact table and a couple of dimensions:

successful business intelligence developer 003

Other modelling techniques worth mentioning:

3^rd Normal form: this is the "default" for many OLTP databases. Here data is normalized so it is optimized for write access (faster inserts, updates and deletes). The downside for data warehouses is that reading data is slower because you have to join a lot of tables together. Data warehouse guru Bill Inmon uses a 3^rd normal form enterprise data warehouse, with smaller star schemas on top (called data marts) for reporting.
Data Vault: here you model data as hubs (storing business keys), satellites (storing historical data) and links (storing relations between hubs). It's a bit more complicated model, but its advantages are highly parallel writes, the ability to easily generate all metadata and ETL, and it's agility. The downside is that you have many, many tables with potentially lots of rows. For example, one source table containing 1 million rows sales data can result in multiple tables, all containing 1 million rows.

More information:

4 – Know the basics of DBA/development/performance tuning

As a BI developer, it doesn't hurt to know the basics of being a DBA (database administrator). Sometimes, you'll be the one responsible for maintaining the data warehouse, so you'll need to know about backups, high availability options and disaster recovery. You don't need to be a hardcore DBA, but it helps if you speak the language and that you can talk with a DBA about your data warehousing needs.

In the same spirit, you'll need to be a database developer as well. You'll need to create tables and indexes, so knowing about files, filegroups and the defaults of SQL Server can be a big help. You'll need to write views and stored procedures and know how to troubleshoot and optimize all of them. As mentioned before, performance matters. People don't like slow reports. If your SQL is slow, you'll have to investigate. Do I need an index? Or the statistics out of date? Is the query badly written? Is something else blocking my query? And so on.

There are some tutorials to help you get started:

5 – Get familiar with the Cloud

The Cloud is here to stay. Microsoft has a "cloud-first" strategy: all new features/products are released in Azure first, and then they'll (potentially) make their way to the on-premises products. Many companies have discovered the flexibility of the cloud and have started their migration process. Sooner or later, you'll come across a project in the cloud, so it isn't a bad idea to familiarize yourself with it. It's a bit of a different world after all.

You can learn about Azure SQL DB, Azure Synapse, Azure Data Factory and so on. But you don't have to stick with the Data Platform alone; if you're setting up services, you might need to know a bit about Virtual Networks, Azure Firewall, Azure Key Vault and many other services.

Good starting points are the fundamentals certification exams and their corresponding learning paths:

At MSSQLTips, there are many Azure related tips. You can find an overview here. Or you can take a look at all the recorded webinars, there are plenty with "cloud" in the title.

6 – Know the shortcomings of your tools

When you have a hammer, all of your problems look like a nail. It's tempting to try to implement all your requirements with one single tool, but in reality, tools are not perfect, nor cover all possible uses cases. For example, Power BI is well suited for interactive reporting and dashboarding, but doesn't really do well when the report has to be printed on a piece of paper. In that case, Reporting Services (SSRS) will be a better choice.

A good BI practitioner knows which tool to use in which situation. If the case isn't as clear-cut, you can do a proof-of-concept to figure out the pros and cons of a tool.

7 – Keep on learning!

The world of data is continuously evolving, and so should you. The world of BI from 10 years ago - which was all about SSIS, SSAS Multidimensional, SSRS and SharePoint - is very different from the world today. We still have SSIS and SSRS, but SSAS Tabular has gained more momentum than Multidimensional and the Power BI Service has replaced SharePoint (or did Microsoft Teams do this?). And I haven't even mentioned all the Azure services yet.

During all those years, we had to learn new products, new features and new services. It can all become overwhelming, since there's so many to learn as a BI professional. There are now also "new" disciplines, such as data science, data engineering and big data. Luckily, there are a lot of good and free options out there to learn:

On MSSQLTips.com you have new tips daily focusing on everything related to the Microsoft Data Platform.
The Microsoft Data Platform community is vibrant and many people share their knowledge. Many write blogs, but there are also YouTube channels, such as PASSTV for example. There are tons of conferences, both in-person as virtual, and there are regular user group meetings.
Microsoft offers a great deal of free training on the Microsoft Learn website.

8 – Embrace established software engineering practices

In the past years, tooling support for BI projects has improved drastically. There's no reason anymore to not use source control for your BI projects in Visual Studio or to deploy everything manually. In your projects, try to implement processes for managing integration, testing and deployments. Azure Devops is a good choice, as it supports CI/CD for the on-premises BI tools and for the Azure services.

When working in a team with multiple developers, having a good branching strategy can avoid many headaches. Accidentally overwriting each other's work is not exactly productive, and manually comparing XML-objects such as SSIS packages or SSRS reports is not easy.

You can find more info about branching and CI/CD in the following tips:

9 – Don't always stay on your BI island

Working in BI and with data in general is interesting and challenging. However, expanding your skillset beyond just business intelligence tools can pay off in the long term. For example, trying to program something in C# .NET will teach you how to work with other programming paradigms instead of the ones you encounter in SQL alone. And theses programming languages can be useful when you want to create a script task in SSIS for example, or an Azure Function. Taking up Python is useful for doing some data science or data engineering work.

Even some JavaScript and/or web development can be very useful. Who knows, somewhere in the future you need to embed some reporting into a website? Or you need to work with Power BI embedded? Another option is to go the non-technical route: learn more about the tenets of data visualization will help you to create better dashboards. Or you can go into the Agile/DevOps path and try to take your team to the next level.

The point is: always be learning, and don't be afraid to try something new.

Next Steps

What do you think? Should something be added to the list? Let us know in the comments!
If you're starting with BI, check out this tutorial.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-10-18

How To Become a Successful BI Developer