Don’t be afraid to visualise complex business processes

In Business Intelligence we extract data from anywhere. Beside the technical structures that define how the data is stored, we also need to comprehend the business processes behind that structure. It’s all in the name, right?

Figure 1 – The devil is in the details (made with Lucidchart)

If it was so easy to just extract the data from the source and load it directly into the data warehouse, BI analysts/developers would become obsolete. In a lot of cases the incoming data is transformed which entails extra business rules or logic being applied to obtain a final, correct, expanded data set that makes sense to the business user and offers a clear insight.

When a business rule isn’t translated properly into a technical transformation, this early error – that can be a small detail – made between analysis and development will cascade further and can have disastrous results. Defining this translation means communicating with the people involved at the technical and business level. This can mean meeting up, calling or in most cases mailing the persons involved. In my personal experience I’ve noticed that a visualization of a question/problem tends to be better understood by other people and involves them more effectively. A diagram is concise, more appealing and less prone to ambiguity which goes hand in hand with the KISS principle (Keep It Short and Simple). Remember nobody is waiting for that 100th mail of the day with pages of text.


Despite Lucidchart being a direct competitor of Microsofts’ Visio, which also offers extensive capabilities, this tool is cloud based which means you don’t have to install anything. Since in a lot of cases a workstation is provided by the customer, this shouldn’t be an obstruction anymore. You will have a cloud based working environment and a document repository at your disposal for free. These benefits made me choose Lucidchart above Visio. Free also means limited capabilities though:


But even with limited capabilities your options are still very diverse. The user interface is appealing, responsive and intuitive so you are started up quickly.

You will be able to import and export to Visio files. Although this is not a free feature, it’s nice to know that the tool offers this kind of extensibility.

Main benefits for me as an individual user:

  • Free (though limited)
  • Cloud repository
  • Fluent/responsive/appealing
  • Easy to get started
  • Export capabilities

Next I will explain two personal kinds of visualizations that recur from time to time.

Real life examples

Business user vs the BI developer

Figure 2 –Timeline business case chart (made with Lucidchart)

A lot of times the business needs to involve time as a transformation parameter which in my eyes can become a maze fast. Especially if the business user himself is defining cases on the spot. In the image above you see the result of a first meeting with the business user. I have visualized 3 different cases based on a timeline (gray line), events (yellow rectangles) and contracts (green rectangles). Over a certain time period we have all kinds of events that can happen to a certain contract. The above image acted as a starting point. The visual representation enabled the business to think of new cases more easily: what if I drag a yellow rectangle after a green rectangle et cetera. Moreover this way we had common concepts based on colors: a timeline, an event and a contract. So we were always talking about the same things when communicating to each other. While this seems self-evident my experience teaches me that this is definitely not always the case.

Technical user vs the BI developer

Figure 3-ERD diagram (made with Lucidchart)

A lot of times you will be searching for the correct data when business requests have been defined. This means going to the technical person that has knowledge of the data source: an application developer, functional analyst et cetera. Sometimes this really can be a hassle because of the normalization of OLTP systems. You have your business definition that you can explain to the technical person and normally he should be able to point you in the right direction. But this isn’t sufficient, remember the devil is in the details.

In just a few situations I have been presented an ERD (Entity Relationship Diagram) while it makes all the difference. So especially in longer assignments I have tried to compose my own ERD’s for the source entities/tables. Sometimes it seems overkill to make an ERD for 3 entities, but from my own experience I have learned that with new business requests new tables will have to be migrated with or without additional transformations. After a year you may end up extracting data from 40 entities and then a good ERD will prove it’s worth acting as a blueprint to offer quick insight.


Do we have to visualize everything? Off course not, but when it is the most efficient way to explain complex problems, define business cases, dissect database structures et cetera in a visual way, don’t be afraid to try it out.




Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...