Don’t be afraid to visualise complex business processes


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.




Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...