Haavest | Performance Optimization through Data Engineering and Dashboard Redesign for an International Pharmaceutical Company

Task

One of the most-widely used tools to our client is a dashboard for the analysis of content performance in companies.

It was used by different groups of persons with individual analytical questions simultaneously and served in particularly for the impact analysis of campaigns in the marketing and sales department. The specific issue was the scope of emails and newsletters, as well as of completed sales calls.

The tricky thing about the dashboard was that, due to its historically grown development and the simultaneous consideration of totally different target groups, its operation was inefficient. The loading time for a call was more than 2 minutes – way too long.

So it was definitely no fun to use it. And our goal was clearly defined: the performance had to be improved dramatically.

Implementation

We took a closer look at the object in order to get our own idea of the problem at hand. We also talked to the product owner and tried to find out the backgrounds regarding the graphics and charts that were shown in the dashboard. Central question: what was really still relevant to the end user today?

Furthermore, it was necessary to sight the dashboard’s code technically. However, in the hectic of the development there hadn’t been much time for helpful documentations that we could resort to. Therefore, our experienced consultants made a special analysis. Only a simultaneous expert in the fields of Tableau and SQL can understand which code parts are important and which ones perhaps were nothing more than discarded ideas of the previous developers. The performance needed to be measured fundamentally in order to identify the weakest points.

Apart from the depths of the back end, we also examined the dashboard’s front end. Result: there were too many filters at the same time, 18 for each view. These had to be reasonably reduced. Also the numerous charts with sometimes thousands of simultaneous entries, which could only be explored via scrollbar, were an example of inefficient representations.
Thanks to a preliminary analysis we were able to evaluate the necessary steps.

We divided the project into three targeted phases:

Phase 1

So we set down to work. First the existing dashboard built in Tableau had to be examined carefully. Every single computed field was analyzed. From that we draw conclusions regarding business logic, which we coordinated closely with the client. Besides we developed new, more simple formulas that led to the same outcome. However, that wasn’t enough yet for a performant result, so we also had to access the structure behind the dashboard. The SQL data source – the foundation on which the report was operating – had to be designed again from scratch.

Phase 2

The data model was outdated. For this reason it had been necessary to use complicated formulas.

So we analyzed the SQL code from scratch and found new ways to develop more efficient back-end logics. Basically we split a huge, denormalized back-end table into 14 flexible artefacts which could be dynamically queried in the report if necessary.

Success showed up quickly: the performance went up by 95%.

Phase 3

In agile workshops with the various stakeholders we identified the optimal visualizations that were really relevant to the end users. Because the data foundation already existed, we were able to provide a large number of special views individually in the Tableau work folder.

There were some visualizations, especially charts, that we didn’t transfer to the new dashboard, in consultation with the clients. Because images and graphics, if used properly, can often lead you to the analytical answer much faster and more intuitively.

Originally there was the idea that we must only have a few perspectives, in order not to cause too much maintenance work IT-wise. But this wasn’t even necessary. Because we were able to prove that an efficient work folder in Tableau almost always has its weak point in the back end and not the front end.

Conclusion

In the end, our redesign project was able to substantially contribute to the fact that the dashboard was much better accepted company-wide. That is why the client can, on the whole, make better decisions for the optimization of his campaign.

The user rate was measurable. Only a few weeks after its launch, the new dashboard already had twice as many users as before. And the users’ feedback regarding the better and more intuitive front end was entirely positive.